CSE Roadmap

Common tier · about 3 hours · 7 parts

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.