2. Latest Version of Each Customer
Difficulty: Medium · Topic: Sources & staging
The CRM is replicated with change data capture: {{ source('crm', 'customers_cdc') }} gets a new row every time a customer changes, and the loader sometimes delivers the same change twice.
Write stg_customers with exactly one row per customer: the version with the latest updated_at. When two rows share the latest updated_at (a re-delivered change), keep the one with the latest _loaded_at.
Return customer_id, email, plan, updated_at.
Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.
Sample inputs
{{ source('crm', 'customers_cdc') }}
| customer_id | plan | updated_at | _loaded_at | |
|---|---|---|---|---|
| 1 | ana@ex.com | free | 2024-01-01 09:00:00 | 2024-01-01 10:00:00 |
| 1 | ana@ex.com | pro | 2024-02-01 09:00:00 | 2024-02-01 10:00:00 |
| 2 | bo@ex.com | free | 2024-01-05 12:00:00 | 2024-01-05 13:00:00 |
| 3 | cy@ex.com | pro | 2024-01-07 08:00:00 | 2024-01-07 09:00:00 |
| 3 | cy@new.com | pro | 2024-01-09 08:00:00 | 2024-01-09 09:00:00 |
Expected output
| customer_id | plan | updated_at | |
|---|---|---|---|
| 3 | cy@new.com | pro | 2024-01-09 08:00:00 |
| 1 | ana@ex.com | pro | 2024-02-01 09:00:00 |
| 2 | bo@ex.com | free | 2024-01-05 12:00:00 |
Hints
Hint 1
Number each customer's rows withrow_number() over (partition by customer_id order by ...) and keep number 1.Hint 2
Order by both columns, newest first:updated_at desc, _loaded_at desc.Hint 3
DuckDB, Snowflake and Databricks supportqualify, which filters on a window function without an extra CTE.More dbt problems
- Staging Model for Raw Orders · Easy