Data Types: Advanced

These are the widths the engine uses for each value in memory. Files on disk usually hold less, which the compression section covers.

Pick the narrowest type that holds the largest value the column could ever take, judged by what the value measures. watch_ms in the Reels view log is a single view's watch time in milliseconds. SMALLINT stops at 32,767, under 33 seconds, so the first long view breaks it. INTEGER reaches 2,147,483,647 ms, almost 25 days, far past any single view, so it fits with room to spare, while BIGINT would spend 4 more bytes per row on range no view will use. IDs are the exception: generators at this scale hand out 64-bit values, and a narrower column fails the first time an ID passes its ceiling, so IDs get BIGINT.

Raw logs often land with every field as text. Keeping watch_ms as VARCHAR costs more bytes than INTEGER and changes how values compare: text is compared character by character, so '15000' sorts ahead of '800' because '1' comes before '8'. Predict the order in each tab before you run it.

The country value is a 2-letter code: 2 bytes of text plus 4 bytes recording where the value ends. Variable-width data is commonly laid out with a 4-byte length or offset per value, so 4 bytes is a sound planning figure. Now scale it: suppose the table takes 10 billion rows a day, and run the arithmetic.

Narrow widths pay off because they multiply. Here is what the 4 bytes INTEGER saves over BIGINT on watch_ms come to at that daily volume.

To price a VARCHAR column, sample it, find the average bytes per value and add the 4-byte length. A city column averaging 9 ASCII characters costs roughly 13 bytes a value; the same length in a script needing 3 bytes per character costs about 31. Storing country as 'United States' rather than 'US' takes it from 6 bytes to 17.

A declared length reserves nothing: VARCHAR(255) holding 'US' costs what VARCHAR holding 'US' costs. The limit acts when text is converted. Casting longer text to a bounded VARCHAR keeps the first n characters and raises no error, so a limit set too low corrupts data quietly.

Both results fit their limits. The limit counts characters, so 'São' keeps 3 characters while taking 4 bytes. A pipeline that casts incoming text to VARCHAR(2) would turn a source's 'Brazil' into 'Br', which matches none of the stored codes, without a single error.

Client attributes change faster than schemas. iOS sends app_version and Android adds network. Meanwhile a test build ships flags that nobody will read next month. A column per attribute means a schema change for each new key and rows that are NULL in most columns. A MAP column holds a set of key-value pairs on each row, so every row carries exactly the keys it has.

MAP(VARCHAR, VARCHAR) names a key type and a value type. All keys in a map share the first, all values share the second, and a key appears at most once per map. You build a map from 2 arrays whose positions pair up: MAP(ARRAY['os', 'network'], ARRAY['android', 'wifi']) holds os as android and network as wifi.

A key can be read 2 ways, and they part company on a missing key. The subscript client_props['os'] fails the whole query when any row lacks the key. element_at(client_props, 'network') returns NULL for that row and carries on. Predict which views get a NULL network, then run it.

Views 101 and 103 send no network key, so element_at returns NULL for them. Written as client_props['network'], the same query would fail on the first view lacking the key with a key-not-present error, which is why subscripts belong only on keys every row is guaranteed to carry, such as os here. cardinality counts the pairs in each map and map_keys lists their keys, a fast way to see which attributes each client sends.

The second row of that comparison is a cost you can price with the storage method: the key app_version costs 15 bytes on every row, 11 for the characters and 4 for the length, so at 10 billion rows a day it adds 150 GB of decoded key text daily, before any value is counted. Repeated keys compress well on disk, but every query that opens the map handles them again.

Pass UNNEST a map and it returns a row for each key-value pair, as 2 columns you name in the alias. Each output row keeps its parent's columns, so view_id repeats for every pair. Predict how many rows view 203 produces before you run it.

A ROW bundles named fields of different types into a single value, much like a small record. Read a field with a dot: device.os. An ARRAY of rows is the common shape for a list of records, such as the reels watched in a session, and UNNEST spreads each record's fields into separate columns. Adding WITH ORDINALITY appends a further column holding each element's position, counted from 1.

Session s-1 becomes 3 rows, a row per reel, each carrying its session's os. The watch_order column preserves viewing order, which matters because SQL guarantees no row order without ORDER BY, and nothing else in the unnested rows records it.

UNNEST yields a row for each element, so the output holds as many rows as all the arrays combined, with every parent column repeated on each. A session with 50 reels repeats its session_id and device 50 times, which adds up before a large unnest. A parent whose array is empty or NULL produces no rows at all, so it drops out of the result.

Video 9002 has an empty hashtags array, so it's absent from the output. When every parent row must appear, this pattern loses rows you need; an outer form that keeps them arrives with the outer join lessons.

The alias after UNNEST names every column it produces, so the number of names is fixed by what is being expanded: 1 for an array of plain values, 2 for a map's keys and values, 1 per field for an array of rows, and 1 more with WITH ORDINALITY. A name list that does not match the column count fails the query before any row is read.

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.

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.