[CT-1110] [Feature] Cross-database macro for type_boolean()

[jaypeedevlin]

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

There are some great type helpers that have migrated from utils to core, but type_boolean() is a notable exception. Speaking to @joellabes he indicated that y'all would likely be supportive of adding this one in. I've got a basic implementation in brooklyn-data/dbt_artifacts#188 that I'd be happy to contribute.

Describe alternatives you've considered

Not doing it :)

Who will this benefit?

Mostly folks maintaining packages.

Are you interested in contributing this feature?

Certainly!

Anything else?

Not officially included in the scope of this request, but something I'd love input on is a type_json and type_array, both of which I implemented in the above PR. If y'all were open to this I might expand the scope since I have versions ready to go.

[joellabes]

Additional context: another community member opened a PR for type_boolean in utils (dbt-labs/dbt-utils#508) which I closed because I didn't want to add it just to remove it a couple of weeks later. I had intended for it to come along in the initial migration, but it turns out it missed the boat.

[dbeatty10]

@jaypeedevlin thanks for opening!

Reaction

Any (or all) of the following are reasonable to include:

• type_boolean
• type_array
• type_json

@Fleid please feel free to weigh in or contradict me case you have both interest and insight on this!

Side note: it would be awesome to consult the most recent SQL standard to see which types we already have covered in some fashion and which we don't. Maybe this list is an accurate reflection of SQL standard data types? Not that dbt-core has to also define and support these, but would serve as a helpful guide.

And maybe we'd create a pretty picture like this too:

Implementation hints

We'd expect any adapter to override these methods as-needed (including raising a not supported exception when appropriate). Not all databases will support all of these types, but they might be able to be "faked" in some fashion (see SQL Server and booleans).

Each high-level default implementation will look like this:

dbt-core/core/dbt/include/global_project/macros/utils/data_types.sql

Lines 3 to 9 in 3bfce2b

	{%- macro type_string() -%}
	{{ return(adapter.dispatch('type_string', 'dbt')()) }}
	{%- endmacro -%}
	{% macro default__type_string() %}
	{{ return(api.Column.translate_type("string")) }}
	{% endmacro %}

Then the low-level default implementation will use translate_type and be like this:

dbt-core/core/dbt/adapters/base/column.py

Line 11 in 1df713f

"STRING": "TEXT",

For database adapters that need to override the default, it will look like this:
https://github.com/dbt-labs/dbt-bigquery/blob/main/dbt/adapters/bigquery/column.py#L14

        "STRING": "STRING",

Testing hints

1. Implement testing in dbt-core:
   • https://github.com/dbt-labs/dbt-core/blob/1df713fee986741289568f553befdd4e38a51122/tests/adapter/dbt/tests/adapter/utils/data_types/test_type_string.py
2. Also implement the testing in every adapter (dbt-biquery, dbt-snowflake, dbt-redshift, and dbt-spark):
   • https://github.com/dbt-labs/dbt-bigquery/blob/defb70e5311ae79620b9caf7567e45b26ffd5f56/tests/functional/adapter/utils/test_data_types.py#L27-L28

[jpmmcneill]

@jaypeedevlin related to discussion over in #5778, I'm happy to take the type_array that @dbeatty10 suggested. I have a few cross db macros that I've spun up for https://github.com/jpmmcneill/dbt-graph-theory that I might lift and shift over.

This is one that's grinding on my gears for a lack of support.

[jaypeedevlin]

You're welcome to any/all of it, I have some (temporary) code here you're welcome to borrow, although as Doug points out there will need to be changes in each adapter.

[dbeatty10]

@jpmmcneill very timely of you: #5520 is related to cross db macros for arrays.

Background:
dbt-labs/dbt-utils#595 added some macros related to arrays to dbt-utils:

• array_append
• array_concat
• array_construct

I'm in progress of moving those into dbt-core (plus each adapter, like dbt-redshift, etc)

Would be glad for any feedback you're able to provide on those implementations!

• Array macros #5823
• Array macros dbt-redshift#182
• Array macros dbt-snowflake#257
• Array macros dbt-bigquery#308
• Array macros dbt-spark#454

[dbeatty10]

@jpmmcneill or @jaypeedevlin do either of you still want to submit a PR for type_boolean()?

To have a chance to be included in dbt Core 1.3, we'd probably want to have it ready within the next 7 days.

General outline for implementation is here. The testing part is always the most time-consuming for me.

[jpmmcneill]

@dbeatty10 👋 I've spun up two PRs for type_boolean. I think the bigquery one is required because of how the inheritence is set up, but am not 100%.

dbt-core: #5875
dbt-bigquery: dbt-labs/dbt-bigquery#313

I need to figure out how to get my dbt-core local dev setup for running the data type tests (they're complaining about a module error when I try to pytest them).

Depending on how fast local dev will get setup, type array / json might be possible before 1.3. type_boolean is definitely the most simple version of the three!

[dbeatty10]

Awesome @jpmmcneill !

Continuing the conversation with next steps over at #5875
and dbt-labs/dbt-bigquery#313.

[jaypeedevlin]

Thanks @jpmmcneill for picking this up and apologies both for my absence, I’ve been dealing with a family emergency. Glad to see this looks like it will sneak in for 1.3

[dbeatty10]

Resolved by #5875, et al.

Thank you to @jpmmcneill, @jaypeedevlin, @Maayan-s and everyone else who participated in making this feature happen 🙌