Construire un pipeline CSV avec DuckDB

Transformez un CSV en table DuckDB persistante et publiez le pipeline.

Introduction

30 Second Summary

Un fichier de ventes est facile à consulter une fois. Le vrai défi commence quand il faut rejouer exactement la même transformation et partager une preuve fiable du résultat.

Dans ce projet, vous allez construire un pipeline en Python qui transforme un fichier CSV en table DuckDB persistante. Vous utiliserez SQL pour vérifier les lignes avant de publier les sources sur GitHub.

What You'll Build

Vous lancerez votre pipeline pour voir cinq ventes chargées dans une table ordonnée que vous pourrez rouvrir depuis un second processus.

By the end of this project, you'll have:

  • Une ingestion répétable qui transforme data/sales.csv en table dans data/sales.duckdb.
  • Une preuve de persistance où un second script rouvre la base et retrouve les mêmes cinq ventes.
  • Un dépôt GitHub reproductible qui contient le code et les données d'exemple sans inclure la base locale générée.
  • Secret Mission: Combiner plusieurs fichiers CSV dans une seule table tout en conservant le nom du fichier source pour chaque ligne.

Are there any prerequisites?

Votre environnement Windows doit déjà disposer de Git, de Python 3.10 ou plus récent et de Visual Studio Code.

Votre dépôt local doit déjà pointer vers GitHub avec le remote origin.

Before We Start

Avant tout travail pratique, ce point de départ fixe la transformation de data/sales.csv vers data/sales.duckdb. Votre pipeline devra permettre à un processus Python séparé de rouvrir les données de ventes persistantes.

Prepare an Isolated Python Environment

A reproducible pipeline needs the same database library on every machine. A global DuckDB installation can drift away from the version recorded by this repository.

A virtual environment keeps this project's Python dependency isolated. The requirements.txt pin makes DuckDB version 1.5.6 repeatable.

In this step, get ready to:
  • Verify your local Python and Git toolchain.
  • Create the project structure with pinned dependency controls.
  • Build a verified DuckDB environment inside .venv.
Verify your local toolchain

Your existing repository gives every tool a shared starting point. Opening it as a Visual Studio Code workspace keeps Windows PowerShell at the repository root.

  • Press the Windows key to open Windows Search.
  • Type Visual Studio Code into the search bar.
  • Press Enter to launch Visual Studio Code.
  • Press Ctrl+K Ctrl+O to open the folder picker.
  • Select your existing local Git repository folder.
  • Click Select Folder.
  • Confirm that you trust the repository if the Workspace Trust dialog appears.

Why open the repository folder?

