Data Transformation

↑ Back to top

5.1 Bronze Layer

5.1.1 What is the Bronze Layer?

The Bronze Layer is the first storage layer in the Medallion Architecture.

Its primary purpose is to store data from source systems in a raw or near-raw form, while preserving the original information as much as practical.

Source Systems
     │
     ▼
   Ingestion
     │
     ▼
┌──────────────┐
│    BRONZE    │
│  Raw Data    │
└──────────────┘
     │
     ▼

Silver

In Microsoft Fabric, Bronze data is commonly stored as Delta tables in a Lakehouse.

5.1.2 Why Do We Need a Bronze Layer?

Imagine you directly transform data from SQL Server into a final reporting table.

SQL Server
    │
    ▼
Transform
    │
    ▼

Gold

If something goes wrong, you may have difficulty determining:

What did the source originally contain?

Which records changed?

Which transformation caused the problem?

Can we reprocess the data?

Can we reproduce yesterday's result?

The Bronze layer provides a persistent landing area.

Source
   │
   ▼
Bronze ← Original/near-original data
   │
   ▼
Silver ← Cleaned data
   │
   ▼

Gold ← Business data

5.1.3 Bronze Layer Characteristics

A good Bronze layer generally has these characteristics:

The Bronze layer should not become a dumping ground for uncontrolled data. You still need basic ingestion validation and operational metadata.

5.1.4 Sources Feeding Bronze

Bronze can receive data from many sources:

SQL Server

Oracle

Azure SQL

REST APIs

Azure Blob Storage

ADLS Gen2

CSV

JSON

SaaS applications

Streaming sources

Example:

              Source Systems
                    │
       ┌────────────┼────────────┐
       ▼            ▼            ▼
   SQL Server    REST API      ADLS
       │            │            │
       └────────────┼────────────┘
                    ▼

Bronze

5.1.5 Bronze in Microsoft Fabric

A typical Fabric Lakehouse might look conceptually like:

SalesLakehouse
│
├── Files
│
└── Tables
    │
    ├── Bronze_Customers
    ├── Bronze_Products
    ├── Bronze_Orders
    └── Bronze_OrderItems

You could alternatively use separate Lakehouses for different layers depending on your organization's architecture.

For example:

Bronze Lakehouse
      │
      ▼
Silver Lakehouse
      │
      ▼

Gold Lakehouse

The choice depends on security, governance, workload isolation, and operational requirements.

5.1.6 Bronze vs Silver vs Gold

Example:

Bronze

CustomerID | Name | Country

101 | " John " | india

102 | NULL | INDIA

101 | " John " | india

Silver

CustomerID | Name | Country

101 | John | INDIA

102 | Unknown | INDIA

Gold

Country | TotalCustomers | TotalSales

INDIA | 50000 | 250000000

USA | 30000 | 180000000

5.1.7 What Transformations Should Happen in Bronze?

The general rule is:

Keep Bronze transformations minimal.

However, some technical transformations are appropriate.

For example:

Add ingestion timestamp.

Add source system.

Add batch ID.

Add source file name.

Apply basic schema handling.

Standardize technical metadata.

Reject completely unreadable records.

Avoid applying complex business logic at this stage.

5.1.8 Audit Columns

Audit metadata is extremely useful.

Example:

from pyspark.sql.functions import (
    current_timestamp,
    lit,
    input_file_name

)

df = df \
    .withColumn(
        "LoadTimestamp",
        current_timestamp()
    ) \
    .withColumn(
        "SourceSystem",
        lit("SQL_SERVER")
    ) \
    .withColumn(
        "SourceFile",
        input_file_name()
    )

The Bronze table might contain:

5.1.9 Batch ID

A Batch ID helps identify which ingestion run produced a record.

For example:

BATCH_20260822_001

Add it:

from pyspark.sql.functions import lit

batch_id = "BATCH_20260822_001"

df = df.withColumn(
    "BatchID",
    lit(batch_id)

)

This is useful when investigating ingestion failures.

5.1.10 Source File Tracking

For file-based ingestion:

from pyspark.sql.functions import input_file_name

df = df.withColumn(
    "SourceFile",
    input_file_name()

)

Example:

SourceFile
-----------------------------------------

Files/Raw/Sales_20260822.csv

This allows you to answer:

Which source file produced this record?

5.1.11 Reading CSV into Bronze

Suppose:

Files/Raw/Sales/Sales.csv

Read it:

df = spark.read \
    .option("header", "true") \
    .option("inferSchema", "true") \
    .csv("Files/Raw/Sales/Sales.csv")

Add metadata:

from pyspark.sql.functions import (
    current_timestamp,
    input_file_name,
    lit

)

df = df \
    .withColumn(
        "LoadTimestamp",
        current_timestamp()
    ) \
    .withColumn(
        "SourceFile",
        input_file_name()
    ) \
    .withColumn(
        "SourceSystem",
        lit("CSV")
    )

Write Bronze:

df.write \
    .format("delta") \
    .mode("append") \
    .saveAsTable("Bronze_Sales")

5.1.12 Why Append is Common in Bronze

Bronze often acts as a historical landing layer.

For example:

August 20 → 100,000 records

August 21 → 105,000 records

August 22 → 110,000 records

Using:

.mode("append")

allows new batches to be added.

Conceptually:

Bronze_Sales
│
├── Batch 001
├── Batch 002
├── Batch 003
└── Batch 004

However, append alone does not guarantee that rerunning a batch won't create duplicates. Production ingestion needs batch/file-level idempotency controls.

5.1.13 Bronze Idempotency

An idempotent pipeline can be rerun without producing unintended duplicates.

Suppose:

Sales_20260822.csv

was successfully loaded.

Then the pipeline accidentally runs again.

Without controls:

First run → 100,000 records

Second run → +100,000 records

You now have:

200,000 records

when you expected:

100,000 records

A production Bronze process should track something like:

SourceFile

BatchID

LoadStatus

LoadTimestamp

and prevent accidental duplicate ingestion.

5.1.14 Bronze Processing Pattern

A stronger design is:

                Source File
                    │
                    ▼
              Check File/Batch
                    │

Already Loaded?

/ \

             Yes         No
              │           │
              ▼           ▼
           Skip          Read
                          │
                          ▼
                    Add Metadata
                          │
                          ▼

Bronze Delta

5.1.15 Bronze from SQL Server

Suppose Fabric Data Factory in Fabric or another ingestion process extracts SQL Server data.

The pipeline could be:

SQL Server
    │
    ▼
Copy Activity
    │
    ▼
Lakehouse
    │
    ▼

Bronze_Sales

The Bronze table should represent the source data with minimal transformation.

5.1.16 Bronze from REST API

Example API response:

{

"id": 101,

"name": "John",

"country": "India",

"amount": 2500

}

The ingestion process can land the data into Bronze.

REST API
   │
   ▼
Data Factory / Notebook
   │
   ▼

Bronze Delta

Later:

Bronze

↓

Parse

↓

Clean

↓

Silver

5.1.17 Bronze from Azure Blob

Suppose:

Blob Storage
│
└── sales/
    ├── sales_20260820.csv
    ├── sales_20260821.csv
    └── sales_20260822.csv

The ingestion pipeline reads the files:

Blob
 │
 ▼
Fabric Data Factory
 │
 ▼
Lakehouse
 │
 ▼

Bronze_Sales

Source file metadata can be retained.

5.1.18 Bronze Table Design

Example:

Bronze_Sales

Business columns:

SaleID

CustomerID

ProductID

OrderDate

Amount

Technical columns:

SourceSystem

SourceFile

BatchID

LoadTimestamp

Example:

5.1.19 Should We Clean Bronze Data?

Usually:

No heavy cleansing.

For example, if source contains:

" John "

you may preserve it in Bronze.

Silver can perform:

trim(col("CustomerName"))

Similarly:

india

INDIA

India

can be standardized in Silver.

This preserves the source state for troubleshooting.

5.1.20 Bronze → Silver

The next stage is:

Bronze
   │
   ▼
Read Bronze Delta
   │
   ▼
Clean
   │
   ├── Trim
   ├── Deduplicate
   ├── Validate
   ├── Cast
   └── Standardize
   │
   ▼

Silver Delta

Example:

bronze_df = spark.table(
    "Bronze_Sales"

)

silver_df = bronze_df \

    .withColumn(
        "CustomerName",
        trim(col("CustomerName"))
    ) \
    .withColumn(
        "Country",
        upper(col("Country"))
    )

5.1.21 Bronze Layer and Data Lineage

Bronze improves traceability.

Suppose a Gold dashboard shows:

Total Sales = ₹250 Crore

but the number looks incorrect.

You can trace:

Gold

↓

Silver

↓

Bronze

↓

Source

This is an important advantage of the Medallion architecture.

5.1.22 Bronze Layer and Reprocessing

Suppose business rules change.

Old logic:

Amount > 0

New logic:

Amount >= 100

If raw data is preserved in Bronze:

Bronze
   │
   ├── Old Silver
   │
   └── New Silver

You can reprocess Bronze using the new rules.

Without Bronze:

Source
   │
   ▼
Old Transformation
   │
   ▼

Silver

you may need to request the source data again.

5.1.23 Bronze Layer Storage

Delta is commonly used:

df.write \
    .format("delta") \
    .mode("append") \
    .saveAsTable("Bronze_Sales")

Why Delta?

Transactional writes

Schema management

Reliable storage

Version history

Efficient reads

Support for MERGE

Support for incremental processing

5.1.24 Bronze Data Retention

Bronze data can grow very quickly.

For example:

Daily ingestion = 100 GB

1 month ≈ 3 TB

1 year ≈ 36 TB

Therefore, retention policies should be designed according to:

Business requirements

Audit requirements

Compliance

Storage cost

Reprocessing needs

Don't keep unlimited raw data without a retention strategy.

5.1.25 Bronze Security

Bronze may contain sensitive source information.

Therefore consider:

Access control

Workspace permissions

Lakehouse permissions

Row/column-level security where appropriate

Data masking

Encryption

Governance policies

Not every user should automatically have access to raw Bronze data.

5.1.26 Bronze vs Raw Files

These are not necessarily the same thing.

Raw Files
   │
   ▼

Bronze Delta

Raw files may be the original landing format:

CSV

JSON

Parquet

Bronze can be a structured Delta representation used for downstream processing.

Example:

Files/
└── Raw/
    └── Sales.csv
Tables/
└── Bronze_Sales

5.1.27 Complete Bronze Notebook

from pyspark.sql.functions import (
    current_timestamp,
    input_file_name,
    lit

)

# -----------------------------------------
# 1. Source
# -----------------------------------------

source_path = "Files/Raw/Sales/Sales.csv"

# -----------------------------------------
# 2. Read raw data
# -----------------------------------------

df = spark.read \
    .option("header", "true") \
    .option("inferSchema", "true") \
    .csv(source_path)

# -----------------------------------------
# 3. Add technical metadata
# -----------------------------------------

df = df \
    .withColumn(
        "SourceFile",
        input_file_name()
    ) \
    .withColumn(
        "SourceSystem",
        lit("CSV")
    ) \
    .withColumn(
        "LoadTimestamp",
        current_timestamp()
    ) \
    .withColumn(
        "BatchID",
        lit("BATCH_20260822_001")
    )

# -----------------------------------------
# 4. Write Bronze Delta
# -----------------------------------------

df.write \
    .format("delta") \
    .mode("append") \
    .saveAsTable("Bronze_Sales")

# -----------------------------------------
# 5. Validate
# -----------------------------------------

bronze_df = spark.table(
    "Bronze_Sales"

)

print(
    "Bronze records:",
    bronze_df.count()

)

display(bronze_df)

5.1.28 Bronze Architecture Example

For a complete sales project:

                     SOURCES
                        │
          ┌─────────────┼─────────────┐
          ▼             ▼             ▼
      SQL Server     REST API      Azure Blob
          │             │             │
          └─────────────┼─────────────┘
                        ▼
                Fabric Data Factory
                        │
                        ▼
                 ┌─────────────┐
                 │   BRONZE    │
                 │             │
                 │ Sales       │
                 │ Customers   │
                 │ Products    │
                 │ Orders      │
                 └──────┬──────┘
                        │
                        ▼
                 Spark Notebook
                        │

Cleansing

Validation

                 Deduplication
                        │
                        ▼
                 ┌─────────────┐
                 │   SILVER    │
                 └──────┬──────┘
                        │
                        ▼
                 Business Logic
                        │
                        ▼
                 ┌─────────────┐
                 │    GOLD     │
                 └──────┬──────┘
                        │
                        ▼

Power BI

5.1.29 Production Best Practices

1. Preserve source information

Avoid unnecessary transformations.

2. Add ingestion metadata

At minimum consider:

SourceSystem

SourceFile

BatchID

LoadTimestamp

3. Make ingestion idempotent

Prevent accidental duplicate batches.

4. Use Delta

Use Delta tables as the primary Bronze storage format where appropriate.

5. Validate ingestion

Check:

Record count

Schema

File availability

Load status

6. Maintain lineage

Track where records originated.

7. Plan retention

Bronze data can become very large.

8. Don't put business logic in Bronze

Business transformations belong primarily in Silver/Gold.

5.1.30 Interview Questions

What is the Bronze layer?

The Bronze layer is the raw or near-raw data layer in a Medallion architecture. It preserves source data for downstream processing, auditing, and reprocessing.

Why do we need Bronze?

It provides:

Source preservation

Traceability

Reprocessing capability

Auditability

Historical data

Should Bronze data be cleaned?

Generally, heavy cleansing should not happen in Bronze. Bronze should preserve the source as much as practical.

What format is commonly used for Bronze in Fabric?

Delta Lake is commonly used for structured Bronze tables in a Fabric Lakehouse.

What metadata should be added?

Common technical metadata includes:

SourceSystem

SourceFile

BatchID

LoadTimestamp

Should Bronze use append or overwrite?

Often append is used for historical ingestion, but the correct mode depends on the source and ingestion pattern. Idempotency must still be handled.

What is the difference between Bronze and Silver?

Bronze → Raw / Near Raw

Silver → Cleaned / Validated / Standardized

Why preserve raw data?

It allows you to reproduce, audit, troubleshoot, and reprocess downstream datasets.

5.1.31 Key Takeaway

Think of Bronze as your trusted landing and preservation layer:

                 SOURCE
                    │
                    ▼
                INGESTION
                    │
                    ▼
            ┌──────────────┐
            │    BRONZE    │
            │              │
            │ Raw Data     │
            │ + Metadata   │
            └──────┬───────┘
                   │
                   ▼
                SILVER
                   │
                   ▼

GOLD

Golden Rule

Bronze should preserve what arrived, Silver should make it trustworthy, and Gold should make it useful for the business.

For a Fabric data engineer, the Bronze layer is the foundation that makes the rest of the Medallion architecture reliable, traceable, and reprocessable.

↑ Back to top

↑ Back to top

5.2 Silver Layer

5.2.1 What is the Silver Layer?

The Silver Layer is the cleaned, validated, standardized, and integrated layer of the Medallion Architecture.

It sits between Bronze and Gold:

Source Systems
      │
      ▼
   Ingestion
      │
      ▼
┌──────────────┐
│    BRONZE    │
│ Raw / Near   │
│ Raw Data     │
└──────┬───────┘
       │
       ▼

Data Cleansing

Validation

Deduplication

Standardization

 Joins
      │
      ▼
┌──────────────┐
│    SILVER    │
│ Clean &      │
│ Trusted Data │
└──────┬───────┘
       │
       ▼

Business Logic

 Aggregations
      │
      ▼
┌──────────────┐
│     GOLD     │
│ Business     │
│ Ready Data   │
└──────────────┘

The key idea is:

Bronze preserves the source; Silver makes the data trustworthy.

5.2.2 Why Do We Need a Silver Layer?

Raw data often contains:

NULL values

Duplicate records

Incorrect data types

Extra spaces

Inconsistent capitalization

Invalid dates

Invalid business values

Duplicate business keys

Different formats across source systems

For example, Bronze might contain:

CustomerID | CustomerName | Country | Amount

101 | " John " | india | "2,500"

102 | NULL | INDIA | "3,200"

101 | " John " | india | "2,500"

103 | "David" | UK | "-500"

Silver can produce:

CustomerID | CustomerName | Country | Amount

101 | John | INDIA | 2500

102 | Unknown | INDIA | 3200

103 | David | UK | -500

Depending on the business rule, the negative amount may be rejected or sent to quarantine.

5.2.3 Responsibilities of Silver

The Silver layer commonly performs:

Bronze
  │
  ├── Data type conversion
  ├── NULL handling
  ├── Duplicate removal
  ├── Text standardization
  ├── Date standardization
  ├── Data validation
  ├── Business-key validation
  ├── Basic joins
  ├── Record matching
  ├── Incremental MERGE
  └── Technical metadata
  │
  ▼

Silver

5.2.4 Bronze vs Silver

5.2.5 Example Architecture

Suppose your company has:

SQL Server
   │
   ▼
Bronze_Sales
   │
   ▼
Silver_Sales
   │
   ▼

Gold_Sales

You might have:

Bronze
├── Bronze_Customers
├── Bronze_Products
├── Bronze_Orders
└── Bronze_OrderItems
Silver
├── Silver_Customers
├── Silver_Products
├── Silver_Orders
└── Silver_OrderItems
Gold
├── Gold_DailySales
├── Gold_CustomerRevenue
└── Gold_ProductPerformance

5.2.6 Reading Bronze Data

Suppose the Bronze table is:

Bronze_Sales

Read it:

bronze_df = spark.table(
    "Bronze_Sales"

)

Inspect:

display(bronze_df)

Schema:

bronze_df.printSchema()

5.2.7 Data Type Standardization

Bronze may contain:

Amount: string

Silver should contain:

Amount: double

Use:

from pyspark.sql.functions import col

silver_df = bronze_df.withColumn(

"Amount",

col("Amount").cast("double")

)

For IDs:

silver_df = silver_df.withColumn(

"CustomerID",

col("CustomerID").cast("long")

)

5.2.8 Trim Text

Bronze:

" John Kumar "

Silver:

"John Kumar"

Code:

from pyspark.sql.functions import trim

silver_df = silver_df.withColumn(

"CustomerName",

trim(col("CustomerName"))

)

5.2.9 Standardize Case

Bronze:

India

india

INDIA

Silver:

INDIA

Code:

from pyspark.sql.functions import upper

silver_df = silver_df.withColumn(

"Country",

upper(trim(col("Country")))

)

5.2.10 Handle NULL Values

Check:

silver_df.filter(

col("CustomerID").isNull()

).show()

For optional attributes:

silver_df = silver_df.fillna({

"CustomerName": "Unknown",

"Country": "UNKNOWN"

})

For required keys, it may be better to quarantine the record:

invalid_df = silver_df.filter(

col("CustomerID").isNull()

)

valid_df = silver_df.filter(

col("CustomerID").isNotNull()

)

5.2.11 Remove Duplicates

Suppose Bronze contains:

CustomerID | CustomerName

101 | John

101 | John

102 | Anita

Silver:

