What are the possible column-type values in the catalog.json?

[feliblo]

I'm building a tool and I want to handle all cases in an ENUM. The spec only provides that it's a string. I'm seeing the following options, but I'm certain this isn't extensive

"type": "INTEGER",
"type": "VARCHAR",
"type": "DATE",
 "type": "BOOLEAN",
"type": "BIGINT",
"type": "DOUBLE",
"type: "ARRAY<*>"

It doesn't seem to me like theres many more? Is there any resource? Are they different for each provider?

[pgoslatara]

Are they different for each provider?

Yes. I know for sure that Databricks produces a variety of these types, values like decimal(38, 16), decimal(38, 17), etc. For other adapters I expect similar is possible.

[mateuscarestiato]

Hi @pgoslatara! Yes — the column types in catalog.json are entirely adapter-specific. dbt doesn't normalize them; it passes through whatever the underlying database's information_schema returns.

There is no universal enum — each adapter has its own set. Here's a practical reference:

PostgreSQL / Redshift: integer, bigint, smallint, numeric, double precision, real, character varying, text, boolean, date, timestamp without time zone, timestamp with time zone, json, jsonb, uuid, array

BigQuery: INT64, FLOAT64, NUMERIC, BIGNUMERIC, STRING, BOOL, BYTES, DATE, DATETIME, TIME, TIMESTAMP, STRUCT, ARRAY, JSON, GEOGRAPHY

Snowflake: TEXT, NUMBER, FLOAT, BOOLEAN, DATE, TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ, VARIANT, OBJECT, ARRAY

Databricks / Spark: string, integer, bigint, double, float, decimal, boolean, date, timestamp, array<...>, map<...>, struct<...>

Recommendation for your ENUM: Since you can't enumerate all types statically, consider a fallback strategy:

python
KNOWN_TYPES = {"INTEGER", "VARCHAR", "DATE", "BOOLEAN", "BIGINT", "DOUBLE", ...}
def get_column_type(raw_type: str) -> str:
base_type = raw_type.split("<")[0].upper() # handles ARRAY → ARRAY
return base_type if base_type in KNOWN_TYPES else "UNKNOWN"
The best reference per adapter is each adapter's get_columns_in_relation implementation in the respective GitHub repo (e.g., dbt-bigquery, dbt-snowflake).

[mateuscarestiato]

Hi @feliblo! Yes — the column types in catalog.json are entirely adapter-specific. dbt doesn't normalize them; it passes through whatever the underlying database's information_schema returns.

There is no universal enum — each adapter has its own set. Here's a practical reference:

PostgreSQL / Redshift: integer, bigint, smallint, numeric, double precision, real, character varying, text, boolean, date, timestamp without time zone, timestamp with time zone, json, jsonb, uuid, array

BigQuery: INT64, FLOAT64, NUMERIC, BIGNUMERIC, STRING, BOOL, BYTES, DATE, DATETIME, TIME, TIMESTAMP, STRUCT, ARRAY, JSON, GEOGRAPHY

Snowflake: TEXT, NUMBER, FLOAT, BOOLEAN, DATE, TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ, VARIANT, OBJECT, ARRAY

Databricks / Spark: string, integer, bigint, double, float, decimal, boolean, date, timestamp, array<...>, map<...>, struct<...>

Recommendation for your ENUM: Since you can't enumerate all types statically, consider a fallback strategy:

python
KNOWN_TYPES = {"INTEGER", "VARCHAR", "DATE", "BOOLEAN", "BIGINT", "DOUBLE", ...}
def get_column_type(raw_type: str) -> str:
base_type = raw_type.split("<")[0].upper() # handles ARRAY → ARRAY
return base_type if base_type in KNOWN_TYPES else "UNKNOWN"
The best reference per adapter is each adapter's get_columns_in_relation implementation in the respective GitHub repo (e.g., dbt-bigquery, dbt-snowflake).