Part 7 of 7 · 3 min read
Exercises
Easy
Easy 1: OLTP or OLAP (20 minutes)
Label each: show a user's last 10 orders, total sales per country last quarter, update an address, count visitors per hour for a year, mark an order as shipped.
Done when: five labels, each with the number of rows touched.
Hint
Count the rows each query must touch.
Easy 2: Name the facts (20 minutes)
A ride-hailing company wants to analyse trips. List the columns of a fact_trips table and at least four dimension tables.
Done when: a fact table with columns and four dimensions with their columns.
Hint
Events with numbers are facts; who, what, where and when are dimensions.
Easy 3: Bytes read (20 minutes)
A table has 30 columns of 8 bytes and 200 million rows. A query reads 2 columns. How many bytes does a row store read, and a column store? What is the ratio?
Done when: two sizes in GB and a ratio.
Hint
Row store reads rows × columns × bytes. Column store reads rows × chosen columns × bytes.
Medium
Medium 1: Write the query (30 minutes)
Using the fact_sales, dim_product and dim_store tables of section 3, write SQL for the total revenue of the category Dairy in the city Delhi. Then show the result for the three sample rows.
Done when: a query and a result of 55.
Hint
Join on the two keys and filter on the two descriptions.
Medium 2: Bitmaps by hand (30 minutes)
Ten rows have colour: R, G, B, R, R, G, B, B, R, G. Write the bitmap for each colour. Then compute colour = R OR colour = B, and the count of matching rows.
Done when: three bitmaps, an OR result and a count of 7.
Hint
One bit per row for each colour.
Medium 3: Run-length savings (30 minutes)
A column of 500 million rows sorted by country has 190 countries. Compare the plain size at 2 bytes per value with run-length encoding at 10 bytes per run. A second column sorted within each country by city has 40,000 cities in total. Estimate its encoded size and comment.
Done when: three sizes and one sentence on why the second key compresses less.
Hint
Runs × bytes per run. Second key has up to 40,000 runs.
Hard
Hard 1: Design a warehouse (45 minutes)
A streaming service wants reports on watch time by title, device, country and day. It has 30 million users and 8 plays a day each. Design the fact table and dimensions, estimate rows and size per year at 80 bytes per row, choose sort keys, and list three materialized summaries.
Done when: a schema, a yearly size, sort keys with reasons and three summaries.
Hint
Events are the plays. Think of title, user, device, country and date as dimensions.
Hard 2: Pipeline plan (45 minutes)
Orders live in a relational database at 500 writes per second. Analysts need dashboards no older than 10 minutes. Compare a nightly dump, a 5-minute batch and a change stream. For each, estimate the load on the source and the delay, and recommend one.
Done when: a table of three options with delay, load and risk, and a recommendation.
Hint
Rows per second × time between loads gives the batch size.
Hard 3: Row or column (50 minutes)
A system stores 5 billion events with 60 columns. Two workloads share it: point lookups of one event by id (2,000 a second) and dashboards that scan 2 columns over 30 days. Describe a design that serves both, naming where each workload runs and how the data gets from one to the other.
Done when: a drawing, a data flow and the freshness of the dashboards.
Hint
Keep the live store for lookups and feed a column store for the scans.