Architecting Data

Data Architecture

Purpose

Guide architects and platform engineers through strategic data architecture decisions for modern cloud-native data platforms.

When to Use This Skill

Invoke this skill when:

• Designing a new data platform or modernizing legacy systems
• Choosing between data lake, data warehouse, or data lakehouse
• Deciding on data modeling approaches (dimensional, normalized, data vault, wide tables)
• Evaluating centralized vs data mesh architecture
• Selecting open table formats (Apache Iceberg, Delta Lake, Apache Hudi)
• Designing medallion architecture (bronze, silver, gold layers)
• Implementing data governance and cataloging

Core Concepts

1. Storage Paradigms

Three primary patterns for analytical data storage:

Data Lake: Centralized repository for raw data at scale

• Schema-on-read, cost-optimized ($0.02-0.03/GB/month)
• Use when: Diverse data sources, exploratory analytics, ML/AI training data

Data Warehouse: Structured repository optimized for BI

• Schema-on-write, ACID transactions, fast queries
• Use when: Known BI requirements, strong governance needed

Data Lakehouse: Hybrid combining lake flexibility with warehouse reliability

• Open table formats (Iceberg, Delta Lake), ACID on object storage
• Use when: Mixed BI + ML workloads, cost optimization (60-80% cheaper than warehouse)

Decision Framework:

• BI/Reporting only + Known queries → Data Warehouse
• ML/AI primary + Raw data needed → Data Lake or Lakehouse
• Mixed BI + ML + Cost optimization → Data Lakehouse (recommended)
• Exploratory/Unknown use cases → Data Lake

For detailed comparison, see references/storage-paradigms.md.

2. Data Modeling Approaches

Four primary modeling patterns:

Dimensional (Kimball): Star/snowflake schemas for BI

• Use when: Known query patterns, BI dashboards, trend analysis

Normalized (3NF): Eliminate redundancy for transactional systems

• Use when: OLTP systems, frequent updates, strong consistency

Data Vault 2.0: Flexible model with complete audit trail

• Use when: Compliance requirements, multiple sources, agile warehousing

Wide Tables: Denormalized, optimized for columnar storage

• Use when: ML feature stores, data science notebooks, high-performance dashboards

Decision Framework:

• Analytical (BI) + Known queries → Dimensional (Star Schema)
• Transactional (OLTP) → Normalized (3NF)
• Compliance/Audit → Data Vault 2.0
• Data Science/ML → Wide Tables

For detailed patterns, see references/modeling-approaches.md.

3. Data Mesh Principles

Decentralized architecture for large organizations (>500 people).

Four Core Principles: 1. Domain-oriented decentralization 2. Data as a product (SLAs, quality, documentation) 3. Self-serve data infrastructure 4. Federated computational governance

Readiness Assessment (Score 1-5 each): 1. Domain clarity 2. Team maturity 3. Platform capability 4. Governance maturity 5. Scale need 6. Organizational buy-in

Scoring: 24-30: Strong candidate | 18-23: Hybrid | 12-17: Build foundation first | 6-11: Centralized

Red Flags: Small org (<100 people), unclear domains, no platform team, weak governance

For full guide, see references/data-mesh-guide.md.

4. Medallion Architecture

Standard lakehouse pattern: Bronze (raw) → Silver (cleaned) → Gold (business-level)

Bronze Layer: Exact copy of source data, immutable, append-only

Silver Layer: Validated, deduplicated, typed data

Gold Layer: Business logic, aggregates, dimensional models, ML features

Data Quality by Layer:

• Bronze → Silver: Schema validation, type checks, deduplication
• Silver → Gold: Business rule validation, referential integrity
• Gold: Anomaly detection, statistical checks

For patterns, see references/medallion-pattern.md.

5. Open Table Formats

Enable ACID transactions on data lakes:

Apache Iceberg: Multi-engine, vendor-neutral (Context7: 79.7 score)

• Use when: Avoid vendor lock-in, multi-engine flexibility

Delta Lake: Databricks ecosystem, Spark-optimized

• Use when: Committed to Databricks

Apache Hudi: Optimized for CDC and frequent upserts

• Use when: CDC-heavy workloads

Recommendation: Apache Iceberg for new projects (vendor-neutral, broadest support)

For comparison, see references/table-formats.md.

6. Modern Data Stack

Standard Layers:

• Ingestion: Fivetran, Airbyte, Kafka
• Storage: Snowflake, Databricks, BigQuery
• Transformation: dbt (Context7: 87.0 score), Spark
• Orchestration: Airflow, Dagster, Prefect
• Visualization: Tableau, Looker, Power BI
• Governance: DataHub, Alation, Great Expectations

Tool Selection:

• Fivetran vs Airbyte: Pre-built connectors vs cost-sensitive
• Snowflake vs Databricks: BI-focused vs ML-focused
• dbt vs Spark: SQL-based vs large-scale processing

For detailed recommendations, see references/tool-recommendations.md and references/modern-data-stack.md.

7. Data Governance

Data Catalog: Searchable inventory (DataHub, Alation, Collibra)

Data Lineage: Track data flow (OpenLineage, Marquez)

Data Quality: Validation and testing (Great Expectations, Soda, dbt tests)

Access Control:

• RBAC: Role-based (sales_analyst role)
• ABAC: Attribute-based (row-level security)
• Column-level: Dynamic data masking for PII

For governance patterns, see references/governance-patterns.md.

Decision Frameworks

Framework 1: Storage Paradigm Selection

Step 1: Identify Primary Use Case

• BI/Reporting only → Data Warehouse
• ML/AI primary → Data Lake or Lakehouse
• Mixed BI + ML → Data Lakehouse
• Exploratory → Data Lake

Step 2: Evaluate Budget

• High budget, known queries → Data Warehouse
• Cost-sensitive, flexible → Data Lakehouse

Recommendation by Org Size:

• Startup (<50): Data Warehouse (simplicity)
• Growth (50-500): Data Lakehouse (balance)
• Enterprise (>500): Hybrid or unified Lakehouse

See references/decision-frameworks.md.

Framework 2: Data Modeling Approach

Decision Tree:

• Analytical (BI) workload → Dimensional or Wide Tables
• Transactional (OLTP) → Normalized (3NF)
• Compliance/Audit → Data Vault 2.0
• Data Science/ML → Wide Tables

See references/decision-frameworks.md.

Framework 3: Data Mesh Readiness

Use 6-factor assessment. Score interpretation:

• 24-30: Proceed with data mesh
• 18-23: Hybrid approach
• 12-17: Build foundation first
• 6-11: Centralized

See references/decision-frameworks.md.

Framework 4: Open Table Format Selection

Decision Tree:

• Multi-engine flexibility → Apache Iceberg
• Databricks ecosystem → Delta Lake
• Frequent upserts/CDC → Apache Hudi

Recommendation: Apache Iceberg for new projects

See references/decision-frameworks.md.

Common Scenarios

Startup Data Platform

Context: 50-person startup, PostgreSQL + MongoDB + Stripe

Recommendation:

• Storage: BigQuery or Snowflake
• Ingestion: Airbyte or Fivetran
• Transformation: dbt
• Orchestration: dbt Cloud
• Architecture: Simple data warehouse

See references/scenarios.md.

Enterprise Modernization

Context: Legacy Oracle warehouse, need cloud migration

Recommendation:

• Storage: Data Lakehouse (Databricks or Snowflake with Iceberg)
• Strategy: Incremental migration with CDC
• Architecture: Medallion (bronze, silver, gold)
• Cost Savings: 60-80%

See references/scenarios.md.

Data Mesh Assessment

Context: 200-person company, 5-person central data team

Recommendation: NOT YET. Build foundation first.

• Organization too small (<500 recommended)
• Central team not yet bottleneck
• Invest in self-serve platform and governance

See references/scenarios.md.

Tool Recommendations

Research-Validated (Context7, December 2025)

dbt: Score 87.0, 3,532+ code snippets

• SQL-based transformations, version control, testing
• Industry standard for data transformation

Apache Iceberg: Score 79.7, 832+ code snippets

• Open table format, multi-engine, vendor-neutral
• Production-ready (Netflix, Apple, Adobe)

Tool Stack by Use Case:

Startup: BigQuery + Airbyte + dbt + Metabase (<$1K/month)

Growth: Snowflake + Fivetran + dbt + Airflow + Tableau ($10K-50K/month)

Enterprise: Snowflake + Databricks + Fivetran + Kafka + dbt + Airflow + Alation ($50K-500K/month)

See references/tool-recommendations.md.

Implementation Patterns

Pattern 1: Medallion Architecture

-- Bronze: Raw ingestion
CREATE TABLE bronze.raw_customers (_ingested_at TIMESTAMP, _raw_data STRING);

-- Silver: Cleaned
CREATE TABLE silver.customers AS
SELECT json_extract(_raw_data, '$.id') AS customer_id, ...
FROM bronze.raw_customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY _ingested_at DESC) = 1;

-- Gold: Business-level
CREATE TABLE gold.fact_sales AS
SELECT s.order_id, d.date_key, c.customer_key, ...
FROM silver.sales s
JOIN gold.dim_date d ON s.order_date = d.date;

Pattern 2: Apache Iceberg Table

CREATE TABLE catalog.db.sales (order_id BIGINT, amount DECIMAL(10,2))
USING iceberg
PARTITIONED BY (days(order_date));

-- Time travel
SELECT * FROM catalog.db.sales TIMESTAMP AS OF '2025-01-01';

Pattern 3: dbt Transformation

-- models/staging/stg_customers.sql
WITH source AS (SELECT * FROM {{ source('raw', 'customers') }}),
cleaned AS (
  SELECT customer_id, UPPER(customer_name) AS customer_name
  FROM source WHERE customer_id IS NOT NULL
)
SELECT * FROM cleaned

For complete examples, see examples/.

Best Practices

