Governed dbt Warehouse.

The idea

A warehouse you can trust is one where every number is tested

This project takes the public Olist e-commerce dataset, eight raw CSVs, and turns it into a governed, fully tested warehouse with dbt. The point is not the transformations. It is the discipline around them: tests, lineage, and reconciliation that catch a wrong number before it reaches a dashboard.

It is the same conviction behind my Governed Analytics Agent, expressed in the modern analytics-engineering stack: metrics and models defined once, with the data-quality checks that keep them trustworthy built into the pipeline rather than bolted on after.

Architecture

Raw to marts to consumers, one governed lineage

◎ ref() builds the DAG. dbt knows every dependency, so lineage runs from a raw CSV all the way to the AI agent that consumes the marts, declared as an exposure.

Sources are declared with freshness thresholds. Staging models clean and type each source into one view per entity. Marts assemble the dimensional layer: dim_customers, dim_products, dim_sellers, an order-grain fct_orders, an incremental fct_order_items, and a certified fct_daily_order_metrics.

Modeling

Roll item money up to the order once, so it cannot drift

Order revenue is computed a single time, in fct_orders, by rolling up line-item price. Every downstream consumer reads that one definition, no consumer recomputes revenue its own way.

item_rollup as (
    select
        order_id,
        sum(price)         as order_revenue,   -- defined once, here
        sum(freight_value) as order_freight,
        count(*)           as item_count
    from {{ ref('stg_order_items') }}
    group by order_id
)

The high-volume line-item fact is incremental: after the first build, only new purchase dates are processed, so refreshing years of data stays cheap.

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

select * from joined
{% if is_incremental() %}
  -- only items whose order is newer than what we already loaded
  where purchase_date > (select max(purchase_date) from {{ this }})
{% endif %}

Product attributes are captured as a Type 2 snapshot, so if a product's category ever changes, history is preserved instead of overwritten.

{% snapshot products_snapshot %}
{{ config(unique_key='product_id',
          strategy='check',
          check_cols=['category', 'weight_g', 'photos_qty']) }}
select * from {{ ref('stg_products') }}
{% endsnapshot %}

Data quality

The reconciliation test is the one that matters

Generic tests cover the basics: keys are unique and not null, foreign keys resolve, statuses and review scores fall in their allowed set, and money columns are never negative (a custom generic test). But the check I care most about is a singular reconciliation test: order-grain revenue must equal item-grain revenue, to the cent.

-- Fails the build if the order rollup and the line items disagree
-- by more than a cent. "Looks done" vs "actually reconciled".
with orders_total as (
    select sum(order_revenue) as total from {{ ref('fct_orders') }}
),
items_total as (
    select sum(price) as total from {{ ref('fct_order_items') }}
)
select * from orders_total o cross join items_total i
where abs(o.total - i.total) > 0.01

A zero gap is the expected result, and that is the point: the test exists so that the day a rollup silently doubles or a join drops rows, the gap becomes non-zero and the build fails before anyone reads the number. A companion test asserts there are no orphan line items pointing at a missing order.

Reproducible build

One command builds and proves the whole warehouse

dbt build runs every model, snapshot, and test in dependency order, and stops if any check fails. This run is green end to end:

$ dbt build

  1 incremental model, 1 snapshot, 5 table models,
  7 view models, 57 data tests, 2 exposures
  ...
  PASS assert_order_revenue_ties_to_items ......... [PASS]
  PASS assert_no_orphan_order_items ............... [PASS]
  ...
  Completed successfully
  Done. PASS=71 WARN=0 ERROR=0 SKIP=0

And dbt docs generate produces a browsable catalog with the full lineage graph, so anyone can trace a column on a mart back to the raw CSV it came from.

What it demonstrates

Production-grade dbt, end to end

Stack: dbt · DuckDB · SQL · Jinja · YAML. Built on the Olist Brazilian E-Commerce public dataset (~100k real orders). Used purely to demonstrate method on real, public data. No proprietary or employer data is involved.