The Modern Data Stack

Five boxes, one flow

A "data stack" sounds intimidating, but it's just the chain of systems data passes through on its way from being created to being useful. Strip away the brand names and almost every stack is the same five layers in the same order.

1. Sources. Where data is born: the app and website, third-party tools (payment processors, ad platforms), and uploaded files. ShopStream's app, marketing platform, and supplier spreadsheets all live here.
2. Operational databases. The live system that runs the product. When a customer places an order, it's written here right now, optimized for fast single-record reads and writes — not for big analytical questions.
3. ETL / ELT pipelines. The movers. They extract data from the sources, optionally transform (clean, reshape) it, and load it into the warehouse — on a schedule, automatically. This is the heart of the data engineer's job.
4. Data warehouse. A separate database built for analysis, not for running the app. It holds history from all sources together and is tuned to crunch millions of rows for one big question.
5. BI & analytics tools. The dashboards, reports, and notebooks where humans finally read the data and make decisions. The CEO's revenue chart lives here.

Data flows left to right through these boxes, and your career follows the same line. This course teaches the boxes roughly in order: SQL and the database (Sections 4–6), warehousing (Section 10), pipelines and ETL/ELT (Sections 8–9), and analytics on top (Sections 11–12). When you can point at any tool — Fivetran, Airflow, dbt, Snowflake, Power BI — and say which box it lives in, you've understood the stack better than most beginners ever do.

Database vs. warehouse — the one distinction beginners miss

This trips up nearly everyone, so let's nail it now. Both a database and a warehouse store tables of data, so why have two? Because they're tuned for opposite jobs. An operational database (sometimes called OLTP — online transaction processing) is built to handle thousands of tiny, fast operations: "insert this one order," "update this customer's email." It must never slow down the live app.

A data warehouse (OLAP — online analytical processing) is built for the opposite: a few enormous questions that scan millions of rows, like "total revenue by country by month for the last three years." If you ran that query directly against the live app's database, you could slow checkout to a crawl for real customers. So we copy the data into a separate warehouse and analyze it there, safely. That copy-and-separate step is exactly what pipelines do — and it's why the data engineer sits squarely in the middle of the stack. We'll go deep on this contrast in Section 10; for now, just remember: database runs the business, warehouse analyzes the business.

Walkthrough: ShopStream

Let's map ShopStream directly onto the five boxes. The very same order from Lesson 1.1 passes through every layer. Here's a peek at what that looks like as data moves from the live app database into the warehouse.

-- BOX 2: in ShopStream's operational database, optimized for the live app.
-- A single, fast lookup of one order:
SELECT order_id, customer_id, status
FROM orders
WHERE order_id = 90118;

-- BOX 4: in the warehouse, the same data is reshaped for analysis.
-- A big rollup across millions of rows that we'd never run on the live app:
SELECT c.country,
       COUNT(*)                              AS num_orders,
       SUM(oi.quantity * oi.unit_price)      AS revenue
FROM orders o
JOIN customers   c  ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
WHERE o.status = 'completed'
GROUP BY c.country
ORDER BY revenue DESC;

The first query is a quick, surgical lookup — the kind the live app fires constantly. The second scans and groups everything — the kind a dashboard needs but a live app must be protected from. A pipeline (Box 3) is what carried the data from the first world to the second. The result a BI tool (Box 5) would chart:

Sources              →  Database (OLTP)   →  ETL/ELT pipeline  →  Warehouse (OLAP)  →  BI / dashboard
ShopStream app,         live orders &        loads & cleans       all history,         CEO revenue
marketing, supplier     customers table      on a schedule        analysis-ready       chart
CSVs
[Section 2 — data    [Sections 4–6 — SQL] [Sections 8–9 —      [Section 10 —        [Sections 11–12 —
 types & sources]                          ETL/ELT & pipelines] warehousing]         analytics & BI]