My progress
Menu

Data Analyst / Engineer · Capstone

DT10: Capstone: three data projects for SabziSathi

Goal: bring the whole path together by building, testing and presenting the three path projects: a pipeline that loads messy raw files reliably and refreshes on a schedule, a well-modelled warehouse with personal data protected, and a dashboard that colleagues who aren't technical can trust, all in the SabziSathi practice kit.

You'll need: everything from DT1–DT9, the SabziSathi practice kit (download it from this path's Projects page), and Python 3.11 or later. No cloud account is needed.

Where this is used: this mirrors real data work: taking over a fragile loader that someone wrote in a hurry, turning a handful of queries into a tested model, and replacing a dashboard that impresses with one that informs. A portfolio with these three, backed by passing tests and checked numbers, shows employers more than a list of tools.

How it differs: each earlier module taught one skill. The capstone tests whether you can combine them: the warehouse's numbers depend on the pipeline's cleaning, the dashboard depends on the warehouse's definitions, and a mistake in one shows up as a wrong number two steps later.

Time: reading about 2 hours · practice (the Try it tasks) about 2 hours · projects and capstone about 40 to 50 hours in all.

Lessons

  1. 1Planning the capstoneReading 15–25 minutes, plus practice✓
  2. 2Project 1: a pipeline you can trustReading 15–25 minutes, plus practice✓
  3. 3Project 2: a warehouse with personal data protectedReading 15–25 minutes, plus practice✓
  4. 4Project 3: a dashboard people can trustReading 15–25 minutes, plus practice✓
  5. 5Demo, self-review and case studiesReading 15–25 minutes, plus practice✓

🛠️Assignment: Complete and present the capstone

This is the final project of the Data Analyst / Engineer path.

  1. Write the capstone plan with acceptance criteria, a timeline and the overview table.
  2. Project 1: make the pipeline reliable until the 10 pipeline tests pass.
  3. Project 2: build the tested, documented warehouse with personal data protected until the 10 warehouse tests pass.
  4. Project 3: rebuild the dashboard until the 8 dashboard tests pass, and test it with a reader who isn't technical.
  5. Run the capstone checks on the Projects page, record the demo, complete the self-review, and write three case studies and the design notes.

Self-check before you finish:

✅Module check

Explain in a few sentences, in your own words:

How do the three capstone projects show you can do professional data work?

Show a good answer

A good answer mentions: planning with acceptance criteria, a sensible order and evidence; cleaning rules that read every format on purpose, money in paise, quarantine with reasons and counts that add up; idempotent, scheduled, logged loads that fail loudly; layered warehouse models with a declared grain, correct measures, type 2 history, salted keys and no personal data in the marts, with tests, freshness, incremental builds and documentation; a dashboard built from the marts with defined metrics, honest charts, text alternatives, suppressed small counts and a freshness line; and a demo of failures, an honest self-review, design notes and case studies with labelled figures.

📚Official references

Official documentation and guidance for this module's topics, chosen on 2 November 2026. Tools, laws and standards change, so check the current version before you rely on a detail. If a link has moved, search the same site.

📝Module quiz

10 questions · 10 marks each · 100 marks in all · pass mark 70% · retry as often as you like ·

1. A learner wants to start the capstone with the dashboard because it's what people see. What's the best advice?

Why each option is right or wrong

  • “Start with the dashboard, then fix the numbers in its SQL” (Incorrect): Its numbers come from upstream; patching them in the dashboard hides the real faults.
  • “Do all three at once to save time” (Incorrect): Each project builds on the one before, so parallel work causes rework.
  • “Skip Project 1, since the warehouse can clean the data in SQL” (Incorrect): Some cleaning could move to SQL, but quarantine, counts and safe loads still need building.
  • “Start with Project 1, which the others depend on” is correct.

    Wrong cleaning makes every later number wrong. Pipeline, warehouse, then dashboard avoids chasing errors in the last step.

    • Order: pipeline, warehouse, dashboard.
    • Saves: chasing wrong numbers in the last step.

📖 Revise: Lesson 1: Planning the capstone

Question ID sp-data-10-q1 · Report a mistake in this question
2. Project 1 test 6 fails: the load log shows 329 more quarantined rows than expected. What's the likely mistake?

Why each option is right or wrong

  • “The quarantine table needs a primary key” (Incorrect): A key wouldn't change which rows are put there.
  • “Test orders should also be quarantined” (Incorrect): That would add 60 more, not 329, and they're left out on purpose.
  • “The raw files have changed since the tests were written” (Incorrect): The raw files are fixed by the generator's seed.
  • “Repeats are quarantined instead of counted” is correct.

    Repeats of a resent row aren't rule failures. Count them in rows_duplicate and keep quarantine for rows that break a rule.

    • Load log: 329 in rows_duplicate, 90 in quarantine for the month.
    • Test 6: expects exactly 90 quarantined.

📖 Revise: Lesson 2: Project 1: a pipeline you can trust

Question ID sp-data-10-q2 · Report a mistake in this question
3. A learner's test 10 passes, but their cron runs at 30 6 * * * with the comment "6:30 a.m.". What's wrong?

Why each option is right or wrong

  • “Nothing, since the test passed” (Incorrect): The test checks the expression's shape, not the time zone you meant.
  • “Cron can't run at half past the hour” (Incorrect): It can; minute 30 is fine.
  • “The job must run at midnight to include the whole day” (Incorrect): Any time after the file arrives works.
  • “GitHub schedules run in UTC, so this is noon in Pune” is correct.

    06:30 UTC is 12:00 IST. For 06:00 in Pune, use 30 0 * * *.

    • Comment: # 00:30 UTC = 06:00 in Pune.
    • Check: convert every schedule from UTC.

