Data cardinality: what it is and how it shapes your dashboards

Data cardinality is the number of distinct values in a column. A country column has low cardinality (a couple hundred possible values), an order_status column has very low cardinality (a handful of states), and a user_id or email column has high cardinality (one distinct value per row in the extreme case). Cardinality is not a quality score. It is a property of your data that quietly decides whether a chart is readable, whether a filter is usable, and how much work a query has to do. When a dashboard is slow, a legend has 4,000 entries, or a dropdown never finishes loading, high cardinality is often the reason.

This guide is for analysts, operators, and engineers who build dashboards on real business data. It covers what cardinality means, why it matters for query performance and chart design, how databases use it to plan queries, and a practical playbook for taming high-cardinality columns without throwing away detail.

What data cardinality actually means

Cardinality counts distinct values, not rows. A table with 10 million orders might have:

• order_id: ~10 million distinct values (one per row). Very high cardinality.
• customer_id: maybe 200,000 distinct values. High cardinality.
• product_id: maybe 5,000 distinct values. Medium cardinality.
• country: ~195 distinct values. Low cardinality.
• status: 5 distinct values (pending, paid, shipped, refunded, cancelled). Very low cardinality.

The same column can shift cardinality depending on grain. A signup_date truncated to the month has low cardinality (12 values per year). The same timestamp kept to the second can have millions of distinct values. That distinction matters, because you often get to choose the grain, and choosing it well is one of the cheapest performance wins available.

Two terms are worth separating. In everyday BI usage, “cardinality” describes the distinct-value count of a single column. In data modeling, “cardinality” also describes the relationship between tables (one-to-one, one-to-many, many-to-many). This post is about the first meaning: how many distinct values live in a column, and what that does to your dashboards.

Why cardinality matters for dashboards

Cardinality shows up in four places, and each one bites differently.

Chart legibility. A bar chart with 12 categories is readable. The same chart grouped by customer_id produces thousands of bars, an unreadable legend, and a color palette that recycles until every series looks the same. A line chart broken out by a high-cardinality dimension becomes a solid block of overlapping lines. The chart is not wrong, it is just useless. High-cardinality dimensions belong in tables, search, or top-N views, not in a stacked bar or a pie.

Query cost. Grouping and counting distinct values over a high-cardinality column is expensive. GROUP BY customer_id over 10 million rows has to build and track a large number of groups. COUNT(DISTINCT user_id) has to keep track of every unique value it has seen, which grows with cardinality. On a transactional database serving your app, a heavy group-by can compete with production traffic. See our performance playbook for slow BI dashboards for the query-level fixes.

Filters and controls. A dropdown filter works when it holds a few dozen options. Point it at a 200,000-value customer_id column and it becomes unusable: slow to load, impossible to scroll, useless for finding one value. High-cardinality filters need a search-as-you-type control or a typed input, not a dropdown.

Storage and compression. Columnar analytics engines compress low-cardinality columns extremely well and high-cardinality columns poorly. Microsoft’s Power BI guidance is explicit that the VertiPaq engine hash-encodes non-numeric columns by assigning an identifier to each distinct value, so reducing cardinality is one of the most effective ways to shrink a model and speed up scans. The same logic applies to warehouses like Snowflake, BigQuery, ClickHouse, and Redshift: fewer distinct values means better compression and cheaper scans.

Cardinality tiers and what to do with them

The tier is a design signal. When a field lands in the “high” or “very high” row, that is your cue to reach for a different chart type, a search filter, or an aggregation, rather than forcing it into a standard breakdown.

How databases use cardinality to plan queries

Cardinality is not just a dashboard concern. Your database uses estimated distinct-value counts to decide how to run a query in the first place. PostgreSQL stores an n_distinct estimate for each column in its statistics and uses it, along with most-common-value lists, to guess how many rows a WHERE clause or join will return. Those estimates drive the planner’s choice of scan and join strategy. When the estimate is wrong, often because statistics are stale after a big data change, the planner can pick a bad plan and a query that should take milliseconds takes minutes.

The practical takeaway: if a dashboard query is suddenly slow after a large load or backfill, refreshing table statistics (for example, ANALYZE in PostgreSQL) is often the first thing to try, before you start rewriting SQL.

A playbook for handling high-cardinality columns

You rarely want to delete a high-cardinality column, because it usually carries the identity you need for joins and lookups. The goal is to stop asking a dashboard to display or aggregate raw high-cardinality values directly. Here is the order to work through.

1. Ask whether it should be a dimension at all. A customer_id is a great join key and a terrible chart axis. If you find yourself grouping by an ID to make a chart, you probably want to group by an attribute of that entity instead: customer plan, region, signup_cohort, or industry. Push the high-cardinality key into the join and chart the low-cardinality attribute.

