Snowflake - Complete End To End Data Project Hands-On Guide

Define the problem and select data sources for an air quality index dashboard, planning a Snowflake end-to-end integration using data.gov.in via api and json.

Design end-to-end data flow across stage layer, clean layer, and consumption layer, turning raw json into structured snowflake tables and a curated dashboard layer.

Explore table design for stage and clean layers, load json data via the SnowSite web UI, use transformation queries to extract pollutant metrics, and prepare for copy into structured tables.

Design a dimensional model using a star schema with location and date dimensions and a pollutant fact table to enable hour-by-hour analytics across locations, while outlining the end-to-end data pipeline.

Create a dev_db with stage, clean, consumption, and publish_stage schemas, and set up load, transform, streamlit, and ad hoc warehouses in Snowflake, and define a json file format.

Create an internal stage and json file format, upload India air quality json files, copy into raw_aqi for querying, and automate loading with an hourly copy task.

Flatten and de-duplicate json air quality data in snowflake by parsing raw json into a clean table, deduplicating with row_number, and building a dynamic, flattened pollutant measurement table.

Transpose the data from hourly pollutant rows into a single columnar row per location, using group by and max on averages, and create a flattened dynamic table.

Design and implement a wide table approach to compute the air quality index from seven pollutants, extracting hour, day, month, and year from timestamp, and identifying the prominent pollutant.

Construct date and location dimension tables and a fact table using a hash-based key within the consumption layer, enabling dimensional modeling of AQI measurements.

Load 90 json files from the raw India air quality dataset, run copy commands, refresh stages, and move data through stage to clean and consumption layers for city-level aggregation.

Design and implement aggregated fact tables at city level to summarize station-level air quality index data, compute city-level aqi averages, and enable hourly trends via common table expressions.

Learn to create a day-level aggregated city fact table from hourly data by converting timestamps to dates, grouping by date, country, state, and city, and calculating averages.

Master end-to-end data pipelines in Snowflake, loading from stage to clean to consumption, deduplicating and modeling with fact and dimension tables; publish dashboards with Streamlit and live data integration.

Explore how to build interactive data visualizations in Snowflake by creating and running Streamlit apps within Snowflake, using Snowpark, and connecting to consumption data for dynamic dashboards.

Build a simple streamlit app inside snowflake to display a tabular city-level air quality dashboard (AQI, city, state, pollutants) using snowpark and a pandas dataframe.

Build an interactive Streamlit dashboard to visualize hourly AQI trends by state, city, and date using aggregated data, dynamic queries, and bar and line charts.

Build an interactive Streamlit map that visualizes air quality by joining latitude and longitude from the location dimension with date and air quality fact data in Snowflake.

Automate the ingestion of json data from an api source into snowflake using python and snowpark, with github actions enabling stage-to-dynamic table-to-consumption data flow from data.gov.in.

Learn to download json data from data.gov using python and Snowpark, store it locally, and load it into Snowflake via a stage with hourly copy to raw tables.

Automate hourly data ingestion with GitHub actions, using a Python Snowpark workflow to fetch JSON data and load it into Snowflake.

Monitor an end-to-end Snowflake data pipeline DAG from origin to consumption, using GitHub actions, Python Snowpark, stage to gold layers, with hourly checks, copy histories, and a Streamlit AQI dashboard.

Integrate third-party weather data from the Snowflake marketplace into your ETL workflow to enrich AQI analysis, using shared data and views for seamless integration.

Explore creating a dynamic weather data table and the restrictions of marketplace views, and learn to use a permanent or transient table with an aggregated fact table refreshed hourly.

Create a Snowflake standard table and a daily refresh task to ingest marketplace weather data, build an aggregated daily fact table, and enable hourly and daily data consumption via dashboards.

Create a streamlit app that uses a Snowflake marketplace aggregated fact table to draw a line chart of AQI and temperature, illustrating an end-to-end data pipeline and cost-saving practices.