📖 Revise: Lesson 2: Project 1: a pipeline you can trust

Question ID sp-data-10-q3 · Report a mistake in this question
4. Project 2 test 4 fails with "keys can be rebuilt without a secret". The key is sha256(cast(customer_id as varchar)). What fixes it?

Why each option is right or wrong

  • “Use MD5 instead of SHA-256” (Incorrect): MD5 is just as easy to rebuild from guessed ids.
  • “Hash the phone number instead of the customer id” (Incorrect): Phone numbers can be guessed too.
  • “Hash the id twice” (Incorrect): Two unsalted hashes can be rebuilt just as easily.
  • “Add a secret salt before hashing” is correct.

    With a salt from SABZI_KEY_SALT, nobody can rebuild the keys by guessing ids.

    • Salt: read with env_var('SABZI_KEY_SALT') in the macro.
    • Test 4: also checks the salt isn't in any file.

📖 Revise: Lesson 3: Project 2: a warehouse with personal data protected

Question ID sp-data-10-q4 · Report a mistake in this question
5. After adding the area history, fct_order_lines has 10,882 rows instead of 10,181. What's the most likely cause?

Why each option is right or wrong

  • “The pipeline loaded some days twice” (Incorrect): The pipeline's counts didn't change; the join did.
  • “dbt's incremental model added new rows” (Incorrect): The extra rows appear on a full build too.
  • “dim_date has duplicate days” (Incorrect): The fact isn't joined to dim_date in that model.
  • “The join ignores the order time” is correct.

    Each customer who moved has two history rows, so their lines are copied. Join on the key and the order time.

    • Condition: ordered_at >= valid_from and (valid_to is null or ordered_at < valid_to).
    • Check: test 2's count of 10,181.

📖 Revise: Lesson 3: Project 2: a warehouse with personal data protected

Question ID sp-data-10-q5 · Report a mistake in this question
6. A learner's warehouse passes every test except test 10, which lists mart_daily_sales.orders. What's needed?

Why each option is right or wrong

  • “A unique test on the orders column” (Incorrect): Test 10 checks documentation, not tests.
  • “Renaming the column to order_count” (Incorrect): The name isn't the problem.
  • “Removing the column from the mart” (Incorrect): The dashboard and readers may need it; describe it instead.
  • “A description for that column” is correct.

    In the marts' .yml: test 10 checks every mart column has a description, saying what it counts and what it includes.

    • Example: "Orders placed, whatever their status."
    • Test 10: lists every column still missing one.

📖 Revise: Lesson 3: Project 2: a warehouse with personal data protected

Question ID sp-data-10-q6 · Report a mistake in this question
7. A learner's dashboard shows an average order value of 67,298 paise; test 2 expects 67,367. What did they do?

Why each option is right or wrong

  • “Rounded each order to the nearest rupee” (Incorrect): Rounding gives a much smaller difference than this.
  • “Included cancelled orders in the average order value” (Incorrect): That would also change revenue, which matches.
  • “Used the wrong month's data” (Incorrect): Revenue and orders match October.
  • “Averaged the averages instead of dividing totals” is correct.

    An average of averages weights small days like big ones. Divide summed net revenue by summed delivered orders.

    • Right: sum(net_revenue_paise) / sum(delivered_orders).
    • Result: 67,367 paise, ₹673.67.

📖 Revise: Lesson 4: Project 3: a dashboard people can trust

Question ID sp-data-10-q7 · Report a mistake in this question
8. A learner's moves table hides counts below 5, then adds a last row, "Total moved in: 40". What's the concern?

Why each option is right or wrong

  • “Totals must be shown in lakh” (Incorrect): Units aren't the issue.
  • “The total should be hidden because 40 is a small number” (Incorrect): 40 is above the threshold; the risk is working out hidden cells.
  • “The table needs a pie chart instead” (Incorrect): A pie wouldn't fix the privacy issue.
  • “A hidden count could be worked out from the total” is correct.

    If few cells are hidden, the total minus the visible cells reveals them. Leave the total out, or hide more cells.

    • Kit: the solution shows no total row.
    • Rule: check what can be worked out, not just what's shown.

📖 Revise: Lesson 4: Project 3: a dashboard people can trust

Question ID sp-data-10-q8 · Report a mistake in this question
9. Which piece of evidence best shows a reviewer you understand the whole system?

Why each option is right or wrong

  • “A screenshot of the dashboard looking finished” (Incorrect): It shows the result, not how it's made or how it fails.
  • “A list of every tool used” (Incorrect): Tools don't show understanding.
  • “The number of lines of code written” (Incorrect): Size isn't quality.
  • “Tracing one dashboard number back to raw rows” is correct.

    It shows you know how each step changes the data and where a fault would show.

    • Example: change one raw line, re-run, and show the dashboard follow.
    • Shows: you know each step's effect.

📖 Revise: Lesson 5: Demo, self-review and case studies

Question ID sp-data-10-q9 · Report a mistake in this question
10. A learner's self-review says "Works on BigQuery" but they only estimated costs from the price list. What should it say?

Why each option is right or wrong

  • “Leave it, since BigQuery uses the same SQL as DuckDB” (Incorrect): The dialects differ, and nothing was run.
  • “Remove BigQuery from the design notes entirely” (Incorrect): The design is still useful.
  • “"Works on BigQuery with minor changes"” (Incorrect): That still claims something that wasn't checked.
  • “"Not verified": costed but not run” is correct.

    Honest labels make the rest of the portfolio more credible.

    • Then: add the plan to try it on BigQuery's free allowance.
    • Same for: Kafka, Spark and BI tools.

📖 Revise: Lesson 5: Demo, self-review and case studies

Question ID sp-data-10-q10 · Report a mistake in this question

ProLearners Assist