cswork / libraryOpen practice workspace

Articles

Apache Pinot Interview Questions: Upserts, Deduplication, and Real-Time Query Consistency

Practice Apache Pinot interviews with an out-of-order upsert ledger, dedup-versus-update choice, key partitioning, and concurrent-query consistency modes.

A stream sends order O-7 at versions 10, 12, and then 11. A dashboard briefly changes its distinct-key count during a later update. Would you call that a deduplication bug, a bad comparison column, or a consistency issue?

The useful interview move is to separate three contracts: which record is the latest for one primary key, whether a repeated key should be dropped or updated, and what snapshot a query sees while many keys are changing. Apache Pinot's current upsert documentation is the official basis for the mechanics below. The O-7 ledger is original practice, not a candidate report or a claim about a named employer's interview. For partition-ordering warm-up, use Reason About Kafka Consumers and Partitions.

First ask what the table promises

Is this a REALTIME table consuming a key-partitioned stream? What are its primaryKeyColumns, upsert mode, comparison column, delete marker, and table-level consistency mode? These questions decide whether a record is a new entity, an update, a duplicate delivery, or a logical deletion. The current Pinot docs cover FULL upsert across REALTIME and, with narrower capabilities, OFFLINE/HYBRID tables; PARTIAL upsert is for REALTIME. This article's ledger uses a REALTIME FULL upsert table, so each winning event replaces the prior row rather than merging a subset of fields.

Pinot needs the incoming stream partitioned by primary key for this design. The upsert metadata map tracks where the valid record for each key lives across segments. If events for O-7 are sent to different Kafka partitions and thus different Pinot owners, a local winner rule cannot reliably enforce one global current row. Key the producer consistently or repartition upstream, then verify representative keys never cross the configured partition boundary. The official upsert page states this partitioning requirement. It is more specific than saying “Kafka preserves order”: Kafka's ordering applies within a partition, while Pinot's key map also depends on the partition/server assignment.

Choose a comparison value that represents authoritative source order, ideally a monotonic database sequence or version for each key. Pinot defaults to its table time column if you do not configure an alternative; current docs recommend comparisonColumns rather than the deprecated singular setting. An event timestamp is useful only if its semantics actually order corrections. Arrival time is different from business time, and two producers with unsynchronized clocks can reverse intended precedence. Do not call a record “newer” before saying newer by what.

Question 1: which O-7 version is queryable?

Assume order_id is the primary key, version is the configured comparison value, and each event is a full row. Our invented stream sends these records in arrival order:

ArrivalKey and versionPayloadExpected normal upsert view
1O-7, 10status=open, amount=40Version 10 visible
2O-7, 12status=paid, amount=40Version 12 replaces 10
3O-7, 11status=open, amount=40Late lower version does not replace 12
4O-7, 13delete marker=trueKey hidden in normal upsert query
5O-7, 14status=paid, amount=40Higher version revives key

The official Pinot guide says a greater comparison value wins for the same primary key. It also documents a delete-record column: a winning delete marker removes the key from the normal queryable set, and a higher comparison value can revive it. Therefore the visible sequence here is open → paid → paid → absent → paid. This is a per-key logical view; older physical records can remain in segments until retention or compaction. A diagnostic query with skipUpsert can reveal historical physical rows, but it is not the business-current view. Depending on mode and dropOutOfOrderRecord, a lower-version arrival may or may not be physically persisted; do not infer physical retention solely from its absence in an ordinary query.

The equal-version case is a deliberate follow-up. Current docs warn that when two records share key and event/comparison time, the winner is not fully determined; later ingestion often wins but should not be relied upon as a universal tie-breaker. Version 12 should therefore have a unique source sequence or a documented deterministic ordering contract. A business correction with version 11 arriving after 12 will not win just because it is a correction. Fix the upstream version semantics or rebootstrap under a controlled plan, rather than silently increasing timestamps in a consumer to force the desired row.

Question 2: why is dedup not the same as upsert?

Pinot's dedup documentation describes a REALTIME ingestion option that drops an incoming record if its primary key is already present. That fits a stream where repeated delivery of the same immutable event ID should leave one event. It does not fit an order table in which O-7 legitimately changes from open to paid: dedup keyed by order_id would drop the payment update after seeing the first record.

For a mutable order state, use upsert keyed by order ID with a trustworthy comparison value. For immutable payment attempts, a dedup table keyed by event ID may be appropriate. If the source retries version 12 exactly, an upsert table may still give the desired visible state, but that does not prove all downstream side effects were applied exactly once. Define the business key separately from the delivery ID. A single user can have several orders, one order can have several valid state transitions, and one transition can be delivered more than once.

