[Feature] DBT Unit tests to support Redshift limitations such as LISTAGG, MEDIAN, PERCENTILE_CONT, etc.

[tanapoln]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

The current DBT Unit tests feature doesn't support Redshift if the SQL in CTE contains functions such as LISTAGG, MEDIAN, PERCENTILE_CONT, etc. Those functions must be executed against a user-created-table. However, DBT combines given rows to be part of CTE, which is unsupported by Redshift. You can try SQL below.

create temporary table "test_tmpxxxxx" as (
   with test_fixture as (
       select
         cast(1000 as integer) as id,
         cast('menu1' as character varying(500)) as name,
         cast( 1 as integer) as quantity
      union all
      select
         cast(1001 as integer) as id,
         cast('menu2' as character varying(500)) as name,
         cast( 1 as integer) as quantity
      union all
      select
         cast(1003 as integer) as id,
         cast('menu1' as character varying(500)) as name,
         cast( 1 as integer) as quantity
   ),
   agg as (
      SELECT
         LISTAGG(name || ' x ' || quantity, ',') AS option_name_list,
         id
      FROM test_fixture
      GROUP BY id
   )
   select * from agg
);

This will error [XX000] 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

But the query below works fine:

create temporary table "test_tmp1234" as (
   SELECT
      cast(1000 as integer) as id,
      cast('menu1' as character varying(500)) as name,
      cast( 1 as integer) as quantity
   union all
   select
      cast(1001 as integer) as id,
      cast('menu2' as character varying(500)) as name,
      cast( 1 as integer) as quantity
   union all
   select
      cast(1000 as integer) as id,
      cast('menu1' as character varying(500)) as name,
      cast( 1 as integer) as quantity
);

with agg as (
   SELECT
      LISTAGG(name || ' x ' || quantity, ',') AS option_name_list,
      id
   FROM test_tmp1234
   GROUP BY id
)
select * from agg;

As I mentioned, if all given rows are created as temporary table first, then run the test by referring to it, it will be fine.

This is a feature request to separate DBT unit tests into 2 steps

1. Prepare test fixtures by creating temporary tables
2. Run unit test query by referring to the temporary tables

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[jtcohen6]

Thanks @tanapoln! Sorry for the delay getting back to you.

Indeed, these functions are not supported in the current implementation of dbt unit tests on Redshift.

There was a bit of confusion with the docs update we made to clarify that lack of support, which presented your reproduction case as a viable workaround, when really it's an explanation of why this does not work:

• [Core] Compute-node only functions in Redshift like MEDIAN, etc. won't work with unit tests docs.getdbt.com#5430
• Redshift unit test limitation docs.getdbt.com#5508

In order to get this working, I think we'd need to add support for a "materialized" strategy (rather than "CTE") for unit tests:

• [CT-3033] [Spike] Explore support multiple unit test materialization strategies: CTE vs 'seed'-based dbt-core#8499

This would also enable us to fully test incremental strategies, among other things. While it's very useful to have this issue so other users can search for it (thank you!), I'm going to consider its actual resolution blocked on implementing dbt-labs/dbt-core#8499.

[dbeatty10]

@jtcohen6 just noticed the following in the notes for Amazon Redshift patch 182:

• Updates LISTAGG, MEDIAN, PERCENTILE_CONT and PERCENTILE_DISC to no longer require user-defined tables. Queries that reference catalog tables or that don't reference any tables can also use these functions.

So this might be fixed on Redshift side of things now 🤞