CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Name the two access patterns (15 minutes)

Name the two access patterns this page contrasts.

Done when: OLTP and OLAP are both correctly named.

Hint

OLTP and OLAP.

Easy 2: Name the resource problem (15 minutes)

Name the problem that occurs when both workloads run on the same database at the same time.

Done when: resource contention is correctly named.

Hint

Resource contention.

Easy 3: Name the three steps of ETL (15 minutes)

Name the three steps of the process that moves data into a data warehouse.

Done when: extract, transform, and load are all correctly named, in order.

Hint

Extract, transform, load.

Medium

Medium 1: Classify a query (25 minutes)

Classify UPDATE orders SET status = 'shipped' WHERE order_id = 9001; as OLTP or OLAP, and justify it.

Done when: OLTP is chosen, justified by the query touching exactly one row by key, as a small write from presumably an operational event.

Hint

OLTP, since it touches exactly one row by key as a small write.

Medium 2: Classify a different query (25 minutes)

Classify SELECT book_id, COUNT(*) FROM order_items GROUP BY book_id ORDER BY COUNT(*) DESC; run over the bookstore's full order history, as OLTP or OLAP, and justify it.

Done when: OLAP is chosen, justified by the query scanning a large number of rows to compute an aggregate count per book, rather than looking up a specific known row.

Hint

OLAP, since it scans a large number of rows to compute an aggregate count per book.

Medium 3: Explain the contention risk concretely (30 minutes)

Explain, in concrete terms, what could go wrong if the bookstore ran its monthly revenue report directly against the live operational database during peak shopping hours.

Done when: the explanation correctly notes that the report's large scan would compete for I/O, memory, or locks with concurrent checkout transactions, risking slower or failed checkouts for real customers during the scan.

Hint

The large scan would compete for I/O, memory, or locks with concurrent checkout transactions, risking slower or failed checkouts for real customers.

Hard

Hard 1: Design an ETL schedule for a scenario (30 minutes)

The bookstore wants its analysts to have access to data no more than one hour old, without affecting checkout performance. Propose an ETL approach that satisfies both constraints.

Done when: a reasonable approach is proposed, such as an ETL process running on a short, frequent schedule, for example every hour, extracting only new or changed records since the last run, so the warehouse stays close to current without requiring a disruptive full reload.

Hint

An ETL process on a short, frequent schedule, extracting only new or changed records since the last run, keeping the warehouse near-current without a disruptive full reload.

Hard 2: Identify what the warehouse schema should prioritize (35 minutes)

Explain why a schema optimized for OLTP's small, by-key lookups is not necessarily the best schema for OLAP's large aggregate scans, using what this page covers.

Done when: the explanation correctly notes that OLTP's indexing and schema choices are optimized for finding a few specific rows quickly, which is a different goal from efficiently scanning and aggregating over a huge fraction of all rows, the access pattern OLAP needs instead.

Hint

OLTP's schema and indexing are optimized for finding a few specific rows quickly, a different goal from efficiently scanning and aggregating over most or all rows, which is what OLAP needs.

Hard 3: Decide whether the bookstore needs a warehouse yet (40 minutes)

The bookstore currently has one small operational database and runs only a handful of simple monthly reports against a few thousand orders. Decide whether a separate data warehouse is justified yet, and explain your reasoning.

Done when: a reasonable decision is reached and justified, such as concluding a separate warehouse is not yet justified given the small data volume and infrequent, lightweight reporting, with ETL and a dedicated warehouse becoming worthwhile only once data volume, query frequency, or contention risk grows significantly.

Hint

A separate warehouse is likely not yet justified, given the small data volume and light, infrequent reporting; it becomes worthwhile as data volume, query frequency, or contention risk grows.