Visual Studio Code treats the open folder as a workspace. Its integrated terminal starts at that folder's root.

  • Press Ctrl+` to open the integrated terminal.
  • Select Windows PowerShell from the terminal dropdown when another shell opens.

The terminal starts at the workspace root. That location lets each command find the repository files without extra paths.

DuckDB 1.5.6 requires Python 3.10.0 or newer. Python install manager 26.3 provides the upgrade path when your version is older or missing.

  • Check your active Python version by running this command:
python --version

What does this check?

The command prints the Python version that Windows PowerShell currently uses. That version determines whether DuckDB can be installed.

✔️ I see Python 3.10 or newer

Your output starts with Python followed by 3.10 or a newer version. Your runtime satisfies DuckDB's requirement.

ⓧ I see an older Python version

The installed runtime is below DuckDB's minimum version. Install Python 3.14 through the current Windows manager.

  • Visit the official Python releases page for Windows.
  • Download Python install manager 26.3.
  • Run the downloaded installer.
  • Install Python 3.14 with the first command below:
  • Recheck the active Python version with the second command below:
py install 3.14
python --version

What do these commands do?

  • The first command asks Python install manager to install the Python 3.14 runtime.
  • The second command confirms which Python version Windows PowerShell now resolves.

Still seeing the older version?

Close the current terminal after the installation. Open a new Windows PowerShell terminal so it can load the updated Python configuration.

Run the version check again. Help me check why Windows PowerShell still uses my older Python version.

ⓧ Python is not found

Windows PowerShell cannot currently find a Python runtime. Install the official Windows manager before adding Python 3.14.

  • Visit the official Python releases page for Windows.
  • Download Python install manager 26.3.
  • Run the downloaded installer.
  • Install Python 3.14 with the first command below:
  • Confirm that Python is available with the second command below:
py install 3.14
python --version

What do these commands do?

  • The first command installs the Python 3.14 runtime through Python install manager.
  • The second command checks whether Windows PowerShell can now find Python.

Python still unavailable?

Close the current terminal after the installation. Open a new Windows PowerShell terminal before checking again.

If the command is still unavailable, help me troubleshoot my Python installation on Windows.

The Python check confirms the runtime. A Git status check now confirms that the terminal is inside your existing repository.

  • Check that Git recognizes the repository by running this command:
git status

What does this check?

Git reports the current branch plus the state of tracked files. A repository status confirms that PowerShell is working from the correct folder.

That is the toolchain check done. Both commands now confirm a supported Python runtime inside a recognized Git repository.

Git does not recognize the repository?

Check the folder name shown at the top of the Visual Studio Code Explorer. Reopen the existing repository folder if you selected its parent folder.

Still stuck? Help me confirm whether my PowerShell terminal is at the root of my Git repository.

Prepare the project structure

The data folder holds CSV input plus generated database files. The src folder keeps the pipeline scripts separate.

  • Select the repository's top-level folder in the Explorer sidebar.
  • Select the New Folder icon.
  • Enter data as the folder name.
  • Select the repository's top-level folder again.
  • Select the New Folder icon.
  • Enter src as the folder name.

You'll see data plus src directly beneath the repository folder in Explorer.

The dependency file records the exact DuckDB package release. That pin lets another machine rebuild the same environment.

  • Select the repository's top-level folder in Explorer.
  • Select the New File icon.
  • Enter requirements.txt as the file name.

You'll see a blank requirements.txt tab in the editor.

  • Add the pinned dependency by pasting this line into requirements.txt:
duckdb==1.5.6

What does this dependency pin do?

  • The package name tells Python to install DuckDB.
  • The double equals sign locks the installation to version 1.5.6.
  • Press Ctrl+S to save requirements.txt.
  • Confirm that requirements.txt displays exactly one dependency line.

Dependency line looks different?

Remove spaces around the double equals sign. Keep the package name lowercase.

Need another pair of eyes? Help me check my DuckDB dependency pin in requirements.txt.

✔️ Awesome, I've got everything!

Your dependency file is saved with the required DuckDB pin.

ⓧ I'd like to double check the full code

  • Compare your complete requirements.txt file with this reference:
duckdb==1.5.6

What should match?

The file contains one package pin. The final blank line keeps the text file neatly terminated.

The local environment plus generated database files can always be rebuilt. The .gitignore file keeps those machine-specific artifacts out of version control.

  • Select the repository's top-level folder in Explorer.
  • Select the New File icon.
  • Enter .gitignore as the file name.

You'll see a blank .gitignore tab in the editor.

  • Add the project exclusions by pasting this content into .gitignore:
.venv/
__pycache__/
*.py[cod]
data/*.duckdb
data/*.wal
data/*.tmp/

What do these exclusions protect?

  • The .venv/ entry excludes the disposable virtual environment.
  • The Python cache patterns exclude generated bytecode files.
  • The data patterns exclude DuckDB database artifacts.
  • The source CSV remains available for Git to track.
  • Press Ctrl+S to save .gitignore.
  • Confirm that .gitignore displays six exclusion patterns.

Missing an exclusion pattern?

Check that every pattern occupies its own line. Keep the trailing slash on directory patterns.

If the patterns still look wrong, help me compare my .gitignore with the required project exclusions.

✔️ Awesome, I've got everything!

Your source-control exclusions now match the project's generated artifacts.

ⓧ I'd like to double check the full code

  • Compare your complete .gitignore file with this reference:
.venv/
__pycache__/
*.py[cod]
data/*.duckdb
data/*.wal
data/*.tmp/

What should match?

All six patterns must appear in this order. The final blank line completes the file.

Build a verified DuckDB environment

The virtual environment gives this repository its own Python package location. Calling its interpreter directly also avoids PowerShell activation-policy issues.

  • Create the local virtual environment by running this command:
python -m venv .venv

What does this command create?

Python creates an isolated environment inside .venv. Its Windows interpreter lives under the Scripts subdirectory.

  • Expand .venv in the Visual Studio Code Explorer.

You'll see a Scripts folder inside .venv. That folder contains the isolated Python interpreter.

Virtual environment missing?

Confirm that the terminal remained at the repository root. Check that your Python version is still 3.10 or newer.

Still not seeing .venv? Help me troubleshoot why Python did not create my virtual environment.

The environment starts without project packages. The pinned requirements file now supplies its DuckDB dependency.

  • Install the pinned dependency with the isolated interpreter by running this command:
.\.venv\Scripts\python.exe -m pip install -r requirements.txt

What does this installation command do?

  • The explicit interpreter path keeps the installation inside .venv.
  • The pip command reads the package pin from requirements.txt.
  • The environment receives DuckDB version 1.5.6.
  • Read the final lines printed by the installation command.

You'll see the installation finish without a dependency error. The isolated interpreter can now load DuckDB.

Installation did not finish?

Check that your internet connection is available. Confirm that requirements.txt is saved at the repository root.

Need help with the failed installation? Help me diagnose why pip cannot install my pinned DuckDB dependency.

Before you run the final check, do you expect the isolated interpreter to import DuckDB and print a query result?

  • Test the isolated DuckDB installation by running this command:
.\.venv\Scripts\python.exe -c "import duckdb; duckdb.sql('SELECT 42').show()"

What does this smoke test prove?

  • The interpreter path proves that the command runs inside .venv.
  • The import proves that DuckDB is installed in that environment.
  • The SQL query proves that DuckDB can execute a query plus display its result.

You'll see a small table containing 42. That is the environment complete: your isolated DuckDB installation is running queries.

Table not appearing?

Confirm that the command begins with the interpreter inside .venv\Scripts. Check the terminal for a missing-package message that points back to the installation command.

If the smoke test still fails, help me debug my isolated DuckDB verification command.

Your isolated environment is ready. Next up, you'll ingest the sales CSV into memory before testing what a second process can recover.

Expose the In-Memory Limitation

Your isolated Python environment already runs DuckDB queries. You can now turn a CSV file into a queryable sales table.

This first version stores its sales table in an in-memory database tied to one process. Two scripts will test whether that table survives the process boundary.

In this step, get ready to:
  • Create a CSV input containing five sales.
  • Build two scripts that test one in-memory table across process boundaries.
  • Run the scripts separately to expose the persistence limitation.
Create the sample sales data

A repeatable pipeline needs a fixed input. This sample records five orders across four columns.

  • Create data/sales.csv in Visual Studio Code by copying the CSV below:
order_id,order_date,product,amount
1,2026-10-01,Clavier,79.90
2,2026-10-02,Souris,29.50
3,2026-10-03,Ecran,249.00
4,2026-10-04,Casque,89.90
5,2026-10-05,Webcam,69.00

What does this data contain?

  • The header defines order_id as the order identifier.
  • The order_date column records each sale date.
  • The product column names the item sold.
  • The amount column holds the sale value.
  • Save data/sales.csv.
  • Count the records beneath the header.

You should find five records. Each record has values for all four columns.

CSV rows look misaligned?

Check that each row contains three commas. Remove any extra separators introduced while pasting.

Ask for help checking the file structure: Help me compare my sales.csv structure with the expected four-column CSV.

Build the in-memory scripts

The ingestion script uses CTAS to create a table from a query result. DuckDB reads the CSV directly through read_csv('data/sales.csv').

  • Create src/ingest.py in Visual Studio Code by copying the Python below:
import duckdb


def main():
    duckdb.sql("""
        CREATE TABLE sales AS
        SELECT *
        FROM read_csv('data/sales.csv');
    """)
    duckdb.sql("SELECT * FROM sales;").show()


if __name__ == "__main__":
    main()

Why read the CSV directly?

  • The first query reads data/sales.csv into a new sales table.
  • The CTAS statement creates the table from the selected CSV rows.
  • DuckDB's CSV reader removes the need for another data library.
  • The second query uses .show() to print the table.
  • Save src/ingest.py.

Before you run the script, how many sales rows do you expect the new table to contain?

  • Run the in-memory ingestion with the virtual environment by using this command:
.\.venv\Scripts\python.exe src\ingest.py

What should you see?

The command starts one Python process. You should see the four column names followed by all five sales rows.

That first pipeline run works. One Python process can create the table and read all five rows.

Ingestion command failed?

Confirm that PowerShell is still at the repository root. Check that data/sales.csv contains the exact header shown above.

Get help with the failed run: Help me diagnose why my in-memory DuckDB ingestion script cannot read data/sales.csv.

A second script gives a separate Python process its own entry point. Its count query tests whether that new process can access the table created earlier.

  • Create src/check.py in Visual Studio Code by copying the Python below:
import duckdb


def main():
    duckdb.sql("SELECT count(*) FROM sales;").show()


if __name__ == "__main__":
    main()

What does the check isolate?

  • The script starts without an ingestion query.
  • Its only query asks DuckDB to count rows in sales.
  • Running the file separately creates the process boundary you need to test.
  • Save src/check.py.
  • Compare your project files with the complete versions below:

✔️ Awesome, I've got everything!

Your input file and both scripts are ready for the cross-process test.

ⓧ I'd like to double check the full code

Your existing .gitignore should still contain:

.venv/
__pycache__/
*.py[cod]
data/*.duckdb
data/*.wal
data/*.tmp/

What the ignore rules protect

These rules keep the virtual environment and generated DuckDB files outside version control. The source CSV remains available for Git to track.

Your existing requirements.txt should still contain:

duckdb==1.5.6

What the dependency pin preserves

The requirement keeps every project installation on the same DuckDB release.

Your complete data/sales.csv should contain:

order_id,order_date,product,amount
1,2026-10-01,Clavier,79.90
2,2026-10-02,Souris,29.50
3,2026-10-03,Ecran,249.00
4,2026-10-04,Casque,89.90
5,2026-10-05,Webcam,69.00

What the input fixes

This file gives both processes one stable source containing five orders.

Your complete src/ingest.py should contain:

import duckdb


def main():
    duckdb.sql("""
        CREATE TABLE sales AS
        SELECT *
        FROM read_csv('data/sales.csv');
    """)
    duckdb.sql("SELECT * FROM sales;").show()


if __name__ == "__main__":
    main()

What the ingestion script owns

This process creates sales in DuckDB's global in-memory database. It prints the table before the process exits.

Your complete src/check.py should contain:

import duckdb


def main():
    duckdb.sql("SELECT count(*) FROM sales;").show()


if __name__ == "__main__":
    main()

What the check script isolates

This file starts a separate process. Its count query tests access to sales without recreating the table.

Expose the process boundary

The two scripts now perform separate jobs. Running them one after the other reveals what the second process inherits.

  • Recreate sales in the ingestion process by running this command:
.\.venv\Scripts\python.exe src\ingest.py

What did this process prove?

You should see the same five sales rows again. The table remains queryable until this command finishes.

The ingestion process has now exited. Before you run the check, do you think its fresh process can still count sales?

  • Start the independent check in a second process by running this command:
.\.venv\Scripts\python.exe src\check.py

Why did the planned failure happen?

You should see a DuckDB error reporting that sales is absent. This failure is planned.

Each script receives a separate global in-memory database. The table disappears when the ingestion process exits.

Seeing a different result?

Confirm that src/check.py contains only the count query shown above. Run the check through the virtual environment path from the repository root.

Ask for help comparing the process behavior: Help me determine why my separate check.py process is not showing the planned missing sales-table failure.

You have exposed the limitation firsthand. An in-memory table works inside one process but cannot support a later process that needs the same data.

Your pipeline can ingest the CSV, but its table vanishes too soon. Next up, you will store sales in a local DuckDB file that another process can reopen.

Persist the Sales Table

La dernière étape a chargé cinq ventes avec DuckDB. Le second processus n’a trouvé aucune table sales parce que la base en mémoire a disparu avec le premier processus.

Cette étape résout cette perte en stockant la table dans data/sales.duckdb. Le script de contrôle ouvrira ensuite ce fichier depuis un nouveau processus.

In this step, get ready to:
  • Connecter l’ingestion à un fichier DuckDB persistant.
  • Faire afficher au script de contrôle un comptage suivi d’un aperçu ordonné.
  • Confirmer que les cinq ventes survivent au changement de processus.
Connecter l’ingestion à un fichier

Une base de données persistante conserve ses tables dans un fichier local. Une connexion liée à data/sales.duckdb permet donc aux futurs processus de retrouver les ventes.

  • Reviens dans Visual Studio Code.
  • Sélectionne src/ingest.py dans la liste des fichiers à gauche.
  • Remplace tout le contenu de src/ingest.py avec le code ci-dessous:
import duckdb

DATABASE_PATH = "data/sales.duckdb"


def main():
    with duckdb.connect(DATABASE_PATH) as connection:
        connection.sql("""
            CREATE TABLE sales AS
            SELECT *
            FROM read_csv('data/sales.csv');
        """)
        connection.sql("SELECT * FROM sales;").show()


if __name__ == "__main__":
    main()

Ce que fait le script

  • La constante DATABASE_PATH centralise le chemin du fichier DuckDB.
  • La fonction duckdb.connect(DATABASE_PATH) associe la connexion à ce fichier local.
  • Le bloc with ferme la connexion à la fin de l’ingestion.
  • La requête CREATE TABLE sales AS transforme les résultats du lecteur CSV en table persistante.
  • Enregistre src/ingest.py.

✔️ Awesome, I've got everything!

Ton script d’ingestion pointe maintenant vers le fichier local data/sales.duckdb.

ⓧ I'd like to double check the full code

import duckdb

DATABASE_PATH = "data/sales.duckdb"


def main():
    with duckdb.connect(DATABASE_PATH) as connection:
        connection.sql("""
            CREATE TABLE sales AS
            SELECT *
            FROM read_csv('data/sales.csv');
        """)
        connection.sql("SELECT * FROM sales;").show()


if __name__ == "__main__":
    main()

Avant de lancer le script, penses-tu que les cinq ventes apparaîtront encore après le passage à une connexion sur fichier ?

  • Crée la base persistante depuis PowerShell en exécutant cette commande:
.\.venv\Scripts\python.exe src\ingest.py

Ce que fait cette commande

Cette commande utilise l’interpréteur de l’environnement virtuel pour exécuter src/ingest.py. Le script écrit la table sales dans le fichier configuré.

Tu verras les cinq lignes de vente dans le terminal. Le fichier data/sales.duckdb existe maintenant dans le dossier data.

Bien joué. Tes ventes disposent maintenant d’un stockage qui survit à la fermeture du script d’ingestion.

Le fichier de base n’apparaît pas ?

  • Vérifie que PowerShell se trouve toujours à la racine du dépôt.
  • Compare le contenu de src/ingest.py avec le code complet ci-dessus.
  • Confirme que le dossier data existe déjà dans le dépôt.

Demande de l’aide pour comprendre pourquoi le fichier DuckDB n’est pas créé.

Rouvrir la base depuis le script de contrôle

Le contrôle doit lire le même fichier sans modifier son contenu. Le mode lecture seule transforme src/check.py en validation indépendante de l’ingestion.

  • Sélectionne src/check.py dans la liste des fichiers à gauche.
  • Remplace tout le contenu de src/check.py avec le code ci-dessous:
import duckdb

DATABASE_PATH = "data/sales.duckdb"


def main():
    with duckdb.connect(DATABASE_PATH, read_only=True) as connection:
        connection.sql("""
            SELECT count(*) AS row_count
            FROM sales;
        """).show()
        connection.sql("""
            SELECT *
            FROM sales
            ORDER BY order_id
            LIMIT 5;
        """).show()


if __name__ == "__main__":
    main()

Ce que vérifie le script

  • La même constante DATABASE_PATH dirige le contrôle vers la base créée par l’ingestion.
  • L’option read_only=True ouvre la base sans autoriser de modification.
  • La requête SELECT count(*) AS row_count donne un nom lisible au nombre de ventes.
  • Les clauses ORDER BY order_id et LIMIT 5 produisent un aperçu stable des cinq premières lignes.
  • Enregistre src/check.py.

✔️ Awesome, I've got everything!

Ton script de contrôle est prêt à rouvrir la base depuis un nouveau processus.

ⓧ I'd like to double check the full code

import duckdb

DATABASE_PATH = "data/sales.duckdb"


def main():
    with duckdb.connect(DATABASE_PATH, read_only=True) as connection:
        connection.sql("""
            SELECT count(*) AS row_count
            FROM sales;
        """).show()
        connection.sql("""
            SELECT *
            FROM sales
            ORDER BY order_id
            LIMIT 5;
        """).show()


if __name__ == "__main__":
    main()
Vérifier la persistance entre deux processus

Le premier script a fermé sa connexion. Cette exécution séparée révèle maintenant si les données vivent réellement dans le fichier DuckDB.

Avant d’exécuter le contrôle, quel résultat attends-tu pour row_count ?

  • Rouvre la base depuis un second processus en exécutant cette commande:
.\.venv\Scripts\python.exe src\check.py

Pourquoi ce test prouve la persistance

Cette commande démarre un nouveau processus Python. Le résultat provient donc du fichier data/sales.duckdb au lieu de la connexion utilisée pendant l’ingestion.

Tu verras un premier tableau où row_count vaut 5. Tu verras ensuite les cinq ventes classées de order_id 1 à 5.

Tu as franchi le point clé. La table sales reste disponible après la fin du processus qui l’a créée.

Le contrôle ne retrouve pas les cinq ventes ?

  • Confirme que data/sales.duckdb existe dans le dossier data.
  • Compare la valeur de DATABASE_PATH dans les deux scripts.
  • Vérifie que PowerShell se trouve à la racine du dépôt.

Demande de l’aide pour diagnostiquer le contrôle persistant.

La table survit maintenant à la fermeture du premier processus. La prochaine étape vérifiera si l’ingestion reste fiable lorsque tu la relances.

Make Ingestion Repeatable

Your persistent DuckDB database now keeps the sales table between Python processes. However, the ingestion script still assumes that the table does not exist.

Running the pipeline again exposes a collision with the table created by the first run. A reliable pipeline must handle the same CSV more than once while keeping one current copy of each row.

In this step, get ready to:
  • Reproduce the collision caused by a second ingestion.
  • Make table creation safe to rerun.
  • Prove that repeated runs preserve the same five rows.
Trigger the repeat failure

The existing data/sales.duckdb file already contains the sales table. The current ingestion tries to create that table from scratch every time.

Before you rerun the script, do you think the existing table will allow another creation attempt?

  • Reproduce the second-run behavior by running this command in the PowerShell terminal from earlier:
.\.venv\Scripts\python.exe src\ingest.py

Why This Failure Is Expected

The first process already created sales. DuckDB stops the second CREATE TABLE attempt because that table name is already present.

This planned failure proves that persistence alone does not make an ingestion pipeline repeatable.

Make each run safe

Idempotence means that repeating an operation produces the same final state. Here, every ingestion should rebuild sales from the five CSV rows without colliding with the previous table.

  • In src/ingest.py, find CREATE TABLE sales AS inside main().
  • Replace that line with this version:
CREATE OR REPLACE TABLE sales AS

What Does This Change Do?

CREATE OR REPLACE TABLE replaces the existing table with the current result from read_csv('data/sales.csv'). Each run starts from the same five source rows.

  • Save src/ingest.py.

Before you rerun the script, do you expect the existing table to block this version?

  • Test the replacement query by running this command:
.\.venv\Scripts\python.exe src\ingest.py

What Should You See?

The command now completes successfully. You will see the five sales displayed by the existing preview query.

That rerun is the turning point. Your persisted table can now be rebuilt from the CSV whenever the pipeline runs.

Rerun Still Failing?

Check that the query says CREATE OR REPLACE TABLE sales AS. Confirm that you saved src/ingest.py before rerunning it.

Ask for help with the replacement query.

A successful rerun proves that replacement works. An explicit row count makes the result measurable after every ingestion.

  • In src/ingest.py, find connection.sql("SELECT * FROM sales;").show().
  • Delete that existing preview line.
  • Add the ingestion message plus the row-count query in the same location by copying this code:
        print("Ingestion terminee.")
        connection.sql("""
            SELECT count(*) AS row_count
            FROM sales;
        """).show()

How Does This Validate the Load?

  • The message confirms that the table replacement completed.
  • The count(*) query measures the rows stored in sales.
  • The row_count alias gives the result a readable column name.
  • Save src/ingest.py.

Before you run the updated script, what row count do you expect from the five-line CSV?

  • Check the new validation output by running this command:
.\.venv\Scripts\python.exe src\ingest.py

What Should You See Now?

You will see Ingestion terminee. followed by a result table. The row_count value is 5.

Good work. The script now reports a concrete measure of every completed ingestion.

Missing the Row Count?

Confirm that the row-count query remains inside the connection block. Check that its indentation matches the print() line.

Ask for help with the validation query.

Prove the final result

A count confirms the table size. An ordered preview confirms which rows were loaded while keeping the output stable across repeated runs.

  • In src/ingest.py, place your cursor below the row-count query inside main().
  • Add the ordered preview by copying this code:
        connection.sql("""
            SELECT *
            FROM sales
            ORDER BY order_id
            LIMIT 5;
        """).show()

Why Order the Preview?

ORDER BY order_id LIMIT 5 produces the same five-row sequence on every run. That stable output makes comparisons easier.

  • Save src/ingest.py.

Before you run the final check, do you expect the second ingestion to add five more rows or rebuild the same five-row table?

  • Prove repeatability across three separate processes by running these commands:
.\.venv\Scripts\python.exe src\ingest.py
.\.venv\Scripts\python.exe src\ingest.py
.\.venv\Scripts\python.exe src\check.py

What Proves Repeatability?

  • The first ingestion prints Ingestion terminee..
  • The first ingestion reports a row_count of 5.
  • The second ingestion also completes successfully.
  • The second ingestion still reports a row_count of 5.
  • The separate check.py process reports the same count.
  • The ordered preview begins with order_id 1.
  • The ordered preview ends with order_id 5.

Seeing More Than Five Rows?

Confirm that the ingestion query uses CREATE OR REPLACE TABLE. Check that the source remains data/sales.csv.

Ask for help finding why rows are accumulating.

You have built a repeatable ingestion loop. Every run recreates the same five-row table without accumulating duplicates.

✔️ Awesome, I've got everything!

Great. Double-check that you saved src/ingest.py with the replacement query plus both validation queries.

ⓧ I'd like to double check the full code

import duckdb

DATABASE_PATH = "data/sales.duckdb"


def main():
    with duckdb.connect(DATABASE_PATH) as connection:
        connection.sql("""
            CREATE OR REPLACE TABLE sales AS
            SELECT *
            FROM read_csv('data/sales.csv');
        """)

        print("Ingestion terminee.")
        connection.sql("""
            SELECT count(*) AS row_count
            FROM sales;
        """).show()
        connection.sql("""
            SELECT *
            FROM sales
            ORDER BY order_id
            LIMIT 5;
        """).show()


if __name__ == "__main__":
    main()

How to Compare the File

Compare this reference with your saved src/ingest.py. The replacement query must appear before the message plus the two validation queries.

Your pipeline can now rebuild its persistent table as often as needed. Next, you will commit the reproducible source files before publishing them to GitHub.

Commit and Publish the Pipeline

Your DuckDB pipeline now survives repeated runs with five ordered rows. Its reproducible sources still live only in your local repository.

Git records those sources without the generated database. GitHub publishes the resulting commit from your main branch.

In this step, get ready to:
  • Inspect the files Git is ready to share.
  • Create a targeted commit for the pipeline.
  • Publish a clean main branch to GitHub.
Control what Git will share

A reproducible repository contains the source data plus everything needed to rebuild the pipeline. Your virtual environment and generated database stay local because the ignore rules already cover them.

  • Inspect Git's view of the repository from the PowerShell window you used earlier by running this command:
git status

What should you see?

The status check shows which repository paths have changed since the last commit.

The reproducible set is requirements.txt, .gitignore, data/sales.csv, src/ingest.py, and src/check.py.

The .venv folder should be absent. The data/sales.duckdb file should also be absent.

Seeing generated files in the status output?

  • Confirm that .venv/ remains in .gitignore.
  • Confirm that data/*.duckdb remains in .gitignore.
  • Save .gitignore before repeating the status check.

Help me find out why ignored pipeline files still appear in Git.

Create the pipeline commit

The staging area is Git's review layer for the next snapshot. A commit permanently records only the paths placed there.

Targeted staging gives you control over the published snapshot. The local database remains available for demonstrations without entering repository history.

  • Stage the five reproducible project files by running this command:
git add requirements.txt .gitignore data/sales.csv src/ingest.py src/check.py

What does this staging command do?

Git adds only the five named paths to the staging area. This keeps the commit focused on files another person can use to recreate the pipeline.

The ignore rules still protect .venv plus generated .duckdb files.

  • Review the staged snapshot by running the status check again:
git status

What should be staged?

You should see the five named source paths staged for the next commit. No generated database path should appear.

This checkpoint proves that your commit contains the pipeline recipe without its rebuildable local state.

  • Create the local pipeline commit by running this command:
git commit -m "feat: ajout du pipeline d’ingestion Python vers DuckDB"

What does this commit capture?

The commit stores the staged versions of the dependency pin, ignore rules, sample CSV, ingestion script, and validation script.

Its message identifies the snapshot as the addition of the Python-to-DuckDB ingestion pipeline.

Commit has nothing to capture?

A message about having nothing to commit means the five paths were not staged or their current contents already exist in an earlier commit.

  • Repeat the staging command if the paths were missing from the staged snapshot.
  • Review the status output before trying the commit again.

Help me diagnose why my targeted Git commit has no staged changes.

That snapshot is now complete. Your pipeline sources have a clear local history without the disposable environment or database.

Publish and verify the repository

The commit still exists only in your local repository. Pushing it to the authenticated origin remote makes the pipeline available from GitHub.

Before you publish, predict whether GitHub will receive the generated database.

  • Publish your local main branch to the GitHub remote by running this command:
git push origin main

What does this push publish?

Git sends the new commit from your local main branch to the remote named origin.

Only committed paths travel to GitHub. The generated data/sales.duckdb file remains on your computer.

Push did not complete?

A failed push commonly points to a network interruption or an expired authentication session. Your local commit remains safe while you resolve the connection.

  • Restore your internet connection if PowerShell reports a network problem.
  • Complete the GitHub authentication prompt if one appears.
  • Repeat the push after authentication succeeds.

Help me troubleshoot a failed push from main to my authenticated GitHub origin.

Before the final status check, predict whether your local tree still has anything left to publish.

  • Confirm the final repository state by running this command:
git status

What proves publication succeeded?

The push should complete without a repository error. The final status check should report no pending changes.

Your local main branch should match the remote branch. The data/sales.duckdb file should remain absent from the tracked changes.

You have turned a local ingestion pipeline into a clean GitHub project. Anyone with the repository now has the source data, pinned dependency, ingestion logic, and validation script needed to reproduce it.

Secret mission

Ingestérer plusieurs CSV avec leur provenance

Les ventes arrivent maintenant dans plusieurs exports mensuels. Dans cette mission, vous allez les réunir dans une table répétable de six lignes tout en conservant le nom du fichier source pour chaque vente.

Clean Up Your Resources

Clean Up Your Resources

Ce projet s’exécute entièrement en local sans coût continu. Votre dépôt existant reste couvert par GitHub Free à 0 $ par mois.

Ressources que vous avez utilisées :

  • L’environnement virtuel local .venv qui contient DuckDB 1.5.6.
  • La base générée data/sales.duckdb qui contient la table sales de six lignes.

Keep everything running

Aucune action n’est nécessaire. Cette option convient si vous souhaitez relancer prochainement la démonstration.

  • Conservez .venv pour réutiliser la dépendance installée.
  • Conservez data/sales.duckdb pour garder la table persistante de six lignes.
  • Gardez les fichiers CSV bonus locaux pour conserver la provenance multi-fichiers.

Pause - I'll come back to this later

Les scripts Python se terminent après chaque exécution. Aucun service local ne continue de tourner pendant votre pause.

  • Fermez Visual Studio Code s’il est encore ouvert.
  • Fermez PowerShell lorsque vous avez fini de consulter le dépôt.
  • Conservez .venv avec data/sales.duckdb pour reprendre sans reconstruire les artefacts locaux.

Delete - I don't want to use this again

La suppression est définitive pour les deux artefacts locaux. Vos scripts restent intacts dans le dépôt.

  • Dans l’Explorateur de fichiers Windows, retournez au dossier du dépôt utilisé pour ce projet.
  • Supprimez le dossier .venv situé directement dans le dépôt.
  • Supprimez le fichier data/sales.duckdb.
  • Actualisez l’affichage du dossier.

Vous ne verrez plus .venv ni data/sales.duckdb. Les sources conservées permettent de recréer ces deux artefacts.

Nice Work!

Nice Work!

Bravo ! Vous avez construit un pipeline local qui transforme des fichiers CSV en table DuckDB persistante.

Vous avez appris à :

  • Démontrer la différence entre une base en mémoire et une base persistante en exécutant l'ingestion puis le contrôle dans des processus séparés.
  • Construire une ingestion idempotente avec CREATE OR REPLACE TABLE ... AS SELECT. Vous avez validé son résultat avec count(*), ORDER BY et LIMIT.
  • Publier un dépôt reproductible sur GitHub avec DuckDB 1.5.6 épinglé. Votre fichier .gitignore garde .venv et data/sales.duckdb hors des sources suivies.
  • Secret Mission : Étendre le pipeline à plusieurs fichiers CSV. Chaque ligne conserve sa provenance dans la colonne filename.

Prêt à tester vos connaissances ?