Accueil›Blog›Test technique DuckDB : SQL analytique in-process pour la data

Guide recrutement data

Test technique DuckDB : SQL analytique in-process pour la data

DuckDB est devenu l outil de référence pour l analyse locale sur des fichiers Parquet ou des DataFrames pandas. En entretien, on évalue la capacité à l utiliser comme moteur analytique puissant.

Data Builder·Juin 2025·6 min de lecture·Data Engineer · Data Scientist

Sommaire

Pourquoi DuckDB
SQL analytique avancé
Requêter des fichiers Parquet
Intégration pandas
Optimisation et performance
Cas d usage en production
Grille

1Pourquoi DuckDB en 2025

Question discriminante

Dans quels cas DuckDB est-il préférable à pandas ou Spark ?

DuckDB vs pandas — 10-100x plus rapide sur les agrégations et les jointures. Exécution multi-thread native, pas de GIL
DuckDB vs Spark — pas de cluster à gérer, démarrage instantané, SQL natif complet. Idéal pour les fichiers < 100GB
DuckDB vs BigQuery/Snowflake — gratuit, local, zéro latence réseau. Pour les analyses exploratoires avant de passer au cloud
Cas d usage typique — analyser des dumps Parquet, prototype de transformation avant dbt, remplacement de pandas pour les analyses lourdes

2SQL analytique avancé dans DuckDB

Question discriminante

Quelles fonctionnalités SQL avancées DuckDB supporte-t-il nativement ?

import duckdb

# DuckDB supporte tout le SQL analytique moderne
conn = duckdb.connect()

# PIVOT natif
result = conn.execute("""
    PIVOT orders
    ON region
    USING SUM(amount)
    GROUP BY YEAR(order_date)
""").df()

# UNPIVOT
conn.execute("""
    UNPIVOT wide_table
    ON (q1, q2, q3, q4)
    INTO NAME quarter VALUE revenue
""")

# LIST et MAP (types natifs)
conn.execute("""
    SELECT
        customer_id,
        LIST(product_id ORDER BY order_date) AS purchase_history,
        MAP(product_id, amount) AS amounts_by_product
    FROM orders
    GROUP BY customer_id
""")

# ASOF JOIN : jointure sur la valeur la plus proche
conn.execute("""
    SELECT o.*, p.price
    FROM orders o
    ASOF JOIN prices p
        ON o.product_id = p.product_id
        AND o.order_date >= p.effective_date
""")

3Requêter des fichiers Parquet directement

Question discriminante

Comment requêtez-vous des fichiers Parquet sans les charger en mémoire ?

import duckdb

# Requête directe sur des fichiers Parquet (glob supporté)
result = duckdb.sql("""
    SELECT
        region,
        YEAR(order_date) AS year,
        SUM(amount) AS revenue,
        COUNT(*) AS nb_orders
    FROM read_parquet('data/orders/year=*/month=*/*.parquet')
    WHERE order_date >= '2024-01-01'
    GROUP BY region, YEAR(order_date)
    ORDER BY revenue DESC
""").df()

# Predicate pushdown : DuckDB ne lit que les colonnes et lignes nécessaires
# Beaucoup plus efficace que pd.read_parquet() suivi d un filtre

# Créer une table virtuelle depuis Parquet
duckdb.execute("""
    CREATE VIEW orders_view AS
    SELECT * FROM read_parquet('data/orders/**/*.parquet')
""")

# Lire depuis S3 (nécessite le module httpfs)
duckdb.install_extension('httpfs')
duckdb.load_extension('httpfs')
duckdb.execute("SET s3_region='eu-west-1'")
result = duckdb.sql("SELECT * FROM 's3://mon-bucket/orders/*.parquet'").df()

4Intégration pandas : zero-copy avec Arrow

Question discriminante

Comment DuckDB communique-t-il avec pandas sans copier les données ?

import duckdb
import pandas as pd

df_orders = pd.read_csv('orders.csv')
df_customers = pd.read_csv('customers.csv')