silver_df = silver_df.dropDuplicates(

["CustomerID"]

)

Result:

101 | John

102 | Anita

5.2.12 Latest Record Deduplication

Simple dropDuplicates() is not always enough.

Suppose:

CustomerID | City | UpdatedDate

101 | Hyderabad | 2026-08-20

101 | Bengaluru | 2026-08-22

We want the latest record.

Use a window:

from pyspark.sql.window import Window
from pyspark.sql.functions import (
    row_number

)

window_spec = Window \

.partitionBy("CustomerID") \

.orderBy(

col("UpdatedDate").desc()

)

silver_df = silver_df \

    .withColumn(
        "rn",
        row_number().over(window_spec)
    ) \
    .filter(
        col("rn") == 1
    ) \
    .drop("rn")

Result:

101 | Bengaluru | 2026-08-22

This is a very common Silver-layer pattern.

5.2.13 Date Standardization

Bronze:

22/08/2026

Silver:

2026-08-22

Use:

from pyspark.sql.functions import to_date

silver_df = silver_df.withColumn(

"OrderDate",

to_date(

col("OrderDate"),

"dd/MM/yyyy"

)

)

5.2.14 Numeric Cleaning

Bronze:

"2,500.50"

Clean:

from pyspark.sql.functions import regexp_replace

silver_df = silver_df.withColumn(

"Amount",

regexp_replace(

col("Amount"),

",",

""

).cast("double")

)

Result:

2500.50

5.2.15 Business Rule Validation

Silver is where business validation becomes important.

Example rules:

CustomerID cannot be NULL

Amount >= 0

Quantity > 0

OrderDate cannot be NULL

Implement:

valid_df = silver_df.filter(

(col("Amount") >= 0) &

(col("Quantity") > 0) &

col("CustomerID").isNotNull() &

col("OrderDate").isNotNull()

)

Invalid:

invalid_df = silver_df.filter(

(col("Amount") < 0) |

(col("Quantity") <= 0) |

col("CustomerID").isNull() |

col("OrderDate").isNull()

)

5.2.16 Quarantine Invalid Records

Don't silently delete bad data.

Write invalid records separately:

invalid_df.write \

    .format("delta") \
    .mode("append") \
    .saveAsTable(
        "Silver_Sales_Quarantine"
    )

Valid data:

valid_df.write \

    .format("delta") \
    .mode("append") \
    .saveAsTable(
        "Silver_Sales"
    )

Architecture:

                 Bronze
                   │
                   ▼
             Data Cleaning
                   │
                   ▼

Validation

/ \

/ \

            ▼             ▼
         Valid          Invalid
            │              │
            ▼              ▼

Silver_Sales Quarantine

5.2.17 Joining Data in Silver

Silver is often where related datasets are integrated.

Suppose:

Orders

OrderID | CustomerID | Amount

5001 | 101 | 2500

5002 | 102 | 3200

Customers

CustomerID | CustomerName

101 | John

102 | Anita

Join:

silver_orders = orders.join(

customers,

orders.CustomerID ==

customers.CustomerID,

"left"

)

Result:

OrderID | CustomerID | CustomerName | Amount

5001 | 101 | John | 2500

5002 | 102 | Anita | 3200

5.2.18 Silver as a Conformed Layer

Silver can standardize data coming from different systems.

Suppose:

System A

Customer_ID

System B

CustID

System C

CustomerNumber

Silver can standardize all three into:

CustomerID

This creates a conformed data structure for downstream analytics.

5.2.19 Example: Multiple Sources

SQL Server
   │
   ▼
Bronze_SQL_Customers
   │
   │
REST API
   │
   ▼
Bronze_API_Customers
   │
   ▼
Standardize
   │
   ▼

Silver_Customers

Silver might contain:

CustomerID

CustomerName

Country

Email

Phone

CreatedDate

UpdatedDate

SourceSystem

Now downstream processes don't need to understand every source system's naming conventions.

5.2.20 MERGE into Silver

Silver tables are often maintained incrementally using Delta MERGE.

from delta.tables import DeltaTable

target = DeltaTable.forName(

spark,

"Silver_Customers"

)

target.alias("target") \

.merge(

source_df.alias("source"),

"target.CustomerID = source.CustomerID"

) \

.whenMatchedUpdateAll() \

.whenNotMatchedInsertAll() \

.execute()

Conceptually:

Bronze Incremental Data
          │
          ▼
       Clean
          │
          ▼
     Deduplicate
          │
          ▼

MERGE

/ \

   Existing  New
      │        │
      ▼        ▼

UPDATE INSERT

\ /

\ /

▼ ▼

Silver

5.2.21 Silver and SCD Type 1

For dimensions, Silver often contains the latest trusted state.

Example:

Bronze:

101 | Hyderabad

101 | Bengaluru

After Silver processing:

Silver:

101 | Bengaluru

This represents the latest state.

5.2.22 Silver and SCD Type 2

If historical tracking is required, Silver can contain:

CustomerID | City | StartDate | EndDate | IsCurrent

101 | Hyderabad | 2026-01-01 | 2026-08-21 | false

101 | Bengaluru | 2026-08-22 | NULL | true

This allows Gold models to analyze historical customer states.

5.2.23 Technical Metadata in Silver

Silver can retain Bronze metadata:

SourceSystem

SourceFile

BatchID

LoadTimestamp

and add Silver-specific metadata:

SilverLoadTimestamp

TransformationVersion

DataQualityStatus

Example:

from pyspark.sql.functions import (
    current_timestamp,
    lit

)

silver_df = silver_df \

    .withColumn(
        "SilverLoadTimestamp",
        current_timestamp()
    ) \
    .withColumn(
        "TransformationVersion",
        lit("v1.0")
    )

5.2.24 Data Quality Status

You can create a quality flag:

from pyspark.sql.functions import when

silver_df = silver_df.withColumn(

"DataQualityStatus",

when(

col("CustomerID").isNull(),

"INVALID"

)

.when(

col("Amount") < 0,

"INVALID"

)

.otherwise(

"VALID"

)

)

This makes data-quality problems visible.

5.2.25 Silver Table Example

A production Silver table might look like:

Silver_Sales
│
├── SaleID
├── CustomerID
├── ProductID
├── OrderDate
├── Quantity
├── Amount
├── Country
├── SourceSystem
├── SourceFile
├── BatchID
├── LoadTimestamp
└── SilverLoadTimestamp

The exact columns depend on the project.

5.2.26 Bronze → Silver → Gold Example

Consider a sales transaction:

Bronze

SaleID = "1001"
CustomerName = " John "
Country = "india"
Amount = "2,500"

Silver

SaleID = 1001

CustomerName = "John"
Country = "INDIA"

Amount = 2500.00

Gold

Country = INDIA

Month = August

TotalSales = 250000000

Each layer has a different responsibility.

5.2.27 Complete Silver Notebook

from pyspark.sql.functions import (
    col,
    trim,
    upper,
    regexp_replace,
    current_timestamp

)

# -----------------------------------------
# 1. Read Bronze
# -----------------------------------------

bronze_df = spark.table(
    "Bronze_Sales"

)

# -----------------------------------------

# 2. Standardize data types

# -----------------------------------------

silver_df = bronze_df \

    .withColumn(
        "SaleID",
        col("SaleID").cast("long")
    ) \
    .withColumn(
        "CustomerID",
        col("CustomerID").cast("long")
    ) \
    .withColumn(
        "Amount",
        regexp_replace(
            col("Amount"),
            ",",
            ""
        ).cast("double")
    )

# -----------------------------------------

# 3. Clean text

# -----------------------------------------

silver_df = silver_df \

    .withColumn(
        "CustomerName",
        trim(col("CustomerName"))
    ) \
    .withColumn(
        "Country",
        upper(trim(col("Country")))
    )

# -----------------------------------------

# 4. Handle optional NULL values

# -----------------------------------------

silver_df = silver_df.fillna({

"CustomerName": "Unknown",

"Country": "UNKNOWN"

})

# -----------------------------------------

# 5. Remove duplicate business keys

# -----------------------------------------

silver_df = silver_df.dropDuplicates(

["SaleID"]

)

# -----------------------------------------

# 6. Validate records

# -----------------------------------------

valid_df = silver_df.filter(

(col("Amount") >= 0) &

col("SaleID").isNotNull() &

col("CustomerID").isNotNull()

)

invalid_df = silver_df.filter(

(col("Amount") < 0) |

col("SaleID").isNull() |

col("CustomerID").isNull()

)

# -----------------------------------------

# 7. Add Silver metadata

# -----------------------------------------

valid_df = valid_df.withColumn(

"SilverLoadTimestamp",

    current_timestamp()

)

# -----------------------------------------

# 8. Write valid Silver data

# -----------------------------------------

valid_df.write \

    .format("delta") \
    .mode("overwrite") \
    .saveAsTable("Silver_Sales")

# -----------------------------------------

# 9. Write invalid records

# -----------------------------------------

invalid_df.write \

    .format("delta") \
    .mode("append") \
    .saveAsTable(
        "Silver_Sales_Quarantine"
    )

5.2.28 Production Silver Architecture

A robust implementation might look like:

                     BRONZE
                       │
                       ▼
               Schema Validation
                       │
                       ▼
                Type Conversion
                       │
                       ▼
                 Data Cleaning
                       │
                       ▼
                 Deduplication
                       │
                       ▼
               Business Rules
                       │
              ┌────────┴────────┐
              ▼                 ▼
           VALID             INVALID
              │                 │
              ▼                 ▼
         Silver Delta       Quarantine
              │
              ▼
       Incremental MERGE
              │
              ▼
           SILVER
              │
              ▼
      Business Transformations
              │
              ▼

GOLD

5.2.29 Silver Layer Best Practices

1. Make Silver trustworthy

Downstream developers should be able to rely on its schema and quality.

2. Standardize data types

For example:

CustomerID → BIGINT

Amount → DOUBLE/DECIMAL

OrderDate → DATE

3. Use appropriate decimal types

For financial values, prefer a suitable DECIMAL rather than relying blindly on floating-point types.

Example:

from pyspark.sql.types import DecimalType

silver_df = silver_df.withColumn(

"Amount",

col("Amount").cast(

        DecimalType(18, 2)
    )

)

4. Deduplicate using business keys

Don't simply use:

dropDuplicates()

without understanding the business key.

5. Validate business rules

Examples:

Amount >= 0

Quantity > 0

CustomerID NOT NULL

6. Quarantine invalid records

Don't silently discard them.

7. Use Delta

Silver is typically a strong candidate for Delta storage.

8. Design for incremental processing

Use:

MERGE

when source data contains updates.

5.2.30 Silver vs Gold

This distinction is important.

Silver

Answers:

"What is the clean and trusted version of the source data?"

Example:

Customer

Order

Product

Gold

Answers:

"What information does the business need?"

Example:

Monthly Revenue

Customer Lifetime Value

Product Performance

Regional Sales

5.2.31 Example: E-Commerce Platform

Bronze

Bronze_Orders

Bronze_Customers

Bronze_Products

Bronze_Payments

Silver

Silver_Orders

Silver_Customers

Silver_Products

Silver_Payments

Cleaned, validated and integrated.

Gold

Gold_DailySales

Gold_CustomerRevenue

Gold_ProductRevenue

Gold_PaymentSummary

5.2.32 Interview Questions

1. What is the Silver Layer?

The Silver Layer contains cleaned, validated, standardized, and often integrated data derived from Bronze.

2. What transformations happen in Silver?

Common operations include:

Cleansing

Deduplication

Type conversion

Validation

Standardization

Joins

Business rules

Incremental MERGE

3. Why not perform all transformations in Bronze?

Bronze should preserve source data as much as practical. Keeping significant transformation logic downstream makes the raw data reusable for reprocessing and troubleshooting.

4. What is the difference between Bronze and Silver?

Bronze → Raw / Near Raw

Silver → Clean / Trusted

5. Why use Delta for Silver?

Delta provides reliable transactional storage and supports operations such as:

MERGE

UPDATE

DELETE

6. How do you remove duplicates?

df.dropDuplicates(["CustomerID"])

7. How do you select the latest record?

Use a window function:

Window.partitionBy(

"CustomerID"

).orderBy(

col("UpdatedDate").desc()

)

with:
row_number()

8. What should happen to invalid records?

They should generally be quarantined, logged, or otherwise handled explicitly, rather than silently deleted.

9. What is a conformed Silver dataset?

A standardized dataset where common business entities have consistent names, data types, keys, and definitions across source systems.

10. What is the purpose of Silver?

To create a reliable, reusable, trusted representation of the underlying business data.

5.2.33 Hands-On Lab

Scenario

You have:

Bronze_Sales

with:

SaleID

CustomerID

CustomerName

Country

Amount

OrderDate

Create:

Silver_Sales

Step 1 — Read Bronze

df = spark.table(
    "Bronze_Sales"

)

Step 2 — Clean

df = df \
    .withColumn(
        "CustomerName",
        trim(col("CustomerName"))
    ) \
    .withColumn(
        "Country",
        upper(trim(col("Country")))
    )

Step 3 — Convert types

df = df \
    .withColumn(
        "CustomerID",
        col("CustomerID").cast("long")
    ) \
    .withColumn(
        "Amount",
        col("Amount").cast("double")
    )

Step 4 — Deduplicate

df = df.dropDuplicates(
    ["SaleID"]

)

Step 5 — Validate

valid_df = df.filter(
    col("SaleID").isNotNull() &
    col("CustomerID").isNotNull() &
    (col("Amount") >= 0)

)

Step 6 — Write Silver

valid_df.write \

    .format("delta") \
    .mode("overwrite") \
    .saveAsTable("Silver_Sales")

Step 7 — Query Silver

SELECT *

FROM Silver_Sales

LIMIT 100;

5.2.34 Key Takeaways

Remember the three layers:
┌─────────────────────────────┐
│          BRONZE             │
│   Raw / Near-raw data       │
│                             │
│   Preserve source           │
└──────────────┬──────────────┘
               │
               ▼
┌─────────────────────────────┐
│          SILVER             │
│   Clean / Valid / Trusted   │
│                             │
│   Standardize               │
│   Deduplicate               │
│   Validate                  │
│   Integrate                 │
└──────────────┬──────────────┘
               │
               ▼
┌─────────────────────────────┐
│           GOLD              │
│   Business-ready data       │
│                             │
│   Aggregate                 │
│   Model                     │
│   Report                    │
└─────────────────────────────┘

The Silver Layer's Core Responsibilities

Bronze

↓

Clean

↓

Standardize

↓

Validate

↓

Deduplicate

↓

Integrate

↓

MERGE / Upsert

↓

Silver Delta

Bronze is what arrived. Silver is what you can trust. Gold is what the business wants to consume.

↑ Back to top

↑ Back to top

5.3 Gold Layer

5.3.1 What is the Gold Layer?

The Gold Layer is the final, business-oriented layer of the Medallion Architecture.

It contains business-ready, optimized, and consumption-focused data created from the Silver layer.

Source Systems
      │
      ▼

BRONZE

Raw / Near Raw
      │
      ▼

SILVER

Clean / Trusted
      │
      ▼

GOLD

Business Ready
      │
      ├── Power BI
      ├── Reports
      ├── Dashboards
      ├── Analytics
      └── Data Science

The key principle is:

Bronze preserves, Silver prepares, Gold serves.

5.3.2 Why Do We Need a Gold Layer?

Silver data is clean and trustworthy, but it may still be too detailed for business users.

For example, Silver might contain millions of transactions:

SaleID

CustomerID

ProductID

OrderDate

Quantity

Amount

Country

A business user may want:

Country | Month | TotalSales | Orders | Customers

INDIA | Aug | ₹25 Crore | 125K | 85K

USA | Aug | ₹18 Crore | 95K | 62K

Gold creates this business-friendly representation.

5.3.3 Gold Layer Responsibilities

Typical Gold transformations include:

Business aggregations

KPIs

Metrics

Dimensional models

Fact tables

Reporting tables

Customer summaries

Product performance

Financial summaries

Business-specific calculations

Architecture:

                 SILVER
                    │
                    ▼
           Business Transformations
                    │
        ┌───────────┼───────────┐
        ▼           ▼           ▼
   Aggregations   Joins       KPIs
        │           │           │
        └───────────┼───────────┘
                    ▼
                  GOLD
                    │
        ┌───────────┼───────────┐
        ▼           ▼           ▼

Power BI Analytics Reports

5.3.4 Silver vs Gold

5.3.5 Example

Silver Sales

SaleID | CustomerID | ProductID | Country | Amount

1001 | 101 | P10 | INDIA | 2500

1002 | 102 | P11 | INDIA | 3200

1003 | 103 | P10 | USA | 1800

1004 | 101 | P12 | INDIA | 4100

Gold:

Country | TotalSales | OrderCount

INDIA | 9800 | 3

USA | 1800 | 1

The Gold table is easier for reporting.

5.3.6 Gold Tables

A typical sales solution might have:

Gold
│
├── FactSales
├── DimCustomer
├── DimProduct
├── DimDate
├── SalesByDay
├── SalesByMonth
├── SalesByCountry
├── CustomerRevenue
└── ProductPerformance

The exact design depends on the business requirements.

5.3.7 Gold Fact Tables

A fact table stores measurable business events.

Example:

FactSales

Columns:

SaleID

CustomerKey

ProductKey

DateKey

Quantity

SalesAmount

DiscountAmount

CostAmount

ProfitAmount

Example:

5.3.8 Gold Dimension Tables

Dimensions describe business entities.

Examples:

DimCustomer

DimProduct

DimDate

DimStore

DimRegion

Example:

DimCustomer

CustomerKey

CustomerID

CustomerName

Country

City

Segment

Gold can therefore support a star schema.

                  DimCustomer
                       │
                       │
                       ▼
DimDate ──────── FactSales ──────── DimProduct
                       │
                       │
                       ▼

DimStore

5.3.9 Gold Aggregations

One of the most common Gold operations is aggregation.

from pyspark.sql.functions import (
    sum,
    count,
    avg

)

gold_df = silver_df.groupBy(

"Country"

).agg(

sum("Amount").alias("TotalSales"),

count("*").alias("OrderCount"),

avg("Amount").alias("AverageOrderValue")

)

Write:

gold_df.write \

    .format("delta") \
    .mode("overwrite") \
    .saveAsTable(
        "Gold_SalesByCountry"
    )

5.3.10 Gold by Month

Suppose Silver has:

OrderDate

Amount

Create year/month:

from pyspark.sql.functions import (
    year,
    month,
    sum

)

gold_df = silver_df \

    .withColumn(
        "Year",
        year("OrderDate")
    ) \
    .withColumn(
        "Month",
        month("OrderDate")
    ) \
    .groupBy(
        "Year",
        "Month"
    ) \
    .agg(
        sum("Amount").alias(
            "TotalSales"
        )
    )

Result:

5.3.11 Gold KPIs

Gold is an excellent place to calculate business KPIs.

Examples:

Revenue

SUM(SalesAmount)

Orders

COUNT(DISTINCT OrderID)

Average Order Value

Revenue / Orders

Profit

Revenue - Cost

Profit Margin

Profit / Revenue × 100

5.3.12 Example KPI Calculation

from pyspark.sql.functions import (
    sum,
    countDistinct

)

gold_df = silver_df.groupBy(

"Country"

).agg(

sum("Amount").alias("Revenue"),

countDistinct("OrderID").alias("Orders")

)

Then calculate AOV:

gold_df = gold_df.withColumn(

"AverageOrderValue",

col("Revenue") / col("Orders")

)

5.3.13 Gold Business Logic

Suppose the business defines customer segments:

Revenue >= 1,000,000 → Platinum

Revenue >= 500,000 → Gold

Revenue >= 100,000 → Silver

Otherwise → Bronze

You can implement:

from pyspark.sql.functions import when

gold_df = gold_df.withColumn(

"CustomerSegment",

when(

col("Revenue") >= 1000000,

"Platinum"

)

.when(

col("Revenue") >= 500000,

"Gold"

)

.when(

col("Revenue") >= 100000,

"Silver"

)

.otherwise(

"Bronze"

)

)

This is business logic and belongs naturally in a consumption-oriented Gold model.

5.3.14 Joining Silver Datasets

Gold frequently combines multiple Silver datasets.

Suppose:

Silver_Orders

Silver_Customers

Silver_Products

Join:

gold_df = orders.join(

customers,

orders.CustomerID ==

customers.CustomerID,

"left"

).join(

products,

orders.ProductID ==

products.ProductID,

"left"

)

Then calculate:

Revenue

Profit

CustomerSegment

ProductCategory

Region

5.3.15 Gold and Star Schema

A common Gold architecture is the star schema.

                     DimDate
                        │
                        │
                        ▼
DimCustomer ─────── FactSales ─────── DimProduct
                        │
                        │
                        ▼

DimStore

Fact:

FactSales

Dimensions:

DimCustomer

DimProduct

DimDate

DimStore

This structure works well for analytical workloads and semantic models.

5.3.16 Gold and Power BI

A common Fabric architecture is:

Bronze
   │
   ▼
Silver
   │
   ▼
Gold
   │
   ▼
Semantic Model
   │
   ▼

Power BI

Instead of making Power BI perform all heavy transformations on raw transaction data, you can prepare business-ready Gold datasets.

This can simplify the semantic model and improve governance.

5.3.17 Gold Data Should Have Business-Friendly Names

Silver:

cust_id

txn_amt

ord_dt

prod_cd

Gold:

CustomerID

SalesAmount

OrderDate

ProductCode

Better business-friendly names improve usability.

5.3.18 Gold Data Grain

One of the most important modeling concepts is grain.

Grain means:

What does one row represent?

Example:

FactSales

One row = one sales transaction

SalesByDay

One row = one day

SalesByCountry

One row = one country

CustomerRevenue

One row = one customer

Always define the grain before creating a Gold table.

5.3.19 Example: Customer Revenue

Silver:

CustomerID | Amount

101 | 2500

101 | 3500

102 | 4000

Gold:

gold_df = silver_df.groupBy(

"CustomerID"

).agg(

sum("Amount").alias(

"TotalRevenue"

)

)

Result:

5.3.20 Gold Product Performance

gold_df = silver_df.groupBy(

"ProductID"

).agg(

sum("Quantity").alias(

"UnitsSold"

),

sum("Amount").alias(

"Revenue"

)

)

Result:

5.3.21 Gold with Window Functions

Gold can use window functions for advanced analytics.

Example: rank products by revenue.

from pyspark.sql.window import Window
from pyspark.sql.functions import (
    rank,
    desc

)

window = Window \

.orderBy(

        desc("Revenue")
    )

gold_df = gold_df.withColumn(

"RevenueRank",

rank().over(window)

)

Result:

5.3.22 Gold and Incremental Processing

Gold tables can also be updated incrementally.

For example:

Silver
  │
  ▼

Today's changes

  │
  ▼
Aggregate
  │
  ▼

Gold MERGE

For detailed fact tables:

target.alias("target") \

.merge(

source.alias("source"),

"target.SaleID = source.SaleID"

) \

.whenMatchedUpdateAll() \

.whenNotMatchedInsertAll() \

.execute()

For aggregate tables, the approach may differ because updates to individual source transactions can affect aggregate values.

5.3.23 Gold Table Example

Consider:

Gold_MonthlySales

Columns:

Year

Month

Country

Revenue

OrderCount

CustomerCount

AverageOrderValue

Profit

ProfitMargin

Example:

This is much more suitable for executive reporting than raw transaction data.

5.3.24 Complete Gold Example

Read Silver:

silver_df = spark.table(
    "Silver_Sales"

)

Create year/month:

from pyspark.sql.functions import (
    year,
    month,
    sum,
    countDistinct,
    round

)

gold_df = silver_df \

    .withColumn(
        "Year",
        year("OrderDate")
    ) \
    .withColumn(
        "Month",
        month("OrderDate")
    )

Aggregate:

gold_df = gold_df.groupBy(

"Year",

"Month",

"Country"

).agg(

sum("Amount").alias("Revenue"),

countDistinct("OrderID").alias(

"OrderCount"

),

countDistinct("CustomerID").alias(

"CustomerCount"

)

)

Calculate AOV:

gold_df = gold_df.withColumn(

"AverageOrderValue",

round(

col("Revenue") /

col("OrderCount"),

2

)

)

Write:

gold_df.write \

    .format("delta") \
    .mode("overwrite") \
    .saveAsTable(
        "Gold_MonthlySales"
    )

5.3.25 Gold Data Quality

Gold should still be validated.

For example:

gold_df.filter(

col("Revenue") < 0

).show()

Check duplicate grain:

duplicates = gold_df.groupBy(

"Year",

"Month",

"Country"

).count().filter(

col("count") > 1

)

display(duplicates)

If the intended grain is:

Year + Month + Country

there should generally be only one row per combination.

5.3.26 Gold Table Naming

A consistent naming convention helps.

Examples:

Gold_DailySales

Gold_MonthlySales

Gold_CustomerRevenue

Gold_ProductPerformance

Gold_RegionalSales

For dimensional models:

FactSales

DimCustomer

DimProduct

DimDate

DimStore

Choose one convention and use it consistently.

5.3.27 Gold Layer Security

Gold data is often widely consumed, but that doesn't mean everyone should see everything.

For example:

Gold_Financials

may require stricter access than:

Gold_ProductPerformance

Use Fabric's security and governance capabilities according to the organization's requirements.

5.3.28 Gold vs Semantic Model

They are not the same.

Gold Tables
     │
     ▼
Semantic Model
     │
     ▼

Power BI

Gold

Stores curated business data.

Semantic Model

Defines:

Relationships

Measures

Hierarchies

Business calculations

Reporting metadata

For example:

Gold Revenue
      │
      ▼
Semantic Model
      │
      └── Total Revenue
      └── YoY Growth
      └── Profit Margin
      │
      ▼

Power BI

5.3.29 Gold Architecture

A complete Fabric architecture:

                    SOURCE
                      │
                      ▼
                  INGESTION
                      │
                      ▼
                ┌───────────┐
                │  BRONZE   │
                │ Raw Data  │
                └─────┬─────┘
                      │
                Clean / Validate
                      │
                      ▼
                ┌───────────┐
                │  SILVER   │
                │ Trusted   │
                │ Data      │
                └─────┬─────┘
                      │
          ┌───────────┼───────────┐
          ▼           ▼           ▼
       Joins      Aggregations   SCD
          │           │           │
          └───────────┼───────────┘
                      ▼
                ┌───────────┐
                │   GOLD    │
                │ Business  │
                │ Ready     │
                └─────┬─────┘
                      │
             ┌────────┼────────┐
             ▼        ▼        ▼

Power BI Analytics ML

5.3.30 Complete Example: E-Commerce

Bronze

Bronze_Customers

Bronze_Orders

Bronze_Products

Bronze_Payments

Silver

Silver_Customers

Silver_Orders

Silver_Products

Silver_Payments

Gold

FactSales

DimCustomer

DimProduct

DimDate

Gold_DailySales

Gold_MonthlySales

Gold_CustomerRevenue

Gold_ProductPerformance

Consumption

Gold
 │
 ├── Power BI
 ├── Reports
 ├── Dashboards
 ├── Analytics
 └── Data Science

5.3.31 Hands-On Lab

Project: Build Monthly Sales Gold Table

Input

Silver_Sales

Columns:

OrderID

CustomerID

Country

OrderDate

Amount

Step 1 — Read Silver

df = spark.table(
    "Silver_Sales"

)

Step 2 — Create Year and Month

from pyspark.sql.functions import (
    year,
    month

)

df = df \
    .withColumn(
        "Year",
        year("OrderDate")
    ) \
    .withColumn(
        "Month",
        month("OrderDate")
    )

Step 3 — Aggregate

from pyspark.sql.functions import (
    sum,
    countDistinct

)

gold_df = df.groupBy(
    "Year",
    "Month",
    "Country"

).agg(

sum("Amount").alias(

"Revenue"

),

countDistinct("OrderID").alias(

"OrderCount"

),

countDistinct("CustomerID").alias(

"CustomerCount"

)

)

Step 4 — Calculate AOV

gold_df = gold_df.withColumn(

"AverageOrderValue",

col("Revenue") /

    col("OrderCount")

)

Step 5 — Write Gold

gold_df.write \

    .format("delta") \
    .mode("overwrite") \
    .saveAsTable(
        "Gold_MonthlySales"
    )

Step 6 — Query

SELECT

Year,

Month,

Country,

Revenue,

OrderCount,

CustomerCount,

AverageOrderValue

FROM Gold_MonthlySales

ORDER BY

Year,

Month,

Revenue DESC;

5.3.32 Interview Questions

1. What is the Gold Layer?

The Gold Layer contains curated, business-ready data optimized for analytics, reporting, dashboards, and other consumption workloads.

2. What is the difference between Silver and Gold?

Silver → Clean and trusted

Gold → Business-ready and consumption-focused

3. What transformations happen in Gold?

Common transformations include:

Aggregations

KPIs

Business calculations

Dimensional modeling

Fact creation

Reporting datasets

4. What is the grain of a Gold table?

The grain defines what one row represents.

Example:

Gold_MonthlySales

→ One row per Month + Country

5. What is a fact table?

A table containing measurable business events or metrics.

Example:

FactSales

6. What is a dimension?

A table describing business entities.

Examples:

DimCustomer

DimProduct

DimDate

7. Why is Gold useful for Power BI?

It provides curated datasets with consistent business definitions and often reduces the need for complex transformations in the reporting layer.

8. Should Gold contain raw data?

Generally no. Raw data belongs in Bronze.

9. Should every Gold table be aggregated?

No. Gold can contain detailed fact tables as well as aggregated reporting tables.

10. What is the most important thing before creating a Gold table?

Define the business purpose and grain.

5.3.33 Key Takeaways

The Medallion Architecture can be remembered as:

       BRONZE
          │
          │ Preserve
          ▼
       SILVER
          │
          │ Trust
          ▼
        GOLD
          │
          │ Serve
          ▼

BUSINESS USERS

Bronze

Raw / Near Raw

Silver

Clean / Validated / Standardized

Gold

Business Ready / Optimized / Consumable

The Gold layer is where technical data becomes business information:

Silver Transactions
        │
        ▼

Business

     Logic
        │
        ├── Revenue
        ├── Profit
        ├── Orders
        ├── Customers
        ├── KPIs
        └── Trends
        │
        ▼
   Gold Tables
        │
        ▼

Power BI

Golden Rule: Silver tells you what the data says; Gold tells the business what it means.

↑ Back to top

↑ Back to top

5.4 Data Cleansing

5.4.1 What is Data Cleansing?

Data cleansing is the process of identifying and correcting, removing, standardizing, or handling inaccurate, incomplete, inconsistent, duplicate, or invalid data.

In a Fabric Medallion Architecture, cleansing is primarily performed while transforming Bronze → Silver.

Bronze
  │
  ▼
┌─────────────────────────┐
│    DATA CLEANSING       │
│                         │
│ • NULL handling         │
│ • Duplicates            │
│ • Data types            │
│ • Text standardization  │
│ • Date validation       │
│ • Invalid values        │
│ • Business rules        │
└────────────┬────────────┘
             ▼

Silver

Simple definition

Data cleansing converts messy source data into reliable, standardized, and usable data.

5.4.2 Why is Data Cleansing Important?

Real-world source systems rarely contain perfect data.

For example:

CustomerID | Name | Country | Amount

101 | " John " | india | "2,500"

102 | NULL | INDIA | "3200"

101 | "John" | India | "2500"

103 | " David " | UK | NULL

104 | "Priya" | IND | "-500"

Problems include:

Extra spaces

NULL values

Duplicate customers

Inconsistent country values

String numbers

Missing amounts

Negative values

After cleansing:

CustomerID | Name | Country | Amount

101 | John | INDIA | 2500

102 | Unknown | INDIA | 3200

103 | David | UK | 0

104 | Priya | INDIA | -500*

-500 should not automatically be changed to 0; whether it is valid depends on the business rule. A refund, credit, or reversal may legitimately be negative.

5.4.3 Common Data Quality Problems

The major categories are:

Data Quality Problems
│
├── Missing Values
├── Duplicate Records
├── Incorrect Data Types
├── Invalid Values
├── Inconsistent Formats
├── Extra Spaces
├── Incorrect Capitalization
├── Invalid Dates
├── Invalid Keys
└── Business Rule Violations

5.4.4 Data Cleansing Workflow

A typical Silver transformation:

             Bronze
                │
                ▼
          Profile Data
                │
                ▼
        Standardize Types
                │
                ▼
         Clean Text Fields
                │
                ▼
          Handle NULLs
                │
                ▼
        Remove/Deduplicate
                │
                ▼
         Validate Values
                │
                ▼
        Apply Business Rules
                │
          ┌─────┴─────┐
          ▼           ▼
        Valid       Invalid
          │           │
          ▼           ▼

Silver Quarantine

5.4.5 Data Profiling

Before cleaning data, understand what is actually in it.

Load the Bronze table:

df = spark.table("Bronze_Sales")

Inspect schema:

df.printSchema()

View records:

display(df.limit(100))

Count rows:

print("Rows:", df.count())

Profiling helps you understand the quality problems before writing cleansing rules.

5.4.6 Check NULL Values

Suppose:

CustomerID | CustomerName | Amount

101 | John | 2500

102 | NULL | 3000

NULL | David | 4000

Check NULLs:

from pyspark.sql.functions import col

df.filter(
    col("CustomerID").isNull()

).show()

For multiple columns:

for column in ["CustomerID", "CustomerName", "Amount"]:

    print(
        column,
        df.filter(col(column).isNull()).count()
    )

5.4.7 Handling NULL Values

There are several approaches.

Option 1 — Replace with a default

df = df.fillna({
    "CustomerName": "Unknown",
    "Country": "UNKNOWN"

})

Use this only when a default value makes business sense.

Option 2 — Remove the record

For a required key:

df = df.filter(
    col("CustomerID").isNotNull()

)

Option 3 — Quarantine

For important data-quality failures:

invalid_df = df.filter(
    col("CustomerID").isNull()

)

Then write:

invalid_df.write \

    .format("delta") \
    .mode("append") \
    .saveAsTable("Silver_Sales_Quarantine")

This is often preferable to silently deleting records.

5.4.8 Replace NULL Using coalesce

You can also use:

from pyspark.sql.functions import coalesce, lit

df = df.withColumn(
    "CustomerName",
    coalesce(
        col("CustomerName"),
        lit("Unknown")
    )

)

Conceptually:

CustomerName
     │
     ├── Has value → Keep it
     │
     └── NULL → "Unknown"

5.4.9 Removing Extra Spaces

Raw:

" John Kumar "

Clean:

"John Kumar"

Use:

from pyspark.sql.functions import trim

df = df.withColumn(
    "CustomerName",
    trim(col("CustomerName"))

)

For leading spaces:

ltrim()

For trailing spaces:

rtrim()

5.4.10 Standardizing Case

Raw data:

india

India

INDIA

Standardize:

from pyspark.sql.functions import upper

df = df.withColumn(
    "Country",
    upper(trim(col("Country")))

)

Result:

INDIA

INDIA

INDIA

For names, you might use:

from pyspark.sql.functions import initcap

df = df.withColumn(
    "CustomerName",
    initcap(trim(col("CustomerName")))

)

5.4.11 Standardizing Codes

Suppose the source contains:

IND

IN

India

INDIA

Business standard:

INDIA

Use:

from pyspark.sql.functions import when

df = df.withColumn(
    "Country",
    when(
        col("Country").isin("IND", "IN", "India", "INDIA"),
        "INDIA"
    ).otherwise(
        upper(trim(col("Country")))
    )

)

For many mappings, a reference table is often better than a long when() chain.

5.4.12 Data Type Conversion

Bronze:

Amount = "2500.50"

Type:

string

Silver:

Amount = 2500.50

Type:

decimal

Example:

from pyspark.sql.types import DecimalType

df = df.withColumn(
    "Amount",
    col("Amount").cast(
        DecimalType(18, 2)
    )

)

For financial data, use an appropriate DECIMAL rather than floating-point types.

5.4.13 Cleaning Numeric Strings

Suppose:

"₹2,500.50"

You may need to remove formatting characters before casting.

from pyspark.sql.functions import regexp_replace

df = df.withColumn(
    "Amount",
    regexp_replace(
        col("Amount"),
        "₹",
        ""
    )

)

df = df.withColumn(
    "Amount",
    regexp_replace(
        col("Amount"),
        ",",
        ""
    ).cast("decimal(18,2)")

)

Result:

2500.50

The exact logic should reflect the source's actual formatting.

5.4.14 Date Cleansing

Source:

22/08/2026

Convert:

from pyspark.sql.functions import to_date

df = df.withColumn(
    "OrderDate",
    to_date(
        col("OrderDate"),
        "dd/MM/yyyy"
    )

)

Result:

2026-08-22

5.4.15 Invalid Dates

Suppose the source contains:

22/08/2026

45/15/2026

abc

NULL

When conversion fails, the resulting date can become NULL.

Check:

invalid_dates = df.filter(
    col("OrderDate").isNull()

)

However, if NULL was already valid in the source, distinguish between original NULL and conversion failure when data quality reporting matters.

5.4.16 Duplicate Records

Suppose:

CustomerID | Name

101 | John

101 | John

102 | Anita

Simple deduplication:

df = df.dropDuplicates(
    ["CustomerID"]

)

Result:

101 | John

102 | Anita

But this is only safe when any duplicate version is acceptable.

5.4.17 Latest Record Deduplication

Suppose:

CustomerID | City | UpdatedDate

101 | Hyderabad | 2026-08-20

101 | Bengaluru | 2026-08-22

We want Bengaluru.

Use a window:

from pyspark.sql.window import Window

from pyspark.sql.functions import row_number

window_spec = Window \

.partitionBy("CustomerID") \

.orderBy(

col("UpdatedDate").desc()

)

df = df \
    .withColumn(
        "rn",
        row_number().over(window_spec)
    ) \
    .filter(
        col("rn") == 1
    ) \
    .drop("rn")

5.4.18 Invalid Numeric Values

Suppose:

Quantity
--------

10

5

0

-3

NULL

Business rule:

Quantity > 0

Validate:

valid_df = df.filter(
    col("Quantity") > 0

)

invalid_df = df.filter(
    (col("Quantity") <= 0) |
    col("Quantity").isNull()

)

5.4.19 Data Quality Rules

A Silver pipeline might define:

Rule 1: CustomerID cannot be NULL

Rule 2: OrderID cannot be NULL

Rule 3: Quantity > 0

Rule 4: Amount >= 0

Rule 5: OrderDate cannot be future date

Rule 6: Country must be a valid country

Rule 7: OrderID must be unique

These rules should come from actual business requirements rather than arbitrary assumptions.

5.4.20 Validate Date Against Business Rules

Suppose orders cannot have a future date.

from pyspark.sql.functions import current_date

invalid_df = df.filter(
    col("OrderDate") > current_date()

)

Valid:

valid_df = df.filter(
    col("OrderDate") <= current_date()

)

Be careful with timezone assumptions when comparing timestamps.

5.4.21 Data Quality Flags

Instead of immediately removing invalid records, create a status:

from pyspark.sql.functions import when

df = df.withColumn(
    "DataQualityStatus",
    when(
        col("CustomerID").isNull(),
        "INVALID_CUSTOMER"
    )
    .when(
        col("Amount") < 0,
        "INVALID_AMOUNT"
    )
    .when(
        col("Quantity") <= 0,
        "INVALID_QUANTITY"
    )
    .otherwise(
        "VALID"
    )

)

Result:

5.4.22 Multiple Validation Rules

A more scalable approach is to create multiple flags:

from pyspark.sql.functions import when

df = df \
    .withColumn(
        "InvalidCustomer",
        when(
            col("CustomerID").isNull(),
            1
        ).otherwise(0)
    ) \
    .withColumn(
        "InvalidAmount",
        when(
            col("Amount") < 0,
            1
        ).otherwise(0)
    ) \
    .withColumn(
        "InvalidQuantity",
        when(
            col("Quantity") <= 0,
            1
        ).otherwise(0)
    )

Then:

valid_df = df.filter(
    (col("InvalidCustomer") == 0) &
    (col("InvalidAmount") == 0) &
    (col("InvalidQuantity") == 0)

)

5.4.23 Quarantine Architecture

A production pipeline should often separate valid and invalid records.

                    BRONZE
                       │
                       ▼
                Data Cleansing
                       │
                       ▼
                Data Validation
                       │
                ┌──────┴──────┐
                ▼             ▼
             VALID          INVALID
                │             │
                ▼             ▼
             SILVER       QUARANTINE
                │             │
                ▼             ▼

GOLD Data Quality

Monitoring

Quarantine records should ideally include:

Original record

Failure reason

Rule name

Batch ID

Load timestamp

Source file

5.4.24 Failure Reason

Example:

from pyspark.sql.functions import when

df = df.withColumn(
    "FailureReason",
    when(
        col("CustomerID").isNull(),
        "CustomerID is NULL"
    )
    .when(
        col("Amount") < 0,
        "Amount is negative"
    )
    .when(
        col("Quantity") <= 0,
        "Quantity must be greater than zero"
    )

)

This makes troubleshooting much easier.

5.4.25 Standardizing Column Names

Source:

cust_id

Cust_Name

order-date

txn_amt

Silver:

CustomerID

CustomerName

OrderDate

TransactionAmount

In PySpark:

df = df \
    .withColumnRenamed(
        "cust_id",
        "CustomerID"
    ) \
    .withColumnRenamed(
        "Cust_Name",
        "CustomerName"
    )

Consistent naming makes downstream development easier.

5.4.26 Remove Unnecessary Columns

Bronze may contain:

CustomerID

Name

Country

Amount

DebugColumn

TemporaryColumn

SourceRawValue

If a column has no downstream purpose:

df = df.drop(
    "DebugColumn",
    "TemporaryColumn"

)

Don't remove source information from Bronze merely to make Silver smaller; make the decision at the Silver boundary based on actual requirements.

5.4.27 Data Standardization Example

Suppose three systems provide customer data.

System A

Customer_ID

System B

CustID

System C

customer_number

Silver standard:

CustomerID

This creates a common representation.

SQL Server ──┐
             │
REST API ────┼──► Silver Customer
             │
CSV ─────────┘

5.4.28 Reference Data for Cleansing

Don't hardcode large mapping lists when a reference table is more appropriate.

For example:

CountryCode
-----------

IN | INDIA

US | USA

GB | UK

Join:
clean_df = df.join(
    country_reference,
    df.CountryCode ==
    country_reference.Code,
    "left"

)

Then use the standardized value:

Country = INDIA

This is easier to maintain than hundreds of when() conditions.

5.4.29 Complete Cleansing Example

Suppose Bronze contains:

SaleID | CustomerID | Name | Country | Amount | Quantity

1001 | 101 | " John " | india | "2,500"| 2

1002 | NULL | " Anita " | INDIA | "3200" | 1

1003 | 103 | " David " | uk | "-500" | 0

A Silver transformation:

from pyspark.sql.functions import (
    col,
    trim,
    upper,
    regexp_replace,
    current_timestamp

)

from pyspark.sql.types import DecimalType

df = spark.table(
    "Bronze_Sales"

)

# Clean text
df = df \
    .withColumn(
        "Name",
        trim(col("Name"))
    ) \
    .withColumn(
        "Country",
        upper(trim(col("Country")))
    )

# Convert numeric values
df = df.withColumn(
    "Amount",
    regexp_replace(
        col("Amount"),
        ",",
        ""
    ).cast(DecimalType(18, 2))

)

df = df.withColumn(
    "Quantity",
    col("Quantity").cast("integer")

)

# Validation
valid_df = df.filter(
    col("SaleID").isNotNull() &
    col("CustomerID").isNotNull() &
    (col("Amount") >= 0) &
    (col("Quantity") > 0)

)

invalid_df = df.filter(
    col("SaleID").isNull() |
    col("CustomerID").isNull() |
    (col("Amount") < 0) |
    (col("Quantity") <= 0)

)

# Add processing timestamp

valid_df = valid_df.withColumn(

"SilverLoadTimestamp",

    current_timestamp()

)

Write valid records:

valid_df.write \

    .format("delta") \
    .mode("append") \
    .saveAsTable(
        "Silver_Sales"
    )

Write invalid records:

invalid_df.write \

    .format("delta") \
    .mode("append") \
    .saveAsTable(
        "Silver_Sales_Quarantine"
    )

5.4.30 Data Cleansing Pipeline

A production pattern:

                 Bronze
                    │
                    ▼
             Data Profiling
                    │
                    ▼
           Schema Validation
                    │
                    ▼
            Type Conversion
                    │
                    ▼
           Text Standardization
                    │
                    ▼
              NULL Handling
                    │
                    ▼
             Deduplication
                    │
                    ▼
          Business Validation
                    │
              ┌─────┴─────┐
              ▼           ▼
            Valid       Invalid
              │           │
              ▼           ▼
           Silver      Quarantine
              │
              ▼

Gold

5.4.31 Data Cleansing Metrics

A mature pipeline should measure data quality.

For example:

Total Records = 1,000,000

Valid Records = 985,000

Invalid Records = 15,000

Duplicate Records = 8,000

NULL Customer IDs = 2,000

Invalid Amounts = 5,000

Calculate:

Data Quality Rate =

Valid Records / Total Records × 100

Example:

985,000 / 1,000,000 × 100

= 98.5%

These metrics can feed a monitoring dashboard.

5.4.32 Data Quality Dashboard

A Fabric solution could expose:

                 DATA QUALITY
                      │
       ┌──────────────┼──────────────┐
       ▼              ▼              ▼

Valid % Duplicate % NULL %

98.5% 0.8% 0.2%

       │              │              │
       └──────────────┼──────────────┘
                      ▼

Quality Trend

This is particularly useful in production data engineering.

5.4.33 Data Cleansing Best Practices

1. Profile before cleaning

Understand the actual data first.

2. Don't blindly replace NULLs

A NULL may have business meaning.

3. Don't blindly remove duplicates

Define the business key and determine which record should survive.

4. Preserve invalid records

Quarantine them when they matter.

5. Use appropriate data types

Especially for:

Financial values

Dates

IDs

Quantities

6. Make cleansing rules explicit

Document:

Rule

Reason

Action

Owner

7. Make pipelines repeatable

The same input should produce the same result under the same transformation version.

8. Monitor data quality

Track quality metrics over time.

5.4.34 Common Mistakes

Mistake 1 — Cleaning everything in Bronze

This destroys the ability to reproduce the original source state.

Mistake 2 — Dropping all NULL records

Some NULLs are legitimate.

Mistake 3 — Using dropDuplicates() without a business key

You may keep the wrong version of a record.

Mistake 4 — Converting invalid numbers to zero

For example:

"ABC" → 0

can hide a data-quality problem.

Mistake 5 — Ignoring invalid records

If bad records are silently discarded, data loss becomes difficult to detect.

Mistake 6 — Hardcoding hundreds of mappings

Use reference/master tables where appropriate.

5.4.35 Interview Questions

1. What is data cleansing?

The process of identifying and handling inaccurate, incomplete, inconsistent, duplicate, or invalid data.

2. Where does data cleansing usually happen?

Primarily during the Bronze → Silver transformation.

3. How do you remove duplicates in PySpark?

df.dropDuplicates(["CustomerID"])

4. How do you handle NULLs?

Depending on the business rule:

Replace

Filter

Quarantine

Keep

5. How do you standardize text?

trim()
upper()
lower()
initcap()

6. How do you convert a string to a numeric value?

col("Amount").cast("decimal(18,2)")

7. How do you validate records?

Apply explicit business rules:

col("CustomerID").isNotNull()

and:

col("Amount") >= 0

8. What is a quarantine table?

A table containing records that failed data-quality rules, typically along with failure reasons and technical metadata.

9. Why should invalid records not simply be deleted?

Because deletion can hide data-quality problems and makes troubleshooting and reconciliation difficult.

10. What is the difference between data cleansing and data transformation?

Data cleansing focuses on improving data quality.

Data transformation is broader and includes cleansing plus operations such as joins, aggregations, calculations, restructuring, and business logic.

5.4.36 Hands-On Lab

Scenario

You have:

Bronze_Customers

with:

CustomerID | CustomerName | Country | Email

101 | " John " | india | john@test.com

102 | NULL | INDIA | anita@test.com

101 | " John " | india | john@test.com

NULL | " David " | uk | david@test.com

Step 1 — Read

df = spark.table(
    "Bronze_Customers"

)

Step 2 — Clean text

df = df \
    .withColumn(
        "CustomerName",
        trim(col("CustomerName"))
    ) \
    .withColumn(
        "Country",
        upper(trim(col("Country")))
    )

Step 3 — Handle optional NULL

df = df.fillna({
    "CustomerName": "Unknown"

})

Step 4 — Validate CustomerID

valid_df = df.filter(
    col("CustomerID").isNotNull()

)

invalid_df = df.filter(
    col("CustomerID").isNull()

)

Step 5 — Deduplicate

valid_df = valid_df.dropDuplicates(

["CustomerID"]

)

Step 6 — Write Silver

valid_df.write \

    .format("delta") \
    .mode("overwrite") \
    .saveAsTable(
        "Silver_Customers"
    )

Step 7 — Write quarantine

invalid_df.write \

    .format("delta") \
    .mode("append") \
    .saveAsTable(
        "Silver_Customers_Quarantine"
    )

5.4.37 Final Concept

Data cleansing is not simply:

Remove bad data

It is:

             SOURCE DATA
                  │
                  ▼
              PROFILE
                  │
                  ▼
             UNDERSTAND
                  │
                  ▼
               CLEAN
                  │
       ┌──────────┴──────────┐
       ▼                     ▼
   VALID DATA            INVALID DATA
       │                     │
       ▼                     ▼
    SILVER              QUARANTINE
       │
       ▼

GOLD

Golden Rule

Never hide a data-quality problem. Detect it, apply a documented rule, and make the outcome traceable.

In a Fabric Lakehouse, this makes the Silver layer a trusted foundation for everything that follows—aggregations, SCD processing, Gold models, semantic models, and Power BI.

↑ Back to top

↑ Back to top

5.5 Aggregations

5.5.1 What is Aggregation?

Aggregation is the process of combining multiple rows to produce summarized information.

For example, a Silver sales table may contain millions of transactions:

Instead of analyzing every transaction, the business may want:

This is aggregation.

Silver Transactions
        │
        ▼
    GROUP BY
        │
        ▼
   Aggregation
        │
        ▼

Gold Summary

5.5.2 Why Aggregations Are Important

Aggregations are used to create:

Total sales

Total orders

Average order value

Customer revenue

Product revenue

Daily sales

Monthly sales

Regional performance

Profit summaries

KPIs

They are especially important in the Gold Layer.

5.5.3 Common Aggregation Functions

The most commonly used functions are:

In PySpark, these are available through pyspark.sql.functions.

5.5.4 SUM

Suppose:

Amount
------

2500

3200

1800

4100

Total:

11600

PySpark:

from pyspark.sql.functions import sum

result = df.agg(
    sum("Amount").alias("TotalSales")

)

display(result)

5.5.5 SUM with GROUP BY

Group sales by country:

result = df.groupBy(
    "Country"

).agg(

sum("Amount").alias(

"TotalSales"

)

)

Result:

Equivalent SQL:

SELECT
    Country,
    SUM(Amount) AS TotalSales
FROM Silver_Sales
GROUP BY Country;

5.5.6 COUNT

Count records:

from pyspark.sql.functions import count

result = df.agg(
    count("*").alias("TotalRecords")

)

SQL:

SELECT COUNT(*) AS TotalRecords
FROM Silver_Sales;

5.5.7 COUNT by Country

result = df.groupBy(
    "Country"

).agg(

count("*").alias(

"OrderCount"

)

)

Result:

5.5.8 COUNT DISTINCT

Suppose multiple orders belong to the same customer.

OrderID | CustomerID

1001 | 101

1002 | 101

1003 | 102

Total orders:

3

Unique customers:

2

PySpark:

from pyspark.sql.functions import countDistinct

result = df.agg(
    countDistinct(
        "CustomerID"
    ).alias(
        "UniqueCustomers"
    )

)

5.5.9 AVG

Calculate average sales:

from pyspark.sql.functions import avg

result = df.agg(
    avg("Amount").alias(
        "AverageSale"
    )

)

SQL:

SELECT
    AVG(Amount) AS AverageSale
FROM Silver_Sales;

5.5.10 MIN and MAX

Minimum:

from pyspark.sql.functions import min

df.agg(
    min("Amount").alias(
        "MinimumSale"
    )

)

Maximum:

from pyspark.sql.functions import max

df.agg(
    max("Amount").alias(
        "MaximumSale"
    )

)

5.5.11 Multiple Aggregations

You can calculate several metrics together:

from pyspark.sql.functions import (
    sum,
    count,
    avg,
    min,
    max

)

result = df.groupBy(
    "Country"

).agg(

sum("Amount").alias(

"TotalSales"

),

count("*").alias(

"OrderCount"

),

avg("Amount").alias(

"AverageSale"

),

min("Amount").alias(

"MinimumSale"

),

max("Amount").alias(

"MaximumSale"

)

)

Result:

5.5.12 Grouping by Multiple Columns

Suppose we need:

Country + Product

Use:

result = df.groupBy(
    "Country",
    "ProductID"

).agg(

sum("Amount").alias(

"TotalSales"

)

)

Example:

5.5.13 Aggregation by Date

Suppose we want daily sales.

from pyspark.sql.functions import to_date

df = df.withColumn(
    "SaleDate",
    to_date("OrderDate")

)

daily_sales = df.groupBy(
    "SaleDate"

).agg(

sum("Amount").alias(

"DailySales"

)

)

Result:

5.5.14 Monthly Aggregation

Create Year and Month:

from pyspark.sql.functions import (
    year,
    month

)

df = df \
    .withColumn(
        "Year",
        year("OrderDate")
    ) \
    .withColumn(
        "Month",
        month("OrderDate")
    )

Aggregate:

monthly_sales = df.groupBy(
    "Year",
    "Month"

).agg(

sum("Amount").alias(

"MonthlySales"

)

)

5.5.15 Monthly Sales by Country

monthly_sales = df.groupBy(
    "Year",
    "Month",
    "Country"

).agg(

sum("Amount").alias(

"Revenue"

),

countDistinct("OrderID").alias(

"Orders"

),

countDistinct("CustomerID").alias(

"Customers"

)

)

Example:

This is an excellent Gold-layer dataset.

5.5.16 Calculated KPIs

Aggregation often creates the base values for KPIs.

For example:

Revenue = ₹25,000,000

Orders = 125,000

Average Order Value:

AOV = Revenue / Orders

PySpark:

gold_df = gold_df.withColumn(

"AverageOrderValue",

col("Revenue") /

    col("Orders")

)

5.5.17 Profit Aggregation

Suppose Silver contains:

SalesAmount

CostAmount

Calculate:

gold_df = df.groupBy(
    "Country"

).agg(

sum("SalesAmount").alias(

"Revenue"

),

sum("CostAmount").alias(

"Cost"

)

)

Then:

gold_df = gold_df.withColumn(

"Profit",

col("Revenue") - col("Cost")

)

5.5.18 Profit Margin

gold_df = gold_df.withColumn(

"ProfitMargin",

(

col("Profit") /

        col("Revenue")
    ) * 100

)

Better production logic should handle zero revenue:

from pyspark.sql.functions import when

gold_df = gold_df.withColumn(

"ProfitMargin",

when(

col("Revenue") != 0,

(col("Profit") / col("Revenue")) * 100

).otherwise(None)

)

5.5.19 Conditional Aggregation

Suppose we want:

Total Orders

High Value Orders

Low Value Orders

Use conditional expressions:

from pyspark.sql.functions import (
    sum,
    when

)

result = df.groupBy(
    "Country"

).agg(

count("*").alias(

"TotalOrders"

),

sum(

when(

col("Amount") >= 10000,

1

).otherwise(0)

).alias(

"HighValueOrders"

)

)

5.5.20 Conditional SUM

Calculate sales only for high-value transactions:

result = df.groupBy(
    "Country"

).agg(

sum(

when(

col("Amount") >= 10000,

            col("Amount")
        ).otherwise(0)
    ).alias(
        "HighValueSales"
    )

)

5.5.21 Aggregating with NULL Values

Consider:

Amount
------

1000

2000

NULL

3000

SUM(Amount) generally ignores NULL values.

The sum is:

6000

However, always understand how NULLs affect your business metric before relying on the result.

5.5.22 Aggregation and Data Grain

Before creating an aggregation, define the grain.

For example:

Gold_DailySales

Grain:

One row per Date

Or:

Gold_MonthlySales

Grain:

One row per Year + Month + Country

This is extremely important.

5.5.23 Why Grain Matters

Suppose the intended table is:

Year + Month + Country

Then this:

df.groupBy(
    "Year",
    "Month",
    "Country"

)

is correct.

But if you accidentally use:

df.groupBy(
    "Year",
    "Month",
    "Country",
    "CustomerID"

)

you have changed the grain.

Instead of:

1 row per Month + Country

you now have:

1 row per Month + Country + Customer

This can completely change the numbers.

5.5.24 Aggregation in SQL

The same operation can be performed with Spark SQL.

SELECT
    Country,
    SUM(Amount) AS Revenue,
    COUNT(*) AS Orders,
    COUNT(DISTINCT CustomerID) AS Customers,
    AVG(Amount) AS AverageOrderValue
FROM Silver_Sales
GROUP BY Country;

5.5.25 HAVING

HAVING filters aggregated results.

Example:

SELECT
    Country,
    SUM(Amount) AS Revenue
FROM Silver_Sales
GROUP BY Country

HAVING SUM(Amount) > 1000000;

This returns only countries whose total revenue exceeds 1 million.

5.5.26 WHERE vs HAVING

Very important distinction:

WHERE

Filters rows before aggregation.

SELECT
    Country,
    SUM(Amount)
FROM Silver_Sales
WHERE Amount > 1000
GROUP BY Country;

HAVING

Filters groups after aggregation.

SELECT
    Country,
    SUM(Amount) AS Revenue
FROM Silver_Sales
GROUP BY Country

HAVING SUM(Amount) > 1000000;

Conceptually:

Rows
 │
 ▼
WHERE
 │
 ▼
GROUP BY
 │
 ▼
Aggregation
 │
 ▼
HAVING
 │
 ▼

Result

5.5.27 Aggregation vs Window Function

These are often confused.

Aggregation

df.groupBy("CustomerID").agg(
    sum("Amount")

)

Multiple rows become one row per customer.

Window function

Window.partitionBy("CustomerID")

The original rows remain.

Example:

Aggregation:

101 | 6000

Window:

101 | Order1 | 2000 | 6000

101 | Order2 | 4000 | 6000

5.5.28 Running Total

A running total requires a window rather than ordinary aggregation.

from pyspark.sql.window import Window

from pyspark.sql.functions import sum

window = Window \

.partitionBy("CustomerID") \

.orderBy("OrderDate") \

.rowsBetween(

Window.unboundedPreceding,

Window.currentRow

)

df = df.withColumn(
    "RunningTotal",
    sum("Amount").over(window)

)

Example:

5.5.29 Aggregation and Joins

Gold aggregations often happen after combining datasets.

Example:

Silver Orders
      │
      ├──── Customer
      │
      └──── Product
             │
             ▼
          JOIN
             │
             ▼
        GROUP BY
             │
             ▼

GOLD

Example:

joined_df = orders.join(

customers,

"CustomerID"

).join(

products,

"ProductID"

)

gold_df = joined_df.groupBy(

"Country",

"ProductCategory"

).agg(

sum("Amount").alias(

"Revenue"

)

)

5.5.30 Aggregation with Decimal Data

For financial calculations:

from pyspark.sql.types import DecimalType

df = df.withColumn(
    "Amount",
    col("Amount").cast(
        DecimalType(18, 2)
    )

)

Then:

gold_df = df.groupBy(
    "Country"

).agg(

sum("Amount").alias(

"Revenue"

)

)

Using an appropriate decimal precision/scale helps avoid inappropriate floating-point behavior in financial calculations.

5.5.31 Complete Gold Aggregation Example

Suppose Silver contains:

OrderID

CustomerID

ProductID

OrderDate

Country

Quantity

SalesAmount

CostAmount

Step 1 — Read Silver

df = spark.table(
    "Silver_Sales"

)

Step 2 — Add date attributes

from pyspark.sql.functions import (
    year,
    month

)

df = df \
    .withColumn(
        "Year",
        year("OrderDate")
    ) \
    .withColumn(
        "Month",
        month("OrderDate")
    )

Step 3 — Aggregate

from pyspark.sql.functions import (
    sum,
    countDistinct

)

gold_df = df.groupBy(
    "Year",
    "Month",
    "Country"

).agg(

sum("SalesAmount").alias(

"Revenue"

),

sum("CostAmount").alias(

"Cost"

),

sum("Quantity").alias(

"UnitsSold"

),

countDistinct("OrderID").alias(

"OrderCount"

),

countDistinct("CustomerID").alias(

"CustomerCount"

)

)

Step 4 — Calculate Profit

gold_df = gold_df.withColumn(

"Profit",

col("Revenue") - col("Cost")

)

Step 5 — Calculate Profit Margin

gold_df = gold_df.withColumn(

"ProfitMargin",

when(

col("Revenue") != 0,

(

col("Profit") /

            col("Revenue")
        ) * 100
    ).otherwise(None)

)

Step 6 — Write Gold

gold_df.write \

    .format("delta") \
    .mode("overwrite") \
    .saveAsTable(
        "Gold_MonthlySales"
    )

5.5.32 Result

The final Gold table might look like:

This is now ready for consumption by a semantic model or reporting solution.

5.5.33 Performance Considerations

Aggregation can be expensive because Spark may need to shuffle data.

Example:
Executor 1 ──┐
Executor 2 ──┤
Executor 3 ──┼── Shuffle → GROUP BY
Executor 4 ──┘

For large datasets:

Filter unnecessary rows early.

Select only required columns.

Avoid unnecessary group-by columns.

Use appropriate data types.

Avoid repeated aggregations.

Understand data skew.

Use incremental processing where appropriate.

5.5.34 Filter Before Aggregation

Instead of:

df.groupBy(
    "Country"

).agg(

    sum("Amount")

)

if the business only needs recent data:

recent_df = df.filter(
    col("OrderDate") >= "2026-08-01"

)

result = recent_df.groupBy(

"Country"

).agg(

sum("Amount").alias(

"Revenue"

)

)

This can reduce the amount of data processed.

5.5.35 Select Only Required Columns

Instead of carrying dozens of columns:

df = df.select(
    "OrderID",
    "CustomerID",
    "Country",
    "Amount"

)

before aggregation.

This can reduce data movement and memory requirements.

5.5.36 Common Aggregation Mistakes

Mistake 1 — Wrong grain

Expected:

Month + Country

Actual:

Month + Country + Customer

This produces different results.

Mistake 2 — Counting rows instead of orders

If an order has multiple line items:

OrderID 1001 → 3 rows

Then:

COUNT(*)

returns 3, not 1.

Use:

COUNT(DISTINCT OrderID)

when the metric is "number of orders."

Mistake 3 — Ignoring NULLs

Understand how NULL values affect the metric.

Mistake 4 — Using floating point for money

Prefer suitable decimal types for financial data.

Mistake 5 — Recalculating expensive aggregations unnecessarily

Reuse curated Gold datasets where appropriate.

5.5.37 Hands-On Lab

Scenario

You have:

Silver_Sales

with:

OrderID

CustomerID

OrderDate

Country

Quantity

SalesAmount

CostAmount

Create:

Gold_MonthlySales

Step 1

df = spark.table(
    "Silver_Sales"

)

Step 2

df = df \
    .withColumn(
        "Year",
        year("OrderDate")
    ) \
    .withColumn(
        "Month",
        month("OrderDate")
    )

Step 3

gold_df = df.groupBy(
    "Year",
    "Month",
    "Country"

).agg(

sum("SalesAmount").alias(

"Revenue"

),

sum("CostAmount").alias(

"Cost"

),

sum("Quantity").alias(

"UnitsSold"

),

countDistinct("OrderID").alias(

"OrderCount"

),

countDistinct("CustomerID").alias(

"CustomerCount"

)

)

Step 4

gold_df = gold_df.withColumn(

"Profit",

col("Revenue") - col("Cost")

)

Step 5

gold_df = gold_df.withColumn(

"AverageOrderValue",

col("Revenue") /

    col("OrderCount")

)

Step 6

gold_df.write \

    .format("delta") \
    .mode("overwrite") \
    .saveAsTable(
        "Gold_MonthlySales"
    )

5.5.38 Interview Questions

1. What is aggregation?

Combining multiple rows to calculate summarized values such as totals, counts, averages, minimums, and maximums.

2. What is GROUP BY?

It groups records according to one or more columns before calculating aggregate metrics.

3. What is the difference between COUNT(*) and COUNT(DISTINCT OrderID)?

COUNT(*) counts rows, while COUNT(DISTINCT OrderID) counts unique orders.

4. What is the difference between aggregation and a window function?

Aggregation reduces multiple rows into grouped results. A window function calculates across related rows while generally retaining the original rows.

5. What is HAVING?

HAVING filters grouped results after aggregation.

6. What is data grain?

The definition of what one row represents in a dataset.

Example:

One row per Month + Country.

7. Where are aggregations commonly performed in Medallion Architecture?

They are commonly performed when creating Gold datasets, although aggregations can also occur elsewhere when technically or analytically appropriate.

8. How do you calculate revenue?

sum("SalesAmount")

9. How do you calculate unique customers?

countDistinct("CustomerID")

10. Why is defining grain important?

Because an incorrect grain can produce incorrect business metrics and duplicate or misleading results.

5.5.39 Key Takeaways

Remember:

              SILVER
                 │
                 ▼
          Detailed Records
                 │
                 ▼
             GROUP BY
                 │
        ┌────────┼────────┐
        ▼        ▼        ▼
       SUM     COUNT     AVG
        │        │        │
        └────────┼────────┘
                 ▼
               GOLD
                 │
        ┌────────┼────────┐
        ▼        ▼        ▼

KPIs Reports Power BI

Core PySpark pattern

from pyspark.sql.functions import (
    sum,
    countDistinct,
    avg

)

gold_df = df.groupBy(
    "Country"

).agg(

sum("Amount").alias("Revenue"),

countDistinct("OrderID").alias("Orders"),

countDistinct("CustomerID").alias("Customers"),

avg("Amount").alias("AverageOrderValue")

)

Most important rule

Before performing any aggregation, define the grain of the resulting table.

For example:

Gold_MonthlySales

=

One row per Year + Month + Country

Once the grain is clear, the GROUP BY columns, metrics, validation rules, and downstream Power BI model become much easier to design.

↑ Back to top

↑ Back to top

5.6 Slowly Changing Dimensions (SCD)

5.6.1 What are Slowly Changing Dimensions?

Slowly Changing Dimensions (SCD) are techniques used in data warehousing to manage changes to dimension data over time.

For example, a customer moves from Hyderabad to Bengaluru:

CustomerID | CustomerName | City

101 | Sreehari | Hyderabad

Later:

CustomerID | CustomerName | City

101 | Sreehari | Bengaluru

The key question is:

Should we overwrite Hyderabad, or should we preserve the historical information?

SCD techniques provide different answers.

5.6.2 Why Do We Need SCD?

Operational systems usually care about the current state.

Data warehouses often need to understand historical states.

For example:

Customer 101

2025 → Hyderabad

2026 → Bengaluru

If we simply overwrite the city:

101 | Bengaluru

we lose the information that the customer was previously in Hyderabad.

For historical reporting, we may want:

CustomerID | City | StartDate | EndDate | IsCurrent

101 | Hyderabad | 2025-01-01 | 2026-08-21 | No

101 | Bengaluru | 2026-08-22 | NULL | Yes

This is the essence of SCD Type 2.

5.6.3 Where Does SCD Fit in Medallion Architecture?

SCD is generally associated with dimension modeling, particularly the curated Silver/Gold layers.

A common Fabric architecture is:

Source
  │
  ▼
Bronze
  │
  ▼
Silver
  │
  ▼
Dimension Processing
  │
  ▼
Gold Dimension
  │
  ▼

Fact Tables / Semantic Model

For example:

Bronze_Customers
       │
       ▼
Silver_Customers
       │
       ▼
DimCustomer
       │
       ▼

Power BI

5.6.4 Types of Slowly Changing Dimensions

The most important types are:

For most modern data engineering projects, Type 1 and Type 2 are the most important.

5.6.5 SCD Type 0 — No Change

Type 0 means:

The original value is never changed.

Example:

CustomerID | DateOfBirth

101 | 1988-10-12

If a source system later provides a different date:

101 | 1988-10-13

we continue to keep:

101 | 1988-10-12

When to use?

For attributes that should remain permanently unchanged, such as:

Original registration date

Original customer identifier

Birth date, where the business treats it as immutable

5.6.6 SCD Type 1 — Overwrite

Type 1 is the simplest approach.

When a value changes, overwrite the old value.

Before:

CustomerID | Name | City

101 | Sreehari | Hyderabad

After:

CustomerID | Name | City

101 | Sreehari | Bengaluru

There is no historical record of Hyderabad.

5.6.7 Type 1 Example

Source:

CustomerID | CustomerName | City

101 | Sreehari | Bengaluru

Target:

DimCustomer

Use Delta MERGE:

from delta.tables import DeltaTable

target = DeltaTable.forName(

spark,

"DimCustomer"

)

target.alias("target") \

.merge(

source_df.alias("source"),

"target.CustomerID = source.CustomerID"

) \

.whenMatchedUpdateAll() \

.whenNotMatchedInsertAll() \

.execute()

The existing customer is updated.

5.6.8 Type 1 Architecture

Source
  │
  ▼
Bronze
  │
  ▼
Silver
  │
  ▼
DimCustomer
  │
  ├── Customer 101
  │      City = Bengaluru
  │
  └── Old Hyderabad value lost

Advantage

Simple and efficient.

Disadvantage

Historical changes are lost.

5.6.9 When Should You Use Type 1?

Use Type 1 when:

Historical changes are not required.

Only the current value matters.

Corrections should replace incorrect values.

The attribute is not analytically historical.

Example:

Customer email address

Customer phone number

Corrected spelling

Depending on business requirements, these may be Type 1.

5.6.10 SCD Type 2 — Full History

Type 2 preserves every important change.

Before:

CustomerID | City

101 | Hyderabad

Customer moves:

Hyderabad → Bengaluru

Instead of updating the existing row, we expire the old row and insert a new row.

Result:

Now historical analysis is possible.

5.6.11 Type 2 Columns

A typical SCD Type 2 dimension contains:

CustomerKey

CustomerID

CustomerName

City

Country

StartDate

EndDate

IsCurrent

Sometimes also:

HashValue

CreatedTimestamp

UpdatedTimestamp

SourceSystem

5.6.12 Business Key vs Surrogate Key

This is extremely important.

Business Key

Comes from the source system.

CustomerID = 101

Surrogate Key

Generated by the warehouse.

CustomerKey = 5001

Example:

Notice:

CustomerID = 101

is the same, but:

CustomerKey

is different.

This allows the warehouse to represent multiple historical versions of the same business entity.

5.6.13 Why Do We Need a Surrogate Key?

Suppose a customer has:

CustomerID = 101

History:

2025 → Hyderabad

2026 → Bengaluru

Fact transactions:

OrderDate | Customer

2025-06-10 | 101

2026-08-22 | 101

With surrogate keys:

OrderDate | CustomerKey

2025-06-10 | 5001

2026-08-22 | 5027

Now historical reports can correctly associate each transaction with the customer's state at that time.

5.6.14 Type 2 Table Example

DimCustomer

CustomerKey | CustomerID | Name | City | StartDate | EndDate | IsCurrent

------------|------------|----------|------------|------------|------------|----------

5001 | 101 | Sreehari | Hyderabad | 2025-01-01 | 2026-08-21 | 0

5027 | 101 | Sreehari | Bengaluru | 2026-08-22 | NULL | 1

5.6.15 How Type 2 Works

Suppose the current dimension contains:

101 | Hyderabad | 2025-01-01 | NULL | 1

New source:

101 | Bengaluru

Detect change:

Old City ≠ New City

Then:

Step 1

Expire old row:

EndDate = 2026-08-21

IsCurrent = 0

Step 2

Insert new row:

StartDate = 2026-08-22

EndDate = NULL

IsCurrent = 1

Result:

101 | Hyderabad | 2025-01-01 | 2026-08-21 | 0

101 | Bengaluru | 2026-08-22 | NULL | 1

5.6.16 Type 2 Flow

                 Source
                    │
                    ▼
                 Silver
                    │
                    ▼
           Compare with Dimension
                    │
             ┌──────┴──────┐
             │             │
         No Change       Changed
             │             │
             ▼             ▼
           Ignore       Expire Old
                           │
                           ▼
                       Insert New
                           │
                           ▼

DimCustomer

5.6.17 Detecting Changes

You can compare individual columns:

change_condition = (

(col("target.City") != col("source.City")) |

(col("target.Country") != col("source.Country")) |

(col("target.CustomerName") != col("source.CustomerName"))

)

But NULL-safe comparisons are important.

A more robust approach is often to generate a hash of the tracked attributes.

5.6.18 Hash-Based Change Detection

Create a hash from attributes:

from pyspark.sql.functions import sha2, concat_ws, coalesce, lit

source_df = source_df.withColumn(

"HashValue",

sha2(

concat_ws(

"||",

coalesce(col("CustomerName"), lit("")),

coalesce(col("City"), lit("")),

coalesce(col("Country"), lit(""))

),

256

)

)

Target has its own HashValue.

Then:

Source Hash ≠ Target Hash

means a tracked attribute changed.

This becomes especially useful when dimensions contain many attributes.

5.6.19 Type 2 with Delta Lake

Fabric Lakehouse tables can use Delta functionality for SCD processing.

Conceptually:

Silver_Customers
       │
       ▼
   Detect Changes
       │
       ▼
 DimCustomer
       │
       ├── UPDATE old version
       │
       └── INSERT new version

Delta MERGE is commonly used as part of this implementation.

5.6.20 Important Type 2 MERGE Consideration

A common mistake is assuming one MERGE operation automatically handles every Type 2 requirement.

A Type 2 change generally requires two logical actions:

1. Expire the existing current record

2. Insert the new version

A robust implementation often uses a staged source dataset and carefully designed Delta operations rather than blindly using:

.whenMatchedUpdateAll()

because that would overwrite the historical row and effectively behave like Type 1.

5.6.21 Simplified Type 2 Implementation

Assume:

DimCustomer

contains:

CustomerKey

CustomerID

CustomerName

City

StartDate

EndDate

IsCurrent

First identify changed customers.

from pyspark.sql.functions import (
    col,
    current_date,
    lit

)

current_df = spark.table(
    "DimCustomer"

).filter(

col("IsCurrent") == True

)

changed_df = source_df.alias("source") \

    .join(
        current_df.alias("target"),
        col("source.CustomerID") ==
        col("target.CustomerID"),
        "inner"
    ) \
    .filter(
        col("source.City") !=
        col("target.City")
    )

This is a simplified example; production logic should handle NULL-safe comparisons and multiple tracked attributes.

5.6.22 Expire the Existing Row

Conceptually:

target = DeltaTable.forName(

spark,

"DimCustomer"

)

target.alias("target") \

.merge(

changed_df.alias("source"),

"""

target.CustomerID = source.CustomerID

AND target.IsCurrent = true

"""

) \

.whenMatchedUpdate(

        set={
            "EndDate": "current_date()",
            "IsCurrent": "false"
        }
    ) \
    .execute()

Now:

101 | Hyderabad | 2025-01-01 | 2026-08-21 | false

5.6.23 Insert the New Version

Then insert the new record:

from pyspark.sql.functions import current_date, lit

new_versions = changed_df.select(

"CustomerID",

"CustomerName",

"City"

).withColumn(

"StartDate",

    current_date()

).withColumn(

"EndDate",

lit(None).cast("date")

).withColumn(

"IsCurrent",

    lit(True)

)

Write the new version:

new_versions.write \

    .format("delta") \
    .mode("append") \
    .saveAsTable(
        "DimCustomer"
    )

In a production implementation, surrogate-key generation and concurrency/idempotency must also be designed carefully.

5.6.24 Type 3 — Limited History

Type 3 stores the current value and previous value.

Example:

CustomerID | CurrentCity | PreviousCity

101 | Bengaluru | Hyderabad

When the customer moves again:

CustomerID | CurrentCity | PreviousCity

101 | Chennai | Bengaluru

Hyderabad is lost.

So Type 3 stores only limited history.

5.6.25 Type 3 Example

Before:

101 | Hyderabad | NULL

After first change:

101 | Bengaluru | Hyderabad

After second change:

101 | Chennai | Bengaluru

Advantage

Simple.

Disadvantage

You cannot reconstruct the complete history.

5.6.26 Type 4 — Separate History Table

Type 4 maintains:

Current Dimension

and:

History Table

Example:

DimCustomer

contains current data:

101 | Sreehari | Bengaluru

History:

DimCustomerHistory

101 | Sreehari | Hyderabad | 2025-01-01 | 2026-08-21

101 | Sreehari | Bengaluru | 2026-08-22 | NULL

This separates current-state access from historical data.

5.6.27 Type 6 — Hybrid SCD

Type 6 combines features of:

Type 1

Type 2

Type 3

It is sometimes described as:

Type 1 + Type 2 + Type 3

Example columns:

CustomerID

CurrentCity

HistoricalCity

StartDate

EndDate

IsCurrent

It is more complex and should only be used when the business actually requires the hybrid behavior.

5.6.28 SCD Type Comparison

5.6.29 Example: Employee Department

Suppose:

EmployeeID = 1001

Department = IT

After one year:

Department = Finance

Type 1

1001 | Finance

IT is lost.

Type 2

EmployeeID | Department | StartDate | EndDate | IsCurrent

1001 | IT | 2025-01-01 | 2026-08-21 | 0

1001 | Finance | 2026-08-22 | NULL | 1

Type 3

EmployeeID | CurrentDept | PreviousDept

1001 | Finance | IT

5.6.30 SCD Type 2 and Fact Tables

This is one of the most important concepts.

Suppose:

Customer 101

2025 → Hyderabad

2026 → Bengaluru

Orders:

OrderID | OrderDate | CustomerID

5001 | 2025-05-10 | 101

5002 | 2026-08-22 | 101

Gold fact table should ideally reference the appropriate dimension version:

OrderID | Date | CustomerKey

5001 | 2025-05-10 | 5001

5002 | 2026-08-22 | 5027

Therefore historical reporting can answer:

Where was the customer when the order was placed?

5.6.31 Date Range Lookup

For Type 2 dimensions, the fact record can be matched using:

CustomerID

AND

OrderDate >= StartDate

AND

(OrderDate < EndDate OR EndDate IS NULL)

Example SQL:

SELECT
    f.OrderID,
    f.OrderDate,
    d.CustomerKey
FROM FactSales f
JOIN DimCustomer d
    ON f.CustomerID = d.CustomerID

AND f.OrderDate >= d.StartDate

AND (

f.OrderDate < d.EndDate

OR d.EndDate IS NULL

);

This is called a temporal/range lookup.

5.6.32 Current Record Flag

A common Type 2 pattern:

IsCurrent

Example:

CustomerID | City | IsCurrent

101 | Hyderabad | 0

101 | Bengaluru | 1

Query current customers:

SELECT *
FROM DimCustomer
WHERE IsCurrent = 1;

This makes current-state queries simple.

5.6.33 End Date Convention

There are different conventions.

One approach:

Current row:

StartDate = 2026-08-22

EndDate = NULL

Historical row:

StartDate = 2025-01-01

EndDate = 2026-08-21

Another design uses a far-future date:

EndDate = 9999-12-31

Both can work. Choose one convention and apply it consistently.

5.6.34 Effective Dating

Type 2 is often called effective dating.

Each row has:

StartDate

EndDate

Example:

101 | Hyderabad | 2025-01-01 | 2026-08-21

101 | Bengaluru | 2026-08-22 | NULL

This tells us exactly when each version was effective.

5.6.35 Handling Multiple Changes

Suppose:

2025 → Hyderabad

2026 → Bengaluru

2027 → Chennai

2028 → Mumbai

Type 2:

101 | Hyderabad | 2025 | 2026 | 0

101 | Bengaluru | 2026 | 2027 | 0

101 | Chennai | 2027 | 2028 | 0

101 | Mumbai | 2028 | NULL | 1

The entire history is preserved.

5.6.36 SCD Type 2 with Hash

For dimensions with many tracked attributes:

CustomerName

City

Country

State

PostalCode

Segment

Phone

create:

HashValue

Example:

from pyspark.sql.functions import (
    sha2,
    concat_ws,
    coalesce,
    lit

)

tracked_columns = [
    "CustomerName",
    "City",
    "Country",
    "State",
    "PostalCode"

]

source_df = source_df.withColumn(

"HashValue",

sha2(

concat_ws(

"||",

*[

coalesce(

col(c).cast("string"),

                    lit("")
                )
                for c in tracked_columns
            ]
        ),
        256
    )

)

Then:

Source Hash = Target Hash

       │
       ▼

No change

Source Hash ≠ Target Hash

       │
       ▼

Changed

5.6.37 SCD Type 2 Architecture in Fabric

A practical Fabric implementation:

                   SQL Server
                       │
                       ▼
                Fabric Data Factory
                       │
                       ▼
                    Bronze
                       │
                       ▼
                    Silver
                       │
                       ▼
              SCD Change Detection
                       │
              ┌────────┴────────┐
              ▼                 ▼
          No Change           Changed
              │                 │
              ▼                 ▼
           Ignore          Expire Current
                                │
                                ▼
                          Insert New Version
                                │
                                ▼
                         Gold DimCustomer
                                │
                                ▼
                           Fact Tables
                                │
                                ▼

Power BI

5.6.38 Example End-to-End

Initial customer

CustomerID = 101

Name = Sreehari

City = Hyderabad

Dimension:

CustomerKey | CustomerID | Name | City | StartDate | EndDate | IsCurrent

5001 | 101 | Sreehari | Hyderabad | 2025-01-01 | NULL | 1

Customer moves

Source:

101 | Sreehari | Bengaluru

Detect:

Hyderabad ≠ Bengaluru

Expire:

5001 | 101 | Sreehari | Hyderabad | 2025-01-01 | 2026-08-21 | 0

Insert:

5027 | 101 | Sreehari | Bengaluru | 2026-08-22 | NULL | 1

Final:

CustomerKey | CustomerID | City | StartDate | EndDate | IsCurrent

5001 | 101 | Hyderabad | 2025-01-01 | 2026-08-21 | 0

5027 | 101 | Bengaluru | 2026-08-22 | NULL | 1

5.6.39 Production Best Practices

1. Define which columns are historical

Not every attribute needs Type 2.

Example:

CustomerID → Type 0

CustomerName → Type 1

City → Type 2

CustomerSegment → Type 2

The actual classification should be based on business requirements.

2. Use surrogate keys for Type 2

CustomerKey

should identify the dimension version.

3. Keep a business key

CustomerID

identifies the real-world customer.

4. Use effective dates

StartDate

EndDate

5. Maintain IsCurrent

Makes current-state queries easier.

6. Detect changes reliably

Hash-based comparison is useful for wide dimensions.

7. Make the pipeline idempotent

Rerunning the same batch should not create another identical historical version.

8. Handle late-arriving data

Historical data can arrive after a dimension has already been processed. Your design should define how these cases are handled.

5.6.40 Common Mistakes

Mistake 1 — Using Type 1 when history is required

This permanently loses historical information.

Mistake 2 — Updating a Type 2 row in place

This defeats the purpose of Type 2.

Mistake 3 — Not using surrogate keys

Multiple versions of the same business key become difficult to reference from facts.

Mistake 4 — Multiple current records

A customer should generally have only one:

IsCurrent = 1

record.

Mistake 5 — Overlapping date ranges

Bad:

101 | Hyderabad | Jan 1 | Aug 30

101 | Bengaluru | Aug 20 | NULL

The periods overlap.

Mistake 6 — No change detection

If every ingestion creates a new Type 2 row, the dimension grows unnecessarily.

5.6.41 Validation Queries

Find multiple current records

SELECT
    CustomerID,
    COUNT(*) AS CurrentCount
FROM DimCustomer
WHERE IsCurrent = 1
GROUP BY CustomerID

HAVING COUNT(*) > 1;

Expected:

No rows

Find overlapping records

Conceptually, check whether two versions for the same business key have overlapping effective periods.

This is an important production data-quality check for Type 2 dimensions.

Find missing current records

SELECT

CustomerID

FROM DimCustomer

GROUP BY CustomerID

HAVING SUM(

CASE

WHEN IsCurrent = 1 THEN 1

ELSE 0

END

) = 0;

5.6.42 Interview Questions

1. What is SCD?

Slowly Changing Dimension is a data-warehouse technique for handling changes to dimension attributes over time.

2. What is SCD Type 1?

Overwrite the existing value.

Hyderabad → Bengaluru

Only Bengaluru remains.

3. What is SCD Type 2?

Preserve the complete history by expiring the old record and inserting a new version.

4. What columns are commonly used in Type 2?

Surrogate Key

Business Key

StartDate

EndDate

IsCurrent

5. What is a surrogate key?

A warehouse-generated key that uniquely identifies a particular dimension version.

6. What is a business key?

The identifier from the source/business system, such as:

CustomerID = 101

7. Why use Type 2?

When historical changes must be preserved for accurate historical reporting.

8. What is Type 3?

It stores limited history, commonly current and previous values.

9. What is the difference between Type 1 and Type 2?

Type 1 → Overwrite

Type 2 → Preserve history

10. How do you detect a Type 2 change?

Compare tracked attributes or use a hash of those attributes.

5.6.43 SCD Cheat Sheet
┌────────────────────────────────────────────┐
│             SCD TYPES                      │
├────────┬───────────────────────────────────┤
│ Type 0 │ Never change                      │
│ Type 1 │ Overwrite                        │
│ Type 2 │ Full historical versions         │
│ Type 3 │ Current + previous value         │
│ Type 4 │ Separate history table           │
│ Type 6 │ Hybrid Type 1 + 2 + 3            │
└────────┴───────────────────────────────────┘

Most important

TYPE 1

Old value → Replaced

TYPE 2

Old value → Preserved
            +

New value → Inserted

5.6.44 Final Example

Imagine a customer changes location three times:

2024 → Chennai

2025 → Hyderabad

2026 → Bengaluru

Type 1

101 | Bengaluru

Type 2

CustomerID | City | Start | End | Current

101 | Chennai | 2024 | 2025 | 0

101 | Hyderabad | 2025 | 2026 | 0

101 | Bengaluru | 2026 | NULL | 1

Type 3

CustomerID | CurrentCity | PreviousCity

101 | Bengaluru | Hyderabad

The key difference is how much history the warehouse needs to retain.

SCD Type 1 answers "What is the customer's value now?" while SCD Type 2 answers "What was the customer's value at a particular point in time?"

↑ Back to top

↑ Back to top

5.7 Joins

5.7.1 What is a Join?

A JOIN combines rows from two or more datasets based on a related column or condition.

In a Fabric data engineering project, joins are commonly used to combine Silver tables to create integrated datasets for the Gold layer.

For example:

Silver_Orders
      │
      │ CustomerID
      ▼
Silver_Customers
      │
      ▼
   JOIN
      │
      ▼

Integrated Sales Data

Example:

Orders

Customers

After joining:

5.7.2 Why Are Joins Important?

Real-world data is normally distributed across multiple tables.

For an e-commerce system:

Customers
     │
     ├─────────────┐
     ▼             ▼
  Orders        Addresses
     │
     ▼
OrderItems
     │
     ▼

Products

To produce business information, these datasets must often be combined.

For example:

Orders
   +
Customers
   +
Products
   +
Stores
   │
   ▼

Gold Sales Dataset

5.7.3 Types of Joins

The most important joins are:

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

CROSS JOIN

LEFT SEMI JOIN

LEFT ANTI JOIN

The first four are the most important for interviews and everyday data engineering.

5.7.4 INNER JOIN

An INNER JOIN returns only records that exist in both datasets.

Table A       Table B
   │             │
   └─────┬───────┘
         ▼

Matching

records

Example:

Orders

Customers

INNER JOIN:

SELECT

o.OrderID,

o.CustomerID,

c.Name

FROM Orders o

INNER JOIN Customers c

ON o.CustomerID = c.CustomerID;

Result:

Customer 103 is excluded because it doesn't exist in Customers.

Customer 104 is also excluded because it doesn't have a matching order.

5.7.5 INNER JOIN in PySpark

orders = spark.table(
    "Silver_Orders"

)

customers = spark.table(
    "Silver_Customers"

)

result = orders.join(

customers,

orders.CustomerID ==

customers.CustomerID,

"inner"

)

A simpler syntax is possible when the join column has the same name:

result = orders.join(

customers,

"CustomerID",

"inner"

)

5.7.6 LEFT JOIN

A LEFT JOIN returns:

All rows from the left table

Matching rows from the right table

NULL when no match exists

LEFT TABLE
    │
    ├──────────────┐
    │              │
    ▼              ▼
 All rows       Matches
    │              │
    └──────┬───────┘
           ▼

Result

SQL:

SELECT

o.OrderID,

o.CustomerID,

c.Name

FROM Orders o

LEFT JOIN Customers c

ON o.CustomerID = c.CustomerID;

Result:

Order 1003 remains even though customer 103 doesn't exist in the customer table.

5.7.7 LEFT JOIN in PySpark

result = orders.join(

customers,

orders.CustomerID ==

customers.CustomerID,

"left"

)

When is LEFT JOIN useful?

Very often in data engineering.

For example:

Keep every sales transaction, even if the customer master doesn't contain a matching customer.

5.7.8 RIGHT JOIN

A RIGHT JOIN keeps all records from the right table.

SELECT

o.OrderID,

c.CustomerID,

c.Name

FROM Orders o

RIGHT JOIN Customers c

ON o.CustomerID = c.CustomerID;

Result:

Customer 104 remains even though there is no order.

In practice, many developers rewrite a RIGHT JOIN as a LEFT JOIN by switching table order because LEFT JOIN is often easier to reason about.

5.7.9 FULL OUTER JOIN

A FULL OUTER JOIN returns:

Matching rows

Unmatched rows from the left

Unmatched rows from the right

LEFT ONLY
   +
MATCHING
   +

RIGHT ONLY

=

FULL OUTER

SQL:

SELECT

o.OrderID,

c.CustomerID,

c.Name

FROM Orders o

FULL OUTER JOIN Customers c

ON o.CustomerID = c.CustomerID;

Result:

5.7.10 FULL OUTER JOIN Use Cases

Useful for:

Data reconciliation

Comparing two systems

Identifying missing records

Source-to-target validation

Migration validation

For example:

SQL Server Customers
        │
        │ FULL OUTER JOIN
        ▼

Fabric Customers

Then identify:

Only in SQL Server

Only in Fabric

In both

5.7.11 CROSS JOIN

A CROSS JOIN creates every possible combination.

Table A:

A

1

2

Table B:

B

X

Y

Z

Result:

1 X

1 Y

1 Z

2 X

2 Y

2 Z

Total:

2 × 3 = 6 rows

SQL:

SELECT *

FROM A

CROSS JOIN B;

PySpark:

result = df1.crossJoin(df2)

Warning

CROSS JOIN can produce a huge number of rows.

Use it intentionally.

5.7.12 LEFT SEMI JOIN

A LEFT SEMI JOIN returns rows from the left table where a matching record exists in the right table.

It returns columns only from the left table.

Example:

result = orders.join(

customers,

"CustomerID",

"left_semi"

)

Meaning:

Give me orders whose CustomerID exists in Customers.

5.7.13 LEFT ANTI JOIN

A LEFT ANTI JOIN returns rows from the left table that do not have a match in the right table.

result = orders.join(

customers,

"CustomerID",

"left_anti"

)

Meaning:

Give me orders whose CustomerID does not exist in Customers.

This is extremely useful for data-quality checks.

5.7.14 LEFT ANTI JOIN Example

Orders:

OrderID | CustomerID

1001 | 101

1002 | 102

1003 | 103

Customers:

CustomerID

101

102

Anti join:

OrderID | CustomerID

1003 | 103

This identifies an orphan customer reference.

5.7.15 Join Comparison

5.7.16 Join Diagram

           INNER
      ┌─────────────┐
      │    A ∩ B    │
      └─────────────┘

            LEFT
      ┌───────────────┐
      │ A + A ∩ B     │
      └───────────────┘

           RIGHT
      ┌───────────────┐
      │ B + A ∩ B     │
      └───────────────┘

           FULL
      ┌─────────────────┐
      │ A ∪ B           │
      └─────────────────┘

5.7.17 Joining Multiple Tables

Real projects often require multiple joins.

Suppose:

Orders

Customers

Products

Stores

Architecture:

                 Customers
                     │
                     │ CustomerID
                     ▼
Orders ───────────► JOIN
  │
  │ ProductID
  ▼
Products
  │
  │ StoreID
  ▼

Stores

PySpark:

result = orders \

    .join(
        customers,
        "CustomerID",
        "left"
    ) \
    .join(
        products,
        "ProductID",
        "left"
    ) \
    .join(
        stores,
        "StoreID",
        "left"
    )

5.7.18 Column Ambiguity

Suppose both tables contain:

CustomerID

Country

After joining, referencing:

col("Country")

may be ambiguous.

Use aliases.

orders = orders.alias("o")

customers = customers.alias("c")

result = orders.join(

customers,

col("o.CustomerID") ==

col("c.CustomerID"),

"left"

)

Select explicitly:

result = result.select(

col("o.OrderID"),

col("o.CustomerID"),

col("o.Amount"),

col("c.CustomerName"),

    col("c.Country")

)

5.7.19 SQL Aliases

The same concept applies to SQL:

SELECT

o.OrderID,

o.CustomerID,

o.Amount,

c.CustomerName,

c.Country

FROM Silver_Orders o

LEFT JOIN Silver_Customers c

ON o.CustomerID = c.CustomerID;

Aliases make complex queries much easier to understand.

5.7.20 Join on Multiple Columns

Sometimes one column isn't enough.

Example:

OrderID

LineNumber

Use:

result = orders.join(

order_items,

(

(orders.OrderID ==

order_items.OrderID) &

(orders.LineNumber ==

order_items.LineNumber)

),

"inner"

)

SQL:

SELECT *

FROM Orders o

JOIN OrderItems i

ON o.OrderID = i.OrderID

AND o.LineNumber = i.LineNumber;

5.7.21 Join on Different Column Names

Suppose:

Orders

CustomerID

Customers

CustID

PySpark:

result = orders.join(

customers,

orders.CustomerID ==

customers.CustID,

"left"

)

SQL:

SELECT *
FROM Orders o
JOIN Customers c
    ON o.CustomerID = c.CustID;

5.7.22 Non-Equi Joins

Not all joins use =.

For example:

OrderAmount

and customer segments:

MinAmount

MaxAmount

Segment

Join condition:

result = orders.join(

segments,

(

(orders.Amount >= segments.MinAmount) &

(orders.Amount < segments.MaxAmount)

),

"left"

)

This is a range/non-equi join.

5.7.23 SCD Type 2 Join

This is particularly important in data warehousing.

Suppose:

DimCustomer

contains:

And:

FactSales

contains:

Join using date validity:
SELECT
    f.OrderID,
    f.OrderDate,
    d.CustomerKey,
    d.City
FROM FactSales f
JOIN DimCustomer d
    ON f.CustomerID = d.CustomerID

AND f.OrderDate >= d.StartDate

AND (

f.OrderDate < d.EndDate

OR d.EndDate IS NULL

);

Result:

This is a temporal/range join.

5.7.24 Join and Duplicate Explosion

One of the most important practical problems with joins is unexpected row multiplication.

Suppose:

Orders

OrderID | CustomerID

1001 | 101

Customers

CustomerID | Name

101 | John

101 | John

A join produces:

1001 | 101 | John

1001 | 101 | John

One order became two rows.

If you then calculate:

sum("Amount")

you could double-count the revenue.

5.7.25 Preventing Duplicate Explosion

Before joining, understand the cardinality.

Check:

customers.groupBy(

"CustomerID"

).count().filter(

col("count") > 1

).show()

If the customer table is supposed to contain one row per CustomerID, duplicate records should be resolved before the join.

5.7.26 Join Cardinality

Common relationships:

1 : 1

1 : Many

Many : 1

Many : Many

Example:

Customer → Orders

1 Customer
   │
   ├── Order 1
   ├── Order 2
   └── Order 3

1 : Many

This is normal.

But:

Orders

↕

OrderItems

is also typically:

1 Order : Many OrderItems

Understanding cardinality is essential before joining.

5.7.27 Many-to-Many Joins

Many-to-many relationships can create large row multiplication.

Example:

Students

↕

Courses

A bridge table is often used:

Students
    │
    ▼
StudentCourse
    │
    ▼

Courses

Similarly in business systems:

Customers
    │
    ▼
CustomerProduct
    │
    ▼

Products

5.7.28 Broadcast Join

Spark can optimize joins when one dataset is small enough to broadcast.

Example:

from pyspark.sql.functions import broadcast

result = large_df.join(

broadcast(small_df),

"ProductID",

"left"

)

Conceptually:

Small Table
    │
    ├──► Executor 1
    ├──► Executor 2
    ├──► Executor 3
    └──► Executor 4
Large Table
    │
    ▼

Join locally

This can avoid a large shuffle.

Important: Don't broadcast a table just because it is "smaller." It must be appropriately small for the available executor memory and workload.

5.7.29 Shuffle Join

For large datasets, Spark may perform a shuffle:

Partition A ──┐
Partition B ──┼── Shuffle ──► Join
Partition C ──┤
Partition D ──┘

Shuffles can be expensive because data moves across the cluster.

5.7.30 Join Performance Best Practices

1. Filter before joining

orders = orders.filter(

col("OrderDate") >= "2026-01-01"

)

2. Select only required columns

customers = customers.select(

"CustomerID",

"CustomerName",

"Country"

)

3. Remove unnecessary duplicates

4. Use broadcast for genuinely small tables

broadcast(dim_product)

5. Avoid unnecessary many-to-many joins

6. Understand data skew

7. Use appropriate join keys

Poor join keys can result in large shuffles.

5.7.31 Data Skew

Suppose:

CustomerID = 101

appears in 50 million records.

Most other customers have only 100 records.

One Spark partition may receive far more data than others.

Partition 1 → 100K rows

Partition 2 → 120K rows

Partition 3 → 110K rows

Partition 4 → 50M rows ← SKew

This can make the join slow.

Data skew needs special handling depending on the workload.

5.7.32 Join Optimization Strategy

A practical approach:

1. Filter

↓

2. Select required columns

↓

3. Validate key uniqueness

↓

4. Determine cardinality

↓

5. Choose join type

↓

6. Check data size

↓

7. Consider broadcast

↓

8. Validate row counts

5.7.33 Join Validation

After a join, always check row counts.

Before:

print("Orders:", orders.count())

After:

result = orders.join(

customers,

"CustomerID",

"left"

)

print(
    "Joined:",
    result.count()

)

If you expected a 1-to-1 enrichment but:

Orders = 1,000,000

Joined = 1,500,000

you probably have a cardinality problem.

5.7.34 Orphan Record Detection

A LEFT ANTI JOIN is excellent for finding missing references.

orphan_orders = orders.join(

customers,

"CustomerID",

"left_anti"

)

Count:

print(
    orphan_orders.count()

)

This gives:

Orders whose CustomerID doesn't exist in the customer dimension.

5.7.35 Reconciliation with FULL OUTER JOIN

Suppose you want to compare two systems:

SQL Server

vs

Fabric

comparison = source_df.alias("source") \

    .join(
        target_df.alias("target"),
        col("source.ID") ==
        col("target.ID"),
        "full"
    )

Then classify:

comparison = comparison.withColumn(

"Status",

when(

col("source.ID").isNull(),

"TARGET_ONLY"

)

.when(

col("target.ID").isNull(),

"SOURCE_ONLY"

)

.otherwise(

"MATCHED"

)

)

This is useful for ETL validation.

5.7.36 Complete Fabric Example

Suppose we have:

Silver_Orders

Silver_Customers

Silver_Products

Orders

OrderID

CustomerID

ProductID

Quantity

Amount

Customers

CustomerID

CustomerName

Country

Products

ProductID

ProductName

Category

Join:
orders = spark.table(
    "Silver_Orders"

).alias("o")

customers = spark.table(
    "Silver_Customers"

).alias("c")

products = spark.table(
    "Silver_Products"

).alias("p")

gold_df = orders \

    .join(
        customers,
        col("o.CustomerID") ==
        col("c.CustomerID"),
        "left"
    ) \
    .join(
        products,
        col("o.ProductID") ==
        col("p.ProductID"),
        "left"
    )
Select:

gold_df = gold_df.select(

col("o.OrderID"),

col("o.CustomerID"),

col("c.CustomerName"),

col("c.Country"),

col("o.ProductID"),

col("p.ProductName"),

col("p.Category"),

col("o.Quantity"),

    col("o.Amount")

)

Now we have:

Gold-ready integrated data

5.7.37 Join + Aggregation

Now aggregate:

from pyspark.sql.functions import sum

sales_summary = gold_df.groupBy(

"Country",

"Category"

).agg(

sum("Amount").alias(

"Revenue"

)

)

Result:

This is a common Silver → Gold transformation.

5.7.38 Join Execution Concept

When Spark executes:

df1.join(

df2,

"CustomerID"

)

Spark's execution engine determines how to execute it.

Conceptually:

Join

/ \

/ \

        df1          df2
         │            │
         ▼            ▼
    Partitions     Partitions
         │            │
         └─────┬──────┘
               ▼

JOIN

The physical execution may involve:

Broadcast

Shuffle

Sort

Hash-based joins

depending on the query plan and data characteristics.

5.7.39 Inspecting the Query Plan

You can inspect Spark's plan:

result.explain(True)

This helps identify:

Broadcast joins

Exchange/shuffle operations

Sorts

Join strategy

For performance troubleshooting, this is extremely useful.

5.7.40 Common Join Mistakes

Mistake 1 — Joining on the wrong key

CustomerID ≠ CustomerName

Always use the appropriate business/technical key.

Mistake 2 — Unexpected duplicates

Usually caused by incorrect cardinality or duplicate keys.

Mistake 3 — Using INNER JOIN when unmatched records matter

You may unintentionally lose records.

Mistake 4 — Broadcasting a large table

This can cause memory pressure.

Mistake 5 — Selecting * from multiple tables

You may get duplicate/ambiguous columns.

Mistake 6 — Ignoring NULL join keys

NULL does not match NULL using ordinary equality joins.

Mistake 7 — Joining huge datasets without filtering

This can cause expensive shuffles.

5.7.41 Interview Questions

1. What is a JOIN?

A JOIN combines records from two or more datasets based on a related condition.

2. What is INNER JOIN?

Returns only matching records from both tables.

3. What is LEFT JOIN?

Returns every record from the left table and matching records from the right.

4. What is FULL OUTER JOIN?

Returns matching and unmatched records from both sides.

5. What is LEFT ANTI JOIN?

Returns records from the left dataset that don't have a match in the right dataset.

6. What is LEFT SEMI JOIN?

Returns records from the left dataset that have a matching record in the right dataset, without returning right-side columns.

7. What is a broadcast join?

A Spark optimization where a sufficiently small dataset is distributed to executors so the large dataset can be joined without a large shuffle.

8. What is data skew?

When a small number of join-key values contain a disproportionately large amount of data, causing uneven partition workloads.

9. How can joins create duplicate rows?

If the join key isn't unique on one or both sides, one input row can match multiple rows.

10. How do you find orphan records?

Use a LEFT ANTI JOIN:

df1.join(

df2,

"ID",

"left_anti"

)

5.7.42 Join Cheat Sheet

INNER

A ∩ B

→ Only matches

LEFT

A + matches

→ Keep all A

RIGHT

B + matches

→ Keep all B

FULL

A + B

→ Keep everything

LEFT SEMI

A where match exists in B

LEFT ANTI

A where match does NOT exist in B

CROSS

A × B

→ Every combination

5.7.43 Practical Fabric Data Engineering Pattern

For a typical sales project:

               Silver_Orders
                     │
                     │ CustomerID
                     ▼
             Silver_Customers
                     │
                     │
                     ▼
                   JOIN
                     │
                     │ ProductID
                     ▼
             Silver_Products
                     │
                     ▼
               Integrated Data
                     │
                     ▼
                 GROUP BY
                     │
                     ▼
                  GOLD
                     │
                     ▼

Power BI

The key rules to remember:

Know your join key.

Know the cardinality before joining.

Check for duplicate keys.

Choose the correct join type.

Validate row counts after the join.

Use LEFT ANTI for orphan detection.

Use FULL OUTER for reconciliation.

Use broadcast only when the smaller side is genuinely small enough.

Avoid unnecessary columns before large joins.

Define the grain of the output after joining.

A technically correct JOIN can still produce incorrect business results if the join key, cardinality, or resulting grain is wrong.

↑ Back to top

↑ Back to top

5.8 Window Functions

A window function performs calculations across related rows without collapsing those rows.

Aggregation vs Window

GROUP BY:

1000

2000

3000

↓

6000

WINDOW:

1000 → 6000

2000 → 6000

3000 → 6000

Window functions are widely used for ranking, running totals, previous/next row analysis, deduplication, Top-N analysis, SCD processing, and time-series calculations.

5.8.1 Basic Syntax

SQL

FUNCTION() OVER (

PARTITION BY column

ORDER BY column

)

PySpark

from pyspark.sql.window import Window

window = Window \

.partitionBy("CustomerID") \

.orderBy("OrderDate")

A window can contain:

PARTITION BY — logical groups

ORDER BY — row sequence

ROWS/RANGE — window frame

5.8.2 Common Window Functions

5.8.3 ROW_NUMBER()

Assigns a sequential number within each partition.

from pyspark.sql.functions import row_number

window = Window \

.partitionBy("CustomerID") \

.orderBy("OrderDate")

df = df.withColumn(
    "RowNumber",
    row_number().over(window)

)

Result:

Latest Record / Deduplication

window = Window \

.partitionBy("CustomerID") \

.orderBy(col("UpdatedDate").desc())

latest = df.withColumn(
    "rn",
    row_number().over(window)

).filter(

col("rn") == 1

).drop("rn")

This is one of the most common Fabric/Spark patterns.

5.8.4 RANK vs DENSE_RANK vs ROW_NUMBER

For values:

100

100

90

80

RANK() leaves gaps after ties; DENSE_RANK() does not.

5.8.5 Top-N per Group

Top 3 products per category:

window = Window \

.partitionBy("Category") \

.orderBy(col("Sales").desc())

ranked = df.withColumn(
    "Rank",
    row_number().over(window)

)

top3 = ranked.filter(

col("Rank") <= 3

)

This is different from a global LIMIT 3.

5.8.6 LAG()

Returns a previous row's value.

from pyspark.sql.functions import lag

window = Window.orderBy("Date")

df = df.withColumn(
    "PreviousSales",
    lag("Sales", 1).over(window)

)

Useful for:

Month-over-month growth

Change detection

Previous transaction comparison

SCD analysis

5.8.7 LEAD()

Returns the next row's value.

from pyspark.sql.functions import lead

df = df.withColumn(
    "NextSales",
    lead("Sales", 1).over(window)

)

LAG → Previous row

LEAD → Next row

5.8.8 Running Total

from pyspark.sql.functions import sum

window = Window \

.orderBy("Date") \

.rowsBetween(

Window.unboundedPreceding,

Window.currentRow

)

df = df.withColumn(
    "RunningTotal",
    sum("Sales").over(window)

)

For:

1000

1500

1200

Result:

1000

2500

3700

Running Total by Customer

window = Window \

.partitionBy("CustomerID") \

.orderBy("OrderDate") \

.rowsBetween(

Window.unboundedPreceding,

Window.currentRow

)

df = df.withColumn(
    "CustomerRunningTotal",
    sum("Amount").over(window)

)

The calculation resets for each customer.

5.8.9 Moving Average

A 3-row moving average:

from pyspark.sql.functions import avg

window = Window \

.orderBy("Date") \

.rowsBetween(-2, 0)

df = df.withColumn(
    "MovingAverage",
    avg("Sales").over(window)

)

rowsBetween(-2, 0) means:

Previous 2 rows + Current row

5.8.10 Customer Total Without GROUP BY

customer_window = Window.partitionBy(

"CustomerID"

)

df = df.withColumn(
    "CustomerRevenue",
    sum("Amount").over(customer_window)

)

Unlike GROUP BY, every original transaction remains.

You can then calculate each transaction's contribution:

df = df.withColumn(
    "RevenuePercentage",
    (
        col("Amount") /
        col("CustomerRevenue")
    ) * 100

)

Production code should handle NULL and zero denominators appropriately.

5.8.11 Window Functions for SCD

LAG() can identify changes between successive versions:

window = Window \

.partitionBy("CustomerID") \

.orderBy("UpdatedDate")

df = df.withColumn(
    "PreviousCity",
    lag("City").over(window)

)

Then:

df = df.withColumn(
    "CityChanged",
    when(
        col("PreviousCity").isNull(),
        False
    ).when(
        col("City") != col("PreviousCity"),
        True
    ).otherwise(False)

)

For production use, NULL-safe comparisons should be considered carefully.

5.8.12 Window Functions vs GROUP BY

Example:

GROUP BY
SELECT
    CustomerID,
    SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerID;

Returns one row per customer.

Window

SELECT
    OrderID,
    CustomerID,
    Amount,
    SUM(Amount) OVER (
        PARTITION BY CustomerID
    ) AS CustomerTotal
FROM Orders;

Returns every order plus the customer's total.

5.8.13 Complete PySpark Example

Suppose Silver_Sales contains:

OrderID

CustomerID

OrderDate

Amount

Create order number, previous amount, running revenue, and customer revenue:

from pyspark.sql.functions import (
    row_number,
    lag,
    sum

)

from pyspark.sql.window import Window

df = spark.table("Silver_Sales")

ordered_window = Window \

.partitionBy("CustomerID") \

.orderBy("OrderDate")

running_window = ordered_window \

.rowsBetween(

Window.unboundedPreceding,

Window.currentRow

)

df = df \
    .withColumn(
        "OrderNumber",
        row_number().over(ordered_window)
    ) \
    .withColumn(
        "PreviousAmount",
        lag("Amount").over(ordered_window)
    ) \
    .withColumn(
        "RunningRevenue",
        sum("Amount").over(running_window)
    ) \
    .withColumn(
        "CustomerRevenue",
        sum("Amount").over(
            Window.partitionBy("CustomerID")
        )
    )

Result:

5.8.14 SQL Examples

Ranking

SELECT
    ProductID,
    Category,
    Sales,
    RANK() OVER (
        PARTITION BY Category
        ORDER BY Sales DESC
    ) AS SalesRank
FROM ProductSales;

Previous value

SELECT
    SaleDate,
    Sales,
    LAG(Sales) OVER (
        ORDER BY SaleDate
    ) AS PreviousSales
FROM DailySales;

Running total

SELECT
    OrderDate,
    Sales,
    SUM(Sales) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW
    ) AS RunningTotal
FROM DailySales;

5.8.15 Performance Considerations

Window functions can be expensive on large datasets because Spark may need to:

Partition

↓

Shuffle

↓

Sort

↓

Calculate

Best practices:

Filter early.

Select only required columns.

Avoid unnecessarily wide windows.

Be careful with high-cardinality partitions.

Avoid repeated identical window definitions where practical.

Inspect the execution plan:

df.explain(True)

5.8.16 Hands-On Lab

Using Silver_Sales, implement:

Exercise 1 — Order number

row_number().over(window)

Exercise 2 — Previous order amount

lag("Amount").over(window)

Exercise 3 — Next order amount

lead("Amount").over(window)

Exercise 4 — Customer running revenue

sum("Amount").over(running_window)

Exercise 5 — Top 3 products per category

Use:

row_number()

or dense_rank() depending on the business requirement.

Exercise 6 — Monthly sales growth

Use:

lag()

to compare the current month with the previous month.

5.8.17 Interview Questions

1. What is a window function? A function that calculates across related rows while preserving the individual rows.

2. What does PARTITION BY do? Divides data into logical groups.

3. What does ORDER BY do? Defines row sequence within each partition.

4. Difference between ROW_NUMBER, RANK, and DENSE_RANK?

ROW_NUMBER → Unique sequence

RANK → Ties + gaps

DENSE_RANK → Ties + no gaps

5. What does LAG() do? Returns a previous row's value.

6. What does LEAD() do? Returns a subsequent row's value.

7. How do you calculate a running total?

sum("Amount").over(

Window

.orderBy("OrderDate")

.rowsBetween(

Window.unboundedPreceding,

Window.currentRow

)

)

8. How do you find the latest record per customer?

window = Window \

.partitionBy("CustomerID") \

.orderBy(col("UpdatedDate").desc())

df.withColumn(
    "rn",
    row_number().over(window)

).filter(

col("rn") == 1

)

