top of page

Data Warehouse and ELT Design Case

Case Introduction

  • I'm an Analytics Engineer at Flipflop.

  • My main responsibility is to design and implement the enterprise data warehouse (EDW) to support marketing and product analytics use cases across regions.

  • The project includes designing the ETL pipeline, building data marts for sales and product domains to enable fast and flexible querying for BI tools. 

EDW structure

Skærmbillede 2025-11-18 kl. 23.49.57.png

EDW: Staging

ExtractSource Layer:

  • Contains raw extracted data from ERP database

  • Serve as staging area before transformation

  • Includes tables like:

    • ExtractSource.Customer,

    • ExtractSource.Product,

    • ExtractSource.SalesTransactions

​

EDW: Dim/Fact

Dimension Layer (Dim):

  • Contains conformed dimensions like Dim.Customer and Dim.Product

  • Implements slowly changing dimension patterns

  • Provides descriptive attributes for analysis

​

Fact Layer (Fact):

  • Contains measurable business events (Fact.SalesTransactions)

  • Stores quantitative data with dimensional references

  • Designed for aggregation and analysis

​

Skærmbillede 2025-11-19 kl. 00.00.13.png

Stored Procedure:

Extract, transform, and load data from source tables (ExtractSource) into target dimensional/fact tables (Dim, Fact).

Skærmbillede 2025-11-19 kl. 00.04.03.png

Data Mart Layers (dmDim, dmFact):

Domain-specific dimensional models optimized for business unit analytics (e.g., marketing, sales teams).

  • DmDim (dmView.Customer, dmView.Product)

    • ​Created dimensional views derived from core Dim tables, optimized for specific business domains

  • DmFact (dmFact.SalesTransactions):

    • Pre-aggregated fact tables or views built from Fact tables for analytical use cases

​

Skærmbillede 2025-11-19 kl. 00.09.02.png

Pipeline Overview

Schedule and execute the stored procedures in the correct order

  • Extract Source tables

    • Extract raw data from source systems into the staging layer (ExtractSource tables)​

  • Load_DimCustomer

    • Transforms and loads customer data from ExtractSource.Customer to Dim.Customer using SCD1 updates​

  • Load-DimProduct

    • Processes product data from ExtractSource.Product to Dim.Product using SCD2 updates​

  • Load-FactSalesTransactions

    • Performs incremental loading of sales transactions into Fact.SalesTransactions using the IncrementalLoad metadata table to track and process only new records.​

  • Load_Master

    • Orchestrates the complete workflow:​

      • Extract --> 2) Load dimensions --> 3) Load facts --> 4) Refresh Po​wer BI model

​

Master Pipeline Flow

  • The Master Package automates the complete ETL workflow: extracts data from sources to ExtractSource, processes dimension and fact tables, and finally refreshed the Power BI semantic model

  • Executes in strict sequence: loads dimensions (Dim.Customer/Dim.Product) first, then facts (Fact.SalesTransactions)

  • Ensures EDW data integrity with atomic transactions and SCD handling and keeps Power BI reports up-to-date

​​

​

Skærmbillede 2025-11-19 kl. 00.22.52.png
bottom of page