# alanchn31/data-engineering-projects architecture

> Architecture diagram of the GitHub repository alanchn31/data-engineering-projects, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-28). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/alanchn31/data-engineering-projects
- Repository: https://github.com/alanchn31/data-engineering-projects

## Overview

##### Overall project type and purpose
- This repo is a collection of stand-alone data engineering projects demonstrating end-to-end ETL, data modelling, warehousing, lake formation, web scraping and orchestration patterns.
- Each numbered top-level folder (e.g., “1. Postgres ETL”, “2. Cassandra ETL”, “3. Web Scraping…”, etc.) is its own pipeline, ingesting data from a source, transforming it, and loading it into a target store.

##### File structure for architectural clues
- Top‐level folders (“1. Postgres ETL”, “2. Cassandra ETL”, etc.) map to discrete pipelines.
- raw data (JSON, CSV, website, S3)
- ingestion/transformation code (Python scripts, Jupyter notebooks, Spark jobs, Scrapy spiders)
- schema/configuration files (sql_queries.py, create_tables.py, dwh.cfg)
- The “0. Back to Basics” folder is documentation/exercises; “6. Data Pipelining with Airflow” is an orchestration layer that ties some pipelines together.

##### README for additional insights
- Describes six completed projects and a capstone.
- Technologies: Postgres, Cassandra, MongoDB, AWS Redshift, AWS S3, Spark, Scrapy, Airflow.
- Each project answers business questions about a music startup or a books website.

##### Main components
a. Data Sources
– JSON log_data and song_data files (local FS)
– CSV event_data files for Cassandra
– Books website (HTTP endpoint)
– AWS S3 buckets containing JSON for the data lake
b. ETL/Processing Modules
– Python scripts & Jupyter notebooks (Postgres ETL, Cassandra ETL, Redshift ETL, Data Lake)
– Scrapy spider + pipelines (Web Scraping → MongoDB)
– Spark application (Spark & S3 Data Lake)
– Airflow DAGs & custom operators/plugins (orchestrates S3→Redshift loads)
c. Storage Targets
– PostgreSQL OLAP DB (star schema)
– Cassandra NoSQL cluster (query-centric tables)
– MongoDB collection (books documents)
– AWS Redshift data warehouse
– AWS S3 data lake (Parquet files)
d. Orchestration Layer
– Apache Airflow (DAGs, custom operators for staging, fact/dimension loads, data quality checks)

##### Relationships and data flows
- For the Data Lake + Redshift projects, show the two-step pipeline: JSON in S3 → Spark job → Parquet in S3 → Airflow DAG → Redshift staging → Redshift fact/dimension tables.
- That Airflow DAGs import SQL scripts and call Python functions to create tables and run ETL.

##### Architectural patterns & design principles
- Batch ETL (daily JSON/CSV loads)
- Schema-on-write vs schema-on-read (Postgres star schema vs Spark/S3)
- Query-centric NoSQL modelling (Cassandra)
- Document store for semi-structured data (MongoDB)
- Data lake + data warehouse coexistence
- Workflow-as-code (Airflow DAGs & custom operators)

##### Technologies, frameworks & libraries
- Python (etl.py, create_tables.py)
- Jupyter Notebooks (exploratory ETL, tutorials)
- psycopg2 / sql_queries.py (Postgres)
- cassandra-driver (Cassandra notebooks)
- Scrapy (items, pipelines, spiders)
- PyMongo (MongoDB pipeline)
- boto3 & Spark (PySpark)
- airflow (DAGs, plugins/operators)

## Diagram (Mermaid)

```mermaid
graph TB
    subgraph "Data Sources"
        A1["JSON logs & song JSONs"]:::source
        A2["CSV event_data files"]:::source
        A3["Books HTTP Endpoint"]:::source
        A4["S3 JSON logs & song data"]:::source
    end

    subgraph "Processing Modules"
        B1["Postgres ETL Script"]:::compute
        B2["Cassandra ETL Notebook"]:::compute
        B3["Scrapy Spider & Pipelines"]:::compute
        B4["Spark ETL Job"]:::compute
        B5["Redshift ETL Script"]:::compute
        B6["Airflow DAG & Operators"]:::orchestration
    end

    subgraph "Storage Targets"
        C1["PostgreSQL Star Schema DB"]:::storage
        C2["Cassandra NoSQL Cluster"]:::storage
        C3["MongoDB Collection"]:::storage
        C4["S3 Parquet Data Lake"]:::storage
        C5["AWS Redshift Data Warehouse"]:::storage
    end

    A1 -->|"input"| B1
    B1 -->|"loads"| C1

    A2 -->|"input"| B2
    B2 -->|"loads"| C2

    A3 -->|"crawl"| B3
    B3 -->|"loads"| C3

    A4 -->|"input"| B4
    B4 -->|"writes"| C4

    C4 -->|"staged by"| B6
    B6 -->|"loads to"| C5

    A4 -->|"input"| B5
    B5 -->|"loads"| C5

    click B1 "https://github.com/alanchn31/data-engineering-projects/blob/master/1. Postgres ETL/etl.py"
    click B2 "https://github.com/alanchn31/data-engineering-projects/blob/master/2. Cassandra ETL/ETL using Cassandra.ipynb"
    click B3 "https://github.com/alanchn31/data-engineering-projects/blob/master/3. Web Scraping using Scrapy, Mongo ETL/books/books/spiders/books_spider.py"
    click B4 "https://github.com/alanchn31/data-engineering-projects/blob/master/5. Data Lake with Spark & AWS S3/etl.py"
    click B5 "https://github.com/alanchn31/data-engineering-projects/blob/master/4. Data Warehousing with AWS Redshift/etl.py"
    click B6 "https://github.com/alanchn31/data-engineering-projects/blob/master/6. Data Pipelining with Airflow/airflow/dags/sparkify_dwh_dag.py"

    classDef source fill:#ADD8E6,stroke:#000;
    classDef compute fill:#FFA500,stroke:#000;
    classDef storage fill:#90EE90,stroke:#000;
    classDef orchestration fill:#DDA0DD,stroke:#000;
```
