Market Data Bronze Layer Pipeline
Project Summary
Built a scheduled, multi-source ingestion pipeline that pulls raw financial data from four external APIs — Alpha Vantage, SEC EDGAR, Finnhub, and Financial Modeling Prep — and lands each payload as an immutable, partitioned bronze table in BigQuery. Cloud Scheduler triggers a dedicated Cloud Function per source on its own cron cadence, keeping ingestion isolated and independently retriable.
Every record is stored exactly as received with a source tag, ingestion timestamp, and run ID appended. No transformations are applied at this layer — the bronze tables are the canonical, replayable record of truth that all downstream silver models build from.
Key Requirements
- • Ingest from 4 financial APIs on independent schedules.
- • Store raw payloads unchanged with source lineage metadata.
- • Partition tables by ingestion date for cost-efficient querying.
- • Isolate source failures — one broken feed must not block others.
- • Support full replay from bronze without re-calling the upstream APIs.
Architecture
Cloud Scheduler fires per-source workers that pull from financial APIs and write raw records straight to BigQuery.
Cloud Scheduler
Cron-based triggers fire each source worker on its own cadence — intraday for quotes, daily for fundamentals and filings.
Cloud Functions
Cloud Scheduler invokes a dedicated Cloud Function per source. Each function calls its API, appends lineage metadata (source, ingested_at, run_id), and streams raw rows directly into BigQuery.
APIs
4 financial data sources — each polled once per day after market close due to API limit timing requirements. See source breakdown below.
BigQuery Bronze
Partitioned, append-only bronze tables — one per source/data type. Immutable raw records, never overwritten, ready for silver dbt models.
Table Schemas by Source
All tables auto-append: _ingested_at TIMESTAMP
Alpha Vantage
bronze_alpha_quotes
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| open · high · low · price | STRING | OHLC prices for the interval |
| volume | STRING | Trading volume |
| latest_trading_day | STRING | Latest trading date |
| previous_close | STRING | Previous close price |
| change · change_percent | STRING | Price and percentage offset |
SEC EDGAR
bronze_edgar_13f
| Field | Type | Description |
|---|---|---|
| cik | STRING | Central Index Key |
| entity_name | STRING | Institutional holder name |
| ticker | STRING | Traded symbol |
| shares_held | INT64 | Total number of shares |
| market_value | FLOAT64 | Value of the position |
| period_of_report | DATE | End date of report period |
bronze_edgar_submissions
| Field | Type | Description |
|---|---|---|
| cik | STRING | Central Index Key |
| ticker | STRING | Traded symbol |
| form | STRING | Form type (e.g., 10-K, 8-K) |
| filed | BOOLEAN | Has been successfully filed |
| filing_date | DATE | Date the form was filed |
| company_name | STRING | Full registered company name |
Finnhub
bronze_finnhub_financials
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| period | STRING | Fiscal period (quarter/annual) |
| revenue | FLOAT64 | Total reported revenue |
| net_income | FLOAT64 | Reported net income |
| eps | FLOAT64 | Earnings per share |
| debt_equity | FLOAT64 | Debt to equity ratio |
bronze_finnhub_news
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| headline | STRING | News headline text |
| source | STRING | Publishing entity |
| sentiment_score | FLOAT64 | NL calculated sentiment |
| datetime | TIMESTAMP | Time of publication |
bronze_finnhub_recommendations
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| analyst | STRING | Name of firm or analyst |
| from_grade | STRING | Previous recommendation |
| to_grade | STRING | New recommendation |
| action | STRING | Action (up/down/init) |
| date | DATE | Date of the rating |
bronze_finnhub_insider
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| year · month | INT64 | Date grouping of trades |
| change | INT64 | Net shares bought/sold |
| mspr | FLOAT64 | Monthly share purchase ratio |
Financial Modeling Prep
bronze_fmp_income
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| date · period | STRING | Filing date and FY/QTR |
| revenue | FLOAT64 | Top line revenue |
| gross_profit | FLOAT64 | Revenue minus COGS |
| net_income | FLOAT64 | Bottom line profit |
| eps | FLOAT64 | Earnings per share |
bronze_fmp_balance
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| date · period | STRING | Filing date and FY/QTR |
| total_assets | FLOAT64 | Total reported assets |
| total_liabilities | FLOAT64 | Total reported liabilities |
| equity | FLOAT64 | Total shareholders equity |
| cash | FLOAT64 | Cash & equivalents |
bronze_fmp_cashflow
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| date · period | STRING | Filing date and FY/QTR |
| operating_cf | FLOAT64 | Cash from operations |
| investing_cf | FLOAT64 | Cash from investing |
| free_cash_flow | FLOAT64 | Operating CF - CapEx |
bronze_fmp_profile
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker symbol |
| company_name | STRING | Full registered name |
| sector · industry | STRING | GICS classification |
| market_cap | FLOAT64 | Total market value |
| exchange | STRING | Primary listing venue |
Engineering Decisions
Key trade-offs made during the build.
Serverless Everywhere (Cloud Functions)
We opted for Cloud Functions over Cloud Run to prioritize cost-efficiency, embracing a "serverless everywhere" ideology. Each API has its own independent Cloud Function. A rate limit, API outage, or schema change on one feed has zero blast radius on others — failures are isolated, logged, and retriable independently.
Append-Only Bronze Tables
No updates or deletes ever touch the bronze layer. Each ingestion run appends new rows with an _ingested_at timestamp. This makes every run fully replayable and makes debugging silver model issues trivial — you can always re-point at the exact bronze snapshot.
Once-Per-Day Cadence
All data pipelines are exclusively processed once per day after market close. Syncing our ingestion schedule directly to the financial markets avoids redundant API calls throughout the day and ensures our API quotas easily sit well below the free-tier thresholds.
Tech Stack
Tools and services used across the ingestion layer.