My progress
Menu

Data Analyst / Engineer · Focus area 2: Data warehousing

DT5: Warehouse foundations

Goal: explain why analysis needs a different kind of database, organise a warehouse into layers, understand how cloud warehouses such as BigQuery and Snowflake charge and scale, write queries that read less, and control who can see which data, personal data above all.

You'll need: DT1 to DT3, Module 52 (Database Structure), Module 54 (Normalisation), Module 57 (Transactions and Indexes), Module 68 (Cloud Architecture), Module 79 (Privacy and Data Protection), and the SabziSathi practice kit.

Where this is used: a bank's finance team asks questions across ten years of transactions; a quick-commerce company's analysts compare stores, hours and products; a state health department combines hospital reports. Their apps' databases weren't built for these questions, so the data is copied into a warehouse that is.

How it differs: an app's database is tuned to read and write one record at a time, quickly and safely. A warehouse is tuned to read millions of rows at once and summarise them, and its design decides what each question costs.

Time: reading about 2½ hours · practice (the Try it tasks) about 2 hours · assignment about 3 hours.

Lessons

  1. 1Why analysis needs a warehouseReading 15–25 minutes, plus practice✓
  2. 2Layers and schemasReading 15–25 minutes, plus practice✓
  3. 3Cloud warehousesReading 15–25 minutes, plus practice✓
  4. 4Queries that read lessReading 15–25 minutes, plus practice✓
  5. 5Access, governance and personal dataReading 15–25 minutes, plus practice✓

🛠️Assignment: SabziSathi's warehouse plan

This is the first part of Project 2.

  1. Draw SabziSathi's layers (raw, clean, staging, marts) with the schema and tool for each.
  2. Classify every column in the raw files by sensitivity.
  3. Write the roles you'd create in a cloud warehouse (for example pipeline, analyst, support) and what each can read and write.
  4. Rewrite the starter's stg_customers so only the customer id, area and signup date pass on.
  5. Write an illustrative monthly cost for SabziSathi on BigQuery or Snowflake, with the date and the pricing page you used.

Self-check before you finish:

✅Module check

Explain in a few sentences, in your own words:

What makes a warehouse fast, affordable and safe?

Show a good answer

A good answer mentions: columnar storage separate from the app's database; layers in separate schemas, with reports on marts only; storage and compute billed separately, with on-demand scanning or sized compute; partitioning, clustering, selected columns and early filters; summary marts for common questions; budgets and limits; classification of data; least-privilege roles; personal data minimised, pseudonymised and aggregated; retention and deletion; and the duties of laws such as India's DPDP Act.

📚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. An e-commerce site's checkout slows down at 9 a.m. every Monday, when the weekly sales report runs. What's the best fix?

Why each option is right or wrong

  • “Run the report at 3 a.m. instead, when fewer people shop” (Incorrect): It moves the slowdown to quieter customers; the report still runs on the live database.
  • “Add more indexes to the order table so the report finishes faster” (Incorrect): Indexes help find a few rows; a weekly report reads most of them, and each index slows writes.
  • “Limit the report to the first 1,000 orders of the week” (Incorrect): That gives a wrong report.
  • “Copy orders to a warehouse and run the report there” is correct.

    Long analytical queries belong on a copy built for them, so the app's database serves customers only.

    • Also: the warehouse can keep history and join other sources.

📖 Revise: Lesson 1: Why analysis needs a warehouse

Question ID sp-data-05-q1 · Report a mistake in this question
2. A query asks for total revenue per store from a 40-column table in a columnar warehouse. Roughly how much of the table does it read?

Why each option is right or wrong

  • “All 40 columns, because every row must be read in full” (Incorrect): That's how row stores work; columnar stores read columns separately.
  • “Only the first row of each store, to save time” (Incorrect): A total needs every row's revenue.
  • “Only the index, not the table itself” (Incorrect): Totals need the values, not just an index.
  • “Just the store and revenue columns, for every row” is correct.

    Columnar storage keeps each column together, so a query reads only the columns it names.

    • Why fast: two columns of a few bytes each, instead of whole rows.
    • Also: repeated store codes compress very well.

📖 Revise: Lesson 1: Why analysis needs a warehouse

Question ID sp-data-05-q2 · Report a mistake in this question
3. A source system renames a column, and 30 dashboards break. They all read from staging models. What layering change prevents this?

Why each option is right or wrong

  • “Ask the source team to stop renaming columns” (Incorrect): Sources change; a good design absorbs it.
  • “Copy each dashboard's query into a spreadsheet as a backup” (Incorrect): Backups don't stop them breaking.
  • “Merge staging and marts into one schema so there are fewer layers” (Incorrect): Fewer layers means less protection from source changes.
  • “Point dashboards at marts, with staging absorbing source changes” is correct.

    When staging maps the new name to the old meaning, marts and dashboards don't need to change at all.

    • Staging: maps the source's new column name to the name marts expect.
    • Result: one model changes; 30 dashboards don't.

📖 Revise: Lesson 2: Layers and schemas

Question ID sp-data-05-q3 · Report a mistake in this question
4. An analyst runs select * from events limit 10 on a 4 TB table in BigQuery with on-demand pricing. What does it cost?

