Market Data Gold Layer
Reporting & AI
Project Summary
The gold layer sits at the top of the market data medallion architecture, consuming clean silver tables from BigQuery and producing four distinct outputs: a company fundamentals card combining FMP profile, annual financials, analyst ratings, and insider sentiment; a daily price movement view with rolling averages and momentum signals for BI reporting; a flat ML feature table for Vertex AI training; and an SCD Type 2 snapshot for historical audit and drift analysis.
The reporting path feeds a Looker Studio dashboard with daily price movement, momentum signals, and fundamental company data across tracked tickers. The AI path builds structured feature vectors by joining daily price signals with static fundamentals, applying per-ticker z-score normalisation so the model learns relative deviations rather than absolute magnitudes — predicting whether a stock will be higher in 7 trading days.
Key Requirements
- • Build a company fundamentals card joining FMP profile, latest FY income, balance sheet, and cash flow with computed derived ratios.
- • Enrich analyst and insider sentiment from Finnhub and join with the company profile for a unified, query-ready view.
- • Produce a daily price movement view with 7/30-day moving averages, trailing returns, and volume spike ratios for Looker Studio.
- • Generate a flat ML feature table with per-ticker z-score normalised signals, lag-1 features, and raw fundamentals for Vertex AI training.
- • Snapshot company fundamentals as SCD Type 2 using dbt's check strategy to capture historical changes for audit and drift analysis.
Architecture
Silver marts split into two parallel gold paths — reporting and AI prediction.
Silver Marts — BigQuery
Clean, deduplicated, tested window records produced by the dbt silver layer.
dbt Gold Aggregations
Daily OHLCV time-series with 7 and 30-day moving averages, trailing returns, volume spike ratios, and momentum signals. Materialised as a BigQuery view on each dbt run.
Looker Studio Dashboard
Price movement dashboards with momentum signals, fundamental company cards, and historical trend lines per ticker. Connected directly to BigQuery gold views.
ML Feature Engineering
dbt joins daily price movement signals with static fundamentals and applies per-ticker z-score normalisation across five feature groups (raw, z-score, lag-1, returns, sentiment). Writes one flat row per ticker per day to a BigQuery table.
Vertex AI — Classifier
A binary classifier trained on historical price direction labels. Batch predictions run on a schedule and write buy/sell probability scores back to BigQuery for downstream consumption.
dbt Gold Models
Four purpose-built models — two views, one table, and one snapshot — each consuming silver source tables and serving a distinct downstream use case. gold_ml_features depends on both gold_daily_price_movement and gold_company_latest_details; gold_company_scd2 wraps gold_company_latest_details to capture historical changes.
gold_company_latest_details
One row per ticker — the most recent available data across every source. Joins FMP profile with the latest annual income statement, balance sheet, and cash flow, then enriches with Finnhub analyst recommendations and insider sentiment. Computes derived ratios: EBITDA margin, FCF margin, net debt/EBITDA, interest coverage ratio, and bullish analyst ratio. Materialised as a BigQuery view.
| Field | Type | Description |
|---|---|---|
| Identity | ||
| ticker | STRING | Stock ticker symbol — natural key, one row per ticker |
| company_name | STRING | Full registered company name |
| sector · industry | STRING | Market sector and industry sub-classification |
| country · exchange · currency | STRING | Country of incorporation, listing exchange, and trading currency |
| cik | STRING | SEC Central Index Key |
| ipo_date | DATE | IPO or first listing date |
| website | STRING | Official corporate website |
| ceo | STRING | Current CEO name |
| full_time_employees | INT64 | Total full-time headcount |
| is_etf · is_actively_trading | BOOLEAN | ETF flag and active trading status |
| Valuation | ||
| market_cap | FLOAT64 | Current market capitalisation (USD) |
| beta | FLOAT64 | Price volatility relative to the market |
| last_dividend | FLOAT64 | Most recent dividend per share |
| Income Statement | ||
| latest_income_date | DATE | Period end date of the latest income statement |
| income_fiscal_year | INT64 | Fiscal year of latest income data |
| revenue | FLOAT64 | Total revenue |
| gross_profit · operating_income | FLOAT64 | Gross profit and EBIT |
| net_income · ebitda | FLOAT64 | Bottom-line net earnings and EBITDA |
| eps · eps_diluted | FLOAT64 | Basic and diluted earnings per share |
| interest_expense | FLOAT64 | Total interest paid on debt |
| depreciation_and_amortization | FLOAT64 | D&A from income statement |
| Margins | ||
| gross_margin · net_margin | FLOAT64 | Gross profit and net income as a fraction of revenue |
| ebitda_margin · fcf_margin | FLOAT64 | EBITDA and free cash flow as a fraction of revenue |
| interest_coverage_ratio | FLOAT64 | Operating income divided by interest expense |
| Balance Sheet | ||
| latest_balance_date | DATE | Period end date of the latest balance sheet |
| total_assets · total_liabilities | FLOAT64 | Total assets and total liabilities |
| total_stockholders_equity | FLOAT64 | Shareholders' equity |
| total_debt | FLOAT64 | Short and long-term debt combined |
| cash_and_equivalents | FLOAT64 | Cash and cash equivalents |
| cash_and_short_term_investments | FLOAT64 | Cash plus short-term investments |
| net_receivables · inventory | FLOAT64 | Net accounts receivable and inventory value |
| debt_to_equity · liability_ratio | FLOAT64 | Leverage ratios: debt/equity and liabilities/assets |
| net_debt_to_ebitda | FLOAT64 | (Total debt − cash) / EBITDA |
| Cash Flow | ||
| latest_cashflow_date | DATE | Period end date of the latest cash flow statement |
| operating_cash_flow · free_cash_flow | FLOAT64 | Net cash from operations and free cash flow |
| capital_expenditure | FLOAT64 | Capital expenditure spending |
| net_dividends_paid | FLOAT64 | Net cash paid as dividends |
| stock_based_compensation | FLOAT64 | SBC non-cash expense |
| Analyst Ratings | ||
| latest_rec_period | DATE | Date of the most recent analyst rating snapshot |
| strong_buy · buy · hold · sell · strong_sell | INT64 | Count of analyst ratings by grade |
| total_bullish · total_bearish | INT64 | Aggregated Strong Buy+Buy count and Sell+Strong Sell count |
| bullish_ratio | FLOAT64 | Bullish ratings / total ratings |
| Insider Activity | ||
| latest_insider_period | DATE | Period of the most recent insider transaction |
| insider_net_purchase_shares | FLOAT64 | Net shares bought minus sold by insiders |
| insider_mspr | FLOAT64 | Monthly share purchase ratio |
gold_daily_price_movement
Daily price summary and momentum metrics for every tracked ticker — one row per symbol per trading day. Built from Alpha Vantage quotes. Computes 7 and 30-day moving averages (price + volume), trailing 7/30-day returns via LAG windows, and momentum signals (price vs MA, volume spike ratio). Materialised as a BigQuery view.
| Field | Type | Description |
|---|---|---|
| symbol | STRING | Stock ticker — composite PK with trading_date |
| trading_date | DATE | Trading session date — composite PK with symbol |
| open · high · low · close | FLOAT64 | OHLC prices for the session |
| volume | INT64 | Session trading volume |
| price_change | FLOAT64 | Absolute price change from prior close |
| change_percent | FLOAT64 | Percentage price change from prior close |
| previous_close | FLOAT64 | Prior session's closing price |
| price_ma_7d · price_ma_30d | FLOAT64 | 7-day and 30-day rolling simple moving averages of close price |
| volume_ma_7d · volume_ma_30d | FLOAT64 | 7-day and 30-day rolling moving averages of volume |
| return_7d · return_30d | FLOAT64 | 7-day and 30-day lookback return (close vs N-day prior close) |
| price_vs_ma_7d · price_vs_ma_30d | FLOAT64 | Deviation of current close from the 7-day and 30-day moving average |
| volume_ratio_vs_30d | FLOAT64 | Today's volume divided by the 30-day average volume |
gold_ml_features
Flat, standardized feature table for binary ML classification — one row per ticker per trading day. Joins gold_daily_price_movement with gold_company_latest_details. Organises features into five groups: raw absolutes, per-ticker z-scores, lag-1 signals, trailing returns, and analyst/insider sentiment. Materialised as a BigQuery table for fast ML export.
| Field | Type | Description |
|---|---|---|
| Keys | ||
| ticker | STRING | Stock ticker — composite PK with trading_date |
| trading_date | DATE | Feature row date — composite PK with ticker |
| Metadata (categorical) | ||
| sector · industry · exchange | STRING | Categorical identifiers for grouping or one-hot encoding |
| Raw Absolute Features (kept for threshold-based filters) | ||
| close · volume | FLOAT64 | End-of-day close price and session volume |
| market_cap · beta | FLOAT64 | Market cap and market-relative volatility — carry cross-ticker information, kept raw |
| gross_margin · net_margin · ebitda_margin · fcf_margin | FLOAT64 | Profitability margin ratios from latest annual income statement |
| eps · debt_to_equity · liability_ratio | FLOAT64 | Earnings per share and leverage ratios from latest annual filings |
| free_cash_flow · ebitda · bullish_ratio · insider_mspr | FLOAT64 | Cash generation, earnings, analyst bullish ratio, and insider monthly share purchase ratio |
| Trailing Returns (already relative — no z-score applied) | ||
| return_7d · return_30d | FLOAT64 | 7-day and 30-day lookback returns — preserved as-is, already relative measures |
| Lag-1: Previous Trading Day (raw) | ||
| prev_close · prev_change_percent · prev_volume | FLOAT64 | Prior day close price, percentage change, and volume |
| Z-Score: Price Momentum (per-ticker normalisation) | ||
| z_change_percent | FLOAT64 | Daily % change normalised against this ticker's own history |
| z_price_vs_ma_7d · z_price_vs_ma_30d | FLOAT64 | Price deviation from 7/30-day moving average, z-scored per ticker |
| z_volume_ratio | FLOAT64 | Volume ratio vs 30-day average, z-scored per ticker |
| Z-Score: Fundamentals (per-ticker normalisation) | ||
| z_gross_margin · z_net_margin · z_eps | FLOAT64 | Profitability and earnings metrics normalised against each ticker's own history |
| z_debt_to_equity · z_free_cash_flow | FLOAT64 | Leverage and cash generation z-scored per ticker |
| Z-Score: Previous Day Features | ||
| z_prev_change_percent · z_prev_price_vs_ma_7d · z_prev_volume_ratio | FLOAT64 | Prior trading day signals z-scored per ticker for temporal context |
| Analyst / Insider Sentiment (naturally bounded — kept raw) | ||
| total_bullish · total_bearish | INT64 | Analyst count by sentiment direction from latest recommendation period |
| insider_net_purchase_shares | FLOAT64 | Net shares bought minus sold by insiders in the most recent period |
gold_company_scd2
Type-2 Slowly Changing Dimension built on gold_company_latest_details — one versioned row per change event per ticker, with a full audit trail of how fundamentals, valuation, and analyst/insider sentiment have evolved over time.
How it works
dbt uses the check strategy with 33 monitored columns — any change to a tracked field triggers a new row version. When a change is detected, the previous record's dbt_valid_to is stamped with the snapshot timestamp and a new row is inserted with dbt_valid_to = NULL, marking it as the current version. The unique key is ticker.
dbt-managed metadata columns
| Column | Type | Description |
|---|---|---|
| dbt_scd_id | STRING | Unique surrogate key per version row |
| dbt_valid_from | TIMESTAMP | When this version of the record became active |
| dbt_valid_to | TIMESTAMP | When it was superseded — NULL = current record |
| dbt_updated_at | TIMESTAMP | Timestamp of the last detected change |
Monitored change columns (33)
Query pattern for current records
SELECT * FROM gold_company_scd2 WHERE dbt_valid_to IS NULL
Tech Stack
Tools and services used across the gold layer.