SAP Data Migration and Profiling Platform

What it was

Cognitus is an SAP consultancy. Its clients were large enterprises coming off legacy ERPs, mainframe extracts, ODBC databases, and, more often than anyone wants to admit, spreadsheets. The platform was the machinery that moved that data into SAP: extract from whatever the legacy source was, profile it, clean it, transform it, validate it, load it, and show a client project manager where their migration actually stood.

What made it hard

Migration is not a service you operate, it is an event you survive. The cutover happens over a weekend, the rollback window is measured in hours, and after Monday morning the business is running on whatever you loaded. Bad data does not throw an exception. It lands quietly and surfaces three months later in a quarterly report, at which point nobody can tell you which of forty transformation rules produced it.

The volumes made that worse rather than better. 30 TB+ across the client book, 50M+ records a day passing the profiling gate, 2M+ records an hour through the ETL path. And the legacy sources were fragile: several clients could only give us a read window one night a week, so an extract you had to re-run was an extract that cost you a week.

What I owned

I led the core ETL and migration microservices and architected the Kafka-based asynchronous pipeline that connects them. I built the transformation rule engine, the connector framework for legacy sources, and the profiling and cleansing services in Python and PySpark, including the statistical profiling, outlier detection, and the column-level and table-level quality scoring that gave clients a go/no-go signal. I also built the React analytics dashboard on top of it. What I did not own was the SAP side of the problem: the functional consultants decided what the target model meant, and I built the machinery that got data into it.

The decision I would defend

I staged every extracted record in MongoDB before transformation rather than streaming source to transform to target in one pass.

This is not free. It roughly doubles storage on a 30 TB workload, adds a network hop, and means a full run touches disk twice. What it bought was the thing that mattered more: transformations became replayable without going back to the source. When a rule was wrong, and rules were often wrong on the first pass, we re-ran against the staged copy in minutes instead of waiting for the client’s next read window. It also let profiling run over the staged data without competing with extraction for the source system’s limited connection pool, which is what made a 50M-record-a-day gate possible at all.

Where it landed

30 TB+ of client data migrated across 15+ enterprise clients. The profiling gate cleared 50M+ records daily; the ETL path sustained 2M+ records per hour. The dashboard put 200+ real-time KPIs in front of client teams who had previously been getting a status spreadsheet on Fridays, and the schema-mapping engine moved enough rule authoring to business analysts to roughly halve custom-code requests hitting engineering.

What I would do differently

The rule engine let analysts write transformation logic as JavaScript functions. That is why the custom-code queue emptied, and I still think handing rule authoring to the people who understand the data was right. But it also put untyped, untested, user-authored code directly in the critical path of a cutover, with no meaningful versioning and no way to diff what changed between a dry run and the real one. I would constrain that surface to a sandboxed, non-Turing-complete expression language with a written spec and a conformance suite behind it. I care about this enough that I have since gone and built exactly that as an open-source project.

I would also revisit MongoDB as the staging store. Document-per-record was genuinely convenient for schemaless legacy input, and it let us stage data before we understood its shape. We then paid for that convenience in every single aggregate profiling query for the next year.