Semantic Models
Advanced 14 min read Semantic Models

Model-Driven Development for BI

The model is the product; the report is just a view

Model-driven BI means the model (not the report) is the product: code-versioned schemas, tests, deployment pipelines, and reporting tools that consume the model rather than re-model.

model-drivendbtsource controltesting datapipelineBI as code
Prerequisites: The Semantic Layer Overview Metric Definitions That Scale
Before we start

What you will learn

  • Shift mindset from report-scoped to model-scoped development
  • Version, test and deploy the model like software
  • Point BI tools at the model, not raw tables
Introduction

What is this?

Model-driven development treats the data model as the primary artifact: schemas and metrics are code, reviewed, versioned and tested in CI, deployed through environments, and consumed by BI tools that read the model. Reports become thin views over a trusted model — instead of each workbook secretly carrying its own modeling (and its own bugs).

Motivation

Why do you need it?

  • Model bugs ripple to every report; fixing the model fixes them all.
  • Code review + tests catch the 'works locally' regression early.
  • Environment parity (dev/staging/prod) makes deploys safe.
  • BI-as-code is the accepted modern stack: dbt + semantic layers + thin front-ends.
Think about it

Mental model

Imagine

Library vs handwritten copies per reader

Think of

Model-driven vs report-driven

A shared, well-proofread book in the library serves everyone identically. Report-driven BI is every reader copying the text by hand — typos multiply. Model-driven means editing and reviewing the one book, then reprinting.

Mechanics

How it works

  • Models and metrics live in code (dbt, LookML, TMD) under version control.
  • CI runs tests: data tests, metric reconciliation, freshness, schema tests.
  • Environment promotion moves models dev → staging → prod with parity.
  • BI tools connect to the deployed model; reports declare what they show, not how it's built.
  • Rollbacks are model version pins — reports point at a pinned, verified model.

Code-model → CI → deploy → thin reports.

Author
Review
Deploy
Serve
Certify
Hands-on

Examples

The workflow that makes models trustworthy

The workflow that makes models trustworthy
dev:   edit fact_orders.sql + revenue.yml, add a test
pr:    reviewers check diff; CI runs:
         - unique/not_null tests on keys
         - revenue reconcile vs control
         - freshness (max(date) < 2d)
staging: deploy, BI tool points "test" workspace here
prod:   promote; consumers see the certified metric
rollback: pin previous model version; reports reconnect

Models change under the same discipline as application code — review, test, promote — so reports trust them.

Real world

In production

  • A dbt project versions fact/dim models; 'Revenue' metric is certified in the catalog.
  • Power BI/Tableau point at model views, so a model fix updates every workbook in one deploy.
  • CI reconcile test fails on a 2% drift — the team investigates before even opening a report.
Guidelines

Best practices

  • Model in code with tests; reports stay thin and declarative.
  • Add data-quality gates to the pipeline (not just after facts).
  • Name and document each model like an API.
  • Pin and version models; make rollbacks reproducible.
Watch out

Common mistakes

  • Modeling 'in the tool' again — every workbook carries its own joins.
  • No tests: the model compiles, therefore it's 'correct'.
  • Direct prod model edits without review or parity.
  • Reports bypassing the certified model for one-off figures — trust leaks.
When things break

Debugging tips

  • A report broke after deploy → check the model diff and its pinned version.
  • CI test fails on reconcile → diff the metric filter/definition against control.
  • Two environments disagree → parity check; one env has drifted code.
Interviews

Interview checkpoint

Practice

Hands-on lab

Drive Everything from a SQL Model
45 min · advanced
In progress
Objectives
  • Write a model view with tests baked into SQL
  • Add a data-quality gate before reports
  • Simulate CI failure (reconcile drift) and fix

Drive Everything from a SQL Model

45 min · advanced

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

In model-driven BI, the main reusable artifact is:

2

A model change is automatically safe because CI passed.

Go deeper

Official documentation

dbt - testing guide
docs