Storage Types and Query Speed
Concepts covered: Basic Types, Storage Optimization, Numeric Precision, Date vs Timestamp
Nobody asks about storage implications directly. The question arrives as a join that slowed down after a migration, as "we store every id as a string, is that a problem?", or inside a schema design prompt where the interviewer watches what you pick for the key columns. The strong answer names the short list of places where a type's width costs real time, and says where it costs nothing.
What a value costs in bytes
| Value | Declared type | Bytes per value | Behaviour at scale |
|---|---|---|---|
| payment_id | BIGINT | 8 | Fixed width; compares in one instruction |
| payment_ref from the new ledger | VARCHAR(32) | Length plus a 1 to 4 byte header | Variable width; compared byte by byte |
| Request id | Native UUID | 16 | Fixed width |
| Request id as text | CHAR(36) | 36 plus header | More than twice the native key |
| amount, up to 18 digits | DECIMAL(18,4) | 8 | 64-bit integer arithmetic inside |
| amount, 38 digits | DECIMAL(38,4) | 16 | 128-bit arithmetic in most engines |
| created_at | TIMESTAMP | 8 | Sorts and prunes by time |
| created_at as ISO text | VARCHAR | 19 to 32 | Sorts by characters |
On disk, columnar formats close some of these gaps. Parquet and ORC dictionary-encode a low-cardinality string, so a status column with 6 distinct values costs a few bits a row whatever its declared type. Dictionary encoding gives up on high-cardinality columns, and a join key is high-cardinality by construction: billions of distinct payment ids never fit a dictionary, so a text key is stored and read at close to its full width.
Decimal width follows the same logic. Parquet stores a DECIMAL with precision up to 18 as a plain INT64, and Spark keeps such a value in a single 64-bit long during arithmetic. Declare DECIMAL(38,4) "to be safe" and every value takes 16 bytes and 128-bit arithmetic, on every row of every scan, for headroom the amount column will never use.
Where width is paid: the hash join
The cost shows up in memory, during the join. A hash join builds a table from one side's keys, then hashes every probe row and compares on each hit. An 8-byte integer hashes and compares in a couple of instructions. A VARCHAR key needs its length read, all its bytes hashed and a byte-wise compare on every collision; under a case-insensitive collation, the default in SQL Server and MySQL, the compare also folds case. The build side's hash table grows with key width, and a build side that no longer fits in memory spills to disk in partitions. Spilling is usually where a join measured in minutes becomes one measured in hours.
A mismatched key costs more than a wide one. Join payments.payment_id BIGINT to ledger_entries.payment_ref VARCHAR(32) and the engine has to convert one side on every row before it can hash anything. That conversion can also disable the runtime filters and dynamic partition pruning that let the probe side skip files, because the filter was built over one type and the scan reads another.
Text breaks the pruning statistics
Warehouses and lake formats keep a minimum and maximum per file or micro-partition and skip any file whose range cannot match the filter. For a text column those bounds are lexicographic: '100' sorts before '99', and '2024-3-5' sorts after '2024-12-01'. A filter that casts the text back to a number returns the right rows, but the stored bounds cannot serve it, so the scan opens files a typed column would have skipped. Filtering the text directly keeps the pruning and returns the wrong rows. The same ordering problem undermines clustering and sort keys, which exist to make those bounds tight.
- Worth arguing about
- Join keys on fact tables of hundreds of millions of rows or more
- Partition keys, and the sort or clustering keys that decide what gets pruned
- GROUP BY keys in heavy aggregations, held in hash tables like join keys
- Money and counters, where the type sets the overflow ceiling
- Not worth a meeting
- Descriptive columns on a 2,000-row dimension
- Low-cardinality strings that dictionary-encode to a few bits
- SMALLINT against INT on columns that only ever appear in the SELECT list
- Free text that is only ever displayed
That split is what gets scored. Recommending SMALLINT for every small number reads as over-engineering, and so does a lecture on byte alignment for a lookup table. Saying that payment_id should be BIGINT on both sides of the join, and that the dimension's label column can be whatever is convenient, shows where the bytes are actually spent.
Applied to the reconciliation job, the type change did 3 separate things: it widened the keys in the hash table, added a per-row conversion on one side, and blunted pruning on the ledger's text column. A complete diagnosis names all 3, then says which one the plan shows dominating.
About this lesson
This section is part of the Type Casting: Advanced 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.