# DuckDB peut requêter directement les DataFrames pandas
# Via Apache Arrow (zero-copy)
result = duckdb.sql("""
    SELECT
        c.customer_name,
        c.segment,
        SUM(o.amount) AS total_revenue,
        COUNT(*) AS nb_orders
    FROM df_orders o
    JOIN df_customers c ON o.customer_id = c.customer_id
    GROUP BY c.customer_name, c.segment
    ORDER BY total_revenue DESC
""").df()

# Convertir vers différents formats
result_arrow = duckdb.sql("SELECT * FROM df_orders").arrow()   # PyArrow
result_polars = duckdb.sql("SELECT * FROM df_orders").pl()     # Polars
result_numpy = duckdb.sql("SELECT amount FROM df_orders").numpy()

5Optimisation des performances DuckDB

Question discriminante

Quels sont les paramètres de configuration importants pour optimiser DuckDB ?

import duckdb

conn = duckdb.connect()

# Utiliser tous les coeurs disponibles
conn.execute("SET threads TO 8")

# Mémoire maximale (défaut : 80% de la RAM)
conn.execute("SET memory_limit = '16GB'")

# Spill to disk si nécessaire (pour les très gros datasets)
conn.execute("SET temp_directory = '/tmp/duckdb_spill'")

# Compression des résultats intermédiaires
conn.execute("SET intermediate_result_chunk_cardinality = 2048")

# Persistance : DuckDB peut aussi fonctionner en mode fichier
conn_persistent = duckdb.connect('analytics.duckdb')
conn_persistent.execute("""
    CREATE TABLE IF NOT EXISTS orders AS
    SELECT * FROM read_parquet('data/orders/*.parquet')
""")

6DuckDB en production : cas d usage

Question discriminante

Comment utilisez-vous DuckDB dans un pipeline de production ?

Script de transformation local — remplacer un script pandas lourd par DuckDB SQL. 10x moins de code, 100x plus rapide
Validation de données — vérifier la qualité d un dump Parquet avant de le charger dans BigQuery
Prototype de modèle dbt — tester la logique SQL localement avec DuckDB avant de déployer sur Snowflake
Analytics serverless — DuckDB + S3 comme stack analytics sans cluster. Coût quasi-nul pour des analyses ad-hoc
dbt avec DuckDB adapter — exécuter dbt localement avec DuckDB comme warehouse. Itérations ultra-rapides

import duckdb

conn = duckdb.connect()

# Lire directement depuis S3 sans telecharger
conn.execute("""
    INSTALL httpfs; LOAD httpfs;
    SET s3_region='eu-west-1';
    SET s3_access_key_id='KEY'; SET s3_secret_access_key='SECRET';
""")

result = conn.execute("""
    SELECT region, SUM(amount) as revenue, COUNT(*) as orders
    FROM read_parquet('s3://bucket/orders/year=2025/**/*.parquet')
    WHERE order_date >= '2025-01-01'
    GROUP BY region ORDER BY revenue DESC
""").df()

# DuckDB comme ETL local
conn.execute("CREATE TABLE orders AS SELECT * FROM read_csv_auto('orders.csv')")
conn.execute("COPY (SELECT * FROM orders WHERE amount > 100) TO 'filtered.parquet' (FORMAT PARQUET)")

# MotherDuck : DuckDB cloud partage
import duckdb
conn = duckdb.connect('md:mon_projet?motherduck_token=TOKEN')

OLAP columnar vectorise - DuckDB utilise un moteur vectorise en memoire. 10-100x plus rapide que pandas sur les agregations analytiques, sans JVM ni cluster
S3/GCS direct - lire des Parquet sur S3 sans telecharger. Predicate et projection pushdown : tire uniquement les colonnes et partitions necessaires
MotherDuck - DuckDB manage dans le cloud. Partager requetes et donnees sans infrastructure. Hybrid local/cloud
Cas d usage parfaits - exploration locale, ETL leger (moins de 50GB), tests de pipelines, remplacement de pandas pour l analytique, SQL dans notebooks
Limites - mono-noeud (pas de distribue), pas adapte aux workloads haute volumetrie Spark, pas de streaming natif

