Modern Data Stack: Architecture Guide for Fivetran, dbt, Snowflake, and Looker [2026]

Ten years ago, enterprise-grade data infrastructure required Oracle licenses, dedicated ETL servers, and teams of consultants. The capital expenditure alone placed it out of reach for companies with fewer than a few hundred engineers. Today, a five-person startup can build the same analytical capability — Fivetran pulling data from 300+ sources, dbt transforming it with version-controlled SQL, Snowflake storing and querying it at warehouse scale, and Looker serving dashboards to business users — for under $5,000 per month. This architectural shift has a name: the Modern Data Stack.

This guide covers every layer of the modern data stack in production depth — what each layer does, which tools lead each category, how to select and sequence them, and what total cost looks like at different scales. By the end, you will have a concrete architecture for your team's specific data maturity level and a realistic budget model.

Modern Data Stack: Principles and Layers

The modern data stack is not a specific set of products — it is an architectural philosophy built on five principles:

1. Cloud-native: All components run in managed cloud infrastructure. No on-premises servers to provision or maintain.

2. ELT over ETL: Data loads raw into the warehouse first; transformation happens inside the warehouse using SQL. The warehouse's compute power handles transformation rather than a separate server.

3. Modular composition: Each layer is handled by a best-of-breed tool. Swap Fivetran for Airbyte without rebuilding the transformation layer. Replace Looker with Metabase without touching dbt models.

4. SQL-centric transformation: Data transformation is written as SQL (via dbt), not proprietary GUI workflows or custom Python scripts. SQL is version-controlled, testable, and accessible to analysts without engineering degrees.

5. Self-service access: Business users can query and explore data without submitting tickets to data engineering. The semantic layer translates raw metrics into business concepts.

The Five Layers

Sources (Salesforce, Stripe, Postgres, APIs)
       ↓
Ingestion (Fivetran, Airbyte)
       ↓
Storage (Snowflake, BigQuery, Databricks)
       ↓
Transformation (dbt)
       ↓
Orchestration (Airflow, dbt Cloud Scheduler)
       ↓
Visualization (Looker, Tableau, Metabase)

Data moves top-to-bottom. Each layer has distinct responsibilities and distinct tooling requirements.

Ingestion Layer: Moving Data from Sources to Storage

The ingestion layer extracts data from source systems and loads it into the storage layer. This is the most operationally complex layer to build from scratch — every data source has different authentication, pagination, rate limiting, and schema evolution behavior. Managed connectors exist precisely because this work is largely undifferentiated plumbing.

Fivetran

Fivetran connects to 500+ sources — CRM, ERP, advertising platforms, databases, file stores — with pre-built connectors that handle schema changes automatically. When Salesforce adds a new field, Fivetran detects the schema change and creates the corresponding column in the destination without manual intervention.

Fivetran's reliability is its core value proposition. Customers report 99.9%+ sync reliability without dedicated data engineering attention. The pricing model charges per "Monthly Active Rows" — rows read, updated, or deleted across all syncing tables in a billing period. This model scales costs with data activity rather than connector count, which can be expensive for high-churn tables.

Airbyte

Airbyte is the open-source alternative to Fivetran, with 300+ connectors and an active contributor community. The self-hosted version eliminates per-row costs — infrastructure cost only. Airbyte Cloud provides a fully managed service at pricing competitive with Fivetran.

Key differences from Fivetran: Airbyte requires more engineering attention to operate reliably (connector updates, infrastructure maintenance, schema change handling). The connector quality varies more across sources than Fivetran's more standardized catalog. For teams with engineering capacity and cost sensitivity, Airbyte self-hosted is typically 60–80% cheaper than Fivetran at equivalent volume.

When to Build Custom Extractors

Managed connectors cover standard SaaS applications well. Custom extractors are necessary for:

• Internal microservices and proprietary databases not in the connector catalog
• Legacy systems that require custom authentication or non-standard protocols
• APIs with unique pagination or rate-limiting behavior that generic connectors cannot handle

Custom extractors built on Singer (open-source protocol) or Airbyte's connector development kit (CDK) can be contributed back to the community, reducing long-term maintenance burden.

Storage Layer: Cloud Data Warehouses

The storage layer holds all data — raw, staged, and transformed — and provides the compute for queries and transformations.

Snowflake

Snowflake separates compute and storage completely. You can scale query compute independently of storage, or pause compute entirely when not in use (billing only for storage). This architecture makes Snowflake cost-efficient for variable query workloads — burst compute for end-of-quarter reporting without paying for it year-round.