1. Start simple: Avoid over-engineering; begin with warehouse or basic lakehouse 2. Invest in governance early: Catalog, lineage, quality from day one 3. Medallion architecture: Use bronze-silver-gold for clear quality layers 4. Open table formats: Prefer Iceberg or Delta Lake to avoid vendor lock-in 5. Assess mesh readiness: Don't decentralize prematurely (<500 people) 6. Automate quality: Integrate tests (Great Expectations, dbt) into CI/CD 7. Monitor pipelines: Observability is critical (freshness, quality, health) 8. Document as code: Use dbt docs, DataHub, YAML for self-service 9. Incremental loading: Only load new/changed data (watermark columns) 10. Business alignment: Align architecture to outcomes, not just technologies

Anti-Patterns

• ❌ Data swamp: Lake without governance or cataloging
• ❌ Premature mesh: Mesh before organizational readiness
• ❌ Tool sprawl: Too many tools without integration
• ❌ No quality checks: "Garbage in, garbage out"
• ❌ Centralized bottleneck: Single team in large org (>500 people)
• ❌ Vendor lock-in: Proprietary formats without migration path
• ❌ No lineage: Can't answer "where did this come from?"
• ❌ Over-engineering: Complex architecture for simple use cases

Integration with Other Skills

Direct Dependencies:

• ingesting-data: ETL/ELT mechanics, Fivetran, Airbyte implementation
• data-transformation: dbt and Dataform detailed implementation
• streaming-data: Kafka, Flink for real-time pipelines

Complementary:

• databases-relational: PostgreSQL, MySQL as source systems
• databases-document: MongoDB, DynamoDB as sources
• ai-data-engineering: Feature stores, ML training pipelines
• designing-distributed-systems: CAP theorem, consistency models
• observability: Monitoring pipeline health, data quality metrics

Downstream:

• visualizing-data: BI and dashboard patterns
• sql-optimization: Query performance tuning

Common Workflows:

End-to-End Analytics:

data-architecture (warehouse) → ingesting-data (Fivetran) →
data-transformation (dbt) → visualizing-data (Tableau)

Data Platform for AI/ML:

data-architecture (lakehouse) → ingesting-data (Kafka) →
data-transformation (dbt features) → ai-data-engineering (feature store)

Further Reading

Reference Files:

• decision-frameworks.md - All 4 decision frameworks in detail
• storage-paradigms.md - Lake vs warehouse vs lakehouse
• modeling-approaches.md - Dimensional, normalized, data vault, wide
• data-mesh-guide.md - Data mesh principles and implementation
• medallion-pattern.md - Bronze, silver, gold layers
• table-formats.md - Iceberg, Delta Lake, Hudi comparison
• tool-recommendations.md - Tool analysis and recommendations
• modern-data-stack.md - Tool categories and selection
• governance-patterns.md - Catalog, lineage, quality, access control
• scenarios.md - Startup, enterprise, data mesh scenarios

Examples:

• examples/dbt-project/ - dbt project with medallion architecture

External Resources:

• Apache Iceberg: https://iceberg.apache.org/
• dbt Documentation: https://docs.getdbt.com/
• Data Mesh (Zhamak Dehghani): https://www.datamesh-architecture.com/
• Databricks Medallion: https://www.databricks.com/glossary/medallion-architecture

dbt Project Example

Example dbt project structure demonstrating medallion architecture (bronze → silver → gold).

Project Structure

my_dbt_project/
├── models/
│   ├── staging/           # Silver layer (bronze → silver)
│   │   ├── stg_customers.sql
│   │   ├── stg_orders.sql
│   │   └── schema.yml     # Tests and documentation
│   ├── intermediate/      # Silver layer joins
│   │   └── int_customer_orders.sql
│   └── marts/             # Gold layer (business-level)
│       ├── fact_sales.sql
│       ├── dim_customer.sql
│       └── schema.yml
├── dbt_project.yml
└── profiles.yml

Installation

pip install dbt-snowflake
dbt init my_dbt_project
cd my_dbt_project
dbt run
dbt test

Model Examples

See individual SQL files for transformation logic and schema.yml for tests.

Data Mesh Guide

Decentralized data architecture for large organizations. Addresses centralized bottlenecks through domain ownership.

Table of Contents

• Four Core Principles
• 1. Domain-Oriented Decentralization
• 2. Data as a Product
• 3. Self-Serve Data Infrastructure
• 4. Federated Computational Governance
• When to Use Data Mesh
• Lakehouse Implementation
• Readiness Assessment

Four Core Principles

1. Domain-Oriented Decentralization

Each business domain owns its data:

• Sales domain owns sales data
• Marketing domain owns marketing data
• Product domain owns product usage data

Traditional vs Mesh:

Traditional: All Domains → Central Data Team → Warehouse
Data Mesh:   Sales Domain → Sales Data Product → Consumers
             Marketing Domain → Marketing Data Product → Consumers

2. Data as a Product

Treat data like products with quality commitments:

• SLAs: Freshness, availability, quality guarantees
• Documentation: Schema, use cases, examples
• Support: Help channels, onboarding
• Consumers as customers: Internal users treated like external customers

Data Product Contract Example:

data_product:
  name: sales-orders-product
  domain: sales
  owner: sales-data-team@company.com
  sla:
    freshness: 15 minutes
    availability: 99.9%
    quality_score: >95%
  schema:
    format: Avro
    version: 2.1.0
  access:
    read: SELECT on sales.orders
    authentication: OAuth2

3. Self-Serve Data Infrastructure

Platform team provides tooling for domain autonomy:

• Infrastructure as code (Terraform, Pulumi)
• CI/CD pipelines for data products
• Observability (monitoring, alerting, lineage)
• Data catalog (discovery)
• Governance tooling

Domain teams can do (without platform team):

• Deploy new data pipeline
• Update schema
• Monitor data quality
• Set up alerts
• Publish data product to catalog

4. Federated Computational Governance

Global policies with local implementation:

• Global Policy: "All PII must be encrypted at rest"
• Domain Implementation: Sales team encrypts customer email
• Automated Check: Platform scans for unencrypted PII
• Audit: Central governance reviews quarterly

When to Use Data Mesh

Strong Candidates:

• Large organizations (>500 people)
• Clear business domains with distinct ownership
• Central data team is bottleneck
• Domain teams have data engineering skills

Not Ready:

• Small organizations (<100 people)
• Unclear domain boundaries
• No platform engineering team
• Domain teams lack data skills

Lakehouse Implementation

Each domain owns bronze-silver-gold for their data products:

Sales Domain:
  bronze.sales_raw → silver.sales_cleaned → gold.sales_analytics (data product)

Marketing Domain:
  bronze.marketing_raw → silver.marketing_cleaned → gold.marketing_analytics

Cross-Domain:
  gold.customer_360 (combines sales + marketing silver tables)

Readiness Assessment

Score 1-5 for each factor: 1. Domain clarity 2. Team maturity 3. Platform capability 4. Governance maturity 5. Scale need 6. Organizational buy-in

24-30: Strong mesh candidate 18-23: Hybrid approach 12-17: Build foundation first 6-11: Centralized approach

See decision-frameworks.md for full assessment.

Data Architecture Decision Frameworks

Table of Contents

1. Storage Paradigm Selection 2. Data Modeling Approach 3. Data Mesh Readiness Assessment 4. Open Table Format Selection

---

Storage Paradigm Selection

Decision Tree

Start: What is your primary use case?
│
├─ BI/Reporting only
│  └─ Known queries, structured data?
│     ├─ Yes → Data Warehouse
│     └─ No → Data Lakehouse
│
├─ ML/AI primary
│  └─ Need raw data + feature engineering?
│     ├─ Yes → Data Lake or Lakehouse
│     └─ No → Data Lakehouse
│
├─ Mixed BI + ML
│  └─ Budget constraints?
│     ├─ High budget → Data Warehouse + Data Lake
│     └─ Optimizing cost → Data Lakehouse
│
└─ Exploratory/Unknown
   └─ Data Lake (preserve raw, decide later)

Detailed Criteria Matrix

Recommendation by Organization Size

Startup (<50 people):

• Primary: Data Warehouse (Snowflake, BigQuery)
• Why: Simplicity over flexibility; focus on BI
• Cost: Pay-per-use pricing manageable at small scale
• Skills: Analysts can write SQL; no data engineers needed

Growth (50-500 people):

• Primary: Data Lakehouse (Databricks with Delta Lake, or Iceberg on S3)
• Why: Balance cost and features; support BI and emerging ML
• Cost: 60-80% cheaper than warehouse at scale
• Skills: Hire 1-2 data engineers for lakehouse management

Enterprise (>500 people):

• Primary: Hybrid or unified Lakehouse
• Option 1 (Hybrid): Snowflake for BI + Databricks for ML
• Option 2 (Unified): Databricks lakehouse for all workloads
• Why: Scale and specialization requirements
• Cost: Negotiate enterprise contracts; leverage reserved capacity

Cost-Benefit Analysis

Data Lake:

• Storage Cost: $0.02-0.03/GB/month (S3, GCS)
• Compute Cost: Pay per query (Athena, Presto)
• Total Cost (1TB): ~$30-50/month storage + query costs
• Best for: Archive, exploration, raw data retention

Data Warehouse:

• Storage Cost: $20-40/TB/month (Snowflake, BigQuery)
• Compute Cost: $2-5/credit-hour (varies by size)
• Total Cost (1TB): ~$500-2,000/month (including compute)
• Best for: Known BI workloads, fast queries

Data Lakehouse:

• Storage Cost: $0.02-0.03/GB/month (lake storage)
• Compute Cost: $2-5/DBU-hour (Databricks) or similar
• Total Cost (1TB): ~$100-500/month (depending on compute)
• Best for: Mixed workloads, cost optimization

---

Data Modeling Approach

Decision Matrix

Primary Workload?
│
├─ Analytical (BI, Dashboards)
│  └─ Query patterns known?
│     ├─ Yes → Dimensional (Star Schema)
│     └─ No → Wide Tables or Data Vault
│
├─ Transactional (OLTP)
│  └─ Normalized (3NF)
│
├─ Compliance/Audit Required?
│  └─ Yes → Data Vault 2.0
│  └─ No → Dimensional or Wide
│
├─ Data Science/ML
│  └─ Wide Tables (Feature Tables)
│
└─ Multi-source Integration
   └─ Data Vault 2.0

