Data Lakehouse Solution using Databricks for Go-Pak

Each site at Go-Pak used separate source systems to store their sales data, so they wanted to improve cross-company visibility of this data by creating a consolidated dataset. Go-Pak were looking for a robust solution. This meant improvements needed to be made in data load efficiency and data modelling to achieve enhanced load times, faster query speeds, and effective storage of data changes.

To meet Go-Pak’s requirements, DSP used Power BI and Databricks on Azure. In Databricks, a medallion architecture was leveraged to process the raw data from the source systems into a structured model suitable for reporting. This architecture consists of the following layers:

• Bronze layer: Ingest raw data from each site’s source systems using incremental load for loading efficiency.
  
  
• Silver layer: Consolidate and deduplicate the sales data from each source system into a single table.
  
  
• Gold layer: Model the data for reporting using aggregation and implementing Slowly Changing Dimensions (SCD) type 2 to store data changes.
  
  
• Platinum layer: Materialise gold tables as views with only necessary reporting fields selected, and row-level security applied.

A daily Databricks job was configured to process the sales data through these layers, ensuring the data remained up-to-date. Furthermore, separate development, testing, and production environments were set up in Databricks with the principle of least privilege (PoLP) implemented for security.