How to query for a single row when using BETWEEN on valid_from and valid_to

Help
1

For my snapshot tables, I want to be able to see the state of a row at a given time.
However, the VALID_TO of a row and the VALID_FROM of the next row overlap (ex. both equal ‘2024-01-10’)
So a query with “WHERE ‘2024-01-10’ BETWEEN dbt_valid_from AND dbt_valid_to” can return 2 rows.
Is there an efficient query to only return the newer row?
Or should I consider modifying the VALID_FROM values to not overlap by using a post-hook (subtracting a millisecond for example)?

2

is there a reason you can’t use SQL rank to get the latest row?

3

You should not use between, the semantics for dbt_valid_from and dbt_valid_to are that the data is inserted @ dbt_valid_from and deleted @ dbt_valid_to, meaning if it equals dbt_valid_to then it was just deleted. So if you want something at a particular time, you should be using “dbt_valid_from <= {{ timestamp }} and {{ timestamp }} < dbt_valid_to”.

Note: @Jack Nguyen originally posted this reply in Slack. It might not have transferred perfectly.

1 Like