dbt should handle json / variant data more ergonomically

[matt-winkler]

Related to 5778

Unpacking JSON events is a common step in the early stages of dbt pipelines. Semi-structured data presents a few challenges that are more acute than when handling tabular data:

Today, dbt users are writing their own parsing code

• We can do more to abstract common operations into standardized macros, e.g. this example

Another Snowflake example from a dbt Labs internal repository:

{% macro json_to_model_sql( source_or_model_name, json_column, is_source = false, source_name = '' ) %}
    {% set columns = generate_column_names( source_or_model_name, json_column, is_source, source_name ) %}
    
    select 
    {% for column in columns %}
        {{ json_column }}:{{ column.COLUMN_NAME }} as {{ column.COLUMN_NAME }}
        {%- if not loop.last -%},{%- endif -%}
    {% endfor %}
    from
    {% if not is_source %}
        {{ ref(source_or_model_name) }}
    {% else %}
        {{ source(source_name, source_or_model_name) }}    
    {% endif %}
{% endmacro %}

{# generates a list of columns names from a json column #}
{% macro generate_column_names(source_or_model_name, json_column, is_source = false, source_name = '', row_limit = 10000) %}

{% set json_parse_query %}

select distinct
json_data.key as COLUMN_NAME
from (select * from 
{% if not is_source %}
    {{ ref(source_or_model_name) }}
{% else %}
    {{ source(source_name, source_or_model_name) }}    
{% endif %} limit {{ row_limit }}) json_table
,
lateral flatten( input => json_table.{{ json_column }} ) json_data
{% endset %}

{% set results = run_query(json_parse_query) %}
{% if execute %}
    {% set res_list = results.rows %}
{% else %}
    {% set res_list = [] %}
{% endif %}

{{ return(res_list) }}

{% endmacro %}

The syntax for parsing json is not uniform across data warehouses

To make this as easy as possible for end users, should dbt have cross-database compatible macros?

-- in snowflake
select
  id,
  parse_json(data) as data
from {{ ref('input_model') }}

-- in bigquery
select
  id,
  json_extract(data) as data
from {{ ref('input_model') }}

-- multi db version via macros and adapter.dispatch
select
  id,
  {{ json_from_string('data') }} as data
from {{ ref('input_model') }}

Schema Management

• Some data warehouses (e.g. databricks) require specifying the json schema in advance of parsing. How to make this as ergonomic as possible?
• It's easy for inferred data types to change from run to run. E.g. in run 1 field1 may contain all INT values, while in run 2 some values may be null causing the parsed field to be inferred as a STRING.

[matt-winkler]

Curious @jtcohen6 @dbeatty10 what might our appetite be for creating some additional functionality (materializations??) here?

[dbeatty10]

What do you have in mind for additional functionality @matt-winkler ?

Appetite will definitely depend on potential impact vs. relative effort.