Union all vs unpivot

UNION ALL ou UNPIVOT

Résumé

UNION ALL pose problème dans un seul cas : transformer N colonnes d’une même table en N lignes en écrivant une branche par colonne. Chaque branche relit la table. Un UNPIVOT (ou CROSS APPLY (VALUES …) sur SQL Server) la lit une seule fois. Empiler N tables différentes avec UNION ALL est normal : aucune syntaxe ne dispense de les lire. Sur BigQuery, qui est colonnaire, le gain du premier cas est plus faible que sur un moteur ligne comme SQL Server.

Prérequis

Savoir ce que fait UNION ALL (concaténer des résultats sans dédupliquer) et ce qu’est un moteur colonnaire (il ne lit que les colonnes demandées).

Passer d’une table large à une table longue

Une table « large » a une colonne par type de charge ; une table « longue » a une ligne par charge.

Avant :

order_id	items_subtotal	shipping_fee	tax_amount
1	100	8	20

Après :

order_id	charge_type	amount
1	ItemsSubtotal	100
1	ShippingFee	8
1	TaxAmount	20

Cas 1 : N colonnes d’une même table

L’écriture courante est une branche par colonne :

SELECT order_id, 'ItemsSubtotal' AS charge_type, items_subtotal AS amount
FROM stg.order_extract
WHERE items_subtotal IS NOT NULL
UNION ALL
SELECT order_id, 'ShippingFee', shipping_fee
FROM stg.order_extract
WHERE shipping_fee IS NOT NULL
UNION ALL
SELECT order_id, 'TaxAmount', tax_amount
FROM stg.order_extract
WHERE tax_amount IS NOT NULL

Chaque branche référence la table séparément, avec un filtre différent : le moteur ne les regroupe pas. Trois colonnes (comme ici) donnent trois lectures ; N colonnes, N lectures. Les clauses WHERE écrites à la main peuvent aussi diverger au fil du temps.

Le correctif

BigQuery et dbt : UNPIVOT.

select order_id, charge_type, amount
from {{ ref('stg_order_extract') }}
unpivot (
    amount for charge_type in (
        items_subtotal as 'ItemsSubtotal',
        shipping_fee   as 'ShippingFee',
        tax_amount     as 'TaxAmount'
    )
)

UNPIVOT exclut par défaut les lignes dont la valeur est NULL. Il demande des colonnes de même type.

SQL Server : CROSS APPLY (VALUES …).

SELECT o.OrderId, c.ChargeType, c.Amount
FROM stg.OrderExtract AS o
CROSS APPLY (VALUES
    ('ItemsSubtotal', o.ItemsSubtotal),
    ('ShippingFee',   o.ShippingFee),
    ('TaxAmount',     o.TaxAmount)
) AS c (ChargeType, Amount)
WHERE c.Amount IS NOT NULL

Pour chaque ligne lue, VALUES produit N lignes en mémoire. Cette forme accepte des casts et des colonnes calculées, ce qu’UNPIVOT accepte moins bien. PostgreSQL a un équivalent avec CROSS JOIN LATERAL (VALUES …).

Effet sur un moteur ligne

Sur un moteur orienté lignes comme SQL Server, chaque branche d’un UNION ALL relit la table : dix branches font environ dix fois plus de lectures logiques qu’un seul passage, et c’est ce que CROSS APPLY (VALUES …) supprime. Le temps CPU change peu quand l’écriture du résultat domine. Mesurer sur les données concernées avec SET STATISTICS IO ON avant et après.

Sur BigQuery

BigQuery lit seulement les colonnes référencées par chaque branche, et facture les octets de ces colonnes. Chaque branche lit donc order_id et sa colonne de charge, pas toute la table. L’économie d’un UNPIVOT porte surtout sur les colonnes communes relues N fois (ici order_id) et sur la simplicité du plan. Elle est réelle, mais bien moindre que dans l’exemple SQL Server. Pour la quantifier, comparer les octets facturés de deux versions sur la même table (dry run).

Cas 2 : N tables différentes

SELECT order_id, amount, 'EU' AS region FROM sales.orders_eu
UNION ALL
SELECT order_id, amount, 'US' AS region FROM sales.orders_us
UNION ALL
SELECT order_id, amount, 'APAC' AS region FROM sales.orders_apac

Ici, UNPIVOT ne s’applique pas : il transforme des colonnes en lignes, il ne fusionne pas des objets de stockage distincts. Chaque table doit être lue. Les leviers sont ailleurs :

• Ne sélectionner que les colonnes utiles : SELECT * lit toutes les colonnes de chaque table.
• Partitionner chaque table source et filtrer sur la partition dans chaque branche.
• Préférer UNION ALL à UNION : UNION déduplique, ce qui coûte du calcul et n’est utile que si les doublons comptent.
• Selon le cas, une autre structure : si les tables ont un schéma identique, une seule table partitionnée par région remplace le UNION ALL ; sur BigQuery, les wildcard tables interrogent plusieurs tables par motif de nom ; en dbt, la macro dbt_utils.union_relations génère l’union.

Piège : un modèle coûteux référencé N fois

Si N branches repassent par le même modèle dbt non matérialisé (avec des jointures coûteuses), ce modèle est réévalué N fois. Le correctif est de matérialiser ce modèle intermédiaire en table, pas de changer le UNION ALL.

Côté dbt

• Cas 1 : un seul ref() vers le modèle source au lieu de N. Les tests sont attachés au modèle, pas au nombre de ref() : ils ne sont pas exécutés N fois dans le cas 1.
• Cas 2 : N ref() dans le DAG est normal.
• Ajouter un type de charge revient à ajouter une ligne dans la liste unpivot ou VALUES.

Diagnostiquer avant d’optimiser

Deux questions

1. La requête relit-elle plusieurs fois la même donnée ? Si oui, UNPIVOT.
2. Ou lit-elle des tables différentes ? Alors UNION ALL convient, et l’effort va au partitionnement et à la projection de colonnes.

Notes liées

• OLTP vs OLAP et stockage en colonnes : pourquoi un moteur colonnaire ne lit que les colonnes demandées.
• Parquet, le format de fichier colonnaire : le même principe, sous forme de fichier.
• dbt — Fondamentaux : ref(), matérialisation, DAG.
• Glossaire data engineering : moteur colonnaire, partition.
• MOC Data Engineering : carte du vault.