Construire un pipeline Olist

Transformez les CSV Olist en modèle étoile DuckDB prêt pour un dashboard.

Introduction

30 Second Summary

Neuf fichiers de données séparés sont difficiles à exploiter chaque fois que les chiffres doivent être actualisés. Chaque mise à jour oblige à répéter les mêmes imports et les mêmes rapprochements.

Dans ce projet, tu vas construire un pipeline analytique local qui transforme neuf fichiers CSV Olist en une base DuckDB prête à alimenter un dashboard avec dbt. Une commande unique reconstruira ce résultat à chaque mise à jour.

What You'll Build

À la fin, ton terminal affichera le chargement des neuf sources Olist, la réussite des modèles et des tests, puis les commandes, les lignes de commande et la valeur brute totale prêtes pour ton futur dashboard.

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

  • Un rafraîchissement reproductible qui recharge les neuf fichiers Olist dans une base persistante sans import manuel.
  • Un schéma en étoile testé que tu peux interroger à travers fct_order_items, dim_customers, dim_products et dim_sellers.
  • Des contrôles automatiques qui vérifient les clés et les relations du modèle tout en démontrant pourquoi chaque ligne de faits représente un article de commande.
  • Secret Mission: Une table quotidienne des ventes qui regroupe les commandes, les articles et la valeur brute pour alimenter directement un graphique de dashboard.

De quoi as-tu besoin avant de commencer ?

Tu as besoin d’un ordinateur Windows avec Python et Visual Studio Code. Prépare aussi les neuf fichiers du dataset Brazilian E-Commerce Public Dataset by Olist avec leurs noms d’origine.

Before We Start

Before the hands-on work begins, lock in what you are building and why it matters. You are turning the nine Olist CSV files into a local analytics pipeline that can reliably supply a future dashboard.

Set Up the Local Analytics Project

Nine Olist CSV files are waiting to become one analytics source. They need a controlled workspace before Python can load them reliably.

An isolated Python virtual environment keeps this project's packages separate from the rest of your computer. Pinned DuckDB and dbt packages make each refresh reproducible.

In this step, get ready to:
  • Confirm Python 3.10 or newer is available.
  • Prepare the olist-pipeline folder structure for the nine CSV sources.
  • Configure the pinned local dbt environment.
Verify Python and create the project

The pinned packages require Python 3.10 or newer. You will use Visual Studio Code to keep the files beside the terminal that runs them.

  • Press the Windows key to open Windows Search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.
  • Select Terminal from the top menu.
  • Select New Terminal to display the integrated terminal.
  • Click the profile dropdown beside the plus button in the terminal panel.
  • Select PowerShell as the terminal profile.
  • Confirm the installed Python version by running this command:
python --version

What does this command check?

The command prints the Python interpreter version used by this terminal. Continue when the number is 3.10 or newer.

Your interpreter clears the compatibility gate. The pinned DuckDB and dbt packages can run with this Python version.

  • Move the PowerShell terminal to your Desktop by running this command:
Set-Location -Path "$HOME\Desktop"

Why start on the Desktop?

This gives the project a predictable location. You can find the olist-pipeline folder from Windows or Visual Studio Code.

  • Create the complete project structure by running these commands:
New-Item -Path "olist-pipeline" -ItemType Directory
Set-Location -Path "olist-pipeline"
New-Item -Path "data" -ItemType Directory
New-Item -Path "data\raw" -ItemType Directory
New-Item -Path "models" -ItemType Directory
New-Item -Path "models\staging" -ItemType Directory
New-Item -Path "models\marts" -ItemType Directory
New-Item -Path "requirements.txt" -ItemType File
New-Item -Path "profiles.yml" -ItemType File
New-Item -Path "dbt_project.yml" -ItemType File

What does this structure provide?

  • The data/raw folder holds the original Olist files.
  • The models/staging folder will hold cleaned source views.
  • The models/marts folder will hold dashboard-ready analytical models.
  • The three files in olist-pipeline define packages plus dbt configuration.

PowerShell lists each new directory or file as it creates it. That output confirms the empty project structure exists on disk.

Could PowerShell not create the structure?

Check that the terminal location is your Desktop before retrying the commands. Remove any incomplete olist-pipeline folder if PowerShell says an item already exists.

Ask for help with the folder creation output:

  • Open the current olist-pipeline folder as the Visual Studio Code workspace by running this command:
code .

What does this command open?

The dot represents the terminal's current folder. Visual Studio Code opens olist-pipeline as the workspace shown in the Explorer sidebar.

You should see data plus models in the Explorer sidebar. You should also see the three empty configuration files.

Did the project folder stay closed?

Use File followed by Open Folder in Visual Studio Code. Select the olist-pipeline folder on your Desktop.

Ask for help opening the workspace:

The raw layer preserves the canonical source files before any business transformations occur. Keeping every CSV together gives the ingestion script one stable source location.

  • Open the folder containing your nine Olist CSV files in Windows File Explorer.
  • Select all nine canonical CSV files.
  • Press Ctrl+C to copy the selected files.
  • Switch back to Visual Studio Code.
  • Expand the data folder in the Explorer sidebar.
  • Select the raw folder.
  • Press Ctrl+V to paste the CSV files into data/raw.

What should be in data/raw?

  • olist_customers_dataset.csv.
  • olist_geolocation_dataset.csv.
  • olist_order_items_dataset.csv.
  • olist_order_payments_dataset.csv.
  • olist_order_reviews_dataset.csv.
  • olist_orders_dataset.csv.
  • olist_products_dataset.csv.
  • olist_sellers_dataset.csv.
  • product_category_name_translation.csv.
Pin and install the analytics packages

A requirements file records the exact package versions used by the pipeline. These pins prevent a future installation from silently selecting different releases.

  • Select requirements.txt in the Explorer sidebar.
  • Replace its empty contents with the following package pins:
duckdb==1.5.6
dbt-core==1.12.5
dbt-duckdb==1.11.0

What do these packages provide?

  • duckdb==1.5.6 provides the embedded analytical database.
  • dbt-core==1.12.5 provides model ordering plus data tests.
  • dbt-duckdb==1.11.0 lets dbt build those models inside DuckDB.
  • Save requirements.txt by pressing Ctrl+S.
  • Confirm the editor contains exactly three package pins.

Does the requirements file differ?

Check each package name plus both equals signs. A missing character changes the dependency request.

Ask for help comparing the pins:

Package installation can take a few minutes while pip downloads the pinned releases. The terminal output shows progress throughout the install.

  • Return to the PowerShell terminal from earlier.
  • Create the virtual environment plus activate it plus install the pinned packages by running these commands:
python -m venv .venv
.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt

What do these commands set up?

  • The first command creates an isolated environment in .venv.
  • The second command activates that environment for the current PowerShell terminal.
  • The final command installs every version recorded in requirements.txt.
  • Confirm the environment name appears at the start of the PowerShell prompt.
  • Confirm the pip output finishes with a successful installation summary.

Did activation or installation fail?

If PowerShell blocks the activation script, follow your organization's script execution policy before continuing. Avoid changing a managed policy without approval.

If pip cannot find requirements.txt, confirm the terminal path ends with olist-pipeline.

Ask for help with your installation output:

Configure dbt and verify the connection

