Dataform en production : déploiement en CLI, assertions et tests unitaires

Dataform est l'outil de Google Cloud pour écrire, tester et orchestrer des transformations SQL dans BigQuery. Cet article décrit son utilisation sur un projet réel :

• déploiement en ligne de commande depuis une chaîne CI/CD existante ;
• installation de la CLI sur les postes et isolation des développeurs dans un projet partagé ;
• estimation du coût des requêtes BigQuery affichée dans le terminal, dès le développement ;
• validation des résultats par assertions et double run ;
• tests unitaires, leurs limites et les fonctions utilitaires écrites pour les contourner ;
• conventions communes à plusieurs équipes.

Contexte : migration de chaînes BI SAS vers BigQuery chez Club Med, jusqu'en juin 2024. Le projet servait de pilote pour standardiser Dataform auprès des équipes data analysts et data engineering. Les lots migrés comprenaient notamment un export de données vers l'INSEE et une estimation du budget N+1 rapprochée de l'outil comptable. Leurs résultats alimentaient des tableaux de bord Power BI et Looker Studio, des fichiers livrés à un tiers, le contrôle de gestion et des analystes en SQL ou dans Excel. La migration devait rester invisible pour tous ces consommateurs.

Dataform en bref

Un projet Dataform est un dépôt Git de fichiers SQLX. Un fichier SQLX contient un bloc config et une requête SELECT. Dataform en tire le DDL/DML BigQuery : CREATE TABLE, CREATE VIEW ou MERGE, selon le type déclaré.

config {
  type: "incremental",
  uniqueKey: ["id_commande"],
  bigquery: {
    partitionBy: "DATE(date_commande)",
    clusterBy: ["pays"]
  },
  assertions: {
    nonNull: ["id_commande", "montant"]
  }
}

SELECT c.id_commande, c.date_commande, c.pays, c.montant * t.taux AS montant_eur
FROM ${ref("commandes")} c
JOIN ${ref("taux_conversion")} t USING (devise)
${when(incremental(), `WHERE c.date_commande > (SELECT MAX(date_commande) FROM ${self()})`)}

ref() produit le nom complet de la table et enregistre la dépendance. Dataform construit le graphe d'exécution à partir de ces appels, sans ordre à maintenir à la main.

Types d'actions utilisés sur le projet :

Les paramètres métier modifiables par des non-développeurs, comme les taux de conversion entre devises, étaient saisis dans une Google Sheet. BigQuery la lit comme table externe, déclarée dans Dataform avec declaration. Changer un taux ne demande ni merge request ni déploiement.

Trois commandes couvrent le cycle :

• dataform compile : génère le graphe et le SQL, sans rien exécuter ;
• dataform run : exécute les actions dans BigQuery ;
• dataform test : lance les tests unitaires.

Console Google Cloud ou CLI

Dataform s'exécute de deux façons.

Le projet a retenu la CLI, pour deux raisons :

• la DSI voulait limiter la dépendance au fournisseur et garder ses outils : GitLab CI, Ansible, OpCon ;
• le déploiement devait survivre à un changement d'entrepôt. Si BigQuery était remplacé, seules les requêtes seraient à réécrire, pas la chaîne de déploiement.

Chaîne de déploiement

Rôle de chaque maillon :

• Merge request : les fichiers SQLX et les tests sont revus dans GitLab.
• GitLab CI : lance dataform test sur chaque merge request.
• Déploiement : un job GitLab déclenché à la main appelle OpCon avec son client en ligne de commande. Il récupère les logs et le code retour, et le résultat de l'exécution s'affiche dans GitLab.
• OpCon : l'ordonnanceur existant, qui déclenchait déjà les chaînes SAS. Il déclenche aussi Dataform.
• Playbook Ansible : prépare la VM et lance dataform run.
• VM Compute Engine : partagée entre plusieurs services et équipes data. Chaque équipe exécute la CLI avec son propre compte de service, lié à OS Login. Les administrateurs se connectent par des sessions Wallix, elles aussi rattachées à OS Login.

