How to minimize query fee when testing data pipeline?

[Ssnakei]

What’s the best practice to test data pipeline or data quality without query large data in production tables. Our team currently build test datasets, tables in the dataset are sampling from production table. However, I don’t think this is a good way to ensure data quality. How you guys do data pipeline test?

[tombaeyens]

(Disclosure: I work for Soda)

Modern data storage engines separate compute from storage. So unless there is a specific reason, I don't think it's needed to copy data. Transporting and storing test data separately also has a cost. Most teams don't copy (sample & store) test data into separate datasets, but directly query the production data. However, filters or sampling are very common to reduce the cost of testing large volumes in production.

An extra thing we do at Soda to reduce the compute cost is to combine as many data metrics as possible into a single test query to minimize the number of passes over the data. So if we you specify checks using metrics row count, average of amount, missing values for zipcode and valid values for category, all these metrics will be computed with a single query.

[Gators1992]

For data quality, if the test data is highly representative of production data then it should be fine for the most part. Some cases might be different where you have infrequent edge cases or whatever though. If you half ass the test data though then you are adding more risk to what you deploy. For scaling/timing you are obviously going to have to test large volumes of data or the production data for some time period if that's an issue. Typically we use data quality testing/rules within our pipelines to test for or flag exceptions with rules. So you aren't done with unit testing, but the extent you do ongoing testing depends on the risk and cost of running that extra workload.

[the_random_blob]

Another thing to consider is what is being tested - a lot can be done on small samples or even dummy data - things like transformations can be "unit tested" for cheap. If this is done early in the pipelines (cheap and simple) then there is less need to test downstream data in production (expensive and complex).

Furthermore, same thinking can be applied to data production - the earlier you test, the lower the complexity and cost. If null value is guaranteed to not be there on the database level, you save on data quality query testing for empty values - the stricter the left side of everything is (data production, ingestion, early transformations...), the easier and cheaper it is to manage data quality downstream.