A dbt profile identifies the database adapter plus the local database file. The project file defines where dbt finds models plus how it materializes each layer.

  • Select profiles.yml in the Explorer sidebar.
  • Replace its empty contents with this DuckDB profile:
olist_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: olist.duckdb
      schema: analytics
      threads: 4

How does this profile connect?

  • olist_pipeline names the profile that the dbt project uses.
  • dev selects the local development output.
  • olist.duckdb is the persistent database path inside the project.
  • analytics is the schema where dbt will build models.
  • threads: 4 allows dbt to run up to four independent tasks at once.
  • Save profiles.yml by pressing Ctrl+S.
  • Confirm path points to olist.duckdb.

Does the YAML indentation look uneven?

Use spaces before each nested key. A tab character can stop dbt from reading the profile.

Ask for help checking the profile structure:

  • Select dbt_project.yml in the Explorer sidebar.
  • Replace its empty contents with this project configuration:
name: olist_pipeline
version: "1.0.0"
config-version: 2
profile: olist_pipeline

model-paths: ["models"]
clean-targets: ["target", "dbt_packages"]

models:
  olist_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table

How does dbt organize this project?

  • profile: olist_pipeline links this project to profiles.yml.
  • model-paths tells dbt to search inside models.
  • clean-targets records the generated directories that dbt may clean.
  • staging models become views.
  • marts models become tables.
  • Save dbt_project.yml by pressing Ctrl+S.
  • Confirm the staging materialization is view.
  • Confirm the marts materialization is table.

Does dbt reject the project file?

Check that models is nested below the top-level settings. Confirm each materialization line starts with a plus sign.

Ask for help checking the project structure:

Use the comparison below before testing the connection. Each file should match its complete reference.

✔️ Awesome, I've got everything!

Your three setup files match the project configuration. Save every open editor tab before running the connection check.

ⓧ I'd like to double check the full code

Compare each file with the complete reference below.

duckdb==1.5.6
dbt-core==1.12.5
dbt-duckdb==1.11.0

Requirements file purpose

This complete file pins the three packages installed inside .venv.

olist_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: olist.duckdb
      schema: analytics
      threads: 4

Profile file purpose

This complete file directs dbt to the local DuckDB database plus the analytics schema.

name: olist_pipeline
version: "1.0.0"
config-version: 2
profile: olist_pipeline

model-paths: ["models"]
clean-targets: ["target", "dbt_packages"]

models:
  olist_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table

Project file purpose

This complete file links the project to its profile. It also assigns views to staging plus tables to marts.

Before you run the check, do you expect dbt to find both the project and its local DuckDB profile?

  • Verify the local dbt connection by running this command in the activated PowerShell terminal:
dbt debug

What should you see?

The diagnostic identifies the olist_pipeline profile. It confirms that the DuckDB adapter can open olist.duckdb.

The successful connection check proves that dbt can read both YAML files. Your local analytics project is ready for its first raw load.

🙋‍♀️ Did dbt debug report a problem?

Confirm the PowerShell prompt still shows the active virtual environment. Activate it again if you opened a fresh terminal.

Confirm profiles.yml plus dbt_project.yml are saved directly inside olist-pipeline.

Ask for help with the diagnostic output:

That is the setup hurdle cleared: dbt can reach the local DuckDB file through the project profile. Next, you will load all nine CSV sources into a persistent raw layer.

Load the Raw CSV Sources

The local dbt setup can already reach the configured DuckDB path. The nine Olist files still exist as separate CSVs.

This step uses Python to materialize those files in a persistent raw layer. Every source stays as text so dbt can own the business typing later.

In this step, get ready to:
  • Define a stable mapping between the nine canonical CSV files and their raw table names.
  • Create a repeatable loader that rebuilds the raw schema from the source files.
  • Run the ingestion twice to prove repeated refreshes keep stable row counts.
Map the CSV sources

An explicit mapping gives every source file a short table name. It also makes missing files easier to identify before loading begins.

  • In the VS Code Explorer sidebar, create ingest.py inside the olist-pipeline folder.
  • Paste the project paths plus source mapping by adding this code:
from pathlib import Path

import duckdb

ROOT = Path(__file__).resolve().parent
DATA_DIR = ROOT / "data" / "raw"
DATABASE_PATH = ROOT / "olist.duckdb"

CSV_TABLES = {
    "customers": "olist_customers_dataset.csv",
    "geolocation": "olist_geolocation_dataset.csv",
    "order_items": "olist_order_items_dataset.csv",
    "order_payments": "olist_order_payments_dataset.csv",
    "order_reviews": "olist_order_reviews_dataset.csv",
    "orders": "olist_orders_dataset.csv",
    "products": "olist_products_dataset.csv",
    "sellers": "olist_sellers_dataset.csv",
    "product_category_translation": "product_category_name_translation.csv",
}

What does this setup define?

  • Path builds filesystem locations that work with the Windows project folder.
  • ROOT identifies the folder containing ingest.py.
  • DATA_DIR points to the existing data/raw folder.
  • DATABASE_PATH identifies the persistent olist.duckdb database file.
  • CSV_TABLES pairs each raw table name with its canonical Olist filename.
  • Save ingest.py.

You should see ingest.py beside the existing project files in the Explorer sidebar.

Is the file in the wrong folder?

  • Move ingest.py directly into the olist-pipeline folder.
  • Check that requirements.txt appears beside it in the Explorer sidebar.

Help me place ingest.py in the correct VS Code project folder.

Build repeatable raw ingestion

An idempotent loader reaches the same database state whenever its source files stay unchanged. This loader drops each existing raw table before recreating it from the matching CSV.

The all_varchar = true option preserves every CSV column as text. Later models can apply explicit business types without changing the original raw values.

  • In ingest.py, place your cursor below the closing brace of CSV_TABLES.
  • Add the raw loading function by pasting this code:
def load_raw() -> dict[str, int]:
    missing_files = [
        DATA_DIR / filename
        for filename in CSV_TABLES.values()
        if not (DATA_DIR / filename).is_file()
    ]
    if missing_files:
        formatted = "\n".join(str(path) for path in missing_files)
        raise FileNotFoundError(f"Fichiers CSV manquants:\n{formatted}")

    row_counts: dict[str, int] = {}

    with duckdb.connect(str(DATABASE_PATH)) as connection:
        connection.execute("CREATE SCHEMA IF NOT EXISTS raw")

        for table_name, filename in CSV_TABLES.items():
            csv_path = (DATA_DIR / filename).as_posix().replace("'", "''")
            connection.execute(f"DROP TABLE IF EXISTS raw.{table_name}")
            connection.execute(
                f"CREATE TABLE raw.{table_name} AS "
                f"SELECT * FROM read_csv('{csv_path}', all_varchar = true)"
            )
            row_count = connection.execute(
                f"SELECT COUNT(*) FROM raw.{table_name}"
            ).fetchone()[0]
            row_counts[table_name] = row_count

    return row_counts

How does the loader stay reliable?

  • missing_files collects any mapped CSV paths that do not exist.
  • FileNotFoundError stops ingestion before the database receives a partial refresh.
  • CREATE SCHEMA IF NOT EXISTS raw makes the raw namespace available for every table.
  • DROP TABLE IF EXISTS removes the previous copy before each source is reloaded.
  • row_counts records the number of loaded rows for each table.
  • Save ingest.py.

The editor should show the complete load_raw() function without indentation warnings or red syntax markers.

Seeing Python syntax markers?

  • Compare the indentation inside load_raw() with the code block above.
  • Confirm the function starts immediately after the closing brace of CSV_TABLES.

Help me fix the syntax in my load_raw function.

Run the full refresh twice

The loader now returns its row counts. A small entry point calls the function whenever you run the file directly.

  • Place your cursor at the bottom of ingest.py.
  • Add the entry point by pasting this code:
if __name__ == "__main__":
    counts = load_raw()
    for name, count in counts.items():
        print(f"raw.{name}: {count:,} lignes")

What does the entry point do?

  • __main__ identifies a direct run of ingest.py.
  • load_raw() rebuilds the raw tables before returning their row counts.
  • print() gives each table a visible success signal in the terminal.
  • Save ingest.py.
  • Double-check the completed loader with the tabs below.

✔️ Awesome, I've got everything!

Great work. Your loader now maps all nine sources and rebuilds their raw tables.

ⓧ I'd like to double check the full code

from pathlib import Path

import duckdb

ROOT = Path(__file__).resolve().parent
DATA_DIR = ROOT / "data" / "raw"
DATABASE_PATH = ROOT / "olist.duckdb"

CSV_TABLES = {
    "customers": "olist_customers_dataset.csv",
    "geolocation": "olist_geolocation_dataset.csv",
    "order_items": "olist_order_items_dataset.csv",
    "order_payments": "olist_order_payments_dataset.csv",
    "order_reviews": "olist_order_reviews_dataset.csv",
    "orders": "olist_orders_dataset.csv",
    "products": "olist_products_dataset.csv",
    "sellers": "olist_sellers_dataset.csv",
    "product_category_translation": "product_category_name_translation.csv",
}


def load_raw() -> dict[str, int]:
    missing_files = [
        DATA_DIR / filename
        for filename in CSV_TABLES.values()
        if not (DATA_DIR / filename).is_file()
    ]
    if missing_files:
        formatted = "\n".join(str(path) for path in missing_files)
        raise FileNotFoundError(f"Fichiers CSV manquants:\n{formatted}")

    row_counts: dict[str, int] = {}

    with duckdb.connect(str(DATABASE_PATH)) as connection:
        connection.execute("CREATE SCHEMA IF NOT EXISTS raw")

        for table_name, filename in CSV_TABLES.items():
            csv_path = (DATA_DIR / filename).as_posix().replace("'", "''")
            connection.execute(f"DROP TABLE IF EXISTS raw.{table_name}")
            connection.execute(
                f"CREATE TABLE raw.{table_name} AS "
                f"SELECT * FROM read_csv('{csv_path}', all_varchar = true)"
            )
            row_count = connection.execute(
                f"SELECT COUNT(*) FROM raw.{table_name}"
            ).fetchone()[0]
            row_counts[table_name] = row_count

    return row_counts


if __name__ == "__main__":
    counts = load_raw()
    for name, count in counts.items():
        print(f"raw.{name}: {count:,} lignes")

What should match?

This reference contains the complete loader for this step. Every path, filename, table name, SQL statement, and indentation level should match your saved file.

Before the first run, do you expect the database file to exist before ingestion finishes or only after the loader connects?

  • Create the database plus all nine raw tables by running this command in the active PowerShell terminal:
python ingest.py

What happened on the first run?

You should see nine lines beginning with raw.. Every line should end with a positive row count.

You should also see olist.duckdb in the VS Code Explorer sidebar. You have turned nine isolated CSV files into one persistent raw layer.

🙋‍♀️ Missing a table or database file?

  • Check that all nine canonical CSV files remain inside data/raw.
  • Close any database viewer that may still hold olist.duckdb open.
  • Confirm the active terminal is running from the olist-pipeline folder.

Help me diagnose why ingest.py did not load all nine Olist CSV files.

Before the second run, do you expect unchanged CSV files to double the table counts or keep them stable?

  • Test the idempotent refresh by running the same command again:
python ingest.py

What proves the refresh is idempotent?

You should see the same nine table names with the same row counts. Each old table was dropped before its replacement was created.

That stable result proves the loader can rebuild the raw layer without accumulating duplicate rows.

🙋‍♀️ Did a row count change unexpectedly?

  • Confirm that none of the CSV files changed between the two runs.
  • Check that every table creation query still includes all_varchar = true.
  • Compare each printed table name with its entry in CSV_TABLES.

Help me explain why my second ingestion run produced different row counts.

Your persistent raw layer is loaded and repeatable. Next, you will use dbt to type the order data and reveal its true grain.

Expose the Fact Table Grain

Your nine source files now live as persistent tables in DuckDB. The next layer gives those raw values consistent names and data types for analysis.

A fact table needs a clearly defined grain. This step uses dbt to test whether order_id can identify every row after orders meet their items.

In this step, get ready to:
  • Declare the nine raw tables as dbt sources.
  • Create typed staging views for customers, orders, and order items.
  • Test whether order_id remains unique in a joined fact table.
Declare raw sources and stage customers

A dbt source declaration gives every raw table a name inside the model dependency graph. The first staging view selects the customer fields that later models need.

  • In the VS Code Explorer sidebar, create sources.yml inside models with the New File button.
  • Paste this source declaration into models/sources.yml:
version: 2

sources:
  - name: olist_raw
    schema: raw
    tables:
      - name: customers
      - name: geolocation
      - name: order_items
      - name: order_payments
      - name: order_reviews
      - name: orders
      - name: products
      - name: sellers
      - name: product_category_translation

What does this source declaration do?

  • The olist_raw source points dbt at the existing raw schema.
  • Each table entry gives dbt a named dependency that models can reference.
  • All nine sources remain available even when the current mart only uses three of them.
  • Save models/sources.yml.
  • Confirm that sources.yml appears directly inside models in the Explorer sidebar.

Source file in the wrong folder?

Check that the path is models/sources.yml. A source file placed inside data/raw is outside dbt's model path.

Help me check my dbt source file location and YAML indentation.

  • In the VS Code Explorer sidebar, create stg_customers.sql inside models/staging with the New File button.
  • Paste this customer staging query into models/staging/stg_customers.sql:
SELECT
    customer_id,
    customer_unique_id,
    customer_zip_code_prefix,
    customer_city,
    customer_state
FROM {{ source('olist_raw', 'customers') }}

What does this staging model do?

  • The source reference connects the model to raw.customers.
  • The selected columns preserve the order-level customer key plus the repeat-customer identifier.
  • The project configuration materializes this staging model as a view.
  • Save models/staging/stg_customers.sql.
  • Build the current dbt graph by running this command in the active PowerShell terminal:
dbt build

What does this check prove?

dbt resolves the customer source before building stg_customers. A successful result proves the first source dependency is valid.

Good progress. You should see stg_customers complete successfully as a view.

Customer staging model failed?

Check the indentation in models/sources.yml. Confirm that the source name remains olist_raw in both files.

Help me diagnose why dbt cannot build stg_customers from my raw customers source.

Stage orders and order items

Raw ingestion deliberately kept every CSV value as text. These two staging views convert timestamps, item numbers, and monetary values into types that analytical models can use.

  • In the VS Code Explorer sidebar, create stg_orders.sql inside models/staging with the New File button.
  • Paste this order staging query into models/staging/stg_orders.sql:
SELECT
    order_id,
    customer_id,
    order_status,
    TRY_CAST(order_purchase_timestamp AS TIMESTAMP) AS order_purchase_timestamp,
    TRY_CAST(order_approved_at AS TIMESTAMP) AS order_approved_at
FROM {{ source('olist_raw', 'orders') }}

What does this order model do?

  • The model keeps the order key plus the customer key needed for the fact join.
  • The two timestamp conversions turn raw text into values that support date analysis.
  • A failed conversion becomes a null value through TRY_CAST.
  • Save models/staging/stg_orders.sql.
  • Build the expanded staging layer by running this command in PowerShell:
dbt build

What does this build confirm?

dbt builds stg_customers plus stg_orders from their declared sources. Both models should complete before the command returns.

You should see both staging views finish successfully.

Order staging model failed?

Confirm that orders appears beneath the olist_raw source. Compare every selected column with the canonical order CSV headers.

Help me troubleshoot my stg_orders model and its source reference.

  • In the VS Code Explorer sidebar, create stg_order_items.sql inside models/staging with the New File button.
  • Paste this item staging query into models/staging/stg_order_items.sql:
SELECT
    order_id,
    TRY_CAST(order_item_id AS INTEGER) AS order_item_id,
    product_id,
    seller_id,
    TRY_CAST(shipping_limit_date AS TIMESTAMP) AS shipping_limit_date,
    TRY_CAST(price AS DECIMAL(18, 2)) AS item_price,
    TRY_CAST(freight_value AS DECIMAL(18, 2)) AS freight_value
FROM {{ source('olist_raw', 'order_items') }}

What does this item model do?

  • The model retains the order key plus the item number.
  • The product and seller keys identify which dimensions each item can reference.
  • The price and freight conversions create fixed-precision monetary values.
  • Save models/staging/stg_order_items.sql.
  • Build all three staging views by running this command in PowerShell:
dbt build

What does this build confirm?

The command resolves all three source dependencies. It then builds the customer, order, and item staging views in the analytics schema.

You should see stg_customers, stg_orders, and stg_order_items complete successfully.

Item staging model failed?

Confirm that the file uses order_items as the source table name. Check each cast for matching parentheses.

Help me find the SQL or source error in stg_order_items.

Build and test the naive fact

The temporary fact joins each item to its parent order. You will first build that join without a line-level key.

  • In the VS Code Explorer sidebar, create fct_order_items.sql inside models/marts with the New File button.
  • Paste this temporary fact query into models/marts/fct_order_items.sql:
SELECT
    order_items.order_id,
    order_items.order_item_id,
    orders.customer_id,
    order_items.product_id,
    order_items.seller_id,
    orders.order_status,
    orders.order_purchase_timestamp,
    orders.order_approved_at,
    order_items.shipping_limit_date,
    order_items.item_price,
    order_items.freight_value,
    order_items.item_price + order_items.freight_value AS gross_item_value
FROM {{ ref('stg_order_items') }} AS order_items
INNER JOIN {{ ref('stg_orders') }} AS orders
    ON order_items.order_id = orders.order_id

What does this fact model do?

  • The item staging view supplies product, seller, shipping, price, and freight fields.
  • The order staging view supplies customer, status, purchase, and approval fields.
  • The inner join keeps item rows whose order key exists in both staging views.
  • The gross value adds the item price to its freight value.
  • Save models/marts/fct_order_items.sql.
  • Build the temporary fact table by running this command in PowerShell:
dbt build

What does this build establish?

dbt follows the model references from the staging views into fct_order_items. The project configuration materializes this mart model as a table.

You should see the three staging views plus fct_order_items complete successfully.

Fact model failed to build?

Check that both model references match stg_order_items and stg_orders exactly. Confirm that the join uses order_id on both sides.

Help me troubleshoot the temporary fct_order_items join.

  • In the VS Code Explorer sidebar, create schema.yml inside models/marts with the New File button.
  • Paste this temporary test configuration into models/marts/schema.yml:
version: 2

models:
  - name: fct_order_items
    description: Version temporaire qui démontre pourquoi order_id n'est pas unique au grain article de commande.
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique

What do these tests check?

  • The not_null test checks that every fact row has an order identifier.
  • The unique test checks the temporary assumption that each order identifier appears once.
  • The model description records why this configuration is temporary.
  • Save models/marts/schema.yml.

This is the designed grain check. Before you run it, do you think one order identifier can remain unique after joining orders to their items?

  • Test the assumption by running this command in PowerShell:
dbt build

What did the grain check reveal?

The staging views and temporary fact compile. The unique test on fct_order_items.order_id fails because some orders contribute multiple item rows.

The failed test proves that the total fact rows exceed the number of distinct order identifiers. This red result is useful because it exposes the model's real line-item grain.

Did the build fail somewhere else?

If dbt stops before the uniqueness test, compare the affected file with the full-code check below. If the uniqueness test passes, confirm that schema.yml targets fct_order_items.

Help me distinguish the intended unique-test failure from a SQL or YAML error.

✔️ Awesome, I've got everything!

Great. Save every file before moving on.

ⓧ I'd like to double check the full code

Compare each project file with the cumulative state below.

name: olist_pipeline
version: "1.0.0"
config-version: 2
profile: olist_pipeline

model-paths: ["models"]
clean-targets: ["target", "dbt_packages"]

models:
  olist_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table

This file keeps staging models as views and mart models as tables.

from pathlib import Path

import duckdb

ROOT = Path(__file__).resolve().parent
DATA_DIR = ROOT / "data" / "raw"
DATABASE_PATH = ROOT / "olist.duckdb"

CSV_TABLES = {
    "customers": "olist_customers_dataset.csv",
    "geolocation": "olist_geolocation_dataset.csv",
    "order_items": "olist_order_items_dataset.csv",
    "order_payments": "olist_order_payments_dataset.csv",
    "order_reviews": "olist_order_reviews_dataset.csv",
    "orders": "olist_orders_dataset.csv",
    "products": "olist_products_dataset.csv",
    "sellers": "olist_sellers_dataset.csv",
    "product_category_translation": "product_category_name_translation.csv",
}


def load_raw() -> dict[str, int]:
    missing_files = [
        DATA_DIR / filename
        for filename in CSV_TABLES.values()
        if not (DATA_DIR / filename).is_file()
    ]
    if missing_files:
        formatted = "\n".join(str(path) for path in missing_files)
        raise FileNotFoundError(f"Fichiers CSV manquants:\n{formatted}")

    row_counts: dict[str, int] = {}

    with duckdb.connect(str(DATABASE_PATH)) as connection:
        connection.execute("CREATE SCHEMA IF NOT EXISTS raw")

        for table_name, filename in CSV_TABLES.items():
            csv_path = (DATA_DIR / filename).as_posix().replace("'", "''")
            connection.execute(f"DROP TABLE IF EXISTS raw.{table_name}")
            connection.execute(
                f"CREATE TABLE raw.{table_name} AS "
                f"SELECT * FROM read_csv('{csv_path}', all_varchar = true)"
            )
            row_count = connection.execute(
                f"SELECT COUNT(*) FROM raw.{table_name}"
            ).fetchone()[0]
            row_counts[table_name] = row_count

    return row_counts


if __name__ == "__main__":
    counts = load_raw()
    for name, count in counts.items():
        print(f"raw.{name}: {count:,} lignes")

This file retains the idempotent raw ingestion from the previous step.

SELECT
    order_items.order_id,
    order_items.order_item_id,
    orders.customer_id,
    order_items.product_id,
    order_items.seller_id,
    orders.order_status,
    orders.order_purchase_timestamp,
    orders.order_approved_at,
    order_items.shipping_limit_date,
    order_items.item_price,
    order_items.freight_value,
    order_items.item_price + order_items.freight_value AS gross_item_value
FROM {{ ref('stg_order_items') }} AS order_items
INNER JOIN {{ ref('stg_orders') }} AS orders
    ON order_items.order_id = orders.order_id

This temporary fact joins item rows to their parent orders.

version: 2

models:
  - name: fct_order_items
    description: Version temporaire qui démontre pourquoi order_id n'est pas unique au grain article de commande.
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique

This temporary schema file applies the intentional uniqueness test.

version: 2

sources:
  - name: olist_raw
    schema: raw
    tables:
      - name: customers
      - name: geolocation
      - name: order_items
      - name: order_payments
      - name: order_reviews
      - name: orders
      - name: products
      - name: sellers
      - name: product_category_translation

This file declares all nine raw tables as dbt sources.

SELECT
    customer_id,
    customer_unique_id,
    customer_zip_code_prefix,
    customer_city,
    customer_state
FROM {{ source('olist_raw', 'customers') }}

This model exposes the customer fields needed by later marts.

SELECT
    order_id,
    TRY_CAST(order_item_id AS INTEGER) AS order_item_id,
    product_id,
    seller_id,
    TRY_CAST(shipping_limit_date AS TIMESTAMP) AS shipping_limit_date,
    TRY_CAST(price AS DECIMAL(18, 2)) AS item_price,
    TRY_CAST(freight_value AS DECIMAL(18, 2)) AS freight_value
FROM {{ source('olist_raw', 'order_items') }}

This model converts raw item fields into analytical types.

SELECT
    order_id,
    customer_id,
    order_status,
    TRY_CAST(order_purchase_timestamp AS TIMESTAMP) AS order_purchase_timestamp,
    TRY_CAST(order_approved_at AS TIMESTAMP) AS order_approved_at
FROM {{ source('olist_raw', 'orders') }}

This model converts the raw order timestamps.

olist_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: olist.duckdb
      schema: analytics
      threads: 4

This profile keeps dbt connected to the local DuckDB database.

duckdb==1.5.6
dbt-core==1.12.5
dbt-duckdb==1.11.0

This file retains the pinned package versions for the project.

You have proved that an order-level key cannot uniquely identify every item row. Next, you will define the line-item key and turn this temporary model into a tested star schema.

Build the Star Schema

Your previous dbt run exposed the grain problem. The failed uniqueness test proved that one order can contain several item rows.

A star schema keeps measurable events in a fact table. Reusable context sits in dimensions. A key built from the order plus its item number gives every fact row an unambiguous identity.

In this step, get ready to:
  • Add the remaining staging views plus three reusable dimensions.
  • Set the fact-table grain to one row per order item.
  • Validate the star schema with automated dbt tests.
Add the remaining staging views and dimensions

The remaining staging views expose product data plus seller data. A separate view provides the English category translations used by the product dimension.

  • Create `models/staging/stg_products.sql` from the file tree in Visual Studio Code.
  • Add the product staging query by pasting this code:
SELECT
    product_id,
    product_category_name
FROM {{ source('olist_raw', 'products') }}

What Does This View Provide?

  • The view exposes one product identifier plus its source category name.
  • The `source()` dependency tells dbt that this model reads the `products` table from `olist_raw`.
  • Save `models/staging/stg_products.sql`.
  • Confirm the file tree now lists `stg_products.sql` inside `models/staging`.

Product View File Missing?

Check that the file is inside `models/staging`. Confirm that its name ends with `.sql`.

Ask for help with the file location: help me place stg_products.sql in the correct dbt staging folder.

  • Create `models/staging/stg_sellers.sql` from the file tree in Visual Studio Code.
  • Add the seller staging query by pasting this code:
SELECT
    seller_id,
    seller_zip_code_prefix,
    seller_city,
    seller_state
FROM {{ source('olist_raw', 'sellers') }}

What Does This View Provide?

  • The view keeps the seller identifier used by each fact row.
  • The location columns provide reusable seller context for future analysis.
  • Save `models/staging/stg_sellers.sql`.
  • Confirm the file tree now lists `stg_sellers.sql` inside `models/staging`.

Seller View File Missing?

Check that `stg_sellers.sql` sits beside the other staging models. Confirm that the filename contains no extra extension.

Ask for help with the file: help me check my stg_sellers.sql file.

  • Create `models/staging/stg_category_translation.sql` from the file tree in Visual Studio Code.
  • Add the category translation query by pasting this code:
SELECT
    product_category_name,
    product_category_name_english
FROM {{ source('olist_raw', 'product_category_translation') }}

What Does This View Provide?

  • The source category name acts as the join field.
  • The English category name gives the product dimension a dashboard-friendly label when a translation exists.
  • Save `models/staging/stg_category_translation.sql`.
  • Confirm the file tree now lists `stg_category_translation.sql` inside `models/staging`.

Translation View File Missing?

Check that you created the file inside `models/staging`. Compare the filename with `stg_category_translation.sql`.

Ask for help with the translation model: help me check my category translation staging model.

Each dimension now selects a stable business key from a staging view. The product dimension also enriches its source category with the available translation.

  • Create `models/marts/dim_customers.sql` from the file tree in Visual Studio Code.
  • Add the customer dimension by pasting this code:
SELECT
    customer_id,
    customer_unique_id,
    customer_zip_code_prefix,
    customer_city,
    customer_state
FROM {{ ref('stg_customers') }}

Why Keep Both Customer Identifiers?

  • `customer_id` links each order to the matching dimension row.
  • `customer_unique_id` preserves the identifier used to recognise repeat customers.
  • The `ref()` dependency places `stg_customers` before this dimension in the dbt graph.
  • Save `models/marts/dim_customers.sql`.
  • Confirm the file tree now lists `dim_customers.sql` inside `models/marts`.

Customer Dimension File Missing?

Check that `dim_customers.sql` is inside `models/marts`. Confirm that `stg_customers` matches the existing staging filename.

Ask for help with this dependency: help me check the customer dimension reference.

  • Create `models/marts/dim_products.sql` from the file tree in Visual Studio Code.
  • Add the enriched product dimension by pasting this code:
SELECT
    products.product_id,
    products.product_category_name,
    CASE
        WHEN translations.product_category_name_english IS NOT NULL
            THEN translations.product_category_name_english
        ELSE products.product_category_name
    END AS product_category
FROM {{ ref('stg_products') }} AS products
LEFT JOIN {{ ref('stg_category_translation') }} AS translations
    ON products.product_category_name = translations.product_category_name

How Does the Category Fallback Work?

  • The left join keeps every product even when no translation is available.
  • The `CASE` expression uses the English category when it exists.
  • The source category becomes the fallback value for `product_category`.
  • Save `models/marts/dim_products.sql`.
  • Confirm the file tree now lists `dim_products.sql` inside `models/marts`.

Product Dimension Looks Incomplete?

Check that both `stg_products` plus `stg_category_translation` appear in the query. Compare the two category column names carefully.

Ask for help with the join: help me debug the product category translation join.

  • Create `models/marts/dim_sellers.sql` from the file tree in Visual Studio Code.
  • Add the seller dimension by pasting this code:
SELECT
    seller_id,
    seller_zip_code_prefix,
    seller_city,
    seller_state
FROM {{ ref('stg_sellers') }}

What Does the Seller Dimension Add?

  • Each row represents one seller identifier.
  • The location fields make seller geography available without widening the fact table.
  • Save `models/marts/dim_sellers.sql`.
  • Confirm the file tree now lists `dim_sellers.sql` inside `models/marts`.

Seller Dimension File Missing?

Check that the file sits inside `models/marts`. Confirm that its `ref()` value matches `stg_sellers` exactly.

Ask for help with this model: help me check the seller dimension model.

Set the fact table to its true grain

The fact table needs one unique key for each order item. Concatenating `order_id` with `order_item_id` creates that key while preserving both original fields.

  • Select `models/marts/fct_order_items.sql` in the Visual Studio Code file tree.
  • Replace its temporary query with this final fact query:
SELECT
    CAST(order_items.order_id AS VARCHAR)
        || '-'
        || CAST(order_items.order_item_id AS VARCHAR) AS order_item_key,
    order_items.order_id,
    order_items.order_item_id,
    orders.customer_id,
    order_items.product_id,
    order_items.seller_id,
    orders.order_status,
    orders.order_purchase_timestamp,
    orders.order_approved_at,
    order_items.shipping_limit_date,
    order_items.item_price,
    order_items.freight_value,
    order_items.item_price + order_items.freight_value AS gross_item_value
FROM {{ ref('stg_order_items') }} AS order_items
INNER JOIN {{ ref('stg_orders') }} AS orders
    ON order_items.order_id = orders.order_id

How Does the Correct Grain Work?

  • `order_item_key` combines the order identifier with its item sequence.
  • The customer key comes from the matching order.
  • The product key plus seller key come from the individual item row.
  • `gross_item_value` adds the item price to its freight value.
  • Save `models/marts/fct_order_items.sql`.
  • Confirm that `order_item_key` is the first selected field in the saved file.

Composite Key Missing?

Check that both casts target `VARCHAR`. Confirm that a hyphen sits between the two values.

Ask for help with the fact grain: help me check the order-item composite key.

Replace the temporary tests and run the full build

The old test expected `order_id` to be unique. The final test suite checks the composite key plus the links from each fact row to its dimensions.

  • Select `models/marts/schema.yml` in the Visual Studio Code file tree.
  • Replace its temporary contents with this first test section:
version: 2

models:
  - name: dim_customers
    description: Une ligne par identifiant client rattaché à une commande Olist.
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
      - name: customer_unique_id
        data_tests:
          - not_null

What Do the Customer Tests Protect?

  • The `unique` test confirms that every `customer_id` identifies one dimension row.
  • The `not_null` tests prevent missing customer identifiers from entering the dimension.
  • Save `models/marts/schema.yml`.
  • Build the customer dimension tests from the active PowerShell terminal by running:
dbt build

What Does This Checkpoint Prove?

dbt rebuilds the models in dependency order. It now validates the customer dimension without applying the discarded `order_id` uniqueness test.

You should see the models complete successfully. The customer key tests should also pass.

Customer Tests Failing?

Check the indentation below `columns`. Confirm that `dim_customers.sql` selects `customer_id` from `stg_customers`.

Ask for help with this checkpoint: help me debug the customer dimension tests.

  • Append the product plus seller dimension tests below the customer section:

  - name: dim_products
    description: Une ligne par produit avec sa catégorie source et sa traduction disponible.
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null

  - name: dim_sellers
    description: Une ligne par vendeur Olist.
    columns:
      - name: seller_id
        data_tests:
          - unique
          - not_null

Why Test Dimension Keys?

A dimension key must identify one row. These tests catch duplicate products plus duplicate sellers before those rows can multiply fact results.

  • Save `models/marts/schema.yml`.
  • Validate all three dimensions from the active PowerShell terminal by running:
dbt build

What Does This Checkpoint Prove?

The build now creates all three dimensions as tables. It checks that their tested identifiers remain unique plus populated.

You should see successful results for `dim_customers`, `dim_products` plus `dim_sellers`. Their key tests should pass.

A Dimension Test Failed?

Use the failed model name to identify the affected file. Check that each dimension selects its key from the matching staging view.

Ask for help with the failed model: help me diagnose the failing dimension test.

  • Append the fact description plus its first key tests below the seller section:

  - name: fct_order_items
    description: Une ligne par article de commande, avec les montants et les clés dimensionnelles.
    columns:
      - name: order_item_key
        data_tests:
          - unique
          - not_null
      - name: order_id
        data_tests:
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_customers')
                field: customer_id

What Changes in the Fact Tests?

  • `order_item_key` becomes the tested unique key.
  • `order_id` stays required without being treated as unique.
  • The customer relationship test checks that every fact customer exists in `dim_customers`.
  • Save `models/marts/schema.yml`.
  • Validate the new fact key plus customer relationship by running:
dbt build

What Does This Checkpoint Prove?

The composite key now passes the uniqueness rule that `order_id` could not satisfy. The customer relationship test also proves that the fact rows point to valid dimension records.

You should see the fact model build successfully. The composite key plus customer tests should pass.

Fact Key Test Still Failing?

Check that `order_item_key` appears in both the SQL model plus the YAML tests. Confirm that the SQL key includes `order_item_id`.

Ask for help with the failed key: help me debug the order-item grain test.

  • Append the remaining fact relationships plus amount tests below the customer test:
      - name: product_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_products')
                field: product_id
      - name: seller_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_sellers')
                field: seller_id
      - name: item_price
        data_tests:
          - not_null
      - name: freight_value
        data_tests:
          - not_null

What Does the Final Test Layer Cover?

  • The product relationship checks every `product_id` against `dim_products`.
  • The seller relationship checks every `seller_id` against `dim_sellers`.
  • The amount tests prevent missing item prices plus freight values.
  • Save `models/marts/schema.yml`.

Before you run the final check, do you expect the composite fact key plus all three dimension relationships to pass?

  • Build the complete star schema from the active PowerShell terminal by running:
dbt build

What Does the Final Build Run?

dbt follows the dependency graph from sources through staging views to mart tables. It runs the associated data tests after each required parent is ready.

You should see six staging views plus four mart tables complete successfully in DuckDB. The command should finish with zero test errors.

Final Build Not Passing?

Check the first failed model or test in the terminal output. A model failure usually points to a filename or column mismatch.

Close any database viewer that still holds `olist.duckdb` open. A second process can prevent dbt from accessing the file.

Ask for help with the output: help me diagnose my final dbt build failure.

✔️ Awesome, I've got everything!

Your build is passing. Double-check that every changed file is saved before moving on.

ⓧ I'd like to double check the full code

SELECT
    product_id,
    product_category_name
FROM {{ source('olist_raw', 'products') }}
SELECT
    seller_id,
    seller_zip_code_prefix,
    seller_city,
    seller_state
FROM {{ source('olist_raw', 'sellers') }}
SELECT
    product_category_name,
    product_category_name_english
FROM {{ source('olist_raw', 'product_category_translation') }}
SELECT
    customer_id,
    customer_unique_id,
    customer_zip_code_prefix,
    customer_city,
    customer_state
