Current Progress

Completed

• Python API extraction

• Raw JSON ingestion

• Bronze transformation

• Bronze Parquet output

• Bronze data-quality validation

• Terraform AWS infrastructure

• AWS S3 bucket

• AWS IAM authentication - rail-data-platform IAM user → your AWS CLI / boto3 authentication

• AWS IAM role for Snowflake - rail-snowflake-role IAM role → Snowflake's authentication to S3

• Local Bronze → S3 upload

• boto3 S3 upload automation

• Snowflake database and RAW schema

• Snowflake RAW table - Snowflake appropriate data types for bronze parquet - light transformations

• Snowflake storage integration - The clean approach is to use a Snowflake storage integration + AWS IAM role, rather than putting AWS access keys inside Snowflake.

• Snowflake external stage

• S3 → Snowflake connectivity verified - Snowflake can see Parquet

• S3 → Snowflake RAW ingestion - using COPY INTO command to load the Parquet into RAW (Batch 1)

• Snowpipe - gets new data into Snowflake (automation) -> to detent new files and automatically loads them (Batch 2)

        Batch 1 → 5 rows
        Batch 2 → 5 rows
                ↓
        10 rows in RAW
  

• Snowflake Streams - identifies what changed -> track changes and only new records coming in from api when extracting:

    Batch 1 → RAW -> 5 rows 
    Batch 2 → RAW -> 10 rows 
  
    CREATE STREAM
  
    Batch 3 → RAW -> 15 rows
                ↓
            STREAM
                ↓
            detects Batch 3
  

• Snowpark transformations - Python transformations inside Snowflake

    RAW       = 15 rows
    STREAM    = 5 new rows
    SILVER    = 5 processed rows
  

The Python code executed inside Snowflake's environment:

    Local Machine
        │
        │ SQL
        ▼
    Snowflake
        │
        ├── Stream
        │
        └── Snowpark Python
                ↓
              Silver

• dbt-snowflake is the adapter in virtual environment allows dbt to communicate with Snowflake (dbt isn't replacing Snowflake. dbt is managing your SQL transformations inside Snowflake.)

• dbt Silver/Gold models - SQL-based data modelling (created dbt tests for daily_operator_performance)

In Progress

• Snowflake Tasks - automate the pipeline
• Power BI - connect to BI -> analysis for downstream consumption

Architecture

API → Python ETL → Bronze Parquet → S3 → Snowpipe → Snowflake RAW → Streams → Snowpark → dbt Silver/Gold → Tasks → Power BI