Snowflake - E2E Data Engineering Project (Food Delivery App)

Analyze the source system ER diagram for a food delivery app, identifying one-to-many relationships and mapping master and transactional tables for dimensional modeling in a snowflake data warehouse.

Explore an end-to-end data engineering workflow using CSV files from location, restaurant, customer, and delivery domains, loaded in VSCode and transformed to handle inserts, updates, and invalid records.

Map the end-to-end data architecture for a food delivery platform—from csv loading to stage and copy into tables, through clean and consumption layers to a star schema and streamlit UI.

Explore Snowflake native objects for end-to-end data pipelines: stage objects, file formats, copy commands, sequences for surrogate keys, streams for change data capture, and merge and tasks.

Learn about Snowflake stage objects, including external and internal stages, user and table stages, and unnamed stages; practice listing, describing, and loading data with SQL and SnowSQL CLI.

Explore Snowflake file formats, including csv, json, parquet, avro, rc, and xml, and learn to create, describe, and query formats stored in stages using dollar notation.

Learn how to load csv data into Snowflake tables using the copy command from internal stages, with file formats, stages, and options like force on error continue.

Learn how to create and manage Snowflake sequence objects, set start and increment values, retrieve next values, attach as surrogate keys, and inspect them in object explorer.

Explore how Snowflake stream objects track changes on base tables, capturing inserts, deletes, and updates with metadata and hash keys, including append only behavior.

Create and manage a Snowflake task and task tree to form a dag. Learn to configure compute warehouses, schedules, and single sql statements to run tasks with dependencies.

Explore common table expressions to break down complex queries, rank account balances by market segment with row_number, extract top two per category, compute averages and gaps for customers.

Explore window functions in Snowflake, focusing on row_number and rank using over with partition by and order by to generate row numbers and rankings on a sample customer table.

Explore an end-to-end data flow in Snowflake by creating a sandbox database, multiple schemas, a file format and stage, then load a CSV with copy and inspect tags and policies.

Create a sandbox database and schemas stage, consumption, and common, and configure a csv file format and a csv stage with directory service for loading data.

Learn to load csv files into a Snowflake stage, create initial and delta partitions, and use copy commands, file formats, and stage listing to validate data flow.

Load location data from stage to clean and consumption layers using Snowflake streams and copy commands; implement audit columns, hash key, effective dates, SCD two location dimension, and delta processing.

Handle bad records in location data by cleansing the stream, purging invalid delimited data with a temp table, and using on error continue to load only valid rows.

Load and transform the restaurant master data into a restaurant dimension in Snowflake, using stage tables, streams, and merges to handle inserts, updates, and deltas across daily data.

Create the customer table in stage, establish a clean layer and customer dim with surrogate key, and use stream objects and merge to insert or update records as data flows.

Load and validate customer address data from CSV files, create address table and stream, merge into clean and delta layers, and build the customer address dimension for the delivery app.

Load menu data from initial and delta CSVs into a stage, then build a menu dimension from a menu stream in the clean schema, yielding 26 records across restaurants.

Load delivery agent master data from CSV files, create the delivery agent and team tables with surrogate keys, and use streams and merge statements to populate and update dimension tables.

Create a delivery data warehouse by turning delivery, order, and order item transactions into a fact table, and add a date dimension, with slowly changing dimension type two.

Create and populate an order item fact table with a surrogate key, join to dimension tables, and build annual, monthly, daily revenue views and lineage in a Snowflake star schema.

Create an end-to-end revenue dashboard using Streamlit on a sandbox Snowflake database, linking fact and dimension tables and sharing the app with roles for public or system admin.

Learn how a data pipeline automates left-to-right data flow from stage to clean to consumption, using DDL and DML, enabling dashboards without manual intervention.

Design an end-to-end data pipeline by orchestrating sequential copy and merge steps from stage to clean to consumption using stored procedures and parent orchestration.

Create the ESP_pipeline_DB database and four schemas (stage, clean, consumption, common), then build tables, streams, and a date dimension and order fact, with views and lineage via stored procedures.

Create and orchestrate Snowflake stored procedures to copy data from internal stages to tables, merge into clean and consumption layers, and build the date dimension and fact table.

Load csv data into the Snowflake stage with SQL CLI and put commands, validate the internal stage, then run the stored procedure toward the Streamlit dashboard.

Execute the main pipeline stored procedure to load data into customer, date, and consumption tables, verify via the Streamlit dashboard, and schedule a five-minute task for automated runs.

Schedule a task to invoke the stored procedure every five minutes, loading data from stage into the pipeline and refreshing the dashboard with run history and task tree for traceability.

Explore a task and task tree approach to data orchestration, replacing sequential stored procedures with atomic tasks for better traceability. Build the task_pipeline_db, load data, and run the workflow.

Explore a Snowflake data pipeline built with task and task tree, orchestrating staging, cleaning, and consumption steps with copy and merge operations.

Process large volumes of data in Snowflake by creating bigdata_db, defining clean, consumption, and common schemas, loading 24 csv files via sql cli, and validating with Streamlit dashboards.

Process large volumes of delivery data in Snowflake, loading 6.2 million order item records and validating the pipeline with a Streamlit dashboard and run histories.

Implement continuous data ingestion by simulating hourly arrivals of orders, order items, and deliveries into a Snowflake stage, automated via a Snowpark program, a control table, and a GitHub action.

Set up a GitHub action workflow to ingest food delivery data with a Snowpark program, pushing order CSVs to a stage, updating a control table, and scheduling hourly processing.