Retiring the spreadsheet a reporting process ran 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 hard to scale for the 100+ business users who depended on its output.

Approach

I 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

100+ business users now pull from generated reporting tables with role-restricted access, and monthly turnaround dropped 30%. Because every layer is parameterized and Landing keeps the raw data, a change in business logic is a config change and a replay, not a rebuild.

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.