An end-to-end data engineering and AI analytics platform that takes Zomato-style food delivery data from raw CSV files to a cloud data warehouse and finally exposes analytics and AI-powered applications.

Zomato Dataset → Amazon S3 → Snowflake → dbt → Airflow → AI → Streamlit

The platform follows a medallion-style data architecture:

                    ZOMATO DATA PLATFORM

Zomato Dataset
      │
      ▼
 Amazon S3
  Data Lake
      │
      ▼
Snowflake RAW
 Bronze Layer
      │
      ▼
 dbt STAGING
 Silver Layer
      │
      ▼
  dbt MARTS
  Gold Layer
      │
      ▼
Streamlit / SnowSight
 Analytics & AI Apps

Alongside the core data pipeline, an AI lane provides three capabilities:

                         AI LANE
                            │
            ┌───────────────┼───────────────┐
            │               │               │
            ▼               ▼               ▼
     LLM Enrichment        RAG        Text-to-SQL
            │               │               │
            ▼               ▼               ▼
    Review Insights    Review Chat    Warehouse Chat

This project demonstrates a complete batch data engineering pipeline for Zomato-style food delivery data.

The source data consists of seven datasets:

• Restaurants
• Users
• Food
• Menu
• Orders
• Order Items
• Reviews

The dataset contains approximately:

• 10 million orders
• 23 million order items
• 300,000 reviews

The data is stored in Amazon S3, loaded into Snowflake using a storage integration, transformed using dbt, orchestrated using Apache Airflow, and finally exposed through analytics and AI applications.

The source consists of seven CSV datasets:

restaurants
users
food
menu
orders
order_items
reviews

These files represent the raw Zomato-style food delivery data.

The raw CSV files are uploaded to Amazon S3 using a folder structure:

s3://<BUCKET>/raw/
│
├── restaurants/
├── users/
├── food/
├── menu/
├── orders/
├── order_items/
└── reviews/

Amazon S3 acts as the raw data lake before the data is loaded into Snowflake.

Snowflake loads the raw files from S3 using:

• Storage Integration
• External Stage
• CSV File Format
• COPY INTO

The raw data is stored inside:

ZOMATO.RAW

The Bronze layer preserves the source data before transformation.

Amazon S3
    │
    ▼
External Stage
    │
    ▼
COPY INTO
    │
    ▼
ZOMATO.RAW

dbt transforms the RAW data into clean staging models.

Schema:

ZOMATO.STAGING

Typical transformations include:

• Data type conversion
• Column renaming
• Null handling
• Data cleaning
• Standardization
• Derived columns
• Source joins

Example transformations:

-- → NULL
₹ 200 → 200

Business fields such as delivery status are also derived during staging.

ZOMATO.RAW
     │
     ▼
   dbt
     │
     ▼
ZOMATO.STAGING

The Gold layer contains analytics-ready models.

Schema:

ZOMATO.MARTS

dim_restaurants
dim_customer
dim_food
dim_date

fct_orders
fact_order_items

The large fact tables use incremental processing and MERGE strategies so that new data can be processed without rebuilding the entire dataset.

Examples include:

• Daily city revenue
• GMV
• Average Order Value
• Cancellation rate
• Restaurant performance
• Delivery SLA
• Review insights

The project contains three AI capabilities:

1. LLM Review Enrichment
2. RAG — Chat With Your Reviews
3. Text-to-SQL — Chat With Your Warehouse

The LLM is used as a transformation step.

stg_reviews
      │
      ▼
     LLM
      │
      ▼
Structured JSON
      │
      ▼
ZOMATO.AI.REVIEW_ENRICHED
      │
      ▼
mart_review_insights

Free-text reviews are converted into structured information such as:

• Sentiment
• Topic
• Review summary

This makes unstructured review data easier to query and analyze.

The RAG system allows users to ask questions about customer reviews.

Architecture:

Reviews
   │
   ▼
Embeddings
   │
   ▼
Vector Store
   │
   ▼
Similarity Retrieval
   │
   ▼
LLM
   │
   ▼
Grounded Answer + Sources

The system retrieves relevant reviews before generating an answer.

This allows the application to provide answers grounded in the actual review dataset.

What are customers complaining about most?

What do customers like about this restaurant?

What are the most common delivery-related complaints?

Users can ask questions about the warehouse using natural language.

Example:

Which restaurants have the highest number of positive reviews?

The pipeline becomes:

Natural Language
       │
       ▼
      LLM
       │
       ▼
Generated SQL
       │
       ▼
SELECT-only Guard
       │
       ▼
   Snowflake
       │
       ▼
     Result

The application validates generated SQL before execution.

The SQL execution uses a restricted warehouse role:

DBT_ROLE

The goal is to prevent destructive SQL operations from being executed through the AI interface.

Apache Airflow orchestrates the complete pipeline as a daily DAG.

The main workflow is:

reload_raw
     │
     ▼
dbt_build_core
     │
     ▼
enrich_reviews
     │
     ▼
dbt_build_ai

Airflow runs inside Docker.

                    SOURCE
                      │
                      ▼
              Zomato CSV Files
                      │
                      ▼
                 Amazon S3
                      │
                      ▼
              Snowflake RAW
                Bronze Layer
                      │
                      ▼
                dbt STAGING
                 Silver Layer
                      │
                      ▼
                 dbt MARTS
                  Gold Layer
                      │
              ┌───────┴────────┐
              │                │
              ▼                ▼
        Analytics          AI Layer
                              │
                 ┌────────────┼────────────┐
                 ▼            ▼            ▼
             Enrichment      RAG      Text-to-SQL
                 │            │            │
                 └────────────┼────────────┘
                              ▼
                     Streamlit Apps