7Grille par niveau

Niveau	Maitrise	Signal GO	NO-GO
Confirmé	SQL DuckDB basique, read_parquet, intégration pandas	Remplace pandas par DuckDB pour les agrégations, utilise read_parquet	N a jamais utilisé DuckDB
Senior	PIVOT/UNPIVOT natif, ASOF JOIN, S3, optimisation	Utilise DuckDB en production, configure la mémoire et les threads	Ne sait pas que DuckDB peut lire directement depuis S3

1Why DuckDB in 2025

Discriminating question

In which cases is DuckDB preferable to pandas or Spark?

DuckDB vs pandas — 10-100x faster on aggregations and joins. Native multi-thread execution, no GIL
DuckDB vs Spark — no cluster to manage, instant startup, full native SQL. Ideal for files < 100GB
DuckDB vs BigQuery/Snowflake — free, local, zero network latency. For exploratory analysis before moving to the cloud
Typical use case — analyzing Parquet dumps, transformation prototype before dbt, pandas replacement for heavy analytics

2Advanced analytical SQL in DuckDB

Discriminating question

What advanced SQL features does DuckDB support natively?

import duckdb

# DuckDB supports all modern analytical SQL
conn = duckdb.connect()

# Native PIVOT
result = conn.execute("""
    PIVOT orders
    ON region
    USING SUM(amount)
    GROUP BY YEAR(order_date)
""").df()

# UNPIVOT
conn.execute("""
    UNPIVOT wide_table
    ON (q1, q2, q3, q4)
    INTO NAME quarter VALUE revenue
""")

# LIST and MAP (native types)
conn.execute("""
    SELECT
        customer_id,
        LIST(product_id ORDER BY order_date) AS purchase_history,
        MAP(product_id, amount) AS amounts_by_product
    FROM orders
    GROUP BY customer_id
""")

# ASOF JOIN : join on the closest value
conn.execute("""
    SELECT o.*, p.price
    FROM orders o
    ASOF JOIN prices p
        ON o.product_id = p.product_id
        AND o.order_date >= p.effective_date
""")

3Querying Parquet files directly

Discriminating question

How do you query Parquet files without loading them into memory?

import duckdb

# Direct query on Parquet files (glob supported)
result = duckdb.sql("""
    SELECT
        region,
        YEAR(order_date) AS year,
        SUM(amount) AS revenue,
        COUNT(*) AS nb_orders
    FROM read_parquet('data/orders/year=*/month=*/*.parquet')
    WHERE order_date >= '2024-01-01'
    GROUP BY region, YEAR(order_date)
    ORDER BY revenue DESC
""").df()

# Predicate pushdown : DuckDB only reads the necessary columns and rows
# Much more efficient than pd.read_parquet() followed by a filter

# Create a virtual table from Parquet
duckdb.execute("""
    CREATE VIEW orders_view AS
    SELECT * FROM read_parquet('data/orders/**/*.parquet')
""")

# Read from S3 (requires httpfs module)
duckdb.install_extension('httpfs')
duckdb.load_extension('httpfs')
duckdb.execute("SET s3_region='eu-west-1'")
result = duckdb.sql("SELECT * FROM 's3://my-bucket/orders/*.parquet'").df()

4pandas integration: zero-copy with Arrow

Discriminating question

How does DuckDB communicate with pandas without copying data?

import duckdb
import pandas as pd

df_orders = pd.read_csv('orders.csv')
df_customers = pd.read_csv('customers.csv')

# DuckDB can directly query pandas DataFrames
# Via Apache Arrow (zero-copy)
result = duckdb.sql("""
    SELECT
        c.customer_name,
        c.segment,
        SUM(o.amount) AS total_revenue,
        COUNT(*) AS nb_orders
    FROM df_orders o
    JOIN df_customers c ON o.customer_id = c.customer_id
    GROUP BY c.customer_name, c.segment
    ORDER BY total_revenue DESC
""").df()

