Data Quality Best Practices

Quick Answer: Enforce data quality at every medallion layer: completeness at bronze, validation and cleansing at silver, and business rule correctness at gold. Use Lakeflow expectations for pipeline-level enforcement, SQL constraints for table-level rules, and SQL Alerts for monitoring.

Quality by Medallion Layer

Bronze Layer Quality

-- Bronze: expectations for data arrival
CREATE OR REFRESH STREAMING TABLE bronze_orders (
    -- Warn if rescued data exists (indicates source schema changes)
    CONSTRAINT no_rescued_data EXPECT (_rescued_data IS NULL)
)
AS SELECT * FROM STREAM read_files('/data/raw/orders/');

Silver Layer Quality

-- Silver: comprehensive quality expectations
CREATE OR REFRESH STREAMING TABLE silver_orders (
    -- Required fields
    CONSTRAINT valid_order_id EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW,
    CONSTRAINT valid_customer EXPECT (customer_id IS NOT NULL) ON VIOLATION DROP ROW,

    -- Range validation
    CONSTRAINT positive_amount EXPECT (amount > 0) ON VIOLATION DROP ROW,
    CONSTRAINT valid_quantity EXPECT (quantity BETWEEN 1 AND 10000) ON VIOLATION DROP ROW,

    -- Business rules
    CONSTRAINT valid_status EXPECT (order_status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')) ON VIOLATION DROP ROW,

    -- Date validation
    CONSTRAINT valid_date EXPECT (order_date >= '2020-01-01' AND order_date <= current_date()) ON VIOLATION DROP ROW
)
AS SELECT ... FROM STREAM(LIVE.bronze_orders);

Gold Layer Quality

-- Gold: validation queries (run as post-processing checks)

-- Check: daily total matches sum of individual orders
SELECT
    order_date,
    ABS(gold_total - silver_total) AS discrepancy
FROM (
    SELECT order_date, total_revenue AS gold_total FROM gold.daily_sales
) g
JOIN (
    SELECT order_date, SUM(amount) AS silver_total FROM silver.orders GROUP BY order_date
) s ON g.order_date = s.order_date
WHERE ABS(gold_total - silver_total) > 0.01;
-- Expected: 0 rows (no discrepancies)

Data Quality Monitoring Strategy

Layered Approach

Delta Lake Table Constraints

-- Add NOT NULL constraint
ALTER TABLE silver.orders ALTER COLUMN order_id SET NOT NULL;

-- Add CHECK constraint
ALTER TABLE silver.orders ADD CONSTRAINT positive_amount CHECK (amount > 0);

-- These constraints are enforced on every write — violations fail the write

SQL Assertion Tasks (Job-Level Checks)

-- Add as a task in Databricks Workflows
-- The task fails if the query returns any rows (indicating quality issues)

SELECT 'DUPLICATE_ORDERS' AS issue, COUNT(*) AS count
FROM (
    SELECT order_id FROM silver.orders GROUP BY order_id HAVING COUNT(*) > 1
)
UNION ALL
SELECT 'NULL_AMOUNTS' AS issue, COUNT(*) AS count
FROM silver.orders WHERE amount IS NULL
UNION ALL
SELECT 'FUTURE_DATES' AS issue, COUNT(*) AS count
FROM silver.orders WHERE order_date > current_date();

Common Data Quality Metrics

On the Exam: Understand the layered quality approach — expectations in pipelines, constraints on tables, assertions in jobs, and alerts for monitoring. Know which layer (bronze/silver/gold) is appropriate for each type of quality check.