Project: Crypto Medallion ETL (Bronze → Silver → Gold)

Overview

A multi-layer (Bronze → Silver → Gold) ETL pipeline that ingests live cryptocurrency market data from the CoinGecko API, stores raw and cleaned records in PostgreSQL, and produces daily aggregated insights. The pipeline is orchestrated by pipeline.py, version-controlled on GitHub, and implemented in Python (Anaconda). The Claude AI agent assisted during development. Logs are written to etl.log.

Goals

Ingest top N coins from CoinGecko on an hourly cadence.

Preserve raw extracted records (Bronze) for auditing and reprocessing.

Enrich and normalize data (Silver) for analysis-ready consumption.

Produce daily aggregated summaries and top-movers (Gold) for reporting and downstream analytics.

Architecture — Medallion Layers

CoinGecko API

▼

Bronze Layer → coins table

▼

Silver Layer → coins_silver table

▼

Gold Layer → gold_daily_summary + gold_top_movers

Bronze: extract.py → transform.py → load.py → coins table

Silver: silver.py → coins_silver table

Gold: gold.py → gold_daily_summary + gold_top_movers tables

Orchestration: pipeline.py. runs all three layers end-to-end

Automation cron job runs pipeline.py every hour (add manually via crontab -e)

Data Flow (High-level)

• pipeline.py triggers extraction, transformation, loading for Bronze.
• Bronze layer persists cleaned raw snapshots to coins.
• Silver layer reads recent Bronze snapshots, enriches data (derived fields, currency normalization), and writes to coins_silver.
• Gold layer computes daily summaries and top movers and writes to gold_daily_summary and gold_top_movers.
• Cron schedules pipeline hourly.

Project Structure

├── pipeline.py # Orchestrates full Bronze → Silver run → Gold

├── extract.py # Fetch top N coins from CoinGecko API

├── transform.py # Select and clean fields (Bronze)

├── load.py # Insert into Bronze coins table

├── silver.py # Extract from Bronze, enrich, load into Silver

├── config.py # DB connection via .env credentials

├── db_setup.sql # SQL to create coins and coins_silver tables

├── etl.log # Auto-generated pipeline log

├── #test_extract.py # Unit test for extract

├── # test_transform.py # Unit test for transform

├── #test_load.py # End-to-end Bronze pipeline test

└── test_silver.py # End-to-end Silver pipeline test

Environment

– Python 3.8 (Anaconda) — ~/opt/anaconda3/bin/python

– PostgreSQL with psycopg2

– Credentials stored in .env (not committed to git)

– Logs written to etl.log

---

Tools: Jupiter Notebook | PostgreSQL | Python: Pandas | NumPy | Matplotlib | Seaborn | SciPy

---

Project Overview

This project implements a multi-layer ETL pipeline for analyzing survey data using the Medallion Architecture pattern. The pipeline extracts raw survey data, performs data quality checks, applies transformations, and prepares, clean and normalized data using Python and PostgreSQL for analysis.

Dataset:

Our data set is a survey works among IT professional , collected and published in the link below.

“https://api.example.com/data”

It has 11551 records and 84 columns. (11552, 85)

Architecture

The project follows the **Medallion Architecture** pattern:

• Bronze Layer: Raw data ingestion from APIs and databases

• Silver Layer: Data cleaning, normalization, and transformation

• Gold Layer: (Future) Aggregated, business-ready analytics

Pipeline Components

Bronze Layer

The Bronze layer handles:

– Data extraction from external APIs

– Connection to PostgreSQL database

– Loading raw survey data

– Initial column filtering and selection

– Raw data storage for future reference

Key Features:

– API data ingestion

– Database connectivity with PostgreSQL

– Column management and initial filtering

– Raw data preservation

---

Silver Layer

The Silver layer performs:

– Data quality checks (duplicates, missing values)

– Data cleaning and imputation

– Currency and payment frequency normalization

– Data type standardization

– Statistical analysis preparation

– Chi-square tests for categorical variables

Key Operations:

– Duplicate detection and removal

– Missing value identification and imputation

– Earnings normalization across currencies

– Payment frequency standardization to annual income

– Data validation and quality reporting

---

Running the Pipeline

1. Bronze Layer Run [Bronze_layer 1.38.59 PM.ipynb](Bronze_layer%201.38.59%20PM.ipynb)

– Extracts raw data from sources

– Loads into PostgreSQL

2. Silver Layer: Run [Silver_layer 1.38.59 PM.ipynb](Silver_layer%201.38.59%20PM.ipynb)

– Cleans and transforms data

– Normalizes earnings and currencies

– Performs quality checks

Data Transformation Details

Earnings Normalization

– Converts various payment frequencies to annual income

– Standardizes multiple currencies to USD

– Uses current exchange rates for accurate conversion

Data Quality Checks

– Duplicate record detection

– Missing value analysis

– Data type validation

– Statistical distribution analysis

Security Notes

– Database credentials are stored separately (not in version control)

– API keys and sensitive data are managed in external configuration files

– Follow the principle of least privilege for database access

Future Enhancements

– [ ] Implement Gold layer for business analytics

– [ ] Add automated data validation rules

– [ ] Create data quality dashboard

– [ ] Implement incremental data loading

– [ ] Add comprehensive error handling

– [ ] Create automated testing suite

– [ ] Add CI/CD pipeline integration

– [ ] Implement data versioning

License

This project is for educational and analysis purposes.

Author

Rana

**Note**: This project is under active development. Refer to individual notebooks for detailed implementation and analysis steps.

---