Skip to content

About

Streaming lakehouse ingesting live Wikipedia edits into Apache Iceberg via PySpark, transformed with dbt into a star schema, and queried by a manifest-grounded text-to-SQL agent measuring accuracy lift of pgvector retrieval over naive schema prompting.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

Β 

History

3 Commits

Folders and files

Repository files navigation

πŸ”οΈ LakehouseCopilot

Streaming Lakehouse + Text-to-SQL Agent

A production-grade data engineering platform that ingests live Wikipedia edits into Apache Iceberg, transforms them with dbt, and exposes the warehouse to an AI agent grounded in dbt manifest metadata

Measure the accuracy lift of manifest-grounded retrieval over naive schema prompting β€” with a full eval harness, golden dataset, and CI regression gate.


Python Apache Spark Apache Iceberg dbt Trino Kafka PostgreSQL Docker Terraform Airflow


Eval Questions Star Schema Guardrails CI


πŸ—οΈ Architecture

Wikipedia EventStreams (SSE)
        β”‚
        β–Ό
   Redpanda (Kafka)          ← partition key: wiki name
        β”‚
        β–Ό
PySpark Structured Streaming  ← at-least-once, 30s micro-batches
        β”‚
        β–Ό
Iceberg REST Catalog ──► MinIO   (raw.recentchange β€” partitioned by day)
        β”‚
        β–Ό
   dbt-trino (Trino)         ← deduplication on event_key via ROW_NUMBER()
        β”‚
        β–Ό
  Star Schema
  β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”   β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”   β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”   β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  β”‚  fct_edits  │──▢│ dim_wiki β”‚   β”‚ dim_user β”‚   β”‚ dim_date β”‚
  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜   β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜   β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜   β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
        β”‚                          β”‚
        β”‚                   manifest.json  ← dbt writes this
        β”‚                          β”‚
        β–Ό                          β–Ό
   Trino query ◄──── MCP Agent ◄── pgvector (per-model embeddings)
                         β”‚
                    text-to-SQL
                    + guardrails
                    + retry loop

The manifest.json is the bridge between the two halves. dbt writes it after every build. The agent embeds each model's description, columns, and lineage into pgvector and retrieves the top-k most relevant models as schema context at query time β€” instead of dumping the raw information_schema.


πŸ“Š Experiment Results

(Populated after running make eval β€” real measured numbers only)

Mode Execution Accuracy
Naive (information_schema dump) TBD
Grounded (manifest retrieval) TBD

Execution accuracy = result sets compared order-insensitively, not SQL string matching. Two queries that return the same rows both score as correct.


⚑ Key Capabilities

Capability Description
Live Streaming Ingestion Wikipedia SSE β†’ Redpanda β†’ Spark β†’ Iceberg in under 60 seconds end-to-end
At-Least-Once + Dedup Spark checkpoints offsets to MinIO; fct_edits deduplicates on event_key via ROW_NUMBER()
Star Schema fct_edits + dim_wiki + dim_user + dim_date β€” all built and tested by dbt
Manifest-Grounded Agent pgvector retrieves the top-k most relevant dbt models as schema context
SQL Guardrails SELECT-only, no multi-statement, no raw.* access, auto-LIMIT enforcement
Eval Harness 40+ golden questions across 5 categories; naive vs grounded accuracy comparison
Iceberg Maintenance Daily compaction, manifest rewrite, snapshot expiry, orphan removal via Airflow
AWS Deploy Path Terraform provisions S3, Glue, Athena, IAM; dbt-athena profile included

πŸ”§ MCP Tools

search_schema

Embeds the question and retrieves the top-k most relevant dbt models from pgvector. Returns model name, description, all column definitions, and upstream lineage β€” exactly the context the agent needs to write correct SQL.

run_query

Executes a read-only SELECT against Trino. Four guardrails applied before execution:

Guardrail Rule
SELECT-only Any non-SELECT statement is blocked
No multi-statement Semicolon-separated statements are blocked
No raw.* access Agent sees only governed marts, never raw tables
Auto-LIMIT A LIMIT is appended if absent (capped at QUERY_MAX_ROWS)

explain_lineage

Returns the upstream dbt model dependencies for any model name β€” useful for the agent to understand join paths before writing SQL.


πŸ—„οΈ Star Schema

fct_edits (grain: one row per Wikipedia edit)
β”œβ”€β”€ edit_sk          surrogate key
β”œβ”€β”€ wiki_sk    ──▢  dim_wiki.wiki_sk
β”œβ”€β”€ user_sk    ──▢  dim_user.user_sk
β”œβ”€β”€ date_sk    ──▢  dim_date.date_sk
β”œβ”€β”€ edit_type        edit | new | log | categorize
β”œβ”€β”€ namespace        0 = main article space
β”œβ”€β”€ title
β”œβ”€β”€ size_delta       bytes added/removed
β”œβ”€β”€ revision_old / revision_new
└── event_ts / ingest_ts

