How to build a modern data stack

"Modern data stack" describes a pattern, not a product: pull data from source systems into a central store, transform it there with SQL, and serve it back out to dashboards, spreadsheets and other applications. The pattern replaced an older one where transformation happened in flight, before data landed anywhere, in tools only a specialist could open. The modern version pushes transformation after loading, in plain SQL, where more people can read it. It is not a requirement for every company — a five-person startup querying a production database directly does not need five new subscriptions — but once more than one team asks for numbers and the answers start disagreeing, the pattern earns its keep.

The five layers

Ingestion moves data from source systems — product databases, SaaS tools, event streams — into a central store. Managed connectors such as Fivetran or the open-source Airbyte handle the common sources (Salesforce, Stripe, Postgres) without custom code; you write connectors only for internal or unusual systems.

Storage is the data warehouse or data lakehouse. Snowflake, Google BigQuery and Databricks all separate storage from compute and bill by usage, so cost tracks query volume rather than a fixed cluster size. The choice between a warehouse-first product and a lakehouse matters mainly if you also store unstructured data or train models on the same platform; for reporting-only use cases, either works.

Transformation turns raw loaded tables into modeled, tested datasets. dbt is the default here: it lets analysts write SELECT statements, adds version control, automated tests and a documented lineage graph, and runs entirely inside the warehouse's own compute.

Orchestration schedules and sequences the above — load this, then transform that, then refresh this dashboard — and alerts when a step fails. Apache Airflow is the established choice with the deepest integration ecosystem; Dagster models pipelines around the data assets they produce rather than just tasks, which pays off once you want to trace a bad number back to the job that made it.

Serving is how people and other systems consume the result: BI tools such as Looker, Power BI or the open-source Metabase for dashboards, a semantic layer for shared metric definitions, and Reverse ETL tools such as Hightouch for pushing warehouse data back into CRMs and ad platforms, so the destination is not always a dashboard.

What to buy first, by stage

Pre-product-market-fit. Query the production database directly, or export to spreadsheets. A stack is infrastructure for a problem you do not have yet; the fastest path to an answer wins.

First data hire, single product. A managed connector into a cheap warehouse, plus a lightweight BI tool, is the whole stack. Skip orchestration — a connector's built-in scheduler and a couple of scheduled dbt runs cover it. Skip a semantic layer — one analyst can keep definitions straight without tooling.

Multiple teams asking for numbers. This is when definitions start to drift and the stack needs the pieces that keep them from drifting further: dbt for tested, documented models; a real orchestrator once pipelines have dependencies that matter (finance cannot close the books on data that has not finished loading); and either your BI tool's built-in modeling layer or a standalone semantic layer so "revenue" means the same thing in every tool that queries it.

Data feeding other systems, not just dashboards. This is when reverse ETL earns its place — marketing wants a lead score computed in the warehouse inside the CRM, product wants a churn-risk flag inside the support tool. Build this after the warehouse model is trustworthy, not before; reverse ETL syncs bad definitions just as efficiently as good ones.

The transformation layer in practice

A typical dbt project turns a raw, ingested table into something reportable in stages: a staging model that renames and casts columns, an intermediate model that joins and deduplicates, and a mart that aggregates to the grain a dashboard needs.

-- models/marts/fct_daily_revenue.sql
select
  order_date,
  sum(order_amount) as gross_revenue,
  count(distinct customer_id) as paying_customers
from {{ ref('stg_orders') }}
where order_status = 'completed'
group by 1

Every model like this is version-controlled, tested (dbt can assert order_amount is never null, or that customer_id is unique in a dimension table) and documented, so the next analyst — or an AI assistant querying the warehouse — inherits a definition instead of reconstructing one.

Build vs buy at each layer

Ingestion and orchestration are the layers most teams should buy rather than build: connector maintenance (a source API changes its schema, silently) is unglamorous, continuous work that a vendor amortizes across thousands of customers. Transformation logic is the opposite — it encodes what your business means by "active customer," and that belongs in your own version-controlled repository, not a vendor's proprietary format, even when the tool that runs it is a vendor's product. See how to decide build vs buy for analytics for the fuller trade-off, and how to reduce data warehouse costs once usage-based storage and compute billing starts to sting.

Common mistakes

• Buying the serving layer first. A BI tool connected to unmodeled raw tables just moves the spreadsheet problem into a nicer chart library. Fix the transformation layer before investing in dashboards; see how to build a dashboard people use.
• No owner for pipeline failures. A stack with five vendors and no on-call rotation means a silent ingestion failure is discovered a month later, when someone asks why revenue looks flat.
• Skipping a tracking plan for event data. Product and marketing events loaded without agreed names and properties recreate the definitions problem inside the warehouse instead of solving it — see how to write a tracking plan.
• Treating the semantic layer as optional forever. It is optional at five people. It is not optional once three teams each have their own dashboard for "active users." See how to build a metrics layer.
• No data governance until a compliance request arrives. Ownership, access and retention are cheaper to establish while the stack is small; see how to establish data governance.

Where to start

Every tool named above, and the rest of the category, is browsable at cloud data warehouses, ELT and data integration tools, workflow orchestration tools and BI and dashboard platforms. For picking the BI layer specifically, see how to choose a BI tool.