Projets, datasets et droits

La VM est commune, mais les cibles ne le sont pas. Le découpage en projets Google Cloud et en datasets suit les domaines de données et les équipes de gouvernance. Ce découpage fixe aussi les droits IAM de chaque compte de service : un compte n'écrit que dans les datasets de son domaine.

Le catalogue de données de l'entreprise, Zeenea à l'époque, recensait ce patrimoine. Son connecteur Google Cloud avait un droit de lecture sur les métadonnées BigQuery. Les champs et le lignage des tables produites par Dataform y remontaient sans saisie manuelle.

Avantages et inconvénients

Avantages :

• le déploiement est un playbook versionné, exécuté sur une VM gérée par l'équipe, avec des comptes de service dédiés ;
• aucune action manuelle dans une interface web ;
• le résultat d'un déploiement (logs, code retour) reste dans GitLab, à côté de la merge request qui l'a produit ;
• l'exploitation suit les chaînes SAS et Dataform dans le même plan de production OpCon, et intervient au même endroit en cas d'échec.

Inconvénients :

• cinq composants à maintenir, là où la console n'en demande aucun ;
• chaque composant est un point de panne supplémentaire ;
• OpCon, un outil legacy, se retrouve au cœur de la nouvelle plateforme ;
• l'intégration d'un nouvel arrivant prend plus de temps.

Installer et maintenir la CLI

Installation sur les postes

Les data analysts et data engineers travaillaient sous Windows. Un script bash installait l'environnement :

• nvm pour Windows ;
• une version épinglée de Node.js ;
• une version épinglée de @dataform/cli.

Tout le monde avait les mêmes versions, et les écarts de comportement entre postes ne venaient pas de l'outil.

Isoler chaque développeur dans un projet partagé

Tout le monde développait dans le même projet Google Cloud sandbox/staging. Sans isolation, deux dataform run lancés en même temps écrivent dans les mêmes tables.

Un utilitaire lisait le nom d'utilisateur git et le normalisait au format accepté par BigQuery pour un nom de dataset : minuscules, lettres, chiffres et _. Il générait ensuite trois fichiers :

• un alias de la commande dataform, qui ajoute les options propres à l'utilisateur ;
• le fichier d'identifiants .df-credentials.json ;
• un dataform.json qui pointe vers le projet sandbox/staging.

Avec schemaSuffix / tablePrefix, le dataset ventes devient ventes_jdupont pour cet utilisateur. Chacun travaille sur ses propres tables, et personne n'a de paramètre à renseigner à la main.

Afficher le coût de chaque requête

BigQuery facture à la demande selon le volume de données lues. La CLI Dataform n'affichait pas ce volume.

Le script d'installation modifiait le code JavaScript de la CLI :

1. repérer dans les sources l'endroit où la CLI lit totalBytesProcessed ;
2. multiplier cette valeur par le tarif à la demande de la région, en euros par To ;
3. afficher le résultat en couleur, pour chaque action, pendant dataform run.

Sortie reconstituée à titre d'illustration ; noms et montants fictifs.

$ dataform run
  Table updated:    ventes_jdupont.agregat_journalier [incremental]
                    67,3 Go lus · 0,40 €
  Table created:    ventes_jdupont.commandes_eur [table]
                    612,0 Go lus · 3,67 €
  Assertion passed: assertions_jdupont.commandes_eur_assertions_rowConditions
                    1,2 Go lus · 0,01 €

Ici, commandes_eur lit 612 Go pour une table de quelques Go : c'est un filtre à revoir sur la colonne de partitionnement.

Le montant sert surtout à comparer le volume de données lues au volume de données retenues. Un écart important signale :

• un filtre qui n'utilise pas la colonne de partitionnement ;
• un clustering sans effet sur la requête ;
• une jointure mal écrite, qui lit une table entière pour en garder quelques lignes.

