# Problems with Array Data Types in DBT Unit Tests with Snowflake

**URL:** <https://discourse.getdbt.com/t/problems-with-array-data-types-in-dbt-unit-tests-with-snowflake/20520>\
**Category:** Help\
**Tags:** testing, dbt-core\
**Created:** [January 5, 2026, 3:29pm UTC](https://discourse.getdbt.com/t/problems-with-array-data-types-in-dbt-unit-tests-with-snowflake/20520 "2026-01-05T15:29:34Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![AlexiaWu](https://avatars.discourse-cdn.com/v4/letter/a/cdc98d/32.png) [@AlexiaWu](https://discourse.getdbt.com/u/AlexiaWu)\
**Post date:** [January 5, 2026, 3:29pm UTC](https://discourse.getdbt.com/t/problems-with-array-data-types-in-dbt-unit-tests-with-snowflake/20520/1 "2026-01-05T15:29:34Z")

</div>

## The problem I’m having

We’re trying to write unit tests for a Snowflake-based dbt model that uses **`ARRAY_CONTAINS()`** to check for ancillaries. The model works in production, but unit tests fail with type mismatch errors when trying to mock array columns.

**Error:**

```auto
Database Error
001044 (42P13): SQL compilation error: error line 164 at position 13
Invalid argument types for function 'ARRAY_CONTAINS': (VARCHAR(14), ARRAY)

```

## The context of why I’m trying to do this

**Model: `int_order_details.sql`**

```auto
{{ config(materialized="ephemeral") }}

select
    order_id,
    flight_route,
    added_ancillaries, -- ARRAY column
    carrier_codes, -- ARRAY column
    case
        when array_contains('Insurance', added_ancillaries) then 1 else 0
    end as has_insurance,
    case
        when year(created_date) >= 2021
        then coalesce(service_fee_raw, 0)
        else 2.30 -- Legacy fee
    end as service_fee
from {{ ref("base_orders") }}

```

## What I’ve already tried

**Approach 1. Inline SQL with `array_construct()` in YAML**

I used array\_construct() directly as below, also tried with array\_construct(“”)::array, to\_array(parse\_json(‘[]’)). But got the error above.

```auto
unit_tests:
  - name: test_legacy_fee
    model: int_order_details
    given:
      - input: ref('base_orders')
        format: sql
        rows: |
          select 'ORDER_001' as order_id,
                 'JFK-LAX' as flight_route,
                 array_construct('Insurance', 'FastTrack') as added_ancillaries,
                 array_construct('AA') as carrier_codes,
                 '2020-12-31'::date as created_date,
                 5.00 as service_fee_raw
    expect:
      rows:
        - { order_id: "ORDER_001", service_fee: 2.30, has_insurance: 1 }

```

**Approach 2.**  **YAML Row Format with Array Literals**

I followed this approach based on recommendation on a similar [issue](https://discourse.getdbt.com/t/workaround-for-arrays-not-supported-in-unit-test-expected-output/13762) on dbt unit test. Got the same error as above.

```auto
given:
  - input: ref('base_orders')
    rows:
      - order_id: 'ORDER_001'
        flight_route: 'JFK-LAX'
        added_ancillaries: ['Insurance', 'FastTrack']
        carrier_codes: ['AA']
        created_date: '2020-12-31'
        service_fee_raw: 5.00

```

**Approach 3: Ephemeral Helper Model**

I created a separate model with mock data and called this model in the unit test

ut\_base\_orders\_mock\_data.sql (_mock data model )_

```auto
{{ config(materialized="ephemeral", tags=["unit_test_helper"]) }}

with mock_data as (
    select
        'ORDER_001' as order_id,
        'JFK-LAX' as flight_route,
        array_construct('Insurance', 'FastTrack') as added_ancillaries,
        array_construct('AA') as carrier_codes,
        '2020-12-31'::date as created_date,
        5.00 as service_fee_raw
)
select * from mock_data

```

unit test file

```auto
model: int_order_details 
given:
  - input: ref('base_orders')
    format: sql
    rows: |
      select * from ut_base_orders_mock_data
      where order_id = 'ORDER_001'

```

## **Current Hypothesis**

In all approaches, when the unit test framework materializes the mock data, **`array_construct()`** or YAML array literals (**`['item']`**) appear to be treated as **VARCHAR** instead of **ARRAY** type when passed to the model’s SQL logic.

The **`ARRAY_CONTAINS()`** function in the model then receives:

- First argument: String literal **`'Insurance'`**

- Second argument: **VARCHAR** (should be ARRAY)

## **Questions**

1. Is there a way to explicitly cast array columns in unit test mock data?

2. Does dbt’s unit test framework support complex Snowflake types like ARRAY/OBJECT?

3. Should we be using a different testing pattern for models with array operations?

Will really appreciate your help. Thanks so much.

---

<div class="post-metadata">

**Author:** ![gdumic](https://avatars.discourse-cdn.com/v4/letter/g/c37758/32.png) [@gdumic](https://discourse.getdbt.com/u/gdumic)\
**Post date:** [January 5, 2026, 4:24pm UTC](https://discourse.getdbt.com/t/problems-with-array-data-types-in-dbt-unit-tests-with-snowflake/20520/2 "2026-01-05T16:24:59Z")

</div>

Hi @ **AlexiaWu**! I replicated your error with this simple example (see screenshot). The problem might reflect the fact that ARRAY\_CONTAINS expects a VARIANT column, not varchar. So just add a cast as seen on the 2nd screenshot.

Replicated error:

 ![replicated_error](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/5/509733bcb6cbd6541df7991372c22ce04e1b159e.jpeg)

In the ephemeral model, action needed:  
case  
when array\_contains(‘Insurance’:: **VARIANT** , added\_ancillaries) then 1 else 0  
end as has\_insurance

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [January 12, 2026, 4:25pm UTC](https://discourse.getdbt.com/t/problems-with-array-data-types-in-dbt-unit-tests-with-snowflake/20520/3 "2026-01-12T16:25:25Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.