Snowflake's Data Sharing feature enables sharing live data with external organizations without copying — particularly valuable for B2B data products. Multi-cloud support (AWS, GCP, Azure) and strong ecosystem integration (native Fivetran connector, deep dbt integration, most BI tools support Snowflake natively) make it the default choice for teams without strong cloud provider preferences.

Pricing: storage at $23–40/TB/month; compute at $2–4 per credit (Snowflake Credit = 1 hour of 1-node XS virtual warehouse). A typical mid-scale data team runs $5K–30K/month.

Google BigQuery

BigQuery charges per query based on bytes scanned, not uptime. A query scanning 1 TB of data costs approximately $5. This pricing model makes BigQuery extremely cost-efficient for irregular query patterns (research, ad-hoc exploration) and expensive for predictable high-frequency queries.

BigQuery's flat-rate pricing option ($2K–15K/month for dedicated slot reservations) makes sense when query volume is high and predictable. Native integration with Google's ML (BigQuery ML, Vertex AI) and analytics ecosystem (Looker, Data Studio) makes BigQuery natural for GCP-native organizations.

Databricks Lakehouse

Databricks merges data lake and warehouse capabilities through Delta Lake — an open table format that adds ACID transactions, schema enforcement, and efficient updates to Parquet files. The lakehouse architecture keeps data in open formats on object storage (S3, GCS, ADLS) while providing SQL query performance comparable to traditional warehouses.

Databricks is the right choice when: Python and Spark are primary tools (data science teams doing feature engineering alongside analytics), data volumes reach petabyte scale, or unstructured data processing (NLP, computer vision) needs to coexist with structured analytics in the same platform.

Transformation Layer: dbt

dbt (Data Build Tool) is the standard for ELT transformation in the modern data stack. SQL models define transformations as SELECT statements. dbt compiles models into the target warehouse dialect, manages dependencies between models, and runs them in the correct order.

Why dbt Became the Standard

Before dbt, transformation logic lived in stored procedures, handwritten Python scripts, or proprietary ETL GUIs. None of these supported version control, testing, or documentation as first-class features. dbt brought software engineering practices to data transformation:

• Version control: every model is a SQL file in a Git repository
• Testing: built-in tests for non-null, unique, referential integrity, and custom constraints run on every build
• Documentation: model descriptions and column-level docs generate a browsable data catalog automatically
• Lineage: dbt renders the dependency graph, showing exactly which models upstream changes will affect

Model Layers

dbt projects are typically organized into three layers:

Staging models rename, cast, and lightly clean raw source data. One staging model per source table. No business logic. The staging layer is the contract between the raw source and the rest of the project.

Intermediate models implement business logic joins — combining orders with customers, sessions with conversions, employees with departments. Intermediate models are internal; they are not directly used by BI tools.

Mart models are denormalized tables optimized for specific analytical use cases: marketing_mart, finance_mart, product_mart. BI tools query mart models. Mart models are the contract between data engineering and business users.

Incremental Models

For large tables, dbt incremental models process only new records since the last run rather than rebuilding the full table:

{{ config(materialized='incremental') }}

SELECT * FROM {{ ref('stg_events') }}
{% if is_incremental() %}
  WHERE event_time > (SELECT MAX(event_time) FROM {{ this }})
{% endif %}

Incremental models reduce transformation compute cost dramatically for high-volume append-only tables.

Orchestration Layer: Scheduling and Dependencies

Orchestration coordinates when each pipeline component runs, handles failures, and sends alerts.

Apache Airflow

Airflow is the most widely adopted orchestration platform, with a rich operator ecosystem that integrates with essentially every data tool. Pipelines are Python DAGs (Directed Acyclic Graphs) that express task dependencies explicitly.

Managed Airflow options (Cloud Composer on GCP, Amazon MWAA, Astronomer) eliminate the operational overhead of self-hosting the scheduler, metadata database, and workers. For teams without dedicated platform engineers, managed Airflow is nearly always worth the premium over self-hosted.

dbt Cloud Scheduler

For ELT pipelines where all orchestration consists of triggering dbt builds, dbt Cloud's built-in scheduler may be sufficient. It supports cron scheduling, dependency-based triggering, Slack and email notifications, and run history — without a separate orchestration layer. As pipeline complexity grows (mixing dbt runs with custom Python, API calls, and file transfers), a dedicated orchestration layer becomes necessary.