Le développeur voit ce signal dès sa première exécution en sandbox, avant la revue de code et bien avant la facture.

Contrepartie : le patch porte sur du code tiers. Il est à réappliquer et à vérifier à chaque montée de version de la CLI. C'est une raison de plus d'épingler les versions. Avec Dataform dans la console, l'équivalent passe par une requête sur INFORMATION_SCHEMA.JOBS.

Valider les résultats

Brancher les consommateurs avant de migrer le calcul

Première étape, avant toute réécriture : une vue BigQuery lit l'export Cloud Storage des fichiers que SAS produit déjà. Les tableaux de bord, les analystes et les exports se connectent à cette vue. Droits, connexions et habitudes sont en place des mois avant la migration du calcul.

Pour basculer, on change la définition de la vue : elle lit les tables Dataform au lieu de l'export. Revenir en arrière demande une seule requête.

Assertions intégrées

Les invariants simples se déclarent dans le bloc config de la table :

config {
  type: "table",
  assertions: {
    uniqueKey: ["id_commande"],
    nonNull: ["id_commande", "montant"],
    rowConditions: ["montant >= 0"]
  }
}

Dataform génère une action d'assertion par règle, dépendante de la table. Si une règle échoue, l'exécution échoue, et les actions qui dépendent de la table ne tournent pas.

Double run : comparer ancienne et nouvelle chaîne

OpCon enchaîne trois étapes : la chaîne SAS, la chaîne Dataform, puis la comparaison.

Côté SAS, un post-traitement préparait les résultats avant l'export sur Cloud Storage :

• conversion dans un format lisible par BigQuery ;
• normalisation des types, des formats de date et de la précision ;
• application des écarts voulus, pour comparer les deux chaînes sur le même périmètre.

BigQuery lit ces fichiers comme tables externes, déclarées dans Dataform avec declaration.

Méthode :

1. Hacher chaque ligne entière : la ligne est convertie en chaîne, puis hachée.
2. Réunir les deux sources dans une table, avec une colonne source (ancienne / nouvelle) comme discriminant.
3. Compter les lignes par hash et par source. Vérifier que les deux chaînes produisent le même nombre de lignes.
4. Retirer les hash présents autant de fois des deux côtés : ces lignes sont reproduites à l'identique.
5. Analyser ce qui reste, c'est-à-dire les lignes encore en écart.

Requête simplifiée, noms et fonctions illustratifs :

WITH lignes AS (
  SELECT 'ancienne' AS source, TO_JSON_STRING(a) AS contenu
  FROM ${ref("export_sas_agregat")} a
  WHERE NOT (pays = 'XX' AND date_commande >= '2023-01-01')  -- écart voulu
  UNION ALL
  SELECT 'nouvelle', TO_JSON_STRING(n)
  FROM ${ref("agregat_journalier")} n
  WHERE NOT (pays = 'XX' AND date_commande >= '2023-01-01')
)
SELECT
  FARM_FINGERPRINT(contenu)    AS hash_ligne,
  ANY_VALUE(contenu)           AS exemple,
  COUNTIF(source = 'ancienne') AS nb_ancienne,
  COUNTIF(source = 'nouvelle') AS nb_nouvelle
FROM lignes
GROUP BY hash_ligne
HAVING nb_ancienne != nb_nouvelle

La table d'écarts porte une assertion : elle échoue tant qu'il reste des lignes. L'équipe analyse ensuite les lignes restantes à la main.

Le hachage d'une ligne entière suppose que les deux côtés ont les mêmes colonnes, dans le même ordre, avec les mêmes types et le même format. Toute différence de précision, d'arrondi ou de format de date produit un hash différent. C'est ce qui a fait apparaître les cas limites décrits dans la section sur les tests unitaires.

Écarts voulus. La réécriture a corrigé des bugs mineurs et modifié certaines règles métier. Ces écarts sont traités des deux côtés : par le post-traitement SAS, et par des filtres dans les requêtes de comparaison. Sans ce traitement, les écarts voulus masquent les écarts subis.

