Combiner DBT + DuckDB avec Azure Blob Storage

Introduction

Dans le paysage actuel de la data, de nombreuses entreprises sont équipées de Datalakes et adoptent une architecture de “Modern Data Warehouse”.

Dans ce schéma, les transformations de données sont réalisées directement dans un Datalake, ne laissant qu’une couche ‘Gold’ raffinée, exposée dans le Data Warehouse.

Historiquement, ces transformations étaient souvent réalisées avec Spark, mais avec la montée en puissance du SQL et des outils tels que DBT, une nouvelle approche est possible.

• DBT nous offre tous les avantages du Software Engineering en nous permettant d’appliquer ces principes à des pipelines de transformation de données, rédigés en SQL.
• Pour compléter ce tableau, DuckDB est une base OLAP in-memory en plein essor, reconnue pour son efficacité dans la manipulation de fichiers plats.

Dans cet article, nous allons explorer comment l’intégration de ces outils peut permettre à des entités de rapidement mettre en place une architecture “Best of Breed”.

Comprendre DBT, DuckDB et Azure Blob Storage

DUCKDB :

DuckDB est un système de gestion de base de données OLAP, spécialement conçu pour fonctionner in-memory. En 2022, il a connu une montée en flèche de sa notoriété, et il est souvent repris dans les différents benchmarks comme une alternative à Pandas ou Spark. Son moteur optimisé peut lire une variété de formats de fichiers, y compris Parquet, CSV, etc.

DBT :

DBT (Data Build Tool), quant à lui, est un framework SQL écrit en Python. Il permet d’appliquer tous les bons principes du Software Engineering sur les pipelines de données, comme la modularisation et les tests de qualité de la donnée. Il permet également de générer de la documentation.

En France, DBT est de plus en plus présent dans les entreprises, bien que des alternatives comme Dataform et SQLMESH soient également disponibles.

Configurer DBT et DuckDB avec Azure Blob Storage

Création du storage account sur Azure Blob Storage et des containers Bronze, Silver et Gold

Dans Azure, nous allons créer un storage account qui sera notre Datalake :

Lorsque que le storage account est créé, nous créons 3 containers représentant les 3 couches de transformations : Bronze (raw), Silver (cleansed) et Gold (business) (Architecture médaillon)

Configuration de DBT-DUCKDB

Dans l’introduction, nous avons vu ce qu’est DBT et DUCKDB. Un package Python nommé dbt-duckdb nous permet de bénéficier de ces 2 outils.

1. Installer dbt-duckdb : https://github.com/jwills/dbt-duckdb
2. Lancer la commande ‘dbt init’

Vous devez choisir duckdb. On vous demandera également de choisir un nom de projet. J’ai renseigné ‘pipeline_datalake’.

3. Votre projet sur VSCODE devrait ressembler à ceci :

DBT crée tout le nécessaire : un répertoire ‘logs’ ainsi qu’un répertoire ‘pipeline_datalake’

4. Créer un fichier profiles.yml dans le répertoire du projet dbt, et ajouter les éléments ci-dessous :

default:
  outputs:
    dev:
      type: duckdb
      path: memory
      extensions:
        - httpfs
        - parquet
      filesystems:
        - fs: abfs
          account_name: "datalakeduckdbdbt"
          account_key: "YOUR_ACCESS_KEY"
  target: dev

Ce fichier sera lu par DBT afin de se connecter à notre Datalake. Nous avons renseigné le nom du storage account ainsi que l’access key.

Celle-ci se trouve ici :

NLDR : Afin d’éviter de laisser la clé dans la configuration, vous pouvez utiliser une variable d’environnement.

5. Ajouter le on-run-start dans le fichier dbt_project.yml

on-run-start:
  - "{{ register_upstream_external_models() }}"

Note : Lorsque vous utilisez Duckdb in-memory, certaines exécutions de DBT peuvent échouer si elles font appel à des modèles dépendant de tables externes. Pour éviter cela, vous devez activer l’enregistrement automatique de ces tables externes au début de chaque exécution de DBT grâce à la macro ‘register_upstream_external_models’.

6. Avant de tester, nous devons installer les packages fsspec et adlfs

pip install -r requirements.txt

7. Lancer la commande ‘dbt debug’ afin de vérifier que tout est ok :

