How to support the next X adapters?

[dbeatty10]

Context

• dbt-utils contains macros to abstract away complex work
  • Sometimes, adapter-specific implementations are necessary (e.g., deduplicate, star, pivot, etc)
• There are dbt adapters for 40+ databases publicly available via PyPI or on GitHub (and more are added every month).
• The only adapter-specific overrides within dbt-utils are for Postgres, Redshift, BigQuery, and Snowflake
  • Other adapters that want to provide an override need to create a custom repo like tsql-utils
  • Most weekly dbt usage is covered by these four + Spark

Key Question

• How do we want to support abstract definitions and concrete implementations for deduplicate, star, pivot, etc going forward?

Aspiration

Explain how community members can share and submit across a continuum ranging from "purely experimental" database macros on the right to "absolutely crucial" on the left.

One way this might look: dbt packages in personal git repos on the far right, dbt-core on the far left, and dbt-utils somewhere in the middle.

Some options

1. Only support the "big five": Postgres, Redshift, BigQuery, Snowflake, and Spark
2. Allow contributions outside of the big five, but leave them untested, just hope they work for end users (and note as "unsupported")
3. Document links to 3rd party git repos for dbt packages that implement the given macro for an unsupported database
4. Only include contributions for databases with a public docker image that can be utilized within CI
5. Expand the "big five" to the "top ten" or some other defined list of notability/utility.
6. Choose notable abstractions (like deduplicate, etc) to "shift left" into core for implementation by adapters (similar to here).

Some considerations

(many courtesy of @judahrand)

• Kind of a pain to have macros in dbt-utils that don’t work for some popular database adapters
• How popular is popular enough to be included in dbt-utils?
• Kind of a pain to have untested code/macros in dbt-utils
• Kind of a pain to require a contributor to submit pull requests to multiple projects for the same macro
• May be costly to test commercial databases via CI
• Maintainers of dbt-utils may lack expertise to support some databases

[dbeatty10]

Here's the existing list of adapter-specific extensions to dbt-utils that I can think of:

• spark_utils
• tsql_utils
• materialize_dbt_utils
• athena_utils
• teradata_utils

[judahrand]

Sometimes, adapter-specific implementations are necessary (e.g., deduplicate, star, pivot, etc)

An additional point here is that sometimes it isn't that an adapter-specific implementation is necessary but that a specific implementation might perform better on a particular adapter due to a specific feature which is implemented. So sometimes not 'required' but certainly better due to centralization of the optimization.

This is the case, for example, with the BigQuery implementation of deduplicate.

[dbeatty10]

Excellent call out that sometimes an adapter-specific implementation can unlock a performance optimization.

[joellabes]

This is a really important discussion, thanks for kicking it off @dbeatty10!

My gut feeling is that in the same way as dbt Labs stewards the core four five adapters and the rest are community contributions, the same behaviour should apply here.

I wouldn't feel great about adding community-powered adapters into this repo - it has our name on it and we can't commit to doing a good job with those (see also the legacy data transformation packages which we are trying to extricate ourselves from because we're no longer the best people to drive them forward).

I like a combination of

• Choose notable abstractions (like deduplicate, etc) to "shift left" into core for implementation by adapters (similar to The future of dbt utils #487).
• Document links to 3rd party git repos for dbt packages that implement the given macro for an unsupported database

The default__ implementations are a best-effort attempt at baseline compatibility; combined with the fact that we've already moved a lot of the primitives into dbt Core, I hope that reduces the number of overrides community projects have to support.

Related: this feels like a discussion that @dataders should know about. I don't think new adapters have to support utils to become certified, but maybe it's a piece of metadata they self-report and we include in the big grid he's building?

This also feels like something that will come up not just for utils, but also for other popular packages. How long until dbt_date_duckdb or dbt_fivetran_shopify_greenplum makes its debut?

[joellabes]

An example of a good notable extraction might be ilike:

{% macro default__ilike(expression, comparison_string) %}
  {{ expression }} ilike '{{ string }}'
{% endmacro %}

{% macro athena__ilike(string) %}
  lower({{ expression }}) like lower({{ string }})
{% endmacro %}

select * 
from my_table
where {{ dbt.ilike('email_address', '%@DBTLABS.COM') }}

renders to

select * from my_table where email_address ilike '%@DBTLABS.COM'
select * from my_table where lower(email_address) like lower('%@dbtlabs.com') --I'm pretty sure this is legal??!

This came to mind having looked at #547 and #563