IBM ETL Data Flow
Alpha Vantage API → PostgreSQL Medallion Architecture
Source
Extract
Alpha Vantage API
Pull daily IBM OHLCV stock data from the public API and parse the response into a pandas DataFrame.
IBM Ticker
OHLCV
pandas DataFrame
Python
▼
Load raw records
INSERT INTO IBM_history
Bronze
Stage
IBM_history
Raw API records stored without deduplication. Multiple loads may produce repeated dates — this is the landing zone.
Append-Only
Raw Records
Repeated Dates OK
PostgreSQL
▼
Upsert on Date — keep newest Last_updated
MERGE / ON CONFLICT DO UPDATE
Silver
Clean
IBM
One clean, deduplicated record per business date. Upsert logic retains the most recently loaded value per Date.
1 Row / Date
Upsert
Deduped
Watermark
▼
Compute business metrics from silver
Stored Procedure → IBM_gold
Gold
Analytics
IBM_gold
Analytics-ready metrics derived from the silver table — daily return, 7-day MA, 30-day MA, 30-day volatility, and volume moving average.
Daily Return
7-Day MA
30-Day MA
30-Day Vol
Volume MA