Retiring a spreadsheet that 500 people depended on

Problem

Monthly reporting for a pharma client ran on an Excel-based pipeline that someone had to edit by hand. Changing a parameter meant opening a spreadsheet and editing it. The process was slow, hard to audit, and impossible to scale past the 500+ people who depended on its output.

Approach

Owned the migration to a layered data lake on AWS. Raw claims data lands as Parquet and is preserved exactly as delivered, with prior loads archived. Staging applies quality rules and standardization. Mastering builds conformed facts and dimensions. Aggregation calculates business metrics once, so they’re consistent everywhere downstream.

Every layer runs on parameterized PySpark jobs in AWS Glue, sequenced by Step Functions — changing behavior means changing config, not editing code.

We built and validated one layer at a time, spending a week testing and tweaking each before starting the next, with the legacy Excel process untouched until the new reporting tables reconciled against it.

Outcome

500+ business users now pull from generated reporting tables with role-restricted access, and monthly turnaround dropped 30%. The same four-layer pattern was reused on a separate $50M commercial sales product — the architecture became a repeatable asset rather than a one-off.

Technical detail

Source & storage

Source data is IQVIA claims, delivered monthly. Storage is Parquet on S3, organized by layer. Loads are full monthly refreshes with prior loads archived, so any layer can be replayed from raw when transformation logic changes.

Quality & access control

Data quality rules run through a reusable framework built in-house at ZS. Access is restricted by role at the layer level.

Layer architecture

The four-layer scheme — Landing, Staging, Mastering, Aggregation — is a variant of the medallion pattern, with an explicit Mastering layer for dimensional conformance sitting between cleansing and aggregation. Landing preserves raw data exactly as delivered, which is what makes replay possible; without it, a logic change means going back to the source system.