FROM {{ ref('stg_customers') }}
SELECT
    products.product_id,
    products.product_category_name,
    CASE
        WHEN translations.product_category_name_english IS NOT NULL
            THEN translations.product_category_name_english
        ELSE products.product_category_name
    END AS product_category
FROM {{ ref('stg_products') }} AS products
LEFT JOIN {{ ref('stg_category_translation') }} AS translations
    ON products.product_category_name = translations.product_category_name
SELECT
    seller_id,
    seller_zip_code_prefix,
    seller_city,
    seller_state
FROM {{ ref('stg_sellers') }}
SELECT
    CAST(order_items.order_id AS VARCHAR)
        || '-'
        || CAST(order_items.order_item_id AS VARCHAR) AS order_item_key,
    order_items.order_id,
    order_items.order_item_id,
    orders.customer_id,
    order_items.product_id,
    order_items.seller_id,
    orders.order_status,
    orders.order_purchase_timestamp,
    orders.order_approved_at,
    order_items.shipping_limit_date,
    order_items.item_price,
    order_items.freight_value,
    order_items.item_price + order_items.freight_value AS gross_item_value
FROM {{ ref('stg_order_items') }} AS order_items
INNER JOIN {{ ref('stg_orders') }} AS orders
    ON order_items.order_id = orders.order_id
version: 2

models:
  - name: dim_customers
    description: Une ligne par identifiant client rattaché à une commande Olist.
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
      - name: customer_unique_id
        data_tests:
          - not_null

  - name: dim_products
    description: Une ligne par produit avec sa catégorie source et sa traduction disponible.
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null

  - name: dim_sellers
    description: Une ligne par vendeur Olist.
    columns:
      - name: seller_id
        data_tests:
          - unique
          - not_null

  - name: fct_order_items
    description: Une ligne par article de commande, avec les montants et les clés dimensionnelles.
    columns:
      - name: order_item_key
        data_tests:
          - unique
          - not_null
      - name: order_id
        data_tests:
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_customers')
                field: customer_id
      - name: product_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_products')
                field: product_id
      - name: seller_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_sellers')
                field: seller_id
      - name: item_price
        data_tests:
          - not_null
      - name: freight_value
        data_tests:
          - not_null

That is the grain problem solved. Your tested star schema is ready for a single refresh command that reloads the sources plus rebuilds every model.

Automate the Full Refresh

Votre schéma en étoile passe maintenant tous ses tests dbt. Les tables analytiques sont stockées dans DuckDB.

Le rafraîchissement demande encore plusieurs commandes séparées. Un script Python va recharger les sources. Il exécutera ensuite le DAG. Il arrêtera le traitement lorsqu’un test échoue. Il affichera enfin les indicateurs du dashboard.

In this step, get ready to:
  • Créer une commande unique pour recharger les neuf tables raw.
  • Exécuter le build dbt depuis le dossier du projet.
  • Afficher trois indicateurs prêts pour un dashboard.
Créer le point d’entrée automatisé

Le script doit retrouver le dossier olist-pipeline depuis son propre emplacement. Cette référence permet à dbt de lancer son build depuis le bon dossier.

  • Sélectionnez l’icône de création de fichier dans la barre latérale des fichiers.
  • Nommez le nouveau fichier refresh.py.
  • Collez ce premier bloc dans refresh.py:
from pathlib import Path
import subprocess

import duckdb

from ingest import DATABASE_PATH, load_raw

ROOT = Path(__file__).resolve().parent


def run_dbt() -> None:
    subprocess.run(["dbt", "build"], cwd=ROOT, check=True)

Que fait ce code ?

  • La constante ROOT contient le chemin absolu du dossier olist-pipeline.
  • La fonction run_dbt() lance le build depuis ce dossier.
  • L’option check=True interrompt le script si le build renvoie un échec.
  • Enregistrez refresh.py.
  • Vérifiez que l’éditeur ne souligne aucune ligne en rouge.

Une importation est-elle soulignée ?

Vérifiez que le terminal PowerShell affiche toujours l’environnement .venv actif. Confirmez aussi que refresh.py se trouve à côté de ingest.py.

Demandez de l’aide pour corriger les importations de refresh.py.

Le premier flux automatisé peut maintenant appeler load_raw(). Il imprime chaque comptage avant de confier les données au build dbt.

  • Ajoutez ce bloc sous run_dbt() dans refresh.py:
def main() -> None:
    counts = load_raw()
    for name, count in counts.items():
        print(f"raw.{name}: {count:,} lignes")

    run_dbt()


if __name__ == "__main__":
    main()

Comment fonctionne l’orchestration ?

  • La fonction main() récupère les comptages produits par load_raw().
  • La boucle imprime une ligne pour chaque table du schéma raw.
  • L’appel à run_dbt() reconstruit les vues de staging. Il reconstruit ensuite les tables analytiques. Les tests s’exécutent dans le même DAG.
  • Enregistrez refresh.py.

Avant cette première exécution, quel résultat devrait apparaître en premier dans le terminal ?

  • Testez le premier flux automatisé en exécutant cette commande dans le terminal PowerShell actif:
python refresh.py

Que prouve cette exécution ?

Vous devriez voir neuf lignes commençant par raw. avec un comptage positif. Le build dbt doit ensuite reconstruire le DAG sans erreur de test.

Cette séquence prouve que l’ingestion libère sa connexion avant le démarrage de dbt.

🙋‍♀️ La base est-elle verrouillée ?

Si le terminal mentionne IO Error: Could not set lock on file, fermez tout outil qui consulte olist.duckdb. Relancez ensuite la commande.

Demandez de l’aide pour identifier le processus qui verrouille olist.duckdb.

Ajouter le résumé des indicateurs

Le build valide la structure analytique. Le résumé final transforme cette réussite technique en trois contrôles directement lisibles par une équipe dashboard.

  • Insérez cette fonction entre run_dbt() et main() dans refresh.py:
def show_summary() -> None:
    with duckdb.connect(str(DATABASE_PATH), read_only=True) as connection:
        order_count, item_count, gross_sales_value = connection.execute(
            """
            SELECT
                COUNT(DISTINCT order_id),
                COUNT(*),
                SUM(gross_item_value)
            FROM analytics.fct_order_items
            """
        ).fetchone()

    print("\nRésumé prêt pour le dashboard")
    print(f"Commandes: {order_count:,}")
    print(f"Lignes de commande: {item_count:,}")
    print(f"Valeur brute: {gross_sales_value:,.2f}")

Quels indicateurs sont calculés ?

  • La connexion en lecture seule consulte la base après la fermeture des écritures d’ingestion.
  • Le premier calcul compte les order_id distincts.
  • Le deuxième calcul compte les lignes au grain article de commande.
  • Le troisième calcul additionne gross_item_value.
  • Enregistrez refresh.py.
  • Vérifiez que l’éditeur ne signale aucune erreur de syntaxe dans show_summary().

Voyez-vous une erreur d’indentation ?

Alignez le bloc with sous la définition de show_summary(). Gardez les lignes print au même niveau que le bloc with.

Demandez de l’aide pour corriger l’indentation de show_summary().

Il reste à placer le résumé après le build. Cet ordre garantit que les indicateurs proviennent uniquement de modèles validés.

  • Dans main(), repérez cette ligne:
    run_dbt()

