PipeQL

PipeQL is a granular, functional query language that compiles to SQL for dbt workflows.

Project Layout

• src/pipeql/: compiler core, adapters, and CLI
• dbt_pipeql/models_pipeql/: authored PipeQL models (.pipeql)
• dbt_pipeql/models_generated/: compiler output SQL for dbt runtime
• benchmarks/healthcare_pipeql/dbt_healthql/: complex healthcare benchmark project
• tests/: compiler/parser/join/golden SQL tests
• scripts/: compile/build helper scripts
• docs/README.md: docs index
• docs/spec/grammar_vnext.md: canonical grammar/spec

Canonical Vocabulary (Zero Aliases)

• pick, drop, mutate
• filter, rollup, dedupe, sort, limit
• join_left, join_inner, join_cross, concat, window, sql, sample
• if(...) expression for conditional logic inside expression-bearing verbs (mutate, rollup, etc.)

Canonical rules:

• pick(...) is projection-only (no named assignments).
• Use mutate(name: expr) for all renames and derived columns.
• Use cast shorthand expr -> type instead of a cast verb.
• Cast shorthand must use spaced arrows: expr -> type.

Join Syntax

• Same-key join:
  • join_left(users, on: [user_id], bring: [name])
• Key mapping join:
  • join_left(users, on: [user_id -> id], bring: *)
• on: must be a list form (on: [..]), even for single keys.
• Join mapping arrows must be spaced: left_key -> right_key.
• bring supports explicit renames:
  • join_left(users, on: [user_id -> id], bring: [name -> user_name, plan])
• bring: * (or omitted bring) selects all right-side columns.
  • CLI emits warnings when this is used in paths containing /marts/.

Workflows

uv sync

# Run compiler tests
uv run pytest -q

# Interactive run (human/agent workflow)
pipeql run --file path/to/model.pipeql --db /tmp/dev.duckdb --limit 50
pipeql run --file path/to/model.pipeql --db /tmp/dev.duckdb --step 3 --format json
pipeql run --file path/to/model.pipeql --db /tmp/dev.duckdb --block int_patient_activity

# Demo dbt project
./scripts/compile_pipeql_models.sh
uv run dbt build --project-dir dbt_pipeql --profiles-dir dbt_pipeql

# Healthcare benchmark project
./scripts/build_healthcare_benchmark.sh

Expression Shorthands

• Cast operator:
  • event_ts: recorded_date -> timestamp
  • Wrap complex expressions before cast:
    • total_num: (amount + tax) -> double
  • Typed null:
    • missing_value: (null) -> varchar
• JSON access operator:
  • resource_json:subject.reference
  • resource_json:code.coding[0].code

SQL Governance

• sql(...) expression mode is allowed for column expressions.
• Full-query sql(...) must reference __input__.
• sql(...) rejects semicolons and DDL/DML keywords.
• CLI emits marts warnings for sql(...) usage because it bypasses structural guarantees.