PSX Analytics Warehouse

dbt-core + BigQuery analytics layer on top of the psxdata SDK, turning raw Pakistan Stock Exchange (PSX) OHLCV data into tested, analysis-ready marts.

Every run: pull the current KSE-100 constituents and their full OHLCV history from PSX, load it into an append-only, hash-diffed raw layer (no restatement ever silently overwritten), then transform it through dbt into ticker dimensions, sector/index rollups, valuation snapshots, and fact tables ready to query.

uv sync --extra dev --extra dbt
BACKEND=motherduck uv run python -m extract.main   # writes ./warehouse.duckdb, no account needed
cd dbt && uv run --project .. dbt build --target dev

Full walkthrough (local DuckDB/MotherDuck setup, scheduling a local cron sync, or deploying your own copy to GCP): DEPLOYMENT.md.

• extract/ — Python extraction from the psxdata SDK into raw tables. Ships with BigQuery and MotherDuck backends; see CONTRIBUTING.md to add another.
• dbt/ — dbt-core project (staging / intermediate / marts), targeting MotherDuck/DuckDB for local dev and BigQuery for production:
  • models/staging/ — one staging model per raw source (stg_stock_history, stg_symbols, stg_sectors, stg_index_constituents, stg_screener)
  • snapshots/ — dim_tickers, a Type-2 snapshot of ticker attributes (with delisting handling)
  • models/marts/ — three Type-1 dimensions (dim_sectors, dim_indices, dim_date) and five fact tables (fact_ohlcv, fact_restatement_history, fact_index_membership, fact_sector_daily, fact_valuation_daily)
• infra/ — Terraform for the production GCP deployment: one service account, its IAM bindings, a BigQuery dataset, a Cloud Run Job, and a Cloud Scheduler job. See DEPLOYMENT.md.

• DEPLOYMENT.md — run it locally against MotherDuck, or deploy your own copy to GCP.
• CONTRIBUTING.md — add a new raw-storage backend.

• ci.yml — lint, test, and dbt parse on every push/PR to main.
• docker-publish.yml — builds and pushes mtauha/psx-warehouse:latest (+ a short-sha tag) to Docker Hub, then redeploys the Cloud Run Job to that exact image digest via Workload Identity Federation, on every push to main.
• dbt-docs.yml — publishes the dbt lineage docs to GitHub Pages on every dbt/ change.

MIT