菜单
Debug dlthub transformation failures. Use when a transformation fails on a different destination than it was developed on, SQL dialect errors occur after deployment, pipeline recovery is needed after a failed run, or columns are silently dropped from output.
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/dlt-hub/dlthub-ai-harness --skill debug-transformation命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
基于 SOC 职业分类
正在显示 SKILL.md
| name | debug-transformation |
| description | Debug dlthub transformation failures. Use when a transformation fails on a different destination than it was developed on, SQL dialect errors occur after deployment, pipeline recovery is needed after a failed run, or columns are silently dropped from output. |
Diagnose and fix dlthub transformation failures. Two main failure classes: SQL dialect incompatibility (transformation works on dev destination, fails on production) and pipeline state errors (stale packages, schema drift, failed jobs).
The toolkit includes a bundled dialect checker that reads your transformation file directly — no pipeline connection needed, and no script to write. It parses each @dlt.hub.transformation function's SQL in the dev dialect and transpiles it to the target dialect, catching portability issues before deployment.
Tell the user: "I'm going to run a static SQL compatibility check on your transformation file to catch any SQL dialect issues before you move to prod."
You must pass the dev and prod destination types explicitly via --read and --write. If you already have this from context (e.g. from create-transformation), use it directly. If not, call get_local_pipeline_state via MCP to retrieve the destination type for the pipeline, then ask the user for the prod destination if unknown.
Run the checker yourself and read the output:
uv run python ${CLAUDE_PLUGIN_ROOT}/tools/check_dialect.py transformations/<dataset>_to_cdm.py --read <dev_dialect> --write <prod_dialect>
Expected output looks like:
Dialects: duckdb -> bigquery
Checking 5 transformation(s) from transformations/hubspot_to_cdm.py
[dim_company] OK
[dim_person] WARN: double-quoted identifier "email"; verify destination quoting in bigquery
[fact_activity] ERROR: transpile duckdb->bigquery failed: ...
...
SUMMARY
warnings: 1
errors: 1
Walk the user through the results:
This check catches the most common issues, but does not replace inspecting Relation.to_sql() output if deployment still fails after fixes:
If either dlt destination is not covered by SQLGlot, the script stops and prints the available SQLGlot dialects. In that case, inspect dlt's actual Relation.to_sql() output or run a target-destination test pipeline instead. SQLGlot supports 31+ dialects — see https://sqlglot.com/sqlglot.html for dialect names.
Rewrite these to ANSI SQL so SQLGlot can transpile them to any destination:
| Dialect-specific pattern | Portable ANSI alternative |
|---|---|
x::TEXT, x::INT (cast shorthand) | CAST(x AS VARCHAR), CAST(x AS INTEGER) |
IFNULL(a, b) | COALESCE(a, b) |
ILIKE | LOWER(x) LIKE LOWER(y) |
INT64 / STRING / FLOAT64 as type names | BIGINT / VARCHAR / DOUBLE |
EPOCH_MS(), STRFTIME(), LIST_AGG(), etc. | No ANSI equivalent — use query_dialect (see below) |
QUALIFY clause | Wrap in subquery with WHERE on the window result |
If a transformation genuinely requires a dialect-specific function with no ANSI equivalent, declare the source dialect with query_dialect so SQLGlot knows how to transpile:
@dlt.hub.transformation
def my_transform(dataset: dlt.Dataset):
yield dataset(
"SELECT STRFTIME(created_at, '%Y-%m') AS month FROM events",
query_dialect="duckdb", # tells dlt this SQL is DuckDB dialect
)
If SQLGlot raises UnsupportedError or logs warnings at unsupported_level, the construct has no mapping to the target dialect and must be rewritten to an ANSI equivalent or handled in application code.
References:
Relation source: https://github.com/dlt-hub/dlt/blob/master/dlt/dataset/relation.py#L276Use this escalation order. Do not skip steps.
uv run dlthub local pipeline failed-jobs <pipeline_name>
uv run dlthub local pipeline trace <pipeline_name>
Read the error messages before taking any recovery action. Most failures are fixable without touching destination state.
If a prior run failed mid-load and left pending packages that keep retrying old (broken) SQL:
uv run dlthub local pipeline drop-pending-packages <pipeline_name>
Re-run after clearing. If the underlying SQL was fixed, this is often all that is needed.
If local pipeline state has drifted from the destination (e.g. after a partial load or schema change):
uv run dlthub local pipeline sync <pipeline_name>
If no recoverable destination state exists, sync may not resolve partial retries — use drop-pending-packages first.
DuckDB only: if the failure is
"Parser Error: Adding columns with constraints not yet supported",dlthub local pipeline dropwill not fix it — go to section 3's DuckDB workaround instead.
Only if the steps above do not resolve the failure and incorrect schema or tables were already loaded to the destination:
uv run dlthub local pipeline drop <pipeline_name> <resource> # drop a specific resource
uv run dlthub local pipeline drop <pipeline_name> --drop-all # only with explicit user confirmation
Safety rules before dropping:
--drop-alldrop removes destination tables and resets matching local state — this forces a full reload and may remove good data alongside badReferences:
dlthub silently drops columns it cannot type-infer — no error, no warning. Two root causes:
When a column is NULL-only on the first run and no columns= hint was provided, dlthub strips the column from the schema. Subsequent runs write data but the column is absent.
When a transformation uses derived expressions — md5(), strftime(), TRY_CAST, CASE WHEN ... END, aggregates with aliases, or function chains — dlthub cannot infer the output type from the SQL alone. Without a columns= hint the column is silently dropped.
Diagnose: compare expected vs actual columns using the MCP get_table_schema tool, or inspect via:
uv run dlthub local pipeline show <pipeline_name>
Identify which computed columns are absent from the output schema. Each missing column needs an explicit columns= hint.
Fix: add columns= hints for every affected column:
@dlt.hub.transformation(
write_disposition="replace",
columns={
"company_sk": {"data_type": "text", "nullable": False},
"joined_at": {"data_type": "timestamp", "nullable": True},
"email_hash": {"data_type": "text", "nullable": True}, # md5() result
"month_bucket": {"data_type": "text", "nullable": True}, # strftime() result
"event_count": {"data_type": "bigint", "nullable": True}, # COUNT() alias
},
)
def dim_company(dataset: dlt.Dataset):
...
data_type values must match the key type contract established during create-transformation (consistently text or bigint for surrogate keys).
Apply columns= hints for:
LEFT JOIN (lookup may return NULL)md5(), strftime(), TRY_CAST, CASE WHEN, aggregate aliases, function chainsThis error surfaces when re-running a transformation with new or modified columns= hints against DuckDB — DuckDB's ALTER TABLE cannot add constrained columns. The workaround is to drop the dataset and re-run from scratch:
# DuckDB only — this drops all tables in the dataset
duckdb <path_to_db_file> "DROP SCHEMA <dataset_name> CASCADE;"
Then re-run the transformation script. This is DuckDB-specific behavior and will not occur on cloud destinations (BigQuery, Snowflake, Postgres). Do not use dlthub local pipeline drop for this error — it does not clear the DuckDB schema and will not resolve the constraint conflict.
After fixing an issue and re-running, validate the output using the same checks as the happy path — see create-transformation Step 9.