What is cardinality?

In depth

What is cardinality? Cardinality is the count of distinct values in a column, and it describes how records in one table relate to records in another.

Email, UUID, and invoice ID usually have a unique value in almost every row (high cardinality). Status, plan tier, and a true/false flag have only a handful of options (low cardinality).

Cardinality also describes relationships between tables:

• One to one: Each employee has one badge ID.
• One to many: One customer, many orders.
• Many to many: Products and tags linked through a bridge table.

These patterns affect the numbers leaders rely on. Grouping by a high-cardinality column clutters charts and slows queries. Grouping by a low-cardinality column makes comparisons cleaner. Join logic depends on relationship cardinality to prevent duplicate rows and inflated totals.

A simple way to compare columns is the cardinality ratio:

cardinality ratio = distinct_count / row_count

As a rule of thumb, ratios above 0.8 signal high cardinality. Ratios below 0.1 signal low cardinality.

Pro tip

Before you join tables or publish a metric, check distinct counts on the join keys. If a supposed one-to-many key turns out to be many-to-many, fix it with a bridge table or a different grain.

Why cardinality matters

• Trustworthy totals: Faulty relationship cardinality creates duplicates and double counting, so reported numbers drift from reality.
• Clear visuals: High-cardinality dimensions crowd charts. Low-cardinality dimensions make trends easier to see and act on.
• Query speed: High-cardinality columns increase index size and slow performance.
• Storage and compression: Some databases compress or index low-cardinality columns more efficiently.

Cardinality in practice

• High-cardinality examples: "user_id", "email", "session_id", "invoice_id".
• Low-cardinality examples: "status" {open, closed}, "plan" {free, pro, enterprise}, "is_active" {true, false}.
• Recommended actions:
  • Use top N, binning, or an "Other" bucket when charting high-cardinality fields.
  • Use approximate distinct functions on very large datasets when exact counts are expensive or unusable. You trade perfect accuracy for speed.
  • Validate relationship cardinality after joins by counting duplicates of the primary key.

Applying cardinality to metrics and data models

Cardinality choices decide whether a metric gives the team one clear answer or a cluttered one.

• When defining or exploring a metric, pick dimensions with manageable cardinality for default breakdowns. Save high-cardinality fields for filters.
• Use top N and grouping to keep charts readable, especially when a dimension contains thousands of unique values.
• When building data models, confirm the join keys form the expected one-to-many pattern. If they don't, adjust the model or add a bridge table to avoid double counting.
• Track distinct counts over time for keys like "user_id" to catch unexpected spikes.

Before your next report goes out, check the distinct counts on its join keys. A quick check prevents inflated totals from reaching a decision.