SQL Mastery
Advanced 15 min read SQL Mastery

Analytics Engineering in SQL

Turn SQL into reviewed, tested, versioned code

Analytics engineering treats SQL models as code: versioned, tested, documented and layered — the discipline dbt makes real and that makes warehouse models reliable.

analytics engineeringdbtmodelingtestslayeringsemantic layer
Prerequisites: CTEs and Subqueries
Before we start

What you will learn

  • Structure warehouse SQL into layered models
  • Add tests (uniqueness, not-null, freshness) as gates
  • Use macros/configuration to keep models DRY and portable
Introduction

What is this?

Analytics engineering is applying software engineering to SQL: models are code in a repo, structured in layers (staging → marts), compiled and run by tools like dbt, tested with assertions, and documented so analysts consume a dependable semantic surface instead of raw table soup.

Motivation

Why do you need it?

  • Ad-hoc 'gold' SQL that only one person understands becomes the single point of truth.
  • Versioned, reviewed models make warehouse changes as safe as code changes.
  • Tests gate publishes: a model with duplicate keys fails before a dashboard trusts it.
Think about it

Mental model

Imagine

A re:published, reviewed library

Think of

A modeling codebase in dbt

Every book (model) has an ISBN (lineage), a reviewer (code review), an edition (version), and shelf checks (tests). Readers (analysts) trust the catalog because the library maintains it — not because they re-read every page.

Mechanics

How it works

  • Layers: staging (raw → clean), marts (clean → business metrics); each with an owner and grain.
  • dbt compiles Jinja-SQL models, runs them in dependency order, and materializes views/tables/increments.
  • Tests: uniqueness, not-null, accepted values, and custom SQL assertions run as part of build.
  • Documentation: each model/column gets docs rendered into a catalog.
  • Macros and packages centralize repeated SQL so dialect and business logic stay consistent.

dbt-style layered modeling.

Sources
Staging
Intermediate
Marts
Tests + docs
Hands-on

Examples

A tested staging model

A tested staging model
-- stg_orders.sql
SELECT
  id AS order_id,
  customer_id::BIGINT AS customer_id,
  COALESCE(amount, 0) AS amount,
  created_at::TIMESTAMPTZ AS created_at
FROM {{ source('oltp', 'orders') }}
WHERE id IS NOT NULL;
 
-- tests: source uniqueness (one row per order_id),
--        not-null on customer_id, freshness on max(created_at)

The model defines clean contract at a grain; tests enforce uniqueness, null-safety and freshness.

Real world

In production

  • A finance mart reruns nightly; a duplicate-key test fails the build and pages the owner before dashboards move.
  • A schema change to a source breaks the staging model's contract test — surfaced in CI, not at 6am.
  • Analysts query certified marts; lineage shows every dashboard back to source rows.
Guidelines

Best practices

  • Keep staging narrow (cleaning only) and marts business-flavored at an explicit grain.
  • Test every model's primary key, not-null on criticals, and freshness thresholds.
  • Review SQL in PRs like application code; a model change is a schema change.
  • Document columns with owner intent: 'amount is net of tax, in EUR'.
Watch out

Common mistakes

  • Letting analysts query staging directly, recreating the 'spaghetti' analytics it was meant to end.
  • Materializing every model as a table — views for interfaces, tables for hot marts only.
  • Skipping tests on the documented contract, so 'certified' tables drift.
  • One model with 8 joins doing everything — split by grain and test each.
Root cause analysis

Common issues

Error
Test failed: duplicate rows in stg_orders
Root cause

Source gained multiple rows per natural key or the model's grain drifted

Fix

Document the intended grain, dedup explicitly, and update the uniqueness test.

Error
Model compiled locally but fails in validation
Root cause

Profiles/credentials differ between environments

Fix

Standardize the target schema/profile and run CI against the same warehouse.

When things break

Debugging tips

  • When a downstream mart changes, use lineage to diff its staging parents and the commit.
  • Re-run a single model + its dependents rather than the whole build.
  • Read the compiled SQL (target/compiled) when a model misbehaves — the macro expansion is the truth.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which job does a dbt-style test (uniqueness on order_id) perform?

2

In layered modeling, marts should be re-derived directly from raw sources.

Go deeper

Official documentation

dbt project structure
docs
dbt tests
docs