# Convert to different formats
result_arrow = duckdb.sql("SELECT * FROM df_orders").arrow()   # PyArrow
result_polars = duckdb.sql("SELECT * FROM df_orders").pl()     # Polars
result_numpy = duckdb.sql("SELECT amount FROM df_orders").numpy()

5DuckDB performance optimization

Discriminating question

What are the important configuration parameters for optimizing DuckDB?

import duckdb

conn = duckdb.connect()

# Use all available cores
conn.execute("SET threads TO 8")

# Maximum memory (default: 80% of RAM)
conn.execute("SET memory_limit = '16GB'")

# Spill to disk if necessary (for very large datasets)
conn.execute("SET temp_directory = '/tmp/duckdb_spill'")

# Intermediate result compression
conn.execute("SET intermediate_result_chunk_cardinality = 2048")

# Persistence: DuckDB can also operate in file mode
conn_persistent = duckdb.connect('analytics.duckdb')
conn_persistent.execute("""
    CREATE TABLE IF NOT EXISTS orders AS
    SELECT * FROM read_parquet('data/orders/*.parquet')
""")

6DuckDB in production: use cases

Discriminating question

How do you use DuckDB in a production pipeline?

Local transformation script — replace a heavy pandas script with DuckDB SQL. 10x less code, 100x faster
Data validation — check the quality of a Parquet dump before loading it into BigQuery
dbt model prototype — test SQL logic locally with DuckDB before deploying to Snowflake
Serverless analytics — DuckDB + S3 as an analytics stack without a cluster. Near-zero cost for ad-hoc analysis
dbt with DuckDB adapter — run dbt locally with DuckDB as the warehouse. Ultra-fast iterations

import duckdb

conn = duckdb.connect()

# Read directly from S3 without downloading
conn.execute("""
    INSTALL httpfs; LOAD httpfs;
    SET s3_region='eu-west-1';
    SET s3_access_key_id='KEY'; SET s3_secret_access_key='SECRET';
""")

result = conn.execute("""
    SELECT region, SUM(amount) as revenue, COUNT(*) as orders
    FROM read_parquet('s3://bucket/orders/year=2025/**/*.parquet')
    WHERE order_date >= '2025-01-01'
    GROUP BY region ORDER BY revenue DESC
""").df()

# DuckDB as local ETL
conn.execute("CREATE TABLE orders AS SELECT * FROM read_csv_auto('orders.csv')")
conn.execute("COPY (SELECT * FROM orders WHERE amount > 100) TO 'filtered.parquet' (FORMAT PARQUET)")

# MotherDuck : DuckDB cloud sharing
import duckdb
conn = duckdb.connect('md:my_project?motherduck_token=TOKEN')

Vectorized columnar OLAP - DuckDB uses a vectorized in-memory engine. 10-100x faster than pandas on analytical aggregations, without JVM or cluster
S3/GCS direct - read Parquet on S3 without downloading. Predicate and projection pushdown: pulls only the necessary columns and partitions
MotherDuck - DuckDB managed in the cloud. Share queries and data without infrastructure. Hybrid local/cloud
Perfect use cases - local exploration, lightweight ETL (less than 50GB), pipeline testing, pandas replacement for analytics, SQL in notebooks
Limitations - single-node (no distributed), not suited for high-volume Spark workloads, no native streaming

7Level grid

Level	Mastery	GO signal	NO-GO
Confirmed	Basic DuckDB SQL, read_parquet, pandas integration	Replaces pandas with DuckDB for aggregations, uses read_parquet	Has never used DuckDB
Senior	Native PIVOT/UNPIVOT, ASOF JOIN, S3, optimization	Uses DuckDB in production, configures memory and threads	Does not know DuckDB can read directly from S3

Vous recrutez un Data Engineer ou Data Scientist ?

Premier entretien gratuit. Rapport GO/NO-GO sous 48h.

Tester gratuitement Reserver un appel