supply_integration

What it is

A production ELT platform that takes more than a hundred supplier feeds and turns them into operational data models a business can actually plan against. It runs on Google Cloud: Python and SQL for the work itself, Apache Airflow for orchestration, dbt for the transformation layer, BigQuery as the warehouse.

I work on it as part of a remote engineering team.

The problem

A hundred suppliers means a hundred different ideas of what a data feed is. Some expose an API. Some drop files on SFTP. Some maintain a spreadsheet. The payloads arrive as JSON, as XML, as CSV, with their own field names, their own units, their own opinions about what counts as missing.

None of that is unusual, and none of it is interesting to the person downstream who just wants to know what something costs and how long it takes. The difficulty is that heterogeneity at the edge becomes fragility everywhere else: one supplier renames a column and a report is quietly wrong for a week. So the real problem is not parsing a hundred formats. It is absorbing a hundred independent sources of change without the warehouse becoming untrustworthy.

How it works

Extraction is per-source and deliberately dull: fetch, land the payload as it arrived, and do no thinking. Keeping raw arrivals immutable means a transformation bug is replayable rather than an incident, and it draws a hard line between “the supplier changed something” and “we broke something”.

Transformation happens in dbt, where each source is normalised into shared models. Reusable models and macros keep that layer from becoming a hundred bespoke pipelines wearing a trench coat. The shape of the work repeats even when the payloads do not, and the repetition belongs in one place.

The parts that matter most are the least glamorous. Every load is idempotent, so a retry is safe and a partial failure does not double-count. Retry logic absorbs the ordinary flakiness of pulling from a hundred systems that were never built to be pulled from. Tests run in CI on every change, and they encode assumptions about the data rather than about the code. Observability makes a feed going quiet visible on the day it happens, which is the difference between a fix and an archaeology project.

BigQuery transformations are tuned for cost as well as speed, because in a warehouse those are the same conversation. A query nobody notices is one that scans more than it needs to, every hour, for a year.

What I’d change

I would push for more validation at the boundary. Today a malformed feed is caught by tests in the transformation layer, which is early enough to prevent bad data reaching a model but late enough that the diagnosis starts in the wrong place. Contract checks at extraction would name the offending supplier instead of the offending model.

The other change I would argue for is making the per-source extraction layer more declarative. It is honest code, but adding another supplier is still more typing than it should be, and that cost compounds quietly: it shapes how readily anyone adds the next one.