Handling late-arriving data in dbt - Step-by-Step Execution

Concept Flow - Handling late-arriving data

Data arrives

↓

Check if data is late?

No→Process normally

Yes↓

Apply late data handling logic

↓

Update existing records or append

↓

Recalculate aggregates if needed

↓

Final clean dataset ready

Data arrives and is checked if late. If late, special logic updates or appends data, then aggregates are recalculated.

Execution Sample

dbt

with base as (
  select * from source_table
),
late_data as (
  select * from base where date < current_date - interval '7' day
),
final as (
  select * from base
  union all
  select * from late_data
)
select * from final

This dbt SQL snippet identifies late data older than 7 days and combines it with the base data for final processing.

Execution Table

Step	Action	Data Sample	Resulting Data State
1	Load source_table	[{id:1, date:2024-06-01}, {id:2, date:2024-06-10}]	All data loaded
2	Filter late data (date < current_date - 7)	[{id:1, date:2024-06-01}]	Late data isolated
3	Combine base and late_data	[{id:1, date:2024-06-01}, {id:2, date:2024-06-10}, {id:1, date:2024-06-01}]	Duplicates possible, data unioned
4	Process final dataset	Duplicates handled or aggregates recalculated	Clean final dataset ready
5	End	-	Processing complete

💡 All data processed including late arrivals, final dataset ready for analysis

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
base	empty	[{id:1, date:2024-06-01}, {id:2, date:2024-06-10}]	[{id:1, date:2024-06-01}, {id:2, date:2024-06-10}]	[{id:1, date:2024-06-01}, {id:2, date:2024-06-10}]
late_data	empty	[{id:1, date:2024-06-01}]	[{id:1, date:2024-06-01}]	[{id:1, date:2024-06-01}]
final	empty	empty	[{id:1, date:2024-06-01}, {id:2, date:2024-06-10}, {id:1, date:2024-06-01}]	[Cleaned dataset without duplicates]

Key Moments - 2 Insights

Why do we check if data is late before processing?

Because late data can change past results, so we handle it differently to update or append existing records as shown in step 2 of the execution_table.

What happens if we just append late data without cleaning duplicates?

Duplicates can cause wrong analysis results. Step 3 shows unioning data which may create duplicates, so step 4 cleans or recalculates aggregates.

Visual Quiz - 3 Questions

Look at the execution_table, what data does 'late_data' contain after step 2?

A All records from source_table

B Records with event_date within last 7 days

C Records with event_date older than 7 days

D Empty dataset

💡 Hint

Check the 'Data Sample' column in step 2 of execution_table

At which step does the dataset potentially contain duplicates?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the 'Resulting Data State' in step 3 mentioning duplicates

If we skip late data handling, what is the likely impact on the final dataset?

A Final dataset will be missing late-arriving records

B Final dataset will have duplicates

C Final dataset will be empty

D No impact, data is always complete

💡 Hint

Refer to the concept_flow where late data is checked and handled specially

Concept Snapshot

Handling late-arriving data in dbt:
- Identify late data by comparing event dates
- Separate late data for special processing
- Combine base and late data carefully
- Clean duplicates or recalc aggregates
- Ensures accurate, up-to-date datasets

Full Transcript

This visual execution shows how to handle late-arriving data in dbt. First, data is loaded from the source. Then, late data is identified by filtering records older than a threshold (7 days). This late data is combined with the base data, which may cause duplicates. Finally, duplicates are cleaned or aggregates recalculated to produce a clean final dataset. Handling late data ensures that delayed records update past results correctly, avoiding errors in analysis.