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: ``json { "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 fktargetfield_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 emitunmappedfallback: 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 emitunmappedfallback with nophysicaltable, 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