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.