Criteria by Model

Use Case Mapping

Dimensional Modeling (Star/Snowflake):

• Sales analytics dashboard
• Customer behavior analysis
• Financial reporting
• Marketing attribution
• Operational KPIs

Normalized Modeling (3NF):

• E-commerce order management
• CRM systems
• ERP systems
• Inventory management
• Transactional databases

Data Vault 2.0:

• Banking (regulatory compliance)
• Healthcare (HIPAA audit trails)
• Multi-source master data management
• Long-term historical archives
• Insurance claims processing

Wide Tables (Denormalized):

• ML feature stores
• Data science notebooks
• High-performance dashboards
• Aggregated reporting tables
• Real-time analytics

Slowly Changing Dimensions (SCD) Selection

Type 1 (Overwrite):

• Use when: History doesn't matter
• Example: Fixing typos in customer names
• Storage: No additional columns
• Query Complexity: Simple

Type 2 (Add Row):

• Use when: Full history is critical
• Example: Customer address changes
• Storage: Add effective_date, end_date, is_current
• Query Complexity: Filter on is_current or date ranges

Type 3 (Add Column):

• Use when: Need to compare current vs previous
• Example: Customer's previous and current segment
• Storage: Add previous_value column
• Query Complexity: Simple, limited to one previous value

Type 6 (Hybrid 1+2+3):

• Use when: Need full history + current value in all rows
• Example: Product pricing with full history and current price
• Storage: Combine Type 2 + add current_value to all rows
• Query Complexity: Medium

---

Data Mesh Readiness Assessment

6-Factor Assessment (Score 1-5 each)

1. Domain Clarity:

• 1: No clear domains, everything is interconnected
• 3: Some domains exist but boundaries are fuzzy
• 5: Clear bounded contexts (Domain-Driven Design), distinct ownership

2. Team Maturity:

• 1: Domain teams have no data skills
• 3: Some teams have analysts, but no data engineers
• 5: Domain teams have data engineers, can own pipelines

3. Platform Capability:

• 1: No self-serve infrastructure, manual provisioning
• 3: Some automation (Terraform), but requires platform team involvement
• 5: Fully self-serve platform (domains can deploy independently)

4. Governance Maturity:

• 1: No data governance, ad-hoc policies
• 3: Centralized governance, but difficult to enforce
• 5: Federated governance with automated enforcement

5. Scale Need:

• 1: Central team is adequate, no bottleneck
• 3: Central team is sometimes slow, but manageable
• 5: Central team is clear bottleneck, blocks domain progress

6. Organizational Buy-In:

• 1: No leadership support, resistance to change
• 3: Leadership is interested, but skeptical
• 5: Strong leadership support, cultural readiness

Scoring Interpretation

24-30 points: Strong Data Mesh Candidate

• Proceed with data mesh implementation
• Start with pilot domain (sales or marketing)
• Build self-serve platform capabilities
• Establish federated governance framework

18-23 points: Hybrid Approach

• Implement data mesh for critical domains only
• Keep less critical domains centralized
• Gradually expand mesh as maturity increases
• Example: Sales domain owns data products, others remain central

12-17 points: Build Foundation First

• Invest in self-serve platform (infrastructure as code)
• Improve governance (catalog, lineage, quality)
• Train domain teams on data engineering
• Reassess in 6-12 months

6-11 points: Centralized Approach

• Data mesh is premature
• Focus on scaling central team
• Improve centralized platform efficiency
• Consider data mesh at >500 people or when bottleneck emerges

Red Flags (Do NOT Pursue Data Mesh)

Organizational:

• Small organization (<100 people)
• Unclear domain boundaries
• No platform engineering team
• Leadership resistance

Technical:

• Weak data governance
• No catalog or lineage tooling
• Poor observability
• Immature data engineering practices

Cultural:

• Domain teams lack data ownership mindset
• Centralized team is not a bottleneck
• No appetite for decentralization
• Resistance to data-as-product thinking

Readiness Checklist

Before starting data mesh:

• [ ] Organization has >500 people (or >200 with clear need)
• [ ] 3+ clear business domains with distinct ownership
• [ ] Domain teams have data engineering skills or budget to hire
• [ ] Self-serve platform exists (infrastructure as code, CI/CD)
• [ ] Data catalog and lineage tooling in place
• [ ] Federated governance processes defined
• [ ] Central data team is clear bottleneck (proven, not assumed)
• [ ] Leadership buy-in and commitment to cultural shift
• [ ] Budget for domain-level data teams
• [ ] Pilot domain identified with executive sponsor

---

Open Table Format Selection

Feature Comparison Matrix

Decision Tree

Start: What is your priority?
│
├─ Multi-engine flexibility (avoid lock-in)
│  └─ Apache Iceberg
│
├─ Databricks ecosystem (committed)
│  └─ Delta Lake
│
├─ Frequent upserts/CDC (Change Data Capture)
│  └─ Apache Hudi
│
├─ Partition evolution without rewrites
│  └─ Apache Iceberg
│
├─ Maximum community support
│  └─ Apache Iceberg (broadest adoption)
│
├─ Streaming-first architecture
│  └─ Apache Hudi or Delta Lake
│
└─ Cost optimization (query performance)
   └─ Apache Iceberg or Delta Lake

Vendor Lock-In Analysis

Apache Iceberg:

• Governance: Apache Foundation (vendor-neutral)
• Support: AWS (Athena, EMR), Google (BigQuery), Snowflake, Databricks, Cloudera
• Migration: Easy to switch query engines (multi-engine design)
• Lock-In Risk: Lowest

Delta Lake:

• Governance: Linux Foundation (Databricks-led)
• Support: Databricks (primary), AWS EMR, Azure Synapse, limited Trino
• Migration: Possible but optimized for Databricks
• Lock-In Risk: Medium (Databricks ecosystem)

Apache Hudi:

• Governance: Apache Foundation (vendor-neutral)
• Support: AWS (EMR, Glue), Uber, Alibaba, ByteDance
• Migration: Possible, but fewer query engine options
• Lock-In Risk: Low-Medium

Workload-Based Recommendations

Batch Analytics (BI, Reporting):

• Best: Apache Iceberg
• Why: Multi-engine support, best query performance optimizations
• Alternative: Delta Lake if using Databricks

Streaming Analytics (Real-Time):

• Best: Apache Hudi or Delta Lake
• Why: Optimized for streaming upserts and CDC
• Alternative: Iceberg with Flink for streaming

Mixed Batch + Streaming:

• Best: Apache Iceberg
• Why: Balanced support for both patterns
• Alternative: Delta Lake (streaming excellent, batch good)

ML/Data Science:

• Best: Apache Iceberg or Delta Lake
• Why: Time travel, schema evolution, snapshot isolation
• Alternative: Both are excellent; choose based on ecosystem

CDC-Heavy Workloads:

• Best: Apache Hudi
• Why: Optimized for record-level updates (Merge-on-Read)
• Alternative: Delta Lake (also excellent for CDC)

Migration Considerations

Migrating from Hive Tables:

• Iceberg: In-place migration (no data rewrite)
• Delta Lake: Requires data rewrite
• Hudi: Requires data rewrite

Migrating from Parquet:

• All: Require metadata creation; data files can often be reused

Cross-Format Migration:

• Iceberg ↔ Delta Lake: Possible with tools (e.g., Delta Lake Universal Format)
• Hudi ↔ Others: More complex, may require full rewrite

Recommendation Summary

For New Projects:

• Default: Apache Iceberg (vendor-neutral, broadest support)
• If Databricks-committed: Delta Lake
• If CDC-heavy: Apache Hudi

For Existing Systems:

• Hive migration: Apache Iceberg (in-place migration)
• Databricks platform: Delta Lake
• Streaming-first: Apache Hudi or Delta Lake

Red Flags for Each Format:

• Iceberg: Avoid if only using Databricks (Delta Lake is better integrated)
• Delta Lake: Avoid if need multi-cloud, multi-engine flexibility
• Hudi: Avoid if batch analytics is primary workload (Iceberg/Delta better)

Data Governance Patterns

Table of Contents

• Data Catalog
• Data Lineage
• Data Quality
• Access Control
• Data Quality in Medallion
• Best Practices

Data Catalog

Purpose: Searchable inventory of all data assets

Core Features:

• Metadata management (schema, owner, lineage)
• Search and discovery
• Tagging (PII, confidential, deprecated)
• Business glossary
• Lineage visualization

Tools:

• DataHub (LinkedIn): Open-source, REST API
• Alation: Enterprise, AI-powered search
• Collibra: Governance-focused, compliance
• AWS Glue Data Catalog: Native AWS
• Azure Purview: Native Azure

Data Lineage

Purpose: Track data flow from source to consumption

Benefits:

• Impact analysis (what breaks if I change this?)
• Root cause analysis (where did bad data come from?)
• Compliance (where is PII used?)

Column-Level Lineage Example:

customers.email
  → bronze.raw_customers._raw_data['email']
  → silver.customers.email
  → gold.customer_360.email_address
  → Marketing Tool (GDPR: track PII usage)

Tools:

• OpenLineage: Open standard
• Marquez: Reference implementation
• DataHub: Visualization

Data Quality

Six Dimensions: 1. Accuracy: Does data reflect reality? 2. Completeness: Are required fields populated? 3. Consistency: Do related data agree? 4. Timeliness: Is data fresh enough? 5. Validity: Does data conform to schema? 6. Uniqueness: Are there unwanted duplicates?

Tools:

Great Expectations:

validator.expect_column_values_to_not_be_null("customer_id")
validator.expect_column_values_to_be_unique("customer_id")
validator.expect_column_values_to_match_regex("email", r'^[\w\.-]+@[\w\.-]+\.\w+$')

Soda Core:

checks for silver.customers:
  - row_count > 1000
  - missing_count(customer_id) = 0
  - duplicate_count(customer_id) = 0
  - invalid_percent(email) < 1%

dbt Tests:

models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

Access Control

1. Role-Based Access Control (RBAC):

CREATE ROLE sales_analyst;
GRANT SELECT ON gold.fact_sales TO ROLE sales_analyst;
GRANT ROLE sales_analyst TO USER alice@company.com;

2. Attribute-Based (Row-Level Security):

CREATE ROW ACCESS POLICY customers_region_policy
AS (region string) RETURNS BOOLEAN ->
  CASE
    WHEN IS_ROLE_IN_SESSION('US_ANALYST') AND region = 'US' THEN TRUE
    WHEN IS_ROLE_IN_SESSION('EU_ANALYST') AND region = 'EU' THEN TRUE
    ELSE FALSE
  END;

3. Column-Level Security (Dynamic Data Masking):

CREATE MASKING POLICY mask_email AS (val string) RETURNS string ->
  CASE
    WHEN IS_ROLE_IN_SESSION('PII_VIEWER') THEN val
    ELSE '***MASKED***'
  END;

ALTER TABLE gold.customers
  MODIFY COLUMN email SET MASKING POLICY mask_email;

Data Quality in Medallion

Best Practices

1. Automate quality checks: Integrate into CI/CD 2. Monitor lineage: Track data flow end-to-end 3. Catalog everything: Make data discoverable 4. Enforce access control: Least privilege principle 5. Track data quality: Metrics and dashboards 6. Document data products: Schema, SLAs, examples

Medallion Architecture Pattern

Standard pattern for organizing data in lakehouses. Three layers of increasing data quality.

Table of Contents

• Three Layers
• Bronze Layer (Raw)
• Silver Layer (Cleaned & Conformed)
• Gold Layer (Business-Level)
• Data Quality by Layer
• Medallion + Data Mesh
• Benefits

Three Layers

Bronze Layer (Raw)

Purpose: Exact copy of source systems; immutable historical archive

Characteristics:

• Append-only (never modify or delete)
• Full fidelity (all columns, all rows)
• Format: Source format or Parquet
• Retention: Forever (or years)

Example:

CREATE TABLE bronze.raw_customers (
  _ingested_at TIMESTAMP,
  _source_file STRING,
  _raw_data STRING  -- entire JSON/CSV blob
);

Silver Layer (Cleaned & Conformed)

Purpose: Validated, deduplicated, typed data

Transformations:

• Parse JSON/XML
• Type conversions (string → int, date)
• Deduplication
• Normalized naming

Example:

CREATE TABLE silver.customers AS
SELECT
  json_extract_scalar(_raw_data, '$.id') AS customer_id,
  json_extract_scalar(_raw_data, '$.name') AS customer_name,
  CAST(json_extract_scalar(_raw_data, '$.revenue') AS BIGINT) AS annual_revenue,
  DATE(json_extract_scalar(_raw_data, '$.created')) AS created_date
FROM bronze.raw_customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY _ingested_at DESC) = 1;

Data Quality Checks:

• Primary key uniqueness
• Null checks on required fields
• Referential integrity
• Value range validation

Gold Layer (Business-Level)

Purpose: Optimized for consumption (BI, ML, APIs)

Patterns:

• Star schemas (dimensional models)
• Wide tables (ML features)
• Aggregates (daily_sales_by_region)

Example:

CREATE TABLE gold.fact_sales AS
SELECT
  s.order_id,
  d.date_key,
  c.customer_key,
  p.product_key,
  s.quantity * s.unit_price AS gross_revenue,
  s.quantity * s.unit_price - s.discount_amount AS net_revenue,
  (s.quantity * s.unit_price - s.discount_amount) - (s.quantity * p.unit_cost) AS profit
FROM silver.sales s
JOIN gold.dim_date d ON s.order_date = d.date
JOIN gold.dim_customer c ON s.customer_id = c.customer_id
JOIN gold.dim_product p ON s.product_id = p.product_id;

Data Quality by Layer

Medallion + Data Mesh

Each domain owns bronze-silver-gold for their data products:

Sales Domain:
  bronze.sales_raw
  silver.sales_cleaned
  gold.sales_analytics (data product)

Marketing Domain:
  bronze.marketing_raw
  silver.marketing_cleaned
  gold.marketing_analytics (data product)

Cross-Domain:
  gold.customer_360 (combines sales + marketing)

Benefits

• Clear separation of concerns
• Reprocessing at any layer
• Incremental quality improvement
• Standard pattern across organization

Data Modeling Approaches

Table of Contents

1. Dimensional Modeling (Kimball) 2. Normalized Modeling (Inmon) 3. Data Vault 2.0 4. Wide Tables (Denormalized)

---

Dimensional Modeling (Kimball)

Overview

Optimized for analytical queries and business intelligence. Organizes data into fact tables (metrics) and dimension tables (context).

Star Schema Design Process

Step 1: Identify Business Process

• Example: "Analyze product sales performance"
• Focus on measurable business events

Step 2: Declare Grain

• Grain = level of detail in fact table
• Examples: "one row per order line item" or "one row per daily sales summary"
• Critical: Grain must be specific and consistent

Step 3: Identify Dimensions (Context)

• Who: Customer, Salesperson
• What: Product, Promotion
• When: Date, Time
• Where: Store, Location
• How: Payment Method
• Why: Campaign, Channel

Step 4: Identify Facts (Measures)

• Quantity Sold
• Unit Price
• Discount Amount
• Net Revenue
• Cost
• Profit

Star Schema Example

DimDate                DimProduct              DimCustomer
- DateKey (PK)         - ProductKey (PK)       - CustomerKey (PK)
- Date                 - ProductID             - CustomerID
- DayOfWeek            - ProductName           - CustomerName
- Month                - Category              - Segment
- Quarter              - Brand                 - Country
- Year                 - UnitCost              - JoinDate
           \                 |                 /
            \                |                /
             \               |               /
              \              |              /
               \             |             /
                \            |            /
                 FactSales (Fact Table)
                 - SalesKey (PK, surrogate)
                 - DateKey (FK)
                 - ProductKey (FK)
                 - CustomerKey (FK)
                 - StoreKey (FK)
                 - Quantity
                 - UnitPrice
                 - DiscountAmount
                 - NetRevenue

Slowly Changing Dimensions (SCD)

Type 1: Overwrite (No History)

-- Update customer segment
UPDATE DimCustomer
SET Segment = 'Premium'
WHERE CustomerID = 12345;

-- Old value lost, no history

Use When: History doesn't matter (typo corrections, insignificant changes)

---

Type 2: Add Row (Full History)

-- Current record
INSERT INTO DimCustomer (CustomerKey, CustomerID, Name, Segment, EffectiveDate, EndDate, IsCurrent)
VALUES (98765, 12345, 'John Doe', 'Premium', '2025-01-01', '9999-12-31', TRUE);

-- When segment changes
UPDATE DimCustomer SET EndDate = '2025-06-30', IsCurrent = FALSE WHERE CustomerKey = 98765;
INSERT INTO DimCustomer (CustomerKey, CustomerID, Name, Segment, EffectiveDate, EndDate, IsCurrent)
VALUES (98766, 12345, 'John Doe', 'VIP', '2025-07-01', '9999-12-31', TRUE);

Query Pattern:

-- Current customers
SELECT * FROM DimCustomer WHERE IsCurrent = TRUE;

-- Historical customers at specific date
SELECT * FROM DimCustomer
WHERE '2025-03-15' BETWEEN EffectiveDate AND EndDate;

Use When: Full history is critical (customer address, product pricing, employee roles)

---

Type 3: Add Column (Limited History)

ALTER TABLE DimCustomer
ADD COLUMN PreviousSegment VARCHAR(50);

-- Track current and previous
UPDATE DimCustomer
SET PreviousSegment = Segment, Segment = 'VIP'
WHERE CustomerID = 12345;

Use When: Need to compare current vs previous (not full history)

---

Type 6: Hybrid (1+2+3) Combines Type 2 (full history) with Type 1 (current value in all rows).

CREATE TABLE DimCustomer (
  CustomerKey BIGINT PRIMARY KEY,
  CustomerID BIGINT,
  Name VARCHAR(100),
  CurrentSegment VARCHAR(50),      -- Type 1: Always current
  HistoricalSegment VARCHAR(50),   -- Type 2: Historical value
  EffectiveDate DATE,              -- Type 2
  EndDate DATE,                    -- Type 2
  IsCurrent BOOLEAN                -- Type 2
);

-- All rows show current segment, but history preserved

Use When: Need both point-in-time history and current value for all historical rows

When to Use Dimensional Modeling

Ideal Scenarios:

• Business intelligence and reporting
• Known query patterns (dashboards, KPIs)
• Historical trend analysis
• User-friendly for SQL analysts
• BI tools (Tableau, Power BI, Looker)

Trade-Offs:

• ✅ Fast analytical queries (denormalized, few joins)
• ✅ Intuitive for business users
• ✅ Optimized for BI tools
• ❌ Inflexible to schema changes
• ❌ Denormalization creates redundancy
• ❌ Requires upfront modeling effort

---

Normalized Modeling (Inmon)

Overview

Eliminate redundancy and ensure data integrity through normalization (3NF, BCNF). Designed for transactional systems (OLTP).

Normal Forms

1st Normal Form (1NF):

• Atomic values (no repeating groups)
• Each column contains single value

2nd Normal Form (2NF):

• 1NF + No partial dependencies
• Non-key attributes depend on entire primary key

3rd Normal Form (3NF):

• 2NF + No transitive dependencies
• Non-key attributes depend only on primary key (not other non-key attributes)

Boyce-Codd Normal Form (BCNF):

• Stricter version of 3NF
• Every determinant is a candidate key

Normalized Example (3NF)

Customers Table
- CustomerID (PK)
- CustomerName
- Email
- PhoneNumber

Orders Table
- OrderID (PK)
- CustomerID (FK)
- OrderDate
- TotalAmount

