highcardinality

High cardinality refers to a data column that contains a large number of distinct values relative to the number of observations. In data management, cardinality is the count of unique values in a column. A column is considered high cardinality when most rows have a different value, such as user identifiers, email addresses, session IDs, or IP addresses in web logs. By contrast, low cardinality columns have few distinct values, like status codes or country codes.

In databases and data warehousing, high cardinality affects storage, indexing, and query performance. Indexing a high-cardinality column can improve equality lookups but increases maintenance costs. The accuracy of cardinality estimates in the query planner can degrade, and certain index types (such as bitmap indexes) are less effective for very high cardinality. Hash-based indexes or partitioning can help mitigate some performance issues. In practice, designers may use summary tables, materialized views, or selective indexing to balance speed and space.

In machine learning and data preprocessing, high cardinality categorical features present encoding challenges. One-hot encoding becomes impractical for large alphabets due to memory usage and sparse matrices. Alternatives include ordinal or label encoding, target encoding, frequency encoding, and the hashing trick. Each method has trade-offs: target encoding can cause leakage and overfitting if not regularized or cross-validated; hashing reduces dimensionality but can introduce collisions.

When used as a dimension or key in analytical schemas, high cardinality can lead to very large dimension tables and slower joins. Solutions include employing surrogate keys, compressing identifiers, or aggregating at coarser grain. Overall, the treatment of high cardinality depends on the data context, performance goals, and privacy considerations.