← Back to series overview

Data Engineering: Bronze to Silver to Gold with Copilot

Hey everyone, this week I learned how messy that "clean" shortcut data really is under the hood, 32 orphan ProductKeys and all. Here's the join I wish I'd built on day one.

Fabric & AI 4 min read Intermediate
By Felipe Andrade Author, TemJayto

What you'll build in Part 2

  • Silver clean_sales joining shortcut CSVs + SQL product snapshot on ProductKey
  • Referential-integrity checks, 32 unknown products quarantined, not silently dropped
  • Gold star schema with conformed DimProduct from Source B + enriched facts
  • A scheduled notebook pipeline that runs the whole medallion flow

Table of Contents

The medallion pattern in 2 minutes

One of my customers asked the obvious question here: "If the shortcut already shows the CSVs, why copy anything?" Fair. The shortcut is our bronze for sales, zero copy. The SQL snapshot is bronze for products because you don't want Spark hammering your OLTP every run. Same word "bronze", two very different mechanisms under the hood. To start I'm assuming Part 1's checks passed, if you still have null keys, fix those first, the join below won't save you.

🥉 Bronze
raw_sales as-is
→
🥈 Silver
clean + conformed
→
🥇 Gold
star schema

Bronze preserves truth. Silver fixes types, dedupes, handles returns. Gold reshapes into facts + dimensions, exactly what a Power BI semantic model loves. Fabric MCP lets Copilot read, write, and execute all of this from chat.

Silver: join shortcut + SQL snapshot with a Copilot notebook

Source A is CSV-via-shortcut (no Delta), Source B is Delta. Silver is where they meet: read both, type both, left-join sales to product on ProductKey, keep match flag for quarantine.

You: Create notebook Silver Cleaning: read sales CSVs from shortcut bronze/raw_sales, read dim_product Delta from bronze/dim_product, fix types, flag returns, left-join on ProductKey with match flag, write enriched silver clean_sales
Copilot: Generating PySpark… 
# Files/notebooks/silver_cleaning.ipynb
from pyspark.sql import functions as F

# Source A: shortcut CSVs, first materialization happens here
sales_raw = (spark.read.option("header", True)
    .csv("Files/bronze/raw_sales/daily/*/*/*/sales.csv")
    .withColumn("Quantity", F.col("Quantity").cast("int"))
    .withColumn("UnitPrice", F.col("UnitPrice").cast("decimal(10,2)"))
    .withColumn("SalesAmount", F.col("SalesAmount").cast("decimal(18,2)")))

# Source B: SQL snapshot Delta from Part 1
products = spark.read.format("delta").load("Files/bronze/dim_product")

sales = (sales_raw
    .dropDuplicates(["SalesKey"])
    .filter(F.col("SalesKey").isNotNull())
    .withColumn("Date", F.to_date("DateKey", "yyyyMMdd"))
    .withColumn("IsReturn", F.col("SalesAmount") < 0)
    .withColumn("SalesAmount", F.abs("SalesAmount")))

# The join: sales LEFT JOIN product master
enriched = (sales.join(products.select("ProductKey", "Category",
        "Subcategory", "Brand", "UnitCost"), "ProductKey", "left")
    .withColumn("HasValidProduct", F.col("Category").isNotNull())
    .withColumn("PartitionYM", F.date_format("Date", "yyyyMM")))

enriched.write.format("delta").mode("overwrite") \
    .partitionBy("PartitionYM").save("Files/silver/clean_sales")

print("silver written (shortcut + SQL joined)")
// MCP Call: fabric.runNotebook(ws, "silver_cleaning.ipynb")  to  2m 41s [ok]
Why join in Silver, not Gold? Gold should assume clean conformed keys. Catching orphan ProductKeys here means DimProduct in Gold is complete by construction, Part 3 relationships never break.

Data quality + quarantine (including join orphans)