2. Bucket continuous values. Turn a raw amount, age, or duration into ranges: 0 to 10, 10 to 100, 100 to 1000. Truncate timestamps to the day, week, or month. Bucketing collapses thousands of distinct values into a handful and makes both charts and filters usable. This is the single most common fix.

3. Use top-N plus an “other” bucket. Show the top 10 or 20 values by revenue, count, or whatever matters, and roll the long tail into a single “other” row. A top-20 products chart answers the real question (“what sells?”) far better than a 5,000-bar chart nobody can read.

4. Replace dropdowns with search. For filters on medium and high-cardinality columns, use a search-as-you-type control or a free-text input backed by an indexed lookup. Never render a select box with 50,000 options. Modern BI tools, including Basedash, handle this by letting you type to filter instead of scrolling a giant list.

5. Use approximate distinct counts at scale. When you only need “roughly how many unique users,” an exact COUNT(DISTINCT) over a very high-cardinality column can be slow and memory-hungry. Warehouses offer approximate functions built on HyperLogLog: BigQuery’s APPROX_COUNT_DISTINCT trades a small error (roughly a fraction of a percent) for a large speedup, and Snowflake, Redshift, and ClickHouse have equivalents. Use exact counts for billing and finance, approximate counts for exploration and trend dashboards.

6. Pre-aggregate the heavy stuff. If a high-cardinality group-by runs on every dashboard load, compute it once. A nightly rollup table, a materialized view, or a summary model turns an expensive scan into a cheap lookup. This is also where a warehouse copy or a read replica earns its keep, so heavy reporting queries do not compete with your application. Our guide to modeling data for BI covers where to draw the grain.

Deciding which fix to use

• The value is an identity you join on, not a thing you compare: push it into the join, chart an attribute instead.
• The value is continuous (amount, duration, timestamp): bucket or truncate it.
• The value is categorical but has a long tail: use top-N plus “other.”
• You need the count of uniques, not the values themselves: use exact counts for money, approximate for everything else.
• The same expensive breakdown runs constantly: pre-aggregate it.

When high cardinality is not a problem

High cardinality is fine, and even necessary, in several cases. Do not “optimize” it away:

• Join keys. order_id and customer_id are supposed to be unique or near-unique. That is their job.
• Detail tables and lookups. A searchable table of individual orders or users is meant to show high-cardinality values one row at a time. Cardinality only hurts when you aggregate or chart it.
• Small data. On a few thousand rows, a high-cardinality group-by is instant. Reach for these fixes when data volume or dashboard latency actually justifies them, not preemptively.
• Warehouses tuned for it. A columnar warehouse handling a COUNT(DISTINCT) over a partitioned, well-clustered table may be perfectly fast. Measure before you assume.

The discipline is simple: match the cardinality of a column to how you are using it. Use low-cardinality columns for grouping, coloring, and dropdowns. Use high-cardinality columns for identity, joins, search, and detail. Trouble starts when the two get swapped.

FAQ

What is the difference between high and low cardinality? Low cardinality means a column has few distinct values relative to its rows, like a status column with five states. High cardinality means many distinct values, like email or user_id where nearly every row is unique. It is a description of the data, not a judgment, but it strongly influences how you should chart, filter, and query the column.

Does high cardinality slow down queries? It can. Grouping by or counting distinct values over a high-cardinality column forces the database to track many groups or many unique values, which uses more memory and time. Databases also estimate cardinality to plan queries, so stale statistics on a high-cardinality column can lead to a bad plan. Bucketing, top-N, pre-aggregation, and approximate distinct counts are the usual fixes.

How do I reduce the cardinality of a column? Lower the grain. Truncate timestamps to a day or month, round or bucket numeric values into ranges, group rare categories into “other,” or replace a composite identifier with the specific attributes you actually filter on. The goal is fewer distinct values while keeping the detail you need for analysis.

Should I ever chart a high-cardinality column directly? Almost never as a full breakdown. A chart grouped by thousands of distinct values is unreadable and slow. Instead, show the top N values with an “other” bucket, chart a lower-cardinality attribute of the same entity, or move the raw values into a searchable table where they are viewed one row at a time.

Is cardinality the same as a many-to-many relationship? Not exactly. In data modeling, “cardinality” also describes how tables relate (one-to-one, one-to-many, many-to-many). In everyday BI and this guide, cardinality means the distinct-value count of a single column. Both matter, but they answer different questions: one is about relationships between tables, the other is about the variety of values inside one column.

Do I need a data warehouse to handle high-cardinality data? Not necessarily. Small and medium datasets on a transactional database handle high cardinality fine, especially with sensible filters and top-N views. A warehouse or read replica helps when heavy distinct counts or group-bys run often enough to compete with production traffic or slow the dashboard, at which point pre-aggregation and columnar storage make a real difference.