Handling late-arriving data in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all records from the source table.

dbt

SELECT * FROM [1]

Drag options to blanks, or click blank then click option'

Alate_data

Bfinal_table

Cstaging_table

Dsource_table

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing the final or staging table instead of the source table.

Using a table name that does not exist.

The source table contains the raw incoming data including late arrivals.

2fill in blank

medium

Complete the code to filter records where the event date is after the last processed date.

dbt

WHERE event_date > [1]

Drag options to blanks, or click blank then click option'

Alast_processed_date

Bcurrent_date

Cstart_date

Dmax_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'current_date' which filters out late data.

Using 'start_date' which might exclude some late arrivals.

Filtering with 'last_processed_date' ensures only new or late-arriving data is processed.

3fill in blank

hard

Fix the error in the merge statement to update existing records with late-arriving data.

dbt

MERGE INTO final_table AS target USING staging_table AS source ON target.id = source.id WHEN MATCHED THEN [1]

Drag options to blanks, or click blank then click option'

AINSERT

BUPDATE SET target.value = source.value

CDELETE

DSELECT *

Attempts:

3 left

💡 Hint

Common Mistakes

Using DELETE which removes records instead of updating.

Using INSERT which is for new records, not matches.

The 'UPDATE SET' clause updates existing records with new values from late-arriving data.

4fill in blank

hard

Fill both blanks to create a dictionary comprehension that filters late-arriving data by date and updates values.

dbt

late_data = {record['id']: record[1] for record in data if record['date'] [2] last_processed_date}

Drag options to blanks, or click blank then click option'

A.update({'value': new_value})

B>

C>=

D.copy()

Attempts:

3 left

💡 Hint

Common Mistakes

Using '.update()' which returns None and breaks comprehension.

Using '>=' which includes already processed data.

We copy each record to avoid modifying original data and filter records with date greater than last processed date.

5fill in blank

hard

Fill all three blanks to create a SQL query that merges late-arriving data, updating existing records and inserting new ones.

dbt

MERGE INTO final_table AS target USING [1] AS source ON target.id = source.id WHEN MATCHED THEN [2] WHEN NOT MATCHED THEN [3]

Drag options to blanks, or click blank then click option'

Astaging_table

BUPDATE SET target.value = source.value

CINSERT (id, value) VALUES (source.id, source.value)

Dsource_table

Attempts:

3 left

💡 Hint

Common Mistakes

Using source_table instead of staging_table as source.

Using DELETE instead of UPDATE for matched records.

Omitting the INSERT clause for new records.

The staging table holds late-arriving data. Matched records are updated, unmatched are inserted.