Custom SQL To Data Model

Custom SQL → Data Model

End-to-end flow:

1. Scan workbooks for source.kind: "sql" elements (Phase 1).
2. Scan data models to index every existing SQL or warehouse-table source (Phase 1.5).
3. Group manifest entries by normalized SQL; for each group, decide reuse an existing DM element or build a new one (Phase 1.5 → plan).
4. Build DMs only for the to-build groups (Phase 2 / 3 / 4).
5. Drive :swapSources from the plan, one workbook at a time (Phase 5).
6. Audit and repair residual [Prefix/SNAKE_CASE] formulas left behind by Sigma's auto-match (Phase 6).

The key win versus the legacy GET/mutate/PUT approach: one workbook with N SQL elements is one API call with N entries in sourceMapping, and the auto-match handles formula rewrites — except for the rough edges Phase 6 catches.

Prerequisites

Required env var: SIGMA_BASE_URL. Then pick one of two auth options:

• Client credentials (headless): set SIGMA_CLIENT_ID and SIGMA_CLIENT_SECRET (from Sigma → Administration → Developer Access). Best for automation.
• Browser login (no client ID/secret): sign in once through the browser using the sigma-api skill — eval "$(bash <repo-root>/sigma-api/scripts/browser-login.sh)". It stores a refresh token in your OS keychain, and the token scripts below redeem it headlessly (no browser round-trip on subsequent runs). SIGMA_CLIENT_ID / SIGMA_CLIENT_SECRET can stay unset.

Both options feed the same get_token.py / get-token.sh helpers below — when the client-credential env vars are absent they automatically fall back to the stored browser-login refresh token.

Default (shell-neutral, works in bash/zsh/PowerShell/cmd): mint a token with the stdlib-only Python script and let scripts/lib/sigma_rest.rb pick it up automatically from auth.json — no eval, no shell-specific syntax:

python3 scripts/get_token.py --workdir /tmp/custom-sql-run

