ADE Data Quality and Testing

A pipeline that runs without errors can still deliver wrong data. A duplicate order, a missing price, or a late file silently distorts every report that follows. Data quality checks catch these problems at the door and keep trust in the platform high.

The Food Inspector Analogy

A grocery store inspects each delivery before shelving it. Damaged boxes go to a separate area, and fresh produce moves forward. Data checks play the same role. Good rows move to the next layer, and suspicious rows wait in quarantine for review.

Six Dimensions of Quality

Dimension	Question	Sample Check
Completeness	Are required values present?	No blank customer_id
Uniqueness	Does each record appear once?	order_id appears one time
Validity	Does the value follow the rules?	Quantity is a positive whole number
Consistency	Do related values agree?	Ship date falls after order date
Timeliness	Did the data arrive on time?	Today's file landed before 6 AM
Accuracy	Does the data match reality?	Totals match the source system

Where to Place Checks

SOURCE ---> BRONZE ---> [Check A] ---> SILVER ---> [Check B] ---> GOLD ---> [Check C] ---> REPORT

Check A: file arrived, schema matches, row count above zero
Check B: no duplicates, no nulls in keys, values in valid ranges
Check C: totals reconcile with the source, no sudden jumps

Early checks stop bad data before it spreads. Late checks confirm that business totals still make sense.

Scenario: FreshCart Daily Sales

FreshCart, a fictional grocery chain, loads store sales every night. One morning, the revenue dashboard shows a 40 percent drop. The cause: one store sent an empty file, and the pipeline accepted it. A simple row count check would have flagged the problem before any report refreshed.

Checks in PySpark

from pyspark.sql import functions as F

sales = spark.read.format("delta").load("/lake/silver/sales")

total_rows = sales.count()
null_keys = sales.filter(F.col("order_id").isNull()).count()
duplicates = total_rows - sales.dropDuplicates(["order_id"]).count()
bad_qty = sales.filter(F.col("quantity") <= 0).count()

if total_rows == 0 or null_keys > 0 or duplicates > 0 or bad_qty > 0:
    raise Exception(
        f"Quality check failed: rows={total_rows}, null_keys={null_keys}, "
        f"duplicates={duplicates}, bad_qty={bad_qty}"
    )

The raised exception fails the notebook activity, and the pipeline stops before gold tables refresh.

Built-In Azure Options

• Delta constraints: A CHECK constraint on a Delta table rejects rows that break a rule, such as quantity above zero.
• ADF Assert transformation: A mapping data flow step that tests rows against expectations and marks failures.
• ADF Validation activity: Waits for a file to exist, reach a minimum size, or both, before the pipeline continues.
• Purview data quality: Rule-based scoring of assets in the governance catalog.
• Open-source libraries: Tools such as Great Expectations define reusable checks in Python.

ALTER TABLE silver_sales ADD CONSTRAINT positive_qty CHECK (quantity > 0);

The Quarantine Pattern

Failing an entire load for one bad row wastes everybody's time. The quarantine pattern splits the data in two. Valid rows continue to the silver table. Invalid rows land in a quarantine table with a reason column, so stewards can fix and replay them.

Incoming rows ---> Rule check ---+---> Valid rows ---> Silver table
                                |
                                +---> Invalid rows + reason ---> Quarantine table

Testing Pipeline Logic

• Unit tests feed small sample data to a transformation function and compare the output with the expected result.
• Reconciliation tests compare row counts and totals between source and target.
• Regression tests rerun old input after a code change and confirm the output stays identical.
• Test environments hold safe sample data, so production stays untouched.

Alerting on Failures

A failed check must reach a human. Connect pipeline failures to Azure Monitor alerts that send email or Teams messages. Include the dataset name, the failed rule, and the run link in every message.

Key Points

• Quality checks cover completeness, uniqueness, validity, consistency, timeliness, and accuracy.
• Place checks between layers and stop bad data early.
• Delta constraints, ADF Assert, and PySpark tests enforce rules automatically.
• Quarantine tables keep good data flowing and bad data visible.