Unit Test Data Challenges

Help
,
1

The problem I’m having

dbt seems to be incorrectly loading my expect.rows in my unit test.

  • 1 text field is showing as 0 expected
  • 1 date (ISO format text) field is only including the year

Database is SQLite, though I don’t expect that would be a problem given that the issue related to the expected values.

The context of why I’m trying to do this

I’d like to unit test some logic in my model.

What I’ve already tried

  • Different formats to input the data
  • Run the compiled sql to validate actual data matches expectations

Some example code or error messages

# unit_tests.yml
unit_tests:
  - name: test_is_valid_revolut_transfer
    description: "Check my is_transfer logic captures all known edge cases"
    model: stg_revolut__transactions
    given:
      - input: source('revolut', 'personal')
        rows:
          - {type: 'TRANSFER', product: 'Current', completed_date: '2025-01-01 00:00:00', amount: 100.0, currency: 'EUR'}
          - {type: 'CARD_PAYMENT', product: 'Pocket', completed_date: '2025-01-02 00:00:00', amount: 100.0, currency: 'EUR'}
      - input: source('revolut', 'joint')
        rows:
          - {type: 'TRANSFER', product: 'Joint', completed_date: '2025-01-01 00:00:00', amount: -100.0, currency: 'EUR'}
    expect:
      rows:
        # NOTE: For some reason, the unit test is not properly loading the product / completed_date data
        - {product: 'Current', completed_date: '2025-01-01', amount: 100.0, currency: 'EUR', is_transfer: true}
        - {product: 'Pocket', completed_date: '2025-01-02', amount: 100.0, currency: 'EUR', is_transfer: false}
        - {product: 'Joint', completed_date: '2025-01-01', amount: -100.0, currency: 'EUR', is_transfer: true}

14:51:57  Failure in unit_test test_is_valid_revolut_transfer (models\staging\unit_tests.yml)
14:51:57

actual differs from expected:

@@,product  ,completed_date ,amount,currency,is_transfer
→ ,0→Pocket ,2025→2025-01-02,100.0 ,EUR     ,0
→ ,0→Current,2025→2025-01-01,100.0 ,EUR     ,1
→ ,0→Joint  ,2025→2025-01-01,-100.0,EUR     ,1
2

UPDATE:
The issue was related to the data types in my actual model view, which were not as expected:

sqlite> PRAGMA table_info(stg_revolut__transactions);
cid|name|type|notnull|dflt_value|pk
0|transaction_key||0||0
1|type|TEXT|0||0
2|product|BLOB|0||0
3|started_date||0||0
4|completed_date||0||0
5|description|TEXT|0||0
6|amount|FLOAT|0||0
7|fee|FLOAT|0||0
8|currency|TEXT|0||0
9|state|TEXT|0||0
10|balance|FLOAT|0||0
11|is_transfer||0||0

With a few cast() statements in my final CTE, the test is working now.