Spark transform into a Postgres star schema

The transform task reads parquet out of MinIO, builds the fact and dimension frames, and writes to Postgres via JDBC. Because we partitioned raw by dt, Spark can push the date filter down and read only the affected partition during a backfill.

spark/spark_batch_job.py
python
import sys
from pyspark.sql import SparkSession

run_date = sys.argv[1]  # '2025-04-23'

spark = SparkSession.builder.appName('warehouse-transform').getOrCreate()

raw = (
    spark.read.parquet('s3a://raw-data/orders/')
    .where(f"dt = '{run_date}'")
    .dropDuplicates(['order_id'])
    .dropna(subset=['order_id', 'amount'])
)

dim_customers = (
    raw.select('customer_id', 'customer_email', 'customer_country').distinct()
       .withColumn('dim_customer_sk', F.monotonically_increasing_id())
)

fact_orders = raw.join(
    dim_customers.select('customer_id', 'dim_customer_sk'), on='customer_id', how='left'
).select('order_id', 'dim_customer_sk', 'order_ts', 'amount', 'status')

for frame, table in [(dim_customers, 'stg_dim_customers'), (fact_orders, 'stg_fact_orders')]:
    frame.write.format('jdbc').option('url', PG_URL).option('dbtable', table).mode('overwrite').save()

The job takes the run_date as an argument so backfills can target a single partition. It reads raw, builds dims and fact, then writes to staging tables before promoting to the live tables in a single Postgres transaction inside the Airflow task.

Spark writes to stg_ tables first. A final Airflow task runs a Postgres transaction that promotes stg_ to the live tables atomically. This pattern means a partial Spark failure never leaves half-written gold. The dashboard sees either the previous good state or the new one, never a mix.

Spark transform plus atomic promote

Spark writes staging tables in parallel. A short Postgres transaction promotes them as one unit, so dashboards never see a half-written gold layer.

Staging-then-swap keeps gold consistent even when Spark partially fails.

Because Spark JDBC writes are not atomic across multiple tables. If fact writes succeed and dim writes fail, the warehouse has broken referential integrity. The staging-then-promote pattern uses Postgres transactions to make the swap atomic. Bonus: you can run integrity checks between the stage and the swap.

AI prompt: Try it: generate the Postgres promote transaction

Loading practice…

Quiz: Quiz

Loading practice…

Checkpoint: Checkpoint: the batch pipeline is real

Loading practice…