Implementing Dictionary Encoding for Categorical Data

Apply dictionary encoding to compress repetitive categorical values, reducing storage footprint and accelerating filter operations on low-cardinality columns.

In this lesson, you will learn how dictionary encoding shrinks your data footprint by replacing repetitive strings with compact numeric identifiers.

When you work with categorical columns—like "Country," "Status," or "Department"—you often encounter the same strings repeating millions of times across a dataset. Storing "United States" repeatedly is inefficient; it consumes significant memory and increases the amount of data transferred from disk. Dictionary encoding solves this by decoupling the data from its definition.

How It Works

Instead of storing the full string in every row, we create a dictionary that maps each unique string to a small integer (a surrogate key). The actual data column then stores only these integers.

Imagine a column tracking shipping status:

Original Data: ["Shipped", "Pending", "Shipped", "Delivered", "Pending"]
Dictionary: {0: "Delivered", 1: "Pending", 2: "Shipped"}
Encoded Data: [2, 1, 2, 0, 1]

The Engineering Benefits

Reduced Storage: Integers occupy far fewer bytes than repeating long strings, drastically lowering your storage footprint.
Accelerated Filtering: When you run an analytical query like SELECT * FROM orders WHERE status = 'Shipped', the database engine doesn't need to perform expensive string comparisons. Instead, it looks up the ID for 'Shipped' (which is 2) and performs a high-speed integer comparison against the encoded column.
CPU Efficiency: By reducing the data size, you improve cache locality. Because the values are now small, more of your dataset fits into your CPU cache simultaneously, further accelerating your scan operations.

Dictionary encoding is most effective for "low-cardinality" columns—where the number of unique categories is relatively small compared to the total number of rows. If every row has a unique value (like a GUID), a dictionary becomes as large as the data itself, offering no benefit. However, for columns like region or user_role, it is a fundamental technique for minimizing I/O and boosting query performance.

Key Takeaway: Dictionary encoding transforms repetitive strings into compact integers, drastically reducing storage costs and enabling high-performance integer-based filtering in analytical queries.