semantic-query-compiler 0.1.0

Agent-friendly semantic metric compiler with dialect-aware SQL rendering

semantic-query-compiler

Agent-friendly semantic metric compiler with dialect-aware SQL rendering.

semantic-query-compiler turns governed YAML metric definitions into inspectable SQL for warehouses such as BigQuery, DuckDB, Postgres, and Snowflake. It is designed for CLI and agent workflows: discover metrics, validate a model, compile SQL, explain the query plan, and compare results against trusted SQL when you need confidence.

The compiler is intentionally separate from warehouse execution. It owns semantic definitions, validation, planning, SQL rendering, and comparison helpers. Run compiled SQL with your warehouse client, BI tool, notebook, dbt workflow, or agent runtime.

Current status: 0.1.0 beta. BigQuery and DuckDB have execution coverage; Postgres and Snowflake are compile/parse-tested. See docs/support-matrix.md for support levels and v1 release gates.

Install

From a checkout:

uv venv
uv pip install -e '.[dev]'
semantic --help

From GitHub:

uv tool install 'semantic-query-compiler @ git+https://github.com/rasmusengelbrecht/semantic-query-compiler.git'

Optional BigQuery comparison support needs the BigQuery extra or dependency:

uv pip install -e '.[bigquery]'

Quickstart

Create starter files:

semantic init --dir demo-semantic --with-duckdb-example
cd demo-semantic
semantic validate --model semantic.yml --check-compilable
semantic compile revenue --model semantic.yml --period "current month" --request request.json --format sql
semantic compare-metric revenue \
  --model semantic.yml \
  --period "current month" \
  --request request.json \
  --reference reference_revenue.sql \
  --dialect duckdb \
  --engine duckdb \
  --setup setup.sql

A runnable DuckDB example lives in examples/quickstart/.

Define a semantic model

Create a YAML file, for example semantic.yml. The recommended root key is semantic; model and semantic_layer are also accepted root keys.

semantic:
  version: 1
  modules:
    - identifier: bookings
      project: demo-project
      schema: analytics
      table: bookings
      dimensions:
        - column: country
          type: categorical
        - column: channel
          type: categorical
      metrics:
        - identifier: booking_count
          name: Booking Count
          calculation: count
          time: bookings.created_at
          filters:
            - column: state
              operator: in
              expression: confirmed,completed,cancelled
          slices:
            - name: confirmed_only
              filter:
                column: state
                operator: equals
                expression: confirmed
          dimensions:
            - country
            - channel

        - identifier: revenue
          name: Revenue
          calculation: sum
          time: bookings.created_at
          value: bookings.revenue
          filters:
            - column: state
              operator: in
              expression: confirmed,completed,cancelled
          dimensions:
            - country
            - channel

        - identifier: average_daily_rate
          name: Average Daily Rate
          calculation: ratio
          time: bookings.created_at
          numerator: bookings.gross_booking_value
          denominator: bookings.nights
          dimensions:
            - country

        - identifier: request_success_rate
          name: Request Success Rate
          calculation: custom-ratio
          time: bookings.created_at
          numerator_sql: COUNT(CASE WHEN requested_at IS NOT NULL AND state IN ('confirmed', 'completed') THEN id END)
          denominator_sql: COUNT(CASE WHEN requested_at IS NOT NULL THEN id END)
          allowed_functions: [COUNT, IF]
          dimensions:
            - country

    - identifier: ad_spend
      project: demo-project
      schema: analytics
      table: marketing_costs
      dimensions:
        - column: country
          type: categorical
      metrics:
        - identifier: marketing_spend
          name: Marketing Spend
          calculation: sum
          time: marketing_costs.date
          value: marketing_costs.cost
          dimensions:
            - country

  cross_module_metrics:
    - identifier: roas
      name: ROAS
      calculation: derived-ratio
      numerator_metric: revenue
      denominator_metric: marketing_spend
      join_on: time
      join_type: inner
      dimensions:
        - country

Supported metric calculations:

• sum
• count
• count-distinct
• ratio as safe SUM(numerator) / SUM(denominator)
• custom-value
• custom-ratio
• cross-module derived-ratio

Supported metric and slice filter operators:

• equals, not-equals
• is, is-not
• in, not-in
• like, not-like
• greater-than, greater-than-or-equal
• less-than, less-than-or-equal
• null, not-null, is-null, is-not-null

Create a request shape

For CLI use, prefer --period for the date window and keep the request file focused on query shape:

{
  "timeGrain": "monthly",
  "breakdownDimensionIds": ["country"],
  "filters": [
    { "dimensionId": "country", "filterValues": ["DK"] }
  ]
}

Then compile with a named period:

semantic compile revenue \
  --model semantic.yml \
  --period "last 24 complete months" \
  --request request.json \
  --dialect bigquery

