Understanding cardinality

A column's cardinality is how many distinct values it holds against how many rows the table has. In the 6-row orders table, order_id has 6 distinct values, so every row has one of its own: it's a key. customer_id has 3 distinct values across the same 6 rows, so each customer appears 2 times on average, and C1 appears 3 times. The row count and the distinct count are the whole measurement, and aggregates are how you take it.

SELECT
  COUNT(*) AS row_count,
  COUNT(DISTINCT order_id) AS distinct_orders,
  COUNT(DISTINCT customer_id) AS distinct_customers
FROM orders

Read the result as ratios. row_count divided by distinct_orders is 1, so order_id is unique. row_count divided by distinct_customers is 2, so a customer has 2 orders on average, and an average of 2 means some have more. Finding which ones is the HAVING pattern from the beginner lesson, pointed at the key column.

SELECT
  customer_id,
  COUNT(*) AS orders_per_customer
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY orders_per_customer DESC

What cardinality does to a row count

Analysts combine tables by lining rows up on a shared column, and a later lesson covers the syntax for it, the join. The arithmetic of that pairing is what matters here, and cardinality decides it. Picture each order looking up its customer: for every order, the engine copies the matching customer row next to it. With 3 customers, each appearing once in customers, 6 orders become 6 paired rows, and C1's name is copied 3 times.

Now suppose C1 moves from Berlin to Munich and someone loads a second C1 row into customers instead of updating the first. Each of C1's 3 orders now finds 2 customer rows and the pairing copies both. C1 contributes 3 times 2, which is 6 rows instead of 3, the whole result grows from 6 rows to 9, and SUM(amount) over it reports 960 instead of 690, because C1's 270 is counted twice. The orders table didn't change; the duplicate sat on the customers side, which was supposed to be unique, and it inflated every total that touched it. The intro's tripled stream totals follow the same rule with a 3 in place of the 2: each track sits on 3 playlists in the playlist table, so every stream of that track pairs with 3 playlist rows and is counted 3 times.

A products table has 10,000 rows and 10,000 distinct product_id values. A sales table has 50,000 rows and 10,000 distinct product_id values. Each sale is paired with its product on product_id. How many rows does the pairing produce?

• 10,000, 1 per product, because products is the unique side of the pairing
• 60,000, the rows of both tables added together into a single result
• 500,000,000, the product of the 2 row counts, as every pair is formed
• 50,000, 1 per sale, because each sale finds exactly 1 product row

The duplicate detector is the cardinality check turned into a list. GROUP BY on the key makes 1 row per value, COUNT(*) says how many rows share that value, and the condition COUNT(*) > 1 belongs in HAVING, because WHERE runs before the groups exist and has no count to compare. Sorting by the count puts the worst offenders at the top.

A customers table should hold 1 row per customer_id, and a reload may have duplicated some of them. List each customer_id that appears more than once with how many times it appears, most repeated first.

About this lesson

This section is part of the Aggregating: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.