OrderLines Table
- OrderLineID (PK)
- OrderID (FK)
- ProductID (FK)
- Quantity
- UnitPrice

Products Table
- ProductID (PK)
- ProductName
- CategoryID (FK)
- UnitCost

Categories Table
- CategoryID (PK)
- CategoryName

When to Use Normalized Modeling

Ideal Scenarios:

• Transactional systems (OLTP)
• Data with frequent updates
• Strong consistency requirements
• Source systems feeding data warehouse

Trade-Offs:

• ✅ No redundancy (DRY principle)
• ✅ Data integrity enforced
• ✅ Flexible to changes
• ❌ Complex joins slow analytical queries
• ❌ Not intuitive for business users
• ❌ Poor performance for BI

---

Data Vault 2.0

Overview

Flexible, auditable, scalable model for enterprise data warehouses. Designed for compliance, multi-source integration, and agile requirements.

Core Structures

Hubs: Unique Business Keys

CREATE TABLE HubCustomer (
  CustomerHashKey BINARY(20) PRIMARY KEY,  -- Hash of CustomerID
  CustomerID VARCHAR(50),                  -- Business key
  LoadDate TIMESTAMP,
  RecordSource VARCHAR(50)
);

-- Immutable (never deleted)
-- No descriptive attributes

Links: Relationships Between Hubs

CREATE TABLE LinkOrder (
  OrderHashKey BINARY(20) PRIMARY KEY,    -- Hash of OrderID
  CustomerHashKey BINARY(20),             -- FK to HubCustomer
  ProductHashKey BINARY(20),              -- FK to HubProduct
  OrderID VARCHAR(50),                    -- Business key
  LoadDate TIMESTAMP,
  RecordSource VARCHAR(50),
  FOREIGN KEY (CustomerHashKey) REFERENCES HubCustomer(CustomerHashKey),
  FOREIGN KEY (ProductHashKey) REFERENCES HubProduct(ProductHashKey)
);

-- Represents transactions or associations

Satellites: Descriptive Attributes

CREATE TABLE SatCustomer (
  CustomerHashKey BINARY(20),             -- FK to HubCustomer
  LoadDate TIMESTAMP,                     -- Part of PK
  EndDate TIMESTAMP,
  CustomerName VARCHAR(100),
  Email VARCHAR(100),
  Phone VARCHAR(20),
  Address VARCHAR(200),
  RecordSource VARCHAR(50),
  PRIMARY KEY (CustomerHashKey, LoadDate),
  FOREIGN KEY (CustomerHashKey) REFERENCES HubCustomer(CustomerHashKey)
);

-- Temporal (tracks changes over time)
-- Multiple satellites per hub (source-specific)

Query Pattern (Join Hubs, Links, Satellites)

-- Reconstruct customer orders (current state)
SELECT
  hc.CustomerID,
  sc.CustomerName,
  sc.Email,
  lo.OrderID,
  hp.ProductID,
  sp.ProductName
FROM HubCustomer hc
JOIN SatCustomer sc ON hc.CustomerHashKey = sc.CustomerHashKey
  AND sc.EndDate = '9999-12-31'  -- Current record
JOIN LinkOrder lo ON hc.CustomerHashKey = lo.CustomerHashKey
JOIN HubProduct hp ON lo.ProductHashKey = hp.ProductHashKey
JOIN SatProduct sp ON hp.ProductHashKey = sp.ProductHashKey
  AND sp.EndDate = '9999-12-31';  -- Current record

When to Use Data Vault

Ideal Scenarios:

• Compliance requirements (full audit trail)
• Multiple source systems with overlapping data
• Agile warehousing (requirements change frequently)
• Long-term historical archive

Trade-Offs:

• ✅ Highly flexible (easy to add sources)
• ✅ Complete audit trail
• ✅ Parallel loading (hubs, links, satellites independent)
• ❌ Complex queries (many joins)
• ❌ Requires data mart layer for BI
• ❌ Storage overhead

---

Wide Tables (Denormalized)

Overview

Single table with hundreds of pre-joined columns. Optimized for columnar storage and query performance.

Example: Customer Feature Table (ML)

CREATE TABLE CustomerFeatures (
  -- Identity
  customer_id BIGINT PRIMARY KEY,
  created_at DATE,

  -- Demographics
  age INT,
  gender VARCHAR(10),
  country VARCHAR(50),
  segment VARCHAR(50),

  -- Behavioral (Last 30 Days)
  total_purchases_last_30d INT,
  total_revenue_last_30d DECIMAL(10,2),
  avg_order_value_last_30d DECIMAL(10,2),
  days_since_last_purchase INT,

  -- Behavioral (Last 90 Days)
  total_purchases_last_90d INT,
  total_revenue_last_90d DECIMAL(10,2),

  -- Lifetime
  total_purchases_lifetime INT,
  total_revenue_lifetime DECIMAL(10,2),
  tenure_days INT,
  customer_lifetime_value DECIMAL(10,2),

  -- Product Affinity (100+ category columns)
  purchases_category_electronics INT,
  purchases_category_clothing INT,
  -- ... 100+ more category columns

  -- Predictions
  churn_risk_score DECIMAL(3,2),
  next_purchase_days INT,

  -- Metadata
  last_updated TIMESTAMP
);

Optimization Techniques

1. Columnar Storage (Parquet, ORC):

# Write as Parquet (columnar format)
df.write.parquet("s3://bucket/customer_features.parquet")

# Only read needed columns
spark.read.parquet("s3://bucket/customer_features.parquet") \
  .select("customer_id", "churn_risk_score", "total_revenue_lifetime")

# Reads only 3 columns, not all 100+

2. Partitioning:

CREATE TABLE CustomerFeatures (
  customer_id BIGINT,
  segment VARCHAR(50),
  -- ... 100+ columns
  last_updated DATE
)
PARTITIONED BY (segment, last_updated);

-- Prune partitions
SELECT * FROM CustomerFeatures
WHERE segment = 'VIP' AND last_updated = '2025-01-01';

3. Clustering:

-- Snowflake clustering
ALTER TABLE CustomerFeatures
CLUSTER BY (customer_id, segment);

-- Co-locate related data for faster joins/filters

4. Materialized Views:

-- Pre-compute aggregates
CREATE MATERIALIZED VIEW CustomerSummary AS
SELECT
  segment,
  COUNT(*) AS customer_count,
  AVG(total_revenue_lifetime) AS avg_ltv,
  AVG(churn_risk_score) AS avg_churn_risk
FROM CustomerFeatures
GROUP BY segment;

-- Auto-refresh on base table changes

When to Use Wide Tables

Ideal Scenarios:

• ML feature stores
• Data science notebooks (exploratory analysis)
• High-performance dashboards
• Columnar databases (Snowflake, BigQuery, Redshift)

Trade-Offs:

• ✅ Fastest analytical queries (no joins)
• ✅ Simple for users (one table)
• ✅ Efficient in columnar storage (only read needed columns)
• ❌ Significant redundancy
• ❌ Large storage footprint
• ❌ Update complexity (many columns to maintain)

---

Modeling Approach Comparison

Hybrid Approaches

1. Data Vault + Dimensional (Common Pattern):

Source Systems
  → Data Vault (Integration Layer, full history, audit)
  → Dimensional (Presentation Layer, star schemas for BI)
  → BI Tools

Benefits: Flexibility of Data Vault + performance of dimensional models

---

2. Dimensional + Wide Tables:

Dimensional Model (Star Schema)
  → Wide Tables (ML Feature Tables)
  → ML Models

Benefits: BI on dimensional, ML on denormalized features

---

3. Normalized (OLTP) + Dimensional (OLAP):

Transactional Database (3NF)
  → ETL/ELT
  → Data Warehouse (Dimensional)
  → BI Tools

Benefits: Classic separation of transactional and analytical workloads

Modern Data Stack Architecture

Table of Contents

• Standard Architecture Layers (2025)
• Layer Selection Criteria
• Ingestion
• Storage
• Transformation
• Orchestration
• Typical Workflow
• Tool Stack Evolution

Standard Architecture Layers (2025)

┌─────────────────────────────────────────┐
│ Data Sources                            │
│ - Databases (PostgreSQL, MySQL, MongoDB)│
│ - SaaS (Salesforce, Stripe, HubSpot)   │
│ - Events (Segment, Rudderstack)         │
│ - Files (S3, GCS, SFTP)                 │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Ingestion Layer                         │
│ - Fivetran (ELT, pre-built connectors)  │
│ - Airbyte (open-source, custom)        │
│ - Kafka (streaming, event-driven)       │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Storage Layer                           │
│ - Snowflake (cloud data warehouse)      │
│ - Databricks (lakehouse, Spark)         │
│ - BigQuery (Google Cloud, serverless)   │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Transformation Layer                    │
│ - dbt (SQL-based, version controlled)   │
│ - Dataform (Google, SQL + Dataform)    │
│ - Spark (PySpark, large-scale)          │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Orchestration Layer                     │
│ - Airflow (Python DAGs, most popular)   │
│ - Dagster (asset-based, modern)         │
│ - Prefect (Python, dynamic workflows)   │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Visualization/BI Layer                  │
│ - Tableau (enterprise visualizations)   │
│ - Looker (Google, LookML modeling)      │
│ - Power BI (Microsoft ecosystem)        │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Governance Layer (Cross-Cutting)        │
│ - DataHub (catalog, open-source)        │
│ - Great Expectations (data quality)     │
│ - OpenLineage (lineage tracking)        │
└─────────────────────────────────────────┘

Layer Selection Criteria

Ingestion

Fivetran:

• ✅ Pre-built connectors (300+)
• ✅ SaaS, low maintenance
• ❌ Expensive ($1K-10K+/month)

Airbyte:

• ✅ Open-source, free
• ✅ Custom connectors
• ❌ More maintenance

Kafka:

• ✅ Real-time streaming
• ✅ High throughput
• ❌ Complex setup

Storage

Snowflake:

• Best for: BI/analytics
• Strength: Zero-maintenance, strong governance
• Cost: $$$

Databricks:

• Best for: ML/data science
• Strength: Unified analytics (BI + ML)
• Cost: $$

BigQuery:

• Best for: Google Cloud users
• Strength: Serverless, pay-per-query
• Cost: $$

Transformation

dbt:

• SQL-based, analysts can write
• Version control, testing
• Cost: Free (open-source)

Spark:

• PySpark/Scala, data engineers
• Large-scale processing
• Cost: Compute-based

Orchestration

Airflow:

• Most mature (2014)
• Large community
• Complexity: High

Dagster:

• Asset-based (modern)
• Strong testing
• Complexity: Medium

Prefect:

• Simpler than Airflow
• Dynamic workflows
• Complexity: Low-Medium

Typical Workflow

Batch ELT Pipeline:

PostgreSQL → Fivetran → Snowflake → dbt → Tableau
             (Extract/Load)  (Transform)  (Visualize)

Streaming Pipeline:

App Events → Kafka → Flink → Iceberg → Trino → Dashboard
            (Stream)  (Process) (Store)  (Query)

Hybrid Pipeline:

Batch Sources → Fivetran → Lakehouse
                             ↓
Streaming Sources → Kafka → Lakehouse
                             ↓
                           dbt (Transform)
                             ↓
                           BI Tools

Tool Stack Evolution

Phase 1 (Startup):

• BigQuery + Airbyte + dbt + Metabase
• Cost: <$1K/month
• Team: 1-2 analysts

Phase 2 (Growth):

• Snowflake + Fivetran + dbt + Tableau + DataHub
• Cost: $10K-50K/month
• Team: 2-5 data engineers, 5-10 analysts

Phase 3 (Enterprise):

• Snowflake + Databricks + Fivetran + Kafka + dbt + Airflow + Tableau + Alation
• Cost: $50K-500K/month
• Team: 10+ data engineers, 20+ analysts

See tool-recommendations.md for detailed comparisons.

Common Scenarios and Recommendations

Table of Contents

• Scenario 1: Startup Data Platform
• Scenario 2: Enterprise Modernization
• Scenario 3: Data Mesh Assessment
• Scenario 4: Lakehouse Table Format Selection
• Scenario 5: Streaming + Batch Architecture
• Scenario 6: Cost Optimization
• Decision Framework Summary

Scenario 1: Startup Data Platform

Context:

• 50-person startup
• Data sources: PostgreSQL, MongoDB, Stripe
• Need: Analytics for CEO dashboard + ML experiments
• Team: 1-2 analysts, no data engineers
• Budget: <$5K/month

Recommendation:

Storage: BigQuery or Snowflake (pay-per-use)

• BigQuery if GCP-committed
• Snowflake if multi-cloud flexibility desired

Ingestion: Airbyte (open-source) or Fivetran (if budget)

• Airbyte: Free, requires some ops
• Fivetran: $1K-2K/month, zero maintenance

Transformation: dbt Core (SQL-based)

• Free, open-source
• Version control with Git
• Testing framework included

Orchestration: dbt Cloud or Prefect Cloud

• dbt Cloud: Simple, dbt-specific
• Prefect Cloud: More flexible

Visualization: Metabase (open-source) or Looker Studio (free)

Architecture: Simple data warehouse

• Start with dimensional models (star schemas)
• Add lakehouse if ML becomes priority

Why: Minimize complexity, leverage managed services, focus on business value.

---

Scenario 2: Enterprise Modernization

Context:

• Legacy Oracle data warehouse
• Want cloud migration
• Support BI + ML workloads
• Reduce costs
• Team: 10+ data engineers

Recommendation:

Storage: Data Lakehouse (Databricks or Snowflake with Iceberg)

• Databricks if ML-heavy
• Snowflake if BI-primary

Migration Strategy: 1. Identify critical BI reports (keep on Oracle short-term) 2. Build lakehouse for new analytics and ML 3. Use CDC tools (Debezium, Fivetran) for continuous replication 4. Gradually migrate Oracle tables to lakehouse 5. Sunset Oracle after full migration

Architecture: Medallion (bronze, silver, gold)

• Bronze: Raw replication from Oracle
• Silver: Cleaned, typed data
• Gold: Star schemas + ML features

Cost Savings: 60-80% with lake storage vs Oracle licensing

Timeline: 12-18 months for full migration

---

Scenario 3: Data Mesh Assessment

Context:

• 200-person company
• Centralized data team: 5 people
• Domains: Sales, Marketing, Product
• Question: Should we adopt data mesh?

Assessment:

6-Factor Scores (1-5): 1. Domain clarity: 4 (clear domains) 2. Team maturity: 2 (analysts, but no data engineers in domains) 3. Platform capability: 2 (some automation, but not self-serve) 4. Governance maturity: 3 (centralized, but difficult to enforce) 5. Scale need: 2 (5-person team not yet bottleneck) 6. Organizational buy-in: 3 (interested, but skeptical)

Total Score: 16/30

Recommendation: NOT YET. Build foundation first.

Why:

• 200 people is too small for full data mesh (recommend >500)
• Central team of 5 is not yet a bottleneck
• Domain teams lack data engineering skills
• Self-serve platform doesn't exist yet

Alternative Approach: 1. Invest in self-serve platform (infrastructure as code, CI/CD) 2. Improve governance (catalog, lineage, quality) 3. Train domain teams on data engineering 4. Start with "data product thinking" (SLAs, documentation) without full decentralization 5. Reconsider data mesh at 500+ people or when central team is clear bottleneck

Hybrid Option:

• Sales domain (most mature) could start owning data products
• Marketing and Product remain centralized
• Platform team provides shared infrastructure

---

Scenario 4: Lakehouse Table Format Selection

Context:

• Building lakehouse on S3
• Need Spark for ML
• Need Trino for BI queries
• Want to avoid vendor lock-in

Recommendation: Apache Iceberg

Why:

• Multi-engine support: Both Spark and Trino have excellent Iceberg support
• Vendor-neutral: Apache Foundation governance (not Databricks-led)
• Hidden partitioning: Query without partition predicates
• Partition evolution: Change partitioning without rewriting data
• Mature ecosystem: Apache Polaris (catalog), Project Nessie (versioning)

Alternative: Delta Lake if committed to Databricks, but less optimal for Trino

Implementation:

CREATE TABLE catalog.db.sales (
  order_id BIGINT,
  customer_id BIGINT,
  order_date DATE,
  amount DECIMAL(10,2)
)
USING iceberg
PARTITIONED BY (days(order_date));

---

Scenario 5: Streaming + Batch Architecture

Context:

• Need both real-time dashboards and historical analytics
• Data sources: Kafka (streaming), PostgreSQL (batch)
• Team: Experienced data engineers

Recommendation: Kappa Architecture with Medallion

Architecture:

Kafka (Streaming)
  → Flink (Stream Processing)
  → Bronze Layer (Iceberg tables)
  → Silver Layer (cleaned, 5-minute windows)
  → Gold Layer (BI dashboards + ML features)

PostgreSQL (Batch)
  → Fivetran (CDC)
  → Bronze Layer
  → Silver Layer (daily batch)
  → Gold Layer

Unified Gold Layer: Merge streaming and batch data

Why:

• Single pipeline for both streaming and batch (Kappa)
• Medallion architecture for quality layers
• Iceberg supports both streaming writes (Flink) and batch reads (Trino)

Trade-Offs:

• Complexity: High (streaming infrastructure)
• Latency: Real-time (milliseconds for streaming path)
• Cost: Medium (compute for streaming)

---

Scenario 6: Cost Optimization

Context:

• Currently on Snowflake
• $50K/month bill
• Mostly BI workloads
• Want to reduce costs

Recommendation: Migrate to Lakehouse

Strategy: 1. Assess workloads:

• 80% of queries access last 30 days
• 20% access historical data (>1 year)

2. Hot-Cold Architecture:

• Hot data (last 30 days): Snowflake
• Cold data (>30 days): Iceberg on S3
• Unified view: Snowflake External Tables + Iceberg

3. Incremental Migration:

• Week 1-2: Export historical data to S3 as Iceberg tables
• Week 3-4: Update queries to use external tables for historical data
• Week 5+: Keep only hot data in Snowflake

Cost Savings:

• Snowflake: $50K/month → $15K/month (70% reduction)
• S3 + Trino: $5K/month
• Total: $20K/month (60% savings)

Trade-Offs:

• Query performance: Slightly slower for historical queries
• Complexity: Moderate (manage hybrid architecture)
• Maintenance: Some ops work for Iceberg tables

---

Decision Framework Summary

Storage Paradigms Deep Dive

Table of Contents

1. Data Lake 2. Data Warehouse 3. Data Lakehouse 4. Architectural Patterns

---

Data Lake

Definition

Centralized repository storing raw data in native format at massive scale. Schema-on-read approach allows flexibility but requires discipline to avoid "data swamp."

Core Characteristics

• Schema-on-read: Structure applied when querying, not when storing
• Format-agnostic: CSV, JSON, Parquet, Avro, images, videos, logs
• Cost-optimized: Object storage (AWS S3, Google GCS, Azure ADLS)
• Scalability: Petabyte+ capacity with linear scaling
• Immutability: Write-once, read-many pattern

Three-Zone Architecture

┌─────────────────────────────────────────┐
│ Raw Zone (Bronze)                       │
│ - Exact copy of source data             │
│ - No transformations                    │
│ - Immutable historical record           │
│ - Retention: Forever                    │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Cleaned Zone (Silver)                   │
│ - Validated, deduplicated               │
│ - Type conversions                      │
│ - Normalized formats                    │
│ - Retention: 1-2 years                  │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Curated Zone (Gold)                     │
│ - Business-level aggregates             │
│ - Ready for consumption                 │
│ - Optimized for queries                 │
│ - Retention: Business-defined           │
└─────────────────────────────────────────┘

When to Use Data Lake

Ideal Scenarios:

• Diverse data sources (structured + semi-structured + unstructured)
• Exploratory analytics with unknown use cases
• ML/AI training data requiring raw, full-history data
• Cost-sensitive workloads (storage is cheapest)
• Long-term archival (years to decades)
• Future-proof data retention

Anti-Patterns:

• Known BI/reporting requirements (warehouse is better)
• Need for strong data quality guarantees
• Users require SQL-first interface
• No data engineering team to manage complexity

Trade-Offs

Advantages:

• Lowest storage cost ($0.02-0.03/GB/month)
• Maximum schema flexibility
• Store all data types
• Future-proof (keep raw data for unforeseen use cases)
• No upfront schema design required

Disadvantages:

• No ACID guarantees (eventual consistency)
• Data quality issues common ("garbage in, garbage out")
• Governance challenges (risk of data swamp)
• Slower query performance vs warehouse
• Requires data engineering expertise

Technologies

Storage:

• AWS S3 (Standard, Infrequent Access, Glacier tiers)
• Google Cloud Storage (Standard, Nearline, Coldline)
• Azure Data Lake Storage Gen2

File Formats:

• Parquet (columnar, best for analytics)
• ORC (columnar, Hive-optimized)
• Avro (row-based, schema evolution)
• JSON, CSV (human-readable, not optimized)

Query Engines:

• Presto / Trino (distributed SQL)
• AWS Athena (serverless Presto)
• Apache Spark (batch and streaming)
• Google Cloud Dataproc (managed Spark)

---

Data Warehouse

Definition

Centralized, structured repository optimized for analytical queries. Schema-on-write enforces data quality but reduces flexibility.

Core Characteristics

• Schema-on-write: Structure enforced on ingestion
• Optimized for BI: Indexing, partitioning, materialized views
• ACID transactions: Consistency guaranteed
• Columnar storage: Only read needed columns
• Query optimization: Cost-based optimizer, statistics

Three-Layer Architecture

┌─────────────────────────────────────────┐
│ Staging Layer                           │
│ - Raw data ingestion                    │
│ - Temporary storage                     │
│ - Minimal transformations               │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Integration Layer (EDW)                 │
│ - Cleaned, conformed data               │
│ - Normalized or data vault              │
│ - Source of truth                       │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Presentation Layer (Data Marts)         │
│ - Star/snowflake schemas                │
│ - Department-specific views             │
│ - Optimized for reporting               │
└─────────────────────────────────────────┘

When to Use Data Warehouse

Ideal Scenarios:

• Structured, relational data primarily
• Known BI and reporting use cases
• Strong governance and compliance requirements
• Performance-critical dashboards
• Financial reporting, regulatory compliance
• Business users need SQL interface

Anti-Patterns:

• Highly unstructured data (images, videos, logs)
• Exploratory analytics with unknown requirements
• Rapid schema changes
• Tight budget constraints

Trade-Offs

Advantages:

• Best query performance (sub-second for most queries)
• Strong governance and data quality
• ACID transactions
• Mature tooling and ecosystem
• User-friendly for analysts (SQL interface)

Disadvantages:

• Highest storage cost ($20-40/TB/month)
• Schema inflexibility (migrations are painful)
• Not ideal for unstructured data
• Difficult to support ML workloads requiring raw data
• Compute tightly coupled to storage (scaling complexity)

Technologies

Cloud Data Warehouses:

• Snowflake (multi-cloud, zero-maintenance)
• Google BigQuery (serverless, pay-per-query)
• AWS Redshift (AWS-native, columnar)
• Azure Synapse Analytics (Azure-native, integrated)

On-Premises (Legacy):

• Teradata
• Oracle Exadata
• IBM Netezza
• Vertica

Optimization Techniques

1. Partitioning:

• Time-based (daily, monthly) - most common
• Geography (region, country)
• Hash (even distribution)

2. Clustering:

• Co-locate related data on disk
• Snowflake: Automatic micro-partitions + clustering keys
• BigQuery: Clustering columns (max 4)

3. Materialized Views:

• Pre-compute expensive aggregations
• Auto-refresh on base table changes
• Trade storage cost for query performance

4. Incremental Loading:

• Only load new/changed data
• Watermark column (e.g., last_updated_timestamp)
• Reduces load time by 90%+

---

Data Lakehouse

Definition

Hybrid architecture combining data lake cost efficiency and flexibility with data warehouse reliability and performance. Enabled by open table formats (Iceberg, Delta Lake, Hudi).

Core Characteristics

• Open table formats: ACID transactions on object storage
• Schema enforcement: Optional validation on write
• Time travel: Query historical versions
• Unified platform: BI and ML on same data
• Multi-engine support: Spark, Trino, Flink, Presto
• Cost-effective: Lake storage prices with warehouse features

Architecture

┌─────────────────────────────────────────┐
│ Cloud Object Storage (S3, GCS, ADLS)   │
│ - Low-cost, scalable                    │
│ - Parquet/ORC files                     │
│ - Open formats (no proprietary lock-in)│
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Metadata Layer (Iceberg/Delta)          │
│ - Table schema, statistics              │
│ - Partition information                 │
│ - Transaction log (ACID)                │
│ - Time travel snapshots                 │
└─────────────────────────────────────────┘
         ↓
┌─────────────────────────────────────────┐
│ Compute Engines (Multi-Engine)          │
│ - Spark (batch, ML)                     │
│ - Trino/Presto (SQL analytics)          │
│ - Flink (streaming)                     │
│ - Dremio (semantic layer)               │
└─────────────────────────────────────────┘

When to Use Data Lakehouse

Ideal Scenarios:

• Both BI and ML workloads (unified platform)
• Cost optimization (60-80% cheaper than warehouse)
• Multi-engine access (Spark, Trino, Flink)
• Schema evolution requirements (frequent changes)
• Streaming + batch processing
• Avoid vendor lock-in (open formats)

Anti-Patterns:

• Only BI workloads (warehouse is simpler)
• Very small scale (<100GB data)
• No data engineering expertise
• Legacy tools incompatible with lakehouse

Trade-Offs

Advantages:

• Cost-effective (lake pricing, warehouse features)
• Flexible (structured + semi-structured)
• Multi-engine support (avoid vendor lock-in)
• Open formats (Iceberg, Delta Lake)
• Time travel and schema evolution
• Unified BI + ML platform

Disadvantages:

• Newer technology (less mature than warehouse)
• Performance not quite warehouse-level (improving)
• Requires careful optimization (partitioning, clustering)
• Steeper learning curve
• Smaller ecosystem than traditional warehouses

Technologies

Table Formats:

• Apache Iceberg (vendor-neutral, multi-engine)
• Delta Lake (Databricks-led, Spark-optimized)
• Apache Hudi (upsert-optimized, streaming)

Lakehouse Platforms:

• Databricks (Delta Lake native)
• Snowflake (Iceberg support added)
• AWS Lake Formation (Iceberg + Hudi)
• Dremio (Iceberg semantic layer)

Compute Engines:

• Apache Spark
• Trino / Presto
• Apache Flink
• Dremio

---

Architectural Patterns

Lambda Architecture (Batch + Speed Layers)

Data Sources
     ↓
┌────────────────────┬────────────────────┐
│ Batch Layer        │ Speed Layer        │
│ (Hourly/Daily)     │ (Real-time)        │
│ - Historical data  │ - Recent data      │
│ - Reprocessable    │ - Low latency      │
│ - Spark batch      │ - Kafka + Flink    │
└──────────┬─────────┴──────────┬─────────┘
           │                     │
           ↓                     ↓
      Batch View            Real-time View
           │                     │
           └──────────┬──────────┘
                      ↓
                 Serving Layer
                  (Queries)

When to Use:

• Need both historical accuracy and real-time insights
• Batch reprocessing for data corrections
• Trade-off between latency and correctness

Challenges:

• Complexity (two parallel pipelines)
• Code duplication (batch and streaming logic)
• Consistency challenges between layers

Kappa Architecture (Streaming-Only)

Data Sources
     ↓
 Stream Processing
 (Kafka + Flink)
     ↓
┌────────────────────┬────────────────────┐
│ Serving Layer      │ Archive Layer      │
│ (Last 30 days)     │ (All history)      │
│ - Fast queries     │ - S3/GCS           │
│ - Hot data         │ - Reprocessable    │
└────────────────────┴────────────────────┘

When to Use:

• Streaming-first organization
• Simplified architecture (one pipeline)
• Reprocessing via stream replay

Challenges:

• Stream processing for everything (including batch)
• Requires mature streaming infrastructure
• Reprocessing can be slow

Medallion Architecture (Databricks/Lakehouse Standard)

Bronze Layer (Raw)
- Exact copy of source
- Append-only
- All history retained
     ↓
Silver Layer (Cleaned)
- Validated, deduplicated
- Type conversions
- Slowly Changing Dimensions applied
     ↓
Gold Layer (Business-level)
- Aggregates
- Star schemas
- Feature tables
- Ready for BI/ML

When to Use:

• Lakehouse platform (Databricks, Iceberg)
• Clear data quality layers
• Progressive data refinement

Benefits:

• Clear separation of concerns
• Reprocessing at any layer
• Incremental quality improvement

Comparison

Recommendation:

• Start with Medallion (simplest, most flexible)
• Add Kappa if streaming-first
• Avoid Lambda unless strong requirement for separate batch/speed layers

Open Table Formats Comparison

Quick comparison of Apache Iceberg, Delta Lake, and Apache Hudi for lakehouse architectures.

Feature Matrix

Decision Matrix

Use Apache Iceberg when:

• Need multi-engine flexibility (Spark, Trino, Flink)
• Want vendor neutrality (Apache Foundation)
• Partition evolution required
• Future-proof architecture (broadest adoption)

Use Delta Lake when:

• Committed to Databricks ecosystem
• Primarily Spark-based workflows
• Excellent streaming + CDC support needed
• Unity Catalog integration desired

Use Apache Hudi when:

• CDC and upserts are primary workload
• Streaming ingestion dominant
• Need record-level updates (Merge-on-Read)
• Cost-optimized incremental processing