Note : N’oubliez pas de vous placer dans votre répertoire dbt

Mise en situation

Vous souhaitez prototyper des transformations avant de les passer à l’échelle avec Snowflake et vous avez besoin d’une architecture qui reproduit la configuration cible pour former votre équipe de Data Engineers et démontrer la valeur de ce type de solution.

Présentation des données

Nous avons créé deux datasets fictifs pour représenter nos sources de données. Le premier, “order_data”, contient des informations détaillées sur les commandes passées par les clients.

Le second, “customer_data”, contient des informations sur les clients eux-mêmes.

Objectif

Notre objectif est d’automatiser et d’industrialiser la transformation de ces données brutes afin de créer des datasets facilement exploitables par le métier. Grâce à ces données, les utilisateurs finaux pourront prendre de meilleures décisions et créer de la valeur.

Architecture du pipeline

Vue d’ensemble de l’architecture du pipeline de données

Dans ce scénario, un utilisateur (ou une application) dépose deux fichiers CSV qui contiennent respectivement les données des commandes et des clients dans le conteneur ‘Bronze’.

Ensuite, grâce à l’utilisation de DuckDB et DBT, des scripts de transformation rédigés en SQL seront utilisés pour nettoyer et préparer les données à chaque étape du pipeline, c’est-à-dire entre les conteneurs ‘Bronze’ et ‘Silver’, puis entre ‘Silver’ et ‘Gold’.

Enfin, les données transformées et prêtes à l’emploi seront accessibles dans le conteneur ‘Gold’ pour être utilisées, par exemple, dans un rapport de visualisation de données.

Rédaction des scripts et tests

Passons à la rédaction des scripts et aux tests. Pour ce faire, nous créons deux dossiers, ‘Silver’ et ‘Gold’, dans ‘pipeline_datalake/models’. Ces dossiers hébergeront nos scripts SQL de transformation.

Configuration du fichier sources.yml

sources:
  - name: bronze
    meta:
      external_location: "abfs://{your_container_name}@{your_storage_account_name}.blob.core.windows.net/{name}.csv"
    tables:
      - name: customers
      - name: orders
  - name: silver
    meta:
      external_location: "abfs://{your_container_name}@{your_storage_account_name}.blob.core.windows.net/{name}.parquet"
    tables:
      - name: silver_customer
      - name: silver_order

Nous configurons ensuite le fichier ‘sources.yml’ dans ‘pipeline_datalake/models’ pour indiquer à DBT où trouver les fichiers dans notre Datalake. En spécifiant le chemin du fichier à partir du compte de stockage et en renseignant les paramètres de ‘tables’, DBT remplace automatiquement le {name} par l’argument choisi.

Bronze to Silver :

Ce fichier se trouve dans pipeline_datalake/models/silver/

{{config(materialized='external', 
location='abfs://silver@datalakeduckdbdbt.blob.core.windows.net/silver_customer.parquet')}}

WITH   
CUSTOMERS AS (
    SELECT * 
    FROM {{source('bronze', 'customers')}}),
RENAME_AND_ENFORCE_SCHEMA AS (
    SELECT 
    CAST(CustomerId as INTEGER) as customer_id,
    CAST(FirstName as VARCHAR) as first_name,
    CAST(LastName as VARCHAR) as last_name, 
    CAST(Email  as VARCHAR) as email,
    CAST(SignUpDate as DATE) as sign_up_date
FROM CUSTOMERS
),
REMOVE_DUPLICATES AS (   
    SELECT *
    FROM RENAME_AND_ENFORCE_SCHEMA
    QUALIFY row_number() over (partition by customer_id order by sign_up_date desc) = 1),
ADD_TECHNICAL_FIELD AS (   
    SELECT *,
    today() as _ingestion_time
    FROM REMOVE_DUPLICATES)

SELECT * 
FROM ADD_TECHNICAL_FIELD

Ce fichier se trouve dans pipeline_datalake/models/silver/

{{config(materialized='external', 
location='abfs://silver@datalakeduckdbdbt.blob.core.windows.net/silver_order.parquet')}}

WITH   
ORDERS AS (
    SELECT * 
    FROM {{source('bronze', 'orders')}}),
