Semantic Layer - Capture

This skill covers when and how to capture new patterns into the semantic layer. For schema reference and query grammar, load the sl skill first.

When the current turn produces a reusable pattern (business metric, derived view, join pattern, computed dimension), capture it so future queries can reach for it instead of rediscovering it.

SQL dialect

The user-facing prompt includes a Warehouse: line under the SL Sources index (e.g. Warehouse: BIGQUERY). All expr strings - measure expressions, segment predicates, computed-column SQL - execute on that warehouse and must use its syntax. Date arithmetic in particular varies by dialect:

• BigQuery: transaction_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY) (when the column is TIMESTAMP); event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) (when DATE).
• Postgres / Redshift: transaction_date >= current_date - interval '90 days'.
• Snowflake: transaction_date >= dateadd(day, -90, current_timestamp()).

Match the column's manifest type (type: time → TIMESTAMP/DATETIME on the warehouse) - comparing TIMESTAMP to a DATE-arithmetic result fails on BigQuery. After every sl_edit_source/sl_write_source, the inline validator runs a LIMIT 1 warehouse probe per measure and surfaces dialect mismatches; if you see an error trailer, fix the expression and retry rather than leaving the source for the post-squash gate to revert.

What's worth capturing

• Business metric aggregations (ARR, MRR, revenue, churn, retention, conversion, LTV, CAC).
• Derived calculations combining multiple signals (risk scores, health scores, composite KPIs).
• Multi-table join patterns producing a reusable analytical view.
• Computed categories or flags useful as reusable dimensions (case when num_protocols >= 3 then 'power' else 'regular' end).
• Missing joins between two sources that both exist but aren't connected in the join graph.