Opérations sur les tableaux dbt multi-warehouse

Les opérations sur les tableaux divergent plus fondamentalement entre les warehouses que les fonctions de date ou le cast de types. Les différences de dates et de cast sont principalement des questions d’ordre des arguments ; les opérations sur les tableaux utilisent des syntaxes et des mots-clés complètement différents, sans aucune similarité syntaxique entre les plateformes.

dbt ne fournit aucune macro multi-warehouse intégrée pour les tableaux. Le pattern dispatch est l’approche standard.

Le paysage syntaxique

Même ARRAY_CONTAINS ne peut pas se mettre d’accord sur l’ordre des arguments entre Snowflake et Databricks. BigQuery contourne entièrement la fonction avec son pattern IN UNNEST.

Les différences d’aplatissement sont les plus impactantes car elles affectent la structure même de la clause FROM. UNNEST de BigQuery effectue une jointure implicite avec une virgule. Snowflake utilise LATERAL FLATTEN comme fonction de table. Databricks utilise LATERAL VIEW EXPLODE, une syntaxe héritée de Hive. Ce ne sont pas juste des noms de fonctions différents — ce sont des structures grammaticales différentes dans la requête.

Macro dispatch pour l’aplatissement des tableaux

Voici une macro dispatch qui gère l’aplatissement des tableaux dans une clause FROM :

{% macro flatten_array(table_alias, array_column, element_alias) %}
    {{ return(adapter.dispatch('flatten_array', 'my_project')(
        table_alias, array_column, element_alias
    )) }}
{% endmacro %}

{% macro bigquery__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }}, UNNEST({{ array_column }}) AS {{ element_alias }}
{% endmacro %}

{% macro snowflake__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }}, LATERAL FLATTEN(input => {{ array_column }}) AS {{ element_alias }}
{% endmacro %}

{% macro databricks__flatten_array(table_alias, array_column, element_alias) %}
    {{ table_alias }} LATERAL VIEW EXPLODE({{ array_column }}) AS {{ element_alias }}
{% endmacro %}

Notez que le second argument de adapter.dispatch() est le nom du projet ('my_project'). Cela est requis quand vous souhaitez que l’ordre de recherche dispatch s’applique correctement, notamment si cette macro réside dans un package.

Utilisation dans un modèle :

SELECT
    e.event_id,
    param.value AS param_value
FROM {{ ref('base__app__events') }} e
{{ flatten_array('e', 'e.event_params', 'param') }}

Cela compile vers UNNEST sur BigQuery, LATERAL FLATTEN sur Snowflake ou LATERAL VIEW EXPLODE sur Databricks. Le modèle lui-même se lit clairement — la complexité spécifique à la base de données est cachée derrière la macro.

Pourquoi les tableaux sont courants dans les données analytics

Les colonnes de type tableau apparaissent fréquemment en analytics engineering, notamment dans les données d’événements. L’export BigQuery de GA4 stocke les paramètres d’événements sous forme de champs répétés imbriqués qui nécessitent UNNEST pour être interrogés. Les données semi-structurées de Snowflake issues de l’ingestion JSON contiennent souvent des tableaux. Databricks traite les tableaux issus de flux Kafka et de fichiers Parquet imbriqués.

Si vous construisez un package dbt qui gère des données d’événements, l’aplatissement des tableaux est probablement le premier problème multi-warehouse que vous rencontrerez. C’est également celui où copier-coller un pattern spécifique à Snowflake depuis Stack Overflow dans un projet BigQuery crée le plus de confusion, car la syntaxe est si fondamentalement différente que les messages d’erreur n’aident pas à comprendre ce qui s’est passé.

Accès aux tableaux imbriqués

L’aplatissement n’est que la première opération. Une fois aplatis, vous avez souvent besoin d’accéder aux champs des éléments aplatis. Cela diverge également :

Sur BigQuery, UNNEST produit une ligne par élément, et vous accédez directement aux champs de struct :

SELECT
    event_name,
    param.key,
    param.value.string_value
FROM events, UNNEST(event_params) AS param

Sur Snowflake, LATERAL FLATTEN produit un résultat avec des colonnes de métadonnées. La valeur réelle réside dans value :

SELECT
    event_name,
    f.value:key::STRING AS param_key,
    f.value:value:string_value::STRING AS param_value
FROM events, LATERAL FLATTEN(input => event_params) f

Sur Databricks, EXPLODE fonctionne de manière similaire à BigQuery pour les tableaux simples mais utilise une syntaxe différente pour l’accès aux structs :

SELECT
    event_name,
    param.key,
    param.value.string_value
FROM events
LATERAL VIEW EXPLODE(event_params) AS param

Les patterns d’accès après aplatissement sont suffisamment différents pour qu’une seule macro dispatch pour l’aplatissement n’abstraie pas entièrement le problème. Pour les données imbriquées complexes, vous pourriez avoir besoin de macros dispatch pour les patterns d’accès aux champs également, ou concevoir vos modèles de base pour normaliser les tableaux en colonnes plates dès le début du DAG, afin que les modèles en aval n’aient pas à gérer la syntaxe des tableaux.

L’approche pragmatique

Pour la plupart des équipes, la stratégie pragmatique est :

1. Aplatir les tableaux dans la couche base de votre architecture trois couches. Un modèle par source qui gère toutes les opérations sur les tableaux.
2. Utiliser des macros dispatch dans ces modèles de base pour qu’ils soient portables.
3. Garder les couches intermédiaires et mart sans tableaux. Les modèles en aval travaillent avec des données plates et relationnelles identiques entre les bases de données.

Cela concentre la complexité multi-warehouse dans un petit nombre de modèles plutôt que de la disperser dans tout le projet. Quand vous ajoutez le support d’une nouvelle base de données, vous n’avez qu’à mettre à jour les implémentations dispatch de la couche base.