Layer 3 · Go deeper · 6 min
Go deeper
Size it, build it, then make the calls an interviewer will push you on.
Build-up · 5 steps
Read one query in rows, then columns
One table, one aggregate, two layouts. Watch how many bytes each must read.
Step 1 of 5
One table, one question
Assume 1 billion orders with 100 columns averaging 8 bytes: about 800 bytes a row, 800 GB in all. The query needs just two columns, city and amount.
4 interview moments
You’re the candidate
The interviewer pushes on the hard parts. Pick an approach and see how it holds up.
Deep dive 1 of 4
Interviewer
“Restaurant dashboards query the orders primary, and checkout slows at dinner peak. Where should the dashboards run?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“A customer moves from Pune to Bengaluru, and last year’s orders must still report as Pune. How does dim_customer change?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“Order events stream from Kafka into ClickHouse for a live dashboard. How do you write them?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“An analyst runs
SELECT *on a wide orders table in the warehouse to check one day’s revenue. Your advice?”Your move. Pick an approach:
Build-up · 5 steps
Move analytics off the primary
Three places to run analytics, each further from checkout. Each step adds lag and something to operate.
Step 1 of 5
Analytics on the primary
The BI tool queries the database that takes orders. Its scans use the same disks and cores as checkout, and a 40-minute query holds back vacuum, so tables bloat.