TG
All posts
2 min read

Architecting a 24M Record ELT Data Platform on PostgreSQL

Chromium scrapers capped at 1GB each feeding a three-zone PostgreSQL warehouse, where dbt does the transformation in-database.

ELT inverts the usual order: instead of transforming data on middleware before it lands, you load it raw and let the database's own engine do the work. For a financial data platform that meant ingesting 24.7 million time-series records from sources that offered no clean REST API — which made scraping, not transformation, the hard part.

Headless browsers are a memory problem

Acquisition needed aggressive DOM parsing, so we orchestrated Selenium and Chromium in headless mode. On bare metal that failed quickly: headless browsers leak memory and leave zombie processes, and a single runaway scraper was enough to take the host down with an OOM kill.

Containerising the acquisition layer under a multi-profile Docker Compose setup fixed the blast radius rather than the leak. Each scraper is capped at 1 GB of RAM through deploy.resources.limits.memory, so when one spikes the Docker daemon kills and restarts that container alone — the host, and every other scraper on it, keeps running.

ELT INTO A THREE-ZONE POSTGRESQL WAREHOUSEACQUISITION LAYERHEADLESS SCRAPERSDocker ComposePOSTGRESQL WAREHOUSEANALYTICS UITarget sitesnews · government portalsSchedulerfrequency · retriesChromium scraperscapped at 1 GB RAM eachKill and restartone container, not the hostRaw zonelanding tables, as ingesteddbt / SQL migrationsidempotent, version controlledStar schema zonefact + dimension tablesNext.js + Plotlyinteractive dashboard1. dispatch run2. DOM scrapeon memory spike3. raw JSON / CSV4. transform5. model6. querynumbered happy pathcontainer failure path
Three zones, not one database: raw lands as-is, dbt transforms it, and only the star schema is read by anything downstream.

How a record reaches the warehouse

  1. A scheduler dispatches runs across the target sites — news portals, government portals — on a configurable frequency, with retries and error handling.
  2. A Chromium scraper parses the site, handling dynamic content that a plain HTTP client would miss.
  3. It writes raw JSON and CSV straight into the warehouse's raw zone — landing tables, stored exactly as ingested.
  4. dbt and SQL migrations transform, clean and model that data, re-runnable and version controlled.
  5. The result lands in the star schema zone as fact and dimension tables, shaped for analytics rather than for ingestion.
  6. A Next.js and Plotly dashboard queries that zone directly.

Three zones, not one database. Raw data is never edited in place, so a transformation bug is a re-run rather than a reload — and nothing downstream reads anything but the star schema.

Idempotency is what makes it restartable

Over 19 strict, idempotent SQL migrations shape the model, and every one upserts rather than appends. If a scraper container crashes or times out mid-run, re-running it writes the same rows instead of duplicating them — so recovery is just running the job again, with no manual reconciliation and no dedupe pass.

That combination — contained failures during acquisition, idempotent transformation after it — is what produced a 99.9% execution success rate across the pipeline.

GitHub
LinkedIn