Prompt and Scope
The interviewer gives you an event table that receives continuous writes and asks you to build a daily fact table. The first run may scan history, but later runs should process only changed data. Events can arrive late, be updated, or be duplicated, and model columns can change. Explain the filter boundary, unique key, incremental strategy, backfill plan, and recovery after failure.
Assume the warehouse supports SQL, the target is aggregated by dateday, and each event has eventat, updatedat, and a stable eventid. State these assumptions at the start. Without a stable key, update semantics and deduplication change.
What the Interviewer Evaluates
The interviewer wants to see whether you can reduce scanned data without sacrificing correctness. A basic answer mentions isincremental() and a timestamp filter. A strong answer explains why the filter must cover late arrivals, why the aggregate needs a uniquekey matching its grain, and when --full-refresh is mandatory.
They are also testing whether you separate three risks: missing late events, writing duplicate rows at the business grain, and mixing old and new logic after a historical transformation changes. Current data-engineering interview material treats incremental models, snapshots, and dependency graphs as practical preparation topics; this question combines SQL, data modeling, and run governance.
Clarifying Questions Before Answering
What is the business grain?
If one row represents a day, dateday can be the unique key. If one row represents a user and a day, the key should be (userid, date_day). Grain changes the merge condition, duplicate checks, and backfill cost.
How late can data arrive?
If events are normally no more than two days late, recompute the most recent three days. If there is no useful upper bound, a fixed window is insufficient; use a watermark, partition repair, or periodic full reconciliation. A window must cover the accepted lateness distribution.
Which field represents an upstream update?
eventat is business time, while updatedat is the last modification time. Filtering only by eventat misses an old event that was corrected later. Prefer a trustworthy updatedat or change sequence and verify that the source does not move it backward.
How are model or column changes released?
Adding a column, removing a column, and changing calculation logic need different treatments. Confirm whether onschemachange may synchronize structure, whether old rows must be backfilled, and whether a full refresh can be scheduled.
30-Second Answer Framework
“I would first confirm the target grain and lateness bound. The first run builds the model from all history. Later runs use isincremental() to filter by updatedat and look back across a lateness window. The target declares a unique_key that matches its grain, so recent days are updated rather than appended as duplicates. I deduplicate the window by event version before aggregating, then write with merge or the warehouse equivalent. I test the window, key, and schema-change behavior. If logic changes make historical results inconsistent, I run a controlled --full-refresh and rebuild affected downstream models. Finally I monitor rows processed, the maximum update time, duplicate keys, and incremental-versus-full differences.”
Step-by-Step Deep Dive
1. Define a full-refresh correctness baseline
Write the full query first: read all events and aggregate at the target grain. That is the correctness baseline. Incremental output must reconcile with a full calculation over the same time range. Optimizing before establishing this baseline makes a missed record hard to detect.
2. Choose the incremental filter boundary
An incremental branch applies only when the target table exists, --full-refresh is absent, and the model is configured as incremental. One option is to subtract a lateness window from the target's maximum update time:
{{
config(
materialized = 'incremental',
unique_key = ['date_day'],
incremental_strategy = 'merge'
)
}}
with source_events as (
select *
from {{ ref('app_events') }}
{% if is_incremental() %}
where updated_at >= (
select coalesce(max(updated_at), '1900-01-01') from {{ this }}
) - interval '3 day'
{% endif %}
)
select
cast(event_at as date) as date_day,
count(distinct event_id) as events,
max(updated_at) as max_updated_at
from source_events
group by 1The three-day value is an interview assumption, not a universal constant. Choose the window from lateness, the SLA, and recomputation cost. Adapt the date expression to the warehouse.
3. Make the unique key match the model grain
For a daily table, dateday is the key. For a user-day table, use ['userid', 'date_day']. Key columns must not contain nulls, or a merge may fail to match and create duplicates. Without a key, many adapters behave as append-only, so recomputing a window can write multiple rows for one grain.
4. Deduplicate inside the window before aggregation
Replay or CDC updates can produce several versions of one event. Sort by event_id and update time, retain the latest version, then aggregate:
with ranked_events as (
select
*,
row_number() over (
partition by event_id
order by updated_at desc, ingest_seq desc
) as rn
from source_events
),
deduped_events as (
select * from ranked_events where rn = 1
)
select
cast(event_at as date) as date_day,
count(*) as events,
max(updated_at) as max_updated_at
from deduped_events
group by 1Use ingest_seq only if it is a stable tie-breaker for equal timestamps. Otherwise call the tie rule an unresolved source contract. Deduplication must precede aggregation, or two versions of one event are both counted.
5. Choose merge, partition overwrite, or append
merge fits update-and-insert semantics keyed by a grain. A partition-recompute workload may use insert_overwrite, which relies on partitions instead of row keys. Pure append is simpler when upstream events never change. Choose from update semantics, scan cost, and adapter support rather than treating one strategy as universal.
6. Handle schema and logic changes
Adding a column does not necessarily backfill old rows; dropped columns and type changes may surface only at run time. onschemachange can be ignore, fail, appendnewcolumns, or syncallcolumns, but it tracks only top-level columns and does not replace historical backfill. If calculation logic changes, old and new history may follow different rules, so run --full-refresh and rebuild affected downstream incremental models.
7. Design backfill and failure recovery
Record the lateness window, target maximum update time, and source watermark in run metadata. After a failed window run, recompute from the last committed target watermark; do not treat an in-memory “processed through” value as truth. For broad repairs, process date partitions with bounded concurrency, then reconcile samples against a full query so one refresh does not overwhelm the warehouse.
8. Close the verification loop
Verify at least four signals: each eventid appears at most once in the window; target keys are unique; recent incremental output stays within an allowed difference from a full recomputation; and processed rows and maximum updatedat do not jump unexpectedly. Test empty input, duplicate events, updates to old events, late arrivals, equal-to-boundary timestamps, and an incremental run after a full refresh.
High-Quality Sample Answer
“I would confirm the model grain, lateness bound, and source update field first. Assume the target has one row per day and events have stable eventid and updatedat. The first run builds from history; later runs use is_incremental() and look back three days from the target’s maximum update time. That window is derived from lateness, not a fixed rule.
Within the window I deduplicate by event ID and version, then aggregate by day. The target declares dateday as its uniquekey and uses merge so recent days are replaced rather than duplicated. For a user-day grain I would use a composite key. Filtering only by event time would miss later corrections to old events, so I would prefer a reliable update timestamp or change sequence.
I would monitor the window size, watermark, rows processed, duplicate keys, and incremental-versus-full reconciliation. A schema-change setting can handle structural evolution, but it does not populate historical values. If logic changes or history needs repair, I would run a controlled full refresh or partitioned backfill and rebuild affected downstream models. I would test empty input, late and duplicate events, boundary timestamps, and retry behavior before calling the model reliable.”
Common Mistakes
Filtering only by eventat → old updates are missed → use updatedat or an explicit CDC watermark
Business time does not change when an old event is corrected. If updates are allowed, filter by update time or change sequence and verify its contract.
Merging without a key → rows cannot be matched reliably → define grain and non-null keys first
A key must identify exactly one target row. If the grain is user-day, using only the date merges different users into one row.
Assuming a new column is backfilled automatically → historical values remain empty → plan a backfill or full refresh
Schema synchronization and historical data population are separate. A structural change may be lightweight, but populated old rows need an explicit update or rebuild.
Picking a fixed one-hour window → late data falls outside it → calibrate with percentiles and reconciliation
Choose the window from the lateness distribution, SLA, and cost. Monitor arrivals outside the window and expand or repair when the distribution changes.
Follow-Ups and Responses
If 5% of events arrive two days late, how would you choose the window?
First confirm the accepted freshness delay. If a daily report may be corrected the next day, cover two to three days and reconcile late events. If day-one results must be stable, use a watermark plus a repair queue rather than only a larger SQL window. Validate the choice against lateness and cost curves instead of copying the percentage.
What happens if unique_key is duplicated in the source?
Duplicate keys in the incremental input or target can make an adapter fail or produce undefined results. Check uniqueness in both places, identify the duplicate source, deduplicate by event version, or redefine a composite key that represents the true grain. A random ID should not conceal an unstable business key.
The model SQL changed, but you only want to recompute seven days. Can you keep running incrementally?
Only if historical results are unaffected by the new logic. If the change affects all history, a seven-day incremental run creates a table with mixed rules. Run a controlled full refresh or partitioned recompute for the affected range and rebuild downstream models.
The upstream table was truncated. How does the incremental model recover?
Stop advancing the watermark, confirm the source rebuild, and replay from a reliable snapshot or CDC point. If the source no longer covers the required history, an incremental run would treat an incomplete source as a complete baseline; restore the snapshot or rebuild the target fully.