[Unit Testing] Extend TestPostgresUnitTestingTypes to include complex types

[MichelleArk]

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-spark functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Per dbt-labs/dbt-core#8423 (comment), add postgres-specific input fixtures extend TestPostgresUnitTestingTypes to test complex types (at least JSON & ARRAY).

Describe alternatives you've considered

n/a

Who will this benefit?

dbt users of dbt-postgres using unit testing

Are you interested in contributing this feature?

Anything else?

Depends on dbt-labs/dbt-adapters#113.

See dbt-bigquery for a similar example.

[bdewilde]

Hi @MichelleArk , this missing functionality has been a critical blocker in my ability to leverage the new unit testing framework for my models, most of which have one or two array-type columns. Do you have any idea how to go about implementing this, or know who might? I'd love to at least try, so I can unblock myself.

[MichelleArk]

Hey @bdewilde! There's a proposal written up with some issues as part of the spiking done here:dbt-labs/dbt-core#8423 (comment), have you had a chance to read through it?

It'll be a medium-sized lift because it'll require changes in the base unit materialization (in the dbt-adapters repo) to introduce a new strategy pattern that defaults to the current strategy (using relation-based column schemas), but also implements an alternative strategy (using adapter. get_column_schema_from_query). the new strategy would then need to be selected + tested for in dbt-postgres.

[bdewilde]

Hey @MichelleArk , thanks very much for the link / writing all that up. This may be... more intensive than I'm comfortable taking on, since I'm not particularly familiar with dbt's internals. 😮‍💨 So for now, I'll just submit a strong +1 for building out this capability, then let qualified folks prioritize it as able and interested.

[bdewilde]

Hi, it's me again 😅 Just following up to reaffirm that this is an acute pain point: I run into it frequently, and it has significantly distorted what is and is not tested in my dbt project. I'd been pulling for it in v1.9, but see that microbatch and snapshots took priority. I get it! But if there's any way to prioritize updates needed to support more dtypes in dbt unit tests, I know I and a few others in the issues here would be very appreciative 🙏