Transforms — models (T as code)

Transforms are the versioned, testable layer where raw data becomes the tables and marts an agent queries — SQL models built into the data plane.

obelisk build runs transformation models: plain SQL files with a dbt-flavored template dialect. Everything materializes through the normal SQL front door, so models get pruning, history, the result cache, and governance like any other statement. That means an agent querying a model is bound by the same guarantees as any other query — no side channel around the data plane.

A model

One .sql file in the models directory (default ./models) = one model, named by its filename:

-- models/stg_events.sql
{{ config(materialized='view') }}
SELECT EVENT_ID, DAY_NUM, UPPER(KIND) AS KIND
FROM {{ source('SALES', 'EVENTS') }}

-- models/event_kind_counts.sql
{{ config(materialized='table') }}
SELECT KIND, COUNT(*) AS N FROM {{ ref('stg_events') }} GROUP BY KIND

-- models/orders_current.sql
{{ config(materialized='incremental', unique_key='ORDER_ID') }}
SELECT ORDER_ID, REGION, AMOUNT FROM {{ source('SALES','INGESTED_ORDERS') }}
{% if is_incremental() %}
WHERE ORDER_ID > (SELECT COALESCE(MAX(ORDER_ID), 0) FROM {{ this }})
{% endif %}

Template dialect

Materializations

• - view (default) — CREATE OR REPLACE VIEW
• - table — CREATE OR REPLACE TABLE … AS
• - incremental — first run (or --full-refresh): CTAS. After that: with

unique_key, a real MERGE (update matched, insert new); without, an append-only INSERT.

Building

obelisk build --target DEMO.ANALYTICS                # all models, DAG order
obelisk build --target DEMO.ANALYTICS --select stg_events,orders_current
obelisk build --target DEMO.ANALYTICS --full-refresh

Models build in ref()-dependency order (cycles are detected and reported); an error stops downstream models. The UI's Models tab shows the DAG order and builds with one click. A failing model prints its error and exits non-zero — safe for cron/tasks.

Pattern: the full pipeline

Models are the curated tables behind an agent's answers; the surrounding pipes, streams, and tasks keep them fresh without anyone asking:

pipe (EL, incremental merge)  →  raw table
stream on raw table           →  change detection (metadata-only)
task: WHEN SYSTEM$STREAM_HAS_DATA(…) AS EXECUTE PIPE / INSERT … FROM stream
obelisk build                →  staging views → incremental marts

A refresh that runs on a schedule is how a standing question stays current — see Streams & tasks. Curate the answers themselves under Insights & memory, and expose consistent definitions over these models with the Metrics & semantic layer.

Schema tests

Declare tests in the model config; run them with obelisk test:

{{ config(materialized='incremental', unique_key='ID',
          tests={'ID': ['not_null', 'unique']}) }}

obelisk test --target DEMO.ANALYTICS      # ✓/✗ per test, exit code for CI

Tests are guarantees the data plane enforces on the tables agents read — not advisory checks bolted on after the fact.

CI/CD (git-aware)

obelisk ci validates a branch by building everything into an ephemeral schema, testing it, and tearing it down:

obelisk ci --database DEMO
obelisk ci · main@6f7b491 · target DEMO.CI_6F7B491_1787235857
  ✓ build stg_events               create view
  ✓ test  orders_current.ORDER_ID unique
PASSED in 0.03s

Production tables are never touched (models read sources, build into the CI schema). Failures keep their schema for debugging and exit non-zero — drop it into GitHub Actions as-is:

- run: uv sync
- run: uv run obelisk ci --database DEMO