Comparaisons génériques. Écrire une requête par table ne passe pas à l'échelle. Des actions operations écrites en JavaScript génèrent un script BigQuery pour une paire de tables. Le script lit la liste des colonnes dans INFORMATION_SCHEMA.COLUMNS au moment de l'exécution, puis construit la comparaison avec EXECUTE IMMEDIATE.

Le JavaScript de Dataform s'exécute à la compilation, sans accès aux données. Il ne peut pas connaître les colonnes d'une table. La lecture des colonnes doit donc se faire dans le SQL généré, à l'exécution.

Script généré, simplifié :

DECLARE colonnes STRING;

SET colonnes = (
  SELECT STRING_AGG(column_name, ', ' ORDER BY column_name)
  FROM `projet.ventes.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'agregat_journalier'
);

EXECUTE IMMEDIATE FORMAT("""
  CREATE OR REPLACE TABLE recette.ecarts_agregat_journalier AS
  WITH lignes AS (
    SELECT 'ancienne' AS source, TO_JSON_STRING(STRUCT(%s)) AS contenu FROM ventes.export_sas_agregat
    UNION ALL
    SELECT 'nouvelle', TO_JSON_STRING(STRUCT(%s)) FROM ventes.agregat_journalier
  )
  SELECT FARM_FINGERPRINT(contenu) AS hash_ligne, ...
""", colonnes, colonnes);

Trier les colonnes par nom (ORDER BY column_name) aligne les deux côtés, même si leur ordre de déclaration diffère.

Fichiers livrés à un tiers. Le format est imposé au caractère près. Pendant la recette, ces fichiers ont été comparés à la main avec ceux produits par SAS.

Deux passes

Chaque exécution du double run coûte deux fois le traitement, d'où deux passes :

1. Forme : sur un sous-ensemble de partitions journalières. On vérifie la structure, les types et les cardinalités, sur des exécutions courtes et peu coûteuses.
2. Fond : une fois la forme stable, la recette porte sur l'ensemble des données.

Tests unitaires

Principe

Un test Dataform remplace les sources d'une table par des lignes littérales, exécute la requête de la table dans BigQuery, et compare le résultat à des lignes attendues, elles aussi littérales. dataform test lance tous les tests. Sur le projet, GitLab CI l'exécutait sur chaque merge request.

Deux syntaxes :

SQLX, lisible par un data analyst :

config {
  type: "test",
  dataset: "agregat_journalier"
}

input "commandes" {
  SELECT 1 AS id_commande, DATE '2023-03-31' AS date_commande, 'FR' AS pays, 10.25 AS montant
  UNION ALL
  SELECT 2, DATE '2023-04-01', 'FR', NULL
}

SELECT DATE '2023-03-31' AS date_commande, 'FR' AS pays, 10.25 AS montant_total
UNION ALL
SELECT DATE '2023-04-01', 'FR', 0

JavaScript, plus expressif, qui demande de savoir programmer :

test("agregat_journalier_valeur_absente")
  .dataset("agregat_journalier")
  .input("commandes", `SELECT ... UNION ALL SELECT ...`)
  .expect(`SELECT ... UNION ALL SELECT ...`);

Les deux syntaxes ont été utilisées : SQLX pour les tests écrits par les data analysts, JavaScript pour ceux qui profitaient des fonctions utilitaires.

Documentation : au moment d'écrire les tests, la page officielle sur les tests unitaires n'était plus en ligne. La référence utilisée a été l'exemple dataform_udf_unit_test du dépôt bigquery-utils de Google.

Limites de la comparaison

Le résultat et l'attendu sont comparés position par position.

• Ordre des lignes. Ni un UNION ALL de SELECT littéraux ni la requête testée ne garantissent un ordre. Sans ordre imposé des deux côtés, un même test passe ou échoue d'une exécution à l'autre.
• Ordre des colonnes. Les colonnes sont comparées dans l'ordre de déclaration, pas par nom. Ajouter une colonne au milieu d'un SELECT décale la comparaison. Le test échoue sur la mauvaise colonne, ou passe en comparant des colonnes sans rapport.

Fonctions utilitaires

Des fonctions JavaScript, dans includes/, génèrent le SQL des tests :

• valeurs par défaut : chaque table source a un modèle de ligne complet, et un cas de test ne déclare que les colonnes qui le concernent ;
• colonnes alignées : l'ordre des colonnes vient du modèle, pas de l'auteur du test ;
• génération des UNION ALL à partir d'un tableau de lignes ;
• ordre imposé côté fixtures : les lignes sont triées en JavaScript avant la génération des UNION ALL ;
• ordre imposé côté modèle, seulement en test : une fonction lit la configuration Dataform pour savoir dans quel contexte le projet s'exécute. En test, elle ajoute un ORDER BY à la sortie de la table. Ailleurs, elle ne produit rien : dans une table écrite en production, un tri n'apporte rien et coûte du calcul.

Illustration :

const COMMANDES = {
  id_commande:   "CAST(NULL AS INT64)",
  date_commande: "CAST(NULL AS DATE)",
  pays:          "'FR'",
  montant:       "CAST(0 AS NUMERIC)",
};

function lignes(modele, cas) {
  return cas
    .map(ligne => "SELECT " + Object.keys(modele)
      .map(col => `${col in ligne ? ligne[col] : modele[col]} AS ${col}`)
      .join(", "))
    .join("\nUNION ALL\n");
}

module.exports = { COMMANDES, lignes };

const { COMMANDES, lignes } = require("includes/fixtures");

test("montant_absent_compte_zero")
  .dataset("agregat_journalier")
  .input("commandes", lignes(COMMANDES, [
    { id_commande: 1, date_commande: "DATE '2023-03-31'", montant: "NULL" },
  ]))
  .expect(`SELECT DATE '2023-03-31' AS date_commande, 'FR' AS pays, 0 AS montant_total`);

function trierEnTest(colonnes) {
  const enTest = dataform.projectConfig.vars.contexte === "test";
  return enTest ? `ORDER BY ${colonnes.join(", ")}` : "";
}

module.exports = { trierEnTest };

SELECT date_commande, pays, SUM(montant) AS montant_total
FROM ${ref("commandes")}
GROUP BY date_commande, pays
${tri.trierEnTest(["date_commande", "pays"])}

Un test se lit alors comme le cas qu'il décrit : une commande sans montant, le 31 mars.

Cas limites rencontrés pendant la migration

Le double run a fait apparaître des écarts qui ne venaient pas d'erreurs de traduction. Ils venaient de règles implicites, portées par le comportement de SAS. Chacun a donné lieu à un test :

Une règle testée est documentée et vérifiée à chaque merge request. Modifier la règle fait échouer le test.

Modélisation BigQuery

Les données se répartissent en deux périodes :

• l'historique clos, qui ne change plus ;
• la période récente, encore corrigeable et la plus requêtée.

Le modèle les sépare :

• une table pour l'historique figé ;
• une table pour la période récente, alimentée selon le cas d'usage :
  • incremental avec uniqueKey, pour que les lignes corrigées soient mises à jour par MERGE ;
  • remplacement des partitions récentes, supprimées puis réécrites à chaque exécution ;
• une clôture périodique, qui fait passer les périodes closes de la table récente à la table figée ;
• des vues d'union (UNION ALL) entre historique et période récente. Une vue peut elle-même réunir d'autres vues d'union, et le modèle s'empile sur plusieurs niveaux. Les consommateurs n'interrogent que la vue du niveau le plus haut.

Les tables sont partitionnées par jour et clusterisées, par déclaration dans le bloc config :

config {
  type: "incremental",
  uniqueKey: ["id_commande"],
  bigquery: {
    partitionBy: "DATE(date_commande)",
    clusterBy: ["pays"]
  }
}

Effets :

• une période close n'est plus recalculée après sa clôture, et une correction sur la période récente ne peut pas la modifier ;
• une requête filtrée sur le mois courant ne lit que les partitions du mois, pas des années d'historique.

La vérification passe par le coût affiché à chaque action. Une requête sur le mois qui lit autant de données qu'une requête sur l'historique complet signale un filtre qui ne tombe pas sur la colonne de partitionnement.

Inconvénient des vues d'union : les métadonnées remontent mal. Les descriptions de colonnes et le lignage déclarés sur les tables ne se propagent pas d'eux-mêmes aux vues, et chaque niveau de vue empilée ajoute une étape à documenter pour qu'ils arrivent jusqu'au catalogue.

Standardiser Dataform pour plusieurs équipes

Le projet servait de pilote pour standardiser Dataform auprès des équipes data analysts et data engineering. Livrables, en plus du code :

• arborescence et conventions de nommage des dossiers, fichiers, datasets et actions ;
• modèles de tables à recopier, un par cas d'usage : table complète, incremental avec MERGE, remplacement de partitions, historique figé et vue d'union ;
• exemples de tests, en SQLX et en JavaScript, avec les fonctions utilitaires ;
• outillage du poste : script d'installation, utilitaire de configuration, affichage du coût ;
• provisioning : dépôt GitLab, projets Google Cloud, datasets et droits IAM, déclinés à partir du domaine de données de l'équipe.

Le provisioning s'appuyait sur un socle construit les deux années précédentes sur la même mission : une project factory Terraform, avec des droits demandés par merge request. Détail dans la fiche de mission Club Med.

Documenter les règles métier depuis le graphe compilé

Les règles métier sont décrites au plus près de la requête qui les applique, dans un commentaire multiligne au format maison.

Illustration :

/*
  @regle       arrondi_montants
  @description Montants arrondis au centime, au pair le plus proche.
  @reference   
*/
SELECT ROUND(montant, 2, "ROUND_HALF_EVEN") AS montant
FROM ${ref("commandes")}

Un script Python lit ces commentaires. Il ne parcourt pas les fichiers SQLX : il lit le graphe JSON produit par dataform compile --json. Ce graphe contient pour chaque action la table cible, le fichier source, les dépendances et le SQL compilé, commentaires compris. Le script :

1. extrait les balises de chaque action ;
2. rattache chaque règle à la table qui l'applique et à ses dépendances ;
3. publie de la documentation Markdown : versionnée dans le dépôt avec le code, et publiée sur le wiki / GitLab Pages par la CI ;
4. renvoie vers la documentation métier de référence.

La règle est écrite au moment où la requête est réécrite. C'est le moment où la personne qui migre en connaît encore le contexte.

Bilan

• La chaîne de données Google Cloud est intégrée à l'infrastructure on-premise, avec nos outils. GitLab CI teste et déclenche, OpCon ordonnance, Ansible exécute, Wallix et OS Login gèrent les accès. L'exploitation pilote les chaînes SAS et Dataform au même endroit, avec les mêmes procédures.
• La dépendance au fournisseur est limitée. Code, tests, déploiement et ordonnancement restent dans des outils maîtrisés par l'équipe. Le projet tire parti de BigQuery et de Dataform sans dépendre de la console Google Cloud ni de ses services d'orchestration.
• Les transformations sont documentées et testables. Les règles implicites de SAS sont devenues des tests unitaires et des assertions, vérifiés à chaque merge request. La documentation des règles métier est générée depuis le graphe compilé, et le lignage remonte au catalogue de données.
• L'équipe a suivi le cycle de vie d'un produit racheté puis intégré à Google Cloud. Dataform, racheté par Google en 2020, a évolué pendant le projet. L'équipe a gardé la main : versions épinglées, CLI enrichie de l'affichage du coût, tests unitaires outillés malgré le retrait de la documentation officielle.

Merci à Xavier Oger, qui portait le projet côté Club Med.