Both features have state and partition costs. Dedup documentation requires primary-key partitioning and retains a key map; its metadata TTL must cover the duplicate horizon if enabled. Upsert similarly maintains primary-key-to-record-location state, so cardinality and key size affect memory. A short TTL is a capacity choice with correctness consequences for late replay. Ask for key cardinality, update rate, retention, and maximum lateness before recommending it. A Bloom filter or approximate distinct count is not a substitute for the documented table semantics when the use case needs exact current state.

Question 3: what does a “real-time” query actually see?

Pinot's ingestion FAQ says events become queryable as they are indexed in memory, but open consuming-segment replicas are not immediately transactional or identical. Consumer lag, routing, and segment transitions can make two close-in-time queries observe different progress. This is a visibility boundary, even before considering upsert-specific concurrency.

The current upsert documentation names a second boundary: while queries acquire validDocIds bitmaps from multiple segments, concurrent upserts can invalidate one row and validate another. With the default table consistency mode NONE, a high-ingestion/high-QPS query can temporarily capture an inconsistent combination and overcount or undercount a distinct-key total. The docs use a million-key example where an expected million can briefly differ. That is a query snapshot problem; it does not necessarily mean the per-key comparison rule chose version 11 over version 12.

The documented SYNC mode blocks upserts while queries take a stable view, at the cost of write latency. SNAPSHOT supplies a consistent bitmap snapshot for queries without the same write blocking; it has its own resource and freshness trade-offs. First check the deployed Pinot version and table setting, then reproduce the observed count drift under concurrent updates and compare throughput, query p95, and freshness under candidate modes. Do not promise serializable transactions for an entire external workflow merely because a bitmap view is stable. The mode addresses a specific upsert-query race, not upstream missing or duplicated events.

Question 4: how do deletes, TTL, and restarts complicate recovery?

The O-7 delete marker at version 13 is a logical current-state deletion. A higher version 14 can revive the key. In a PARTIAL upsert table, the official docs say a revived record is treated as the source of truth for all columns; do not assume old merged fields survive resurrection. The example uses FULL mode and sends all required values at version 14, avoiding that trap.

Pinot's upsert state is not just stored rows; it includes in-memory key-to-location and valid-document metadata. Current docs describe snapshots and preloading to speed recovery, plus TTL options to bound metadata. They also warn about interactions among deleted-key TTL, compaction, and restart that can resurrect an older non-deleted record unless the documented consistency protection is configured. An interview answer should not recommend reducing TTL or compacting segments solely from a heap graph. First establish the maximum replay/deletion horizon, test a delete → restart → compaction → higher-version revival sequence, and inspect the exact version's configuration constraints.

Changing primary-key fields, comparison columns, or upsert mode on an existing table is a migration, not a harmless tweak. The current docs identify these fields as effectively immutable because existing valid-document snapshots and maps were built under the old interpretation; they recommend a new table and reingestion for a semantic change. State a cutover plan: build a new table from authoritative events, verify key counts and sample version winners, then switch readers. Keep the old table until rollback and retention requirements are satisfied.

Question 5: how would you debug a wrong result?

Start by classifying the symptom rather than tuning indexes. For one key showing an old payload, compare its source versions, comparison values, Kafka partition, and query output. For two visible rows of one key, inspect whether the stream was partitioned by the primary key and whether the table is actually upsert-enabled. For a key that disappeared, check delete markers, TTL, and the latest comparison value. For a transient aggregate count drift during heavy writes, reproduce against the table's consistencyMode and inspect query/ingest concurrency. For a stale result, measure consumer lag and replica/segment state.

Then articulate a test matrix: out-of-order lower version; equal-version collision; exact retry; deletion and revival; restart; replay after TTL; and a sustained concurrent query that counts expected active keys. The expected normal view for O-7 after version 14 is one current row with the complete version-14 payload. A skipUpsert diagnostic can have a different physical count. Record both, with table config and stream offset boundary, so a reviewer can tell whether the failure is ingestion, version choice, or query visibility.

The concise interview answer is: “I would key the stream by the primary key, order updates with a source-authoritative comparison value, and use upsert for mutable current state. Dedup is for repeated immutable IDs. I would not treat a briefly wrong aggregate under concurrent writes as proof of a wrong per-key winner until I check Pinot's consistency mode and ingestion progress. Recovery must rebuild the key map under the same contract and prove the post-restart current-key set.”

Sources and further reading