Pick types that save storage and speed

Different types occupy different amounts of space. On a billion-row table, the difference between INT and BIGINT is 4GB of storage.

INTEGER (4 bytes)

Range: -2.1B to 2.1B. Use for most IDs and counts.

BIGINT (8 bytes)

Range: -9 quintillion to 9 quintillion. Use for timestamps or very large counts.

SMALLINT (2 bytes)

Range: -32K to 32K. Use for age, quantity, status codes.

BOOLEAN (1 byte)

Always use for flags. Never store as VARCHAR('true').

VARCHAR stores only the actual string content, while CHAR pads short values to fixed length. For variable-length data, VARCHAR saves space.

CHAR(10) wastes 7 bytes per row. On 100M rows, that's 700MB of wasted space.

SELECT
  product_id,
  CAST(status AS ___) AS status
FROM products

The difference between INTEGER and SMALLINT is 2 bytes per row. On a table with one billion rows, that adds up to 2 GB of storage. At scale, type precision is never a trivial choice.

Estimate how much space your data uses

VARCHAR length limits are critical for storage planning. Choosing VARCHAR(1000) when you need VARCHAR(100) wastes index space and slows queries.

VARCHAR(n) uses a length prefix (1-2 bytes) plus the actual string bytes. UTF-8 encoding means international characters use 2-4 bytes each.

The Chinese characters "你好" are 2 characters but 6 bytes. VARCHAR(100) can store 100 ASCII characters but only 33-50 Chinese characters depending on encoding.

SELECT
  username,
  LENGTH(___) AS char_count
FROM users
WHERE ___(username) > 50

Auditing actual string lengths before choosing a VARCHAR size prevents wasted storage and avoids truncation surprises.

A good practice is to size VARCHAR to roughly 2x the expected maximum to provide a safety margin without excessive waste.

Store flexible metadata in columns

A MAP stores key-value pairs, like a Python dictionary or JavaScript object. This is useful for sparse data where each row has different attributes.

Use MAP with key-value pairs or MAP_FROM_ENTRIES to build maps from arrays.

Access values with bracket notation or ELEMENT_AT. Missing keys return NULL.

SELECT
  product_id,
  ___ ___ AS color
FROM products

When a key does not exist in the map, the result is NULL rather than an error, so always account for missing keys in your logic.

Query inside arrays and nested fields

When you have arrays or maps, UNNEST "explodes" them into separate rows, making nested data queryable with standard SQL.

UNNEST takes an array and produces one row per element.

Customer C001 had tags [premium, early_adopter], which UNNEST expanded into 2 rows.

A common pattern: UNNEST to expand, then WHERE to filter the individual elements.

SELECT
  customer_id,
  ___ AS tag_count
FROM customers
CROSS JOIN ___(___) AS t(tag)
GROUP BY customer_id

UNNEST creates a Cartesian product between the original row and each array element. Be mindful of row explosion when arrays are large or when multiple arrays are unnested simultaneously.

After unnesting, you can filter, join, and aggregate using standard SQL syntax. The pattern of UNNEST followed by GROUP BY to re-aggregate is one of the most common in data engineering.

Recover gracefully from type mismatches

The chart below shows which compression technique works best for different data patterns. Dictionary compression excels with low-cardinality data (repeating values like status codes). Run-length encoding dominates when data is sorted with long consecutive runs. Delta encoding is ideal for sequential values like timestamps. High-cardinality data with unique values typically cannot be compressed effectively.

When working with untrusted data, providing a fallback value for NULL results keeps downstream queries safe. Use TRY_CAST to avoid crashes and COALESCE to provide sensible defaults.

SELECT
  name,
  ___(rating, ___)
FROM products

Robust error handling with TRY_CAST and COALESCE is essential for production data pipelines. These patterns ensure queries complete gracefully even with invalid input data.

TRY_CAST returns NULL instead of raising an error when conversion fails. This makes it ideal for data from external sources where type consistency cannot be guaranteed.

Combining TRY_CAST with COALESCE is the canonical ETL pattern: attempt the conversion and substitute a safe default if the value cannot be cast.