RENAME_AND_ENFORCE_SCHEMA AS (
    SELECT 
    CAST(OrderId as INTEGER) as order_id,
    CAST(CustomerId as INTEGER) as customer_id,
    CAST(ProductId as INTEGER) as product_id, 
    CAST(Quantity as INTEGER) as quantity,
    CAST(OrderDate as DATE) as order_date
FROM ORDERS
),
REMOVE_DUPLICATES AS (   
    SELECT *
    FROM RENAME_AND_ENFORCE_SCHEMA
    QUALIFY row_number() over (partition by order_id order by order_date desc) = 1),
ADD_TECHNICAL_FIELD AS (   
    SELECT *,
    today() as _ingestion_time
    FROM REMOVE_DUPLICATES)
SELECT * 
FROM ADD_TECHNICAL_FIELD

Concernant la transformation de ‘Bronze’ à ‘Silver’, nos deux scripts déposés dans ‘silver_customer.sql’ et ‘silver_order.sql’ nettoient et préparent nos données. Ces transformations appliquées sur les deux tables permettent d’extraire les colonnes souhaitées, d’imposer le schéma, d’éliminer les doublons et d’ajouter un champ technique d’ingestion.

En définissant l’argument ‘materialized’ comme ‘external’, nous informons DBT que l’écriture se fait sur une source externe, et non dans une base de données DuckDB. De plus, le paramètre ‘location’ nous permet de spécifier le chemin du fichier où nous souhaitons écrire.

Enfin, l’argument source (‘bronze’, ‘orders’) spécifie à DBT de se référer au fichier ‘sources.yml’, d’aller dans le répertoire ‘bronze’ et de remplacer le ‘{name}’ par ‘orders’.

Silver to Gold :

Enfin, nous créons notre fichier ‘Gold’, qui répond à notre besoin client. Ce fichier est basé sur les données nettoyées Silver.

Ce fichier se trouve dans pipeline_datalake/models/gold/

{{config(materialized='external', 
location='abfs://gold@datalakeduckdbdbt.blob.core.windows.net/gold_nb_orders_by_customer.parquet')}}

WITH   
silver_customer AS (
    SELECT * 
    FROM {{source('silver', 'silver_customer')}}),
silver_order AS (
    SELECT * 
    FROM {{source('silver', 'silver_order')}})

SELECT 
    customer_id, 
    first_name, 
    last_name, 
    email, 
    count(order_id) as nb_orders
FROM silver_customer as customer
INNER JOIN silver_order using(customer_id)
WHERE 
    email is not null
    and first_name is not null
    and last_name  is not null
GROUP BY customer_id, first_name, last_name, email

Lancement des scripts :

La commande ‘dbt run’ nous permet de générer nos fichiers dans les différents containers.

Tests :

Nous pouvons également tester nos données avec DBT. Pour cela, nous créons un fichier ‘schema.yml’ dans le dossier silver et un autre dans le dossier gold.

Ce fichier se trouve dans pipeline_datalake/models/gold/

version: 2

models:
  - name: gold_nb_orders_by_customer
    description: "Gold table"
    columns:
      - name: customer_id
        description: "The primary key for this table"
        tests:
          - unique
          - not_null
      - name: first_name
        description: "First name"
        tests:
          - not_null
      - name: last_name
        description: "Last name"
        tests:
          - not_null
      - name: email
        description: "Email"
        tests:
          - not_null

On vérifie par exemple que la clé primaire ‘customer_id’ dans le container Gold est unique et non null

Puis, on exécute la commande ‘dbt test’ afin de lancer les tests sur notre fichier :

Documentation :

Enfin, nous générons la doc afin de formaliser notre travail grâce à la commande ‘dbt docs generate’ :

Nous l’affichons en local avec la commande ‘dbt docs serve’ :

Conclusion

Nous avons démontré qu’avec DBT, DuckDB et Azure Blob Storage, nous pouvons très simplement générer des pipelines en SQL sur notre Datalake. Ces outils de pointe nous permettent de transformer des données stockées en format plat, d’accélérer le développement de pipelines de données et de préparer le terrain pour une transition fluide vers un Data Warehouse.

Concernant l’industrialisation, envisager d’utiliser des technologies comme Azure Container Instances (ACI) ou Azure Functions pour l’exécution, et Azure Data Factory pour l’orchestration, pourrait être une prochaine étape logique.