A Lakehouse for Consistent Net Sales Reporting

How a globally operating pharma company (API producer) consolidated its Net Sales reporting with an Azure Databricks lakehouse – including centralized KPI definitions and a controlled Excel upload process.

The goal was to bring Net Sales KPIs, budget and forecast data, and other key control metrics together on a single platform.

The result: faster month-end closes, fewer discussions about the numbers, and a data foundation that supports reliable analysis across countries, business units, and sales structures.

As an internationally operating development and manufacturing partner for leading pharma and biotech companies, this kind of international reality can make steering and reporting genuinely challenging. When business units (BUs), country organizations, sales teams, and markets all work in parallel, data quickly ends up in different structures, definitions, and levels of granularity. At the same time, management, finance/controlling, and BU and sales leadership expect reliable numbers every month: Net Sales at group and BU level, budget-vs-actual, forecast status, variance analyses, and of course the ability to quickly track down errors when something doesn't add up.

In a recent project, we therefore built a standardized, DWH-based reporting solution that delivers Net Sales KPIs and central control metrics (including CAPEX, FTE, and budget/planning figures) consistently. The goal wasn't simply to build another dashboard, but a platform that processes monthly data quickly, keeps the structure of the overall process more stable and secure, and measurably speeds up decision-making.

Why we chose Azure Databricks

The central architectural decision was a lakehouse approach built on Azure Databricks.

Not for trend reasons, but because Databricks brings data ingestion, transformation, and provisioning together in a single scalable stack, while also integrating cleanly into the Azure ecosystem — for security, identity, and monitoring alike.

Especially for reporting platforms that keep evolving — new KPIs, additional sources, further dimensions — this combination is decisive: scalable, controllable, and without extensions turning into a collection of one-off solutions.

Technically, the solution is built on a modern Databricks lakehouse architecture

– Delta Lake for reliable, transactional tables in the data lake

– A medallion architecture with clearly defined layers (Bronze, Silver, Gold)

– Unity Catalog for governance and role-based access

– Databricks Jobs/Workflows for orchestration, including logging and error handling

– Power BI as the front end via the Databricks SQL Warehouse

The critical point: KPI logic doesn't belong scattered across multiple Power BI files, but centralized where data quality, governance, and traceability can actually be controlled — in the lakehouse's Gold layer.

The setup: Bronze, Silver, Gold – and why this separation helps

The medallion architecture isn't a theoretical architecture model, but a very practical answer to typical data problems: manual Excel sources, differing definitions, changing data deliveries, or missing identifiers.

Bronze: raw data with full traceability

Excel files are uploaded monthly by business users via a Streamlit app. The application enforces a controlled upload process, including metadata (such as period and source), and stores the original files in a standardized way in a Databricks Volume as a landing zone.

From there, the files are loaded into the Bronze layer as raw Delta data, with clear traceability back to the source file. The original files are fully preserved throughout. That may sound unremarkable, but it becomes critical later on when you need to trace, say, why a number has changed.

Silver: cleansing, typing, and quality rules

In the Silver layer, the data is cleansed and made operational: typing, standardization, mapping logic, and harmonization. Validations and quality rules also apply here, to catch discrepancies early and handle them in a traceable way.

These include, for example:

– Schema checks

– Mandatory field validation

– Duplicate detection logic

– Reference checks against dimension tables

– Period consistency checks

This step is indispensable, especially for Excel-based sources.

Gold: business-ready tables and KPIs

The Gold layer produces curated tables and views that serve as a stable foundation for reporting and analysis. This is where the business definitions of key metrics — such as Net Sales and other control KPIs — are implemented in a standardized way. Power BI accesses these structures directly and builds the semantic model and visualizations on top of them. As a result, analysis at group, BU, and country level is based on one shared, consistent data foundation. Power BI (Power Query / M code) accesses the Databricks SQL Warehouse via Catalog → Schema → Table.

Excel upload without chaos: Streamlit as a controlled gateway

As described above, many reporting projects fail less because of complex data platforms and more because of seemingly small things — Excel files with different templates, manual adjustments, or missing metadata.

That's why the upload process was deliberately designed as part of the solution.

The Streamlit app provides:

– a simple, guided upload process for business users

– standardized storage in the Databricks Volume

– mandatory metadata (period, source) and testing of data fields for missing data or quality issues, so that loading and historization work cleanly

– clear traceability of which file populated which tables

This significantly reduces later discussions about "which file was the right one?" while also stabilizing the entire loading process.

Orchestration: notebooks for logic, workflows for operations

Data processing happens in Python and SQL notebooks, orchestrated via Databricks Workflows (Jobs). This combination deliberately separates development from operations: notebooks are used for implementing and evolving the transformation logic, while workflows handle scheduling, dependencies between processing steps, and monitoring and error handling. Especially for periodic loading processes, such as monthly data deliveries, what matters most — beyond pure processing — is that errors and load states are reproducible. A stable data foundation and a traceable loading process are therefore key prerequisites for reliable reporting and consistent KPIs.

What the platform made possible

The original focus was on Net Sales reporting at group and BU level, but the real value becomes apparent once you can analyze and evaluate the data across multiple dimensions.

Multi-dimensional market analysis

Analysis across:

– Geographies (countries and regions)

– Sales organizations

– Sales teams

– Business units

This makes it possible to answer questions that previously often required significant manual effort: Which regions are truly growing? Which organization is delivering what performance? Where are the opportunities, and where are the risks emerging?

Integrated planning and forecasting

Actuals, budget, and multiple forecast versions are brought together in a single structure. This makes variance analyses and budget-vs-actual comparisons consistent and historizable — including clean performance tracking over time.

Global standardization

Unified country and region hierarchies ensure that analyses remain comparable internationally, without every unit bringing its own definitions.

Price and margin transparency

Alongside gross and net sales, price components and deductions are also taken into account (such as logistics, bonuses, or discounts). This makes it possible to analyze margin drivers, discount effects, and pricing logic far more effectively.

Typical challenges – and how they were solved in a structured way

The complexity of platforms like this rarely comes from dashboards — it comes from real sales and organizational structures.

In this project, the following aspects played a key role, among others:

– Customer segments and hierarchies (direct customers vs. distributors)

– Multi-currency transactions and FX effects

– Delivery terms (Incoterms) as cost and risk drivers

– Consistent business unit assignments

– Heterogeneous data sources and Excel origins

The decisive factor here was a clearly defined validation and quality approach in the Silver layer, complemented by mapping logic and structured correction processes. This ensures that data issues become visible early — rather than being discovered only in management reporting.

Operations, cost, and governance

One practical advantage of the platform is its operating model: compute is used only on demand (pay-as-you-go), rather than keeping resources provisioned permanently.

The medallion structure also reduces rework, since raw data, cleansed data, and curated KPIs are already clearly separated. Governance is handled via Unity Catalog with role-based access: Bronze and Silver are more tightly restricted, while the Gold layer is more broadly accessible for reporting purposes. Together with the original files, load logs, and metadata, this creates robust traceability — an important foundation for numbers that aren't just available, but actually trusted.

Conclusion

The platform doesn't just deliver reports — it delivers a stable foundation for decision-making: scalable, traceable, and consistent in its KPI logic.

In everyday use, the biggest impact shows up in two areas above all: monthly data is available automatically in reporting right after upload, and international structures can be analyzed transparently. As a result, reporting becomes less of a monthly obligation and more of a tool for market steering, pricing decisions, and reliable forecasts.