A data stack is the set of tools that moves data out of the systems where it is created, stores it in one place, shapes it into tables people can trust, and puts it in front of the people, tools and AI agents that use it. A modern data stack is the cloud version of that idea: managed connectors load raw data into a cloud warehouse or lakehouse, transformations run as SQL inside it, and everything downstream reads from the same modelled tables.
The short version of the architecture:
| Layer | What it does |
|---|---|
| Sources | The apps and databases where data is created |
| Ingestion (ELT) | Copies source data into the warehouse on a schedule |
| Warehouse or lakehouse | Stores the data and runs the SQL |
| Transformation and modelling | Turns raw tables into clean, tested business tables |
| Semantic layer | Holds one definition per metric, shared by every consumer |
| BI | Dashboards and self-serve analysis |
| Activation (reverse ETL) | Sends modelled data back into CRM, ads and support tools |
| Orchestration and observability | Runs everything in order and tells you when something breaks |
| AI agents | Answer questions and do work on top of the governed tables |
This guide covers each layer and the tools in it, then gets practical: three reference stacks for teams of different sizes, with what to buy and what to skip, and a 90-day build plan for a company running Shopify, Stripe, HubSpot and Google Ads, including the first three models to write.
What is a data stack?
The term borrows from the "tech stack" of software engineering: the set of technologies a product is built on. A data stack is the same thing for analytics. It answers four questions: where does data come from, where is it stored, who turns it into something reliable, and how does it reach the people and systems that act on it.
What makes a stack "modern" is a handful of design choices rather than a list of brands:
| Choice | Traditional data stack | Modern data stack |
|---|---|---|
| Where data is transformed | Before loading, on an ETL server (ETL) | After loading, in SQL inside the warehouse (ELT) |
| Storage | On-premise database sized for peak load | Cloud warehouse or lakehouse, storage separate from compute |
| Pricing | Licences and servers paid up front | Usage-based: rows synced, bytes scanned, seats |
| Who builds pipelines | Engineers writing custom code | Managed connectors, with code only for the long tail |
| Who builds models | A central BI team | Analysts and analytics engineers in version-controlled SQL |
| Who reads the output | Dashboards and scheduled reports | Dashboards, business tools through reverse ETL, AI agents |
The ETL to ELT shift is the one everything else depends on. Once raw data lands untouched in the warehouse, you can rebuild any model from it, fix a definition and backfill history in one query, and let analysts own the logic in SQL. Our ETL vs ELT guide covers that change in detail.
Around 2021 the phrase "modern data stack" came to mean a dozen separate tools stitched together, one per layer. The pendulum has since swung back towards fewer tools that cover several layers each. The layers themselves have not gone away, which is why they are the useful way to think about the stack. We wrote about that shift in the modern data stack is not what you think.
The layers of a modern data stack
1. Sources
Every system that creates data: SaaS apps (CRM, payments, ads, support, ecommerce), your product's own database, event tracking, spreadsheets and files, and internal APIs. Most companies have more of these than they expect. You do not move all of them on day one; you move the ones behind the questions you need answered.
2. Ingestion (ELT and CDC)
Ingestion copies data from sources into storage on a schedule, ideally incrementally: only rows that changed since the last run. For SaaS APIs this means managed connectors that handle authentication, pagination, rate limits and schema changes. For production databases it often means change data capture (CDC), which reads the database's change log instead of querying tables, so it catches deletes and puts no load on the application. See what is change data capture.
The decision at this layer is usually whether to build connectors or buy them. We worked through the numbers in build vs buy data pipelines.
3. Storage: warehouse or lakehouse
The warehouse is the hub. Everything upstream writes into it, everything downstream reads from it. The common choices are BigQuery, Snowflake, Databricks and Redshift; Postgres, MotherDuck and ClickHouse work well at smaller sizes or for specific workloads. Our top data warehouses post compares them.
The newer option is a lakehouse built on an open table format, most often Apache Iceberg. Iceberg tables live as files in your own object storage (S3, GCS, Azure) and are tracked by a catalog, so several engines, for example Snowflake, Spark, Trino, Athena and DuckDB, can query the same tables without copying them. Teams pick it when they want to own the storage, avoid lock-in to one query engine, or keep data in their own cloud account for compliance. The cost is more moving parts: a catalog, file compaction, and permissions across engines. If you are a small team with one warehouse, a warehouse's native tables are simpler.
4. Transformation and modelling
Raw tables are shaped by the source's API, not by your business. Transformation turns them into tables built around business entities and processes: orders, customers, subscriptions, daily marketing spend. Almost every team now does this in SQL inside the warehouse, organised in layers: staging models that clean one source table each, and marts (facts and dimensions) that join them into business tables. Some teams call these layers bronze, silver and gold; our medallion architecture guide shows the pattern with SQL.
Tools here range from dbt and SQLMesh to the modelling features built into ELT platforms. Whatever you use, three habits matter more than the tool: models in version control, tests on keys and critical columns, and one owner per model.
5. Semantic layer and metric definitions
A semantic layer stores metric definitions once ("net revenue excludes tax, shipping and refunds, counted on the order date in the store's timezone") and serves them to every consumer. Without one, the same metric gets rebuilt in each dashboard and drifts. With AI agents querying data, this matters more: an agent that writes its own SQL for "revenue" will produce a different number from your finance dashboard unless both read the same definition. The agent context stack post covers how semantic layers, context and permissions fit together for agents.
A small team does not need a dedicated semantic layer tool on day one. A documented list of metric definitions, implemented once in a mart that everything reads, does most of the job.
6. BI and analytics
Dashboards, exploration and reports. Choose by who will use it: analysts who write SQL, business users who want to filter and drill, or both. Every BI tool should read marts, never raw tables, so a source change is fixed in one model rather than in fifty charts.
7. Activation (reverse ETL)
Reverse ETL sends modelled data back into the tools teams work in: lifetime value and subscription status onto CRM contacts, audiences into ad platforms, account health into the support tool. It is how the warehouse stops being a reporting system only. See what is reverse ETL.
8. Orchestration and observability
Orchestration runs jobs in the right order: sync the sources, rebuild the models that depend on them, run the tests, then refresh dashboards and reverse ETL syncs. Observability tells you when something is late, empty or wrong: failed syncs, freshness checks, row count anomalies, test failures, and lineage so you can see what a broken table affects downstream. Airflow, Dagster and Prefect are the general-purpose orchestrators; many ELT and transformation tools include a scheduler that is enough for a small team.
9. AI agents reading governed data
The newest consumer of the stack is an AI agent: Claude, ChatGPT, Cursor or an internal assistant that answers questions or does work by querying your data. Agents connect through the Model Context Protocol (MCP), an open standard for exposing tools and data to AI applications. The stack's job is to make sure agents read the same governed marts and metric definitions as your dashboards, with read-only permissions, rather than raw tables. Our comparison of MCP servers for your data warehouse shows what the Snowflake, BigQuery, dbt and Weld servers can do on the same tasks.
Modern data stack tools by layer
Example tools for each layer, listed alphabetically. This is a map of the landscape, not a ranking; most teams need one tool from a few of these rows, not one from each.
| Layer | Example tools |
|---|---|
| Ingestion (ELT) | Airbyte, dlt, Fivetran, Weld |
| CDC from databases | Debezium, Estuary, Fivetran, Weld |
| Warehouse | BigQuery, ClickHouse, Databricks, MotherDuck, Postgres, Redshift, Snowflake |
| Open table format and catalog | Apache Iceberg with AWS Glue, Apache Polaris, Amazon S3 Tables; Delta Lake |
| Transformation | Dataform, dbt, SQLMesh, Weld |
| Semantic layer | Cube, dbt Semantic Layer, LookML |
| BI | Lightdash, Looker, Looker Studio, Metabase, Power BI, Sigma, Tableau |
| Reverse ETL | Census, Hightouch, Weld |
| Orchestration | Airflow, Dagster, Prefect, built-in schedulers in ELT and transformation tools |
| Data quality and observability | dbt tests, Elementary, Monte Carlo, Soda |
| AI agents | Claude, ChatGPT, Cursor and other MCP clients, connected through MCP servers |
For a deeper comparison of the ingestion row alone, see our ETL tools compared guide.
Three reference stacks, by team size
The right stack is sized by the team that has to run it, not by the number of logos you can fit on an architecture slide. Here are three reference stacks, with what each one buys and what it can safely skip.
| Layer | Startup, 1 analyst | Data team of 5 | Regulated or enterprise |
|---|---|---|---|
| Sources | 3 to 6 SaaS tools | 10 to 30 tools plus the product database | Dozens of systems, ERP, on-premise databases |
| Ingestion | Managed ELT, daily or hourly | Managed ELT, CDC from the product database | Managed ELT and CDC with SSO, audit logs, column hashing |
| Storage | BigQuery, Postgres or MotherDuck | BigQuery, Snowflake or Databricks | Warehouse plus Iceberg tables in your own cloud account |
| Transformation | SQL models in one tool | SQL or dbt in Git, with CI and tests | Same, plus code review and separate dev and prod |
| Semantic layer | A written list of metric definitions | Add a tool once metrics are reused in many places | Certified metrics with access policies |
| BI | Looker Studio or Metabase | A self-serve BI tool | Enterprise BI with row-level security |
| Activation | One or two reverse ETL syncs | Reverse ETL for sales, marketing and support | Reverse ETL with an approval step and audit trail |
| Orchestration | The platform's scheduler and alerts | One orchestrator, tests and freshness alerts | Orchestrator, observability tool, incident process |
| AI agents | One read-only agent on marts | Agents through MCP on governed models | Agents through the semantic layer, with queries logged |
Stack 1: the startup with one analyst
The constraint is time, not money. One person has to run ingestion, modelling, dashboards and the occasional "can you pull this number" request.
- Buy: one platform that covers ingestion, SQL models, scheduling and reverse ETL, so there is one thing to log into and one place where failures show up. A warehouse with a free tier or low entry cost: BigQuery's first 10 GiB of storage and first 1 TiB of queries each month are free, then on-demand queries cost $6.25 per TiB scanned (BigQuery pricing). A BI tool that is free or cheap to start: Metabase is free as open source and its hosted Starter plan is $100 a month for the first 5 users (Metabase pricing).
- Skip: a separate orchestrator, a semantic layer tool, a data catalog, an observability product, streaming, and a lakehouse. Each of these solves a problem you do not have yet and adds a system to maintain.
- Watch for: the day a second person starts writing models. That is when version control and tests stop being optional.
Stack 2: the data team of five
Now there are several people changing models at once, more sources, and stakeholders across sales, marketing, finance and product.
- Buy: managed connectors for every common SaaS source, and CDC for the product database. Version-controlled transformation with pull requests and CI that builds changed models before they merge. Tests on every mart's keys. A self-serve BI tool so the team is not the bottleneck for every chart. Reverse ETL for the two or three workflows that move revenue, such as lead scoring in the CRM or audiences in ad platforms.
- Add when it hurts: a semantic layer, once the same metric appears in several BI tools or agents and the numbers start to disagree. A dedicated observability tool, once you have more than a few hundred models and failures are found by stakeholders before the team.
- Skip: building your own connectors for common SaaS tools (the maintenance never ends), and real-time streaming unless a product feature needs sub-minute data.
Stack 3: the regulated or enterprise team
The constraints are compliance, access control and auditability, often across several business units.
- Buy: vendors with SOC 2 Type II reports, a data processing agreement, SSO and audit logs. Ingestion that can hash or block sensitive columns before they land. A semantic layer with access policies, so the same metric is filtered correctly per role. Data observability and lineage that answer "which reports used this column" during an audit.
- Consider: Iceberg tables in your own cloud account and region, so data stays where your compliance team needs it and several engines can read it without copies.
- Skip: one-off scripts that move personal data outside the governed path, and any tool that needs data to leave your approved region.
The common mistakes at every size, over-engineering early and forgetting activation among them, are covered in 5 common mistakes when building your first data stack.
Worked example: the first 90 days for a Shopify, Stripe, HubSpot and Google Ads company
Take a direct-to-consumer brand with an online store on Shopify, a subscription line billed through Stripe, HubSpot as CRM and email tool, and Google Ads as the main paid channel. One analyst, no data engineer. Leadership wants three answers: what did we really sell, what does a new customer cost us, and which customers are worth more attention.
The plan
| Days | Goal | Done when |
|---|---|---|
| 1 to 5 | Warehouse and connectors | BigQuery project set up, four sources syncing, full history loaded |
| 6 to 20 | Staging models | One staging model per source table you use, with tests on primary keys |
| 21 to 40 | The first three marts | fct_orders, dim_customers and fct_marketing_daily reconciled to source |
| 41 to 60 | Dashboards and metric definitions | Three dashboards on marts only, every metric written down once |
| 61 to 75 | Activation | Customer value and subscription status synced to HubSpot contacts |
| 76 to 90 | Orchestration, alerts and agent access | One scheduled run in dependency order, failure alerts, a read-only agent |
Days 1 to 5: warehouse and connectors
Create the warehouse first, then connect the four sources and load full history. Sync only the tables you need for the first questions:
| Source | Data to sync | Sync frequency |
|---|---|---|
| Shopify | Orders, order lines, refunds, products | Hourly or daily |
| Stripe | Customers, subscriptions, invoices | Hourly or daily |
| HubSpot | Contacts, companies, deals | Daily |
| Google Ads | Accounts, campaigns, daily campaign stats | Daily |
For Shopify, make sure the connector has access to your full order history; Shopify only returns the last 60 days of orders to apps without the read_all_orders scope.
Days 6 to 20: staging models
One staging model per source table: rename columns, cast types, lowercase emails so they can be joined across systems, and drop test records. Here is the Google Ads one, because its raw cost is stored in micros (millionths of the account currency):
1-- staging.stg_google_ads__campaign_stats
2select
3 date as date_day,
4 campaign_id,
5 cost_micros / 1000000 as spend,
6 clicks,
7 impressions,
8 conversions
9from google_ads.campaign_statsThe Shopify orders staging model is shown in full in our medallion architecture guide. Add a stg_shopify__refunds model that sums refunds to one row per order, and Stripe and HubSpot staging models with customer_email and email lowercased.
Days 21 to 40: the first three models
These three answer most of the first questions, and every later model builds on them. The SQL is BigQuery; in Snowflake swap countif for count_if and safe_divide for div0.
1. fct_orders: one row per order, with net sales.
1-- marts.fct_orders
2select
3 o.order_id,
4 o.created_at,
5 date(o.created_at) as order_date,
6 o.customer_email,
7 o.currency,
8 o.subtotal_price as sales_after_discounts,
9 coalesce(r.refunded_amount, 0) as refunded_amount,
10 o.subtotal_price - coalesce(r.refunded_amount, 0) as net_sales,
11 row_number() over (
12 partition by o.customer_email
13 order by o.created_at
14 ) = 1 as is_first_order
15from staging.stg_shopify__orders o
16left join staging.stg_shopify__refunds r
17 on r.order_id = o.order_id
18where not o.is_cancelled2. dim_customers: one row per customer across Shopify, Stripe and HubSpot.
1-- marts.dim_customers
2with shop as (
3 select
4 customer_email,
5 min(order_date) as first_order_date,
6 max(order_date) as last_order_date,
7 count(*) as orders,
8 sum(net_sales) as lifetime_net_sales
9 from marts.fct_orders
10 group by 1
11),
12
13subs as (
14 select
15 c.customer_email,
16 max(case when s.status in ('active', 'trialing', 'past_due')
17 then 1 else 0 end) = 1 as is_subscriber,
18 min(s.start_date) as subscribed_since
19 from staging.stg_stripe__subscriptions s
20 join staging.stg_stripe__customers c
21 on c.customer_id = s.customer_id
22 group by 1
23),
24
25crm as (
26 select email as customer_email, contact_id, lifecycle_stage
27 from staging.stg_hubspot__contacts
28)
29
30select
31 coalesce(shop.customer_email, subs.customer_email, crm.customer_email) as customer_email,
32 crm.contact_id as hubspot_contact_id,
33 crm.lifecycle_stage,
34 shop.first_order_date,
35 shop.last_order_date,
36 coalesce(shop.orders, 0) as orders,
37 coalesce(shop.lifetime_net_sales, 0) as lifetime_net_sales,
38 coalesce(subs.is_subscriber, false) as is_subscriber,
39 subs.subscribed_since
40from shop
41full outer join subs
42 on subs.customer_email = shop.customer_email
43full outer join crm
44 on crm.customer_email = coalesce(shop.customer_email, subs.customer_email)3. fct_marketing_daily: ad spend against what the store sold, per day.
1-- marts.fct_marketing_daily
2with spend as (
3 select date_day, sum(spend) as ad_spend, sum(clicks) as clicks
4 from staging.stg_google_ads__campaign_stats
5 group by 1
6),
7
8orders as (
9 select
10 order_date as date_day,
11 sum(net_sales) as net_sales,
12 count(*) as orders,
13 countif(is_first_order) as new_customers
14 from marts.fct_orders
15 group by 1
16)
17
18select
19 coalesce(s.date_day, o.date_day) as date_day,
20 coalesce(s.ad_spend, 0) as ad_spend,
21 coalesce(o.net_sales, 0) as net_sales,
22 coalesce(o.orders, 0) as orders,
23 coalesce(o.new_customers, 0) as new_customers,
24 safe_divide(s.ad_spend, o.new_customers) as blended_cac,
25 safe_divide(o.net_sales, s.ad_spend) as mer
26from spend s
27full outer join orders o
28 on o.date_day = s.date_dayBlended CAC (ad spend divided by new customers) and MER (net sales divided by ad spend) are deliberately simple. They need no attribution model, and they are the numbers a founder checks first. For one week in October the output looks like this (example figures):
| date_day | ad_spend | net_sales | orders | new_customers | blended_cac | mer |
|---|---|---|---|---|---|---|
| 2026-10-06 | 2,140 | 18,420 | 231 | 88 | 24.32 | 8.61 |
| 2026-10-07 | 1,980 | 16,950 | 214 | 79 | 25.06 | 8.56 |
| 2026-10-08 | 2,310 | 21,060 | 259 | 97 | 23.81 | 9.12 |
Reconcile before anyone sees a dashboard. Compare one full month of net_sales from fct_orders with the sales report in the Shopify admin. The gaps are almost always the same three things: timezone (Shopify reports in the store's timezone, the raw created_at is UTC), test orders, and refunds (Shopify's sales report puts a refund on the day it happened, this model subtracts it from the original order). Decide which convention you want, write it down, and fix it in the model. Our Shopify sales model shows how to match the admin report exactly. For Stripe subscription revenue, our MRR in SQL guide is the next model to add.
Days 41 to 60: dashboards and definitions
Build three dashboards, all on marts: daily sales (from fct_orders), acquisition (from fct_marketing_daily) and customers (from dim_customers). Next to them, write one definition per metric: what "net sales", "new customer" and "subscriber" mean, which table holds them, and who owns them. That page is your first semantic layer.
Days 61 to 75: activation
Sync lifetime_net_sales, orders, last_order_date and is_subscriber from dim_customers to custom contact properties in HubSpot, matched on email. Marketing can now build segments such as "bought twice, not a subscriber" in the tool they already use. Our reverse ETL to HubSpot guide walks through the setup.
Days 76 to 90: orchestration, alerts and agent access
Put the source syncs, the models and the reverse ETL sync into one scheduled run, so each step waits for the one before it. Turn on alerts for failed syncs and failed models, sent to the channel the analyst reads. Then connect one AI agent through an MCP server with read-only access to the marts schema, and test it against the dashboards: if the agent's answer for last month's net sales does not match the dashboard, the definition is not yet in a place the agent can read.
At day 90 the company has four sources syncing, around ten staging models, three marts, three dashboards, one reverse ETL sync and an agent that gives the same numbers as the dashboards. That is a complete modern data stack for a team of one.
Where Weld fits
Weld covers the ingestion, transformation, orchestration and activation layers in one platform on top of your own warehouse, including BigQuery, Snowflake, Databricks, Redshift, Postgres, ClickHouse, MotherDuck and Apache Iceberg. You connect sources, write SQL models with lineage and version history, run them with orchestration in dependency order, and send results to tools like HubSpot with reverse ETL. AI agents connect through the Weld MCP server, which can also run queries against your warehouse when your workspace allows it.
For the 90-day example above, the four connectors fit the Basic plan at €99 a month plus two extra connectors at €49 each, which is €197 a month with daily syncs. Premium, at €399 a month with six connectors included, adds hourly syncs, orchestration, model tests and the MCP server. Current details are on the pricing page.
Sign up to Weld
FAQ
A data stack is the set of tools a company uses to collect data from its systems, store it in one place, transform it into reliable tables and deliver it to dashboards, business tools and AI agents. The term comes from the "tech stack" used in software engineering.
The modern data stack is a cloud-based data stack built around a cloud warehouse or lakehouse. Managed connectors load raw data (ELT), transformations run as SQL inside the warehouse, and BI, reverse ETL and AI agents all read from the same modelled tables. It replaced on-premise ETL servers and databases.
The architecture is a set of layers that data flows through: sources, ingestion, a warehouse or lakehouse, transformation, a semantic layer, and consumers such as BI, reverse ETL and AI agents. Orchestration and observability run across all of them. Each layer can be a separate tool, or one platform can cover several.
Common examples are Fivetran, Airbyte and Weld for ingestion; BigQuery, Snowflake and Databricks for storage; dbt and SQLMesh for transformation; Cube and the dbt Semantic Layer for metrics; Looker, Metabase, Power BI and Tableau for BI; and Census, Hightouch and Weld for reverse ETL. Most teams need far fewer tools than the full list.
Start from the questions the business needs answered, then pick a warehouse, connect only the sources behind those questions, and build staging models and a few marts in version-controlled SQL. Add dashboards, then reverse ETL and orchestration. Add a semantic layer, an observability tool or a lakehouse only when a concrete problem calls for it.
The layers are, and AI agents have made the modelling and semantic layers more important, because agents need governed definitions to give consistent answers. What has changed is the tooling: teams now use fewer, broader tools instead of one product per layer.



