LEARN · MODERN DATA ENGINEERING
ELT changes the center of gravity of a data pipeline.
In a traditional ETL pipeline, data is extracted, transformed outside the analytical database, and then loaded into its final analytical shape. In an ELT pipeline, the order changes: data is extracted, loaded relatively early, and transformed inside the analytical system that will query it.
That distinction matters because modern analytical databases are already optimized for relational work such as:
-
scanning large columnar datasets;
-
filtering and joining tables;
-
aggregating billions of values;
-
executing window functions;
-
parallelizing SQL;
-
persisting intermediate and final analytical relations.
If your raw order data is already queryable in a warehouse, pulling it into an application process merely to perform joins and aggregations can add unnecessary movement and another execution environment to operate.
dbt fits directly into the transformation part of ELT. It lets you express transformations as version-controlled SQL models, connect those models through explicit dependencies, test their outputs, attach documentation to them, and derive lineage from the same project.
As of August 10, 2026, dbt’s documentation marks the Rust-based v2 generation as the recommended current generation, while the original Python-based dbt Core v1.x remains maintained. dbt Core 1.12.0 was released on July 16, 2026, and dbt supports minor Core releases for one year from their initial release. This course deliberately uses dbt Core 1.12 because it remains current and gives us a straightforward local Python installation and the familiar Core v1.x documentation workflow.
For our local adapter, we will use dbt-duckdb 1.11.0, released August 7, 2026, together with DuckDB 1.5.5, released July 22, 2026. The adapter documentation describes support for dbt Core versions starting at 1.8.x and DuckDB starting at 1.0.0, so dbt-duckdb 1.11.0 is compatible with the dbt Core 1.12 setup used here; dbt Core 1.12 is not the adapter’s minimum requirement.
By the end, you will have a complete local analytics project in which:
-
a Python script performs the tiny extract-and-load simulation;
-
DuckDB stores raw and transformed data;
-
dbt owns the transformation graph;
-
generic tests protect structural assumptions;
-
singular tests enforce business rules;
-
source()andref()create explicit dependencies; -
documentation describes the metric semantics;
-
generated artifacts expose lineage.
Why transform where the data lives?
Imagine that an ingestion system has already loaded two tables into your analytical database:
raw.customers raw.orders
You need to produce customer revenue.
An application-heavy design might:
-
query both tables into Python;
-
materialize them as dataframes;
-
normalize statuses;
-
join customers to orders;
-
aggregate revenue;
-
send the results back to the analytical database.
That is sometimes justified. Python is the right tool when the transformation genuinely requires Python libraries or algorithms that are awkward in SQL.
But our example is almost entirely relational. The database can do it directly.
The ELT flow is therefore:
Application / API / files
|
| Extract + Load
v
Raw analytical tables
|
| dbt transformations
v
Staging models
|
v
Fact models
|
v
Business marts
|
v
Dashboards / notebooks / applications
dbt begins once the data is available to query.
In production, another system might be responsible for putting records into raw.orders. dbt can declare that table as a source and then transform it without pretending to be the ingestion system.
That separation is useful: extraction, loading, transformation, orchestration, and consumption do not need to be one monolithic application.
The model we will build
Our sample business is a fictional online shop.
The raw layer contains customer records and orders. Order values are stored in integer cents, which is common because it avoids treating currency as an imprecise floating-point input representation.
Our transformation graph is:
source: raw.customers ──> stg_customers ───────────────┐
|
v
source: raw.orders ─────> stg_orders ─────────────> fct_orders
|
v
customer_revenue
Each model has an explicit grain:
-
stg_customers: one row per customer; -
stg_orders: one row per order; -
fct_orders: one row per order, enriched with customer attributes; -
customer_revenue: one row per customer.
That concept of grain is one of the most important ideas in analytical modeling.
Before asking whether a table is correct, ask:
What does one row represent?
If the answer is “one order,” then order_id should usually be unique and non-null.
If the answer is “one customer,” customer_id should usually be unique and non-null.
Testing becomes much easier once grain is explicit.
Create the local project
Create a directory and virtual environment:
mkdir shop_analytics cd shop_analytics python -m venv .venv source .venv/bin/activate python -m pip install --upgrade pip python -m pip install "dbt-core==1.12.0" "dbt-duckdb==1.11.0" "duckdb==1.5.5"
Check the installation:
dbt --version
DuckDB is especially convenient for learning ELT because it is an in-process analytical database. dbt-duckdb can persist data directly to a .duckdb file by setting path in the profile, so there is no database server to provision for this exercise.
Create this project structure:
shop_analytics/ ├── dbt_project.yml ├── profiles.yml ├── load_raw.py ├── models/ │ ├── sources.yml │ ├── docs.md │ ├── schema.yml │ ├── staging/ │ │ ├── stg_customers.sql │ │ └── stg_orders.sql │ └── marts/ │ ├── fct_orders.sql │ └── customer_revenue.sql └── tests/ ├── assert_nonnegative_amounts.sql ├── assert_revenue_reconciles.sql ├── assert_noncompleted_revenue_is_zero.sql └── properties.yml
It is deliberately small. A useful learning project should make the important boundaries visible rather than hiding them inside dozens of helper files.
Configure the dbt project
Create dbt_project.yml:
name: shop_analytics version: "1.0.0" config-version: 2 profile: shop_analytics model-paths: - models test-paths: - tests clean-targets: - target - dbt_packages models: shop_analytics: staging: +materialized: view marts: +materialized: table
Our staging models will therefore become views, while the marts will become physical tables.
Now create profiles.yml:
shop_analytics: target: dev outputs: dev: type: duckdb path: ./warehouse.duckdb schema: analytics threads: 4
The profile lives inside the project only to make the tutorial self-contained. We will tell dbt to use this location explicitly with --profiles-dir ..
Load neutral sample retail data
Create load_raw.py:
from pathlib import Path import duckdb ROOT = Path(__file__).resolve().parent DATABASE = ROOT / "warehouse.duckdb" connection = duckdb.connect(str(DATABASE)) connection.execute("create schema if not exists raw") connection.execute( """ create or replace table raw.customers as select * from ( values (1, 'Ada', 'Germany'), (2, 'Linh', 'France'), (3, 'Mateo', 'Spain'), (4, 'Zoe', 'Portugal'), (5, 'Nora', 'Ireland') ) as customers( customer_id, customer_name, country ) """ ) connection.execute( """ create or replace table raw.orders as select * from ( values (1001, 1, timestamp '2026-08-01 10:15:00', 'COMPLETED', 12900), (1002, 1, timestamp '2026-08-03 13:45:00', 'RETURNED', 4500), (1003, 2, timestamp '2026-08-04 09:30:00', 'COMPLETED', 7600), (1004, 3, timestamp '2026-08-05 16:20:00', 'SHIPPED', 3200), (1005, 3, timestamp '2026-08-06 11:05:00', 'COMPLETED', 9900), (1006, 4, timestamp '2026-08-07 18:10:00', 'PLACED', 2100) ) as orders( order_id, customer_id, order_ts, status, amount_cents ) """ ) connection.close() print(f"Loaded raw retail data into {DATABASE}")
Run it:
python load_raw.py
The extract-and-load side of our miniature ELT system is now complete.
The important architectural boundary is that dbt did not create those source records. It will transform data that already exists in the analytical database.
Declare the raw tables as dbt sources
Create models/sources.yml:
version: 2 sources: - name: raw description: Raw retail data loaded before dbt transformations run. schema: raw tables: - name: customers description: One row per source customer. - name: orders description: One row per source order from the transactional system.
A model could hard-code the relation:
select * from raw.orders
The database would happily execute it.
But dbt would know less about the relationship.
Instead, use:
select * from {{ source('raw', 'orders') }}
source() returns a relation and creates a dependency between the source and the current model. That dependency is useful to dbt for documentation and graph-aware selection, while the expression compiles to the actual database relation.
This is the first important idea behind dbt lineage: dependencies are encoded in executable project code rather than maintained as a separate drawing.
Build the staging layer
Staging models are a good place to normalize source representation before applying higher-level business logic.
Create models/staging/stg_customers.sql:
select cast(customer_id as integer) as customer_id, trim(customer_name) as customer_name, trim(country) as country from {{ source('raw', 'customers') }}
Create models/staging/stg_orders.sql:
select cast(order_id as integer) as order_id, cast(customer_id as integer) as customer_id, cast(order_ts as timestamp) as order_ts, lower(trim(status)) as status, cast(amount_cents as bigint) as amount_cents, cast(amount_cents as decimal(18, 2)) / 100 as gross_amount from {{ source('raw', 'orders') }}
The staging layer now owns several representation decisions:
-
IDs receive explicit types;
-
statuses are normalized to lowercase;
-
whitespace cleanup happens once;
-
integer cents are converted into currency units.
Suppose a downstream analyst wants completed orders.
Without staging, different queries might contain all of these:
where status = 'COMPLETED'
where lower(status) = 'completed'
where lower(trim(status)) = 'completed'
Centralizing normalization prevents every downstream model from independently rediscovering source quirks.
Build the order fact table
Create models/marts/fct_orders.sql:
select o.order_id, o.customer_id, c.customer_name, c.country, o.order_ts, o.status, o.gross_amount, case when o.status = 'completed' then o.gross_amount else cast(0 as decimal(18, 2)) end as recognized_revenue from {{ ref('stg_orders') }} as o left join {{ ref('stg_customers') }} as c on o.customer_id = c.customer_id
ref() is more than a convenient way to avoid typing a schema name. It returns the referenced relation and creates a dependency edge between the current model and the referenced node. dbt uses those dependencies to construct the graph and determine execution order.
Our fact table also introduces a business definition:
Recognized revenue in this tutorial equals the gross value of completed orders.
A shipped order contributes zero.
A returned order contributes zero.
A newly placed order contributes zero.
That definition is specific to this fictional exercise. Real accounting rules can be substantially more complicated.
The important lesson is not the particular rule. It is that the rule now lives in reviewable, version-controlled transformation code.
Why use a left join?
The join is intentionally a left join:
left join {{ ref('stg_customers') }} as c on o.customer_id = c.customer_id
Suppose an order unexpectedly contains customer_id = 999, but no corresponding customer exists.
With an inner join, that order disappears.
The SQL still runs.
The scheduler can still report success.
The resulting revenue total may even look plausible.
With a left join, the order remains visible and a relationship test can expose the broken key.
That distinction illustrates one of the most dangerous characteristics of analytical SQL: a query can execute perfectly and still be semantically wrong.
Build the customer revenue mart
Create models/marts/customer_revenue.sql:
select c.customer_id, c.customer_name, c.country, count(o.order_id) as order_count, count( case when o.status = 'completed' then 1 end ) as completed_order_count, coalesce( sum(o.recognized_revenue), cast(0 as decimal(18, 2)) ) as total_revenue from {{ ref('stg_customers') }} as c left join {{ ref('fct_orders') }} as o on c.customer_id = o.customer_id group by c.customer_id, c.customer_name, c.country
Notice that the grain changed.
fct_orders means:
one row = one order
customer_revenue means:
one row = one customer
That means our tests must change with the grain.
Testing order_id uniqueness in the aggregate mart would make no sense because order_id is no longer part of the model.
Instead, customer_id should uniquely identify rows.
A reliable modeling habit is:
-
define grain;
-
identify the key that represents that grain;
-
test the key;
-
then test the metric logic.
Add generic data tests
Create models/schema.yml:
version: 2
models:
- name: stg_customers
description: Clean customer records at one row per customer.
columns:
- name: customer_id
description: Unique customer identifier.
data_tests:
- unique
- not_null
- name: customer_name
description: Customer display name.
data_tests:
- not_null
- name: country
description: Customer country from the source system.
- name: stg_orders
description: Clean orders at one row per order.
columns:
- name: order_id
description: Unique order identifier.
data_tests:
- unique
- not_null
- name: customer_id
description: Customer that owns the order.
data_tests:
- not_null
- relationships:
arguments:
to: ref('stg_customers')
field: customer_id
- name: status
description: Normalized order lifecycle status.
data_tests:
- not_null
- accepted_values:
arguments:
values:
- placed
- shipped
- completed
- returned
- name: amount_cents
description: Source order amount stored as integer cents.
data_tests:
- not_null
- name: gross_amount
description: Order amount converted into currency units.
data_tests:
- not_null
- name: fct_orders
description: Enriched order facts at one row per order.
columns:
- name: order_id
description: Unique order identifier.
data_tests:
- unique
- not_null
- name: customer_id
description: Customer that owns the order.
data_tests:
- not_null
- name: recognized_revenue
description: "{{ doc('recognized_revenue') }}"
data_tests:
- not_null
- name: customer_revenue
description: Customer revenue mart at one row per customer.
columns:
- name: customer_id
description: Unique customer identifier.
data_tests:
- unique
- not_null
- name: order_count
description: Number of orders associated with the customer.
data_tests:
- not_null
- name: completed_order_count
description: Number of completed orders for the customer.
data_tests:
- not_null
- name: total_revenue
description: Sum of recognized revenue for the customer.
data_tests:
- not_null
For current dbt projects, data_tests: is the preferred property. dbt still recognizes the older tests: spelling as a legacy alias. Arguments for tests such as relationships and accepted_values belong under arguments: in the current syntax. dbt ships four built-in generic data tests: unique, not_null, accepted_values, and relationships.
That is why a current example looks like:
data_tests: - accepted_values: arguments: values: - placed - shipped - completed - returned
rather than copying older project snippets blindly.
Understand what a dbt data test actually does
A dbt data test is easiest to understand backward.
The test query tries to return failing records.
For example, the conceptual logic behind a uniqueness assertion is similar to:
select
order_id
from some_model
group by order_id
having count(*) > 1
If the query returns no violating groups, the assertion passes.
A not-null test searches for nulls.
A relationship test searches for child keys that cannot be matched to the referenced parent relation.
An accepted-values test searches for values outside the allowed set.
Current dbt documentation describes singular tests in the same failure-oriented way: write SQL that returns the records violating the assertion; zero failing records means success.
That design is powerful because arbitrary business rules can be expressed using SQL.
Add singular tests for business invariants
Structural tests are necessary, but they do not prove that your metric is correct.
A table can have:
-
unique IDs;
-
no missing primary keys;
-
valid foreign keys;
-
accepted statuses;
and still calculate revenue incorrectly.
Create tests/assert_nonnegative_amounts.sql:
select order_id, gross_amount, recognized_revenue from {{ ref('fct_orders') }} where gross_amount < 0 or recognized_revenue < 0
Notice that there is no trailing semicolon.
dbt’s current documentation explicitly recommends omitting semicolons from singular test SQL because a trailing semicolon can interfere with the SQL dbt generates around the test.
Now create tests/assert_noncompleted_revenue_is_zero.sql:
select order_id, status, recognized_revenue from {{ ref('fct_orders') }} where status <> 'completed' and recognized_revenue <> 0
This test directly protects our documented business rule.
If somebody later changes the fact model to:
case
when o.status in ('completed', 'shipped') then o.gross_amount
else cast(0 as decimal(18, 2))
end as recognized_revenue
the SQL will compile.
The primary keys will remain unique.
The status test will still pass.
Revenue will remain non-null.
But the business invariant test will fail.
That is exactly what we want.
Add a reconciliation test
Now create tests/assert_revenue_reconciles.sql:
with detail_revenue as ( select customer_id, sum(recognized_revenue) as expected_revenue from {{ ref('fct_orders') }} group by customer_id ), mart_revenue as ( select customer_id, total_revenue from {{ ref('customer_revenue') }} ) select m.customer_id, m.total_revenue, coalesce( d.expected_revenue, cast(0 as decimal(18, 2)) ) as expected_revenue from mart_revenue as m left join detail_revenue as d on m.customer_id = d.customer_id where abs( m.total_revenue - coalesce( d.expected_revenue, cast(0 as decimal(18, 2)) ) ) > 0.001
This test protects something much closer to what the business actually cares about:
The customer-level revenue mart must reconcile to recognized revenue in the detailed fact table.
Why is that important?
Because an aggregate can be wrong even when all of its fields look structurally valid.
The fan-out bug: correct rows, wrong totals
Suppose you have customer labels:
customer_id label 1 newsletter 1 vip 1 beta
Then somebody writes:
select o.customer_id, sum(o.recognized_revenue) as total_revenue from {{ ref('fct_orders') }} as o join customer_labels as l on o.customer_id = l.customer_id group by o.customer_id
Customer 1 has three labels.
Every order for that customer now appears three times after the join.
If the customer has one completed order worth 129, the joined rows can conceptually look like:
customer_id order_id label recognized_revenue 1 1001 newsletter 129.00 1 1001 vip 129.00 1 1001 beta 129.00
The sum is now 387.
No SQL exception occurred.
The values are individually legitimate.
The join condition is syntactically valid.
The aggregate is still wrong.
This is why data engineering correctness is not equivalent to “the query ran successfully.”
Grain-aware tests and reconciliations give these silent errors somewhere to surface.
Document the metric next to the model
Create models/docs.md:
{% docs recognized_revenue %} Recognized revenue is the gross order amount when the normalized order status is `completed`. Orders in `placed`, `shipped`, or `returned` status contribute zero recognized revenue in this tutorial. This is a business rule for the tutorial, not a universal accounting definition. If the recognition policy changes, update the model, tests, and documentation together. {% enddocs %}
Then this property from schema.yml:
description: "{{ doc('recognized_revenue') }}"
injects the named docs block into the generated documentation.
dbt supports reusable docs blocks in Markdown files and resolves them through doc() inside resource descriptions. For dbt Core v1.x, dbt docs generate builds the static documentation site from that project metadata.
That gives the question “What counts as recognized revenue?” a much stronger answer than “check the dashboard query.”
The definition is now stored beside:
-
the transformation;
-
its tests;
-
its dependency graph;
-
its Git history.
Document the singular tests
Create tests/properties.yml:
data_tests: - name: assert_nonnegative_amounts description: > Returns orders whose gross amount or recognized revenue is negative. - name: assert_noncompleted_revenue_is_zero description: > Returns non-completed orders that incorrectly contribute recognized revenue. - name: assert_revenue_reconciles description: > Returns customer rows where the customer-level revenue mart does not reconcile to recognized revenue from the order fact table.
Current dbt resource properties allow descriptions to be attached to data tests, including singular data tests, so your quality checks can themselves become documented project resources.
Validate the project
First check configuration and connectivity:
dbt debug --profiles-dir .
Then build everything:
dbt build --profiles-dir .
dbt build runs selected buildable project resources in DAG order, including models and data tests. That makes it especially useful for this workflow: materializing SQL successfully is not enough if its assertions immediately fail.
When debugging, you can separate the phases.
Run the models:
dbt run --profiles-dir .
Run the tests:
dbt test --profiles-dir .
For normal development, the combined build is often the stronger habit because it keeps transformation and validation close together.
Inspect the finished mart
Ask dbt to show the customer mart:
dbt show --select customer_revenue --profiles-dir .
The result should be equivalent to:
customer_id customer_name country order_count completed_order_count total_revenue 1 Ada Germany 2 1 129.00 2 Linh France 1 1 76.00 3 Mateo Spain 2 1 99.00 4 Zoe Portugal 1 0 0.00 5 Nora Ireland 0 0 0.00
Several modeling decisions are visible in that output.
Ada has two orders, but only the completed one contributes revenue. Her returned order affects order_count but not total_revenue.
Mateo has one shipped order worth 32 and one completed order worth 99. His recognized revenue is 99, not 131.
Zoe has a placed order but no completed orders.
Nora has no orders at all, yet she still appears because the mart starts from customers and left-joins the order facts.
dbt did not invent any of those semantics.
We did.
dbt’s contribution is giving those decisions a structured, reviewable place to live.
Generate documentation and inspect lineage
Generate the Core v1.x documentation artifacts:
dbt docs generate --profiles-dir .
Serve the generated site locally:
dbt docs serve --profiles-dir .
For dbt Core v1.x, this is the current legacy-docs workflow. dbt’s newer Docs v2 experience belongs to the Fusion/Core v2 generation, while the Core v1.x dbt docs generate command continues to produce the static documentation site used in this tutorial.
Open the lineage view and you should see the relationships created through source() and ref().
Conceptually:
raw.customers | v stg_customers | \ | \ | v | fct_orders | | | v +--> customer_revenue raw.orders | v stg_orders | v fct_orders
The graph is derived from executable dependencies.
You did not draw it separately and hope somebody remembers to update it.
Lineage is also stored as metadata
The visual DAG is only one consumer of dbt’s project metadata.
dbt Core produces manifest.json, which contains project resources and dependency structures including parent_map and child_map. dbt uses the manifest for documentation and state comparison, and external tooling can inspect the same artifact.
That means lineage is useful for far more than diagrams.
It can support:
-
impact analysis;
-
selective builds;
-
CI workflows;
-
project health analysis;
-
discovering downstream dependencies;
-
identifying the blast radius of a model change.
Suppose somebody proposes changing the normalization logic in stg_orders.
The graph tells you that the potential downstream path is:
stg_orders
|
v
fct_orders
|
v
customer_revenue
That is operational information.
Why direct table names weaken a dbt project
Consider this model:
select
*
from analytics.stg_orders
The database can execute it.
Now compare it with:
select * from {{ ref('stg_orders') }}
The second form communicates intent to both the database transformation and dbt’s dependency system.
That one dependency can support:
-
correct build ordering;
-
graph selection;
-
documentation;
-
downstream traversal;
-
lineage;
-
state-aware workflows.
This is why ref() should not be mentally reduced to string substitution. Its graph semantics are one of dbt’s central features.
Tests should protect several different layers
A healthy analytics project rarely has one magical test that proves correctness.
Different assertions defend different failure modes.
Structural tests
These establish the basic shape of a table:
data_tests: - unique - not_null
They answer questions such as:
-
Is the primary identifier present?
-
Does the model still obey its stated grain?
Domain tests
These constrain finite value sets:
data_tests: - accepted_values: arguments: values: - placed - shipped - completed - returned
Without this assertion, a new status such as complete, fulfilled, or COMPLETED could arrive and quietly fall outside downstream logic.
Relationship tests
These protect important joins:
data_tests:
- relationships:
arguments:
to: ref('stg_customers')
field: customer_id
They ask whether the values used as foreign-key-like references actually have corresponding parents.
Business-invariant tests
These protect meaning.
Examples include:
-
non-completed orders contribute zero recognized revenue;
-
totals cannot be negative;
-
invoice totals reconcile to invoice lines;
-
customer revenue reconciles to order revenue;
-
a daily mart reconciles to the underlying transaction facts.
These tests often deliver the most business value because they protect assumptions that a database schema alone cannot express.
Grain is an executable contract
Consider an order-level table:
order_id revenue 42 100.00
Now join it to order items:
order_id item_id revenue 42 A 100.00 42 B 100.00 42 C 100.00 42 D 100.00
Each joined row may look perfectly reasonable.
But this query:
select
sum(revenue)
from joined_orders
returns:
400.00
The original order was only worth 100.
The failure occurred because the grain changed from one row per order to one row per order item while the order-level metric remained duplicated across the new rows.
That leads to a powerful review question:
After every join, what is the grain now?
Do not wait until the final dashboard to ask.
Use layers to localize responsibility
Our tiny project separates four responsibilities.
Sources: what arrived?
Sources describe externally loaded relations.
They should not pretend the input is cleaner than it really is.
Staging: how should the representation be normalized?
Staging owns low-level consistency such as:
-
names;
-
types;
-
casing;
-
whitespace;
-
simple conversions.
Fact models: what reusable analytical event or entity exists?
fct_orders establishes:
one row per order
and attaches reusable business behavior to that grain.
Marts: what question is the business trying to answer?
customer_revenue changes grain to:
one row per customer
and packages the facts into a consumer-oriented analytical model.
When a number is wrong, this layering gives you a debugging ladder:
-
Did the raw record arrive?
-
Did staging normalize it correctly?
-
Did the fact model preserve the intended grain?
-
Is the business rule correct?
-
Did aggregation preserve the total?
Without layers, all five questions tend to be buried inside a giant SQL statement.
Cherry on the cake: a real 2026 dbt-related CVE
Data correctness is not the only engineering property worth treating seriously. Your transformation toolchain is software, which means dependency and supply-chain security matter too.
In March 2026, CVE-2026-29790 was published for dbt-common, the shared utilities package used by dbt Core and adapter implementations. The vulnerable archive-extraction logic used a character-based path-prefix comparison while attempting to keep extracted files inside an intended directory. A malicious archive could exploit that weakness to write into a sibling path with a matching prefix. The issue was patched in dbt-common 1.34.2 and 1.37.3.
The surprising lesson is not merely “there was a CVE.”
It is that a data transformation repository has two distinct trust boundaries:
-
the SQL and project logic you wrote;
-
the code you install in order to execute that project.
You can have immaculate reconciliation tests and still run unsafe tooling if dependency hygiene is ignored.
Conversely, you can have fully patched dependencies and still publish a catastrophically wrong revenue metric if nobody tested its semantics.
Reliable data engineering requires both.
For practical projects:
-
keep dbt and adapters on supported versions;
-
review third-party dbt packages before adding them;
-
pin versions intentionally in reproducible environments;
-
monitor security advisories for your Python and dbt dependency tree;
-
treat CI runners and transformation credentials as production infrastructure.
The current tutorial pins recent releases rather than inheriting whatever versions happen to be installed globally. dbt Core 1.12.0, dbt-duckdb 1.11.0, and DuckDB 1.5.5 were all current releases at the time of writing.
Put dbt build in CI
Local tests become far more valuable when the same assertions gate changes before deployment.
For this self-contained project, the core CI commands are simply:
python -m pip install "dbt-core==1.12.0" "dbt-duckdb==1.11.0" "duckdb==1.5.5" python load_raw.py dbt build --profiles-dir .
If somebody breaks a relationship, grain assumption, accepted status set, or revenue invariant, the build should fail before the change is treated as healthy.
A real warehouse environment would replace load_raw.py with access to an isolated CI dataset or development schema.
The principle does not change:
“SQL compiled” is not the definition of a successful transformation.
Deliberately break the project
A course is more useful when you observe failures rather than merely reading about safeguards.
First, modify one source order inside load_raw.py:
(1006, 999, timestamp '2026-08-07 18:10:00', 'PLACED', 2100)
That particular line is SQL embedded in Python and cannot be pasted directly as Python syntax outside the surrounding SQL string, so make the actual edit inside the values section of the existing loader:
(1006, 999, timestamp '2026-08-07 18:10:00', 'PLACED', 2100)
Reload the source:
python load_raw.py
Then rebuild:
dbt build --profiles-dir .
The relationships test on stg_orders.customer_id should now find the orphaned customer reference.
Restore the customer ID to 4 before continuing.
Next, deliberately weaken the recognition logic in fct_orders.sql:
case
when o.status in ('completed', 'shipped') then o.gross_amount
else cast(0 as decimal(18, 2))
end as recognized_revenue
Run:
dbt build --profiles-dir .
The structural tests may remain green.
But assert_noncompleted_revenue_is_zero should fail because the shipped order now contributes revenue.
That difference is the point.
Tests should not only prove that data looks like a table.
They should protect what important fields mean.
What changes when you move to a cloud warehouse?
Conceptually, not much.
The architecture still looks like:
sources | v staging | v facts / dimensions | v business marts | v consumers
The profile and adapter change.
Some SQL may need adjustment for platform-specific functions or types.
But strong project boundaries remain portable:
-
express external data through
source(); -
express model dependencies through
ref(); -
keep grain explicit;
-
test important keys;
-
test important relationships;
-
test metric invariants;
-
document business semantics beside the code.
That is much more portable than scattering fully qualified physical table names and dashboard-specific calculations throughout an analytics stack.
What dbt does not solve
dbt cannot determine whether your business definition is correct.
It cannot infer that your organization means:
-
revenue after refunds rather than before refunds;
-
active customers excluding employees;
-
churn after 30 days rather than 60;
-
completed orders based on settlement rather than shipment;
-
customer identity based on a new merged account model.
Those decisions have to come from the organization.
What dbt gives you is a disciplined place to encode them.
Likewise, data tests are not every kind of observability.
A production analytical system may additionally need:
-
source freshness checks;
-
unexpected volume monitoring;
-
schema-change detection;
-
warehouse cost monitoring;
-
orchestration alerts;
-
SLA monitoring;
-
anomaly detection on important metrics;
-
downstream dashboard checks.
The defensible claim is not:
dbt makes data correct.
It is:
dbt lets you make many important transformation assumptions explicit, executable, version-controlled, testable, and visible through lineage.
That is far more useful.
The deeper lesson: a metric is software
A revenue metric has:
-
inputs;
-
dependencies;
-
transformation logic;
-
edge cases;
-
assumptions;
-
consumers;
-
version history;
-
failure modes.
Those are software properties.
Once you treat analytical transformations that way, software-engineering practices become natural:
-
keep them in Git;
-
review changes;
-
write tests;
-
separate responsibilities;
-
document important interfaces;
-
make dependencies explicit;
-
gate regressions in CI.
ELT lets the analytical system execute transformations close to the data.
dbt gives the team a framework for managing the logic, contracts, tests, documentation, and dependency graph around those transformations.
Neither has to do everything.
That separation is the strength of the architecture.
Your next move
Run the entire project:
python load_raw.py dbt debug --profiles-dir . dbt build --profiles-dir . dbt show --select customer_revenue --profiles-dir . dbt docs generate --profiles-dir . dbt docs serve --profiles-dir .
Then break it on purpose.
Introduce an orphaned customer key and watch the relationship assertion fail. Change the recognition rule and verify that the business-invariant test catches it. Add a one-to-many join and see whether your reconciliation test protects the final metric.
Finally, take one real SQL transformation from your own analytics stack and apply the same workflow:
-
state its grain in one sentence;
-
replace hidden physical dependencies with
source()andref(); -
test the grain;
-
test important relationships;
-
add at least one assertion about business meaning;
-
document the metric definition;
-
inspect the resulting lineage;
-
run the entire build in CI.
That is the point where ELT stops being an architecture diagram and becomes an engineering practice you can actually trust.