Excelgoodies logo +91 9176633248

LEARN THIS HANDS ON

Azure Data Engineering with Power BI

. Live Online FILLING FAST
View all upcoming batches
Designing a Robust Medallion Architecture on Azure Data Lake for Power BI-Centric Teams

Designing a Robust Medallion Architecture on Azure Data Lake for Power BI-Centric Teams

Most Power BI teams hit the same wall: reports are slow, data refreshes break after every schema change, and nobody is sure which version of a file is the “real” one. You’ve probably got an Azure Data Lake already, but without a clear medallion architecture, it behaves more like a dumping ground than a platform. This guide walks through a concrete way to design a robust medallion architecture on Azure Data Lake, optimised for Power BI, and shows how to move from brittle file-based reporting to a stable, governed setup.


Scenario: Retail Analytics Team Drowning in Files

Imagine a retail analytics team with:

  • Data sources: POS transactions from multiple regions, product master from an ERP, daily inventory snapshots.
  • Current landing: All sources land as CSV files in a single container raw-data in Azure Data Lake Storage Gen2.
  • Reporting: A set of Power BI datasets directly pointing to CSVs via Power Query (Import mode), with scheduled refresh in a shared capacity workspace.

Pain points:

  • Schema changes in POS files regularly break refresh.
  • Analysts copy files into ad-hoc folders to “fix” issues, creating shadow versions.
  • Performance is inconsistent because some queries scan large CSVs with transformations repeated in Power BI.

We’ll redesign this into a medallion architecture (Bronze/Silver/Gold) on Azure Data Lake, with Power BI consuming only curated layers.


Core Design Principles for a Medallion Architecture on ADLS

Before touching folders and pipelines, align on a few principles:

  1. Separation of concerns by layer

    • Bronze: Raw, immutable, schema-on-read. Focus on ingestion reliability.
    • Silver: Clean, conformed, query-ready tables with stable schemas.
    • Gold: Business-specific, denormalised models tuned for analytics.
  2. Storage abstraction

    • Use Azure Data Lake Storage Gen2 with hierarchical namespaces.
    • Keep logical layers via folders and naming, not separate accounts per layer unless there’s a security or compliance reason.
  3. Power BI-centric decisions

    • Optimise file formats, partitioning, and schema stability for Power BI Import and for external tables in Synapse or Fabric warehouses/lakehouses.
    • Push heavy transformations out of Power BI into Silver/Gold layers.
  4. Governance by design

    • Use Microsoft Entra ID (formerly Azure AD) security groups and RBAC to control access per layer.
    • Treat Bronze as restricted, Silver as controlled, Gold as widely consumable.

Step 1: Define the Lake Structure and Naming

For our retail team, we’ll use a single ADLS Gen2 account adl-retail-analytics with hierarchical namespaces enabled.

At the top level:

  • bronze/
  • silver/
  • gold/

Inside each layer, organise by domain and then by dataset:

bronze/
  sales/
    pos/
      year=2026/month=10/day=09/pos_20261009_regionA.csv
  product/
    master/
      snapshot_date=2026-10-09/product_master_20261009.json

silver/
  sales/
    pos/
      year=2026/month=10/pos.parquet
  product/
    master/
      snapshot_date=2026-10-09/product_master.parquet

gold/
  sales/
    pos_daily/
      year=2026/month=10/pos_daily.parquet
  inventory/
    inventory_daily/
      year=2026/month=10/inventory_daily.parquet

Key decisions:

  • Partitioning: Use folder partitioning by date keys that match your reporting grain (e.g., year/month/day for transactional data).
  • File format: Standardise on Parquet for Silver and Gold. Power BI (via Power Query) and engines like Synapse serverless can read Parquet efficiently.
  • Naming: Keep dataset names stable (pos.parquet, product_master.parquet) while partitions change beneath them.

This structure gives the team a predictable path for each dataset and a clear mental model: Bronze is ingestion, Silver is clean tables, Gold is report-ready fact/summary tables.


Step 2: Ingest to Bronze with Minimal Assumptions

Bronze should:

  • Capture data as-is from source systems.
  • Preserve source-level partitioning (e.g., daily files).
  • Avoid complex transformations beyond basic normalisation (encoding, line endings).

Assume the team uses Azure Data Factory (ADF) or Synapse pipelines to land data.

Bronze ingestion pattern

For each source (POS, product master, inventory):

  1. Create linked services to the source (e.g., SFTP, REST, database) and to ADLS.
  2. Create a pipeline that:
    • Reads from source.
    • Writes to bronze/<domain>/<dataset>/<partition>.
    • Uses a dynamic file name including date and source identifier.
  3. Enable fault tolerance:
    • Use retry policies on copy activities.
    • Log failures to a monitoring table or storage folder.

No schema enforcement at Bronze beyond what the copy activity needs to write the file. If the POS vendor adds a column, Bronze still ingests it.

This stabilises ingestion so Power BI never depends directly on source quirks.


Step 3: Build Silver as Clean, Conformed Tables

Silver is where most of the data engineering work happens:

  • Schema enforcement: Define explicit column types and naming.
  • Data quality: Handle nulls, invalid codes, deduplication.
  • Conformance: Align dimensions (e.g., product IDs) across sources.

You can implement Silver using:

  • Spark (Synapse or Fabric)
  • Azure Databricks
  • Dataflows Gen2 / Power Query in Fabric

The choice depends on your platform. For ADLS + Power BI teams without Fabric, Synapse or Databricks is common.

Example: Silver POS table using PySpark (Databricks / Synapse)

Below is a simplified PySpark job that reads Bronze POS CSV files, enforces schema, and writes partitioned Parquet to Silver:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, to_date, lit, current_date
from pyspark.sql.types import StructType, StructField, StringType, IntegerType, DecimalType, DateType

spark = SparkSession.builder.getOrCreate()

bronze_path = "abfss://data@adl-retail-analytics.dfs.core.windows.net/bronze/sales/pos/"
silver_path = "abfss://data@adl-retail-analytics.dfs.core.windows.net/silver/sales/pos/"

# Define enforced schema for POS
pos_schema = StructType([
    StructField("transaction_id", StringType(), False),
    StructField("store_id", StringType(), False),
    StructField("product_id", StringType(), False),
    StructField("quantity", IntegerType(), True),
    StructField("gross_amount", DecimalType(18, 2), True),
    StructField("transaction_date", StringType(), False),  # will be converted
    StructField("region", StringType(), True)
])

# Read all Bronze POS files
bronze_df = spark.read \
    .option("header", "true") \
    .schema(pos_schema) \
    .csv(bronze_path)

# Basic cleaning and type conversions
silver_df = bronze_df \
    .withColumn("transaction_date", to_date(col("transaction_date"), "yyyy-MM-dd")) \
    .withColumn("load_date", current_date())

# Write to Silver as partitioned Parquet
silver_df.write \
    .mode("overwrite") \
    .partitionBy("transaction_date") \
    .parquet(silver_path)

What this does for the team:

  • Stable schema: Power BI can rely on silver/sales/pos having consistent types.
  • Partitioning by transaction_date: Later, Power BI queries can be scoped by date filters, and engines reading this path can prune partitions.

You’d repeat this pattern for product master and inventory, including joins to create conformed dimensions (e.g., ensuring product IDs align across datasets).


Step 4: Shape Gold for Power BI Models

Gold is where you design fact and dimension tables that match how Power BI models are built:

  • Fact tables: Transactional or periodic data (e.g., daily sales, daily inventory).
  • Dimension tables: Product, store, region, calendar.

The goal is to minimise complex joins and heavy calculations inside Power BI.

Example: Gold daily POS fact

Assume we want a daily sales fact table summarised by store, product, and date.

Using PySpark again:

from pyspark.sql.functions import sum as _sum

silver_pos_path = "abfss://data@adl-retail-analytics.dfs.core.windows.net/silver/sales/pos/"
gold_pos_daily_path = "abfss://data@adl-retail-analytics.dfs.core.windows.net/gold/sales/pos_daily/"

silver_pos_df = spark.read.parquet(silver_pos_path)

pos_daily_df = silver_pos_df.groupBy(
    "transaction_date",
    "store_id",
    "product_id",
    "region"
).agg(
    _sum("quantity").alias("total_quantity"),
    _sum("gross_amount").alias("total_gross_amount")
)

pos_daily_df.write \
    .mode("overwrite") \
    .partitionBy("transaction_date") \
    .parquet(gold_pos_daily_path)

Gold tables like pos_daily are what Power BI datasets will consume. They are:

  • Aggregated to a useful grain (daily in this case).
  • Partitioned by date for efficient reads.
  • Free of source-level noise (no raw transaction IDs unless needed).

Step 5: Connect Power BI to Silver/Gold Correctly

Once Bronze/Silver/Gold are in place, you need to connect Power BI in a way that respects the architecture.

Connection options (non-Fabric, ADLS-based)

  1. Power Query (Import mode) directly to ADLS

    • Use the Azure Data Lake Storage Gen2 connector.
    • Point to Gold Parquet files for facts and Silver/Gold Parquet for dimensions.
    • Suitable for small-to-medium datasets where Import mode is acceptable.
  2. Synapse serverless SQL over ADLS

    • Create external tables over Silver/Gold Parquet.
    • Power BI uses the Azure Synapse Analytics connector.
    • Suitable when you want a SQL abstraction layer and centralised governance.

Example: Power Query reading Gold Parquet from ADLS

This example uses the Azure Data Lake Storage Gen2 connector in Power BI Desktop.

let
    Source = AzureDataLakeStorage.DataLake("https://adl-retail-analytics.dfs.core.windows.net"),
    GoldSales = Source{[Name="data"]}[Content],
    PosDailyFolder = GoldSales{[FolderPath="/gold/sales/pos_daily/"]}[Content],
    PosDailyTable = Parquet.Files(PosDailyFolder),
    ExpandedData = PosDailyTable[Data],
    Result = Table.TransformColumnTypes(ExpandedData,{
        {"transaction_date", type date},
        {"store_id", type text},
        {"product_id", type text},
        {"region", type text},
        {"total_quantity", Int64.Type},
        {"total_gross_amount", type number}
    })
in
    Result

What matters here:

  • Power BI is reading Gold, not Bronze.
  • Data types are aligned with the Silver schema and Gold aggregations.
  • Transformations in Power BI are minimal, mostly type enforcement.

For larger setups, you might prefer Synapse serverless with external tables; Power BI then connects via SQL, which is often more familiar to data teams and helps centralise access control.


Step 6: Access Control and Governance Across Layers

Medallion architecture only helps if access is controlled consistently.

  • Bronze: Restricted to data engineering and platform teams.

    • Use Azure RBAC on the storage account/container.
    • Assign Storage Blob Data Contributor to a small set of technical groups.
  • Silver: Accessible to data engineers and BI developers.

    • Fine-grained permissions per folder or container via Access Control Lists (ACLs) on ADLS Gen2.
  • Gold: Accessible to broader analytics teams and Power BI service principals.

    • Grant read-only access to Gold folders for Power BI service principals used for scheduled refresh.

This prevents analysts from accidentally pointing reports at Bronze and bypassing the curated layers.


Step 7: Operationalising and Handling Change

Once the architecture is live, the retail team’s biggest ongoing challenge is schema change and data volume growth.

Handling schema changes

  • Bronze absorbs changes: new columns appear in raw files without breaking ingestion.

  • Silver jobs should:

    • Explicitly select and cast columns.
    • Handle missing columns with default values or flags.
    • Fail fast with clear logging when a breaking change occurs (e.g., type change from string to complex JSON).
  • Gold jobs depend on Silver and should be updated only after Silver is stable.

Monitoring

  • Use Azure Monitor and pipeline logs to track ingestion success/failure.
  • Add data quality checks in Silver (row counts, null rate thresholds) and log metrics.

Performance considerations for Power BI

For Power BI Import mode consuming Gold:

  • Limit fact table size by selecting appropriate grain (daily vs transaction-level) and filtering historical data if necessary.
  • Use incremental refresh in Power BI where applicable, with partitions aligned to date columns present in Gold tables.

Having Gold partitioned by date makes it easier to design incremental refresh policies that align with the underlying data layout.


Before and After: The Retail Team’s Experience

Before:

  • Reports connected directly to CSVs in raw-data.
  • Every POS schema change broke multiple Power BI refreshes.
  • Performance varied wildly due to heavy transformations in Power Query.

After:

  • Bronze handles source volatility; ingestion rarely breaks.
  • Silver provides stable, typed tables; schema changes are managed centrally.
  • Gold offers report-ready tables at the right grain; Power BI refresh is faster and more predictable.
  • Analysts no longer copy files around; they know to request changes to Silver/Gold if they need new fields.

Practical Takeaway

If your Power BI reports still point at raw CSVs in Azure Data Lake, pick one critical dataset and move it into a Bronze–Silver–Gold pattern first; once you see the stability and refresh improvements from that single change, you’ll have a concrete blueprint to extend the medallion architecture across the rest of your lake.

Editor's Note

This article reflects a shift from ad-hoc file-based reporting to a structured medallion architecture on Azure Data Lake, particularly relevant for Power BI-centric teams that need predictable refresh behaviour and governed data layers.

Professionals who want to apply these patterns to their own data can explore Excelgoodies' Data Engineering & BI Azure (On Cloud) programme - taught live by instructors, with certification awarded once a real project is running at work.

Insights compiled through ongoing industry research and discussions within the Excelgoodies Analytics Community.

Data Engineering

New

Next Batches Now Live

Power BIPower BI
SQLSQL
Power AppsPower Apps
Power AutomatePower Automate
Microsoft FabricMicrosoft Fabrics
AzureAzure Data Engineering
Explore Dates & Reserve Your Spot → Reserve Your Spot →