cat projects/sap

The problem. A DataStage to Netezza to Cognos stack was being retired and the SAP data underneath it had to land in AWS. SAP does not present as one source: it is services and sub-services, hundreds of them, each with its own refresh expectation, and the ones finance cares about are not the ones supply chain cares about. Assembling that by hand was never on the table — four hundred flows built in a console is four hundred things that exist only as somebody's memory of having clicked them.

The architecture. Every service and sub-service pair is one AppFlow flow, and no flow is authored by hand: a Python module over the AppFlow client takes the pair and produces the flow — connector profile, schedule, load type, whether it is full or incremental, destination prefix — from configuration, so adding a source is a config entry rather than a session in the console. Flows land raw in S3 and nothing interprets them there. Scheduled Glue jobs pick raw up per tier, do the unglamorous work — date extraction, type coercion, Parquet out — and write staging; raw stays under a lifecycle policy for roughly twelve months, so any transform can be replayed against what actually arrived rather than against what we believe arrived. Glue tables over both buckets are Terraform, deliberately apart from the CDK that deploys the provisioner and the transform jobs: the catalogue holds no logic, changes on a different clock, and has no business being coupled to the code that fills it. Athena for engineers, Denodo for everyone else.

The interesting decision. Not making the transform event-driven. Firing on object arrival is the default answer and usually the right one — lower latency, nothing polling, no schedule to keep aligned with upstream. At four hundred flows it inverts. Each flow lands on its own cadence into its own prefix, so arrivals never batch; they come as a long tail of single-object events, each one starting a Glue job to process a fraction of a partition. The cost of that is real and the latency it buys is worth nothing, because nobody downstream reads SAP in the second it lands — they read it in the morning. So the transform runs per tier on a schedule and processes whole partitions. Event-driven was a better default and a worse fit, and telling those two apart is most of the job.

A controlled-deployment feature meant to route flows to the right environment by SAP client id deleted a production flow instead. It was a GB-a-day source, the pipeline reported nothing wrong, and for two to three days everything downstream looked healthy. The failure surfaced as business users noticing their numbers had stopped moving — not as an alert.

Gave the flows a filtered read so a gap can be closed after the fact: re-query from a last_updated watermark and backfill, which is how the missing days were recovered. Environment routing now asserts that a client id belongs to the environment it is being applied to, and a flow missing from config is reported rather than deleted implicitly.