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:

LayerWhat it does
SourcesThe apps and databases where data is created
Ingestion (ELT)Copies source data into the warehouse on a schedule
Warehouse or lakehouseStores the data and runs the SQL
Transformation and modellingTurns raw tables into clean, tested business tables
Semantic layerHolds one definition per metric, shared by every consumer
BIDashboards and self-serve analysis
Activation (reverse ETL)Sends modelled data back into CRM, ads and support tools
Orchestration and observabilityRuns everything in order and tells you when something breaks
AI agentsAnswer 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.

The modern data stack as layers from left to right. Sources, such as SaaS apps, the product database, files and APIs, are loaded by ELT into a warehouse or lakehouse that holds raw tables and runs SQL. SQL models turn those into staging tables, marts, tests and metric definitions. Three consumers read the governed models: BI dashboards, reverse ETL to CRM and ad tools, and AI agents through MCP. Underneath all of it, orchestration and observability handle schedules, tests, freshness alerts and lineage.

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:

ChoiceTraditional data stackModern data stack
Where data is transformedBefore loading, on an ETL server (ETL)After loading, in SQL inside the warehouse (ELT)
StorageOn-premise database sized for peak loadCloud warehouse or lakehouse, storage separate from compute
PricingLicences and servers paid up frontUsage-based: rows synced, bytes scanned, seats
Who builds pipelinesEngineers writing custom codeManaged connectors, with code only for the long tail
Who builds modelsA central BI teamAnalysts and analytics engineers in version-controlled SQL
Who reads the outputDashboards and scheduled reportsDashboards, 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.

LayerExample tools
Ingestion (ELT)Airbyte, dlt, Fivetran, Weld
CDC from databasesDebezium, Estuary, Fivetran, Weld
WarehouseBigQuery, ClickHouse, Databricks, MotherDuck, Postgres, Redshift, Snowflake
Open table format and catalogApache Iceberg with AWS Glue, Apache Polaris, Amazon S3 Tables; Delta Lake
TransformationDataform, dbt, SQLMesh, Weld
Semantic layerCube, dbt Semantic Layer, LookML
BILightdash, Looker, Looker Studio, Metabase, Power BI, Sigma, Tableau
Reverse ETLCensus, Hightouch, Weld
OrchestrationAirflow, Dagster, Prefect, built-in schedulers in ELT and transformation tools
Data quality and observabilitydbt tests, Elementary, Monte Carlo, Soda
AI agentsClaude, 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.

LayerStartup, 1 analystData team of 5Regulated or enterprise
Sources3 to 6 SaaS tools10 to 30 tools plus the product databaseDozens of systems, ERP, on-premise databases
IngestionManaged ELT, daily or hourlyManaged ELT, CDC from the product databaseManaged ELT and CDC with SSO, audit logs, column hashing
StorageBigQuery, Postgres or MotherDuckBigQuery, Snowflake or DatabricksWarehouse plus Iceberg tables in your own cloud account
TransformationSQL models in one toolSQL or dbt in Git, with CI and testsSame, plus code review and separate dev and prod
Semantic layerA written list of metric definitionsAdd a tool once metrics are reused in many placesCertified metrics with access policies
BILooker Studio or MetabaseA self-serve BI toolEnterprise BI with row-level security
ActivationOne or two reverse ETL syncsReverse ETL for sales, marketing and supportReverse ETL with an approval step and audit trail
OrchestrationThe platform's scheduler and alertsOne orchestrator, tests and freshness alertsOrchestrator, observability tool, incident process
AI agentsOne read-only agent on martsAgents through MCP on governed modelsAgents 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

DaysGoalDone when
1 to 5Warehouse and connectorsBigQuery project set up, four sources syncing, full history loaded
6 to 20Staging modelsOne staging model per source table you use, with tests on primary keys
21 to 40The first three martsfct_orders, dim_customers and fct_marketing_daily reconciled to source
41 to 60Dashboards and metric definitionsThree dashboards on marts only, every metric written down once
61 to 75ActivationCustomer value and subscription status synced to HubSpot contacts
76 to 90Orchestration, alerts and agent accessOne 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:

SourceData to syncSync frequency
ShopifyOrders, order lines, refunds, productsHourly or daily
StripeCustomers, subscriptions, invoicesHourly or daily
HubSpotContacts, companies, dealsDaily
Google AdsAccounts, campaigns, daily campaign statsDaily

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):

SQL
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_stats

The 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.

SQL
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_cancelled

2. dim_customers: one row per customer across Shopify, Stripe and HubSpot.

SQL
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.

SQL
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_day

Blended 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_dayad_spendnet_salesordersnew_customersblended_cacmer
2026-10-062,14018,4202318824.328.61
2026-10-071,98016,9502147925.068.56
2026-10-082,31021,0602599723.819.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

Ready to start transforming your data into insights? Get started with Weld for free today.

FAQ