Technical overviewData engineering and dimensional modelling

Batch Warehouse

A job-posting corpus landed immutably, modelled in Spark, and loaded into PostgreSQL by an idempotent write.

Source code

Inspect the implementation, commit history, and project documentation.

Role: Solo engineer: ingest, Spark transform, dimensional model, PostgreSQL load, tests, CI
Evidence status: Public repository; ingest, transform, and load are merged with all continuous-integration gates green. The modelled warehouse loads into PostgreSQL on a private host and is verified idempotent in continuous integration. A scheduled orchestrator, a query-tuning pass, and a public read-only view are the remaining steps.
Verified: 2026-08-26

Purpose and scope

A batch pipeline over 24,636 landed observations of 18,969 distinct job postings. Raw scrapes land immutably by ingest date; a Spark job conforms and deduplicates them into two fact tables and four dimensions; a loader mirrors the model into PostgreSQL. The grain is written before any transform, and every rerun is idempotent.

What it is used for

Answer whether a posting was open on a given date, from the observation grain.
Measure how long a market holds a vacancy and how much it churns.
Query one clean dimensional model instead of nine inconsistent scrape formats.

Architecture

Source

Upstream data

Job-posting corpus

47 raw scrapes, 143 MB, nine boards

Selection

Ingest selector

Globs the real scrapes, excludes derived files

Land · immutable, by ingest date

Landing

Immutable store

Partitioned landing

One partition per ingest date, never mutated

Durability

Atomic write

Temp file then rename, gzip mtime zero

Exclusion

PII boundary

The emails column never enters the warehouse

Conform · Spark in a container

Transform

Processing

Spark transform

Runs in the official Spark image, not on Windows

Business logic

Conforming rules

Pure functions: company folding, location resolution

Schema

Dimensional model

Two fact tables and four dimensions

Load · idempotent, per partition

Serving

Database

PostgreSQL warehouse

wh schema, real foreign keys, grain in table comments

Loader

Load modes

Full load in one transaction, or replace one ingest date

Enforcement

CI gates

Idempotency and referential integrity checked on each push

Text equivalent: Raw scrapes land immutably in date partitions after the emails column is dropped; a Spark job in the official image conforms and deduplicates them into two fact tables and four dimensions, with the conforming rules kept as pure functions; a loader mirrors that model into PostgreSQL on a private host, replacing a full load or a single ingest date without ever duplicating rows.

Engineering decisions

Write the grain before the transform

The grain of each fact is committed to the README before any transform code, because a landing step that quietly repairs its input destroys the evidence of what the source actually sent.

Keep two fact tables, not one

The observation fact keeps one row per posting, per source, per ingest date. Collapsing to one row per posting first would permanently destroy whether a posting was open on a given date.

Put the conforming judgement in pure functions

The rules that fold company names and resolve locations import nothing from Spark, so 61 tests exercise every rule in 0.06 seconds. A rule that only runs by starting a session is a rule nobody runs.

Strip legal-form suffixes, not country suffixes

Folding Inc, Ltd, and Corp merges 127 spellings of the same employer. Folding Canada or USA would merge KPMG with KPMG Canada, a global brand and a national entity a job-market warehouse keeps apart.

Verification evidence

The transform’s row counts cross-check against an independent profile: fact_posting equals the 18,969 distinct job_url values, dim_company equals the 5,878 legal-form keys measured before any code, and fact_posting_observation is 24,519 after the duplicate-triple collapse.
Two full loads leave identical counts and zero orphan keys, and reloading one partition twice leaves its count unchanged, checked against a Postgres service in CI.
Idempotency is proved on bytes: re-landing an ingest date reproduces its sha256 exactly. The 61 conforming tests run with no Spark, network, or database in 0.06 seconds; the Spark and load suites run in CI.

verified-2026-08-26

$ spark-submit warehouse/transform.py
fact_posting_observation 24,519 · fact_posting 18,969
dim_company 5,878 · dim_location 1,534 · dim_source 9 · dim_date 123
$ python -m warehouse.load --full   (then re-run)
reload leaves identical counts · 0 orphan keys
✓ idempotent on the real corpus

Limitations

This is a single-node, local-mode Spark job over 143 MB, not big data; the engineering is the modelling and the idempotent load, not scale.
PostgreSQL runs on a private, tailnet-bound host, so there is no public query endpoint yet; a read-only view and a scheduled orchestrator are the remaining steps.
Title normalisation is deliberately left as a raw attribute rather than a role dimension, because a half-built role dimension that mislabels a third of the corpus would be worse than none.