cswork / libraryOpen practice workspace

Articles

R data.table Interview Questions: Reference Semantics, Keys, and Non-Equi Joins

Practice R data.table interviews with reference-mutation traces, keyed joins, non-equi FX lookups, cardinality checks, and tested edge cases.

Two R objects point to the same data.table. A helper adds a column to one of them, and a later keyed join returns more rows than expected. Which action changed the caller's data, and which action changed the join's cardinality? A strong data.table interview answer traces both, rather than saying only that data.table is “fast.”

This guide uses the current data.table reference semantics, keys, and joins documentation as official package behavior. The order-and-rate exercise below is original practice, not a report of any employer's exact interview.

The interview thesis: track identity before syntax

The central mistake is treating an R variable assignment as proof of independent data. In ordinary R code, copy-on-modify often protects a value when it changes. data.table deliberately offers operations that change its input by reference. The package documents := and the set* family as the key mutation paths. If alias <- DT, then alias[, flag := TRUE] changes the table that DT also names. An explicit copy(DT) is the boundary when the caller needs an independent table.

That identity rule interacts with keys. setkey(DT, id, day) physically reorders DT by its key columns and marks the columns as its key; it is itself a reference mutation. A key is not a uniqueness constraint. Duplicate (id, day) pairs remain duplicates, and a join can produce multiple matches. You therefore need two ledgers in an answer: which object changed and how many rows each input key can match. The speed claim matters only after those contracts are correct.

Question 1: what changes when a helper receives DT?

Consider this original mini-exercise:

library(data.table)
DT <- data.table(id = c("A", "A", "B"), amount = c(10, 20, 30))
alias <- DT
helper <- function(x) {
  x[, amount2 := amount * 2]
  invisible(x)
}
helper(alias)

After the helper returns, DT has amount2 values 20, 40, and 60. There was no need to return and reassign the table. alias and DT referred to the same mutable data.table object for this operation. The assignment reference says := updates columns by reference and returns the modified table invisibly. That is a useful optimization, but an API surprise when a function claims to be pure.

Now try isolated <- copy(DT); isolated[, amount2 := NULL]. The new table loses its amount2 column; DT keeps it. The copy reference explicitly describes copy() as an entire-object copy. State where to copy: at the function boundary if the helper promises not to mutate its input, or at the caller when the function documents in-place behavior. Copying after a mutation cannot undo a prior shared change.

An interviewer may ask whether DT2 <- DT is ever safe. It is safe if you intend another name for the same mutable table and can account for every reference update. It is not a defensive snapshot. Similarly, names(DT) can share a names vector that changes when columns are added; the copy documentation demonstrates copy(names(DT)) when a stable names snapshot is required. Do not recite “R always copies” or “data.table never copies.” Identify the operator, the object being changed, and the point at which a separate object is required.

Question 2: why does setkey() surprise a downstream check?

Imagine a pipeline that expects arrival order in orders: row 1 is the first observed order, row 2 the second, and so on. A helper calls setkey(orders, customer_id, order_day) for a lookup. The keys vignette explains that setting a key sorts the table by those columns and records the key for binary-search-based subsets. Because setkey() mutates by reference, a later orders[1] now refers to the first row in key order, not necessarily the first arrival.

The repair depends on the requirement. If arrival order is part of the contract, carry an immutable arrival_seq column and explicitly order by it when needed. If the helper is not allowed to reorder the caller's table, key copy(orders) or use a suitable on= join without modifying the source table. A secondary index can also be useful, but it is not a replacement for stating the expected ordering and mutation behavior. Avoid claiming that a key makes a column unique: setkey() can sort several rows with the same key, and they can all match.

Explain setkeyv(DT, c("customer_id", "order_day")) when column names arrive as strings. The distinction between setkey and setkeyv is argument form, not “safe” versus “unsafe”; both operate on the input table. For readable production code, an explicit on=.(customer_id, order_day) often makes a join's columns easier to audit even when a table already has a key. The package's data.table reference recommends on= for that clarity.

Question 3: what does x[i, on=...] retain?

Let rates be x and orders be i. In data.table's native join form, rates[orders, on=.(currency)] uses rows of orders as the driving side and, by default, retains an unmatched orders row with missing rate columns. The official join reference describes this as right-join-like behavior for x[i]; nomatch=NULL changes it to matched rows only. This orientation is easy to invert when someone mentally translates SQL's LEFT JOIN syntax.

Suppose rates has two USD rows and orders has three USD rows. An equality join on currency alone can produce six pairings. That is not a data.table bug, and a key on rates would not fix it. The business rule must decide which rate belongs to which order—perhaps the last effective rate on or before each order date. Before joining, check the uniqueness of the intended effective-date key, the number of candidate rates per order, and whether dates on a boundary are inclusive. Then select or constrain matches accordingly. A join can be syntactically valid and analytically wrong because its predicate encodes too little.