dim_wiki            dim_user            dim_date
β”œβ”€β”€ wiki_sk         β”œβ”€β”€ user_sk         β”œβ”€β”€ date_sk
β”œβ”€β”€ wiki            β”œβ”€β”€ username        β”œβ”€β”€ event_date
β”œβ”€β”€ wiki_name       β”œβ”€β”€ is_anonymous    β”œβ”€β”€ year / month / day
β”œβ”€β”€ project_type    └── is_bot          β”œβ”€β”€ day_name
└── language_code                       β”œβ”€β”€ iso_week
                                        β”œβ”€β”€ quarter
                                        └── is_weekend

πŸ“‹ Eval Golden Dataset

40+ questions across 5 categories, scored by execution accuracy (result set comparison):

Category Count Example Question
simple_filter 5 "Find all anonymous edits to English Wikipedia in the last hour"
aggregation 8 "Which users made the most edits to English Wikipedia last week?"
join 5 "Which editors contributed to the most different wikis last week?"
time_window 5 "What is the 7-day rolling average of daily edits on English Wikipedia?"
adversarial 15 "DROP TABLE iceberg.marts.fct_edits" β†’ must be refused
coverage 5 "Which language Wikipedias are most active by edit count this month?"

Adversarial cases test guardrail firing, unanswerable question handling, and SQL injection attempts.


πŸ—οΈ Repository Structure

LakehouseCopilot/
β”‚
β”œβ”€β”€ ingestion/                    # Wikipedia producer + Spark streaming
β”‚   β”œβ”€β”€ producer.py               # SSE β†’ Redpanda (partition key: wiki)
β”‚   β”œβ”€β”€ spark_stream.py           # Kafka β†’ Iceberg (at-least-once, DLQ)
β”‚   β”œβ”€β”€ models.py                 # Pydantic event schema
β”‚   β”œβ”€β”€ Dockerfile                # Producer container
β”‚   β”œβ”€β”€ Dockerfile.spark          # Spark submit container
β”‚   └── tests/
β”‚       └── test_producer.py      # Parse + partition key unit tests
β”‚
β”œβ”€β”€ warehouse/                    # dbt star schema
β”‚   β”œβ”€β”€ models/
β”‚   β”‚   β”œβ”€β”€ staging/
β”‚   β”‚   β”‚   └── stg_recentchange.sql   # View over raw Iceberg table
β”‚   β”‚   └── marts/
β”‚   β”‚       β”œβ”€β”€ fct_edits.sql          # Fact table with deduplication
β”‚   β”‚       β”œβ”€β”€ dim_wiki.sql           # Wiki dimension
β”‚   β”‚       β”œβ”€β”€ dim_user.sql           # Editor dimension (bot/anon flags)
β”‚   β”‚       └── dim_date.sql           # Date dimension
β”‚   β”œβ”€β”€ tests/
β”‚   β”‚   β”œβ”€β”€ assert_no_orphaned_wiki_fks.sql
β”‚   β”‚   └── assert_size_delta_consistent.sql
β”‚   β”œβ”€β”€ dbt_project.yml
β”‚   β”œβ”€β”€ profiles.yml               # Local Trino
β”‚   └── profiles_aws.yml           # Athena (AWS deploy)
β”‚
β”œβ”€β”€ agent/                        # MCP server + manifest indexer
β”‚   β”œβ”€β”€ mcp_server.py             # 3 tools: search_schema, run_query, explain_lineage
β”‚   β”œβ”€β”€ manifest_indexer.py       # Embeds dbt manifest into pgvector
β”‚   β”œβ”€β”€ agent.py                  # Text-to-SQL loop with retry
β”‚   β”œβ”€β”€ Dockerfile
β”‚   └── tests/
β”‚       └── test_guardrails.py    # 15 guardrail unit tests
β”‚
β”œβ”€β”€ evals/                        # Accuracy measurement
β”‚   β”œβ”€β”€ golden.yaml               # 40+ golden questions + reference SQL
β”‚   β”œβ”€β”€ runner.py                 # Naive vs grounded eval harness
β”‚   └── baseline.json             # Regression baseline (updated by make eval-baseline)
β”‚
β”œβ”€β”€ orchestration/                # Airflow DAGs
β”‚   β”œβ”€β”€ dags/
β”‚   β”‚   β”œβ”€β”€ transform_dag.py      # Hourly dbt build (per-model tasks)
β”‚   β”‚   └── maintenance_dag.py    # Daily Iceberg compaction + expiry
β”‚   └── maintenance/
β”‚       └── maintenance.py        # Spark maintenance operations + metrics
β”‚
β”œβ”€β”€ infra/                        # Infrastructure
β”‚   β”œβ”€β”€ docker-compose.yml        # Full local stack (10 services)
β”‚   β”œβ”€β”€ trino/
β”‚   β”‚   β”œβ”€β”€ catalog/iceberg.properties
β”‚   β”‚   β”œβ”€β”€ entrypoint.sh         # Injects env vars into Trino config
β”‚   β”‚   β”œβ”€β”€ config.properties
β”‚   β”‚   └── jvm.config
β”‚   β”œβ”€β”€ postgres/
β”‚   β”‚   └── init.sql              # Enables pgvector extension
β”‚   └── terraform/                # AWS deployment
β”‚       β”œβ”€β”€ main.tf               # S3, Glue, Athena, IAM
β”‚       β”œβ”€β”€ variables.tf
β”‚       β”œβ”€β”€ outputs.tf
β”‚       └── terraform.tfvars.example
β”‚
β”œβ”€β”€ .github/
β”‚   └── workflows/
β”‚       └── ci.yml                # Unit tests on PR; full eval on main push
β”‚
β”œβ”€β”€ .env.example                  # All config variables with defaults
β”œβ”€β”€ Makefile                      # up / down / dbt / index / eval / test
└── README.md

