Build the mart, add dbt tests, and find the gap

Step 1: The profitability mart

The staging layer is clean. Three models, three sources, all row counts verified. Now the business logic.

Mihai's question is straightforward: which cheese makes money? Answering it requires joining all three sources. Milk purchases tell you what the milk cost. Production tells you how much cheese came out of that milk. Sales tell you what the cheese sold for. The mart model connects these three layers and calculates profitability per variety.

Open materials/pipeline-spec.md and review the target outputs. The mart model fct_variety_profitability needs to produce one row per variety with: total milk cost, total revenue, yield (cheese kilos out per milk kilos in), and profit margin. The fct_ prefix signals this is a business-level table -- unlike stg_ models that clean individual sources, this one joins them and calculates.

The grain matters. One row per variety means aggregating across all batches, all sales, and all purchases for each cheese type. If the grain drifts to one-row-per-batch or one-row-per-customer, the profitability numbers will be wrong in ways that are hard to spot.

Step 2: Build fct_variety_profitability

Direct Claude to build the mart model. This is the first model that uses ref() to pull from other dbt models rather than source() to pull from raw tables. The dependency declarations are what make the DAG meaningful -- dbt knows to build staging models before the mart because ref() tells it so.

Write a mart model at branzeria_carpati/models/marts/fct_variety_profitability.sql. It should use ref('stg_production'), ref('stg_sales'), and ref('stg_purchases') to join all three staging models. Calculate per variety: total milk cost (from purchases, aggregated by variety through the shepherd-production link), total revenue (from sales), yield (total kilos cheese out / total kilos milk in), and profit margin ((revenue - milk cost) / revenue). The output grain is one row per cheese variety. Handle null aging_end_date values -- about 20% of production records have no end date.

Review what Claude produces. The joins are where AI commonly makes mistakes. The connection between purchases and production runs through shepherd_name -- the same shepherd who sold the milk produced the batch. The connection between production and sales runs through variety -- there is no batch-level link in the sales data. Check that these join paths make sense against what you know about the data from profiling in Unit 2.

Step 3: Run the mart and check the output

Run the full pipeline:

dbt run

This time, dbt builds four models. The output shows the execution order -- staging models first, mart model last. The order is not arbitrary. dbt reads the ref() declarations in fct_variety_profitability and determines that all three staging models must exist before the mart can build. This is the DAG in action: declared dependencies determine build order automatically.

Query the mart to see what it produced:

SELECT * FROM fct_variety_profitability ORDER BY profit_margin DESC;

Check three things. First, row count: there should be exactly six rows, one per variety. If the count is different, the aggregation grain is wrong. Second, the six varieties should match Mihai's list: telemea, cascaval, branza de burduf, urda, cas, nasal. Third, scan the revenue and cost columns -- do the numbers look plausible for a small artisan cheese operation in rural Romania?

Step 4: Introduce dbt tests

The mart is built and the numbers exist. Before verifying those numbers, add automated quality gates.

A dbt test does not test your code. It tests the data. When you add unique on variety in the mart, you are asserting: "the output of this model must never contain duplicate varieties." The model could run without errors and still fail the test -- if the aggregation logic accidentally produces two rows for telemea, the code ran fine but the data is wrong.

dbt has four built-in test types, each making a different kind of assertion:

unique verifies no duplicate values in a column. On batch_number in staging, this catches duplication bugs. On variety in the mart, this verifies the grain.

not_null asserts that a column always has a value. This is a business assertion, not a schema constraint. Adding not_null on shepherd_name in production says "every batch must be traceable to a shepherd." The meaningful not_null tests are on columns that could plausibly be null due to transformation logic, not columns the database already guarantees.

accepted_values encodes business rules. A variety column should only contain the six cheese types Mihai produces. If AI's transformation logic maps an unexpected source value to "other" or introduces a typo, this test catches it.

relationships verifies referential integrity between models. Every shepherd_name in production should exist in purchases -- if a shepherd appears in the production log but not in the purchase records, something is wrong with the data or the join logic. AI commonly generates joins that silently drop records with missing keys. A relationships test makes that failure visible.

Step 5: Add tests to schema.yml

Direct Claude to add the tests:

Add dbt tests to branzeria_carpati/models/schema.yml. For stg_production: unique on batch_number, not_null on batch_number, variety, and shepherd_name. For all models with a variety column: accepted_values with telemea, cascaval, branza_de_burduf, urda, cas, nasal. For stg_production and stg_purchases: a relationships test verifying that every shepherd_name in stg_production exists in stg_purchases. For fct_variety_profitability: unique on variety, not_null on variety and profit_margin.

AI commonly adds tests in two problematic patterns. First, tautological tests -- unique and not_null on primary key columns where the database already enforces these constraints. Those tests pass by definition and verify nothing. Second, tests only on the mart but not on staging models -- failures at the source layer propagate unchecked into the mart. Check that Claude added tests at both layers.

The act of writing these tests is also an act of declaring what "correct" means for this pipeline. Anyone reading schema.yml can see exactly what this pipeline guarantees: no duplicate batches, no null shepherds, only six known varieties, and every shepherd traceable to a purchase record. The test suite is an executable specification.

Step 6: Run dbt test

Run the test suite:

dbt test

Read the output. Each test runs independently and reports pass or fail. The summary at the end tells you how many passed, how many failed, and how many were skipped.

If any test fails, read the failure message carefully. A failed relationships test tells you exactly which values in the child model have no match in the parent. A failed accepted_values test tells you which unexpected values appeared. Fix the underlying issue -- not the test -- and re-run.

Step 7: Verify profitability against the checklist

All tests pass. The pipeline is structurally correct -- varieties are unique, required fields are present, accepted values match, referential integrity holds.

Now open materials/verification-checklist.md and compare the profitability numbers against the expected margin ranges. Pick at least two varieties and check: does the calculated margin fall within the expected range?

AI commonly handles null aging_end_date values incorrectly. About 20% of production records have no end date -- batches still aging. If the mart model uses null arithmetic on those records (subtracting a null date produces null, which propagates through the cost calculation), the aging cost for those batches drops to zero. That systematically overstates profitability for varieties with long aging times -- cascaval and branza de burduf in particular.

The dbt tests did not catch this. unique on variety passed. not_null on profit margin passed. accepted_values on variety passed. Every structural test is green. But the numbers may be wrong because the tests verify structure, not business meaning. This is the gap: "all tests pass" and "the numbers are right" are different statements.

If the profitability values for cascaval or branza de burduf fall outside the expected range, the aging duration calculation is the likely culprit. Direct Claude to fix the logic:

The profitability numbers for varieties with long aging times look too high. Check how fct_variety_profitability handles production records where aging_end_date is null. If the model uses null arithmetic that drops aging cost to zero for those batches, fix it -- use CURRENT_DATE as the end date for batches still aging, so the aging cost estimate reflects reality.

Re-run dbt run and dbt test after the fix. Verify the corrected values against the checklist.

Step 8: Test idempotency

Run dbt run a second time:

dbt run

Query the mart again:

SELECT * FROM fct_variety_profitability ORDER BY profit_margin DESC;

The output should be identical to the previous run -- same six rows, same profitability values, same row counts in every model. No duplicates, no changes.

In P1, re-running an INSERT-based script doubled the data. In P2, the same risk existed with API extraction. You had to handle idempotency manually -- MERGE patterns, DELETE-then-INSERT, or checking for existing records before loading. dbt solves this by design. Every model uses CREATE OR REPLACE by default. Re-running the pipeline rebuilds each table from scratch, producing the same output whether it runs once or ten times. The framework encodes the property that scripts required manual effort to achieve.