sl-capture

# 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. Skip: - Simple `SELECT * LIMIT 10` previews. - Trivial `COUNT(*)` on one table with no business filtering. - One-off ad-hoc explorations unlikely to repeat. - Equivalent measures that already exist (cite the existing one as `source.measure_name`). When in doubt, capture. Measures are easy to remove but impossible to recover from a lost conversation. ## Generalization rules The SL must stay small and general over time. Before adding a measure, decide whether it belongs as a generic pattern or a specific constant. **Prefer one generic measure with query-time filters over N hardcoded variants.** Anti-pattern: ```yaml - name: revenue_us_region expr: sum(case when region = 'US' then amount end) - name: revenue_eu_region expr: sum(case when region = 'EU' then amount end) ``` Preferred: ```yaml - name: total_revenue expr: sum(amount) ``` Callers filter `region = 'US'` at query time. **Bake constants in only when the filter has named business meaning that won't change** (`enterprise_arr` for a contractually defined tier), cannot be expressed via the source's dimensions, or comes from a regulated/fixed list. **Time anchors and value lists belong in callers' filters, not in measure expressions or source SQL.** - Anti-pattern (date anchor inlined): `expr: count(distinct case when transaction_date >= '2026-04-12' then customer_id end)` - the date will need editing every time the question shifts, and every reader has to discover it. - Anti-pattern (value list inlined in source SQL): `WHERE product_category_1 IN ('Testosterone', 'Weight Loss', …)` - locks the source to today's catalog and blocks callers from broadening or narrowing. - Preferred: a generic measure (`count(distinct customer_id)`) plus either a named segment that captures the *meaning* of the anchor (`gh_new_products_since_launch`) or a query-time filter. Callers compose; the source stays small. - A date is durable to bake in only when it represents a regulatory cutover, a contractually fixed boundary, or a one-time event that reshapes how the source itself is read. **If you create a segment whose expr matches a measure's filter, the measure MUST reference the segment via `segments: [segment_name]` rather than re-inlining the predicate.** This is the canonical pattern even with a single measure - duplicating the predicate inline defeats the purpose of naming it. Anti-pattern: ```yaml segments: - name: engaged_subscriber expr: "is_paid = true AND <date-window-90-days-on-transaction_date>" measures: - name: engaged_subscriber_count expr: "count(distinct case when is_paid = true and transaction_date >= current_date - interval '90 day' then admin_user_id end)" ``` Preferred: ```yaml segments: - name: engaged_subscriber expr: "is_paid = true AND <date-window-90-days-on-transaction_date>" measures: - name: engaged_subscriber_count expr: "count(distinct admin_user_id)" segments: [engaged_subscriber] ``` **Use computed dimensions for derived categories.** A flag like `is_power_user` belongs on `columns[]` with `expr`, not inlined into every measure. **Extract repeated filter bundles into named segments.** If the same predicate appears on multiple measures of the same source, lift it to a `segments[]` entry and have each measure reference it. One edit updates every measure that depends on it. **Never write a standalone file on a manifest-backed name.** If `sl_discover({ query: "<table-or-source-name>" })` finds an existing schema for that name, you MUST write an overlay. A standalone with `sql:` or `table:` on a manifest-backed name clobbers the inherited columns and joins; `sl_write_source` and `sl_validate` both reject this shape with a clear fix hint. Always run `sl_discover` before your first write on any existing name. Overlay before/after examples: ```yaml # Wrong: patches an inherited manifest column through columns: name: fct_orders columns: - name: status descriptions: user: "Order lifecycle status." ``` ```yaml # Right: patch inherited columns with column_overrides: name: fct_orders column_overrides: - name: status descriptions: user: "Order lifecycle status." columns: - name: is_large_order type: boolean expr: "amount > 1000" ``` Overlay YAML may include `measures:`, `segments:`, `descriptions:`, `joins:`, `disable_joins:`, `exclude_columns:`, `column_overrides:`, and computed-only `columns:` entries with `expr` and `type`. Do not include `sql:`, `table:`, `grain:`, or base-table `columns:`. **Prefer overlay decomposition over standalone SQL sources.** Before reaching for `source_type: sql`, check whether the metric decomposes into measures on existing overlays (including cross-source derived measures). Use `source_type: sql` only when: - The metric requires per-user/per-entity derivation that cannot be expressed as a single `expr` (e.g., `EXISTS` over a time-windowed subset), OR - The metric requires multi-step CTEs whose intermediate grain is not a column in any existing source. When an `sql` source is unavoidable, note in its `descriptions` map which SL gap forced the choice so it can be retired once the primitive ships. It must target a name NOT in the manifest - pick a distinct one (e.g. `mrr_waterfall_rollup`, not `fct_orders`). ## Slim standalone sources via `inherits_columns_from` When a standalone SQL source filters or projects from a single manifest-backed base table (the common pattern for derived views like `aav_consignments` over `MARTS.CONSIGNMENTS`), set `inherits_columns_from:` to the base table's manifest key and list only column **names** in `columns:`. Compose-time enrichment fills `type`, `descriptions`, and `role` from the matching manifest column. Discover the manifest key with `sl_discover` - pass the bare name (`CONSIGNMENTS`), the fully-qualified path (`ANALYTICS.MARTS.CONSIGNMENTS`), or any suffix; the tool resolves all forms and prints the canonical key in its output. ```yaml name: aav_consignments descriptions: user: AAV consignments - filtered view of MARTS.CONSIGNMENTS for the auto-auction-vaulting channel. source_type: sql sql: | SELECT CONSIGNED_ITEM_ID, CASH_ADV_AMOUNT, ALT_VALUE_COMBINED, my_derived_flag FROM MARTS.CONSIGNMENTS WHERE IS_AUTO_AUCTION_VAULTING_SUBMISSION = TRUE AND IS_CARD_SHOW_SUBMISSION = FALSE AND CONSIGNMENT_CANCELED_FLAG = FALSE inherits_columns_from: CONSIGNMENTS grain: [CONSIGNED_ITEM_ID] columns: - { name: CONSIGNED_ITEM_ID } # type/descriptions inherited from manifest - { name: CASH_ADV_AMOUNT } - { name: ALT_VALUE_COMBINED } - { name: my_derived_flag, type: boolean, expr: "CASH_ADV_AMOUNT > 0", descriptions: { user: "Computed locally - has any cash advance." } } measures: - name: total_cash_advance expr: sum(CASH_ADV_AMOUNT) ``` Rules: - Inheritance fills only **blank** fields. If you set a `description` locally, it wins - useful when the base description is misleading in the filtered view. - A column not in the manifest (a derived/aliased column, or one from a different table in a `JOIN`) needs its own `type` and `description` declared. - If `inherits_columns_from` doesn't resolve, the source still loads, but every column without a type triggers a validator error on the warehouse probe - `sl_discover` first to confirm the key. - Don't use `inherits_columns_from` for sources backed by `table:` (those should be overlays - see the rule against shadowing the manifest above). ## Refinement - replace, don't append When the user corrects a prior answer, the existing measure is wrong by the user's own standard. Replace it, don't add a parallel measure. Signals that the current turn is a refinement: - "no, I meant...", "actually use X", "exclude Y", "wait, by X I mean Z". - Pushback on a prior result ("that's wrong because...", "this should be higher"). - Redefinition of a term used in an existing measure. Distinguishing question: *would the prior measure still be correct for someone else asking the prior question?* If no → replace. If yes → add. ## Edit SL vs document in wiki If the user explicitly names an SL artifact and asks to change it, the primary action is always an SL tool call. Examples: - "edit the source", "edit the YAML", "edit `fct_intakes.yaml`" → `sl_edit_source` or `sl_write_source`. - "refine the measure", "change the filter on `active_users`", "fix the expr", "add `is_test = false`" → `sl_edit_source` on the source that owns the measure. - "don't create a new one, update the existing" → `sl_edit_source` (never `sl_write_source` with a new source name; never `wiki_write` as the only action). A wiki update may ALSO make sense in the same turn (owner note, lineage, caveat), but it is never a substitute for editing the YAML when the user's request is about changing the measure/source definition itself. Wiki-only is correct when the user is documenting *about* the measure (definition in business terms, owner, policy, glossary, examples of when to use it) without changing its SQL expression or filters. Before sl_write_source, call entity_details on the target table to confirm column names and types match the YAML being written. ## Identifier Verification Protocol Before writing a wiki page or SL source on any topic: 1. `discover_data({query: "<topic>"})` - see what wikis, SL sources, and raw tables already exist. Prefer updating existing pages over creating new ones. Before emitting any `schema.table` or `schema.table.column` into a wiki body, SL source, `tables:` frontmatter, `sl_refs`, or `emit_unmapped_fallback`: 2. `entity_details({connectionId, targets: [{display: "<identifier>"}]})` - confirm the identifier resolves; inspect native types, FK/PK, and sampleValues. 3. For literal values from the source, such as status codes or plan tiers, check whether they appear in `entity_details` sampleValues for the relevant column. If sampleValues is short or the sample may have missed real values, run a `sql_execution` probe with the same warehouse connection id: `sql_execution({connectionId, sql: "SELECT DISTINCT <col> FROM <ref> LIMIT 50"})`. 4. If the candidate identifier still does not resolve, do one of: - Use `sql_execution({connectionId, sql: "SELECT 1 FROM <ref> LIMIT 0"})`. If it errors, the identifier is fictional. - Wrap the identifier in `[unverified - from <rawPath>]` in the wiki body, citing the exact raw path that mentioned it. - When recording `emit_unmapped_fallback` with `no_physical_table`, include the failing probe error in `clarification`. 5. Never copy `<schema>.<table>` placeholder strings from these instructions into output. ## Tool sequence 1. `sl_discover` - see what source files exist. 2. `sl_discover({ query: "<table-or-source-name>" })` - **REQUIRED before the first write on any name**. Shows columns/joins/grain from the manifest. If the call returns a schema, you MUST write an overlay, not a standalone. Skipping this is the #1 cause of accidentally shadowing the manifest. 3. `sl_read_source({ connectionId, sourceName })` - read the raw YAML before editing. 4. For modifications: `sl_edit_source({ connectionId, sourceName, yaml_edits: [{ oldText, newText, reason }] })` with exact-string replacements. `oldText` must match exactly and be unique in the file. 5. For new sources or full rewrites: `sl_write_source({ connectionId, sourceName, source })` with the full structured source definition. 6. For join discovery: use `sql_execution({connectionId: "warehouse", sql: "SELECT count(*) FROM public.orders o JOIN public.customers c ON c.id = o.customer_id LIMIT 20"})` with the target warehouse connection id and dialect-correct table names to verify the join key exists in both tables and assess cardinality before declaring the join. 7. Cross-reference knowledge: author the edge once on the **wiki** side via `sl_refs: [source_name]` in the page's front-matter. The reverse edge (wiki pages that cite an SL source) is derived automatically by the reconciler - do not add a `knowledge_refs:` field to SL YAMLs. 8. `sl_validate` - run after writing or editing to surface schema issues, duplicate measure names, and cross-source validation errors. Read-only; the writes are already committed (the squash-at-end flow will collapse them into one commit). ## Editing patterns - **`sl_edit_source`** is the workhorse for additive changes: add a measure, add a join, tweak a description, replace a filter. Cheap, targeted, preserves the rest of the file. - **`sl_write_source`** is for brand-new sources or when the entire file needs restructuring. It overwrites the file completely. - Do NOT modify existing measures or their descriptions unless the current turn explicitly corrects them. - During bundle/external ingest, include `rawPaths` on every `sl_write_source`/`sl_edit_source` call with only the raw files that directly support the SL action. ## Worked example - additive overlay Conversation: - User: "What was the average order value last quarter?" - Assistant fell back to SQL: `SELECT AVG(amount) FROM orders WHERE order_date >= ...` Existing index: `orders [measures=0, joins=0] - candidate for enrichment`. ``` sl_discover() → orders.yaml does not exist yet sl_discover({ query: "orders" }) → see grain, columns, no current overlay sl_write_source({ connectionId: "warehouse", sourceName: "orders", source: { name: "orders", measures: [{ name: "avg_order_value", expr: "avg(amount)", description: "Mean order transaction amount - filter by product_category at query time" }] } }) sl_validate({ connectionId: "warehouse" }) → clean ``` The overlay only contains `name` and `measures` - no columns, grain, or table. Those are inherited from the manifest. ## Worked example - refinement (replace) Prior turn: - [user] "How many active users do we have per region?" - [assistant] "… used `count(*) filter: last_login_at > now() - interval '30 days'`" Current user: "Wait, by 'active' I mean users who have placed an order in the last 30 days, not just logged in." The existing `users.active_count` measure is wrong by the new definition. ``` sl_read_source({ connectionId: "warehouse", sourceName: "users" }) → see the wrong measure sl_edit_source({ connectionId: "warehouse", sourceName: "users", yaml_edits: [{ oldText: " - name: active_count\n expr: \"count(*)\"\n filter: \"last_login_at > now() - interval '30 days'\"\n description: Users who logged in within the last 30 days", newText: " - name: active_count\n expr: \"count(distinct case when last_order_at > now() - interval '30 days' then user_id end)\"\n description: Users with at least one order in the last 30 days" }] }) sl_validate({ connectionId: "warehouse" }) ``` If you only added a new measure, the old incorrect `active_count` would stay and future queries would keep answering the wrong question. ## Worked example - new join