Dagster

Dagster treats data assets — tables, files, ML models — as first-class citizens rather than focusing on task graphs. This asset-centric model makes lineage tracking, data quality enforcement, and understanding downstream impact of changes more natural. Dagster's built-in testing and type-checking framework makes it particularly strong for teams building pipelines that need to satisfy explicit data contracts between teams.

Visualization Layer: BI and Analytics

The visualization layer converts warehouse data into dashboards, reports, and self-service exploration tools.

Looker

Looker's unique feature is its semantic layer (LookML): a modeling layer that defines business metrics, dimensions, and their relationships independent of the underlying SQL. Business users can explore data through Looker's web interface without writing SQL, confident that "revenue" means the same thing everywhere because it is defined once in LookML.

Looker's embedded analytics features — embedding dashboards in customer-facing applications via iframe or API — makes it a choice for data product companies as well as internal analytics. Enterprise pricing (seat-based, $1K–5K/user/year) limits adoption.

Tableau

Tableau's visualization capabilities are broader than Looker's, particularly for custom charts and complex data exploration. Tableau Public provides free publishing for public dashboards. Tableau Cloud (previously Tableau Online) provides managed hosting. Tableau is commonly adopted in organizations where stakeholders already know Tableau's interface — the switching cost to a different BI tool is high when users are invested in existing workbooks.

Metabase

Metabase is the open-source BI tool optimized for developer adoption. Self-hosted version is free. The UI prioritizes simplicity — non-technical users can write questions in plain language without learning a query language. Limited compared to Looker and Tableau for complex analytics, but excellent for startups and small teams where dashboarding simplicity and cost matter more than enterprise features.

Cost Modeling by Scale

Startup Stack (10 data sources, <50GB active data, <10 analysts)

Component	Tool	Monthly Cost
Ingestion	Airbyte Cloud	$200–500
Storage	BigQuery	$300–800
Transformation	dbt Core (open source)	$0
Orchestration	dbt Cloud Dev	$100
Visualization	Metabase	$0 (open source)
Total		$600–1,400

Growth Stack (50+ data sources, 500GB active data, 20–50 analysts)

Component	Tool	Monthly Cost
Ingestion	Fivetran	$3,000–8,000
Storage	Snowflake	$5,000–15,000
Transformation	dbt Cloud Team	$500
Orchestration	Astronomer (managed Airflow)	$1,000–3,000
Visualization	Looker	$5,000–15,000
Total		$14,500–41,500

The jump from startup to growth stack is driven primarily by Fivetran and Looker costs. Many teams delay Looker adoption in favor of Metabase or Superset until they have the analyst headcount that justifies LookML modeling investment.

Modern Data Stack Maturity Progression

Build the stack incrementally. Attempting to deploy all five layers simultaneously creates six months of integration work before any business value is delivered.

Level 1 — Raw Data Accessible: One ingestion connector, data lands in warehouse, basic SQL queries possible. Target: raw data visible in 2 weeks.

Level 2 — Cleaned Dimensions: dbt staging and mart models for 3–5 critical entities. Business users can query clean data without SQL expertise. Target: first dbt models in 4–6 weeks.

Level 3 — Orchestrated Pipelines: Scheduled dbt runs, failure alerting, data quality tests. Data freshness is predictable. Target: 6–8 weeks.

Level 4 — Self-Service Analytics: BI tool deployed, semantic layer defined, business users exploring data independently. Target: 10–14 weeks from start.

Level 5 — Data Platform: Data contracts between teams, reverse ETL for operational use cases (writing warehouse data back to CRM), ML feature store integration. Target: 6–12 months.

Conclusion

The modern data stack has lowered the cost and complexity of enterprise-grade analytics by an order of magnitude. The fundamental architecture — managed ingestion connectors feeding a cloud data warehouse, dbt handling SQL transformation, and a BI layer serving business users — is robust enough for petabyte-scale workloads and accessible enough for five-person startups. Select tools based on your current scale, team SQL capability, and budget constraints rather than future hypotheticals. A Metabase on BigQuery stack that delivers insights today is worth more than a Looker on Snowflake stack that takes six months to deploy.

The most important decision in modern data stack adoption is not tool selection — it is sequencing. Get raw data into a warehouse, build three good mart models, and deliver one dashboard that business users actually use before expanding scope. That first end-to-end data product builds organizational trust in data infrastructure, which justifies every subsequent investment.