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

[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:

• dbt-labs/docs.getdbt.com#5430
• dbt-labs/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:

• dbt-labs/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 🤞