Datawise
Practical SQL

Table grain quick validation with SQL

Constantin LunguUpdated 2 min read

Photo by Lutz Wernitz on Unsplash

I was doing some exploratory data analysis on a number of tables I didn’t have much information about and, unfortunately, didn’t know their grain.

The grain is what one row of a table represents. I wrote about why it matters so much, and how I approach an undocumented table, in For quality work, understand your data grain first.

I needed a quick way to validate my assumptions about the table grain, identify contradicting observations (rows), and check for duplicates at the same time.

Therefore I decided to use a combination of TO_JSON_STRING and FARM_FINGERPRINT. The first creates a JSON representation of the entire row (given a table alias), while the second converts the resulting string into a INT64 hash.

By comparing the total number of rows in a group against the distinct count of these fingerprints, we can determine whether the proposed grain is correct and whether there are duplicates in the data.

This was a quick exercise but use this with care. Depending on your SQL implementation, data volumes and context, results may vary.

Input data: input_data with order_id, product_name, qty, price and order_status, 15 rows for orders 1 to 3 (Apples, Mangoes, Cucumbers, Tomatoes and Plums), each product once ORDER_PLACED and once ORDER_SENT, with the order 3 Plums rows repeated and one Plums row with a null order_status.

SELECT

  order_id,
  product_name,
  COUNT(FARM_FINGERPRINT(TO_JSON_STRING(i))) AS count_duplicates,
  COUNT(DISTINCT FARM_FINGERPRINT(TO_JSON_STRING(i))) AS count_grain

FROM input_data i

GROUP BY ALL

HAVING count_duplicates > 1 OR count_grain > 1
SELECT

  order_id,
  product_name,
  order_status,
  COUNT(FARM_FINGERPRINT(TO_JSON_STRING(i))) AS count_duplicates,
  COUNT(DISTINCT FARM_FINGERPRINT(TO_JSON_STRING(i))) AS count_grain

FROM input_data i

GROUP BY ALL

HAVING count_duplicates > 1 OR count_grain > 1

Results of the two queries. Incorrect grain (order_id, product_name): six groups with count_grain 2, e.g. order 1 Apples 2/2 and order 3 Plums with count_duplicates 4. Correct grain, but there are duplicates (adding order_status): order 3 Plums ORDER_PLACED and ORDER_SENT, each count_duplicates 2 and count_grain 1.

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