Spotify & Grammys ETL Pipeline

Author: Juan Pablo Gómez Veira
Course: ETL | Data Engineering and Artificial Intelligence

---

Executive Summary

This project acts as a capstone demonstration of a real-world Data Engineering recruitment code challenge. The overall objective was to build an end-to-end Extract, Transform, and Load (ETL) pipeline using robust, production-grade tools.

We successfully orchestrated an automated pipeline that extracts raw audio features from a massive Spotify mapping (CSV) and historical nominations from a Grammy Awards (PostgreSQL) database. After conducting in-depth Exploratory Data Analysis (EDA), we designed advanced Python scripts to clean the datasets, employ fuzzy string matching (NLP), and perform a Multi-Level Semantic Merge on identical song and album titles.

Finally, the enriched pipeline gracefully routes the outputs to Google Drive (via API) and constructs a strictly constrained Star Schema Data Warehouse inside a local database engine, enabling Power BI to harvest deep analytical insights effortlessly.

---

Project Organization

├── .files/               # Images and workshop instructions
├── dags/                 # Airflow DAGs
│   ├── etl_main_dag.py   # The robust orchestrator DAG for the pipeline
├── data/                 # Directory used as Volume for Airflow & Docker
│   ├── raw/              # Raw CSV sources
│   ├── intermediate/     # XCom data passing files (solves memory/payload limitations)
│   └── processed/        # Final Multi-Level merge CSV output
├── notebooks/            # Jupyter Notebooks (EDA & Prototyping)
│   ├── eda_profiling.ipynb
│   ├── load_prototype.ipynb
│   └── transform_prototype.ipynb
├── src/                  # Extract, Transform, Load Core Modules
│   ├── database/
│   │   ├── connection.py # SQLAlchemy routing handling Docker vs Local localhost mapping
│   ├── etl/
│   │   ├── extract.py    # Ingestion operations (PostgreSQL / CSV)
│   │   ├── transform.py  # Cleaning, aggregation, and Multi-Level merges
│   │   └── load.py       # DDM Star Schema creation and Google Drive API hooks
├── .env                  # Environment Variables
├── docker-compose.yaml   # Docker Airflow + Postgres cluster setup
├── requirements.txt      # Dependency limits ensuring fast Airflow bootups
└── README.md             # You are here

---

Setup Instructions & Prerequisites

To execute this project seamlessly on your local machine, follow these instructions to orchestrate the environment correctly:

1. Prerequisites (OS & Tools)

• Ensure you have Docker Desktop installed.
• If using Windows, update and configure your WSL2 (Windows Subsystem for Linux), as it's required for Docker bindings. Verify with docker --version and docker compose version in your terminal.
• Install uv, an incredibly fast Python package installer and resolver. (When running the prototypes in Jupyter, make sure to select the kernel synced by uv).

2. Configure Environment Variables

Clone the repository, then map out your environment parameters. Create a .env file at the root of the project mirroring this exact configuration:

AIRFLOW_UID=50000
FERNET_KEY=YOUR_UNIQUE_FERNET_KEY # See instructions below
POSTGRES_USER=airflow
POSTGRES_PASSWORD=airflow
POSTGRES_HOST=localhost
POSTGRES_PORT=5433
POSTGRES_DB=airflow
GOOGLE_DRIVE_FILE_ID=YOUR_FILE_ID_HERE

To generate a valid FERNET_KEY, you can run this command in your terminal: python -c "from cryptography.fernet import Fernet; print(Fernet.generate_key().decode())"

Note: Due to Docker clustering, POSTGRES_PORT locally is 5433 (to avoid conflicting with default port 5432 on host machines). However, Airflow containers speak to PostgreSQL natively at 5432 inside the network. Our connection.py dynamically accounts for this!

3. Start Apache Airflow

Run the following commands in your terminal to initialize the backend PostgreSQL database, generate credentials, and spin up the Airflow UI:

# 1. Initialize the Airflow Database
docker-compose up airflow-init

# 2. Boot the cluster in detached mode
docker-compose up -d

Access the user interface at:

• URL: http://localhost:8080
• User: airflow
• Pass: airflow

Search for the DAG titled spotify_grammys_etl and trigger it using the "Play" button!

4. Enable Google Drive Uploads (Optional)

This project securely updates files over the cloud. To do this yourself:

1. Create a Google Cloud Platform project and enable the Google Drive API.
2. Create a Service Account and download the keys as credentials.json (Save it in the root folder. .gitignore will hide it).
3. Look at the instructions in notebooks/load_prototype.py. We use a specific File ID Override strategy (sharing an existing file with the service bot).
4. Extract your generated Drive link ID and paste it into .env under GOOGLE_DRIVE_FILE_ID.

---

ETL Pipeline Details & Architectural Decisions

Building this pipeline involved solving numerous chaotic data modeling and unstructured textual challenges. Here is exactly what we discovered, assumed, and decided during development.

Phase 1: Data Profiling & EDA (Extracted Insights)

Before automating the scripts, we ran pandas-profiling logic in Jupyter.

• Spotify Duplicates: We detected identical track_id rows! The reason? A single song (like "Better") appeared identically across different genres (e.g., Chill vs Soul). We strictly deduplicated by removing conceptual duplicates (track_name + artists) while preserving the most "popular" variation, but we had to group genres into an array first to avoid losing relational data.
• Grammys Category Chaos: The dataset offered 638 unique Grammy Categories (e.g., "Best New Artist" vs "Best New Artist Of 1964"). Using NLP Fuzzy Matching (difflib), we collapsed the historical variations into a strict 80-category modern mapping.
• Grammys Disparaged Artists: The workers column was heavily unstructured. If the Grammy award was for a song, tracking down the artist required implementing Regex \(([^)]+)\) logic to target text in parentheses, while purposefully filtering out mixing engineers and producers using role-based rejections.

Phase 2: Data Transformation

The heart of the challenge was mapping music together. We needed to join the data without primary ID keys!

• We designed a Multi-Level Semantic Merge. Since the Grammys dataset records both "Album of the Year" (Album Level) and "Song of the Year" (Track Level), we implemented dynamic routing logic. We cleaned string punctuations forcefully, exploded array lists, and executed separate inner joins matching either artist + track_name or artist + album_name.

Phase 3: Data Warehouse (The Star Schema)

We created a fully normalized Star Schema Data Warehouse model (data_warehouse schema in Postgres) to serve the visualization interface with extreme speed.

1. Grain: Formulated to One row per Track × Artist × Grammy combination.
2. Dimension Tables:
   • dim_artist: Unique Artist IDs
   • dim_track: Primary Spotify identification fields
   • dim_profile: A "Junk Dimension" that compresses repetitive audio features (Modes, Loudness Booleans, Macros Genres).
   • dim_grammy: Official Grammy mappings. We deliberately introduced a grammy_id = 0 ("No Grammy") surrogate dummy row so that Spotify tracks without awards would not crash our Left Joins.
3. Fact Table (fact_track_metrics): A central measurement table recording foreign keys and quantifiable audio statistics (energy, valence, popularity, etc). We utilized SQLAlchemy ALTER TABLE execution right after .to_sql() to ensure Primary/Foreign key constraints physically existed in the Production Database.

---

Airflow Orchestration

Airflow relies on XCom variables to pass data between nodes. Standard practice forbids passing gigantic 114,000 row dataframes through memory. We utilized Airflow Taskflow operators to write intermediate variables to our Docker Volume disk mapping (/data/intermediate/) and securely pass the .csv paths between DAG vertices, avoiding all XCom payload crashes while mimicking the teacher's desired topology.

Here is our live task orchestration graph scaling gracefully:

• read_db and read_csv initiate in parallel.
• The raw data runs through parallel transformations mapping output files (transform_db, transform_csv).
• The orchestrator merges everything sequentially into merge.
• Finally, the pipeline splits off to physically load to PostgreSQL (load) and securely push files to Google Drive (store).

---

Dashboard Reporting (Power BI)

By extracting data through PostgreSQL's port 5433, we tapped directly into the Star Schema. Power BI instantly detected the predefined Primary/Foreign Key relations and enabled highly responsive data visualizations.

We constructed three major Key Performance Indicators (KPIs):

• Total Spotify Tracks Modeled
• Total Associated Grammy Nominations
• Average Dataset Popularity

We also produced advanced comparison graphs (leveraging Data from both distinct APIs):

• Correlation Scatters: Popularity vs. Danceability plotted dynamically over Grammy-Nominated vs Regular Songs.
• Bar Metrics: The average popularity grouped by their respective distinct Grammy Categories.
• Temporal Line Reporting: Tracking variations of Audio "Energy" and "Valence/Emotion" of nominated tracks dynamically across years bridging from 1968 to 2019.

---