🔄 Data Pipeline Architect — ETL/ELT Design For 2026

Data Pipeline Architecture: Modern Data Stack, 5 Sources → Snowflake, Legacy Migration

A data pipeline architecture produces structured modern stack replacing legacy patchwork. Your situation — mixed custom + Glue + manual exports — is classic modernization opportunity. This design: modern stack (Fivetran + dbt + Airflow + Snowflake), batch-first with clear streaming path if needed, robust monitoring, 3-month migration plan.

Sources + Destinations Overview

Sources:

Destination:

• Snowflake (data warehouse)
• Metabase (dashboards, connects to Snowflake)
• Reverse ETL for ops tools (optional future)

Data flow:

Sources → Fivetran → Snowflake (raw layer)
                          ↓
                    dbt transformations
                          ↓
                    Snowflake (staging + marts)
                          ↓
                    Metabase + other consumers
                          ↓
                  (Optional) Reverse ETL

ETL vs. ELT Decision

ELT (Extract, Load, Transform) — Modern Choice

Why ELT for your stack:

• Snowflake has massive compute + storage
• Transformation cost-effective in-warehouse
• dbt provides version-controlled, testable transformations
• Preserves raw data for re-processing
• Faster time-to-data (load first, transform later)

When ETL (legacy) would fit:

• Very expensive compute target
• Compliance requiring transformation before storage
• Massive volumes where pre-processing saves significant cost

Your situation: ELT fits. Snowflake transform is economical + flexible.

Batch vs. Streaming

Batch (daily + hourly) — Primary approach

Why batch for you:

• Freshness needs: daily/hourly (not real-time)
• Simpler to build + maintain
• Lower cost
• Easier debugging
• Team capacity supports

Streaming reserved for:

• Customer-facing features (separate pipeline — not part of this analytics stack)
• Future use cases if freshness requirements tighten

Architecture:

• Daily refreshes: most sources (Postgres, Stripe, Salesforce, Intercom)
• Hourly refreshes: product events + marketing data (Mixpanel)
• If latency requirements tighten: consider Kafka + Snowflake streaming in 12-18 months

Tool Stack Recommendations

Ingestion: Fivetran

Why Fivetran:

• Managed connectors for all 5 sources
• Automated schema evolution
• Low maintenance
• Compliance features (SOC 2, GDPR)

Cost: $1,500-3,000/month based on your volume (5-15M monthly active rows)

Alternatives:

• Airbyte (open-source, self-hosted) — lower cost but more maintenance
• Segment (for product events specifically) — already have Mixpanel
• Custom ingestion — more engineering, less reliable

Transformation: dbt (Data Build Tool)

Why dbt:

• Industry standard for analytics engineering
• Version-controlled SQL
• Testing framework
• Documentation generation
• Lineage tracking

dbt Cloud vs. Core:

• dbt Core (free) — self-hosted, requires setup
• dbt Cloud ($100-300/month) — managed, scheduling, UI, worth it for small team

Recommendation: dbt Cloud for your team size (saves engineering time).

Orchestration: Airflow (or Prefect)

Why orchestration tool:

• Manage complex dependencies
• Retry logic
• Monitoring
• Alerting

Options:

• Apache Airflow (managed via AWS MWAA): industry standard, more setup
• Prefect Cloud: modern, Python-native, easier learning curve
• Dagster: asset-focused, newer but excellent
• dbt Cloud scheduler: simple, may suffice if only dbt scheduling needed

Recommendation for your scale: start with dbt Cloud scheduler + Fivetran native scheduling. Add Airflow/Prefect when complexity grows.

Cost: dbt Cloud includes scheduling. Airflow MWAA: ~$350-700/month if needed.

Warehouse: Snowflake (existing)

Sizing:

• Compute: Small/Medium warehouse for transformations
• Storage: manageable given data volume
• Auto-suspend enabled (save costs)
• Separate warehouses for ingestion, transformation, BI query

Current cost: $5K/month — reasonable for your scale.

Data Quality: dbt tests + custom monitoring

dbt built-in tests:

• not_null, unique, accepted_values, relationships
• Custom tests for business logic

Additional monitoring:

• Monte Carlo or Datafold (if budget) — data observability platforms
• Custom alerting via Slack/PagerDuty

