dbt unit tests for Redshift with Listagg

Help
,
1

Hi,
I want to try out the new unit tests and can’t get it to work:

$ dbt test --select test_type:unit
...
Runtime Error in unit_test test_workspace_type_used_last_3_month (models/.../int_external_hightouch_workspace_type_usage.yml)
  An error occurred during execution of unit test 'test_workspace_type_used_last_3_month'. There may be an error in the unit test definition: check the data types.
   Database Error
    One or more of the used functions must be applied on at least one user created tables. Examples of user table only functions are LISTAGG, MEDIAN, PERCENTILE_CONT, etc

What I’ve already tried

I followed the suggestion here to create a temporary table:

but got a syntax error message when using the provided example and I also tried a minimal example that still gives me a syntax error “syntax error near create” :

create temporary table "test" as (
   SELECT 1 from dual
);

select * from test;

Cheers,
Johannes M

2

Hey @johannes.muller, Redshift doesn’t support the ‘Dual’ table like some other DBs and so you need to select from an actual user table that you have. That said, in sticking with your example, you can just use the same statement without the original FROM clause:

CREATE TEMPORARY TABLE "test" AS (
   SELECT 1
);

SELECT * FROM test;
3

Thanks DataNath!
Even with your exact SQL query I get the same error when running the dbt tests:

Runtime Error in unit_test test_workspace_type_used_last_3_month (models/intermediate/salesforce/int_external_hightouch_workspace_type_usage.yml)
  An error occurred during execution of unit test 'test_workspace_type_used_last_3_month'. There may be an error in the unit test definition: check the data types.
   Database Error
    syntax error at or near "CREATE"

I don’t really get it. Maybe it’s some limitation of dbt?
Just to clarify, the command I am running is:
dbt test --select test_type:unit

4

@johannes.muller if you change the temp table creation to a CTE, does that help?

WITH cte AS (
   SELECT 1
)

SELECT * FROM cte
5

Well, it won’t throw an error :smiley:
But what I want to accomplish in the first place is to get a unit test to work on a dbt model with a LISTAGG function in Redshift.
And if I understand it correctly I need a temporary table for that.
If I use a CTE with a LISTAGG function I’m back to square one and will receive the first error message in my original question.

6

@johannes.muller I see! Did a little more digging and looks like there’s some good suggestions here - option 3 of the marked response with the post-hook for deletion of a true table in particular: will DBT support temp table creation like create table #temp1 as select * from tab1 or it works only CTE way - Stack Overflow

7

That looks promising @DataNath !
I added the

{{ config(
  materialized='table'
) }}

At the top of the model to give it a try but still run into the error message about LISTAGG having to be applied on a user table.

Using a pre-hook might work but I don’t want to completely change the model just to unit test it if possible.

8

Hey @johannes.muller, hopefully we’re getting closer!

I’m wondering - are you trying to run this against catalog/admin/metadata tables? See Redshift docs.

If so, I’m thinking you may need to create a model upstream that essentially materializes a copy table (to make it user-created) like:

SELECT *
FROM {{ source('<SourceName>','<TableName>') }}
-- Any necessary filtering etc.

Where the source is your catalog/admin/metadata table. Then you can run your unit testing with ‘listagg’ against this upstream object. The post-hook for cleanup of this table may be a useful shout as well as this looks to be a limitation of not being able to create temp tables.

9

@DataNath It doesn’t seem to work.
[EDIT] The previous error I got was because I could not reach the database.
The actual error now is:
...depends on a node named 'ModelName' which was not found.

My second model just looks like this:

SELECT * 
FROM {{ ref('ModelName') }}

I can’t use source I think since it’s a model and not a source, or?

10

hey everyone, great thread here and really appreciate the detail! i’ve checked this with the internal dbt team and this workaround isn’t a viable one. i’m going to remove it from the docs and sorry for the confusion here!

this dbt redshift issue has more technical details on why it doesn’t work: essentially we’d need to add support for a “materialized” strategy (rather than “CTE”) for unit tests.

you can upvote for that here to contribute getting this to work for unit tests.

2 Likes
11

Thanks a lot for the update @mirna.wong! @johannes.muller apologies for not getting back, this got a little buried and slipped my mind.

1 Like