Data Lake Platform — Quick Reference

Build production data lakes and lakehouses: ingest from any source, transform with SQL, store in open formats, query at scale.

---

When to Use This Skill

• Design data lake/lakehouse architecture (medallion, data mesh, lambda/kappa)
• Set up data ingestion pipelines (dlt, Airbyte)
• Build SQL transformation layers (SQLMesh, dbt)
• Choose and configure table formats (Iceberg, Delta Lake, Hudi)
• Deploy analytical query engines (ClickHouse, DuckDB, Doris, StarRocks)
• Implement streaming pipelines (Kafka, Flink, Spark Streaming)
• Set up orchestration (Dagster, Airflow, Prefect)
• Implement data quality and governance (Great Expectations, DataHub)
• Optimize storage costs and query performance
• Migrate from legacy warehouse to lakehouse

---

Quick Reference

Layer	Tools	Templates	When to Use
Ingestion	dlt, Airbyte	templates/ingestion/	Extract from APIs, databases, files
Transformation	SQLMesh, dbt	templates/transformation/	SQL-based data modeling
Storage	Iceberg, Delta, Hudi	templates/storage/	Open table formats with ACID
Query Engines	ClickHouse, DuckDB, Doris	templates/query-engines/	Fast analytical queries
Streaming	Kafka, Flink, Spark	templates/streaming/	Real-time data pipelines
Orchestration	Dagster, Airflow, Prefect	templates/orchestration/	Pipeline scheduling
Cloud	Snowflake, BigQuery, Redshift	templates/cloud/	Managed warehouses
Visualization	Metabase, Superset, Grafana	templates/visualization/	Dashboards, BI, monitoring

---

Decision Tree: Choosing Your Stack

Data Lake Architecture?
    ├─ Self-hosted priority?
    │   ├─ OLAP queries? → ClickHouse (Yandex origin, Russian-friendly)
    │   ├─ Embedded analytics? → DuckDB (in-process, no server)
    │   ├─ Complex joins + updates? → Apache Doris or StarRocks
    │   └─ Data lakehouse? → Iceberg + Trino/Spark
    │
    ├─ Cloud-native?
    │   ├─ AWS? → Redshift, Athena + Iceberg
    │   ├─ GCP? → BigQuery
    │   └─ Multi-cloud? → Snowflake
    │
    ├─ Ingestion tool?
    │   ├─ Python-first, simple? → dlt (recommended)
    │   ├─ GUI, many connectors? → Airbyte
    │   └─ Enterprise, CDC? → Debezium, Fivetran
    │
    ├─ Transformation tool?
    │   ├─ SQL-first, CI/CD? → SQLMesh (recommended)
    │   ├─ Large community? → dbt
    │   └─ Python transformations? → Pandas + Great Expectations
    │
    ├─ Table format?
    │   ├─ Multi-engine reads? → Apache Iceberg (industry standard)
    │   ├─ Databricks ecosystem? → Delta Lake
    │   └─ CDC, real-time updates? → Apache Hudi
    │
    ├─ Streaming?
    │   ├─ Event streaming? → Apache Kafka
    │   ├─ Stream processing? → Apache Flink
    │   └─ Batch + streaming? → Spark Streaming
    │
    └─ Orchestration?
        ├─ Data-aware, modern? → Dagster (recommended)
        ├─ Mature, many integrations? → Airflow
        └─ Python-native, simple? → Prefect

---

Architecture Patterns

Pattern 1: Medallion Architecture (Bronze/Silver/Gold)

Use when: Building enterprise data lake with clear data quality tiers.

Sources → Bronze (raw) → Silver (cleaned) → Gold (business-ready)
            ↓              ↓                  ↓
         Iceberg        Iceberg            Iceberg
         append-only    deduped            aggregated

Tools: dlt → SQLMesh → ClickHouse/DuckDB → Metabase

See templates/cross-platform/template-medallion-architecture.md

Pattern 2: Data Mesh (Domain-Oriented)

Use when: Large organization, multiple domains, decentralized ownership.

Domain A ──→ Domain A Lake ──→ Data Products
Domain B ──→ Domain B Lake ──→ Data Products
Domain C ──→ Domain C Lake ──→ Data Products
                   ↓
            Federated Catalog (DataHub/OpenMetadata)

Tools: dlt (per domain) → SQLMesh → Iceberg → DataHub

See resources/architecture-patterns.md

Pattern 3: Lambda/Kappa (Streaming + Batch)

Use when: Real-time + historical analytics required.

Lambda: Kafka → Flink (speed layer) ───→ Serving
                   ↓                        ↑
        Batch (Spark) → Iceberg ───────────┘

Kappa:  Kafka → Flink → Iceberg → Serving (single path)

Tools: Kafka → Flink → Iceberg → ClickHouse

See resources/streaming-patterns.md

---

Core Capabilities

1. Data Ingestion (dlt, Airbyte)

Extract data from any source: REST APIs, databases, files, SaaS platforms.

dlt (Python-native, recommended):

• Simple pip install, Python scripts
• 100+ verified sources
• Incremental loading, schema evolution
• Destinations: ClickHouse, DuckDB, Snowflake, BigQuery, Postgres

Airbyte (GUI, connectors):

• 300+ pre-built connectors
• Self-hosted or cloud
• CDC replication
• Declarative YAML configuration

See templates/ingestion/dlt/ and templates/ingestion/airbyte/

2. SQL Transformation (SQLMesh, dbt)

Build data models with SQL, manage dependencies, test data quality.

SQLMesh (recommended):

• Virtual data environments
• Automatic change detection
• Plan/apply workflow (like Terraform)
• Built-in unit testing

dbt (popular alternative):

• Large community
• Many packages
• Cloud offering
• Jinja templating

See templates/transformation/sqlmesh/ and templates/transformation/dbt/

3. Open Table Formats (Iceberg, Delta, Hudi)

Store data in open formats with ACID transactions, time travel, schema evolution.

Apache Iceberg (recommended):

• Multi-engine support (Spark, Trino, Flink, ClickHouse)
• Hidden partitioning
• Snapshot isolation
• Industry momentum (Snowflake, Databricks, AWS)

Delta Lake:

• Databricks native
• Z-ordering optimization
• Change data feed

Apache Hudi:

• Optimized for CDC/updates
• Record-level indexing
• Near real-time ingestion

See templates/storage/

4. Query Engines (ClickHouse, DuckDB, Doris, StarRocks)

Fast analytical queries on large datasets.

ClickHouse (self-hosted priority):

• 100+ GB/s scan speed
• Columnar storage + compression
• Russian origin (Yandex), fully open source
• MergeTree engine family

DuckDB (embedded):

• In-process, no server
• Parquet/CSV native
• Python/R integration
• Laptop-scale analytics

Apache Doris / StarRocks:

• MPP architecture
• Complex joins
• Real-time updates
• MySQL protocol compatible

See templates/query-engines/

5. Streaming (Kafka, Flink, Spark)

Real-time data pipelines and stream processing.

Apache Kafka:

• Event streaming platform
• Durable log storage
• Connect ecosystem

Apache Flink:

• True stream processing
• Exactly-once semantics
• Stateful computations

Spark Streaming:

• Micro-batch processing
• Unified batch + stream API
• ML integration

See templates/streaming/

6. Orchestration (Dagster, Airflow, Prefect)

Schedule and monitor data pipelines.

Dagster (recommended):

• Software-defined assets
• Data-aware scheduling
• Built-in observability
• Type system

Airflow:

• Mature, battle-tested
• Many operators
• Large community

Prefect:

• Python-native
• Hybrid execution
• Simple deployment

See templates/orchestration/

