# Dataform Modeler

> Write and review Dataform SQLX models, assertions, tags, and release configs for BigQuery. Use when the user mentions Dataform, SQLX, definitions folder, declarations, assertions, incremental models, or asks to build a staging, intermediate, or mart layer in BigQuery. Also use when migrating dbt models to Dataform or when a Dataform compilation or dependency graph is broken.

- Skill: `rk-chavali/dataform-modeler` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add rk-chavali/dataform-modeler`
- Raw SKILL.md: https://api.skillmd.com/api/skills/rk-chavali/dataform-modeler/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: rk-chavali (https://skillmd.com/u/rk-chavali)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/rk-chavali/dataform-modeler

---


# Dataform modeler

Read `references/conventions.md` for layer rules and naming before writing models.

## Repository shape

```
definitions/
  sources/         declarations only, one file per source table
  staging/         one view per source table, no joins
  intermediate/    joins, dedupe, business logic
  marts/           what consumers query
  assertions/      cross-model checks that do not belong to one table
includes/
  constants.js     shared literals, never business logic
workflow_settings.yaml
```

## Declaration, always first

```sqlx
config {
  type: "declaration",
  database: dataform.projectConfig.vars.raw_project,
  schema: "raw_storefront",
  name: "orders",
  description: "Raw storefront orders, landed by a CDC stream. Grain: one row per order version."
}
```

Never reference a raw table with `${ref()}` unless it is declared. Undeclared
raw references are the most common cause of a broken dependency graph.

## Staging model

```sqlx
config {
  type: "view",
  schema: "stg_storefront",
  name: "stg_storefront__order",
  tags: ["storefront", "staging", "hourly"],
  description: "One row per storefront order, latest version. Types cast, columns renamed.",
  columns: {
    order_id: "Source order id. Primary key.",
    customer_id: "Source customer id. Nullable for guest checkout.",
    ordered_at: "Order placement time in UTC.",
    order_total_usd: "Order total in USD, excluding tax and shipping."
  },
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "ordered_at"]
  }
}

SELECT
  CAST(id AS STRING)                AS order_id,
  CAST(customer_id AS STRING)       AS customer_id,
  TIMESTAMP(created_at)             AS ordered_at,
  CAST(total_price AS NUMERIC)      AS order_total_usd,
  CAST(financial_status AS STRING)  AS payment_status
FROM ${ref("orders")}
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1
```

Rules for staging: no joins, no aggregation, no business filters beyond soft
deletes, and exactly one staging model per source table.

## Incremental mart

```sqlx
config {
  type: "incremental",
  schema: "mart_retail",
  name: "fct_order",
  tags: ["retail", "mart", "daily"],
  bigquery: {
    partitionBy: "DATE(ordered_at)",
    clusterBy: ["customer_id", "payment_status"],
    requirePartitionFilter: true,
    labels: { env: "prod", owner: "data-platform", domain: "retail" }
  },
  description: "One row per order. Grain: order_id.",
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "ordered_at", "order_total_usd"]
  }
}

pre_operations {
  DECLARE watermark DEFAULT (
    ${when(incremental(),
      `SELECT COALESCE(MAX(ordered_at), TIMESTAMP("1970-01-01")) FROM ${self()}`,
      `SELECT TIMESTAMP("1970-01-01")`)}
  )
}

SELECT
  o.order_id,
  o.customer_id,
  o.ordered_at,
  o.order_total_usd,
  o.payment_status,
  c.customer_segment
FROM ${ref("stg_storefront__order")} AS o
LEFT JOIN ${ref("dim_customer")} AS c
  USING (customer_id)
WHERE o.ordered_at > watermark
```

Use a watermark with a lookback window when the source can arrive late. State
the lookback in the description, for example "reprocesses the trailing 3 days".

## Assertions worth writing

- Uniqueness on the declared grain. Every mart, no exceptions.
- Non-null on keys and on any column a dashboard divides by.
- Row count within a band against the previous partition, to catch partial loads.
- Referential integrity from fact to dimension, as a manual assertion:

```sqlx
config { type: "assertion", tags: ["retail", "daily"] }

SELECT order_id, customer_id
FROM ${ref("fct_order")}
WHERE customer_id IS NOT NULL
  AND customer_id NOT IN (SELECT customer_id FROM ${ref("dim_customer")})
```

An assertion that never fails and never could is noise. Delete it.

## Tags and release configs

Tag by domain and by cadence, both. Schedules select on the cadence tag, humans
select on the domain tag. A model with no tag will not run in any schedule,
which is the most common silent failure in Dataform.

## Check the model against the real warehouse

Read `references/execution-model.md`. Two checks are worth running live before
you hand over a model, and both are reads:

1. **Grain.** Run the uniqueness check on the source through
   `execute_sql_readonly` before you declare `uniqueKey`. A model whose declared
   grain does not match the data fails its own assertion on the first run.
2. **Upstream schema.** `get_table_info` on every declaration, so the staging
   model casts columns that exist with the types they actually have.

Compiling and running the project is a write. Emit the `dataform run` command,
do not execute it.

## Reviewing an existing repo

Check in this order: undeclared sources, models with no assertions, marts with no
partition, joins in staging, `SELECT *` anywhere, and models with no tag. Report
findings ranked by blast radius, not by file order.