Compression and error handling

Concepts covered: Safe Casting

The disk holds less than your estimate

Columnar files store each column's values together, and values within a column tend to repeat: the same countries, the same map keys, timestamps milliseconds apart. Encoders exploit that before a general-purpose compressor runs. Dictionary encoding writes each distinct value once and replaces every occurrence with a small integer code. Run-length encoding replaces a run of identical values with a single value and a count.

Against that dictionary, the 6 rows are stored as the codes 0, 0, 1, 0, 2, 1. Sort the rows by country before writing and the codes form 3 runs, one per country. US covers 3 rows and BR 2, leaving a single IN, so sort order is a storage decision too. Suppose a day of reel_views holds 200 distinct countries: a single byte holds 256 different codes, so every row's country shrinks to 1 byte.

• 60 GB
• 10 GB

The decoded estimate still governs the work after the scan: sorting rows and sending them between machines handle each value at its type width. The 2 savings add up, since a narrow type shrinks the decoded size and a repetitive, well-sorted column shrinks the file.

Values that do not fit their type

Type mismatches surface while a query runs: text that does not parse as a number, a value past INTEGER's ceiling, a map key that is absent, a division by zero. Each raises an error, and a single bad value among 10 billion rows fails the whole query. TRY_CAST, from the earlier type lessons, turns a failed conversion into NULL. Watch it parse raw text into 2 target types, and notice which rows each one loses.

SELECT
  view_id,
  watch_ms_text,
  TRY_CAST(watch_ms_text AS INTEGER) AS watch_ms_int,
  TRY_CAST(watch_ms_text AS BIGINT) AS watch_ms_big
FROM raw_watch

Rows 3 and 4 fail both conversions. Row 5 is a valid number past INTEGER's ceiling, so only BIGINT keeps it. Nothing in a NULL says which of those happened, which is why the raw text stays in the result beside each parsed value.

TRY covers more than casts

TRY(expression) evaluates any expression and returns NULL when it raises one of these errors: a failed cast, a numeric overflow, a division by zero, or an invalid function argument such as a missing map key. Predict all 5 columns, then check your answer against the paragraph under the query.

SELECT
  TRY(CAST('4200' AS INTEGER)) AS good_cast,
  TRY(CAST('12.5' AS INTEGER)) AS bad_cast,
  TRY(CAST(2147483647 AS INTEGER) + 1) AS overflowed,
  TRY(10 / 0) AS divide_by_zero,
  TRY(client_props['network']) AS missing_key
FROM sample_view

Only the first column survives. TRY keeps the query alive and erases the evidence with it: an overflow caused by a type set too narrow looks identical to a value that was never sent, and a report built on top counts fewer views without a single error.

Do

• Keep the raw text column beside every value you parse with TRY_CAST
• Widen the type when overflows recur, then remove the TRY around it
• Use element_at for optional map keys and subscripts for guaranteed ones
• Sort files on repetitive columns before writing so runs form

Avoid

• Wrap expressions in TRY to make a failing query go quiet
• Read a NULL from TRY_CAST as proof that the source sent nothing
• Store numbers or dates as VARCHAR to dodge parse failures at load
• Assume compression removes the cost of a wide type at query time

A change meant to save space shipped this week. Since then the Reels dashboard shows no views longer than roughly 33 seconds, and no query has failed. Here is the new parse step. What happened?

SELECT
  view_id,
  TRY_CAST(watch_ms_text AS SMALLINT) AS watch_ms
FROM raw_watch

• More malformed client text arrived, and TRY_CAST turned it into NULL
• view_id overflowed SMALLINT and was rounded down to its ceiling
• Every view over 32,767 ms overflowed SMALLINT, and TRY_CAST made it NULL
• TRY_CAST clamped long views to 32,767 ms, so they now look shorter

TRY_CAST covers a conversion alone, while TRY wraps any expression, so arithmetic that may overflow needs TRY. The target type decides which valid values survive. With SMALLINT every long view was lost; INTEGER would have kept them, as would BIGINT.

About this lesson

This section is part of the Data Types: 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.