This writes /tmp/custom-sql-run/auth.json (mode 0600). Every Ruby script in this skill checks $SIGMA_WORKDIR/auth.json (or ./auth.json) before falling back to a fresh client-credentials exchange, so point SIGMA_WORKDIR at the same directory (or run from inside it) and every subsequent ruby scripts/*.rb invocation authenticates without any shell-specific token plumbing:

export SIGMA_WORKDIR=/tmp/custom-sql-run
ruby scripts/scan-workbooks.rb

bash/zsh convenience (equivalent, but bash-only):

eval "$(bash scripts/get-token.sh)"

Tokens expire after ~1 hour. With client credentials, the Ruby scripts auto-refresh on 401 via scripts/lib/sigma_rest.rb — full-site scans on large orgs (hundreds of workbooks, sometimes >1 hour total) no longer fail mid-run. Browser-login users don't have client credentials for the in-process refresh, so re-run python3 scripts/get_token.py --workdir "$SIGMA_WORKDIR" between phases — it serves the cached keychain token or redeems the stored refresh token headlessly. Either way, if you still see Token missing or malformed, re-run python3 scripts/get_token.py --workdir "$SIGMA_WORKDIR" (or eval "$(bash scripts/get-token.sh)" in bash) manually.

Phase 1 — Scan workbooks for custom SQL

eval "$(bash scripts/get-token.sh)" && ruby scripts/scan-workbooks.rb

Reads every workbook spec in the org, finds all elements where source.kind == "sql", and writes custom-sql-manifest.json to the system temp dir (Ruby's Dir.tmpdir — /tmp on macOS/Linux, %TEMP% on Windows).

Each entry:

{
  "workbook_id":   "25e21c63-...",
  "workbook_name": "Custom Sql Test",
  "folder_id":     "fe98dc76-...",
  "element_id":    "6YSI-SjSmz",
  "element_name":  "Customer Dim SQL",
  "connection_id": "ab12cd34-...",
  "sql":           "select * from DEMO_DB.DEMO.CUSTOMER_DIM",
  "column_count":  18
}

element_id is the same value as the customSqlId consumed by :swapSources in Phase 5 — no extra lookup needed.

Review the findings and confirm with the user which workbooks to convert.

Phase 1.5 — Index existing DMs and plan dedup

Scan DMs

eval "$(bash scripts/get-token.sh)" && ruby scripts/scan-data-models.rb

Writes dm-sql-index.json to the system temp dir (Dir.tmpdir). The scanner emits one entry per existing DM element of either kind:

• kind: "sql" — indexed by normalized SQL text
• kind: "warehouse-table" — indexed by a synthetic select * from <path> string, so trivial SELECT * custom-SQL maps to the right table-backed DM

Each entry also tracks dmElementCount (total elements in that DM) so the planner can prefer focused DMs over kitchen-sink ones.

Build the swap plan

ruby scripts/plan-dedup.rb

Writes swap-plan.json to the system temp dir (Dir.tmpdir). The planner:

1. Groups manifest entries by normalized SQL (whitespace/case-collapsed, but no semantic reformatting — copy/paste duplicates collapse, semantically- equivalent rewrites do not).
2. For each group, looks up the DM index. If a candidate exists, marks the group status: "existing" and picks the best target (same connection, fewest DM elements, stable tiebreak). Otherwise marks it to-build.
3. Prints a human-readable summary:

[REUSE] group 1: 3 occurrence(s)
  SQL: select * from demo_db.demo.customer_dim
    - Custom Sql Test / Custom Sql Test SQL (6YSI-SjSmz)
    - Dedup Test Alpha / Customer Source  (el-customers)
    - Dedup Test Beta  / Customer Source  (el-customers-dup)
  -> Customer Dim / Customer Dim  (54d1f450-... / ROZzp25zn9)

[BUILD] group 2: 1 occurrence(s)
  SQL: with monthly as ( select employee_id, date_trunc('month', date) as month, …
    - Dedup Test Alpha / OT Summary (el-ot-summary)
  -> needs new DM (connection ab12cd34-..., folder fe98dc76-...)

Summary: 3 unique SQL strings → 1 reuse, 2 build. 5 total swap actions.

Confirm with the user before continuing — a [REUSE] decision is only as good as the picked candidate. If the heuristic picked the wrong DM, edit swap-plan.json (in the system temp dir) to point target.dataModelId / target.elementId at the preferred DM before Phase 5.

Phase 2 — Build the data models for to-build groups

Build one DM per to-build group in the plan. Phases 1.5 → 2 → 3 → 4 — only the to-build groups need this; reuse groups skip straight to Phase 5.

One DM per to-build group

A group is "one unique SQL across N workbook occurrences" — build a single DM with one element wrapping that SQL, then the same DM/element pair gets referenced by every occurrence in the swap call. Do not build per-workbook.

Try the converter first

For each to-build group:

mcp__sigma-data-model__convert_sql_to_sigma
  statements    = [{"name": "<group label>", "sql": "<representative sql>"}]
  connection_id = "<connection_id from manifest>"
  database      = ""
  schema        = ""

database / schema are inferred from the SQL.

What the converter produces

Aggregated SQL with GROUP BY: The warehouse-table + metrics shape only exposes GROUP BY dimensions as direct columns; aggregates land in metrics. If child workbook elements reference aggregate columns as direct columns, the DM page must use a sql source instead — build manually with the full SQL and enumerate all output columns.

Column display names — matter for Phases 5 + 6

The swap auto-matches columns between source and target by display name, case- and punctuation-insensitive (CUSTOMER_KEY ↔ Customer Key). Most columns match cleanly. The auto-match has known silent misses — see Phase 6 — but those are post-swap-repairable, so don't over-engineer at build time. Just ensure the DM column name fields are reasonable display names ("Total Hours", "OT Hours", …), not SQL aliases.

Column formula rules (for the DM itself)

Key rules:

• Inside a sql DM element, the formula prefix is always the literal string Custom SQL — never the element's own name field.
• Workbook formulas reference DM columns by display name (the column's name field), not by server-assigned column id. Display names are stable across DM PUTs; column IDs are reassigned every PUT.

def col(sql_alias, display=None):
    title = display or sql_alias.replace("_", " ").title()
    return {"id": sql_alias, "name": title, "formula": f"[Custom SQL/{sql_alias}]"}

SELECT * — converter produces no columns

When the converter returns a warehouse-table element with no columns:

1. mcp__sigma-mcp-v2__search query="<TABLE_NAME>" entityTypes=["table"] → inodeId
2. mcp__sigma-mcp-v2__describe object={"type":"table","inodeId":"<inodeId>"} → column names
3. Build columns with formula: [TABLE/<col>], name: "<Title Case>".

CTEs — discard converter output, build manually

When the converter returns path: ["CTE_NAME"], that CTE name is being used as a fake warehouse table path. Discard the converter output and build a sql element by hand with the full CTE in source.statement. Enumerate the output columns from the final SELECT.

Set folderId

Always set folderId from the plan's representative.folder_id (taken from the source workbook's own folder — guaranteed real).

Phase 3 — POST the data model

eval "$(bash scripts/get-token.sh)" && \
curl -s -X POST \
  -H "Authorization: Bearer $SIGMA_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d @/tmp/<name>-datamodel-spec.json \
  "$SIGMA_BASE_URL/v2/dataModels/spec" \
  | ruby -r yaml -r json -r date -e "
    d = YAML.safe_load(STDIN.read, permitted_classes: [Date, Time])
    if d['dataModelId']
      puts 'SUCCESS  dataModelId: ' + d['dataModelId'].to_s
    else
      puts 'ERROR: ' + d.inspect
    end
  "

Response is YAML — never pipe to jq.

After POST, fetch the spec back with Accept: application/json to learn the server-assigned element IDs:

curl -s -H "Authorization: Bearer $SIGMA_API_TOKEN" -H "Accept: application/json" \
  "$SIGMA_BASE_URL/v2/dataModels/<dataModelId>/spec" \
  | jq '.pages[].elements[] | {id, name}'

Update the plan: fill in target.dataModelId / target.elementId on every to-build entry with the new IDs.

Phase 4 — Validate the new DMs with MCP

For each newly-built DM:

mcp__sigma-mcp-v2__describe
  object = {"type": "datamodel", "dataModelId": "<dmId>"}

Then for each element listed:

mcp__sigma-mcp-v2__describe
  object = {"type": "datamodel-element", "dataModelId": "<dmId>", "elementId": "<elId>"}

mcp__sigma-mcp-v2__query
  query = { "type": "datamodel", "dataModelId": "<dmId>",
            "sql": "SELECT * FROM \"datamodel\".\"<elId>\" LIMIT 3" }

Column identifiers in the SQL are the server-assigned IDs from the describe DDL, not display names.

If anything fails, fix and re-PUT before Phase 5.

Phase 5 — Repoint workbooks via :swapSources

Endpoint: POST /v3alpha/workbooks/{workbookId}:swapSources.

The call atomically:

• flips source.kind: "sql" → "data-model" on each root SQL element,
• rewrites root column formulas from [Custom SQL/SQL_ALIAS] to [DM Element Name/Display Name],
• rewrites child element formulas that referenced the root by name.

Step 1 — Name unnamed root SQL elements first (CRITICAL)

If a root SQL element has name: null (or empty string), child elements that referenced it used the implicit prefix "Custom SQL". The swap rewrites the root's own formulas correctly but breaks child formulas to [Missing node/...] — and reversing the swap does not fix them; the "Missing node" placeholder is sticky and requires a manual spec edit to recover.

Always set a real name on each unnamed root SQL element before swapping, and update any child formulas that used the Custom SQL prefix to use the new name. Then the swap auto-rewrites cleanly.

The workbook-spec body is wrapped, not flat (verified live 2026-08). GET/POST/PUT all nest schemaVersion/elements/pages/layout under a top-level document key; name/folderId/response-only fields stay outside it, and PUT takes only {document: {...}} — see sigma-workbooks's reference/specification/schema.md. The snippet below unwraps spec['document'] before touching the flat elements array, and re-wraps just the document for the PUT body.

require 'json'
require 'tmpdir'
WB = '<workbookId>'
raw = `curl -s -H "Authorization: Bearer $SIGMA_API_TOKEN" -H "Accept: application/json" "$SIGMA_BASE_URL/v2/workbooks/#{WB}/spec"`
spec = JSON.parse(raw)
doc  = spec['document']   # schemaVersion/elements/pages/layout live here

manifest = JSON.parse(File.read(File.join(Dir.tmpdir, 'custom-sql-manifest.json')))
new_names = manifest
  .select { |m| m['workbook_id'] == WB }
  .to_h    { |m| [m['element_id'], m['element_name']] }

# Children that referenced a root we're renaming
child_parent = {}
doc['elements'].each do |el|
  parent = el.dig('source', 'elementId')
  child_parent[el['id']] = parent if parent && new_names.key?(parent)
end

doc['elements'].each do |el|
  if new_names.key?(el['id'])
    el['name'] = new_names[el['id']] if el['name'].to_s.strip.empty?
  elsif (parent = child_parent[el['id']])
    new_prefix = new_names[parent]
    (el['columns'] || []).each do |col|
      col['formula'] = col['formula']&.gsub('[Custom SQL/', "[#{new_prefix}/")
    end
  end
end

# PUT takes ONLY the document object — no name/folderId/response-only fields.
File.write(File.join(Dir.tmpdir, 'wb-pre-swap.json'), JSON.pretty_generate({ 'document' => doc }))

curl -s -X PUT \
  -H "Authorization: Bearer $SIGMA_API_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json" \
  -d @/tmp/wb-pre-swap.json \
  "$SIGMA_BASE_URL/v2/workbooks/<workbookId>/spec"

Skip this step only if every root SQL element already has a non-empty name AND no child formula references [Custom SQL/...].

Step 2 — Call :swapSources per workbook, batched

For each workbook in the plan, batch all of that workbook's swaps into a single call. The example below assumes the plan groups Alpha's two SQL elements together; the planner output gives you the mapping.

WB="<workbookId>"
curl -s -X POST \
  -H "Authorization: Bearer $SIGMA_API_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json" \
  -d '{
    "sourceMapping": [
      { "from": { "type": "custom-sql", "customSqlId": "<element_id 1>" },
        "to":   { "type": "data-model", "dataModelId": "<dmId 1>", "elementId": "<elId 1>" } },
      { "from": { "type": "custom-sql", "customSqlId": "<element_id 2>" },
        "to":   { "type": "data-model", "dataModelId": "<dmId 2>", "elementId": "<elId 2>" } }
    ]
  }' \
  "${SIGMA_BASE_URL}/v3alpha/workbooks/${WB}:swapSources"

Shell gotcha: the URL contains a : which collides with parameter expansion in zsh/bash. Always wrap the variable in braces: ${WB}:swapSources, not $WB:swapSources (bad substitution error).

A successful response is HTTP 200 with body {}.

Step 3 — Verify the swap had effect

The endpoint has two specific behaviors to guard against:

curl -s -H "Authorization: Bearer $SIGMA_API_TOKEN" \
  "${SIGMA_BASE_URL}/v2/workbooks/${WB}/sources" | jq '[.[] | .type] | unique'
# expect: ["data-model"] (or empty / no custom-sql)

Column name misalignment — columnMapping override (proactive)

The swap auto-matches columns by display name, case- and punctuation- insensitive. If you know in advance that some DM column display names won't match the custom-SQL aliases (and won't auto-resolve), pass columnMapping in the same request:

{
  "sourceMapping": [
    {
      "from": { "type": "custom-sql", "customSqlId": "..." },
      "to":   { "type": "data-model", "dataModelId": "...", "elementId": "..." },
      "columnMapping": [
        { "fromColumn": ["C_KEY"],     "toColumn": ["customer_key"] },
        { "fromColumn": ["TOTAL_REV"], "toColumn": ["lifetime_revenue"] }
      ]
    }
  ]
}

For cases you didn't anticipate, Phase 6 cleans up after the fact.

Phase 6 — Audit and repair residual SNAKE_CASE formulas

The auto-match has documented silent misses: short uppercase aliases and some token patterns survive the swap as [Prefix/SNAKE_CASE] even though the DM element's column display name is something like OT Hours. The broken formula:

• Does not error at PUT time.
• Surfaces at query time as a value-cell error: Column "[OT Summary/OT_HOURS]" does not exist.
• Cascades into child elements that referenced the workbook column by its display name ([OT Summary/OT Flag]) — the child returns the same error string as a data value, not a query failure.

The audit script walks each workbook's elements, finds formulas of the form [Prefix/UPPERCASE_TOKEN] where the local sibling column's id matches the token but its display name differs, and rewrites the suffix to the display name. Warehouse-table prefixes are left alone.

# audit specific workbooks
ruby scripts/audit-formulas.rb <workbookId> [<workbookId> ...]

# or audit every workbook touched by the current plan
ruby scripts/audit-formulas.rb --from-plan

The script reports N formula(s) repaired per workbook. Idempotent — safe to re-run.

After audit, re-validate with MCP to confirm the cells now return data, not error strings:

mcp__sigma-mcp-v2__query
  query = {"type": "workbook", "workbookId": "<wb>",
           "sql": "SELECT <cols> FROM \"workbook\".\"<elementId>\" LIMIT 3"}

Manual fallback (legacy, no :swapSources)

If :swapSources doesn't fit (e.g., a partial swap, or a column remap that's easier as a spec edit), the older GET/mutate/PUT approach still works. The cost: you keep prefix/display-name rewrite logic in sync with the DM, you must strip response-only fields, and layout edits may get rebuilt server- side. Prefer :swapSources whenever it works.

/tmp/wb-spec.yaml here is a saved GET /v2/workbooks/{id}/spec response — which, like every workbook-spec body, nests schemaVersion/elements/pages/layout under document (verified live 2026-08; see the wrapper note in Phase 5, and sigma-workbooks's reference/specification/schema.md). The snippet unwraps spec['document'] before touching elements, and the PUT body is just the re-wrapped document — not the full GET response.

require 'yaml'; require 'json'; require 'date'
spec = YAML.safe_load(File.read('/tmp/wb-spec.yaml'), permitted_classes: [Date, Time])
doc  = spec['document']   # schemaVersion/elements/pages/layout live here

conversions = {
  '<root_element_id>' => {
    dataModelId: '<dataModelId>',
    elementId:   '<server-assigned-element-id>',
    elementName: '<DM element name, e.g. Customer Dim>'
  }
}

root_ids = conversions.keys.to_set
child_parent = {}
doc['elements'].each do |el|
  parent = el.dig('source', 'elementId')
  child_parent[el['id']] = parent if parent && root_ids.include?(parent)
end

doc['elements'].each do |el|
  if (conv = conversions[el['id']])
    el['name']   = conv[:elementName]
    el['source'] = { 'kind' => 'data-model', 'dataModelId' => conv[:dataModelId], 'elementId' => conv[:elementId] }
    (el['columns'] || []).each do |col|
      display = col['id'].split('_').map(&:capitalize).join(' ')
      col['formula'] = "[#{conv[:elementName]}/#{display}]"
    end
  elsif (parent = child_parent[el['id']])
    new_prefix = conversions[parent][:elementName]
    (el['columns'] || []).each { |c| c['formula'] = c['formula']&.gsub('[Custom SQL/', "[#{new_prefix}/") }
  end
end

# PUT takes ONLY the document object — no name/folderId/response-only fields.
File.write('/tmp/wb-updated.json', JSON.pretty_generate({ 'document' => doc }))

curl -s -X PUT \
  -H "Authorization: Bearer $SIGMA_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d @/tmp/wb-updated.json \
  "$SIGMA_BASE_URL/v2/workbooks/<workbookId>/spec"

Data model swap-sources

The same v3alpha shape works for DMs:

POST /v3alpha/dataModels/{dataModelId}:swapSources

Use this when a DM itself contains a custom-SQL element you want to promote. Same sourceMapping[].from / .to shape; same silent-no-op gotcha for bogus from.customSqlId. Not part of the standard Phase 1–6 flow — included for completeness.

Troubleshooting