SQL
Intermediate 15 min read SQL

Building Modeled Tables

From query to governed data product

Modeled tables are SQL artifacts built for consumption: views, dbt models and materialized tables that own the definition and serve dashboards without re-derivation.

modelsviewsmaterializationdbttable artifactsdata flow
Prerequisites: CTEs and Query Design Dimensional Modeling
Before we start

What you will learn

  • Turn a report query into a reviewable, testable model
  • Choose between view, table and incremental materialization
  • Give models grain, tests and lineage that outlive the author
Introduction

What is this?

A modeled table is a SQL artifact that encodes a definition — a view that always reflects source, a CTAS table snapshot, or an incremental/merge model. Good models declare their grain, are tested for uniqueness and nulls, carry documentation, and get refreshed by orchestration so reports consume artifacts rather than raw queries.

Motivation

Why do you need it?

  • Dashboards built on models inherit tested definitions and stay consistent.
  • Models are reviewable and revertible assets — the query becomes lineage-visible code.
  • Materialization choices let you trade freshness for cost deliberately.
  • A modeled layer is what makes self-service BI safe.
Think about it

Mental model

Imagine

Canning the garden's harvest

Think of

Materialized models

A view is the fresh garden: always current, but you're out there picking (querying). A table is the jar: foolproof to consume, but it can spoil (go stale). Canners choose per vegetable which form fits the pantry and the season.

Mechanics

How it works

  • A view re-executes its SQL at read time — always fresh, cost at query time.
  • A table materializes results on refresh — fast reads, owned freshness, needs orchestration.
  • Incremental models apply only new/changed data and merge on the grain key.
  • Tests assert the model's contract: unique grain key, no nulls in keys, row budgets.
  • Orchestration refreshes models on schedule and alerts on failure/staleness.

From report query to governed artifact.

Report query
Encapsulate
Test
Orchestrate
Serve
Hands-on

Examples

A dbt-style model with a testable grain

A dbt-style model with a testable grain
-- models/ops/dim_customer_health.sql
WITH base AS (
  SELECT customer_id, region, COALESCE(plan,'free') AS plan
  FROM raw_customers
  WHERE deleted_at IS NULL
)
SELECT
  customer_id,
  region,
  plan
FROM base
/*
  tests:
  - unique: [customer_id]
  - not_null: [customer_id]
*/

Declares the grain (customer_id), documents expected invariants as tests, and standardizes defaults so consumers share identical semantics.

Real world

In production

  • A dbt project models staging → core, and dashboards read only core models with `unique` and `not_null` tests.
  • A nightly materialized daily-sales table serves the marketing dashboard; reconciliation checks it weekly.
  • An incremental events model merges by (event_id, date) so reruns stay idempotent.
Guidelines

Best practices

  • Declare grain first line-by-line; the reviewer's first question is 'grain?'.
  • Add tests for uniqueness, nulls and freshness at every model boundary worth trusting.
  • Materialize the grains people actually consume; leave exploration data as views.
  • Store models in version control with a code review, not in a GUI click-history.
Watch out

Common mistakes

  • Materializing without orchestration, creating silent staleness.
  • Over-testing exploratory views and under-testing served tables.
  • Incremental models missing idempotent merge keys — reruns corrupt aggregates.
  • Model definitions trapped in someone's notebook, invisible to lineage.
Root cause analysis

Common issues

Error
Incremental model double-counts a rerun
Root cause

Merge key missing idempotency (append instead of upsert)

Fix

Upsert on the natural grain key; make reruns deterministic.

Error
Dashboard still reads an abandoned table
Root cause

Model renamed but consumers not migrated

Fix

Track lineage to consumers; deprecate and redirect before deleting the old artifact.

When things break

Debugging tips

  • When a served table looks stale, check its last refresh timestamp vs its source lag.
  • When tests fail, the model's contract caught it before consumers — fix at the source, not the symptom.
  • Diff full-refresh vs incremental build outputs to find drift.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which model type is most appropriate for a hot daily dashboard over stable history?

2

An incremental model without an idempotent merge key is safe to re-run.

Go deeper

Official documentation

dbt — materializations
docs
dbt — writing data tests
docs