Data Cleansing for Silver Layer Transformation

The Purpose of the Silver Layer

Before we write any code, let's solidify our understanding of the Silver layer's role. While the Bronze layer is an immutable archive of source data, the Silver layer serves as the cleansed, validated "single source of truth."

Think of it this way: if a data analyst or scientist needs customer data, they shouldn't have to query the raw bronze tables and perform the same cleaning steps every time. The Silver layer provides them with a trusted starting point.

Common transformations you'll perform when moving data from Bronze to Silver include:

• Type Casting: Converting strings to dates, timestamps, or numeric types.
• Column Manipulation: Splitting a full_name column into first_name and last_name, or combining fields.
• Handling Nulls: Applying business rules to null values, such as replacing them with defaults.
• Standardization: Ensuring consistent formats for things like addresses, country codes, or currency.

Enforcing Data Quality with Expectations

A core responsibility of the Silver layer is to enforce data integrity. In Databricks, the modern and declarative way to do this is with Delta Live Tables (DLT), now integrated into a broader feature set called Lakeflow. DLT allows you to define data quality rules, called expectations, directly in your pipeline.

When a record fails to meet an expectation, you can configure one of three outcomes:

1. Warn (Default): The record is still included in the target table, but the failure is logged in the pipeline's event log. This is useful for tracking data quality without dropping data.
2. Drop: The record that fails the rule is discarded and not written to the target table. This is the most common action for cleansing data.
3. Fail: The entire pipeline update stops immediately upon encountering a single failing record. This is reserved for critical data errors that signal a major upstream problem and require immediate intervention.

Implementing Silver Layer Transformations

Let's get hands-on. DLT pipelines can be defined using both SQL and Python, giving you the flexibility to use the language you're most comfortable with. Your experience with both languages will be valuable here.

The following resource from the official Databricks documentation provides concise examples for creating a cleansed, Silver-layer table from a bronze source.

Let's break down those implementation patterns.

SQL Implementation

In SQL, you define expectations using CONSTRAINT clauses within your CREATE OR REFRESH STREAMING TABLE statement. This approach is very readable and feels natural for those with a strong SQL background.

-- This DLT SQL code creates a silver table from a bronze source stream.
-- It applies two quality rules, dropping any rows that violate them.

CREATE OR REFRESH STREAMING TABLE silver.customers_clean (
  -- Rule 1: The customer ID must not be null.
  CONSTRAINT valid_id EXPECT (id IS NOT NULL) ON VIOLATION DROP ROW,
  
  -- Rule 2: The operation type must be one of the expected values.
  CONSTRAINT valid_operation EXPECT (operation IN ('APPEND', 'DELETE', 'UPDATE')) ON VIOLATION DROP ROW
)
COMMENT "Cleansed customer data from the bronze layer with quality checks."
AS
SELECT 
  id, 
  firstname, 
  lastname, 
  email, 
  address,
  operation,
  -- Cast the operation_date from string to timestamp for the silver layer
  CAST(operation_date AS TIMESTAMP) as operation_date
FROM STREAM(bronze.customers_cdc_bronze);

Python Implementation

In Python, you use decorators (@dlt.expect, @dlt.expect_or_drop, @dlt.expect_or_fail) to attach quality rules to the function that defines your table. This allows for modularity and integrates well with more complex PySpark transformations.

import dlt
from pyspark.sql.functions import *




# This DLT Python code defines the same silver table.
# Notice how transformations like adding a new column are done with standard PySpark.

@dlt.create_streaming_table(
  name="customers_clean",
  comment="Cleansed customer data from the bronze layer with quality checks."
)
@dlt.expect_or_drop("valid_id", "id IS NOT NULL")
@dlt.expect_or_drop("valid_operation", "operation IN ('APPEND', 'DELETE', 'UPDATE')")
def customers_clean():
  return (
    dlt.read_stream("customers_cdc_bronze")
      .withColumn("operation_date", col("operation_date").cast("timestamp"))
      .withColumn("full_name", concat_ws(" ", col("firstname"), col("lastname")))
  )

A Practical Walkthrough

Now, let's watch a video that puts all these concepts together in an end-to-end scenario. This will show you how these transformations and quality checks are applied in a real pipeline.

Monitoring Data Quality in Production

Defining quality rules is only half the battle. In a production environment, you need to monitor the results. The DLT UI provides a great visual summary, but for automated monitoring and alerting, you need programmatic access to the quality metrics. DLT records all pipeline events, including data quality statistics, to an event log, which is itself a Delta table that you can query.

By querying the event log, you can track the number of records dropped over time, identify which rules are failing most often, and set up alerts if the failure rate exceeds a certain threshold.

Conclusion

In this lesson, we bridged the gap between the raw Bronze layer and the cleansed Silver layer. You learned how to apply critical transformations and enforce data integrity using the declarative expectations feature in Delta Live Tables.

Key Takeaways:

• The Silver layer is the single source of truth for cleansed, conformed, and validated data. It separates cleaning logic from raw ingestion and business aggregation.
• DLT Expectations are the modern, declarative way to enforce data quality rules in Databricks.
• You have granular control over how to handle bad data using three actions: warn, drop, or fail.
• Implementations are flexible, with clear syntax available in both SQL (CONSTRAINT ... EXPECT) and Python (@dlt.expect... decorators).
• For production-grade pipelines, monitoring the DLT event log is crucial for observability and alerting on data quality issues.

Preview of the Next Lesson:

We now have a high-quality, reliable dataset in our Silver layer. The next step is to make this data useful for specific business needs. In our upcoming lesson, we will focus on "Building an aggregated gold table for a specific business use case," where we'll transform our cleansed data into business-centric models ready for analytics and reporting.