A columnar analytics store serves billion-row scans well, but the same data also needs single-record lookups; how do you decide what each access path gets?
answer
- one layout cannot own both patterns
- classify by selectivity and columns touched
- wide scan versus narrow full-width read
- the second copy needs a named owner
- lag, drift and reconciliation are the real cost
basics
~20 sClassify the access paths by selectivity and columns touched, then match each to the layout whose cost model fits. A scan-optimised columnar store and a record-optimised serving store are usually both justified — and the decision you actually own is the synchronisation path between them.
solid answer
~50 sStart from measurements, not preference: for each access path, how selective is it, how many columns does it touch, what latency does it owe, and how often does the data change. Wide scans of few columns belong in a columnar layout; narrow full-width reads and per-record updates belong in a record-oriented serving store, because a columnar point lookup visits every column's block and reassembles, so its latency tracks table width rather than result size. Where both patterns are real, two copies is usually the honest answer — and the cost you take on is not storage but the pipeline between them: lag, partial failure, schema drift, and the reconciliation you need to prove they agree. A middle option exists if updates are the real driver: record row-level changes as side files over the columnar data and compact them periodically, trading read amplification for cheap writes.
go deeper
Recall the asymmetry: a layout that makes wide scans of few columns cheap makes full-width reads of one record expensive, so the two access patterns usually want different storage.
Explain the two cost models side by side — cost per column touched against cost per record touched — and why block ordering inside a columnar file cannot rescue a point lookup.
Argue from measurements: selectivity, projection width, latency budget, update rate. Then describe what a synchronisation pipeline between two copies actually requires to stay trustworthy.
Own the trade-off explicitly. Publish a freshness target per consumer, name the pipeline's owner, schedule reconciliation, and set the condition under which the second copy is retired rather than maintained by habit.
## Why this is a judgement call and not a lookup Both layouts have honest cost models and they point in opposite directions. A columnar layout's cost tracks **columns touched**; a record-oriented layout's cost tracks **records touched**. Analytical queries touch three columns of a billion records; a serving lookup touches all 200 columns of one record. No single arrangement is good at both, and the interesting part of the question is what you are willing to pay to have both. The standard mistake is to decide on principle — "one copy, one source of truth" — before measuring, and then discover that the point-lookup path is two orders of magnitude off its latency budget with no local fix available. ## The evidence to gather first 1. **Selectivity per access path.** What fraction of records does each query touch? Full scans and near-point reads are the two ends, and everything in between behaves like one of them. 2. **Columns touched per access path.** A projection of 3 of 200 is a different animal from `SELECT *`, and the second is what makes columnar lookups expensive. 3. **Latency obligation.** An interactive lookup with a tens-of-milliseconds budget and a dashboard query with a several-second budget cannot be argued about in the same sentence. 4. **Change rate and shape.** Append-only, late-arriving corrections, or per-record updates in place — each pushes the answer differently, and the third is the one columnar layouts handle worst. 5. **Freshness tolerance per consumer.** How stale may each reader be? This number, more than anything else, decides whether a second copy is acceptable. ## What each answer costs | Option | Fits when | What you own | |---|---|---| | Columnar only | Point lookups are rare, tolerant of seconds, and narrow in projection | Slow, width-proportional lookups you cannot tune away | | Record-oriented only | Scans are rare or run over small extracts | Scans that read entire records to use a few fields | | Both, synchronised | Both patterns are real and have budgets | A pipeline: lag, failure handling, schema drift, reconciliation | | Columnar plus row-level change files | Updates drive the problem more than lookup latency | Read amplification and a compaction schedule | The third row is chosen most often and is the one that gets under-specified. Two copies are only equivalent while the pipeline between them keeps up; the moment it lags or partially fails, two consumers of "the same data" disagree, usually in a way nobody notices until a reconciliation is attempted. The work you are signing up for is therefore not the disk space: - **Lag is a product decision, not a detail.** Publish the expected staleness per consumer and alert on it, rather than letting readers assume freshness. - **Schema changes must cross both copies.** A column added in one and not the other is the most common drift, and it usually surfaces as a wrong number rather than an error. - **Partial failure needs a defined outcome.** Replay from a point, idempotent writes, and a way to answer "is the serving copy complete for this window?". - **Reconciliation must be routine.** Counts and aggregates compared per window, on a schedule, not written at incident time. ## Arguments to reject - **"We will make the columnar store fast for lookups by putting the key column first."** Block order does not change the cost model: a full-width read still visits every column's block. - **"Chunk statistics will find the record for us."** Statistics bound a block's values; they can rule chunks out, but unless the data is clustered by the lookup key they rule almost nothing out, and they never locate a single record. - **"Duplication is the bigger risk."** Sometimes true, but state it as a comparison: the risk of a stale second copy against the risk of an access path that misses its budget permanently. Only one of those can be fixed later by tuning. - **"We will decide after launch."** The write path determines clustering and chunking, and rewriting historical data to change them is the expensive part. ## The decision, stated as a lead would state it Match each access path to the layout whose cost model fits it, and accept a second copy only when both paths have a real budget and the freshness tolerance of the serving path can actually be met. Name the owner of the pipeline, publish the staleness target, schedule reconciliation, and revisit the arrangement when the mix changes — if the lookup path quietly becomes rare, the second copy stops paying for itself and should be retired rather than maintained out of habit. The point is not which layout wins; it is being explicit about which costs you have chosen to own.
- What evidence would you demand before agreeing to a second copy?Measured selectivity and projection width per access path, the latency budget each owes, the update rate, and the freshness each consumer can tolerate. Then the cost of the pipeline itself: who owns it, what its failure modes are, and how disagreement between the copies would be detected. Without the freshness number the decision cannot be made honestly.
- How do row-level change files over a columnar dataset change the calculus?They make updates and deletes cheap without rewriting column blocks: changes are recorded alongside and merged at read time, then folded in by periodic compaction. That removes the update objection but not the lookup objection — a full-width point read still visits every column — and it adds read amplification plus a compaction schedule to own.
- When is one columnar copy genuinely the right answer despite real lookups?When the lookup path is rare, tolerates seconds, and projects few columns — an operator investigating one record, not a user-facing profile fetch. The width penalty then falls on a low-volume path with a loose budget, and avoiding a synchronisation pipeline is worth more than the latency you give up.
saying these in an interview costs you the question
- Picks one layout for everything purely to avoid a second copy
- Assumes a point lookup is cheap if the key column is stored first
- Treats duplication as free and ignores the synchronisation path's failures
- Promises interactive point-lookup latency from a scan-optimised layout
- Ignores update rate and freshness tolerance when choosing
- Defers clustering and chunking decisions until after the data is written