Prompt and context
The team already has a change stream and wants to maintain a dynamic table that supports soft deletes, stream-static joins, or stateful aggregation. Explain how you would decide whether CUSTOM_INCREMENTAL fits, define the incremental logic, validate results, and prepare rollback. Do not merely repeat the Snowflake release note.
What the interviewer evaluates
- Understanding that the developer defines MERGE or INSERT logic while the platform provides scheduling, retries, and transactional guarantees.
- Ability to distinguish incremental refresh, full refresh, and stream/Task orchestration.
- Handling of keys, change ordering, duplicate events, soft deletes, and the initial backfill.
- Complete reasoning about refresh lag, retries, cost, observability, and rollback.
Clarifying questions to ask
- Is the source append-only, CDC, or able to correct history? Should a delete leave a tombstone in the target?
- What is the target table's unique key? How are duplicate, late, and repeated updates ordered?
- Can the logic be expressed incrementally, or must it periodically recompute everything? What lag is acceptable?
- Who reconciles results and alerts during first creation, retries, schema changes, and rollback?
30-second answer framework
I would first verify that changes can be represented by stable keys and a watermark, then test whether custom incremental logic can safely maintain the target. If it fits, I would specify the change range, idempotent MERGE/INSERT conditions, soft-delete behavior, and late-event policy for every refresh. I would reconcile incremental output with sampled full-refresh results. Platform scheduling, retries, and transactions do not define business ordering or deletion semantics. Before launch, I would set metrics and controls for lag, failures, fallback to standard refresh, and full rebuilds.
Step-by-step deep dive
1. Define the incremental boundary
Separate projection, filtering, joins, aggregation, and deletion semantics. Incremental logic has a testable boundary only when affected input rows and target keys can be identified. Keep a full-refresh option for global ordering or nondeterministic logic whose impact cannot be bounded.
2. Design the change-application contract
Give every change a business key, version, or event time, and define the winner for conflicts on one key. The MERGE match must be idempotent. A soft delete needs a delete marker or tombstone so a late old event cannot recreate a deleted row.
3. Handle first run and failures
Start with a controlled dataset and compare custom incremental output with a full-refresh baseline before widening the scope. Retries must be safe to repeat without duplicate rows. Pause publication and fall back to a verifiable full refresh or rebuild when a schema change, unexplained drift, or broken watermark appears.
4. Monitor correctness and cost
Monitor refresh lag, affected-row count, failures, retries, and the source watermark; periodically reconcile incremental and full results. Estimate warehouse compute, storage, rebuild, and downstream query costs separately instead of looking only at one refresh duration.
Model answer
I would treat CUSTOM_INCREMENTAL as an incremental-maintenance contract whose correctness must be demonstrated. First I would require a stable business key, version or watermark for each change, with explicit rules for soft deletes, late events, and conflicts; if affected target rows cannot be bounded, I would keep full refresh. I would then implement idempotent MERGE/INSERT logic, reconcile controlled samples with a full baseline, and test repeats, retries, initial backfill, and schema changes. The dynamic-table platform supplies scheduling, retries, and transactional guarantees, but it does not decide event order or deletion semantics. In production I would monitor watermarks, lag, affected rows, drift, and cost, with pause, rebuild, and standard-refresh fallback paths.
Common mistakes
- Assuming
CUSTOM_INCREMENTALcan automatically incrementalize arbitrary SQL. - Writing MERGE without stable keys, versions, or duplicate-event rules.
- Ignoring soft deletes, late data, or the initial full backfill.
- Treating platform transactions as proof that business results are correct.
- Omitting incremental-versus-full reconciliation, drift alerts, or rollback.
- Comparing only latency and ignoring continuous-refresh and rebuild costs.
Follow-up questions and responses
When should you insist on full refresh?
Prefer full refresh when the impact scope cannot be bounded, the logic contains global ordering or nondeterministic functions, or reconciliation cannot prove the incremental result. Narrow the data range and frequency before deciding whether incrementalization is worthwhile.
How do you handle a late event for the same key?
Carry a monotonic version or comparable event time and accept only newer versions in the MERGE condition. If ordering cannot be trusted, isolate the conflict and alert instead of silently overwriting.
How do you prove the incremental result has not drifted?
Periodically recompute a full baseline by partition or key range, compare row counts, checksums, and business metrics, and retain difference samples in an audit table. Pause publication or rebuild when the difference exceeds a threshold.