Pipeline to Dashboardnihalsulaiman.com

Week 4 · Load to the warehouse › Day 22

Day 22. Choosing where the data lives

⏱ 4h · learn 90m · lab 110m · ship 25m · review 15m🏷 marketing agencyBefore: Week 3

By the end of today you can

  • Explain OLTP vs OLAP, row stores vs column stores, and single-node vs MPP
  • Place Azure SQL, Postgres, DuckDB, ClickHouse, Snowflake, BigQuery and Fabric on one map
  • Pick a warehouse for a given company size, budget and skill set, with reasons
  • Estimate storage and query cost before choosing

1. Why this matters

"Which database?" is a decision a five-year person is expected to make and defend. The wrong answer costs a company years: a warehouse too small, too expensive, or one nobody on the team can run. Today you build the map; the rest of the week you load into four of them.

2. Two kinds of workload

OLTP (transactions) OLAP (analytics)
Example POS taking an order, PMS saving a booking "Revenue by brand by week for two years"
Query shape Read/write a few rows by key Scan millions of rows, few columns, aggregate
Storage Row store, B-tree indexes Column store, compression, zone maps
Concurrency Thousands of small writes Tens of heavy reads, batch writes
Examples SQL Server/Azure SQL (rowstore), Postgres, MySQL ClickHouse, DuckDB, Snowflake, BigQuery, Synapse/Fabric Warehouse; SQL Server/Azure SQL with columnstore indexes

Azure SQL and Postgres are general-purpose: excellent for small and medium analytics (up to hundreds of GB) when modelled well, with columnstore (SQL Server) or extensions (Postgres: pg_duckdb, Citus, TimescaleDB, pg_mooncake) for heavier scans.

3. The map

Option Type Cost shape Sweet spot Watch out
Azure SQL Database Managed SQL Server (row + columnstore) vCore or DTU; serverless auto-pause; free offer Microsoft shops, Power BI, up to ~TB Scan-heavy at large scale; tier limits on log rate
Postgres (self-hosted, Azure Flexible Server, Supabase, Neon) Open-source RDBMS VM or managed instance Cheap all-rounder, huge ecosystem Large scans without columnar extensions
DuckDB / MotherDuck In-process columnar; MotherDuck = hosted Free / low Single-node analytics, files, dev, embedded One writer at a time locally
ClickHouse (OSS, Cloud) Columnar MPP VM or usage Billions of event rows, sub-second dashboards Updates/deletes are different (merge trees)
Snowflake / BigQuery / Databricks SQL Cloud columnar MPP Usage-based Large, elastic, many teams Bills grow quietly; needs cost governance
Microsoft Fabric Warehouse / Synapse Cloud MPP inside Fabric Capacity-based Enterprise Microsoft analytics Capacity sizing, licence complexity
Maths and principle

Principle: estimate data size before choosing. Most "big data" problems at medium companies are a few hundred GB.

Equation: raw size \(\approx\) rows/day × days × bytes/row. Columnar compressed \(\approx\) raw ÷ (5 to 10) for typical sales data.

Analogy: order-of-magnitude estimates in physics (Fermi problems): get within a factor of 3 before reaching for precision. Where it breaks: event and clickstream data (web logs, IoT) can be 100× larger than transactions.

Try it: 300 restaurants × 1,500 order lines/day × 3 years × 120 bytes/row. Raw size? Compressed (÷7)? (300 × 1,500 × 1,095 × 120 ≈ 59 GB raw; ≈ 8.5 GB compressed: one Postgres or Azure SQL database handles it.)

4. How to choose

Ask in this order:

  1. Where does the company already live? Microsoft 365 + Power BI → Azure SQL or Fabric fits auth, networking and skills. Google Workspace → BigQuery. Nothing → Postgres.
  2. How big, how fast? Size estimate above; dashboard latency needs; number of concurrent users.
  3. Who runs it? A managed service beats a cheaper self-hosted one if nobody can patch the VM.
  4. What does it cost at 3× today's volume? Ask for this number before signing anything.
  5. How do you leave? Standard SQL and open formats (Parquet) keep the exit cheap.

The 5-year lens

Say "it depends" and then actually decide: "For a 40-restaurant chain on Microsoft 365 with one analyst, I'd use Azure SQL serverless General Purpose with a columnstore on the sales fact, because Power BI and Entra sign-in work out of the box and it's tens of dollars a month at this size. If they had no Microsoft licences, Postgres on a small VM plus DuckDB for heavy scans." Interviewers want the structure and the call.

Lab

  1. Generate 120 days of data. Using the Week 3 pipeline, export the ads, QSR and hotel staging tables to Parquet. Record row counts and Parquet sizes.
  2. Do a Fermi estimate for each sector at real-company scale (your guesses for stores, rows per day, years). Write docs/sizing.md.
  3. Write a one-page decision record (docs/adr-001-warehouse.md, Architecture Decision Record format: context, options, decision, consequences) for the marketing agency.
  4. Set up accounts you'll need this week: an Azure free account (or use a local SQL Server container), Docker for Postgres and ClickHouse. Don't create billable resources yet; Day 23 walks through it.

Ship

New repo ads-warehouse. Commit docs: sizing and ADR-001 warehouse choice.

Check yourself

1. Which workload is OLAP?

2. A 40-store retailer has 15 GB of compressed sales history and uses Microsoft 365 and Power BI. A sensible first warehouse?

3. What is an ADR?

My notes