9. Why can window functions be expensive? They may require partitioning, shuffling, and sorting large datasets.

5.8.18 Window Function Cheat Sheet

ROW_NUMBER()
    ↓

Unique sequence

RANK()
    ↓

Ranking + gaps

DENSE_RANK()
    ↓

Ranking without gaps

LAG()
    ↓

Previous row

LEAD()
    ↓

Next row

SUM() OVER()

↓

Running / partition total

AVG() OVER()

↓

Moving / partition average

FIRST_VALUE()
    ↓

First value

LAST_VALUE()
    ↓

Last value

Key Pattern

window = Window \

.partitionBy("CustomerID") \

.orderBy("OrderDate")

df = df.withColumn(
    "RowNumber",
    row_number().over(window)

)

The window definition determines which rows participate; the function determines what calculation is performed.

GROUP BY summarizes rows; window functions analyze related rows while keeping the original row-level detail.

↑ Back to top

↑ Back to top

5.9 Build Medallion Architecture

5.9.1 What is Medallion Architecture?

Medallion Architecture is a layered data architecture used to progressively improve data quality as data moves through the platform.

In Microsoft Fabric, a common implementation is:

                    SOURCE SYSTEMS
                         │
          ┌──────────────┼──────────────┐
          ▼              ▼              ▼
      SQL Server      REST API        Files
          │              │              │
          └──────────────┼──────────────┘
                         ▼
                    ┌─────────┐
                    │ BRONZE  │
                    │  RAW    │
                    └────┬────┘
                         │
                    Clean & Validate
                         ▼
                    ┌─────────┐
                    │ SILVER  │
                    │ CLEAN   │
                    └────┬────┘
                         │
                Business Transformations
                         ▼
                    ┌─────────┐
                    │  GOLD   │
                    │ BUSINESS │
                    └────┬────┘
                         │
                         ▼
                  Semantic Model
                         │
                         ▼

Power BI

The core principle is:

Raw data → Clean data → Business-ready data

5.9.2 Why Use Medallion Architecture?

Without layers, a data platform can become difficult to maintain:

SQL

REST

CSV

Excel
   │
   ├──── transformations
   ├──── joins
   ├──── calculations
   ├──── business rules
   └──── reporting
             │
             ▼

One huge table

Problems:

Difficult troubleshooting

Difficult data lineage

Hard to reproduce results

Raw data may be lost

Business logic becomes mixed with ingestion

Changes become risky

Medallion architecture separates responsibilities.

5.9.3 The Three Layers

Bronze

Purpose: Store source data with minimal modification.

Source → Bronze

Typical characteristics:

Raw

Source-aligned

Append-oriented where appropriate

Includes ingestion metadata

Used for replay/reprocessing

Silver

Purpose: Create trusted, cleaned, standardized data.

Bronze → Silver

Typical transformations:

Data cleansing

Type conversion

Deduplication

Standardization

Joins

Data validation

SCD processing

Gold

Purpose: Create business-ready datasets.

Silver → Gold

Typical transformations:

Aggregations

KPIs

Business rules

Dimensional models

Fact tables

Reporting datasets

5.9.4 Bronze Layer

Imagine a source CSV:

customer_id,name,country,amount

101," John ",india,2500

102,"Anita",INDIA,3200

101," John ",india,2500

Bronze should generally preserve the source representation.

Bronze_Customers

might contain:

customer_id

name

country

amount

_source_file

_ingestion_timestamp

_batch_id

Example:

df = spark.read.option(
    "header",
    True

).csv(

"/lakehouse/default/Files/raw/customers.csv"

)

df = df.withColumn(
    "_ingestion_timestamp",
    current_timestamp()

)

Write to Bronze:

df.write \
    .format("delta") \
    .mode("append") \
    .saveAsTable(
        "Bronze_Customers"
    )

5.9.5 What Should NOT Happen in Bronze?

Avoid putting heavy business logic into Bronze.

For example, don't turn:

india

India

INDIA

into:

INDIA

unless there is a specific ingestion requirement.

Similarly, avoid unnecessarily removing duplicates before preserving the raw source state.

The goal is to retain enough source fidelity to support:

Reprocessing

Auditing

Debugging

Reconciliation

5.9.6 Bronze Metadata

Useful metadata includes:

_source_system

_source_file

_source_timestamp

_ingestion_timestamp

_batch_id

_pipeline_run_id

Example:

from pyspark.sql.functions import (
    current_timestamp,
    lit

)

df = df \
    .withColumn(
        "_source_system",
        lit("SQL_SERVER")
    ) \
    .withColumn(
        "_ingestion_timestamp",
        current_timestamp()
    ) \
    .withColumn(
        "_batch_id",
        lit("20260822_001")
    )

Metadata is extremely useful for operational monitoring.

5.9.7 Silver Layer

Silver consumes Bronze.

Bronze
   │
   ▼
Clean
   │
   ├── Trim
   ├── Type conversion
   ├── NULL handling
   ├── Deduplication
   ├── Validation
   └── Standardization
   │
   ▼

Silver

Example:

bronze_df = spark.table(
    "Bronze_Customers"

)

Clean names:

from pyspark.sql.functions import (
    trim,
    upper

)

silver_df = bronze_df \

    .withColumn(
        "name",
        trim("name")
    ) \
    .withColumn(
        "country",
        upper(trim("country"))
    )

5.9.8 Silver Deduplication

silver_df = silver_df.dropDuplicates(

["customer_id"]

)

For real systems, determine which record should survive rather than blindly keeping an arbitrary duplicate.

For example, keep the latest record:

from pyspark.sql.window import Window

from pyspark.sql.functions import row_number

window = Window \

.partitionBy("customer_id") \

.orderBy(

col("updated_timestamp").desc()

)

silver_df = silver_df \

    .withColumn(
        "rn",
        row_number().over(window)
    ) \
    .filter(
        col("rn") == 1
    ) \
    .drop("rn")

5.9.9 Silver Data Validation

Example rules:

customer_id cannot be NULL

amount must be numeric

quantity must be > 0

country must be valid

Example:

valid_df = silver_df.filter(

col("customer_id").isNotNull()

)

Invalid data can be sent to a quarantine table:

Silver_Customers_Quarantine

5.9.10 Gold Layer

Gold contains data designed around business requirements.

Example:

Silver_Sales
      │
      ├── Customer
      ├── Product
      ├── Date
      └── Store
             │
             ▼
          JOIN
             │
             ▼
        AGGREGATION
             │
             ▼

Gold_MonthlySales

Example Gold table:

Gold_MonthlySales

with:

Year

Month

Country

Revenue

Cost

Profit

Orders

Customers

UnitsSold

ProfitMargin

5.9.11 Gold Aggregation

from pyspark.sql.functions import (
    sum,
    countDistinct

)

gold_df = silver_df.groupBy(

"Year",

"Month",

"Country"

).agg(

sum("SalesAmount").alias(

"Revenue"

),

sum("CostAmount").alias(

"Cost"

),

sum("Quantity").alias(

"UnitsSold"

),

countDistinct("OrderID").alias(

"Orders"

),

countDistinct("CustomerID").alias(

"Customers"

)

)

Calculate profit:

gold_df = gold_df.withColumn(

"Profit",

col("Revenue") - col("Cost")

)

5.9.12 Medallion Architecture in Fabric

A typical Microsoft Fabric implementation can use Lakehouses to organize the layers.

Fabric Workspace
│
├── Bronze Lakehouse
│     ├── Files
│     └── Tables
│
├── Silver Lakehouse
│     ├── Files
│     └── Tables
│
└── Gold Lakehouse
      ├── Tables
      └── Reporting Data

An alternative is to use one Lakehouse with logical schemas/naming conventions, depending on organizational requirements.

For larger environments, separate Lakehouses/workspaces can provide stronger isolation and governance.

5.9.13 Recommended Fabric Structure

For a learning project:

Fabric Workspace
│
├── LH_Bronze
│
├── LH_Silver
│
├── LH_Gold
│
├── Notebook_Bronze_Ingestion
├── Notebook_Silver_Transform
└── Notebook_Gold_Aggregation

For a production environment:

Development
     │
     ▼
Test
     │
     ▼

Production

with appropriate deployment and governance practices.

5.9.14 Complete Architecture

                           SOURCES
        ┌────────────────────┼────────────────────┐
        │                    │                    │
        ▼                    ▼                    ▼
   SQL Server            REST API              ADLS/Blob
        │                    │                    │
        └────────────────────┼────────────────────┘
                             │
                             ▼
                    ┌─────────────────┐
                    │     BRONZE      │
                    │                 │
                    │ Raw data        │
                    │ Source metadata │
                    │ Audit metadata  │
                    └────────┬────────┘
                             │
                             ▼
                     DATA CLEANSING
                             │
              ┌──────────────┼──────────────┐
              │              │              │
              ▼              ▼              ▼
           Types         Duplicates       NULLs
              │              │              │
              └──────────────┼──────────────┘
                             ▼
                    ┌─────────────────┐
                    │     SILVER      │
                    │                 │
                    │ Clean           │
                    │ Standardized    │
                    │ Validated       │
                    │ Integrated      │
                    └────────┬────────┘
                             │
                ┌────────────┼────────────┐
                ▼            ▼            ▼
              JOINS         SCD       TRANSFORMS
                │            │            │
                └────────────┼────────────┘
                             ▼
                    ┌─────────────────┐
                    │      GOLD       │
                    │                 │
                    │ Facts           │
                    │ Dimensions      │
                    │ Aggregations    │
                    │ KPIs            │
                    └────────┬────────┘
                             │
                             ▼
                     SEMANTIC MODEL
                             │
                             ▼

POWER BI

5.9.15 Build a Complete Sales Pipeline

Let's build a practical example.

Source Systems

SQL Server
    │
    ├── Customers
    ├── Products
    └── Orders

And:

REST API
    │
    └── Exchange Rates

And:

ADLS
    │
    └── Product Files

5.9.16 Step 1 — Ingest into Bronze

SQL Server ───────► Bronze_Customers
SQL Server ───────► Bronze_Products
SQL Server ───────► Bronze_Orders
REST API ──────────► Bronze_ExchangeRates
ADLS ──────────────► Bronze_ProductFiles

No major business transformations.

5.9.17 Step 2 — Clean Bronze into Silver

Bronze_Customers
        │
        ▼

Silver_Customers

Apply:

Trim

Case standardization

Data type conversion

NULL handling

Duplicate handling

Validation

Similarly:

Bronze_Products → Silver_Products

Bronze_Orders → Silver_Orders

5.9.18 Step 3 — Implement SCD

Suppose customer city is historical.

Silver_Customers
       │
       ▼
SCD Type 2
       │
       ▼

DimCustomer

Result:

CustomerKey

CustomerID

CustomerName

City

StartDate

EndDate

IsCurrent

5.9.19 Step 4 — Build Dimensions

Create:

DimCustomer

DimProduct

DimDate

DimStore

Example:

DimCustomer
    │
    ├── CustomerKey
    ├── CustomerID
    ├── CustomerName
    ├── City
    ├── Country
    ├── StartDate
    ├── EndDate
    └── IsCurrent

5.9.20 Step 5 — Build Fact Table

Combine orders with dimensions.

Silver_Orders
     │
     ├──── DimCustomer
     │
     ├──── DimProduct
     │
     └──── DimDate
             │
             ▼

FactSales

Example:

FactSales
------------------

SalesKey

OrderID

CustomerKey

ProductKey

DateKey

Quantity

SalesAmount

CostAmount

5.9.21 Step 6 — Build Aggregated Gold Tables

From FactSales:

FactSales
   │
   ├──► Gold_DailySales
   │
   ├──► Gold_MonthlySales
   │
   ├──► Gold_CustomerSales
   │
   └──► Gold_ProductSales

Example:

Gold_MonthlySales

Year

Month

Country

Revenue

Profit

Orders

Customers

UnitsSold

5.9.22 Step 7 — Semantic Model

Gold data is consumed by a semantic model:

Gold
 │
 ▼
Semantic Model
 │
 ├── Measures
 ├── Relationships
 ├── Business calculations
 └── Security
 │
 ▼

Power BI

Example measures:

Total Revenue =

SUM(FactSales[SalesAmount])

Total Profit =

SUM(FactSales[SalesAmount])

-

SUM(FactSales[CostAmount])

5.9.23 End-to-End Data Flow

                 SQL SERVER
                     │
                     ▼
                  BRONZE
                     │
              Raw / Auditable
                     │
                     ▼
                  SILVER
                     │
          ┌──────────┼──────────┐
          │          │          │
        Clean       Join       SCD
          │          │          │
          └──────────┼──────────┘
                     ▼
                   GOLD
                     │
          ┌──────────┼──────────┐
          │          │          │
        Facts    Dimensions   KPIs
          │          │          │
          └──────────┼──────────┘
                     ▼
              SEMANTIC MODEL
                     │
                     ▼

POWER BI

5.9.24 Medallion Layer Responsibilities

A useful rule:

Bronze → "What did the source give us?"

Silver → "What is the trustworthy version?"

Gold → "What does the business need?"

5.9.25 Idempotency

A production Medallion pipeline should be idempotent where appropriate.

That means:

Running the same processing operation again should not create unintended duplicate results.

For example, if a batch is accidentally rerun:

Batch 100

should not result in:

Batch 100

Batch 100

Batch 100

in a way that duplicates business records.

Techniques include:

Batch IDs

Watermarks

MERGE

Deduplication

Source keys

Load timestamps

Checkpointing where applicable

5.9.26 Incremental Processing

Instead of processing 500 million records every day:

Full Load

500M rows

process only changed data:

Incremental

2M rows

Example:

Source
  │
  ▼

LastModified > LastWatermark

  │
  ▼
Bronze
  │
  ▼
Silver
  │
  ▼

Gold

This can significantly reduce processing cost and time.

5.9.27 Data Quality Across Layers

You can enforce different levels of quality:

BRONZE
│
├── Source availability
├── File completeness
└── Ingestion success
│
▼
SILVER
│
├── NULL checks
├── Data types
├── Duplicate checks
├── Referential integrity
└── Business validation
│
▼
GOLD
│
├── KPI validation
├── Reconciliation
├── Aggregation accuracy
└── Business-rule validation

5.9.28 Data Lineage

Medallion architecture improves lineage:

Source
  │
  ▼
Bronze
  │
  ▼
Silver
  │
  ▼
Gold
  │
  ▼

Power BI

If a Power BI number is wrong, you can trace it backward:

Power BI Revenue

↓

Gold_MonthlySales

↓

FactSales

↓

Silver_Orders

↓

Bronze_Orders

↓

SQL Server

5.9.29 Error Handling

A robust architecture should not simply fail silently.

               Pipeline
                  │
           ┌──────┴──────┐
           ▼             ▼
        Success        Failure
           │             │
           ▼             ▼
       Continue       Log Error
                         │
                         ▼
                    Quarantine
                         │
                         ▼

Alert

Track:

PipelineName

RunID

BatchID

StartTime

EndTime

Status

RecordsRead

RecordsWritten

RecordsRejected

ErrorMessage

5.9.30 Orchestration

In Fabric, multiple components can be orchestrated through Data Factory capabilities.

Example:

Master Pipeline
      │
      ├── Ingest Customers
      │
      ├── Ingest Products
      │
      ├── Ingest Orders
      │
      ├── Transform Silver
      │
      ├── Build Dimensions
      │
      ├── Build Facts
      │
      └── Build Gold

Dependencies are important.

For example:

Bronze Customers ──┐
Bronze Products ───┼──► Silver
Bronze Orders ─────┘
                       │
                       ▼

Gold

5.9.31 Recommended Notebook Structure

For a learning project:

Notebooks
│
├── 01_Bronze_Customers
├── 02_Bronze_Products
├── 03_Bronze_Orders
│
├── 10_Silver_Customers
├── 11_Silver_Products
├── 12_Silver_Orders
│
├── 20_DimCustomer
├── 21_DimProduct
├── 22_DimDate
│
├── 30_FactSales
│
├── 40_Gold_DailySales
├── 41_Gold_MonthlySales
└── 42_Gold_CustomerSales

For larger projects, organize notebooks by domain and responsibility rather than creating an excessively large number of notebooks.

5.9.32 Practical Project

Build this complete Fabric project:

Source

SQL Server
 ├── Customers
 ├── Products
 └── Orders

Bronze

Bronze_Customers

Bronze_Products

Bronze_Orders

Silver

Silver_Customers

Silver_Products

Silver_Orders

Apply:

Dimensions

DimCustomer

DimProduct

DimDate

Apply SCD Type 2 to:

DimCustomer

Fact

FactSales

Gold

Gold_DailySales

Gold_MonthlySales

Gold_ProductSales

Gold_CustomerSales

Reporting

Semantic Model
      │
      ▼

Power BI

5.9.33 Final Architecture

╔══════════════════════════════════════════════════════════╗

║ SOURCE SYSTEMS ║

║ SQL Server │ REST APIs │ ADLS │ Blob │ Files ║

╚════════════════════════════╤═════════════════════════════╝

                             │
                             ▼

╔══════════════════════════════════════════════════════════╗

║ BRONZE ║

║ ║

║ Raw source data + ingestion/audit metadata ║

║ Minimal transformation ║

╚════════════════════════════╤═════════════════════════════╝

                             │
                             ▼

╔══════════════════════════════════════════════════════════╗

║ SILVER ║

║ ║

║ Cleansing │ Validation │ Deduplication │ Joins │ SCD     ║
║ Standardization │ Type Conversion                        ║

╚════════════════════════════╤═════════════════════════════╝

                             │
                             ▼

╔══════════════════════════════════════════════════════════╗

║ GOLD ║

║ ║

║ Facts │ Dimensions │ Aggregations │ KPIs │ Business Data ║

╚════════════════════════════╤═════════════════════════════╝

                             │
                             ▼

╔══════════════════════════════════════════════════════════╗

║ SEMANTIC MODEL ║

╚════════════════════════════╤═════════════════════════════╝

                             │
                             ▼

╔══════════════════════════════════════════════════════════╗

║ POWER BI ║

║ Dashboards │ Reports │ Analytics ║

╚══════════════════════════════════════════════════════════╝

5.9.34 Key Takeaways

The entire Module 5 can now be connected:

5.1 Bronze

↓

5.2 Silver

↓

5.3 Gold

↓

5.4 Data Cleansing

↓

5.5 Aggregations

↓

5.6 SCD

↓

5.7 Joins

↓

5.8 Window Functions

↓

5.9 Medallion Architecture

The most important mental model is:

Bronze preserves data. Silver improves data. Gold serves the business.

And the typical transformation path is:

INGEST

↓

BRONZE

↓

CLEAN + STANDARDIZE

↓

SILVER

↓

JOIN + SCD + TRANSFORM

↓

AGGREGATE + MODEL

↓

GOLD

↓

SEMANTIC MODEL

↓

POWER BI

This architecture provides a clean foundation for the next stages of a Microsoft Fabric data engineering project, including Data Factory orchestration, Spark optimization, semantic modeling, governance, security, and production deployment.

↑ Back to top