Spark supporte désormais les CTEs recursives

Cas d’usage – extraction du data lineage 

Prérequis : Databricks runtime 17.0 (June 2025 – Link), Databricks Unity catalog, accès aux tables system access. 

Introduction 

L’arrivée de Spark 4.0 marque un jalon important dans l’évolution du moteur Spark et amène son lot de nouveautés, parmi lesquelles la possibilité de faire des requêtes SQL récursives. 

Celles-ci ont plusieurs avantages : elles permettent de rendre vos scripts SQL plus lisibles, efficients, et sont un atout considérable pour vos projets analytics. Afin d’illustrer cette fonctionnalité et les éléments à prendre en compte au moment de son implémentation, nous allons partir d’un cas d’usage concret : l’extraction complète (c’est à dire avec les colonnes) du data lineage pour une table spécifique, à l’aide de la table système 

system.access.column_lineage, accessible depuis Unity Catalog (pour les workspaces où celui-ci est activé). 

Pourquoi un tel cas d’usage ? 

Simplement parce que les fonctionnalités de data lineage proposées par Databricks rencontrent aujourd’hui certaines limites. Pour bien comprendre, rappelons brièvement que le data lineage est une fonctionnalité d’Unity Catalog qui permet notamment de suivre le cycle de vie des données en traçant leurs origines, les transformations appliquées et leurs utilisations – bien sûr, tout cela de manière automatisée. 

Plusieurs outils sont ainsi proposés par Databricks pour exploiter le lineage 

Deux tables système : 

system.access.column_lineage (Lien) : permet de tracer les lectures/écritures au niveau des colonnes. 

  system.access.table_lineage (Lien): permet de tracer les lectures/écritures au niveau des tables. 

Le « Catalog explorer » – Interface utilisateur 

Un onglet »Lineage » est accessible lors de la sélection d’une table depuis le Unity Catalog. 

Une vue graphe du data lineage en sélectionnant une table. Une API REST. 

À ce jour, les limitations identifiées sont les suivantes 

Les tables système ne fournissent que les dépendances en amont et en aval dites directes (c’est-à-dire sous la forme source → target). Pour obtenir l’intégralité des dépendances, il est nécessaire d’exécuter plusieurs requêtes récursives. 

Les filtres et colonnes utilisés dans les jointures ne sont pas capturés si les colonnes sont absentes de la table finale. Cela est dû à l’absence de lien direct entre une colonne source et une colonne cible, ce qui peut entraîner une perte de visibilité sur la logique de transformation. 

La périodicité retenue pour la conservation des données de lineage est limitée à un an. 

Il n’y a pas de traçage des opérations de DELETE et UPDATE. Si un champ est renommé ou supprimé, il devient difficile à identifier dans les tables système sans une requête avancée. 

Ainsi, il peut être intéressant, dans certains cas, de disposer d’un outil permettant d’extraire le data lineage sur l’ensemble des dépendances d’une table spécifique. Le support des CTEs récursives (Common Table Expressions) va justement nous y aider. 

– Rappel rapide sur les CTE 

L’objectif de cet article n’est pas de faire une présentation exhaustive des CTEs récursives, mais voici les principales utilisations à garder en tête pour la suite : 

L’exploration des relations hiérarchiques. 

La construction d’arbres de dépendance, notamment de type parent-enfant (ex. : catégories). 

L’itération et la génération de suites numériques ou de séquences (par exemple, pour extraire chaque lettre d’un mot, etc.). 

Structure d’une CTE récursive 

Les CTEs récursives sont structurées en deux parties : 

Une partie d’initialisation, également appelée ancre (anchor) : C’est la requête initiale, définie à l’aide du mot-clé RECURSIVE et de la CTE. 

Une partie récursive : C’est la requête itérative qui réutilise la CTE et s’appuie sur un UNION ALL pour concaténer les résultats. 

– Cas d’usage : Extraction du data lineage 

L’objectif attendu est d’obtenir une table contenant l’ensemble du data lineage d’une table choisie, avec en ligne toutes les dépendances entre colonnes sources et colonnes cibles intervenant dans sa construction. 

Pour visualiser la profondeur de la dépendance, une colonne level est ajoutée, contenant des valeurs numériques : 

Une valeur de 1 indique qu’il faut remonter une table en amont de la table finale. Une valeur de 2 signifie qu’il faut remonter deux niveaux, et ainsi de suite. 

Essayons maintenant d’extraire l’ensemble des dernières dépendances pour une table donnée, et ce pour tous ses champs à l’aide des CTEs récursives. 

Une fois ces étapes réalisées, il s’agit maintenant de construire l’ancre (anchor) de la CTE récursive. 

Construction de l’ancre 

Rien de particulièrement complexe ici, si ce n’est l’application de trois filtres essentiels : Sur la table choisie (cible de l’analyse). 

Sur la dernière version de la table (grâce au timestamp le plus récent du lineage). 

Sur la liste des colonnes (utilisée ensuite dans la phase récursive pour effectuer la jointure). 

Et l’initialisation de la colonne level. 

Construction de la partie récursive 

Pour construire la partie récursive de la CTE, deux jointures internes (INNER JOIN) sont nécessaires : 

Sur lineage_cte : pour relier chaque colonne cible à sa colonne source précédente, et ainsi reconstruire la chaîne de transformation des données. 

Sur recent : pour filtrer les relations et ne conserver que les plus récentes par colonne et table cible, assurant ainsi un lineage à jour. 

À chaque itération, la valeur du champ level est incrémentée pour refléter la profondeur de la dépendance. 

La requête dans sa version finale : 

Deux paramètres sont proposés : 

MAX RECURSION LEVEL : Ce paramètre par défaut à 100, limite la profondeur de récursion générant une erreur « RECURSION_LEVEL_LIMIT_EXCEEDED » si celui-ci est dépassé : Lien 

WITH RECURSIVE recursive_cte(n) MAX RECURSION LEVEL 200 AS ( …) 

La taille du jeu de données retourné en résultat ne peut excéder la limite par défaut de 1 million de lignes, si celle-ci est dépassée, l’erreur suivante est levée RECURSION_ROW_LIMIT_EXCEEDED : Lien 

Conclusion 

Arrivés à l’issue de cet article, nous espérons avoir démontré l’utilité des CTEs récursives à travers un cas concret : l’extraction du data lineage d’une table. 

Au-delà de la mise à disposition d’un outil technique, notre objectif était également de souligner l’importance du data lineage dans Unity Catalog, et de vous permettre d’explorer les différentes fonctionnalités nativement intégrées. 

Par ailleurs, l’arrivée de Spark 4.0 et des nouvelles fonctionnalités SQL qu’il supporte marque une avancée significative vers la création d’une data platform unifiée. Nous vous encourageons vivement à découvrir les autres nouveautés, qui pourraient s’avérer précieuses dans vos projets data.