{"id":"lookml-ingest","name":"lookml_ingest","summary":"LookMLのビュー/モデル/探索をktxセマンティックレイヤーのソースにマッピングします。LookMLからktxへのプリミティブテーブル、出所タグ付け、そして3つの作業例(オーバーレイ、derived_tableからのスタンドアロン、sql_always_whereとのスタンドアロン)をカバーしています。","body":"# LookML to ktx Semantic Layer\n\nLookML views map to SL sources, `measure:` to measures, `explore: { join: }` to the join graph. This skill lays out the mapping and the three capture shapes.\n\n## Mapping table\n\n| LookML | ktx form | Notes |\n|---|---|---|\n| `view: X { sql_table_name: …; measure:/dimension:/join: }` | **Overlay** named `X` with `measures`, computed-only `columns`, `column_overrides`, `joins`, `segments` | Manifest-backed; inherit grain/columns |\n| `view: X { derived_table: { sql: … } }` | **Standalone** with top-level `sql:`, explicit `grain:` + `columns:` | No manifest entry exists |\n| `view: X { sql_always_where: <p> }` | **Standalone** with `sql: SELECT * FROM <base> WHERE <p>` | Enforcement, not opt-in |\n| `explore: { join: Y { sql_on: …; relationship: … } }` | `joins:` entry `{ to: Y, on: \"<local> = Y.<col>\", relationship: … }` | On the overlay or standalone |\n| `conditionally_filter` / `always_filter` | `segments: [{ name, expr }]` | Callers reference by name |\n| Manifest entry | `_schema/*.yaml` | **Never edit** - auto-imported |\n\nType map: `date`/`datetime`/`timestamp` → `time`; `yesno` → `boolean`; `number` → `number`; `string` → `string`. Ignore `drill_fields:` (UI only).\n\n## Decision rules\n\nLookML writes target the run connection directly. Unlike Looker runtime ingestion, the LookML adapter is configured on the warehouse ktx connection, so do not look for `targetWarehouseConnectionId` and do not route through a mapping array.\n\nBefore any SL write, inspect the WorkUnit notes.\n\nIf notes contain:\n\n```text\n[LOOKML SL WRITES DISALLOWED]\nreason: lookml_connection_mismatch\n...\n[/LOOKML SL WRITES DISALLOWED]\n```\n\nthis is a hard gate. The model's declared Looker `connection:` does not match the warehouse connection's configured `expectedLookerConnectionName`. Continue wiki extraction and context candidates. Do not call `sl_write_source` or `sl_edit_source` for that WorkUnit. The runner also removes those write tools for this WorkUnit; treat the missing tools as expected. Preserve the mismatch reason in any `emit_unmapped_fallback` you create.\n\nWhen SL is allowed:\n\n- **Overlay** when the view is a thin wrapper over a manifest table (`sql_table_name:` matches a manifest entry). Do not repeat base columns or grain.\n- **Standalone** when the view uses `derived_table:` or `sql_always_where:`. `sl_write_source` rejects overlays whose name has no manifest entry; that error points here.\n- **Skip** a view with only `view:`, `sql_table_name:`, and bare `dimension:` entries (no `measure:`, `description:`, `derived_table:`, `sql_always_where:`, `join:`). The pre-filter already short-circuits those.\n- Include `rawPaths` on every `sl_write_source`/`sl_edit_source` call with the exact LookML raw file(s) that support the action.\n\n## Preflight: never guess column names\n\nLookML's `dimension_group: date { type: time; timeframes: [raw, date, week, month] }` expands at Looker-render time into `${view.date_raw}`, `${view.date_date}`, `${view.date_week}`, and so on. **These are NOT physical warehouse columns.** The physical column is whatever the group's `sql:` clause references (e.g. `${TABLE}.date` → column `date`).\n\nA prior replay hallucinated `date_date`, `date_week` into `sql:`, `columns:`, and `grain:` across 4+ standalones; every measure on each affected source returned `400 Unrecognized name: date_date` at query time. Preventable.\n\nVerify each sql_table_name from the LookML view with entity_details before\nmapping to an SL source.\n\n## Identifier Verification Protocol\n\nBefore writing a wiki page or SL source on any topic:\n\n1. `discover_data({query: \"<topic>\"})` - see what wikis, SL sources, and raw\n   tables already exist. Prefer updating existing pages over creating new ones.\n\nBefore emitting any `schema.table` or `schema.table.column` into a wiki body,\nSL source, `tables:` frontmatter, `sl_refs`, or `emit_unmapped_fallback`:\n\n2. `entity_details({connectionId, targets: [{display: \"<identifier>\"}]})` -\n   confirm the identifier resolves; inspect native types, FK/PK, and\n   sampleValues.\n3. For literal values from the source, such as status codes or plan tiers,\n   check whether they appear in `entity_details` sampleValues for the relevant\n   column. If sampleValues is short or the sample may have missed real values,\n   run a `sql_execution` probe with the same warehouse connection id:\n   `sql_execution({connectionId, sql: \"SELECT DISTINCT <col> FROM <ref> LIMIT 50\"})`.\n4. If the candidate identifier still does not resolve, do one of:\n   - Use `sql_execution({connectionId, sql: \"SELECT 1 FROM <ref> LIMIT 0\"})`.\n     If it errors, the identifier is fictional.\n   - Wrap the identifier in `[unverified - from <rawPath>]` in the wiki body,\n     citing the exact raw path that mentioned it.\n   - When recording `emit_unmapped_fallback` with `no_physical_table`, include\n     the failing probe error in `clarification`.\n5. Never copy `<schema>.<table>` placeholder strings from these instructions\n   into output.\n\n**Required flow before writing any overlay or standalone**:\n\n1. Call `sl_discover({ query: \"<tableName>\" })` for each base table you're about to touch. That returns the real columns.\n2. If the table isn't in the manifest, use the warehouse `connectionId`\n   returned by `discover_data` or the target connection chosen from\n   `sl_discover`, then call a dialect-appropriate SQL probe with that\n   connection id, for example:\n   `sql_execution({connectionId: \"warehouse\", sql: \"SELECT 1 FROM analytics.orders LIMIT 0\"})`.\n   Replace `warehouse`, `analytics`, and `orders` with the verified connection,\n   schema or dataset, and table from the WorkUnit evidence.\n3. Use only those names in `sql:`, `columns:`, and `grain:`. Map each `dimension_group` to ONE `{ name: <physical_col>, type: time, role: time }` entry - never one per timeframe.\n\n| LookML input | ktx `columns:` entry |\n|---|---|\n| `dimension_group: month { type: time; timeframes: [month]; sql: ${TABLE}.month_date ;; }` | `{ name: month_date, type: time, role: time }` |\n| `dimension_group: date { type: time; timeframes: [raw, date, week, month]; sql: ${TABLE}.date ;; }` | `{ name: date, type: time, role: time }` - single entry, NOT `date_raw`/`date_date`/`date_week` |\n\n**After every `sl_write_source`**: call `sl_validate`. It runs `SELECT * FROM (<your sql:>) LIMIT 0` against the connection. If a column name was invented, the warehouse's `Unrecognized name: …` error comes back verbatim. Treat that as a hard failure - re-read the real columns with `sl_discover` and rewrite.\n\n## Provenance markers\n\nWhen a wiki mixes LookML source prose with `sl_discover` output, tag sections:\n\n```markdown\n<!-- from: lookml -->\nCustomers fan out many-to-one into `accounts` via `account_id`.\n<!-- /from -->\n<!-- from: bq_schema -->\n`customers.admin_user_id` is nullable - orphan rows exist.\n<!-- /from -->\n```\n\nInvisible in most renderers; lets a future pass audit provenance.\n\n## Example 1 - overlay (thin wrapper)\n\nLookML (excerpt):\n\n```lookml\nview: fct_labs {\n  sql_table_name: analytics.fct_labs ;;\n  dimension: is_byol { type: yesno; sql: ${TABLE}.lab_type = 'byol' ;; }\n  measure: count_lab_orders { type: count; description: \"Total lab orders.\" }\n  measure: count_byol_labs { type: count; filters: [is_byol: \"yes\"] }\n}\nexplore: fct_labs {\n  join: dim_customers { sql_on: ${fct_labs.admin_user_id} = ${dim_customers.admin_user_id} ;; relationship: many_to_one }\n}\n```\n\nktx overlay at `<connId>/fct_labs.yaml`:\n\n```yaml\nname: fct_labs\ndescriptions:\n  user: \"Lab-order fact table. One row per lab order event.\"\ncolumns:\n  - name: is_byol\n    type: boolean\n    expr: \"lab_type = 'byol'\"\nmeasures:\n  - name: count_lab_orders\n    expr: count(lab_order_id)\n    description: Total lab orders.\n  - name: count_byol_labs\n    expr: count(lab_order_id)\n    filter: \"is_byol = true\"\njoins:\n  - to: dim_customers\n    on: \"admin_user_id = dim_customers.admin_user_id\"\n    relationship: many_to_one\n```\n\n## Example 2 - standalone from `derived_table`\n\n```lookml\nview: lab_results {\n  derived_table: { sql:\n    SELECT lab_order_id, admin_user_id, lab_date, biomarker, value,\n           value - LAG(value) OVER (PARTITION BY admin_user_id, biomarker ORDER BY lab_date) AS delta\n    FROM analytics.raw_lab_results WHERE status = 'final' ;; }\n  dimension: lab_order_id { primary_key: yes; type: string }\n  measure: avg_delta { type: average; sql: ${delta} ;; }\n}\n```\n\n```yaml\nname: lab_results\ndescription: \"Lab results with biomarker delta vs previous reading per user.\"\nsource_type: sql\nsql: |\n  SELECT lab_order_id, admin_user_id, lab_date, biomarker, value,\n         value - LAG(value) OVER (PARTITION BY admin_user_id, biomarker ORDER BY lab_date) AS delta\n  FROM analytics.raw_lab_results WHERE status = 'final'\ngrain: [lab_order_id]\ncolumns:\n  - { name: lab_order_id, type: string }\n  - { name: admin_user_id, type: string }\n  - { name: lab_date, type: time, role: time }\n  - { name: biomarker, type: string }\n  - { name: value, type: number }\n  - { name: delta, type: number }\nmeasures:\n  - { name: count_lab_results, expr: \"count(lab_order_id)\" }\n  - { name: avg_delta, expr: \"avg(delta)\" }\n```\n\n## Example 3 - standalone with `sql_always_where`\n\n```lookml\nview: rpt_daily_braze_email {\n  sql_table_name: analytics.fct_email_sends ;;\n  sql_always_where: ${TABLE}.channel = 'braze' AND ${TABLE}.status = 'delivered' ;;\n  dimension: send_id { primary_key: yes; type: string }\n  measure: delivered_count { type: count }\n}\n```\n\n```yaml\nname: rpt_daily_braze_email\ndescription: \"Delivered Braze email sends (enforced filter: channel='braze', status='delivered').\"\nsource_type: sql\nsql: |\n  SELECT * FROM analytics.fct_email_sends\n  WHERE channel = 'braze' AND status = 'delivered'\ngrain: [send_id]\ncolumns:\n  - { name: send_id, type: string }\n  - { name: admin_user_id, type: string }\n  - { name: sent_at, type: time, role: time }\nmeasures:\n  - { name: delivered_count, expr: \"count(send_id)\" }\n```\n\n`sql_always_where` is enforcement → wrap into the `sql:`. Don't model it as a segment (segments are opt-in) or per-measure filter (fragile, duplicated).","author":"@Kaelio","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/Kaelio/ktx/tree/main/packages/cli/src/skills/lookml_ingest","license":"Apache-2.0","category":"writing","lang":"en","tokens":2677,"stars":0,"calls30d":2,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":[]}}