Pourquoi utiliser cette ligne comme repère ?

Cette ligne exécute les modèles. Elle exécute aussi leurs tests avant toute lecture des indicateurs.

  • Ajoutez l’appel à show_summary() directement sous cette ligne pour obtenir ce bloc:
    run_dbt()
    show_summary()

Pourquoi cet ordre arrête-t-il les erreurs ?

L’appel à show_summary() s’exécute uniquement après la réussite de run_dbt(). Grâce à check=True, un build en échec interrompt le script avant le résumé.

  • Enregistrez la version finale de refresh.py.

✔️ Awesome, I've got everything!

Parfait. Votre fichier orchestre maintenant l’ingestion. Il exécute le build. Il affiche ensuite le résumé.

ⓧ I'd like to double check the full code

Le fichier de référence ci-dessous correspond à l’état final de refresh.py.

from pathlib import Path
import subprocess

import duckdb

from ingest import DATABASE_PATH, load_raw

ROOT = Path(__file__).resolve().parent


def run_dbt() -> None:
    subprocess.run(["dbt", "build"], cwd=ROOT, check=True)


def show_summary() -> None:
    with duckdb.connect(str(DATABASE_PATH), read_only=True) as connection:
        order_count, item_count, gross_sales_value = connection.execute(
            """
            SELECT
                COUNT(DISTINCT order_id),
                COUNT(*),
                SUM(gross_item_value)
            FROM analytics.fct_order_items
            """
        ).fetchone()

    print("\nRésumé prêt pour le dashboard")
    print(f"Commandes: {order_count:,}")
    print(f"Lignes de commande: {item_count:,}")
    print(f"Valeur brute: {gross_sales_value:,.2f}")


def main() -> None:
    counts = load_raw()
    for name, count in counts.items():
        print(f"raw.{name}: {count:,} lignes")

    run_dbt()
    show_summary()


if __name__ == "__main__":
    main()

Comment lire cette référence ?

Le fichier suit un ordre unique. Il recharge les sources. Il valide le DAG. Il ouvre enfin la base en lecture seule pour calculer les indicateurs.

Valider un remplacement de CSV

Un rafraîchissement reproductible doit accepter une nouvelle copie d’un fichier source tant que son nom canonique reste identique. Son schéma doit aussi rester compatible.

Avant l’exécution complète, quelles étapes du pipeline devraient apparaître dans le terminal ?

  • Lancez le rafraîchissement complet depuis le terminal PowerShell actif avec cette commande:
python refresh.py

Que devez-vous voir ?

Vous devriez voir les neuf comptages raw. Le build dbt doit ensuite se terminer sans erreur de test.

Le terminal doit finir par afficher Résumé prêt pour le dashboard. Trois lignes présentent les commandes. Elles présentent aussi les lignes de commande. Elles présentent enfin la valeur brute.

🙋‍♀️ Le résumé est-il absent ?

Cherchez d’abord un échec dans la sortie du build dbt. Le comportement est volontaire puisque le script protège le résumé contre des modèles invalides.

Si le build réussit, vérifiez que show_summary() se trouve directement sous run_dbt() dans main().

Demandez de l’aide pour diagnostiquer l’absence du résumé KPI.

  • Développez le dossier data/raw dans la barre latérale des fichiers.
  • Remplacez l’un des neuf CSV par une nouvelle copie utilisant le même schéma.
  • Conservez exactement son nom canonique dans data/raw.

Avant la relance, pensez-vous que le pipeline reconstruira seulement la table remplacée ou toutes les tables raw ?

  • Relancez le rafraîchissement complet avec la même commande:
python refresh.py

Comment confirmer le rafraîchissement ?

Vous devriez revoir les neuf comptages. Cela confirme que load_raw() reconstruit chaque table raw à chaque exécution.

Le build dbt doit réussir de nouveau. Le résumé final doit refléter le contenu actuel des CSV.

C’est fait. Une seule commande reconstruit maintenant votre source analytique. Elle bloque aussi tout résumé basé sur des modèles en échec.

Secret mission

Add a Daily Sales Mart

Cette mission ajoute une table de ventes quotidiennes directement exploitable par un dashboard. Chaque ligne représentera une date d’achat. Le modèle calculera le nombre de commandes, le nombre d’articles et la valeur brute des ventes.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep the local analytics files, pause your current session, or delete the generated outputs. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • The Python virtual environment in the .venv folder.
  • The DuckDB database file olist.duckdb. It contains nine raw tables, six staging views, analytics.dim_customers, analytics.dim_products, analytics.dim_sellers, analytics.fct_order_items, and analytics.agg_daily_sales.
  • The generated target folder containing dbt build artifacts.

Keep everything running

No action needed. Choose this if you want to connect a dashboard tool or continue developing the pipeline.

  • Keep the .venv folder so the pinned packages remain available.
  • Keep olist.duckdb so the tested raw, staging, and analytics layers remain available.
  • Keep the target folder if you want to inspect the most recent build artifacts.
  • Use olist.duckdb later as the local source for Power BI or another BI tool.

Pause - I'll come back to this later

Shut down the active session while keeping every project file. You can return to the same database and virtual environment later.

  • Close any database viewer connected to olist.duckdb.
  • Close the active PowerShell terminal in VS Code.
  • Confirm that no terminal tab still shows the active .venv environment.

Your local files remain ready for the next session. No project process continues in the background.

Delete - I don't want to use this again

Remove the generated project resources while keeping the code and CSV inputs needed for a fresh rebuild. Choose this when you want to recover local disk space.

Your Source Files Stay Safe

Deleting these outputs removes the local database and installed project packages. Your code and nine CSV files stay inside olist-pipeline.

  • Close the active PowerShell terminal in VS Code.
  • Close any database viewer connected to olist.duckdb.
  • Select .venv in the VS Code file tree.
  • Press Delete.
  • Approve the confirmation prompt.
  • Select olist.duckdb in the VS Code file tree.
  • Press Delete.
  • Approve the confirmation prompt.

That clears the package environment and local database. Your code and CSV files remain in place.

  • Select target in the VS Code file tree.
  • Press Delete.
  • Approve the confirmation prompt.
  • Confirm that .venv, olist.duckdb, and target no longer appear in the file tree.

You should still see requirements.txt, ingest.py, refresh.py, models, and data in olist-pipeline. The generated environment, database, and build artifacts are gone.

Nice Work!

Nice Work!

Mission accomplie ! Tu as construit un pipeline analytique local qui transforme les données Olist en indicateurs prêts pour un dashboard.

Tu as appris à :

  • Transformer neuf fichiers CSV en une couche raw persistante dans DuckDB. Le pipeline peut reconstruire cette couche à chaque rafraîchissement.
  • Exposer un problème de grain grâce à l'échec attendu d'un test dbt. Tu as ensuite construit un schéma en étoile au grain ligne de commande. Ses clés dimensionnelles sont contrôlées automatiquement.
  • Automatiser le rafraîchissement complet avec Python. Une seule commande recharge les sources. Elle exécute aussi le DAG et affiche les indicateurs de la table de faits.
  • Secret Mission : ajouter agg_daily_sales pour fournir les commandes, les articles et la valeur brute au grain quotidien.

Ton pipeline est maintenant reproductible. Il peut alimenter un futur outil BI sans refaire les imports ni les jointures manuellement.

Prêt à tester tes connaissances ?