ServicesÀ proposNotesContact Me contacter →
EN FR
Note

Précision des nombres à virgule flottante dans la comparaison de données

Pourquoi l'égalité exacte échoue pour les valeurs à virgule flottante dans la comparaison de données, et stratégies pratiques pour gérer les décalages de précision.

Planté
dbtdata qualitytesting

Chaque outil de comparaison de données utilisant l’égalité exacte produira de faux désaccords sur les valeurs à virgule flottante. C’est une propriété fondamentale de la façon dont les ordinateurs représentent les nombres décimaux, pas un bug dans l’outil. Les contournements s’appliquent lors de la refactorisation, de la validation de migration et des tests CI.

Le problème

dbt-audit-helper, les requêtes EXCEPT et la plupart des approches de comparaison utilisent l’égalité exacte (=) pour déterminer si deux valeurs correspondent. Il n’existe pas de tolérance epsilon intégrée pour les nombres à virgule flottante. Une valeur de 99.9999999999 et 100.0000000001 sont traitées comme une divergence même lorsqu’elles sont fonctionnellement identiques pour tout usage métier.

Cela se manifeste dans des scénarios prévisibles :

  • Champs calculés où l’ordre des opérations arithmétiques diffère entre deux systèmes (multiplication avant division vs division avant multiplication peut produire des résultats différents en virgule flottante)
  • Agrégations où SUM ou AVG opère sur des lignes dans un ordre différent (l’addition en virgule flottante n’est pas commutative en raison de la perte de précision à chaque étape)
  • Comparaisons cross-plateformes où un système utilise FLOAT64 et l’autre NUMERIC/DECIMAL avec des représentations internes différentes
  • Migrations ETL où l’ancien système arrondit à l’étape 3 et le nouveau arrondit à l’étape 5, produisant des valeurs qui diffèrent d’une fraction de centime

Le contournement : caster avant de comparer

L’approche la plus fiable est d’arrondir ou de caster à une précision spécifique avant la comparaison. Avec dbt-audit-helper, utilisez compare_queries plutôt que compare_relations pour contrôler le SQL :

{{ audit_helper.compare_queries(
    a_query="SELECT revenue__id, ROUND(revenue__amount, 2) AS revenue__amount FROM " ~ ref('mrt__finance__revenue'),
    b_query="SELECT revenue__id, ROUND(revenue__amount, 2) AS revenue__amount FROM analytics_db.production.mrt__finance__revenue",
    primary_key="revenue__id"
) }}

Le ROUND(..., 2) garantit que les deux côtés sont comparés à la même précision. Choisissez la précision qui correspond à vos exigences métier — deux décimales pour les devises, quatre pour les taux, zéro pour les comptages.

Pour les requêtes EXCEPT manuelles, le même principe s’applique :

SELECT
  id,
  ROUND(amount, 2) AS amount,
  ROUND(tax_rate, 4) AS tax_rate
FROM old_system.orders
EXCEPT DISTINCT
SELECT
  id,
  ROUND(amount, 2) AS amount,
  ROUND(tax_rate, 4) AS tax_rate
FROM new_system.orders

Quand l’arrondi ne suffit pas

L’arrondi gère la plupart des cas, mais certaines situations nécessitent plus de réflexion.

Dérive de précision accumulée. Lorsqu’une valeur est le résultat de nombreux calculs séquentiels (totaux cumulés, intérêts composés, allocations multi-étapes), la dérive de précision peut dépasser les seuils d’arrondi simples. Dans ces cas, comparez les étapes de calcul intermédiaires, pas seulement le résultat final. Si les valeurs intermédiaires correspondent à chaque étape, la différence finale est purement un artéfact de virgule flottante.

Types de données différents. Si un système stocke des valeurs en FLOAT64 et l’autre en NUMERIC(38,9), les représentations diffèrent fondamentalement. Les types NUMERIC utilisent l’arithmétique décimale à point fixe et ne souffrent pas des mêmes problèmes de précision que les types à virgule flottante. Lors d’une migration entre systèmes de types, castez explicitement les deux côtés vers le même type avant de comparer :

CAST(revenue__amount AS NUMERIC) AS revenue__amount

NaN et Infinity. Certains entrepôts (BigQuery, Snowflake) prennent en charge NaN et Infinity comme valeurs float spéciales. Ceux-ci ne sont pas égaux à eux-mêmes en SQL standard (NaN != NaN). Si vos données peuvent contenir ces valeurs, gérez-les explicitement :

CASE
  WHEN IS_NAN(amount) THEN NULL
  WHEN IS_INF(amount) THEN NULL
  ELSE ROUND(amount, 2)
END AS amount

Documenter les différences acceptables

Lors de la migration de systèmes, une équivalence numérique parfaite est parfois impossible. L’ancien système et le nouveau système appliquent des opérations dans des ordres différents, utilisent des types intermédiaires différents, ou arrondissent à des étapes différentes. Les différences résultantes peuvent être techniquement correctes dans les deux systèmes.

La bonne réponse n’est pas de supprimer la comparaison. C’est de documenter explicitement les différences connues. Créez un journal de validation de migration listant chaque modèle, son pourcentage de correspondance, la nature des éventuelles différences, et si ces différences sont acceptées. Une correspondance à 99,9997% où les différences restantes sont toutes des artéfacts en virgule flottante sous le centime est un succès. Une correspondance à 99,9997% non documentée est un risque — vous ne savez pas si ces différences sont des artéfacts de précision ou de vrais bugs jusqu’à ce que vous investigiez.

Cette documentation vous protège aussi après la migration. Lorsque quelqu’un demande « pourquoi ce nombre a-t-il changé de 0,01 € par rapport à l’ancien système », vous avez une réponse documentée au lieu d’une session de débogage.

Notes spécifiques aux plateformes

BigQuery : Utilise FLOAT64 (double précision IEEE 754) et NUMERIC (38 chiffres, 9 décimales). Préférez NUMERIC pour les calculs financiers. BIGNUMERIC s’étend à 76 chiffres avec 38 décimales pour les besoins de précision extrême.

Snowflake : Le type NUMBER est exact (jusqu’à 38 chiffres). FLOAT est double précision IEEE 754. Le NUMBER de Snowflake avec scale 0 est un entier ; avec scale > 0 c’est un décimal à point fixe.

Redshift : DECIMAL/NUMERIC est exact. FLOAT/REAL/DOUBLE PRECISION sont IEEE 754. Les fonctions d’agrégation APPROXIMATE utilisent des algorithmes différents des agrégats exacts et produiront des résultats différents.

Pour les migrations cross-plateformes, vérifiez toujours si vous comparez des types exacts à des types exacts ou des virgules flottantes à des virgules flottantes. Comparer NUMERIC à FLOAT sans cast explicite est la source la plus courante de faux désaccords.