Zomato AI Data Pipeline

What it is: A production-style data pipeline that ingests, transforms, and serves food-delivery data at scale — from raw CSVs to business-ready analytics marts to AI-powered conversational querying.

The problem it solves: Food delivery platforms generate massive, fragmented transactional and review data (orders, order items, free-text reviews) that needs to be reliably ingested, cleaned, modeled for analytics, and made queryable — including by non-technical users who can't write SQL.

Why it matters: Without a structured pipeline, this data stays siloed and hard to trust. Analysts can't get consistent revenue/SLA metrics, and unstructured review text (300K+ reviews) goes unused instead of surfacing customer sentiment and complaint patterns.

At a glance:

Orders processed 10M+

Order items processed ~23M

Customer reviews processed 300K

Source tables 7

Pipeline orchestration Single daily DAG, 4 tasks

Data layers Bronze → Silver → Gold → AI

Ingestion & Storage

• Amazon S3 (data lake)

• Snowflake (cloud data warehouse)

Transformation

• dbt (dbt-snowflake) — medallion architecture, incremental models, testing

Orchestration

• Apache Airflow 3, Dockerized

AI Layer

• OpenAI / Gemini (gemini-3.1-flash-lite, gemini-embedding-001)

• LLM enrichment, RAG, Text-to-SQL

Presentation

• Streamlit (dashboards + AI chat interfaces)

Language/Tooling

• Python, Pandas, SQL

Source → Lake (S3)

Seven CSVs — 4 real dimension tables (restaurants, users, food, menu) and 3 generated fact tables (orders, order items, reviews) — land in s3://<bucket>/raw/<table>/, one folder per table.

S3 → Snowflake: Keyless Ingestion

Rather than storing AWS access keys in Snowflake, the pipeline uses a storage integration paired with an IAM role — a keyless handshake. Snowflake reads directly from the bucket with no credentials to rotate or leak.

Bronze (Raw)

Data lands via COPY INTO, preserving original structure with minimal alteration — the source of truth before any transformation touches it.

Silver (Staging)

dbt staging views clean and standardize each source: parsing malformed fields (-- → null), stripping currency symbols (₹ 200 → 200), lowercasing emails, type-casting, and deriving fields like is_delivered.

Gold (Marts)

• Dimensions: dim_restaurants, dim_customer (with age segmentation), dim_food, a generated dim_date calendar

• Facts: fact_orders and fact_order_items, built as incremental models using MERGE — only new/changed rows are processed on each run, instead of rebuilding 10M+ records every time

• Business marts: daily city-level revenue (GMV, AOV, cancellation rate), restaurant performance, delivery SLA metrics (p50/p90 by city and hour), and review insights

Orchestration (Airflow)

A single daily DAG (zomato_batch) runs the full sequence — load → transform → enrich → AI mart — as one connected graph. Credentials are injected via environment variables and an Airflow connection, never hardcoded.

AI Layer

1. LLM Enrichment — reads free-text reviews, prompts an LLM for structured JSON output (sentiment, topic), writes results back into the warehouse where dbt models them like any other table. Capped by a sample size so the same review is never processed twice — a deliberate cost control.

2. RAG — reviews are embedded; a user's question retrieves the most relevant reviews and generates a grounded answer with cited sources — "chat with your reviews."

3. Text-to-SQL — the LLM is given the marts' schema and translates plain-English questions into Snowflake SQL, gated by a SELECT-only guard before execution — "chat with your warehouse."

Presentation

Streamlit hosts both the analytics dashboards and the two AI chat interfaces in one place.

• Keyless S3–Snowflake integration over static credentials — eliminates a class of credential-leak risk entirely, at the cost of a more involved one-time setup (see below).

• Incremental MERGE over full refresh — chosen specifically because rebuilding 10M+ orders and 23M+ order items on every run would make daily refreshes impractical; MERGE keeps runs fast and idempotent.

• Sampling for LLM enrichment (SAMPLE_N) — enriching all 300K reviews on every run would be both slow and costly against an LLM API; the pipeline is idempotent and caps spend by design rather than by accident.

• SELECT-only guard on Text-to-SQL — since the LLM generates arbitrary SQL from natural language, a validation layer restricts execution to read-only queries under a restricted role (DBT_ROLE) before anything runs against the warehouse.

• Snowflake and AWS Trail Version:  While building this project I've used Trial version ofr Snowflake which will last for upto 30 days and AWS trial version which will last for 6 months. So this ETL will not live after this time period. I've provide the github repository form wherre you can set up this in your local machine. Have fun 😀

Two things learned the hard way (worth including verbatim — this kind of detail is what makes a portfolio credible to a technical reader):

• The IAM trust policy's Principal must reference Snowflake's specific IAM user ARN, not :root — using :root silently under-scopes the trust relationship.

• Re-running CREATE OR REPLACE on the storage integration regenerates the external ID and breaks the existing trust relationship, requiring the AWS-side trust policy to be updated again.

• Use same cluster location for Snowflake and AWS

• dbt tests applied across the model DAG: unique, not_null, relationships, accepted_values

• A custom singular reconciliation test to catch logic errors that generic tests can't

• dbt build runs all models and tests in dependency order, so a failing test blocks downstream models from running on bad data

Ingestion: 10M+ orders, ~23M order items, 300K reviews loaded via keyless integration

Transformation: 7 staging models, incremental fact tables avoiding full 10M+ row rebuilds per run

AI enrichment: 300K unstructured reviews converted into structured, queryable sentiment/topic data

Orchestration: Single daily DAG automating the full load → transform → enrich → serve cycle