# Unit Test Data Challenges

**URL:** <https://discourse.getdbt.com/t/unit-test-data-challenges/19342>\
**Category:** Help\
**Tags:** testing, dbt-core\
**Created:** [May 10, 2025, 9:33pm UTC](https://discourse.getdbt.com/t/unit-test-data-challenges/19342 "2025-05-10T21:33:12Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![m\_coonan](https://avatars.discourse-cdn.com/v4/letter/m/f04885/32.png) [@m\_coonan](https://discourse.getdbt.com/u/m_coonan)\
**Post date:** [May 10, 2025, 9:33pm UTC](https://discourse.getdbt.com/t/unit-test-data-challenges/19342/1 "2025-05-10T21:33:12Z")

</div>

## 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

```yml
# 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}

```

```log
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

```

---

<div class="post-metadata">

**Author:** ![m\_coonan](https://avatars.discourse-cdn.com/v4/letter/m/f04885/32.png) [@m\_coonan](https://discourse.getdbt.com/u/m_coonan)\
**Post date:** [May 10, 2025, 10:26pm UTC](https://discourse.getdbt.com/t/unit-test-data-challenges/19342/2 "2025-05-10T22:26:25Z")

</div>

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

```sh
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.