Handling late-arriving data in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why is handling late-arriving data important in data pipelines?

Late-arriving data can cause issues in analytics and reporting. Which of the following best explains why handling late-arriving data is important?

A It ensures data freshness by discarding any data that arrives late to keep reports fast.

B It allows the data pipeline to update historical records accurately when delayed data arrives.

C It prevents any data from being stored if it arrives after the scheduled pipeline run time.

D It automatically deletes old data to make room for late-arriving data.

💡 Hint

Think about how late data affects historical analysis and accuracy.

Handling late-arriving data is important because it allows the pipeline to update historical records accurately, ensuring reports reflect all relevant data even if it arrives after the initial processing.

❓ Predict Output

intermediate

Output of dbt incremental model with late-arriving data

Consider a dbt incremental model that uses is_incremental() to update records. What will be the output after running this model twice if late-arriving data for an existing date is included in the second run?

dbt

with source_data as (
  select * from {{ ref('raw_events') }}
),
updates as (
  select * from source_data where event_date >= (select max(event_date) from {{ this }})
)

select * from updates

A The model will update existing dates with late-arriving data and append new dates.

B The model will append only new dates, ignoring late-arriving data for existing dates.

C The model will overwrite the entire table with only the latest run's data.

D The model will fail because <code>is_incremental()</code> is missing.

💡 Hint

Think about how incremental models handle data for existing keys.

Incremental models with proper filtering update existing records for late-arriving data and append new records, ensuring data stays current.

❓ data_output

advanced

Resulting row count after handling late-arriving data

You have a table with 1000 rows for dates Jan 1-10. On Jan 11, 50 late-arriving rows for Jan 5 arrive. After running a dbt incremental model that merges late data correctly, how many rows will the table have?

A 1000 rows, because late-arriving rows replace existing rows for Jan 5.

B 950 rows, because some rows are deleted during late data processing.

C 1050 rows, because late-arriving rows are appended without deduplication.

D 1100 rows, because late-arriving rows are duplicated.

💡 Hint

Consider how merging late-arriving data affects existing rows.

When late-arriving data is merged correctly, it replaces or updates existing rows for the same date, so total row count remains the same.

🔧 Debug

advanced

Identify the error in this dbt incremental model handling late data

Review the following dbt model code snippet meant to handle late-arriving data. What error will occur when running it?

dbt

{{ config(materialized='incremental') }}

select * from {{ ref('raw_events') }}
where event_date > (select max(event_date) from {{ this }})

A The model will cause a runtime error because {{ this }} is undefined.

B The model will fail with a syntax error due to missing <code>is_incremental()</code> check.

C The model will run but ignore late-arriving data for existing event_date equal to max(event_date).

D The model will overwrite the entire table instead of incrementally updating.

💡 Hint

Think about the filter condition and how it handles data equal to max date.

The filter uses > max(event_date), so rows with event_date equal to max(event_date) are excluded, causing late-arriving data for that date to be ignored.

🚀 Application

expert

Best approach to handle late-arriving data in dbt incremental models

You want to ensure your dbt incremental model correctly updates records when late-arriving data comes in for any date, including past dates. Which approach below is best?

A Always run the model as full-refresh to ensure all late-arriving data is included.

B Use <code>is_incremental()</code> and filter source data with <code>event_date &gt;= min(event_date)</code> to reprocess all data.

C Use <code>is_incremental()</code> and filter source data with <code>event_date &gt; max(event_date)</code> to append only new dates.

D Use <code>is_incremental()</code> and filter source data with <code>event_date &gt;= (select min(event_date) from {{ this }})</code> to merge late-arriving data for all dates in the table.

💡 Hint

Think about how to include late-arriving data for any date already in the table without full refresh.

Filtering source data with event_date >= min(event_date) in the existing table allows the incremental model to merge late-arriving data for all dates present, updating historical data efficiently.

Practice

1. What does late-arriving data mean in data processing?

easy

A. Data that is generated by a different system

B. Data that arrives after the expected time window

C. Data that is duplicated in the dataset

D. Data that is corrupted or incomplete

Solution

1. Step 1: Understand the timing of data arrival

   Late-arriving data refers to data that comes in after the time it was expected to be available for processing.

2. Step 2: Differentiate from other data issues

   It is not about corruption, duplication, or source system differences, but purely about timing.

3. Final Answer:

   Data that arrives after the expected time window -> Option B

4. Quick Check:

   Late-arriving data = late arrival [OK]

Hint: Late-arriving means data comes late, not corrupted or duplicated [OK]