7. Visualization (Metabase, Superset, Grafana)

Business intelligence dashboards and operational monitoring.

Metabase (business users):

• Intuitive question builder
• Self-serve analytics
• Signed embedding
• Caching and permissions

Apache Superset (analysts):

• SQL Lab for exploration
• Advanced visualizations
• Row-level security
• Custom chart plugins

Grafana (infrastructure):

• Time-series dashboards
• Real-time alerting
• Prometheus/InfluxDB native
• Data pipeline monitoring

See templates/visualization/ and resources/bi-visualization-patterns.md

---

Navigation

Resources (Deep Guides)

Resource	Description
architecture-patterns.md	Medallion, data mesh, lakehouse design
ingestion-patterns.md	dlt vs Airbyte, CDC, incremental loading
transformation-patterns.md	SQLMesh vs dbt, testing, CI/CD
storage-formats.md	Iceberg vs Delta vs Hudi comparison
query-engine-patterns.md	ClickHouse, DuckDB, Doris optimization
streaming-patterns.md	Kafka, Flink, Spark Streaming
orchestration-patterns.md	Dagster, Airflow, Prefect comparison
governance-catalog.md	DataHub, OpenMetadata, lineage
cost-optimization.md	Storage, compute, query optimization
operational-playbook.md	Monitoring, incidents, migrations
bi-visualization-patterns.md	Metabase, Superset, Grafana operations

Templates (Copy-Paste Ready)

Category	Templates
Cross-platform	medallion, pipeline, incremental, quality, schema, partitioning, cost, migration
Ingestion/dlt	pipeline, rest-api, database-source, incremental, warehouse-loading
Ingestion/Airbyte	connection, custom-connector
Transformation/SQLMesh	project, model, incremental, dag, testing, production, security
Transformation/dbt	project, incremental, testing
Storage	iceberg-table, iceberg-maintenance, delta-table, hudi-table, parquet-optimization
Query Engines	clickhouse (setup, ingestion, optimization, materialized-views, replication), duckdb, doris, starrocks
Streaming	kafka-ingestion, flink-processing, spark-streaming
Orchestration	dagster-pipeline, airflow-dag, prefect-flow
Cloud	snowflake-setup, bigquery-setup, redshift-setup
Visualization	metabase (connection-checklist, dashboard-request, incident-playbook)

---

Related Skills

• ai-mlops — ML deployment, drift detection, model registry
• ai-ml-data-science — Feature engineering, ML modeling, EDA
• data-sql-optimization — OLTP database optimization (PostgreSQL, MySQL, Oracle)
• ops-devops-platform — Infrastructure, CI/CD, Kubernetes

---

Quick Start Examples

Example 1: dlt + ClickHouse + Metabase

# Install dlt with ClickHouse
pip install "dlt[clickhouse]"

# Initialize pipeline
dlt init rest_api clickhouse

# Configure and run
python pipeline.py

Example 2: SQLMesh + DuckDB (Local Development)

# Install SQLMesh
pip install sqlmesh

# Initialize project
sqlmesh init duckdb

# Plan and apply
sqlmesh plan
sqlmesh run

Example 3: Dagster + dlt + SQLMesh

# dagster_pipeline.py
from dagster import asset, Definitions
import dlt
import sqlmesh

@asset
def raw_data():
    pipeline = dlt.pipeline("my_pipeline", destination="duckdb")
    return pipeline.run(source_data())

@asset(deps=[raw_data])
def transformed_data():
    ctx = sqlmesh.Context()
    ctx.run()

---

Sources

See data/sources.json for 100+ curated sources covering:

• Official documentation (dlt, SQLMesh, ClickHouse, Iceberg, etc.)
• Architecture guides (medallion, data mesh)
• Comparison articles (2024-2025 benchmarks)
• Community resources (Discord, Stack Overflow)
• Russian ecosystem (Yandex DataLens, Arenadata, Altinity)