6. Smaller Builds in Development

Difficulty: Medium · Topic: Jinja, vars & packages

{{ source('app', 'events') }} holds years of events, and rebuilding all of it on every dbt run in development is slow and expensive.

Write stg_events (columns event_id, user_id, event_type, event_at) so that:

when the target is dev (target.name == 'dev'), it keeps only events from the last 3 days of data: event_at at or after the latest event_at in the source minus 3 days;
in any other target (prod, ci), it keeps every event.

Hidden tests run in both targets.

Your model is compiled like dbt does and run in DuckDB against 4 test cases, including hidden edge cases.

Sample inputs

{{ source('app', 'events') }}

event_id	user_id	event_type	event_at
1	10	view	2024-05-01 08:00:00
2	11	click	2024-05-06 12:00:00
3	10	view	2024-05-07 09:30:00
4	12	purchase	2024-05-09 12:00:00

Expected output

event_id	user_id	event_type	event_at
2	11	click	2024-05-06 12:00:00
3	10	view	2024-05-07 09:30:00
4	12	purchase	2024-05-09 12:00:00

Hints

Hint 1

{% if target.name == 'dev' %} ... {% endif %} adds the WHERE clause only when compiling for dev; prod gets plain SQL with no filter at all.

Hint 2

Anchor the window on the data, not on today: (select max(event_at) from {{ source('app', 'events') }}) - interval 3 day. Sample data never moves, so a filter on the current date would return nothing.

Hint 3

"At or after" means >=.

6. Smaller Builds in Development

Sample inputs

Expected output

Hints

More dbt problems