Data Analyst / Engineer · Projects

Project 2: A warehouse with personal data protected

Marking guide version 1 · Lessons: DT5 lessons 2 and 5, DT6 lessons 1–5, DT7 lessons 1–5, DT10 lesson 3

The problem and who it's for

SabziSathi's dbt project builds, but it passes customers' names, phone numbers and emails into the marts, keys customers by an unsalted MD5 of their phone number, gives every order the area from the September export even after customers moved, counts cancelled and refunded lines as revenue, has no tests or freshness checks, and is almost undocumented. The analysts who use it can't tell which numbers to trust, and any one of them can read every customer's phone number.

Scope

• Stop personal data in staging; build dim_customers with a salted SHA-256 key from SABZI_KEY_SALT and signup_month.
• Build dim_customer_area_history (type 2, half-open periods) and give each order the area at the time it was placed.
• Put net_amount_paise in the fact and net revenue and delivered orders in mart_daily_sales; add mart_area_moves.
• Add tests (grain, keys, accepted values, relationships, a reconciliation), sources with freshness, and descriptions for every mart column.
• Make fct_order_lines incremental with a unique key and a look-back.

Not included

• A cloud warehouse (the capstone's design notes cover it).
• dbt Cloud or a semantic layer.
• Real customer data of any kind.

Before you start

• Core: Module 53 (Relationships and ER Diagrams), Module 54 (Normalisation), Module 56 (Advanced Queries), Module 79 (Privacy and Data Protection).

• Path: DT5, DT6 and DT7 complete, and Project 1 finished (the warehouse's numbers depend on the pipeline).

• pytest tests/test_warehouse.py running (8 of its 10 tests fail at first).

Starter files

• sabzisathi/warehouse/models/staging/stg_customers.sql: passes every column of the customer export on.
• sabzisathi/warehouse/models/marts/dim_customers.sql: md5(phone) as the key, with names, phones and emails.
• sabzisathi/warehouse/models/marts/fct_order_lines.sql: a full rebuild, the export's area, and every line counted as revenue.
• sabzisathi/warehouse/models/marts/mart_daily_sales.sql: revenue including cancellations, and an average per day and store.
• sabzisathi/warehouse/models/staging/sources.yml and sabzisathi/warehouse/models/marts/schema.yml: no tests, freshness or descriptions.
• sabzisathi/tests/test_warehouse.py: the 10 acceptance tests for this project.

Download everything at once: starter/data-starter.zip.

Milestones

1. Run pytest tests/test_warehouse.py and note which of the 10 tests fail and why.
2. Fix staging and the customer key (tests 3 and 4).
3. Build the area history and join it on the order time (test 6).
4. Fix the measures and marts (test 7), and add tests and the reconciliation (test 2).
5. Add sources with freshness, the incremental fact and descriptions (tests 8 to 10).
6. Run all 10 tests twice in a row.

Expected outputs

• 10 of 10 warehouse tests passing, twice in a row.
• dbt build passing every test, and the docs site generated.
• docs/model.md with the grain, the star schema and how to total each measure.

Acceptance tests

Failure and recovery exercises

1. Write the salt into dbt_project.yml as a variable and run test 4. Explain the failure, then move it back to the environment.
2. Join the fact to the history on the customer key only and count the rows. Explain the difference, then restore the time condition.
3. Remove unique_key from the incremental fact, reload a day and rebuild. Describe what happens to the totals, then restore it.

Evidence to submit

• The deliverable link to your repository or folder with the warehouse changes.
• The output of pytest tests/test_warehouse.py showing 10 passed, pasted into test evidence.
• The dbt build summary and a screenshot of the lineage graph from dbt docs.
• Your explanation: where personal data stops, how the customer key is made and kept safe, and how an order gets the right area.

Marking guide and self-check

Pass rule: every Safety-critical (S) and Mandatory (M) item must be met. A single unmet safety item means the project is not complete, whatever else is done. Quality items are for your own improvement.

Your answers are saved in this browser on this device. Self-checked means you checked your own work against this guide; it is not a reviewer's approval. Never paste passwords, keys, tokens or real customer data here.