Where they were
A marketplace with buyers, sellers, payments and a mobile app — so, data arriving from everywhere. It landed in Snowflake via a nightly Python job that someone wrote in a hurry and everyone was afraid of. If it failed (and it failed about once a fortnight), the ops dashboards were empty until someone noticed and re-ran it by hand.
The business wanted same-day pricing decisions. The data was six hours old on a good day.
What was actually hard
- Seven source systems, seven formats. A payments webhook, an app event stream, two Postgres databases, a SaaS CRM, and daily CSVs from a logistics partner that occasionally changed their column order without telling anyone.
- Failures were silent. The job “succeeded” whenever it didn’t crash. Missing files and empty tables counted as success.
- No one owned the schedule. The job ran at 02:00 because that was when it was written.
What we did
One pattern per source type, no clever abstractions:
- Event stream → Snowpipe. The app events land in a stage and Snowpipe loads them continuously. Latency went from hours to about a minute, with no scheduler at all.
- Databases and CRM → Fivetran. Log-based replication every five minutes. Boring is the point.
- Partner CSVs → external tables. The files stay in object storage; Snowflake reads them in place. When the partner reorders columns, a schema test fails loudly with the diff, instead of loading garbage.
- dbt source tests on everything — freshness, row-count anomaly, not-null on keys, accepted values on enums. 180-odd tests, and every one of them pages a Slack channel with the failing query attached.
The only Python left is a forty-line script that renames the partner files. We kept it because it works.
What changed
- Data latency from ~6 hours to ~12 minutes end-to-end, including dbt.
- 180+ tests on the source layer. The partner changed their file format twice in the first two months; both times the test caught it before a dashboard did.
- 0 silent failures in the first 60 days. Two real failures, both caught within minutes, both fixed before anyone outside the data team noticed.
The morning ritual of checking the load is gone. That, more than the latency number, is what the team mentions.
Representative engagement — details anonymized and generalised.