Dynamic dates in unit tests?

Help
,
1

The problem I’m having

I have a unit test set up as follows:

    given:
      - input: ref('mart')
        rows:
          # Case 1
          - { id: 1, datetime: '2024-10-15', price: 123 }

The problem is that the model that is being tested selects only for the data from the last 7 days.
where datetime > dateadd('days', -7, current_date)

What is the best, most elegant way to have these tests work without having to update the datetime value on my tests run?

What I’ve already tried

  • I tried setting a project variable dbt_is_test and then setting an override in the test, but I quite dislike this as we should not be able to set this flag and modify the code based on whether the code is being run or tested.

  • I tried various ways to set the data to current date in the yaml file - to no success.

  • For this use case, what worked is that I set the datetime value in my test to 2245-10-15 and I am set for a 200 years, but I just dislike a hacky solution like this

2

I also had this problem. I added an intermediate view rather than calling current_date directly which lets you inject a row.

I have a model called time_utils that is just:

SELECT CURRENT_DATE::DATE AS todays_date

Then in your actual model you can replace current_date with (SELECT todays_date FROM time_utils)

That lets you then stub the date to anything you want:

    given:
      - input: ref('time_utils')
        rows:
          - { todays_date: '2024-10-07' }

It does mean you end up with an extra model but if you want to test dates between certain ranges or something like that it gives a bit more flexibility