Part 10 — Simulating a Platform Locally (dbt & Governance)

SQL for Every Data Platform · Part 10

Simulating a Platform Locally (dbt & Governance)

Every prior module ran against SQLite/DuckDB. This part compares local-simulation options honestly, then drives a real dbt-duckdb project — showing dbt's tests/docs/DAG as lightweight governance you get for free on a laptop.

In 60 seconds

Match the tool to the question:

SQLite in-memory — zero-install, fastest iteration on plain relational SQL.
DuckDB — zero-install, columnar/analytical (Parquet, partitioning, window-function-heavy queries).
dbt-duckdb — test transformation pipelines (staging → marts, tests, docs) without a real warehouse.
Docker Postgres/MySQL — the one option needed for something SQLite/DuckDB genuinely can't do: real procedural SQL (Part 11).
dbt's tests + dbt docs generate give you a catalog and a lineage graph, for free, from code you'd write anyway.

The Honest Ordering

Before touching a real warehouse, three local options can simulate most of what this series teaches, each suited to a different job. SQLite is a zero-install, file-based (or in-memory) relational engine — the same one behind Parts 1-8 of this series — ideal for plain OLTP-style relational SQL with no server to configure. DuckDB is "SQLite for analytics": also zero-install, but columnar and built for the kind of query a warehouse runs (large scans, aggregations, reading Parquet directly) — it's what makes Part 9's partition-pruning demo possible locally. Docker Postgres is the one heavier option, a real containerized database server, needed only for the one thing SQLite and DuckDB genuinely can't simulate: real server-side procedural execution (stored procedures, triggers — Part 11). Reach for SQLite/DuckDB first every time; reach for Docker only when the question specifically requires a real running server.

Python / shell · the three options, side by side

# SQLite in-memory: zero-install, plain relational SQL
import sqlite3
conn = sqlite3.connect(':memory:')

# DuckDB: zero-install, columnar/analytical (Parquet, partitioning)
import duckdb
ddb = duckdb.connect()

# Docker Postgres: the one thing the two above genuinely can't simulate
# (real procedural SQL -- Part 11)
docker compose up -d

A Real dbt Project, Not a Toy

dbt (data build tool) is the standard way to organize and version-control the "T" in ELT: instead of hand-writing and manually running transformation SQL, you write each transformation as a .sql file that SELECTs from other models via {{ ref('model_name') }}, and dbt figures out the dependency order, runs everything, and can test the results — a workflow layer on top of plain SQL, not a new language. This module runs dbt-duckdb, so the entire workflow (seeds → staging → marts → tests) executes against a local DuckDB file instead of a real warehouse account, with identical dbt commands to what you'd run against Snowflake or BigQuery in production. The companion repo's dbt/ folder seeds the same Store dataset, builds staging views and mart tables, runs an incremental model, and passes both generic (unique/not_null/relationships/accepted_values) and singular tests — all executed via dbt build, no warehouse account needed.

shell / SQL · staging model + build command

# dbt/models/staging/stg_orders.sql
with source as (
    select * from {{ ref('orders') }}
)
select
    id as order_id,
    customer_id,
    cast(order_date as date) as order_date,
    status,
    {{ is_terminal_status('status') }} as is_terminal_status
from source

# Seeds + staging + marts + tests, all in one command, against a local .duckdb file:
dbt build --profiles-dir .

The Incremental Model: Part 8, Productionized

By default, every dbt model rebuilds completely from scratch on every run — simple, but exactly the "full load" tradeoff from Part 8: correct, but wasteful once a table is large. Setting materialized='incremental' in a model's config changes that: the first run still builds the full table, but every run after, dbt's is_incremental() Jinja block conditionally adds a WHERE clause that filters the source down to just the rows newer than what's already in the target table (referenced as {{ this }}). That's the exact watermark pattern from Part 8 — "only process what changed since last time" — expressed as a small amount of configuration and templated SQL instead of hand-written procedural logic you'd otherwise have to write and maintain yourself.

SQL · dbt/models/marts/incremental_orders.sql

{{ config(materialized='incremental', unique_key='order_id') }}

select * from {{ ref('stg_orders') }}

{% if is_incremental() %}
where order_date > (select coalesce(max(order_date), '1900-01-01') from {{ this }})
{% endif %}

Tests and Docs as Governance-Lite

"Governance" at platform scale usually means three things: access control (who can see or change what), a catalog (a searchable inventory of what tables/columns exist and what they mean), and lineage (which tables feed which, so you can trace a number back to its source or forward to everything it affects). dbt gives you two of the three essentially for free, as a side effect of writing the models and tests you'd write anyway: documented models plus column-level tests are a searchable catalog once you run dbt docs generate and dbt docs serve; the dependency graph dbt builds automatically from every ref() call between models is a lineage graph, drawn without anyone maintaining it by hand. Access control is the one piece dbt genuinely doesn't provide — that still lives in the warehouse or catalog layer itself. See Governance, Catalog & Lineage for the platform-scale version (Unity Catalog, Purview), and Data Engineer: The Builder for where dbt sits in a data engineer's day-to-day toolkit.

YAML · dbt/models/marts/_marts.yml (generic tests)

models:
  - name: incremental_orders
    description: >
      One row per order. Incremental (unique_key=order_id) -- after the first
      full build, only pulls orders newer than max(order_date) already loaded.
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

# Turns every model + test above into a browsable catalog and DAG:
dbt docs generate && dbt docs serve

Reach for the leftmost option that answers the question; Docker only for Part 11.

The takeaway: Access control is the one piece dbt genuinely can't simulate locally — that's inherently a platform-level concern. Everything else on this page (tests, docs, lineage) is something you get by documenting models well, not separate work.

Runnable code for this part: sql-for-data-platforms on GitHub — Module 10 of 14.

← Part 9 Query Performance & Tuning Part 11 → Procedural SQL: Stored Procedures, Triggers & PL/SQL

← Back to Publications