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

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

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

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:

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.