For large or complex joins, say whether you intend one result per order, all valid historical matches, or a bounded number of matches. If many-to-many expansion is genuinely needed, describe an expected upper bound and review any allow.cartesian decision. It should not become a switch that hides an accidental multiplier. If all you need is a compact lookup, a rolling join or another explicit latest-rate selection may fit better than materializing every prior rate and filtering afterward.

Question 4: trace a non-equi join without inventing arithmetic in on

Use a tiny order-and-rate ledger. This is an original practice fixture with no employer attribution:

OrderCurrencyOrder dayRate candidates
O1EUR2026-03-05March 1 at 1.08; March 4 at 1.10
O2EUR2026-03-02March 1 at 1.08
O3JPY2026-03-03None before the order

The intended answer is latest rate with the same currency and rate_day <= order_day. O1 uses March 4; O2 uses March 1; O3 is flagged missing. A non-equi join can first enumerate eligible rates, and then a per-order selection can choose the greatest eligible rate_day. The package join vignette explicitly supports <, <=, >, and >= in on, with the left operand from x and the right operand from i.

rates <- data.table(
  currency = c("EUR", "EUR"),
  rate_day = as.IDate(c("2026-03-01", "2026-03-04")),
  rate = c(1.08, 1.10)
)
orders <- data.table(
  order_id = c("O1", "O2", "O3"),
  currency = c("EUR", "EUR", "JPY"),
  order_day = as.IDate(c("2026-03-05", "2026-03-02", "2026-03-03"))
)
eligible <- rates[orders, on = .(currency, rate_day <= order_day),
                  .(order_id = i.order_id, order_day = i.order_day,
                    matched_rate_day = x.rate_day, rate), nomatch = NULL]

The predicate finds two candidate rows for O1 and one for O2; it does not by itself choose “latest.” Group by order_id and select a documented maximum date, with a deterministic tie rule if the rate source permits two rates on one day. Because nomatch=NULL drops O3 from eligible, compare back to all orders to flag missing FX. A candidate who returns only the matched table has silently lost the missing-data case. You could instead use the documented rolling-join approach to choose the most recent past observation, but still check maximum staleness and equality-at-boundary rules.

The column prefixes x. and i. in the projection matter here. The join vignette's non-equi section warns that the output name of a join column can come from x while the displayed value comes from i; explicitly project both sides when auditing values. Non-equi on is also not a general expression evaluator. If the requirement is rate_day <= order_day - 30, calculate a cutoff column in orders first and join against that column. Do not put order_day - 30 directly in on and assume the package accepts arbitrary arithmetic there.

Question 5: how do you choose between a key, an index, and on=?

Begin with the access pattern, not a benchmark slogan. A key sorts the table and supports keyed subsets; its ordering can be useful if repeated queries exploit the same leading columns. That sort is a material change to the table. on= specifies the join columns at the call site and can join unkeyed data. A secondary index provides another search path without sorting the entire table in key order, but it still consumes memory and introduces maintenance considerations. The official keys and secondary-index vignettes are better guides than a blanket claim that one is always fastest.

In an interview, propose a fair comparison: pin package version; generate realistic row counts, duplicate rates, selectivity, and query repetition; include key or index build time when relevant; check identical result sets before timing; and observe memory as well as latency. For a one-off join, paying to reorder a large table may not help. For repeated selective lookups on stable columns, the setup cost may amortize. A wrong one-to-many result can also appear “fast” because it skipped a required disambiguation step. Correctness and cardinality are part of performance analysis.

Question 6: what should the test assert?

Make tests expose the two failure families independently. For mutation, save a copy(DT) before calling a supposedly pure helper, then assert the original's column names, values, key, and order remain as promised. For an explicitly in-place helper, assert the caller sees the new column and document that contract. For joins, assert one output per order when the business rule says one; assert O1's 1.10, O2's 1.08, and O3's missing flag; add a duplicate same-day rate and define the tie result or reject the input. Also test an order exactly on a rate date, an order before all rates, and an order whose prior rate is older than the maximum allowable age.

Do not use identical() on entire tables as your only oracle when the order or attributes may legitimately differ; specify which differences are allowed. Conversely, a row-count-only assertion can miss the wrong rate attached to the right number of orders. A robust answer checks keys, values, types, cardinality, and missingness. The package's address() can help demonstrate aliasing in a local investigation, but the public API contract should be expressed through observable outcomes. This fixture is deliberately small enough to reason through by hand before scaling it.

Sources and further reading