Part 6 of 6 · 4 min read
Exercises
Easy
Easy 1: Pick the storage layout (10 minutes)
A table serves thousands of small single-row lookups per second for a checkout page, and a separate table serves monthly aggregate reports over billions of rows but only two or three columns at a time. Which table should be row-oriented and which column-oriented?
Done when: you correctly match the checkout table to row-oriented and the reporting table to column-oriented, with a one-line reason each.
Hint
a checkout page reads and writes one row at a time, favoring row-oriented; a billion-row aggregate report favoring few columns favors column-oriented.
Easy 2: Count the columns touched (10 minutes)
A fact table has 80 columns. A query computes total revenue grouped by month, using only date_key and revenue. In a column store, how many column files does this query need to read?
Done when: you answer 2 and explain why the other 78 columns are never touched.
Hint
only date_key and revenue are read; every other column file is skipped entirely.
Easy 3: Spot the compressible column (10 minutes)
A country column has 5 distinct values across 10 million rows; a transaction_id column has 10 million distinct values across the same rows. Which column compresses far better with bitmap encoding, and why?
Done when: you pick country and explain that few distinct values means few, highly repetitive bitmaps.
Hint
fewer distinct values means each bitmap is overwhelmingly zeros or ones in long runs, which run-length encoding shrinks dramatically.
Medium
Medium 1: Run-length encode a bitmap (20 minutes)
Given the bitmap 0 0 0 1 1 1 1 0 0 1 1 0, write its run-length encoding as a sequence of run lengths.
Done when: your encoding is 3, 4, 2, 2, 1 and reconstructing the bitmap from it matches the original exactly.
Hint
count each run of identical bits in order: 3 zeros, 4 ones, 2 zeros, 2 ones, 1 zero.
Medium 2: Choose a sort key (20 minutes)
The bookstore's analysts almost always filter sales_fact by a date range first, then frequently group by book_id within that range. Propose a sort key order and explain which column benefits most from compression.
Done when: you propose date_key then book_id, and explain that date_key, as the first sort key, gets the best compression and the best range-query speedup.
Hint
date_key as the first sort key gives it the longest repeated runs and lets a date-range query skip straight to the right slice.
Medium 3: Combine two bitmaps (20 minutes)
A query filters WHERE book_id = 42 AND store_id = 3. Explain which bitwise operation combines the two columns' relevant bitmaps, and why it gives the correct matching rows.
Done when: you name bitwise AND and explain that a 1 in both bitmaps at the same position means that row satisfies both conditions.
Hint
AND keeps only rows where both bitmaps have a 1 in the same position, which is exactly rows matching both filter conditions.
Hard
Hard 1: Explain the write problem concretely (25 minutes)
A column store holds sales_fact sorted by date_key, compressed with run-length encoding. Explain concretely why inserting one new row for an existing date, in the middle of the sorted data, cannot simply be written in place.
Done when: your explanation names both the row-position alignment problem across all columns and the fact that compressed data has no mid-file room to splice a new value into.
Hint
every column's file must stay aligned by row position, and a compressed file has no spare space in the middle to insert a new value without rewriting everything after it.
Hard 2: Design a multi-sort-order setup (25 minutes)
The bookstore's warehouse serves two very different query patterns: one filtering heavily by date range, the other filtering heavily by store location. Propose how to use the fact that data is already replicated across machines to serve both patterns well.
Done when: you propose keeping one replica sorted by date_key and another sorted by store_id, with each query routed to whichever replica's sort order matches its filter.
Hint
route a date-range-heavy query to the date_key-sorted replica and a store-heavy query to the store_id-sorted replica, since each replica's own sort order best compresses and speeds up its matching query pattern.
Hard 3: Trace a query against buffered and on-disk data (25 minutes)
A sale happened one minute ago and is still sitting in the in-memory write buffer, not yet merged into the on-disk column files. Explain what the query engine must do to return a correct, up-to-date total that includes this sale.
Done when: your trace explains that the engine checks both the in-memory buffer and the on-disk column files and combines both results, transparently to the person running the query.
Hint
the engine merges the on-disk column files' results with whatever is still sitting in the in-memory buffer before returning a final answer, so recent writes are never silently missed.