The Modern Data Stack

The stack in one diagram

Ingestion (the "EL" of ELT)

Get raw data from sources into the warehouse with minimal transformation. Options:

• Managed connectors — Fivetran, Airbyte, Stitch. Pay-per-row or per-connector. Hands-off but pricey at scale.
• Open source / DIY — Airbyte (self-hosted), Singer, Meltano, custom Python. More work, cheaper.
• Streaming — Kafka + Snowpipe Streaming / BigQuery Streaming Inserts. For event firehoses.
• CDC (Change Data Capture) — Debezium, Snowflake Streams, BigQuery Datastream. Replicate database changes in near-real-time.
• SaaS-native — most SaaS tools have official connectors (Stripe, Salesforce, etc.).

For interview questions: "How would you ingest data from X?" The answer depends on freshness, volume, cost tolerance. Default: Fivetran for SaaS, custom Python for in-house DBs, Kafka for high-volume events.

Storage — warehouse vs lakehouse

The split matters less than it used to. Modern offerings blur:

See 09-warehouses for deeper coverage of each.

Transformation

dbt is the dominant tool. SQLMesh and Dataform are notable alternatives.

• dbt Core — open source, run anywhere.
• dbt Cloud — managed, includes scheduler + IDE + observability.
• SQLMesh — newer, Python-native, virtual data environments.
• Dataform — Google-acquired, integrates closely with BigQuery.

If asked: "Why dbt?" — "It made the transformation layer a software engineering practice. Version control, tests, lineage, documentation as code. The category exists because of dbt; alternatives are still catching up on ecosystem."

See 05-dbt-deep-dive.

Orchestration

Run things in order, retry, schedule, handle failures, observability.

• Airflow — the default. Python DAGs, huge ecosystem, mature. Also clunky for data-specific patterns.
• Dagster — modern, asset-oriented (you declare what you want to exist, not what to run). Better dev ergonomics.
• Prefect — Python-native, lighter weight, hybrid cloud/OSS.
• dbt Cloud scheduler — limited but sufficient for dbt-only workflows.
• GitHub Actions / cloud-native — for simple cron-style jobs.

See 13-orchestration.

BI / consumption

• Looker — semantic layer (LookML), strong governance, exec-friendly. Now Google.
• Tableau — most powerful viz, weaker semantic story. Now Salesforce.
• Power BI — dominant in Microsoft shops.
• Hex / Mode / Deepnote — notebook + SQL, popular with data teams.
• Metabase / Preset / Superset — open-source-friendly.
• Sigma — spreadsheet-style on warehouse.
• Embedded BI — Cube, ClickHouse + custom UI, for customer-facing analytics.

Metrics layer / semantic layer

The "we have three different numbers for active users" problem.

• dbt Semantic Layer (formerly MetricFlow) — define metrics in dbt, query via a unified API. Most-aligned with the dbt-centric stack.
• Cube — open-source semantic + caching layer.
• LookML — Looker's built-in semantic model.
• Lightdash — open-source dbt-native BI with built-in metrics.

Have an opinion: "For any company with more than 5 analysts asking the same questions, the metrics layer is no longer optional — it's the only way to avoid metric drift."

Quality & observability

• dbt tests — the first line. Built-in (unique, not_null, relationships, accepted_values) + dbt-utils + dbt-expectations.
• Great Expectations — Python-native, more flexible than dbt tests.
• Monte Carlo, Bigeye, Anomalo — commercial data observability (anomaly detection, freshness, schema drift).
• Elementary — open-source dbt-native observability.
• OpenLineage / DataHub / Atlan — lineage and catalog.

See 08-data-quality and 14-observability.

Reverse ETL

Push warehouse data back to operational tools — sync customer segments to marketing tools, sync user attributes to product analytics, etc.

• Hightouch
• Census
• RudderStack (also doubles as a CDP)

The "data activation" layer. Increasingly common; not always part of the analytics-engineering scope, but worth knowing exists.

An opinionated default stack

If asked "what would you set up from scratch?" — have a defensible answer:

✓ Check yourself

Answer from memory before opening each one.

1. Three dashboards show three different "active users" numbers, and eight analysts keep asking the same questions. Which layer is missing, and what does it change?

   Show answer

   The metrics (semantic) layer. It defines each metric once and serves it through one interface, so dashboards stop re-implementing the definition in their own SQL. Once many analysts ask the same questions, convention alone cannot prevent drift. See metrics layer.

2. You must ingest three sources: Stripe, an in-house Postgres database, and a clickstream at tens of thousands of events per second. What decides the approach for each, and what would you pick?

   Show answer

   Freshness, volume and cost tolerance decide. A managed connector for Stripe, custom Python or change data capture for the Postgres database, and Kafka with a streaming load for the clickstream. A managed connector on the clickstream would be costly at that volume. See ingestion.

3. What does dbt give you that a folder of SQL files run in order by cron does not?

   Show answer

   A dependency graph from ref(), so build order is derived rather than hand-maintained, plus tests, documentation and lineage kept next to the code and reviewed in Git. That is why the transformation layer became a software practice. See transformation.

4. A customer-facing dashboard must answer in under a second over billions of rows with new data arriving continuously. Why might your general-purpose warehouse be the wrong store, and what class of system do you consider?

   Show answer

   Warehouses are tuned for throughput on analyst queries, not high-concurrency sub-second reads on fresh data. Consider an OLAP database such as ClickHouse, Druid or Pinot, which trades some SQL breadth and join strength for scan speed and real-time ingest. See storage.

5. You are asked to design a stack from scratch for a mid-size team. Defend leaving streaming out of it.

   Show answer

   Streaming adds cost in exactly-once handling, debugging and observability, and most requirements that sound real-time are satisfied by hourly or micro-batch loads. Add it when a named consumer has a stated latency that batch cannot meet. See an opinionated default stack.

Takeaway

• The stack is ingestion, storage, transformation, consumption and a metrics layer, with orchestration, quality and lineage cutting across all of them.
• Choose ingestion by freshness, volume and cost tolerance: managed connectors for SaaS, custom code for in-house databases, Kafka for event firehoses.
• Warehouse and lakehouse have converged; OLAP databases such as ClickHouse remain the choice for sub-second customer-facing queries.
• dbt made transformation a software practice with version control, tests, lineage and documentation as code.
• Once several analysts ask the same questions, a metrics layer is the reliable defence against metric drift.
• Start with batch and add streaming only when a named consumer needs the latency.

The transformation layer is written in SQL, so the next chapter drills the SQL itself. → SQL Deep Dive

Further reading

• What is dbt? — dbt Labs docs, accessed 2026. What dbt does and does not do in the stack.
• dbt Semantic Layer — dbt Labs docs, accessed 2026. How metrics are defined once and queried through an API.
• About OpenLineage — OpenLineage project, accessed 2026. The open standard for lineage events across tools.
• What is Airflow? — Apache Airflow docs, accessed 2026. The orchestrator most teams default to, in its own words.