Common Mistakes:

• Confusing late data with corrupted data
• Thinking late data means duplicate records
• Assuming late data is from a different system

2. Which of the following is the correct way to filter late-arriving data in a dbt SQL model?

easy

A. WHERE event_date < current_date - interval '1 day'

B. WHERE event_date > current_date + interval '1 day'

C. WHERE event_date = current_date

D. WHERE event_date IS NULL

Solution

1. Step 1: Understand filtering late data by date

   Late-arriving data is usually older than the current date minus a buffer (like 1 day), so we filter for dates less than that.

2. Step 2: Check each option's logic

   WHERE event_date < current_date - interval '1 day' filters for event_date before yesterday, which captures late data. WHERE event_date > current_date + interval '1 day' filters future dates, which is incorrect. WHERE event_date = current_date filters only today's data, ignoring late data. WHERE event_date IS NULL filters nulls, unrelated to late data.

3. Final Answer:

   WHERE event_date < current_date - interval '1 day' -> Option A

4. Quick Check:

   Filter dates before yesterday = WHERE event_date < current_date - interval '1 day' [OK]

Hint: Late data usually has older dates than today minus buffer [OK]

Common Mistakes:

• Filtering future dates instead of past dates
• Filtering only current date data
• Filtering null dates instead of late data

3. Given this dbt incremental model SQL snippet:

SELECT
  id,
  event_date,
  data_value
FROM source_table
WHERE event_date >= (SELECT MAX(event_date) FROM {{ this }})

What is the main purpose of this WHERE clause?

medium

A. To select only new or updated data since last run

B. To select all data including duplicates

C. To exclude all late-arriving data

D. To select data with null event_date

Solution

1. Step 1: Analyze the WHERE clause logic

   The clause filters rows where event_date is greater or equal to the max event_date already in the target table, meaning it picks new or updated data since last run.

2. Step 2: Understand incremental model behavior

   This is a common pattern in dbt incremental models to avoid reprocessing old data and only add new or changed rows.

3. Final Answer:

   To select only new or updated data since last run -> Option A

4. Quick Check:

   Incremental filter = new or updated data [OK]

Hint: Incremental models filter data newer than max existing date [OK]

Common Mistakes:

• Thinking it excludes late-arriving data
• Assuming it selects duplicates
• Confusing null filtering with this clause

4. You wrote this dbt incremental model filter:

WHERE event_date > (SELECT MAX(event_date) FROM {{ this }})

But your model misses some late-arriving data. What is the likely problem?

medium

A. The subquery syntax is invalid in dbt

B. You should filter with event_date < instead

C. Using > instead of >= excludes data with event_date equal to max date

D. Late-arriving data always has null event_date

Solution

1. Step 1: Understand the difference between > and >=

   Using > excludes rows where event_date equals the max date already processed, which can miss late-arriving data with that date.

2. Step 2: Correct the filter to include equal dates

   Changing to >= includes those rows, capturing late-arriving data that arrived late but has the same event_date.

3. Final Answer:

   Using > instead of >= excludes data with event_date equal to max date -> Option C

4. Quick Check:

   Use >= to include late data on max date [OK]

Hint: Use >= not > to include late data on max date [OK]

Common Mistakes:

• Using > excludes late data with max date
• Thinking subquery is invalid in dbt
• Filtering with < instead of >
• Assuming late data has null dates

5. You want to handle late-arriving data in a dbt incremental model. Which approach best ensures late data is included without duplicating existing rows?

hard

A. Filter only event_date = current_date to avoid duplicates

B. Delete all data and reload full dataset every run

C. Ignore late-arriving data to keep model simple

D. Use a merge strategy with unique keys and filter event_date >= max date

Solution

1. Step 1: Understand the goal of including late-arriving data

   Late data can have event_dates already processed, so we must update or insert without duplicates.

2. Step 2: Use merge strategy with unique keys and proper filtering

   Using a merge (upsert) with unique keys and filtering event_date >= max date ensures late data is added or updated correctly.

3. Step 3: Evaluate other options

   Deleting all data every run is costly. Ignoring late data loses accuracy. Filtering only current_date misses late data.

4. Final Answer:

   Use a merge strategy with unique keys and filter event_date >= max date -> Option D

5. Quick Check:

   Merge + unique keys + >= max date = best late data handling [OK]

Hint: Merge with unique keys and >= max date handles late data well [OK]

Common Mistakes:

• Reloading full data every run is inefficient
• Ignoring late data causes incomplete analysis
• Filtering only current date misses late data