Skip to content

exmergo/dex / claude/skills/transform

transform

Verified

Use this to author and change a dbt project or a semantic layer: bootstrap a project (`transform init`), write or refactor models from staging to marts, add tests and docs, and define semantic models, entities, dimensions, and metrics in MetricFlow, Apache Ossie or SLayer YAML. Reach for it instead of editing model files by hand whenever a change spans more than one file or must stay consistent with the project: it validates the edit against the real schema, returns a reviewable diff with a plan id, and catches what otherwise fails at `dbt run`, such as wrong columns or broken refs. Bug-fix tickets count: "this model returns wrong numbers, fix it" is a transform task. Also for "build a staging model for this table", "define a revenue metric", "add a dimension to this entity". Warehouse builds are dev-target only, gated, and cost-surfaced first. If you have not verified the source tables' columns or grain, use explore first. For a project that drifted from the warehouse, use maintain.

references/macros.md

View on GitHub
# Shipped macros

`transform macro` lists the macros Dex ships. `transform macro <name>` proposes
scaffolding one into the project's macro directory as a plan, applied with
`transform apply` like any other. The user's copy is theirs to edit; re-running
the command diffs it against the shipped version (a warning says whether it is
customized or stale), and applying that plan overwrites deliberately.

## `generate_schema_name`

The override `transform init --layered-schemas` scaffolds, so each layer folder
builds into its own `<layer>_<target name>` schema.

## `dex_unique_combination`

The generic test a declared composite grain renders
([declarations](declarations.md#dex_unique_combination-a-composite-grain)), where
`declarations.render_tests` turns it on. A declaration plan adds it for you; this
command scaffolds or diffs it on its own. It fails with the count of rows beyond
the first of each grain value, and never selects a key. The SQL goes through
dbt's dispatch, `default__dex_unique_combination` holds on every connector, and
a project overrides one warehouse with its own
`<adapter>__dex_unique_combination(model, columns)` macro.

## `dex_population_filter`

The generic test a declared population filter renders
([declarations](declarations.md#dex_population_filter-a-population-filter)), one
per `includes` or `excludes` list, where `declarations.render_tests` turns it on.
A declaration plan adds it for you; this command scaffolds or diffs it on its
own. It fails with the count of rows whose column breaks the list (a NULL breaks
an `includes` list and never an `excludes` one), and never selects a value. The
SQL goes through dbt's dispatch, `default__dex_population_filter` holds on every
connector, and a project overrides one warehouse with its own
`<adapter>__dex_population_filter(model, column_name, includes, excludes)` macro.
The cast a text comparison makes is dispatched apart, as
`dex_population_filter_text(column_name)`: ClickHouse cannot cast a NULL to its
non-Nullable `String`, so its variant is `toString`. So is each text literal,
`dex_population_filter_literal(text)`, written as `maintain verify` writes it:
a quote doubled on DuckDB, Postgres and Trino, and a backslash escaped on
BigQuery, Snowflake, Databricks, Redshift and ClickHouse, where it starts an
escape sequence.

## `dex_parent_population`

The generic test a declared parent population renders
([declarations](declarations.md#dex_parent_population-a-parents-population)),
twice per declared `population.parent`, once per `direction` (`missing`,
`unexpected`), where `declarations.render_tests` turns it on. A declaration plan
adds it for you; this command scaffolds or diffs it on its own. It fails with
the count of distinct eligible parent keys the model lacks (`missing`) or of
distinct model keys the parent lacks (`unexpected`), and never selects a key.
The `filters` argument scopes the eligible parents, each applied where the
parent has its column, read from the parent's metadata when the test runs. The
SQL goes through dbt's dispatch, `default__dex_parent_population` holds on every
connector, and a project overrides one warehouse with its own
`<adapter>__dex_parent_population(model, parent, key, direction, filters)`
macro. The text cast and the text literal of a filter comparison are dispatched
apart, as `dex_parent_population_text(column_name)` and
`dex_parent_population_literal(text)`, written per warehouse as
`dex_population_filter`'s are.

## `dex_unit_range`

The generic test a column declared a fraction renders
([declarations](declarations.md#dex_unit_range-a-fraction)), where
`declarations.render_tests` turns it on; a `percent` column renders none. A
declaration plan adds it for you; this command scaffolds or diffs it on its own.
It fails with the count of rows whose value sits below 0 or above 1 (a NULL
never counts), and never selects a value. It reads the column's type from the
relation's metadata and compares a column only where its type is a number as
`maintain verify` reads one (a numeric type name, and no boolean or temporal
one), so text, a boolean, a date, a UUID or JSON is not compared; dbt's own
`Column.is_number()` is not used, since it reads `DECIMAL(2,1)`, `FLOAT64` and `NUMBER(38,0)` as no
number. The SQL goes through dbt's dispatch, `default__dex_unit_range` holds on
every connector, and a project overrides one warehouse with its own
`<adapter>__dex_unit_range(model, column_name)` macro.

## `unpivot_json_object`

Turns a JSON object column with dynamic keys (the NoSQL-sourced shape: a
Firestore, Mongo, or DynamoDB document keyed by a related entity's id) into one
row per top-level key. It renders a complete SELECT:

```sql
select id, key as related_id, value as attrs
from (
  {{ unpivot_json_object(relation=ref('stg_entities'),
                         json_column='attributes', passthrough=['id']) }}
)
```

The contract on every connector: one row per top-level key, `key` a plain string,
`value` the warehouse's native semi-structured type, a NULL object yields no rows,
and a nested object's own field names never surface as top-level keys.

| Connector | `value` type | String-typed source column |
|---|---|---|
| BigQuery | JSON | pass `parse_json(payload)` as `json_column` |
| Snowflake | VARIANT | pass `parse_json(payload)` |
| Databricks | VARIANT (needs DBR 15.3+ or a current SQL warehouse) | pass `parse_json(payload)` |
| Redshift | SUPER | pass `json_parse(payload)` |
| Postgres | jsonb | accepted directly |
| DuckDB | JSON | accepted directly |
| ClickHouse | raw JSON text in a String | accepted directly |
| Trino | json | pass `json_parse(payload)` |

Two BigQuery quirks are absorbed by the macro: a JSON path argument must be a
compile-time literal (the macro reads values with the subscript operator, which
accepts a computed key), and `JSON_KEYS` recurses into nested objects unless
depth-limited (the macro pins depth 1). When a planned model calls the macro and
the project lacks it, the plan warns and names the scaffold command.
finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the workStars: 322kUpdated Claude CodeCodexVerifiedInstall
systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixesStars: 322kUpdated Claude CodeCodexVerifiedInstall
test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation codeStars: 322kUpdated Claude CodeCodexVerifiedInstall
skill-creatoranthropics/skillsCreate new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, edit, or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy.Stars: 198kUpdated Claude CodeCodexVerifiedInstall