/metabase_ingest

name: metabase_ingest
description: Convert Metabase questions, models, and metrics into ktx Semantic Layer source definitions. Covers result-metadata to KSL column type mapping, FK/PK detection, near-duplicate deduplication, pre-aggregation decomposition, join-graph connectivity, and how to react to priorProvenance from earlier ingest syncs. Load when the WorkUnit contains `cards/<id>.json` files under a Metabase bundle.
callers: [memory_agent]

Metabase to ktx Semantic Layer

Each WorkUnit represents one Metabase collection's cards for one Metabase database (mapped to exactly one ktx connection). Every `cards/<id>.json` file carries the resolved SQL, result_metadata, card type, collection path, and referenced-card ids. The WU's `sync-config.json` tells you which sync mode is active and which selections apply. `databases/<id>.json` tells you the target ktx connection.

Context format

Each card JSON looks like:

{
  "metabaseId": 7,
  "name": "Daily orders",
  "description": "Orders by day",
  "type": "model",
  "databaseId": 42,
  "collectionId": 5,
  "resolvedSql": "SELECT ...",
  "templateTags": [{"name": "ref", "type": "card", "cardReference": 10}],
  "resultMetadata": [
    {"name": "day", "base_type": "type/DateTime", "semantic_type": "type/CreationTimestamp"},
    {"name": "order_count", "base_type": "type/Integer"}
  ],
  "collectionPath": ["Data", "Orders Team"],
  "referencedCardIds": [10]
}

Use `resultMetadata` to:

• Map `base_type` to KSL column type: `type/Integer`, `type/Float`, `type/Decimal`, `type/BigInteger` → `number`; `type/Text`, `type/TextLike` → `string`; `type/DateTime`, `type/Date`, `type/DateTimeWithTZ` → `time`; `type/Boolean` → `boolean`.
• Identify grain candidates: columns with `semantic_type: type/PK`.
• Identify join candidates: columns with `semantic_type: type/FK` plus `fk_target_field_id`.
• Identify time columns: `semantic_type: type/CreationTimestamp` or `type/UpdatedTimestamp` → set `role: time`.
• Use `display_name` for measure descriptions when available.

Additional card metadata

• `parameters`: list of card-level parameters with widget types and defaults. When SQL resolution fell back to unresolved SQL, use this to drive Step A of the SQL-translation workflow (drop optional clauses): knowing each `{{ var }}` is `type: "date/range"` vs `type: "category"` tells you what kind of clause it is.
• `resultMetadata[i].field_ref`: Metabase's canonical reference to the source warehouse field. Shape `["field", <field_id>, <options>]`. When this is set, the column maps directly to a warehouse field, which is useful for declaring joins from FK metadata without re-parsing SQL.
• `lastRunAt`: ISO timestamp of the card's last execution. If null or very old, the card may be dead; prefer skipping over creating a source.
• `dashboardCount`: number of dashboards referencing the card. Cards with `dashboardCount: 0` and a stale `lastRunAt` are strong skip signals.

Before writing a wiki page derived from a Metabase question SQL, verify each schema.table.column mentioned with entity_details.

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.

Decision tree

For each card: 1. Analyze `resolvedSql` + `resultMetadata`: identify base tables, aggregations, joins, filters, column types. 2. **REQUIRED before any write**: call `sl_discover` for every candidate target source name. The response tells you whether the name is manifest-backed (`Type: table` or `Type: sql`). For manifest-backed names you MUST use the overlay shape (`name:` plus overlay fields such as `measures:`, `segments:`, `descriptions:`, `joins:`, `disable_joins:`, `column_overrides:`, and computed-only `columns:` entries with `expr` + `type`; no `sql:`, `table:`, `grain:`, or base-table `columns:`); the tool will reject a standalone write and you'll have wasted the call. If `sl_discover` returns nothing for the name, you can write a standalone source. Also call `sl_read_source` on existing sources you intend to extend so you don't duplicate measures. 3. Include `rawPaths: ["cards/<id>.json"]` on every `sl_write_source`, `sl_edit_source`, and `wiki_write` call. If one artifact generalizes multiple near-duplicate cards, include each contributing card path and no unrelated cards. 4. Decide:

• Simple aggregation on a table that already has a source → `sl_edit_source` to add a measure.
• Join between tables that should be linked in the SL graph → `sl_edit_source` to add a join.
• Complex derived SQL (CTEs, multi-layer aggregation, scoring models) → `sl_writ