The dbt project includes data quality tests such as:

• unique
• not_null
• relationships
• accepted_values
• Reconciliation tests

The main command is:

dbt build

This builds models and executes tests according to their dependency order.

Credentials are not stored directly in source code.

Environment variables are used for sensitive configuration such as:

SNOWFLAKE_ACCOUNT
SNOWFLAKE_USER
SNOWFLAKE_PASSWORD
SNOWFLAKE_WAREHOUSE
SNOWFLAKE_DATABASE
SNOWFLAKE_SCHEMA
OPENAI_API_KEY

The repository intentionally excludes:

• .env
• Large datasets
• Airflow logs
• Generated embedding files
• dbt build artifacts

The S3 → Snowflake connection uses a storage integration and IAM role rather than storing AWS access keys in the application.

Snowflake reads the S3 bucket using a storage integration and IAM role.

Amazon S3
    │
    │ IAM Role
    ▼
Snowflake Storage Integration
    │
    ▼
External Stage
    │
    ▼
Snowflake RAW

This avoids storing AWS access keys directly inside the application.

zomato-end-to-end-data-engineering/
│
├── airflow/
│   ├── Dockerfile
│   ├── docker-compose.yaml
│   ├── example.env
│   └── dags/
│       └── zomato_batch.py
│
├── ai/
│   ├── enrich_reviews.py
│   ├── rag_chat.py
│   ├── text_to_sql.py
│   └── example.env
│
├── zomato/
│   ├── models/
│   │   ├── staging/
│   │   ├── marts/
│   │   └── example/
│   ├── macros/
│   ├── dbt_project.yml
│   └── README.md
│
├── docs/
│   └── architecture.png
│
├── .gitignore
└── README.md

git clone https://github.com/Rishabh00b/zomato-end-to-end-data-engineering.git

cd zomato-end-to-end-data-engineering

python -m venv .venv
.venv\Scripts\Activate.ps1

python3 -m venv .venv
source .venv/bin/activate

pip install -r requirements.txt

The complete dataset is intentionally not included in this repository because of its size.

The project uses approximately 2.3 GB of CSV data.

The seven datasets are:

restaurants
users
food
menu
orders
order_items
reviews

Place the downloaded data in the local data directory before running the pipeline.

The Snowflake environment contains:

ZOMATO
│
├── RAW
├── STAGING
├── MARTS
├── SNAPSHOTS
└── AI

The setup process includes:

01_setup.sql
02_storage_integration.sql
03_stage_and_formats.sql
04_raw_tables.sql
05_copy_into.sql

Run these scripts in order when configuring the Snowflake environment.

Navigate to the dbt project:

cd zomato

Configure the Snowflake credentials through environment variables.

Run:

dbt debug

Build the core models:

dbt build --exclude tag:ai

Navigate to:

cd airflow

Create your environment file:

cp example.env .env

Configure the required Snowflake and AI credentials.

Build and start Airflow:

docker compose build
docker compose up -d

Open the Airflow UI locally.

Then enable and trigger:

zomato_batch

python ai/enrich_reviews.py

streamlit run ai/rag_chat.py

streamlit run ai/text_to_sql.py

The analytics layer can answer questions such as:

Which cities generate the highest revenue?

What is the average order value?

Which restaurants perform best?

What are the most common review topics?

What are customers saying about a restaurant?

Which restaurants receive the most positive reviews?

This project demonstrates practical experience with:

• Data Lake architecture
• Data Warehouse architecture
• Medallion architecture
• ETL / ELT
• Cloud storage
• Snowflake
• dbt
• Incremental models
• MERGE strategies
• Dimensional modeling
• Fact and dimension tables
• SCD Type 2
• Data quality testing
• Apache Airflow
• Docker
• LLM integration
• Embeddings
• RAG
• Vector search
• Natural-language-to-SQL
• SQL safety
• Streamlit
• Cloud IAM
• Git/GitHub

Large fact tables are processed incrementally rather than rebuilding the complete dataset.

New Data
   │
   ▼
Incremental dbt Model
   │
   ▼
MERGE
   │
   ▼
Gold Fact Table

The dbt project uses automated tests to validate transformed data.

Source
  ↓
Transformation
  ↓
Data Quality Tests
  ↓
Analytics Models

Airflow coordinates the complete pipeline:

S3
 ↓
Snowflake RAW
 ↓
dbt
 ↓
AI Enrichment
 ↓
AI Marts

Potential future improvements include:

• Automated data quality monitoring
• CI/CD for dbt and Airflow
• Better RAG retrieval
• Hybrid search
• AI evaluation framework
• Pipeline monitoring and alerting
• Streamlit cloud deployment
• Authentication
• Role-based access control
• Additional business dashboards

This project provides hands-on experience with a complete modern data platform.

The major areas covered are:

Cloud Storage
      ↓
Data Lake
      ↓
Cloud Data Warehouse
      ↓
Data Transformation
      ↓
Data Modeling
      ↓
Data Quality
      ↓
Pipeline Orchestration
      ↓
AI Integration
      ↓
Analytics Applications

B.Tech CSE-AI

Areas of interest:

• Data Engineering
• Cloud Computing
• AI/ML
• Backend Development
• Data Analytics

GitHub: https://github.com/Rishabh00b

If you find this project useful, consider giving the repository a star.

The large source dataset, environment files, credentials, generated logs, and generated embedding files are intentionally excluded from the Git repository.

The repository contains the code, transformation logic, orchestration configuration, AI applications, SQL setup, and project documentation required to understand and reproduce the pipeline.