Never silently drop rows, especially failed joins. The 32 sales with unknown ProductKey from Part 1 go here with reason unknown_product, alongside nulls and future dates.

You: Add quality checks: null keys, future dates, HasValidProduct=false. Write failures to silver/_quarantine with reason column, and print a summary table
Copilot: 
checks = (clean
    .withColumn("fail_reason",
        F.when(F.col("Date") > F.current_date(), "future_date")
         .when(F.col("ProductKey").isNull(), "null_product")
         .otherwise(None)))

quarantine = checks.filter("fail_reason IS NOT NULL")
quarantine.withColumn("quarantined_at", F.current_timestamp()) \
    .write.format("delta").mode("append").save("Files/silver/_quarantine")

good = checks.filter("fail_reason IS NULL")
print(f"good={good.count()} quarantined={quarantine.count()}")
MetricValueStatus
Good rows145,198OK
Quarantined32REVIEW

Gold: star-schema tables (DimProduct from SQL source)

Gold is deliberately boring: one fact, conformed dimensions. DimProduct comes from authoritative Source B (SQL snapshot), not re-derived from sales, so categories stay complete even for slow-moving products with no sales this period.

You: Create notebook Gold Star Schema: FactSales from silver clean_sales (valid rows only), DimProduct from bronze dim_product snapshot, DimDate/DimCustomer/DimStore from silver, write to Files/gold/star_schema/
Copilot: 
# Files/notebooks/gold_star_schema.ipynb
silver = spark.read.format("delta").load("Files/silver/clean_sales") \
    .filter("HasValidProduct AND fail_reason IS NULL")
products_bronze = spark.read.format("delta").load("Files/bronze/dim_product")

# Dimensions, DimProduct authoritative from SQL snapshot
dim_date = silver.select("Date").distinct() \
    .withColumn("DateKey", F.date_format("Date","yyyyMMdd").cast("int")) \
    .withColumn("Year", F.year("Date")).withColumn("Month", F.month("Date"))

dim_product = products_bronze.select(
    "ProductKey","Category","Subcategory","Brand","UnitCost").distinct()
dim_customer = silver.select("CustomerKey").distinct() \
    .withColumn("Segment", F.lit("Standard"))
dim_store = silver.select("StoreKey","Region","StoreName").distinct()

fact = silver.join(dim_date, "Date", "left") \
    .select("SalesKey","DateKey","ProductKey","CustomerKey","StoreKey",
            "Quantity","UnitPrice","SalesAmount","IsReturn")

for name, df in {"FactSales": fact, "DimDate": dim_date,
                 "DimProduct": dim_product, "DimCustomer": dim_customer,
                 "DimStore": dim_store}.items():
    df.write.format("delta").mode("overwrite") \
      .save(f"Files/gold/star_schema/{name}")
print("gold star schema: fact joined cleanly, DimProduct from SQL master")
My gold rule: fact stays long and narrow (keys + numbers), dims stay wide and descriptive. No business logic in gold. Measures live in the model in Part 3.

Schedule it as a pipeline

You: Create pipeline Medallion Daily: run Silver Cleaning then Gold Star Schema sequentially, daily 3:30 AM after ingestion, alert on failure
Copilot: 
// MCP Call: fabric.createPipeline(ws, {
  name: "Medallion Daily",
  activities: [
    { type: "Notebook", notebook: "silver_cleaning.ipynb" },
    { type: "Notebook", notebook: "gold_star_schema.ipynb",
      dependsOn: "silver_cleaning.ipynb" }
  ],
  schedule: "0 3:30 * * *", onFailureAlert: true
})
End-to-end nightly: shortcut CSVs + SQL snapshot to silver (joined) to gold, no clicks.

What's next in Part 3

Gold tables are clean, but they're not yet meaningful. Part 3 explains why semantic models matter and builds one with Copilot: relationships, DAX measures, DirectLake, and RLS.

← Part 1: Lakehouse Continue to Part 3: Semantic Models →