Why each option is right or wrong

  • “Almost nothing, since only 10 rows come back” (Incorrect): On-demand pricing charges for data scanned, and LIMIT doesn't reduce the scan here.
  • “Nothing, because previews are free in every warehouse” (Incorrect): The console's table preview can be free; a query with LIMIT is billed by the scan.
  • “The cost of 10 rows times the number of columns” (Incorrect): Billing is by bytes scanned, not rows returned.
  • “About a full 4 TB scan” is correct.

    Every column of every row is read before 10 are returned.

    • Cheaper: select the few columns needed, filtered to one day's partition.
    • Also: use the table's free preview in the console.

📖 Revise: Lesson 3: Cloud warehouses

Question ID sp-data-05-q4 · Report a mistake in this question
5. A Snowflake bill shows a medium warehouse running all weekend with no queries. What's the fix?

Why each option is right or wrong

  • “Move the data to a cheaper storage tier for weekends” (Incorrect): The cost here is compute, not storage.
  • “Use a larger warehouse so queries finish sooner” (Incorrect): Bigger warehouses cost more per hour, and nothing was running.
  • “Delete old tables to reduce the weekend bill” (Incorrect): Storage is billed separately and wasn't the problem.
  • “Turn on auto-suspend after a few idle minutes” is correct.

    Snowflake bills compute while a warehouse runs. Auto-suspend stops it when idle and resumes when a query arrives.

    • Also: set a budget alert for credits used.
    • Size: start small and resize only when queries are slow.

📖 Revise: Lesson 3: Cloud warehouses

Question ID sp-data-05-q5 · Report a mistake in this question
6. A daily report joins five years of tracking events to shipments, then keeps only yesterday's rows. It takes 40 minutes. What's the

Why each option is right or wrong

  • “Add more compute so the join finishes sooner” (Incorrect): It helps, at a cost, while the query still processes five years.
  • “Run the report weekly instead of daily” (Incorrect): It still takes 40 minutes, and the report is later.
  • “Remove the shipments table from the query” (Incorrect): The report needs shipment details.
  • “Filter events to yesterday before the join” is correct.

    Filtering first means the join handles one day's events instead of five years'.

    • Check: the plan's row counts before and after the join.
    • Also: select only the columns the report needs.

📖 Revise: Lesson 4: Queries that read less

Question ID sp-data-05-q6 · Report a mistake in this question
7. A dashboard shows daily revenue per store and is opened 500 times a day. Each view sums a 50-million-row fact table. What's the best

Why each option is right or wrong

  • “Cache the dashboard in each browser for a week” (Incorrect): Revenue would be a week old.
  • “Give the dashboard its own copy of the fact table” (Incorrect): A copy is just as big to sum.
  • “Add LIMIT 100 to the dashboard's query” (Incorrect): That changes the answer, and may not reduce the scan.
  • “A daily summary table that the dashboard reads” is correct.

    Summing once a day into a small mart answers every view cheaply with the same numbers.

    • Kit: mart_daily_sales has 90 rows; the fact table has 10,181.
    • Same numbers: the mart is built from the fact.

📖 Revise: Lesson 4: Queries that read less

Question ID sp-data-05-q7 · Report a mistake in this question
8. A warehouse's marts table of orders includes customers' phone numbers "in case analysts need them". What's the best change?

Why each option is right or wrong

  • “Keep them, but ask analysts not to look at them” (Incorrect): A request doesn't stop access, copying or leaks.
  • “Encrypt the column with a key every analyst can use” (Incorrect): If every analyst has the key, it protects very little.
  • “Move the table to a schema with a less obvious name” (Incorrect): Hiding the name doesn't limit who can read it.
  • “Remove them from marts; link orders with a pseudonymous key” is correct.

    Data that isn't copied can't leak from there. A pseudonymous key still lets analysts count and follow customers.

    • If someone truly needs phones: a separate, restricted table with access logged.

📖 Revise: Lesson 5: Access, governance and personal data

Question ID sp-data-05-q8 · Report a mistake in this question
9. Under India's Digital Personal Data Protection Act, 2023, which practice fits its expectations for a company's warehouse?

Why each option is right or wrong

  • “Keeping every customer's details for ever, in case they're useful later” (Incorrect): The law expects personal data to be kept only as long as its purpose needs.
  • “Sharing customer lists with partners if the file is password-protected” (Incorrect): Sharing needs a lawful basis; a password doesn't provide one.
  • “Ignoring deletion requests if the data is only in the warehouse” (Incorrect): Deletion rights cover the warehouse copy too.
  • “Collecting and keeping only the data needed for the stated purpose” is correct.

    Purpose limitation and data minimisation run through the Act, along with security safeguards and people's rights to correct and erase.

    • Check with: your organisation's privacy or legal team.

📖 Revise: Lesson 5: Access, governance and personal data

Question ID sp-data-05-q9 · Report a mistake in this question
10. In SabziSathi's warehouse, which role should be able to write to the clean schema?

Why each option is right or wrong

  • “Every analyst, so they can fix mistakes quickly” (Incorrect): Hand fixes in clean can't be repeated and hide problems at the source.
  • “The dashboard, so it can store its own totals there” (Incorrect): Dashboards read marts; they don't write to earlier layers.
  • “Nobody, since clean should be read-only for everyone” (Incorrect): Then the pipeline couldn't load anything.
  • “Only the pipeline's own account” is correct.

    The loader is the one process that writes clean data, so every change comes from code that can be re-run and reviewed.

    • Analysts: read marts only.
    • Fixes: go into the pipeline's code, then a reload.

📖 Revise: Lesson 2: Layers and schemas

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

ProLearners Assist