Recommendation

Default: Apache Iceberg (vendor-neutral, multi-engine, best long-term)

Tool Recommendations

Table of Contents

• Research-Validated Tools (Context7, December 2025)
• dbt (Data Build Tool)
• Apache Iceberg
• Tool Comparison Matrix
• Tool Stack Recommendations by Use Case
• Startup (Cost-Optimized)
• Growth Company (Balanced)
• Enterprise (Full Stack)
• Selection Criteria
• Ingestion: Fivetran vs Airbyte vs Kafka
• Storage: Snowflake vs Databricks vs BigQuery
• Transformation: dbt vs Spark
• Orchestration: Airflow vs Dagster vs Prefect

Research-Validated Tools (Context7, December 2025)

dbt (Data Build Tool)

Context7 Score: 87.0 (Excellent) Code Snippets: 3,532+ Reputation: High Library ID: /websites/getdbt

Use For:

• SQL-based transformations in data warehouse/lakehouse
• Version control for data pipelines (Git integration)
• Testing and documentation (built-in framework)
• Lineage tracking (automatic DAG generation)

Key Features:

• Models as SQL SELECT statements
• Built-in testing (unique, not_null, relationships, custom tests)
• Incremental models (only process new data)
• Seeds (CSV files as reference data)
• Snapshots (track changes over time)
• Documentation generation (automatic)
• Multi-warehouse support (Snowflake, BigQuery, Redshift, Databricks)

Ecosystem Packages:

• dbt-utils: Macros for common patterns
• dbt-expectations: Great Expectations-style tests
• dbt-databricks: Databricks-specific optimizations
• dbt-project-evaluator: Project quality checks

Why Recommended:

• Industry standard (majority of data teams use dbt)
• Strong software engineering practices (Git, CI/CD, testing)
• Extensive documentation and community support
• Multi-warehouse support avoids lock-in

Getting Started:

pip install dbt-snowflake
dbt init my_project
cd my_project
dbt run
dbt test

---

Apache Iceberg

Context7 Score: 79.7 (Strong) Code Snippets: 832+ Reputation: High Library ID: /apache/iceberg

Use For:

• Open table format for data lakehouses
• Multi-engine analytics (Spark, Trino, Flink, Presto)
• ACID transactions on object storage
• Schema evolution without rewrites
• Time travel queries

Key Features:

• Hidden partitioning (no partition predicates in queries)
• Partition evolution (change partitioning without rewriting data)
• ACID transactions (serializable isolation)
• Time travel (query historical snapshots)
• Schema evolution (add/drop/rename columns safely)
• Metadata management (efficient manifest files)
• Multi-engine support (best-in-class)

Ecosystem:

• Apache Polaris: Open-source catalog (1,224 snippets, High reputation)
• Project Nessie: Git-like versioning for data lakes (356 snippets, High reputation)
• Lakekeeper: Rust-based Iceberg REST Catalog (301 snippets, Medium reputation)

Why Recommended:

• Vendor-neutral (Apache Foundation governance)
• Broadest multi-engine support (avoid vendor lock-in)
• Production-ready (Netflix, Apple, Adobe, Airbnb use in production)
• Best partition evolution support

Getting Started:

-- Create Iceberg table
CREATE TABLE catalog.db.sales (
  order_id BIGINT,
  customer_id BIGINT,
  order_date DATE,
  amount DECIMAL(10,2)
)
USING iceberg
PARTITIONED BY (days(order_date));

-- Time travel
SELECT * FROM catalog.db.sales
TIMESTAMP AS OF '2025-01-01 00:00:00';

---

Tool Comparison Matrix

---

Tool Stack Recommendations by Use Case

Startup (Cost-Optimized)

Budget: <$5K/month Team: 1-2 analysts, no data engineers Scale: <100GB data

Stack:

• Storage: BigQuery or Snowflake (pay-per-use)
• Ingestion: Airbyte (open-source) or Fivetran (if budget)
• Transformation: dbt Core (open-source)
• Orchestration: dbt Cloud (managed, simple) or Prefect Cloud
• Visualization: Metabase (open-source) or Looker Studio (free)
• Catalog: DataHub (if needed later)

Why: Minimize complexity, leverage managed services, focus on business value.

---

Growth Company (Balanced)

Budget: $10K-50K/month Team: 2-5 data engineers, 3-10 analysts Scale: 100GB-10TB data

Stack:

• Storage: Snowflake (BI-focused) OR Databricks (ML-focused)
• Ingestion: Fivetran (convenience) + Kafka (real-time)
• Transformation: dbt (primary) + Spark (heavy processing)
• Orchestration: Airflow (Astronomer or AWS MWAA managed)
• Visualization: Tableau or Looker
• Catalog: DataHub (open-source)
• Quality: Great Expectations + dbt tests
• Table Format: Apache Iceberg (if lakehouse)

Why: Balance convenience and cost; support BI and ML workloads.

---

Enterprise (Full Stack)

Budget: $50K-500K/month Team: 10+ data engineers, 20+ analysts Scale: >10TB data

Stack:

• Storage: Snowflake (BI) + Databricks (ML) hybrid
• Ingestion: Fivetran + Custom Airflow DAGs + Kafka
• Transformation: dbt + Spark (for heavy processing)
• Orchestration: Airflow (self-hosted or managed)
• Visualization: Tableau (enterprise license) + custom tools
• Catalog: Alation or Collibra (enterprise features)
• Observability: Monte Carlo (data observability) + Datadog
• Quality: Great Expectations + Soda + dbt tests
• Table Format: Apache Iceberg (vendor-neutral)
• Lineage: OpenLineage + Marquez

Why: Full-featured stack, mature governance, support for all workloads.

---

Selection Criteria

Ingestion: Fivetran vs Airbyte vs Kafka

Fivetran:

• ✅ 300+ pre-built connectors
• ✅ Lowest maintenance (SaaS, auto-schema change detection)
• ✅ Best for standard sources (Salesforce, Stripe, PostgreSQL)
• ❌ Expensive (starts $1K/month, scales with rows)
• ❌ Proprietary (vendor lock-in)

Airbyte:

• ✅ Open-source (free)
• ✅ 300+ connectors (community-maintained)
• ✅ Custom connector SDK (build your own)
• ✅ Self-hosted or cloud
• ❌ More maintenance (self-hosted requires ops)
• ❌ Less polished than Fivetran

Kafka:

• ✅ Real-time streaming (millisecond latency)
• ✅ Event-driven architecture
• ✅ Highest throughput (millions of events/sec)
• ❌ High complexity (requires Kafka expertise)
• ❌ Operational overhead (cluster management)
• ❌ Not ideal for batch CDC

Recommendation:

• Standard sources + budget → Fivetran
• Custom sources + cost-sensitive → Airbyte
• Real-time streaming → Kafka
• Hybrid: Fivetran (batch) + Kafka (real-time)

---

Storage: Snowflake vs Databricks vs BigQuery

Snowflake:

• ✅ Best for BI/analytics (fastest SQL queries)
• ✅ Strong governance (RBAC, masking, row-level security)
• ✅ Multi-cloud (AWS, GCP, Azure)
• ✅ Zero-maintenance (fully managed)
• ❌ Expensive for large data volumes
• ❌ Not ideal for ML workloads (limited Python support)

Databricks:

• ✅ Best for ML/data science (Spark-native, notebooks)
• ✅ Unified analytics (BI + ML)
• ✅ Lakehouse platform (Delta Lake)
• ✅ Excellent for Spark workloads
• ❌ Steeper learning curve
• ❌ Not as SQL-optimized as Snowflake

BigQuery:

• ✅ Serverless (no cluster management)
• ✅ Pay-per-query pricing (cost-effective for small workloads)
• ✅ Google Cloud integration (GCS, Dataflow, Vertex AI)
• ✅ Fast for large scans (petabyte-scale)
• ❌ GCP lock-in
• ❌ Limited multi-cloud support

Recommendation:

• BI-primary → Snowflake
• ML-primary → Databricks
• GCP-committed → BigQuery
• Hybrid: Snowflake (BI) + Databricks (ML)

---

Transformation: dbt vs Spark

dbt:

• ✅ SQL-based (analysts can write transformations)
• ✅ Version control (Git integration)
• ✅ Testing framework (built-in)
• ✅ Documentation generation (automatic)
• ✅ Incremental models (efficient)
• ❌ SQL-only (no Python UDFs)
• ❌ Not ideal for >100TB data

Spark:

• ✅ PySpark/Scala (complex logic, UDFs)
• ✅ Large-scale processing (petabyte-scale)
• ✅ ML integration (MLlib, feature engineering)
• ✅ Flexible (batch and streaming)
• ❌ Steep learning curve
• ❌ Requires data engineers

Recommendation:

• Analysts writing transformations → dbt
• Complex logic, large scale → Spark
• Hybrid: dbt (80% of transformations) + Spark (20% heavy processing)

---

Orchestration: Airflow vs Dagster vs Prefect

Airflow:

• ✅ Most mature (2014, battle-tested)
• ✅ Largest community (extensive integrations)
• ✅ Enterprise features (RBAC, audit logs)
• ❌ Steep learning curve (DAG complexity)
• ❌ Operational overhead (self-hosted)
• ❌ Task-based (not asset-based)

Dagster:

• ✅ Asset-based (data pipelines as assets)
• ✅ Strong testing (unit tests for pipelines)
• ✅ Modern design (Python-native)
• ✅ Better for data products
• ❌ Smaller community (2019)
• ❌ Less mature than Airflow

Prefect:

• ✅ Simpler than Airflow (dynamic workflows)
• ✅ Python-native (Pythonic API)
• ✅ Hybrid execution (local + cloud)
• ❌ Smaller community (2018)
• ❌ Less enterprise features

Recommendation:

• Mature, complex workflows → Airflow
• Asset-oriented thinking → Dagster
• Simplicity, dynamic workflows → Prefect
• dbt-only → dbt Cloud (no external orchestrator needed)