πŸš€ Quickstart

Prerequisites

  • Docker & Docker Compose v2.0+
  • Python 3.11+
  • An OpenAI API key (for make index and make eval)

Full Stack

git clone https://github.com/Adityaindurkhya/LakehouseCopilot.git
cd LakehouseCopilot

# 1. Start all 10 services (Redpanda, MinIO, Iceberg REST, Trino,
#    Spark streaming, Wikipedia producer, Airflow, Postgres, MCP agent)
make up

# 2. Wait ~2 minutes for events to land in Iceberg, then build the star schema
make dbt

# 3. Embed the dbt manifest into pgvector
#    Set OPENAI_API_KEY in .env first
make index

# 4. Run the eval harness β€” naive vs grounded accuracy comparison
make eval

# 5. Run unit tests (no Docker required)
make test

Ask a Question Directly

cd agent
pip install -r requirements.txt
python agent.py "Which users made the most edits to English Wikipedia last week?"

🐳 Services

Service URL Description
Trino UI http://localhost:8080 Query engine β€” run SQL directly
MinIO Console http://localhost:9001 Object store β€” browse Iceberg files
Redpanda Console http://localhost:8085 Kafka UI β€” inspect topic lag
Iceberg REST Catalog http://localhost:8181 Table catalog API
Airflow UI http://localhost:8088 DAG scheduler (admin / admin)
Postgres localhost:5432 pgvector store for manifest embeddings

🐳 Docker Commands

Action Command
Start stack make up
Stop stack make down
Tail logs make logs
Wipe all data make clean
Run dbt make dbt
Index manifest make index
Run evals make eval
Save baseline make eval-baseline
Unit tests make test

πŸ› οΈ Tech Stack

Layer Local Dev AWS Deploy
Event source Wikipedia EventStreams (SSE) same
Broker Redpanda (Kafka API) MSK Serverless
Stream processing PySpark Structured Streaming EMR Serverless
Table format Apache Iceberg same
Object store MinIO S3
Catalog Iceberg REST (tabular/iceberg-rest) AWS Glue Data Catalog
Query engine Trino 435 Athena
Transforms dbt-trino dbt-athena
Orchestration Airflow 2.8 (LocalExecutor) same
Vector store Postgres 16 + pgvector RDS + pgvector
Agent Python MCP server (stdio) same
CI GitHub Actions same
IaC Docker Compose Terraform

🧠 Design Decisions

Iceberg REST catalog over Hive Metastore β€” zero Hadoop dependencies, starts in seconds, speaks the Iceberg REST spec natively. No Thrift, no HMS, no extra JVM process.

Partition by day, sort by (wiki, event_ts) β€” day partitioning enables partition pruning for date-range queries. The secondary sort clusters same-wiki files together so Trino can use per-file min/max statistics for a second pruning level.

At-least-once + dedup in dbt β€” Spark checkpoints Kafka offsets to MinIO. Replayed micro-batches produce duplicate rows in the raw table. fct_edits deduplicates on event_key using ROW_NUMBER(), making the mart idempotent. Exactly-once would require a two-phase commit between Iceberg and Kafka β€” significant complexity for no analytical benefit.

Per-model embedding chunks β€” one pgvector document per dbt model. Per-column chunks are too small (a question needs description + column types + join path together). Whole-project is too large and dilutes the embedding signal. Per-model maps directly to one queryable SQL table.

Execution accuracy over SQL string matching β€” two different SQL queries can return the same correct answer. The eval harness compares result sets order-insensitively, not SQL strings. This avoids penalising the agent for stylistic differences.

Separate maintenance DAG β€” compaction and snapshot expiry are long-running Spark jobs (10–30 min). Running them in the transform DAG would delay the next hourly dbt run. The maintenance DAG runs daily at 02:00 UTC when query load is lowest.

7-day snapshot retention β€” comfortable time-travel window for debugging. Keeping snapshots longer on a high-frequency streaming table accumulates thousands of snapshots and gigabytes of metadata with diminishing value.


πŸ‘¨β€πŸ’» Author

Aditya Indurkhya

Master of Science in Data Analytics Engineering | B.Tech in Computer Science Engineering

GitHub LinkedIn

About

Streaming lakehouse ingesting live Wikipedia edits into Apache Iceberg via PySpark, transformed with dbt into a star schema, and queried by a manifest-grounded text-to-SQL agent measuring accuracy lift of pgvector retrieval over naive schema prompting.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages