Cleaning and Reshaping Data from Bronze to Silver

What it is

In the medallion architecture, a bronze table holds data as it was ingested, and a silver table holds the same data cleaned: required values present, consistent types and tidy column names. Cleaning is a read, transform and write: read the bronze table, fix it, and write a new silver table in Unity Catalog.

The exam shows this work in PySpark DataFrame code or in SQL. You need to recognize which function does each job and what it does to rows that do not fit.

How it works

Handling nulls

dropna() with no arguments drops a row if any column is null, which is rarely what you want. Name the key columns with subset.

Standardizing data types

Bronze data often arrives as strings. Cast it to the real type with cast and parse dates with to_date or to_timestamp and a format string.

With ANSI mode on, cast raises an error on a value it cannot convert. try_cast returns NULL for that value instead, so the load continues and you can filter or quarantine the bad rows.

from pyspark.sql import functions as F
silver = (spark.read.table("main.bronze.orders")
  .dropna(subset=["order_id"])
  .fillna({"country": "unknown"})
  .withColumn("amount", F.col("amount").cast("decimal(10,2)"))
  .withColumn("order_date", F.to_date("order_ts")))
silver.write.mode("overwrite").saveAsTable("main.silver.orders")

The same step in SQL is a CREATE TABLE AS SELECT:

CREATE OR REPLACE TABLE main.silver.orders AS
SELECT order_id, try_cast(amount AS DECIMAL(10,2)) AS amount,
       coalesce(country, 'unknown') AS country, to_date(order_ts) AS order_date
FROM main.bronze.orders
WHERE order_id IS NOT NULL;

Adding, dropping and renaming columns

withColumn with an existing name replaces that column. DataFrames are immutable, so each call returns a new DataFrame; assign the result or chain the calls.

Changing the table structure

To change a stored Delta table in place, use ALTER TABLE: ADD COLUMNS, RENAME COLUMN and DROP COLUMN. Rename and drop work only when column mapping is enabled on the table.

ALTER TABLE main.silver.orders SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name');
ALTER TABLE main.silver.orders RENAME COLUMN cust_id TO customer_id;
ALTER TABLE main.silver.orders ADD COLUMNS (channel STRING);

Splitting columns and exploding arrays

split(col, pattern) turns a string into an array; take parts with getItem(0) or [0]. The pattern is a regular expression, so a literal dot or pipe must be escaped.

explode turns each array element into its own row and copies the other columns. Rows whose array is null or empty disappear. explode_outer keeps those rows with a null value.

from pyspark.sql import functions as F
df = (orders
  .withColumn("first_name", F.split("full_name", " ").getItem(0))
  .withColumn("item", F.explode("items"))
  .filter(F.col("item.qty") > 0))

Nested struct fields are read with dot notation, such as item.qty or address.city in SQL.

Filtering rows

filter and where are the same method. Combine conditions with &, | and ~ in PySpark, each condition in parentheses; SQL uses AND, OR and NOT.

How the exam asks about this

• "Orders with an empty items array must still appear in the silver table." Use explode_outer, not explode.
• "A bad amount string must not stop the load; it should become NULL." Use try_cast.
• "Remove only rows where customer_id is null." Use dropna(subset=["customer_id"]) or WHERE customer_id IS NOT NULL.