metabase_ingest

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:

1. entity_details({connectionId, targets: [{display: "<identifier>"}]}) -

confirm the identifier resolves; inspect native types, FK/PK, and sampleValues.

1. 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"}).

1. If the candidate identifier still does not resolve, do one of:
2. 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.

1. 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:
5. Simple aggregation on a table that already has a source → sl_edit_source to add a measure.
6. Join between tables that should be linked in the SL graph → sl_edit_source to add a join.
7. Complex derived SQL (CTEs, multi-layer aggregation, scoring models) → sl_write_source with source_type: sql. When the SQL projects/filters from a single manifest-backed base table, set inherits_columns_from: <manifest_key> so columns inherit type and description from the manifest - see sl_capture skill for the slim form. Use sl_discover to discover the manifest key from the table reference in the SQL (it accepts MARTS.CONSIGNMENTS, ANALYTICS.MARTS.CONSIGNMENTS, or CONSIGNMENTS).
8. New base table not yet in the semantic layer → sl_write_source with source_type: table.
9. Trivial query (SELECT , simple COUNT() with no business logic) → do nothing; the runner will record this card as action_type='skipped'.
10. Duplicate of an existing measure → same as trivial; do nothing for this card.

Manifest-only names need an overlay first. If sl_discover shows a source name with Type: table but sl_read_source returns "Source not found", the source lives only in the schema manifest (no standalone overlay yet). sl_edit_source cannot edit manifest-only names, and a full standalone sl_write_source for that name would shadow manifest columns and joins. Bootstrap an overlay with sl_write_source using the overlay shape:

name: <SOURCE_NAME>
measures:
  - name: <measure_name>
    expr: "<expression>"

Overlay shape: name: plus any of measures:, segments:, descriptions:, joins:, disable_joins:, exclude_columns:, column_overrides:, or computed-only columns: entries with expr + type. Never include sql:, table:, grain:, or base-table columns: on a manifest-backed name — those would shadow the manifest's schema and drop its joins. Use column_overrides: for inherited column descriptions. Overlay joins: are merged additively with the manifest's joins (deduped by to + on); use disable_joins: ["<on-clause>"] to suppress a specific manifest join. After the overlay exists, use sl_edit_source for further tweaks. See sl_capture skill for the canonical overlay rule.

Join discovery: When your card's SQL references warehouse tables (e.g. in FROM or JOIN clauses), call sl_discover({ query: '<table>' }) before writing. The matching manifest entry's name is the value you use in joins: [- to: <name>] only when the card output exposes a local key that matches the target source grain (for example account_id = mart_account_segments.account_id). Do not declare a KTX join just because the card SQL joins that table i