Full Refresh or Incremental?

Concepts covered: Full vs Incremental Load

The interviewer is not asking you to pick one. They are testing whether you can reason about the tradeoff. The trap is saying "incremental, obviously" without acknowledging that every pipeline starts as a full refresh and that full refresh is correct until it is not.

The Cost Crossover

Your answer should frame this as a cost function. Full refresh cost grows linearly with source table size. Incremental has fixed overhead - change detection, merge logic, state tracking - plus variable cost that grows with change volume, not total data. At some point the lines cross. Senior signal: name the crossover for your use case. Say "At 50M rows with a 2% daily change rate, incremental saves us 4 hours of warehouse compute per run." That level of specificity tells the interviewer you have actually done the math.

Full Refresh

• Simple: SELECT * and replace
• Always correct - no state to corrupt
• Cost grows with total data size
• 10M rows: 3 min, 100M: 30 min, 1B: 5+ hrs

Incremental

• Complex: detect changes, merge, track state
• Can accumulate drift if change detection fails
• Cost grows with change volume, not total size
• 10K changes on 1B rows: 20 seconds

The crossover typically lands between 10-50 million rows for most warehouse workloads. But the interviewer wants to hear you go deeper. Compute-time billing pushes the crossover earlier because full refreshes burn credits linearly. Storage-based pricing pushes it later because incremental state has its own cost. Red flag: candidates who give a number without explaining what drives it.

dbt Incremental Models

dbt incremental is the most common answer interviewers expect. If you say "incremental loading" and don't mention dbt, the interviewer will ask about it. Know the config block cold.

-- dbt incremental model config
{{
  config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
  )
}}

SELECT
  event_id,
  user_id,
  event_type,
  created_at
FROM {{ source('app', 'events') }}

{% if is_incremental() %}
  WHERE created_at > (
    SELECT MAX(created_at) FROM {{ this }}
  )
{% endif %}

The is_incremental() block is the key detail. On first run, dbt builds the full table. On subsequent runs, it only processes rows newer than the current maximum timestamp. The unique_key ensures upsert behavior - same event_id arriving twice updates rather than duplicates. The follow-up question will be: "What happens if your timestamp has late-arriving data?" Your answer: overlap windows or a lookback buffer.

Watermark Patterns

The interviewer may probe deeper: "How do you track where you left off?" A watermark is a checkpoint that records how far you have processed. The simplest version is MAX(updated_at) stored in a control table. Each run reads the watermark, processes everything newer, then advances it. The trap: this only works for append-only data. If a row's updated_at was set to yesterday but the row was modified today, a timestamp watermark misses it. Say this before the interviewer asks - it shows you have been burned by it.

About this lesson

This section is part of the Incremental Loading: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.