Reading Bronze Tables and Cleaning Nulls for Silver

1.What bronze hands you, and how to read it

Cleaning starts with knowing the state of the input. Very little validation happens in bronze. To avoid losing data when a source changes shape, Databricks recommends storing most fields as string, VARIANT, or binary. That means a silver job usually gets text where it needs numbers and dates, plus nulls and placeholder values the source system never resolved. Bronze also isn't meant for analysts. It exists to feed the workloads that build silver.

The pattern is always read, then transform, then write: read from one or more bronze (or silver) tables, and write to silver tables. Databricks advises against writing to silver straight from ingestion, because schema changes and corrupt source records then break the silver load. If the bronze sources are append-only, make most bronze reads streaming reads, and keep batch reads for small datasets such as small dimension tables. In PySpark, spark.read.table loads a table as a DataFrame. In SQL, a plain SELECT ... FROM bronze_table inside an INSERT INTO silver_table statement does the same job, as the SQL examples later in this lesson show.

spark.read.table("tblA").sort("age").show()

Sources1

2.Dropping rows with dropna

The bluntest fix is to remove incomplete rows. The medallion example does exactly that for customer and transaction data in silver, dropping nulls and quarantining invalid records. In PySpark, DataFrame.dropna and df.na.drop are aliases. Both return a new DataFrame without rows that contain null or NaN values. Here is the sample DataFrame the examples below use:

from pyspark.sql import Row
df = spark.createDataFrame([
    Row(age=10, height=80.0, name="Alice"),
    Row(age=5, height=float("nan"), name="Bob"),
    Row(age=None, height=None, name="Tom"),
    Row(age=None, height=float("nan"), name=None),
])

df.na.drop(thresh=2).show()
# +---+------+-----+
# |age|height| name|
# +---+------+-----+
# | 10|  80.0|Alice|
# |  5|   NaN|  Bob|
# +---+------+-----+

Pay attention to the default. A bare df.na.drop() uses how='any', which leaves only Alice, because Bob's height is NaN. That is usually far more than you meant to drop. Use subset when only certain columns, such as keys, must be populated.

df.na.drop(how=' ? ').show()
# +----+------+-----+
# | age|height| name|
# +----+------+-----+
# |  10|  80.0|Alice|
# |   5|   NaN|  Bob|
# |NULL|  NULL|  Tom|
# +----+------+-----+

Sources21

3.Filling and replacing missing values

Dropping a row throws away its good fields as well. Often a default value is the better choice. DataFrame.fillna and df.na.fill are aliases. They accept either one scalar or a dict that maps column names to replacement values. With a scalar, only columns of a matching type are filled. In the docs example, df.na.fill(50) fills the numeric age and height columns and leaves the string name null. With a dict, you set a value per column, and any subset you pass is ignored.

df.na.fill({'age': 50, 'name': 'unknown'}).show()
# +---+------+-------+----+
# |age|height|   name|bool|
# +---+------+-------+----+
# | 10|  80.5|  Alice|NULL|
# |  5|  NULL|    Bob|NULL|
# | 50|  NULL|    Tom|NULL|
# | 50|  NULL|unknown|true|
# +---+------+-------+----+

In SQL, coalesce returns its first non-null argument, which makes coalesce(col, default) the SQL counterpart of a fill. The result type is the least common type of the arguments. The arguments are evaluated left to right, and evaluation stops at the first non-null value.

The opposite problem also comes up: a value that should be null but isn't. If an upstream system can't encode NULL, it may write a placeholder such as -1. Instead of making every downstream query filter out -1, convert it back to NULL once, during the bronze-to-silver write:

INSERT INTO silver_table
  SELECT
    * EXCEPT weight,
    CASE
      WHEN weight = -1 THEN NULL
      ELSE weight
    END AS weight
  FROM bronze_table;

Sources3452

4.Quarantining invalid records instead of losing them

Some rows are invalid rather than incomplete, such as a negative quantity or a timestamp in the future. Use filters and WHERE clauses to keep them out of silver. Write the complementary predicate to a quarantine table, so the rows can still be inspected later. Each destination gets its own INSERT:

DECLARE current_time = now()

INSERT INTO silver_table
  SELECT * FROM bronze_table
  WHERE event_timestamp <= current_time AND quantity >= 0;

INSERT INTO quarantine_table
  SELECT * FROM bronze_table
  WHERE event_timestamp > current_time OR quantity < 0;

The two writes are deliberately separate. Databricks recommends processing filtered data as a separate write operation, especially with Structured Streaming, because using .foreachBatch to write to several tables can produce inconsistent results. In PySpark the same split is a pair of filter calls, using column predicates such as isNotNull().

df.filter(df.name.isNotNull()).show()

Sources4

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.df.na.drop() with no arguments removes only rows that are entirely null.Why is that wrong?

   The default is how='any', so a single null or NaN anywhere in a row drops it. Only how='all' limits the drop to fully null rows.

   Covered in Dropping rows with dropna

2. 2.Passing subset together with a dict to fillna restricts the fill to the subset columns.Why is that wrong?

   When value is a dict, subset is ignored. The dict keys alone decide which columns are filled.

   Covered in Filling and replacing missing values