Getting a nightly load from 5h40 down to 1h45

The symptom

Reporting ran on a read replica of the production PostgreSQL database and a folder of SQL scripts started by cron at 01:00. On a good night it finished at 06:40. About twice a week it ran past 08:00, and clinic managers opened dashboards that still showed the previous day.

Where the time went

Before changing anything, we timed each step of the load for two weeks.

Step	Share of runtime
Full reload of appointment and patient tables	61%
Rebuilding aggregates from scratch	24%
Everything else	15%

The appointment table had 40 million rows, and fewer than 1% changed on a given day. It was being copied in full every night.

What we changed

• Ingestion moved to Airflow with incremental loads, based on updated_at plus a weekly full reconciliation to catch rows the timestamp misses.
• Patient identifiers are pseudonymised during ingestion, so the warehouse never holds direct identifiers. This was a requirement from the client's data protection officer, and it also simplified access control in Snowflake.
• The core models were rebuilt in dbt as incremental models.
• Great Expectations checks run after ingestion. If row counts or key columns look wrong, the load stops before the dashboards refresh, so a bad load leaves yesterday's numbers in place instead of overwriting them.

Result

nightly load, median

5h40 → 1h45

loads past 08:00 in the last 3 months

0