Supported request features:

• timeGrain: none, daily, weekly, monthly, quarterly, yearly
• breakdownDimensionIds
• sliceName for named metric slice filters
• query filters
• timeComparison: YoY, MoM, Last Year, Last Month, YoY Match Weekday
• cumulativeMode: ytd, mtd, rolling_days
• targetSeries with explicit target table mapping
• excludeOpenPeriod and excludeToday period clipping

For API use, or when you need exact boundaries, fromDate and toDate can still be provided directly in the request JSON.

Supported period shortcuts:

• current year (year-to-date)
• current month (month-to-date)
• last N days|weeks|months|quarters|years
• last N complete days|weeks|months|quarters|years
• Q1 2026
• 2026
• 01-2026

For trailing closed-period reporting, prefer complete periods. Example: --period "last 24 complete months" returns 24 closed monthly buckets. For current-period-to-date reporting, combine a relative period with excludeOpenPeriod in the request. Example: current-year YTD through the last complete month:

{
  "timeGrain": "monthly",
  "cumulativeMode": "ytd",
  "timeComparison": "YoY",
  "targetSeries": "budget_current",
  "excludeOpenPeriod": true
}

Compile SQL

semantic compile revenue \
  --model semantic.yml \
  --period "last 24 complete months" \
  --request request.json \
  --dialect bigquery \
  --format sql

Explain the generated plan:

semantic compile revenue \
  --model semantic.yml \
  --period "current year" \
  --request request.json \
  --dialect bigquery \
  --format explain

Write SQL to a file:

semantic compile revenue \
  --model semantic.yml \
  --period "current year" \
  --request request.json \
  --dialect bigquery \
  --output query.sql

Inspect and validate a model

semantic init --dir demo-semantic --with-duckdb-example

semantic metrics --model semantic.yml --format json
semantic search-metrics "gross booking value" --model semantic.yml --format json
semantic describe revenue --model semantic.yml --format json
semantic joins --model semantic.yml --format json

metrics, search-metrics, and describe include discovery metadata such as descriptions, ai_context synonyms, teams, generic metadata, and relationship/formula notes when present in the model.

Run validation:

semantic validate \
  --model semantic.yml \
  --check-compilable \
  --format json

Optional: for stronger governance, validate against a column registry. This catches model drift such as missing tables/columns, stale warehouse metadata, and unsafe joins when uniqueness metadata is available:

semantic validate \
  --model semantic.yml \
  --column-registry column_registry.json \
  --max-registry-age-hours 24 \
  --require-join-uniqueness \
  --check-compilable \
  --format json

A registry can be generated from warehouse information_schema exports or a dbt manifest.json when you have those available:

semantic registry-from-information-schema \
  --input information_schema_columns.jsonl \
  --warehouse warehouse-prod \
  --output column_registry.json

semantic registry-from-dbt-manifest \
  --input target/manifest.json \
  --warehouse warehouse-prod \
  --output column_registry.json

Targets

Targets are optional benchmark/budget/goal values that can be joined onto metric results. They live outside the semantic model in a normal warehouse table. The compiler only needs to know which table to read and how its columns map to the generic target contract.

A simple unsegmented target table has one row per metric, target series, and time period:

CREATE TABLE analytics.metric_targets (
  metric_id TEXT,
  target_series TEXT,
  metric_time DATE,
  target_value DOUBLE
);

Example rows:

metric_id	target_series	metric_time	target_value
revenue	budget_current	2026-01-01	125000
revenue	budget_current	2026-02-01	130000
booking_count	budget_current	2026-01-01	420

Then request a target series:

{
  "timeGrain": "monthly",
  "targetSeries": "budget_current"
}

Compile with the target table:

semantic compile revenue \
  --model semantic.yml \
  --period "current year" \
  --request target-request.json \
  --target-table warehouse.analytics.metric_targets \
  --format sql

The default target column contract is:

Purpose	Default column
Metric identifier	metric_id
Target series / version	target_series
Target period start	metric_time
Target value	target_value
Breakdown dimensions	one column per grouped breakdown alias

If your table uses different names, map them at compile time:

semantic compile revenue \
  --model semantic.yml \
  --period "current year" \
  --request target-request.json \
  --target-table warehouse.analytics.metric_targets \
  --target-metric-column metric \
  --target-series-column series \
  --target-time-column time \
  --target-value-column value \
  --format sql

For breakdown targets, add one target column per breakdown dimension. The grain becomes one row per metric, target series, time period, and target dimension combination. For example, a monthly revenue target broken down by country and channel should have one row for each (metric_id, target_series, metric_time, country, channel) combination.

The target column should use the same name as the breakdown alias, or the dimension can set target_column in the semantic model:

- column: country
  type: categorical
  target_column: market

Example segmented target table:

CREATE TABLE analytics.metric_targets (
  metric_id TEXT,
  target_series TEXT,
  metric_time DATE,
  market TEXT,
  channel TEXT,
  target_value DOUBLE
);

Example segmented rows:

metric_id	target_series	metric_time	market	channel	target_value
revenue	budget_current	2026-01-01	DK	paid_search	45000
revenue	budget_current	2026-01-01	DK	organic	30000
revenue	budget_current	2026-01-01	SE	paid_search	25000
revenue	budget_current	2026-01-01	SE	organic	25000

When compiling breakdownDimensionIds: ["country", "channel"], the compiler joins target rows on metric_id, target_series, metric_time, market, and channel, then returns target_value and target_comparison_percentage. If the request does not include a breakdown, matching target rows are summed to the requested time grain.

If one physical target table stores multiple overlapping grains, pass each possible target dimension column with --target-dimension-column:

semantic compile revenue \
  --model semantic.yml \
  --period "current year" \
  --request country-request.json \
  --target-table warehouse.analytics.metric_targets \
  --target-dimension-column market \
  --target-dimension-column channel \
  --format sql

The compiler then picks the lowest matching target grain for the requested breakdown before summing targets. This prevents duplicate totals when a table contains both total rows and segmented rows. Use --target-null-column only when you want to force a fixed-grain target by requiring a target dimension column to be NULL.

Targets can be combined with time comparison and cumulative modes. Target columns describe current-period target comparison; historical comparison columns describe historical metric comparison.

Ratio target semantics:

• additive metrics sum matching target rows
• same-module ratio metrics try to find matching numerator and denominator sum metrics with the same time/filter shape, then compute SUM(numerator target) / SUM(denominator target)
• custom-ratio metrics use the direct target metric aggregate (SUM(value)) because their numerator/denominator are arbitrary SQL snippets, not reusable metric IDs
• cross-module derived-ratio metrics compute SUM(numerator metric target) / SUM(denominator metric target)
• these component target semantics also apply when targets are combined with timeComparison or cumulativeMode

That means a direct target row for the ratio metric itself is not used when component target metrics are available. If a component target is missing, the ratio target is NULL.

Request filters are applied to target rows too. The compiler maps filter dimensions through target_column metadata before filtering the target table.

Custom SQL governance

Custom metrics can use SQL snippets:

- identifier: postgres_custom
  name: Postgres Custom
  calculation: custom-value
  time: bookings.created_at
  custom_sql_dialect: postgres
  allowed_functions: [SUM, CAST]
  sql_expression: "SUM(gross_booking_value::INT)"

Behavior:

• snippets are parsed with SQLGlot
• custom_sql_dialect parses from a source dialect and renders into the requested target dialect
• bare columns are qualified with the base table alias
• allowed_functions is optional; when present, functions outside the allowlist fail compilation

SQLGlot models CASE WHEN ... THEN ... END branches as IF, so include IF in allowed_functions for governed CASE expressions.

Compare results

Compare compiled SQL for one metric against reference SQL:

semantic compare-metric revenue \
  --model semantic.yml \
  --request request.json \
  --reference reference.sql \
  --engine bigquery \
  --project my-gcp-project \
  --location EU \
  --format json

Compare two arbitrary SQL statements:

semantic compare-results \
  --left current.sql \
  --right reference.sql \
  --engine duckdb \
  --setup setup.sql \
  --format json

Use this for adoption checks: compare compiler output to your own trusted SQL, dashboards, or finance-approved reports before relying on a metric in production.

See docs/support-matrix.md for current beta support levels and v1 release gates.

Execute compiled SQL

The compiler does not own warehouse execution. Write SQL to a file and run it with your warehouse client, BI tool, notebook, dbt operation, or agent runtime:

semantic compile revenue \
  --model semantic.yml \
  --period "current year" \
  --request request.json \
  --dialect bigquery \
  --output query.sql

What is implemented

• YAML semantic model loader
• metric listing, metadata-aware search, description, validation, and join graph CLI
• SQL / JSON / explain output
• BigQuery, DuckDB, Postgres, and Snowflake dialect rendering
• aggregate, ratio, custom, and cross-module metrics
• safe multi-hop joins over many-to-one / one-to-one relationships
• metric filters, query filters, breakdowns, and time grains
• time comparisons
• cumulative windows
• target joins and target/comparison/cumulative combinations
• custom SQL source-dialect transpilation and function allowlists
• result comparison harness for DuckDB and BigQuery
• column registry generation from information_schema and dbt manifest.json
• dialect matrix tests that compile and parse representative BigQuery, DuckDB, Postgres, and Snowflake SQL
• CI lint/test gate

Development

uv venv
uv pip install -e '.[dev]'
uv run ruff check .
uv run pytest
uv build