3 Methods for Dealing with Bad Data Quality

Design to handle bad data before it poisons your platform!

Dealing with bad data quality is a given with real-world data. Bad data can come from processing errors, networking issues, software bugs, end-user fat fingering a form, etc. A bad record in one table can cause downstream tables to propagate that same bad record. Resulting in diminished analytics for dashboards and incorrect KPIs. So how can you proactively root out bad data before it affects your users?

This article will walk through 3 methods I’ve used to help mitigate the effects of bad data in a data platform. These methods help keep bad data at bay so that you have time to react to changes in underlying expectations. The examples in this article focus on Delta Tables and PySpark as the underlying data platform, but the overall methods can also be applied to most other tech stacks.

All the code for this article is available in the repo here.

Table Constraints

Table level constraints ensure the quality and integrity of the data being written to a table satisfy a defined condition. When a constraint is violated, the suspect record is not added to the table, and the job fails with an error.

A table constraint is similar to a hard filter on the data. Data that violates the constraint is not added to the table.

Delta tables support standard SQL constraint clauses for NOT NULL and CHECK type constraints. These two methods help keep a consistent profile for the data written to a table and for all underlying data if a constraint is added to an existing table. Since Delta tables use standard SQL notation, the below syntax is pretty straightforward.

CREATE TABLE sales_order_details (
    business_entity_id STRING NOT NULL,
    sales_order_id INT NOT NULL,
    sales_order_detail_id INT NOT NULL,
    order_qty INT NOT NULL,
    product_id STRING NOT NULL,
    unit_price FLOAT NOT NULL,
    unit_price_discount INT,
    line_total FLOAT NOT NULL,
    carrier_tracking_number INT,
    modified_date DATE,
    source_file STRING
) USING DELTA;

ALTER TABLE sales_order_details 
    ADD CONSTRAINT negative_unit_price CHECK (unit_price > 0);

ALTER TABLE sales_order_details 
    ADD CONSTRAINT zero_order_qty CHECK (order_qty <> 0);

The NOT NULL constraints are added directly in line with the table schema, and the CHECK constraints are added with an ALTER TABLE table_name ADD CONSTRAINT after the table creation. For this example, the NOT NULL constraints ensure that all records have a value, and the two CHECK constraints validate that the data always adheres to these business rules:

• unit_price should always be greater than 0
• order_quantity should not be 0 but can be negative or positive

You would now be able to insert data using a MERGE INTO statement like the one below. The data containing new data is raw_sales_order_details.

MERGE INTO sales_order_details as a
    USING raw_sales_order_details as b
    ON a.sales_order_id = b.sales_order_id
    WHEN NOT MATCHED THEN INSERT *;

The significant call out when using Delta tables is if you are loading a batch of data, say 10 million records, and a single record is bad, all 10 million records will be rejected. Again, if one record in a whole batch is bad, the entire batch is rejected, and processing fails.

So how can you weed out a single bad record?

Quarantining

Quarantining data is the practice of filtering data and saving any filtered values in a separate location.

The assumption when quarantining data is that you can backfill those records or manually adjust the data and append them into the target table later.

The example below uses Spark SQL to create a temporary view named valid_sales_order_details which uses a filter to remove known bad data. The data in the temporary view is merged into the target table with a MERGE INTO statement, like the one above. Data that is quarantined will be saved into the table sales_order_details_quarantine.

CREATE OR REPLACE TEMPORARY VIEW valid_sales_order_details AS 
SELECT *
FROM raw_sales_order_details
WHERE 
    ( unit_price > 0 OR order_qty <> 0 );

MERGE INTO sales_order_details as a
    USING valid_sales_order_details as b
    ON a.sales_order_id = b.sales_order_id
    WHEN NOT MATCHED THEN INSERT *;

INSERT INTO sales_order_details_quarantine 
    SELECT *
    FROM raw_sales_order_details
    WHERE 
       ( unit_price < 0 OR order_qty = 0 );

The table sales_order_details_quaratine will receive any records that meet the filter condition. This will allow you to review the data written on the table and ask the appropriate owners about the validity of the data.

Once the data is approved as valid or a new record is created with the corrected data, those values can be appended to the correct table. The data on the table might become stale as you figure out the action plan. So what now?

Flagging

Flagging data when it does not meet expectations is a low-touch solution with little overhead. However, it can become unmanageable if you need a few dozen flags on a single table. This solution can easily be accomplished with a SQL CASE / WHEN with a GENERATED column statement.

CREATE TABLE sales_order_details (
    business_entity_id STRING NOT NULL,
    sales_order_id INT NOT NULL,
    sales_order_detail_id INT NOT NULL,
    order_qty INT NOT NULL,
    product_id STRING NOT NULL,
    unit_price FLOAT NOT NULL,
    unit_price_discount INT,
    line_total FLOAT NOT NULL,
    carrier_tracking_number INT,
    modified_date DATE,
    source_file STRING,
    negative_unit_price STRING GENERATED ALWAYS AS (
        CASE WHEN unit_price < 0 THEN 'Y' ELSE NULL END
    ),
    zero_order_qty STRING GENERATED ALWAYS AS (
        CASE WHEN unit_price = 0 THEN 'Y' ELSE NULL END
    )
) USING DELTA;

So now, when inserting into this table, any records with an invalid value are automatically flagged. The catch is that users need to know how to use these columns if this table is for analytic use. They’d need to construct queries like the one below.

SELECT *
FROM sales_order_details
WHERE 
    negative_unit_price is null 
    AND zero_order_qty is null

This can be time-consuming for analytic queries and might not be the best long-term solution for all user-facing tables.

Conclusion

Of the 3 methods in this article, I tend to use the ‘quarantine` and ‘constraint’ approaches for most downstream tables. These methods allow you to be proactive with data quality issues and enable a data team to react when there is a slide in quality. Your team needs to be invested in data quality from the start for these approaches to be beneficial. Data quality starts with a team culture that cares deeply about providing and monitoring quality. You can make all the great monitoring tools you want, but you don’t have a solution if there is no action.

Data quality is a huge development area; packages like Great Expectations eliminate most of the need to custom code a solution.

I hope these approaches can help you start your journey on ensuring quality!

Again, all the code for this article is available in the repo here.

Please consider liking this article and following me on Medium if you found this post helpful. I write about data engineering, books, tutorials, and more!

Cheers! — Steve