Idempotency + Reliability Patterns

Idempotent Design

Principle: pipelines should be safely re-runnable without duplicating data.

Patterns:

Merge (upsert) operations:

-- In dbt: use incremental materialization with merge strategy
MERGE INTO warehouse.users target
USING staging.users_new source
ON target.user_id = source.user_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Full-refresh option: available when data quality issues require reset.

Watermarks for incremental loads:

• Track last-loaded timestamp
• Re-run from watermark if needed
• Safely re-process without duplicates

Reliability Patterns

Retry logic:

• Exponential backoff
• Max retry count (3-5)
• Different retry strategies per failure type

Circuit breaker:

• If source API failing, pause subsequent runs
• Don't compound failures

Data contracts:

• Formal schemas between producers + consumers
• Breaking changes go through review
• Schema evolution tools (Avro, Protobuf, or dbt source freshness)

Dead letter queues:

• Failed records captured separately
• Investigated + replayed
• Not lost

Monitoring + Alerting

Freshness Monitoring

Monitor:

• Last successful load time per source
• Expected vs. actual run schedule
• Lag from source to warehouse

Alerts:

• Source data >2x expected lag: warning
• Source data >4x: critical
• Destination dashboard stale >24hr: user-visible warning

Completeness Monitoring

Monitor:

• Expected vs. actual row counts
• Min/max values
• Null rate changes
• Unique count changes

dbt tests: run on every transformation

Anomaly detection:

• Sudden 50%+ drop in expected rows
• Schema changes (unexpected columns)

Quality Monitoring

Automated data quality tests:

• Foreign key validity
• Business logic (e.g., revenue always positive)
• Cross-table consistency
• Expected distributions

Monitoring platform options:

• Monte Carlo ($2-5K/month for your scale)
• Datafold (similar)
• Custom Slack alerts (free, more DIY)

Latency Monitoring

Track:

• End-to-end latency (source → warehouse)
• Per-stage latency
• Recovery time from failures

SLAs:

• Daily data: available by 8am ET
• Hourly data: within 2 hours
• Recovery from failure: 4 hours

Implementation Plan (3-Month Migration)

Month 1: Foundation

Week 1-2:

• Fivetran setup + connectors configured
• Initial data loaded to Snowflake raw layer
• Connection testing
• Documentation of current pipelines

Week 3-4:

• dbt project initialized
• Staging models for each source
• First quality tests
• Team training on dbt

Month 2: Transformation

Week 5-6:

• Dimensional modeling (facts + dimensions)
• Core business metrics defined as models
• Documentation in dbt

Week 7-8:

• Marts for reporting
• Metabase connected to new marts
• Parallel running (old + new) for validation

Month 3: Migration + Stabilization

Week 9-10:

• Old pipelines decommissioned gradually
• Monitoring + alerting in place
• Runbooks documented

Week 11-12:

• Performance optimization
• Cost optimization (auto-suspend, warehouse sizing)
• Team training on runbooks
• Post-migration retrospective

Cost Estimation

Annual Tool Costs:

Within your $50K tool budget if skipping Monte Carlo initially.

Alternative: reduce Fivetran cost by using Airbyte (self-hosted):

• Airbyte self-hosted: minimal infrastructure cost (~$200/month)
• BUT: more engineering time for setup + maintenance
• Tradeoff: save $25K/year in tool cost, cost ~$15K in engineering time

Key Takeaways

• Modern ELT stack: Fivetran (ingestion) + dbt (transformation) + Snowflake (warehouse) + scheduler (dbt Cloud sufficient for your scale). $50-68K/year tool cost fits your budget.
• Batch-first architecture. Daily + hourly refreshes cover your freshness needs. Add streaming only when customer-facing real-time requires — separate pipeline.
• Idempotent design with dbt incremental models + watermarks. Pipelines safely re-runnable. Schema contracts between sources + warehouse prevent drift pain.
• Monitoring 4 dimensions: freshness, completeness, quality, latency. dbt tests + Slack alerts sufficient for MVP. Monte Carlo upgrade ($36K/year) when complexity grows.
• 3-month migration: Month 1 foundation, Month 2 transformation, Month 3 migration + stabilization. Parallel run validates + prevents data loss. Documentation + runbooks critical for ongoing operations.