DT10: Capstone: three data projects for SabziSathi
Project 2: a warehouse with personal data protected
🎯You'll be able to
Turn the starter's dbt project into a tested, documented star schema with personal data protected, until all 10 warehouse tests pass.
🏠The everyday picture
A hospital's records room files every visit once, in the right patient folder, with the doctor's notes kept apart from the billing desk. Any question about visits can be answered from the files, and the billing clerk never sees the diagnosis.
📘What it really is
Project 2 asks you to fix:
-
personal data (DT5 Lesson 5, DT6 Lesson 5):
stg_customerspasses names, phone numbers and emails on, anddim_customerskeys customers bymd5(phone). Fix: only id, area and signup date in staging; acustomer_keymacro with a salted SHA-256 fromSABZI_KEY_SALT;signup_monthinstead of the date; -
history (DT6 Lesson 4): orders get the customer's area from the export. Fix:
dim_customer_area_history(type 2, half-open periods) and a time-range join forarea_at_order; -
measures (DT6 Lesson 3): "revenue" includes cancelled and refunded lines, and the average is computed per day and store. Fix:
net_amount_paisein the fact, net revenue and delivered orders as separate columns inmart_daily_sales; -
tests, sources and docs (DT7): no tests, no freshness, almost no descriptions. Fix: grain, key, value and relationship tests, a reconciliation test, freshness on
order_lines, and a description for every mart model and column; -
materialisation (DT7 Lesson 4): the fact is rebuilt in full. Fix: incremental with
unique_key='order_line_id'and a 3-day look-back; - the moves mart: add
mart_area_movesfor Project 3.
📝A worked example
Input: after your changes, with SABZI_KEY_SALT set, run cd warehouse && dbt build --profiles-dir ., then cd .. and
pytest tests/test_warehouse.py.
Expected output: dbt ends with Done. PASS=37 WARN=0 ERROR=0 for the reference solution (your count may differ slightly with your own
tests), and pytest shows 10 passed. mart_daily_sales gives 3,411 orders, 2,918 delivered and ₹19,65,764 net revenue for October.
Measured on 1 November 2026.
🌍Where it's used
- Analytics engineering: moving reporting logic into a tested dbt project.
- Privacy reviews: showing where personal data stops in the warehouse.
- Finance and audit: reconciliation tests between marts and facts.
📝Case scenario
A learner's test 6 fails for a few hundred orders: their area history used valid_from <= ordered_at and ordered_at <= valid_to, so orders
placed exactly when the history's next row began matched two rows, and their store and area no longer matched. Switching to half-open
periods (ordered_at < valid_to) fixed it, and test 2's row count came back to 10,181.
🖱️Try it
Before writing any SQL, run pytest tests/test_warehouse.py -k "03 or 04" and read the two failures. Then fix stg_customers and
dim_customers only, and run them again. Success: both pass, and you can explain why md5(phone) failed test 4.
⚠️Common mistakes
- Putting the salt in a file. Sign: test 4 fails with "the salt must not be stored". Fix: an environment variable only.
- Joining history without the time condition. Sign: 10,882 lines instead of 10,181. Fix: join on the key and the time range.
- Describing only some columns. Sign: test 10 lists missing descriptions. Fix: every column of every mart.
🛠️For your project
Save the dbt build summary, the warehouse test output and a lineage screenshot from dbt docs on the Project 2 page.
✅Quick check
Why is md5(phone) not a safe key?
Show the answer
Anyone can hash every possible phone number and match the results.
What does test 6 check?
Show the answer
Each order's area, taken from the history at the time of ordering, is served by the order's store.