How hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql works
Project Type and Purpose
• This is a data-engineering and analytics pipeline built entirely on Snowflake, ingesting POS and customer data from AWS S3 and external weather data via Snowflake Marketplace, transforming and modeling the data through layered views and UDFs, then visualizing results in a Streamlit + Altair dashboard.
a. External Data Sources
• AWS S3 bucket (“s3://sfquickstarts/tasty-bytes-builder-education/”)
• Snowflake Marketplace (Weather Source LLC dataset)
b. Snowflake Data Warehouse
• PUBLIC layer (file format + S3 stage)
• RAW_POS schema (order_header, order_detail, truck, menu, location, franchise, country)
• RAW_CUSTOMER schema (customer_loyalty)
• HARMONIZED schema (orders_v, daily_weather_v, customer_loyalty_metrics_v, weather_hamburg, winds peed_hamburg)
• ANALYTICS schema (A_ORDERS_V, A_CUSTOMER_LOYALTY_V, UDFs: fahrenheit_to_celsius, inch_to_millimeter)
c. Transformation Scripts
• Ingestion SQL scripts (Ingestion/*.sql, warehouse schema.sql)
• Harmonization & analytics transformations (Transformation/transformations.sql)
• UDF creation scripts (Delivery and udfs/udf.sql, udfCreation.sql)
d. Visualization Layer
• Streamlit app (Streamlit dashboard/dashboard.py) using Python + Altair
• Static images/media (Media/)
1. External Sources ➔ PUBLIC Layer
• S3 bucket ➔ CSV_FORMAT & S3LOAD stage
• Marketplace tables ➔ joined in harmonized views
2. PUBLIC ➔ RAW Schemas
• COPY INTO RAW_POS.<tables>
• COPY INTO RAW_CUSTOMER.customer_loyalty
3. RAW ➔ HARMONIZED
• RAW_POS + RAW_CUSTOMER tables ➔ honey harmonized.orders_v
• Weather source tables ➔ harmonized.daily_weather_v
4. HARMONIZED ➔ ANALYTICS
• orders_v, customer_loyalty_metrics_v ➔ analytics views
• UDFs (fahrenheit_to_celsius, inch_to_millimeter) apply on weather data
5. ANALYTICS ➔ Streamlit Dashboard
• Python connector queries analytics views
• Altair plots daily sales vs. weather metrics
Architectural Patterns & Design Principles
• Layered Data Vault / ELT architecture (Public → Raw → Harmonized → Analytics)
• Modular SQL scripts organized by function: ingestion, transformation, UDF deployment
• Reusable UDFs for unit conversions, promoting DRY principles
• Separation of concerns: data modeling in SQL, visualization in Python