Warehousing for Analytics

• Distinguish OLTP from OLAP workloads
• Explain why analytics needs its own storage layer
• Name the main warehouse options and their trade-offs

• Queries against production databases compete with live traffic and hurt the app.
• Analytics need history, snapshots, and reshaping that operational schemas do not provide.
• Warehouses separate 'can we analyze?' from 'is the app fast?'

The counter (OLTP) handles one order at a time quickly; the warehouse (OLAP) exists so you can run reports over every sale ever, without slowing the counter.

• Pipeline tools extract from sources and load into the warehouse (ELT).
• Raw tables are transformed into modeled layers for specific questions.
• BI tools and notebooks query the modeled layer for dashboards and analysis.
• Compute and storage separate in cloud warehouses, so scale comes as usage does.

• A fintech runs its P&L dashboards from a warehouse modeled nightly.
• An e-commerce team joins orders, sessions, and support into one revenue mart.
• A retail chain loads per-store sales raw and lets analysts self-serve daily.

• Keep raw loads immutable and transform into clean layers — never edit raw.
• Define the warehouse schema around business questions, not source systems.
• Let dashboards read models, never live production databases.

• Pointing dashboards at production OLTP replicas and paying for it in both places.
• Loading without modeling until every dashboard re-does the same joins.
• Choosing one giant raw buck for all traffic so every query scans the world.

• Slow dashboards usually mean a heavy scan of raw — route them to a modeled mart.
• Disagreements between warehouse numbers point at duplicated modeling logic, not the warehouse.