📈 Stock Market Analytics Pipeline

An end-to-end automated data pipeline that fetches real Indian stock market data, loads it into PostgreSQL, and transforms it into analytics-ready tables — all orchestrated by Apache Airflow and running locally in Docker.

---

🏗️ Architecture

┌─────────────────┐     ┌──────────────────┐     ┌─────────────────┐
│   Yahoo Finance  │────▶│  Apache Airflow  │────▶│   PostgreSQL    │
│   (yfinance API) │     │  (Orchestration) │     │   (Storage)     │
└─────────────────┘     └──────────────────┘     └────────┬────────┘
                                                           │
                                                  ┌────────▼────────┐
                                                  │      dbt        │
                                                  │ (Transformation)│
                                                  └────────┬────────┘
                                                           │
                                   ┌───────────────────────┴───────────────────────┐
                                   │                                               │
                          ┌────────▼────────┐                           ┌──────────▼──────────┐
                          │ mart_stock_daily │                           │ mart_stock_summary  │
                          │ (620 rows +      │                           │ (5 rows, one        │
                          │  moving averages)│                           │  per stock)         │
                          └─────────────────┘                           └─────────────────────┘

🔄 Pipeline Flow

fetch_and_load_stocks  ──▶  dbt_run  ──▶  dbt_test
     (Python)              (BashOp)      (BashOp)

The pipeline runs automatically at 6:00 PM IST every weekday (Mon–Fri).

---

📊 Stocks Tracked

Ticker	Company	Exchange
RELIANCE.NS	Reliance Industries	NSE India
TCS.NS	Tata Consultancy Services	NSE India
INFY.NS	Infosys	NSE India
HDFCBANK.NS	HDFC Bank	NSE India
WIPRO.NS	Wipro	NSE India

---

🛠️ Tech Stack

Tool	Version	Purpose
Apache Airflow	2.9.1	Orchestration & scheduling
dbt-core	1.8.0	Data transformation
dbt-postgres	1.8.0	dbt PostgreSQL adapter
PostgreSQL	15	Data storage
Python	3.12	Ingestion scripting
yfinance	Latest	Yahoo Finance API client
Docker	29.5+	Containerization
Docker Compose	v5+	Multi-container management

---

📁 Project Structure

stock-pipeline/
├── airflow/
│   ├── dags/
│   │   └── indian_stock_pipeline.py
├── dbt/
│   ├── models/
│   │   ├── staging/
│   │   │   ├── sources.yml
│   │   │   └── stg_stock_prices.sql
│   │   └── marts/
│   │       ├── mart_stock_daily.sql
│   │       └── mart_stock_summary.sql
│   ├── profiles.yml
│   └── dbt_project.yml
├── scripts/
│   └── init_db.sql
├── Dockerfile
├── docker-compose.yml
└── .env

---

🚀 Getting Started

Prerequisites

• Docker Desktop (v24+)
• Git

Setup

1. Clone the repository

git clone https://github.com/arnavv-agarwal/stock-pipeline.git
cd stock-pipeline

2. Create the .env file

AIRFLOW_UID=50000
POSTGRES_USER=airflow
POSTGRES_PASSWORD=airflow
POSTGRES_DB=airflow
DBT_POSTGRES_USER=dbt_user
DBT_POSTGRES_PASSWORD=dbt_pass
DBT_POSTGRES_DB=stock_db

3. Build and initialize

docker compose build
docker compose up airflow-init

4. Start all services

docker compose up -d

5. Open Airflow UI

• URL: http://localhost:8080
• Username: admin
• Password: admin

6. Trigger the pipeline

• Find indian_stock_pipeline in the DAG list
• Toggle it on and click ▶ Trigger DAG
• Watch all 3 tasks turn green ✅

---

🗄️ Data Models

Raw Layer (raw.stock_prices)

Column	Type	Description
ticker	VARCHAR	Stock symbol (e.g. TCS.NS)
trade_date	DATE	Trading date
open_price	NUMERIC	Opening price
high_price	NUMERIC	Daily high
low_price	NUMERIC	Daily low
close_price	NUMERIC	Closing price
volume	BIGINT	Shares traded

Staging Layer (stg_stock_prices)

Column	Description
daily_return_pct	(close - open) / open * 100
daily_range	high - low

Mart Layer

mart_stock_daily — Daily prices + 7d/30d moving averages

mart_stock_summary — 1 aggregated row per stock with volatility metrics

---

📈 Sample Results (Dec 2025 – Jun 2026)

Ticker	Avg Close (₹)	Lowest (₹)	Highest (₹)	Avg Daily Return	Volatility
HDFCBANK.NS	827.26	719.65	985.30	+0.055% ✅	1.29
INFY.NS	1,304.34	1,029.30	1,654.01	-0.271%	1.49
RELIANCE.NS	1,386.47	1,258.80	1,584.97	-0.120%	1.42
TCS.NS	2,574.03	2,059.60	3,193.46	-0.347%	1.58
WIPRO.NS	210.17	174.48	263.98	-0.343%	1.55

HDFCBANK was the only stock with a positive average daily return during this period.

---

🔧 Useful Commands

# Verify data in PostgreSQL
docker exec -it stock-pipeline-postgres-1 psql -U airflow -d stock_db \
  -c "SELECT * FROM staging_marts.mart_stock_summary;"

# Run dbt manually
docker exec -it stock-pipeline-airflow-scheduler-1 bash -c \
  "cd /opt/airflow/dbt && dbt run --profiles-dir /opt/airflow/dbt"

# View logs
docker compose logs airflow-scheduler --tail=50

# Full reset
docker compose down -v

---

📌 What I Learned

• Building and orchestrating multi-task Apache Airflow DAGs
• Writing dbt models across staging and mart layers
• Using SQL window functions for rolling moving averages
• Managing Docker multi-container environments with Compose
• Designing a layered data warehouse (raw → staging → marts)
• Ingesting real financial data using the yfinance Python library

---

🗺️ Roadmap

• Add dbt schema tests (not_null, unique)
• Add dbt documentation (dbt docs generate)
• Connect Metabase/Superset for visualization
• Add email alerting on pipeline failure
• Expand to more NSE stocks
• Deploy to AWS (MWAA + RDS)

---

👤 Author

Arnav Agarwal

• GitHub: @arnavv-agarwal

---

Built with ❤️ as a data engineering portfolio project