engel-semantic-layer 0.3.3

Engel-style semantic layer compiler for BigQuery

engel-semantic-layer

Engel-style semantic layer as a Python package.

This package lets users define metrics/modules in YAML (same shape as Engel's code reference) and compile executable BigQuery SQL for a metric query request.

Scope

• YAML loading from:
  • one-module-per-file (module: root), or
  • single-file semantic layer (semantic_layer.modules + semantic_layer.cross_module_metrics)
• Metric types:
  • sum
  • count
  • ratio
  • count-distinct
  • custom-value
  • custom-ratio
  • derived-ratio (cross-module, metric-on-metric, join_on: time)
• Metric filters + query-time filters
• Metric slices (slice_name) for base metrics
• Time grain support: none, daily, weekly, monthly, quarterly, yearly
• Breakdowns with dimension access checks
• Relationship graph traversal across modules (relationships, with legacy joinPaths support)
• BigQuery SQL output

Changelog

See CHANGELOG.md for release notes.

Installation

Install from PyPI:

pip install engel-semantic-layer

Install from TestPyPI (pre-release validation):

pip install --index-url https://test.pypi.org/simple/ --extra-index-url https://pypi.org/simple engel-semantic-layer

Install from source for local development:

pip install -e .

Example

from engel_semantic_layer import QueryFilter, QueryRequest, SemanticLayer

layer = SemanticLayer.from_path("examples/model")

request = QueryRequest(
    from_date="2026-01-01T00:00:00Z",
    to_date="2026-01-31T23:59:59Z",
    time_grain="daily",
    breakdown_dimension_ids=["user_email"],
    filters=[
        QueryFilter(
            dimension_id="user_email",
            filter_values=["[e-mail]"],
        )
    ],
)

sql = layer.compile_sql(metric_id="metric_event_count", request=request)
print(sql)

YAML shape

You can use either:

• one-module-per-file (module: root), or
• a single semantic_layer.yml with modules and cross_module_metrics.

module:
  schema: "analytics"
  table: "records"
  identifier: "records"
  dimensions:
    - column: "country"
      type: "country"
  metrics:
    - identifier: "record_volume"
      name: "Record volume"
      ai_context:
        synonyms:
          - "booked value"
          - "record amount"
      relationships:
        formulas:
          - id: "record_volume_from_abv_and_bookings"
            kind: "product"
            expression: "ABV * # Successful Bookings"
            inputs:
              - metric: "abv"
              - metric: "successful_bookings"
        influences:
          - metric: "sessions"
            direction: "positive"
            strength: "strong"
            confidence: "medium"
            note: "Session tracking is directional rather than exact."
      calculation: "sum"
      time: "records.recorded_at"
      value: "records.amount"
      dimensions:
        - "this.*"

Metric relationship metadata is optional and not used in SQL compilation. It is intended for downstream AI/discovery consumers and metric-tree style tooling.

Relationship metadata fields:

• relationships.formulas[].id
• relationships.formulas[].kind (sum | product | ratio | difference | custom)
• relationships.formulas[].expression
• relationships.formulas[].inputs[].metric
• relationships.formulas[].machine_expression (optional)
• relationships.influences[].metric
• relationships.influences[].direction (positive | negative | mixed | unknown)
• relationships.influences[].strength (optional: weak | medium | strong)
• relationships.influences[].confidence (optional: low | medium | high)
• relationships.influences[].note (optional)

Relationship example (use dimensions from another module safely):

module:
  identifier: "orders"
  schema: "analytics"
  table: "orders"
  dimensions:
    - column: "customer_id"
      type: "categorical"
  relationships:
    - to_module: "customers"
      from_column: "customer_id"
      to_column: "id"
      relationship_type: "many-to-one" # one-to-one | one-to-many | many-to-one | many-to-many
      join_type: "left"                # left | inner | full | right
  metrics:
    - identifier: "orders_count"
      name: "Orders"
      calculation: "count"
      time: "orders.created_at"
      dimensions:
        - "customers.country"

Cross-module derived ratio in single-file mode:

semantic_layer:
  modules:
    - identifier: "wide_metric_count_a"
      project: "demo-project-123456"
      schema: "analytics_wide"
      table: "wide_metric_count_a"
      metrics:
        - identifier: "metric_actual_primary"
          name: "Primary Metric, Actual"
          calculation: "sum"
          time: "wide_metric_count_a.fulfilled_at"
          value: "wide_metric_count_a.value_primary_amount"
          filters:
            - column: "settled_at"
              operator: "is-not"
              expression: "NULL"

    - identifier: "entity_snapshots"
      project: "demo-project-123456"
      schema: "analytics_marts"
      table: "entity_snapshots"
      metrics:
        - identifier: "metric_entities_active"
          name: "# Active Entities"
          calculation: "count-distinct"
          time: "entity_snapshots.date"
          distinct_on: "entity_snapshots.dim_entity_id"
          filters:
            - column: "is_live"
              operator: "is"
              expression: "true"

  cross_module_metrics:
    - identifier: "ratio_primary_per_entity"
      name: "Primary Metric per Active Entity"
      calculation: "derived-ratio"
      numerator_metric: "metric_actual_primary"
      denominator_metric: "metric_entities_active"
      join_on: "time"
      join_type: "inner" # optional: inner | left | full
      relationships:
        formulas:
          - id: "ratio_from_primary_and_active_entities"
            kind: "ratio"
            expression: "Primary Metric, Actual / # Active Entities"
            inputs:
              - metric: "metric_actual_primary"
              - metric: "metric_entities_active"

API/OpenAPI field mapping (camelCase -> YAML snake_case):

• sqlExpression -> sql_expression
• numeratorSql -> numerator_sql
• denominatorSql -> denominator_sql
• numeratorMetricId -> numerator_metric
• denominatorMetricId -> denominator_metric

Notes:

• derived-ratio currently supports join_on: time.
• join_type supports inner (default), left, and full (FULL OUTER JOIN).
• query request must include timeGrain for cross-module metrics (timeGrain: none is not supported for cross-module derived ratios).
• requested breakdowns must be available on both numerator and denominator metrics.

Publishing

Release asset workflow:

• .github/workflows/release-assets.yml

What it does:

• Runs tests
• Builds wheel + sdist
• Uploads artifacts to the GitHub release for v* tags
• On manual dispatch, can attach to a specified tag or create a draft release

CLI

Validate your semantic model:

engel-semantic-layer validate --model-path examples/model --json

Compact summary output for CI logs:

engel-semantic-layer validate --model-path examples/model --json --summary-only

Validate that all metrics are compilable with synthetic dates (uses --time-grain monthly by default so cross-module metrics compile too):

engel-semantic-layer validate \
  --model-path examples/model \
  --json \
  --check-compilable

Override synthetic compile time grain when needed:

engel-semantic-layer validate \
  --model-path examples/semantic_layer.yml \
  --check-compilable \
  --time-grain weekly

Use period shortcuts instead of explicit from/to:

engel-semantic-layer validate \
  --model-path examples/model \
  --check-compilable \
  --period "last 12 months"

Write a full validation report artifact for CI (includes warning summary, compile failures, and per-metric validation statuses):

engel-semantic-layer validate \
  --model-path examples/model \
  --check-compilable \
  --report /tmp/semantic-validate-report.json

Fail on semantic warnings:

engel-semantic-layer validate \
  --model-path examples/model \
  --warnings-as-errors

Validate with strict column linting for all metrics:

engel-semantic-layer validate \
  --model-path examples/model \
  --json \
  --strict-column-lint \
  --column-registry /tmp/registry.json

Fail on potential fanout-risk relationship traversals during compile checks:

engel-semantic-layer validate \
  --model-path examples/model \
  --check-compilable \
  --strict-relationships

List metrics:

engel-semantic-layer metrics --model-path examples/model

Machine-readable list + filters:

engel-semantic-layer metrics \
  --model-path examples/model \
  --format json \
  --module fact_records \
  --calculation sum

Generate a browser-friendly HTML metrics catalog:

Installed CLI:

engel-semantic-layer catalog \
  --model-path examples/model \
  --output /tmp/metrics-catalog.html \
  --open

From source during local development:

uv run engel-semantic-layer catalog \
  --model-path examples/model \
  --output /tmp/metrics-catalog.html \
  --open

--model-path accepts either:

• a directory of YAML files (for example examples/model), or
• a single semantic layer file (for example examples/semantic_layer.yml)

What the catalog includes:

• search
• module / calculation / category filters
• grouped metric list
• metric detail pane
• expandable sample compiled SQL preview
• joins overview
• links back to source YAML files

Notes:

• the generated HTML is self-contained
• --open opens the generated file in your default browser
• source links are local file:// links, so they work on the machine that generated the catalog

Inspect module join graph:

engel-semantic-layer joins --model-path examples/model --format json

Compile SQL from a metric + request payload:

engel-semantic-layer compile \
  --model-path examples/model \
  --metric-id metric_event_count \
  --request /tmp/request.json \
  --format sql

Compile a cross-module derived ratio from single-file semantic layer:

engel-semantic-layer compile \
  --model-path examples/semantic_layer.yml \
  --metric-id ratio_primary_per_entity \
  --request /tmp/request.json \
  --period "last 12 months" \
  --format sql

Get compiler explain output (resolved metadata + source files):

engel-semantic-layer compile \
  --model-path examples/model \
  --metric-id metric_event_count \
  --request /tmp/request.json \
  --format explain

Validate request compatibility without returning SQL:

engel-semantic-layer compile \
  --model-path examples/model \
  --metric-id metric_event_count \
  --request /tmp/request.json \
  --dry-run-validate

Use period shortcuts in compile:

engel-semantic-layer compile \
  --model-path examples/model \
  --metric-id metric_event_distinct_count \
  --request /tmp/request.json \
  --period "Q1 2025"

Supported period formats:

• last x years
• last x months
• last x quarters
• last x weeks
• last x days
• current year (YTD)

last x weeks returns full Monday-Sunday week buckets ending with the current week. Example: last 52 weeks on 2026-02-15 resolves to 2025-02-17 through 2026-02-15.

last x days uses an inclusive lookback window: from today - x days through today. Example: last 30 days returns 31 daily buckets when today is included, and 30 when excludeToday is enabled.

• current month (MTD)
• QX YYYY (e.g. Q2 2025)
• YYYY (e.g. 2025)
• MM-YYYY (e.g. 02-2025)

Time comparison options in request payload (timeComparison):

• Percentage comparisons: YoY, MoM
• Comparison values: Last Year, Last Month
• Daily-only percentage comparison: YoY (Match Weekday)

YoY (Match Weekday) requires timeGrain: daily and compares to 364 days prior. With timeGrain: none, comparisons are done as full-window totals (e.g. YTD vs last YTD).

Target comparison options in request payload:

• targetSeries (e.g. budget_current, stretch_target)
• targetComparisonMode: both (default), value, percentage

Target output matrix:

targetComparisonMode	target_value	target_comparison_percentage
both	✅	✅
value	✅	❌
percentage	❌	✅

Example request snippets:

{ "targetSeries": "budget_current", "targetComparisonMode": "both" }

{ "targetSeries": "budget_current", "targetComparisonMode": "value" }

{ "targetSeries": "budget_current", "targetComparisonMode": "percentage" }

Period handling options in request payload:

• excludeOpenPeriod (true|false)
• excludeToday (true|false)

When excludeOpenPeriod is true, the compiler excludes the currently open period bucket for the selected timeGrain (e.g. current month for monthly, current day for daily).

When excludeToday is true, the compiler clips toDate by one day before other period logic.

Cumulative options in request payload:

• cumulativeMode: mtd, ytd, rolling_days
• rollingDays: positive integer (required only for rolling_days)

Validation rules:

• mtd requires timeGrain: daily
• rolling_days requires timeGrain: daily
• ytd works with daily/weekly/monthly/quarterly/yearly grains
• cumulative modes require a time grain (not none)
• cumulative + targets requires targetComparisonMode to include values (value or both)

Output columns for cumulative mode:

• metric_base (original bucket metric)
• metric (cumulative metric)
• if targetSeries is set: target_value_base + cumulative target_value

When targetSeries is provided, the compiler joins aggregated targets from demo-project-123456.analytics_prod.fct_targets_monthly by metric identifier, selected date range, and breakdown dimensions (if any), and returns target columns based on targetComparisonMode (both by default):

• target_value
• target_comparison_percentage

For cross-module derived-ratio metrics, target comparison is built from the component targets instead of a dedicated ratio target row:

• numerator target uses the numerator metric's target_name (or identifier)
• denominator target uses the denominator metric's target_name (or identifier)
• returned target_value is numerator_target / denominator_target

This means ratio targets stay dimension-safe as long as the component targets are additive, so you do not need weighted target rows for every breakdown combination.

Target source can be overridden in CLI with:

• --target-project
• --target-schema
• --target-table

With --strict-column-lint, target columns used for series/metric/time/value, breakdowns, and dimension filters are validated against the column registry.

End-to-end request example (comparison + target + cumulative + period clipping):

{
  "fromDate": "2026-01-01T00:00:00Z",
  "toDate": "2026-12-31T23:59:59Z",
  "timeGrain": "monthly",
  "timeComparison": "YoY",
  "targetSeries": "budget_current",
  "targetComparisonMode": "both",
  "cumulativeMode": "ytd",
  "excludeOpenPeriod": true,
  "excludeToday": true,
  "breakdownDimensionIds": ["segment_group"],
  "filters": [
    {
      "dimensionId": "segment_group",
      "filterValues": ["SegmentA"]
    }
  ]
}

You can also pipe request JSON through stdin:

echo '{"fromDate":"2026-01-01T00:00:00Z","toDate":"2026-01-31T23:59:59Z"}' | \
  engel-semantic-layer compile \
    --model-path examples/model \
    --metric-id metric_event_distinct_count \
    --request -

Compile with strict column linting:

engel-semantic-layer compile \
  --model-path examples/model \
  --metric-id metric_actual_secondary \
  --request /tmp/request.json \
  --strict-column-lint \
  --column-registry /tmp/registry.json

Compile with strict relationship checks (fails on potential fanout-risk traversals):

engel-semantic-layer compile \
  --model-path examples/model \
  --metric-id metric_actual_secondary \
  --request /tmp/request.json \
  --strict-relationships

Optional strict column linting

You can enforce table+column existence checks during compile by providing a column registry.

from engel_semantic_layer import ColumnRegistry, QueryRequest, SemanticLayer

registry = ColumnRegistry.from_dict(
    {
        "analytics_wide": {
            "wide_metric_count_a": ["recorded_at", "fulfilled_at", "value_secondary_amount", "status"]
        }
    }
)

layer = SemanticLayer.from_path(
    "examples/model",
    strict_column_lint=True,
    column_registry=registry,
)

sql = layer.compile_sql(
    "metric_actual_secondary",
    QueryRequest(from_date="2026-01-01T00:00:00Z", to_date="2026-01-31T23:59:59Z"),
)

You can also construct the registry from INFORMATION_SCHEMA.COLUMNS rows using ColumnRegistry.from_information_schema_rows(...).

CLI helper:

engel-semantic-layer registry from-information-schema \
  --input /tmp/information_schema_rows.json \
  --output /tmp/registry.json

Input supports both:

• JSON array ([ {...}, {...} ])
• JSONL (one JSON object per line)

Testing

The test suite includes SQL snapshot tests for representative metrics. Run:

uv run --extra dev pytest -q

If SQL output intentionally changes, update snapshot files in tests/snapshots/.

CLI exit codes:

• 0 success
• 2 validation/parse/compile input error

For machine-readable failures, use --json-errors:

engel-semantic-layer --json-errors compile ...

Notes

• This compiler focuses on BigQuery SQL generation.
• Output aims to be Engel-like SQL, but this is an independent implementation.
• Strict validation is enabled for basics: duplicate IDs, unsupported operators/calculations, invalid metric field combinations, and disallowed breakdown/filter dimensions.
• Custom SQL metric expressions are normalized for BigQuery string literals (double-quoted strings are converted to single-quoted literals).
• Custom SQL reference linting is enabled: dotted references in custom expressions are validated and unknown table references fail at compile time.
• Query compilation guards against invalid breakdown requests (max 2 dimensions, no duplicates, no alias collisions).
• Missing join-path errors are actionable and include metric + module context plus reference sources (which fields caused the dependency).