How rehaman24/retail-etl-pipeline-airflow-gcp works
Project Type & Purpose
• This is a cloud-native ETL pipeline (full-stack data engineering solution) that ingests merchant and sales JSON files from Google Cloud Storage (GCS), orchestrates multi-stage tasks with Apache Airflow, and loads, transforms, and merges data into a star-schema in BigQuery. Its goal is to deliver analytics-ready retail data for BI and reporting.
File Structure
• Top-level directories:
• data/merchants and data/sales contain sample JSON files (staging inputs).
• screenshots/ capture Airflow, BigQuery, and query results (documentation).
• Key external components (implied by README):
• dags/airflow_walmart_data_bigquery_dag.py (Airflow DAG definition).
• requirements.txt and connection/config files for GCP.
Extract Architecture Insights from README
• Core stages:
a. Dataset & Table creation via BigQuery operators
b. Parallel load tasks (GCSToBigQueryOperator)
c. Merge/UPSERT via BigQueryInsertJobOperator
• Dimensional model: star schema with a merchants dimension and a fact table (walmart_sales_tgt) that denormalizes merchant attributes.
• TaskGroup used for parallelism; idempotent design to allow safe re-runs.
a. Main Components:
• GCS Bucket (raw JSON storage)
• Airflow Orchestration (DAG hosted on Cloud Composer or self-managed Airflow)
• BigQuery Data Warehouse (walmart_dwh dataset with staging, dimension, fact tables)
• BI/Visualization Layer (Looker/Tableau/Power BI)—optional, for end-users
b. Relationships & Data Flow:
1. JSON files land in GCS under /merchants and /sales
2. Airflow DAG triggers at midnight UTC
3. Dataset creation → runtime table creation → parallel data load → MERGE task
4. Final tables serve analytics queries or feed into BI tools
c. Architectural Patterns & Principles:
• Star Schema (dimension + fact) for analytical performance
• Staging Layer to decouple ingestion from transformation
• MERGE-based UPSERT for idempotent, atomic updates
• TaskGroup for parallel execution and reduced DAG runtime
• Infrastructure as code: operators define dataset/table schema programmatically
d. Technologies & Frameworks:
• Orchestration: Apache Airflow 2.7+ (TaskGroup, BigQuery/GCS operators)
• Storage: Google Cloud Storage (NDJSON files)
• Warehouse: BigQuery (Standard SQL, MERGE)
• Language: Python 3.8+ for DAG, SQL for transformations
Detailed Elements to Capture
• Exact DAG schedule & DAG ID (walmart_sales_etl_gcs)
• Task dependencies graph: create_dataset → [create_tables] → load_data(TaskGroup) → merge_walmart_sales
• GCS paths: gs://…/merchants/*.json and gs://…/sales/*.json
• BigQuery dataset “Walmart_Dwh” with its three tables
• BI layer connection (optional): direct querying of walmart_sales_tgt