Datawise
Practical SQL

The first thing I do when analyzing a SQL table

Constantin LunguUpdated 1 min read

Photo by Markus Winkler on Unsplash

Here's one of the first things I do with SQL when I want to quickly assess the data quality in a table.

I would run a series of quick COUNTs, testing key attributes of the data, such as key columns being NULL, which can display the distribution of problematic data so it can be processed accordingly.

A good way to ensure data quality in a data pipeline is to have a good look at it in the first place.

Input data: input_data with order_id, is_paid and product_id, six rows; order 1 has a NULL product_id and order 6 a NULL is_paid.

SELECT
  is_paid IS NULL AS is_paid_null,
  product_id IS NULL AS product_id_null,
  COUNT(1) AS occurences
FROM input_data
GROUP BY ALL

Query results: is_paid_null false with product_id_null true occurs 1 time, both false 4 times, and is_paid_null true with product_id_null false 1 time.

Enjoyed this? Here are some related articles you might find useful: