Automatiser un pipeline NYC Taxi

Créez un ETL mensuel NYC Taxi avec DuckDB et GitHub Actions.

Introduction

30 Second Summary

Chaque mois, de nouveaux trajets de taxi arrivent sous la forme de millions de lignes. Sans processus régulier, comparer les périodes devient vite un travail manuel difficile à reproduire.

Dans ce projet, vous allez construire un pipeline ETL mensuel pour les données Yellow Taxi de New York. GitHub Actions exécutera automatiquement tout le parcours du fichier Parquet brut à une base DuckDB propre accompagnée de rapports CSV.

What You'll Build

Une exécution affiche le mois traité dans le terminal avant de livrer des rapports sur le revenu moyen, la durée moyenne et les zones de départ les plus actives.

À la fin de ce projet, vous aurez :

  • Un pipeline mensuel que vous pouvez lancer pour voir le mois source accompagné de son nombre de courses propres.
  • Des rapports métier qui présentent le revenu moyen par course, la durée moyenne et les dix zones de départ les plus actives.
  • Une automatisation mensuelle qui publie la base et les rapports dans un artefact téléchargeable après chaque exécution.
  • Mission secrète : ajouter une entrée manuelle pour retraiter un mois précis au format YYYY-MM sans modifier le code.

Y A-t-il des Prérequis ?

Aucune connaissance préalable en ingénierie des données n’est nécessaire. Vous avez besoin d’un PC Windows avec Python 3.11 ou plus, Visual Studio Code, Git et un compte GitHub.

Before We Start

Before the hands-on work begins, this is your moment to define what the monthly data pipeline does and why automation matters. A clear goal connects every later decision to a useful result.

Set Up the Repository and Environment

Before a monthly pipeline can run unattended, its local setup has to be repeatable. An isolated environment keeps project dependencies separate from the rest of your computer.

Your public GitHub repository gives the project a shared home. A local Git repository tracks your files. A Python virtual environment pins DuckDB to 2.0.0 before you write any source code.

In this step, get ready to:
  • Create an empty public repository on GitHub.
  • Connect the local project through Git inside Visual Studio Code.
  • Install DuckDB 2.0.0 inside an isolated Python environment.
Create the empty GitHub repository

GitHub handles your sign-in on its own page. Your credentials never enter the project files.

  • Go to GitHub in your browser.
  • Sign in to your GitHub account.
  • Select New repository.
  • Enter nyc-taxi-pipeline as the repository name.

GitHub checks the name as you type. You should see confirmation that the repository name is available.

The repository must start empty. GitHub can add starter files during creation.

  • Choose the public visibility option.
  • Leave every initialization option unselected.
  • Select Create repository.
  • Copy the HTTPS repository URL from the empty repository page.

You have the remote home in place. The empty repository is ready to receive your local project.

The local folder belongs on your Desktop so you can find it easily throughout the project.

  • Press the Windows key to open Windows search.
  • Type Windows PowerShell.
  • Press Enter to open PowerShell.
  • Move to your Desktop by running this command:
Set-Location -Path "$HOME\Desktop"

What does this command do?

PowerShell changes its current location to the Desktop inside your Windows profile. The next command creates the project folder there.

Your PowerShell prompt should now show a path ending with Desktop.

  • Create the local project folder by running these commands:
New-Item -Path "nyc-taxi-pipeline" -ItemType Directory
Set-Location -Path "nyc-taxi-pipeline"

What do these commands do?

  • The first command creates the nyc-taxi-pipeline folder on your Desktop.
  • The second command moves PowerShell into that folder so every following command targets this project.

You should see the new folder in the command output. Your prompt should end with nyc-taxi-pipeline.

Connect Git and add the project files

The copied repository URL tells Git where this local project belongs. The remote name origin becomes the destination used later when you publish the pipeline.

  • Replace REMOTE-URL below with the HTTPS repository URL you copied.
  • Initialize the main branch through the updated commands:
git init -b main
git remote add origin REMOTE-URL
git remote -v

What do these commands do?

  • The first command turns nyc-taxi-pipeline into a Git repository on the main branch.
  • The second command connects the local repository to GitHub under the name origin.
  • The final command prints the saved remote so you can confirm the connection.

You should see two origin entries that use your GitHub repository URL. One entry is used for fetching. The other is used for pushing.

Remote connection not listed?

  • Confirm that you replaced REMOTE-URL with the complete HTTPS URL.
  • Confirm that PowerShell still shows the nyc-taxi-pipeline folder in its prompt.

Need help with the connection? Help me diagnose why my local Git repository is not showing its GitHub origin remote.

Visual Studio Code gives you one place to manage the two project files. You will keep PowerShell available for environment commands.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code.
  • Press Enter to open Visual Studio Code.

You should see the Visual Studio Code welcome screen.

  • Select File from the top menu.
  • Select Open Folder.

The Windows folder picker should now be open.

  • Select the nyc-taxi-pipeline folder on your Desktop.
  • Confirm the folder selection.

You should see nyc-taxi-pipeline at the top of the Explorer sidebar.

The dependency file records the exact DuckDB release used by the pipeline. This makes future installations reproducible.

  • Select the new-file icon in the Explorer sidebar.
  • Type requirements.txt.
  • Press Enter.

You should see an empty requirements.txt tab in the editor.

  • Add the pinned dependency by copying this line into requirements.txt:
duckdb==2.0.0

What does this dependency do?

The version pin tells Python to install DuckDB 2.0.0. Every environment built from this file receives the same database engine version.

  • Save requirements.txt.
  • Confirm that the Explorer sidebar lists requirements.txt.

Dependency file not visible?

  • Confirm that the file name ends with .txt.
  • Confirm that the file sits directly inside nyc-taxi-pipeline.

Still stuck? Help me find why requirements.txt is not visible in my VS Code project folder.

Generated data can be large. The ignore file keeps virtual-environment files, raw downloads, databases, and reports out of Git history.

  • Select the new-file icon in the Explorer sidebar.
  • Type .gitignore.
  • Press Enter.

You should see an empty .gitignore tab in the editor.

  • Add the ignore rules by copying this content into .gitignore:
.venv/
__pycache__/
*.pyc
data/raw/
data/*.duckdb
reports/*.csv

What do these rules protect?

  • The first three rules exclude the virtual environment and generated Python cache files.
  • The remaining rules exclude downloaded source data, generated DuckDB databases, and exported CSV reports.
  • Save .gitignore.
  • Confirm that the Explorer sidebar lists .gitignore.

Ignore file has the wrong name?

  • Confirm that the file name begins with a dot.
  • Remove any extra .txt extension from the file name.

Need another pair of eyes? Help me check whether my .gitignore file has the correct name and location.

Use this comparison to confirm that both starter files match the project exactly.

✔️ Awesome, I've got everything!

Both starter files are saved inside nyc-taxi-pipeline.

ⓧ I'd like to double check the full code

Your complete requirements.txt file should match this reference.

duckdb==2.0.0

Requirements checkpoint

This complete file contains one dependency pin. DuckDB must remain at 2.0.0.

Your complete .gitignore file should match this reference.

.venv/
__pycache__/
*.pyc
data/raw/
data/*.duckdb
reports/*.csv

Ignore rules checkpoint

This complete file contains all six ignore rules. Each path matches an environment file or generated pipeline output.

Project checkpoint

Your local repository now contains only requirements.txt and .gitignore as project files. The source scripts, database, raw data, reports, README, and workflow come in later steps.

Create and verify the virtual environment

A virtual environment gives this repository its own Python packages. The pinned dependency stays isolated from packages used by other projects.

  • Switch back to the PowerShell window from earlier.
  • Create the virtual environment by running this command:
python -m venv .venv

What does this command do?

Python creates a .venv folder inside nyc-taxi-pipeline. Windows places the environment's Python executable inside its Scripts folder.

The PowerShell prompt should return without an error. Visual Studio Code should list a new .venv folder in the Explorer sidebar.

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

What does this command do?

The command uses the Python executable inside .venv. Pip reads requirements.txt and installs the pinned DuckDB package into that isolated environment.

You should see installation progress in PowerShell. The command prompt returns when the dependency is ready.

  • Check the installed package metadata by running this command:
.\.venv\Scripts\python.exe -m pip show duckdb

What does this check show?

Pip prints the package metadata from the new virtual environment. The version entry lets you confirm that the installation followed requirements.txt.

✔️ I see version 2.0.0

The version pin worked. Your virtual environment contains the exact DuckDB release required by the project.

ⓧ I see a different version

A different version means the saved dependency file does not match the intended environment state.

  • Confirm that requirements.txt contains duckdb==2.0.0.
  • Save requirements.txt.
  • Install the pinned dependency again by running this command:
.\.venv\Scripts\python.exe -m pip install -r requirements.txt

Why run the installation again?

Pip reads the corrected dependency pin. The virtual environment can then use DuckDB 2.0.0.

ⓧ The package is missing

A missing package usually means the environment creation or dependency installation did not finish.

  • Rebuild the environment dependency by running these commands:
python -m venv .venv
.\.venv\Scripts\python.exe -m pip install -r requirements.txt

What does this recovery do?

The first command ensures the virtual environment exists. The second command installs the dependency through that environment's Python executable.

Before you run the final check, ask yourself whether the isolated Python executable can import DuckDB successfully.

  • Verify the DuckDB import by running this command:
.\.venv\Scripts\python.exe -c "import duckdb; print('DuckDB prêt')"

What does this check prove?

The virtual environment's Python executable imports DuckDB. It prints a short confirmation only after the import succeeds.

You should see DuckDB prêt in PowerShell. That is the setup complete: the virtual environment can import the exact database package your pipeline depends on.

Import check not working?

  • Confirm that your PowerShell prompt ends with nyc-taxi-pipeline.
  • Confirm that requirements.txt is saved before reinstalling dependencies.
  • Confirm that the command uses the Python executable inside .venv.

Still seeing a failure? Help me debug why DuckDB does not import from my Windows virtual environment.

Your repository and isolated environment are ready. Next, you'll download the latest TLC Parquet and inspect its raw row count.

Download and Inspect Source Data

Your public GitHub repository is connected to your local project. Your isolated Python environment can import DuckDB 2.0.0 successfully.

A monthly ETL pipeline needs to locate the latest published source without relying on a fixed month. In this step, you will download a Yellow Taxi Parquet file. You will query its row count directly from disk.

In this step, get ready to:
  • Calculate recent source months for the Yellow Taxi download.
  • Retry an earlier month when the requested file returns HTTP 404.
  • Count the raw Parquet rows with an in-memory DuckDB connection.
Define source paths and month labels

The source module needs one reliable location for downloaded files. It also needs a way to calculate earlier months from the current date.

  • In the file sidebar of Visual Studio Code, create a folder named src inside nyc-taxi-pipeline.

You should see the new src folder in the file sidebar.

  • Create a file named source.py inside src.
  • Build the source configuration in src/source.py by pasting this code:
from datetime import date
from pathlib import Path
from urllib.error import HTTPError
from urllib.request import urlretrieve

import duckdb

ROOT = Path(__file__).resolve().parents[1]
RAW_DIR = ROOT / "data" / "raw"
TRIP_URL_TEMPLATE = (
    "https://d37ci6vzurychx.cloudfront.net/trip-data/"
    "yellow_tripdata_{month}.parquet"
)
ZONE_LOOKUP_URL = (
    "https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv"
)


def month_label(reference: date, months_back: int) -> str:
    absolute_month = reference.year * 12 + reference.month - 1 - months_back
    year, month_index = divmod(absolute_month, 12)
    return f"{year:04d}-{month_index + 1:02d}"


def sql_path(path: Path) -> str:
    return path.resolve().as_posix().replace("'", "''")

What does this code do?

  • The URL templates point to the official Yellow Taxi trip files and zone lookup file.
  • The month_label() function moves backward through calendar months. Its output always uses the YYYY-MM format.
  • The sql_path() function converts a local path into a form that can be embedded safely in the project’s SQL strings.
  • Save src/source.py.
  • Confirm that source.py remains visible inside the src folder.

File missing from the sidebar?

Confirm that you created source.py inside the existing nyc-taxi-pipeline/src folder. A similarly named file outside that folder will not run from the project command.

Still stuck? Help me locate or create src/source.py in my nyc-taxi-pipeline project.

Download and inspect the latest month

TLC source files are published by month. The downloader starts with a recent candidate. An HTTP 404 response makes it try an earlier candidate.

  • Add the fallback downloader below sql_path() in src/source.py by pasting this code:
def download_latest_trip() -> tuple[str, Path, str]:
    RAW_DIR.mkdir(parents=True, exist_ok=True)

    for months_back in range(2, 14):
        month = month_label(date.today(), months_back)
        url = TRIP_URL_TEMPLATE.format(month=month)
        destination = RAW_DIR / f"yellow_tripdata_{month}.parquet"
        temporary = destination.with_suffix(".parquet.part")

        try:
            temporary.unlink(missing_ok=True)
            print(f"Tentative : {url}")
            urlretrieve(url, str(temporary))
            temporary.replace(destination)
            return month, destination, url
        except HTTPError as error:
            temporary.unlink(missing_ok=True)
            if error.code == 404:
                continue
            raise

    raise RuntimeError("Aucun fichier Yellow Taxi récent n'a été trouvé.")

How does the fallback work?

  • The loop generates candidate months from newest to oldest.
  • Each download first uses a temporary .parquet.part file. A successful download replaces the final Parquet atomically.
  • A missing monthly file advances the loop to the next candidate.
  • Any other download failure stops the script. This preserves errors that need attention.
  • Save src/source.py.
  • Confirm that the complete download_latest_trip() function appears below sql_path().

Function indentation looks uneven?

Keep the loop inside download_latest_trip(). Keep the try block inside the loop.

Need help checking the structure? Help me verify the indentation in download_latest_trip().

DuckDB can scan the Parquet file directly. The script only asks for COUNT(*), so it does not build a Python collection containing every trip.

  • Append the inspection function and script entry point below download_latest_trip() by pasting this code:
def inspect_source(trip_path: Path) -> int:
    parquet_path = sql_path(trip_path)
    with duckdb.connect() as connection:
        row = connection.execute(
            f"SELECT COUNT(*) FROM read_parquet('{parquet_path}')"
        ).fetchone()
    if row is None:
        raise RuntimeError("Le comptage du Parquet n'a retourné aucun résultat.")
    return row[0]


def main() -> None:
    month, trip_path, source_url = download_latest_trip()
    raw_rows = inspect_source(trip_path)
    print(f"Mois source : {month}")
    print(f"URL source : {source_url}")
    print(f"Lignes brutes : {raw_rows:,}")
    print("État : données brutes seulement")


if __name__ == "__main__":
    main()

What does the inspection prove?

  • The empty duckdb.connect() call creates an in-memory connection for this inspection.
  • The Parquet reader counts rows directly from the downloaded file.
  • The main() function prints the selected month. It also prints the source URL.
  • The final status identifies the current result as raw source data.
  • Save src/source.py.

Before you run the script, do you think a raw row count is enough to support a reliable monthly report?

  • Switch back to the Windows PowerShell terminal from earlier.
  • Download and inspect the latest available month by running this command:
.\.venv\Scripts\python.exe src\source.py

What should you see?

You will see one or more Tentative : lines. Multiple attempts show that the fallback moved past an unavailable month.

The successful output includes Mois source :, URL source :, and Lignes brutes :. The run ends with État : données brutes seulement.

That final status exposes the intended limitation. You know the source volume. You do not yet have cleaned trips, persistent storage, or business metrics.

Download stops before the row count?

  • Confirm that your internet connection can reach the TLC source.
  • Confirm that PowerShell is still inside the local nyc-taxi-pipeline folder.
  • Compare the URL templates in source.py with the code above.

Still blocked? Help me diagnose why src/source.py stops before printing the raw row count.

Add the zone lookup downloader

Trip rows identify pickup areas with numeric location IDs. The source module keeps the official zone lookup download beside the monthly trip downloader.

  • Return to src/source.py in Visual Studio Code.
  • Insert the zone lookup function immediately above inspect_source() by pasting this code:
def download_zone_lookup() -> Path:
    RAW_DIR.mkdir(parents=True, exist_ok=True)
    destination = RAW_DIR / "taxi_zone_lookup.csv"
    temporary = destination.with_suffix(".csv.part")
    temporary.unlink(missing_ok=True)
    urlretrieve(ZONE_LOOKUP_URL, str(temporary))
    temporary.replace(destination)
    return destination

Why keep this downloader here?

  • The function stores the zone lookup in the same raw-data directory as the trip file.
  • The temporary-file pattern prevents an interrupted download from replacing a complete lookup file.
  • The returned path gives later pipeline code a precise file location.
  • Save src/source.py.

Before you rerun the source inspection, do you expect the raw trip count to change because the unused lookup helper is now present?

  • Verify the completed source module by running this command in PowerShell:
.\.venv\Scripts\python.exe src\source.py

Your source inspection is working

You should see a source month in YYYY-MM format. You should also see a positive raw row count.

The last line remains État : données brutes seulement because this step only inspects the source. That is your first live data result from the monthly Yellow Taxi pipeline.

Still not reaching the final status?

  • Confirm that download_zone_lookup() appears above inspect_source().
  • Confirm that every function begins at the left edge of source.py.
  • Rerun the command after saving the file.

Need another set of eyes? Help me compare my completed src/source.py with the expected source module.

✔️ Awesome, I've got everything!

Your source module now matches the completed version for this step.

ⓧ I'd like to double check the full code

  • Compare your saved src/source.py with this complete reference:
from datetime import date
from pathlib import Path
from urllib.error import HTTPError
from urllib.request import urlretrieve

import duckdb

ROOT = Path(__file__).resolve().parents[1]
RAW_DIR = ROOT / "data" / "raw"
TRIP_URL_TEMPLATE = (
    "https://d37ci6vzurychx.cloudfront.net/trip-data/"
    "yellow_tripdata_{month}.parquet"
)
ZONE_LOOKUP_URL = (
    "https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv"
)


def month_label(reference: date, months_back: int) -> str:
    absolute_month = reference.year * 12 + reference.month - 1 - months_back
    year, month_index = divmod(absolute_month, 12)
    return f"{year:04d}-{month_index + 1:02d}"


def sql_path(path: Path) -> str:
    return path.resolve().as_posix().replace("'", "''")


def download_latest_trip() -> tuple[str, Path, str]:
    RAW_DIR.mkdir(parents=True, exist_ok=True)

    for months_back in range(2, 14):
        month = month_label(date.today(), months_back)
        url = TRIP_URL_TEMPLATE.format(month=month)
        destination = RAW_DIR / f"yellow_tripdata_{month}.parquet"
        temporary = destination.with_suffix(".parquet.part")

        try:
            temporary.unlink(missing_ok=True)
            print(f"Tentative : {url}")
            urlretrieve(url, str(temporary))
            temporary.replace(destination)
            return month, destination, url
        except HTTPError as error:
            temporary.unlink(missing_ok=True)
            if error.code == 404:
                continue
            raise

    raise RuntimeError("Aucun fichier Yellow Taxi récent n'a été trouvé.")


def download_zone_lookup() -> Path:
    RAW_DIR.mkdir(parents=True, exist_ok=True)
    destination = RAW_DIR / "taxi_zone_lookup.csv"
    temporary = destination.with_suffix(".csv.part")
    temporary.unlink(missing_ok=True)
    urlretrieve(ZONE_LOOKUP_URL, str(temporary))
    temporary.replace(destination)
    return destination


def inspect_source(trip_path: Path) -> int:
    parquet_path = sql_path(trip_path)
    with duckdb.connect() as connection:
        row = connection.execute(
            f"SELECT COUNT(*) FROM read_parquet('{parquet_path}')"
        ).fetchone()
    if row is None:
        raise RuntimeError("Le comptage du Parquet n'a retourné aucun résultat.")
    return row[0]


def main() -> None:
    month, trip_path, source_url = download_latest_trip()
    raw_rows = inspect_source(trip_path)
    print(f"Mois source : {month}")
    print(f"URL source : {source_url}")
    print(f"Lignes brutes : {raw_rows:,}")
    print("État : données brutes seulement")


if __name__ == "__main__":
    main()

Your pipeline can now find a published month and download its Parquet file. Next, you will turn those raw rows into a persistent table of valid trips.

Clean and Load Taxi Trips

Your raw Parquet check proved that the latest Yellow Taxi file is available. It also exposed the prototype's limit because every row is still unfiltered.

A defensible ETL needs a repeatable clean layer. This step filters valid trips into a persistent DuckDB database.

Each run replaces the table for the selected month. This replacement behavior is called idempotence.

In this step, get ready to:
  • Create the persistent database paths for the pipeline.
  • Filter invalid trips with an analytical query.
  • Run the pipeline twice to prove that repeated processing does not duplicate rows.
Create persistent database paths

The pipeline needs stable destinations for its database and later outputs. All paths begin at the existing project-level ROOT from source.py.

  • In the VS Code file sidebar, create pipeline.py inside the existing src folder.
  • Define the project paths by pasting this code:
from pathlib import Path

import duckdb

from source import ROOT, download_latest_trip, sql_path

DATA_DIR = ROOT / "data"
REPORTS_DIR = ROOT / "reports"
DATABASE_PATH = DATA_DIR / "nyc_taxi.duckdb"
MONTHLY_REPORT_PATH = REPORTS_DIR / "monthly_metrics.csv"
ZONES_REPORT_PATH = REPORTS_DIR / "top_zones.csv"

What does this code set up?

  • The imported ROOT value anchors every generated file inside nyc-taxi-pipeline.
  • DATABASE_PATH identifies the persistent nyc_taxi.duckdb file.
  • MONTHLY_REPORT_PATH establishes the destination for the monthly summary.
  • ZONES_REPORT_PATH establishes the destination for the zone ranking.
  • Save src/pipeline.py.
  • Confirm pipeline.py is listed under src in the VS Code file sidebar.

Cannot find the new file?

Confirm that you created pipeline.py inside src. A file created beside the src folder cannot import source.py through this structure.

Still stuck? Help me verify the location and imports for src/pipeline.py.

Filter valid trips with SQL

The cleaning layer uses SQL to reject trips that cannot support reliable metrics. It also calculates each trip's duration directly from the pickup and drop-off timestamps.

  • In src/pipeline.py, place your cursor below ZONES_REPORT_PATH = REPORTS_DIR / "top_zones.csv".
  • Add the cleaning query by pasting this function:
def load_clean_trips(
    connection: duckdb.DuckDBPyConnection,
    trip_path: Path,
    month: str,
) -> None:
    parquet_path = sql_path(trip_path)
    connection.execute(
        f"""
        CREATE OR REPLACE TABLE clean_trips AS
        SELECT
            ? AS source_month,
            CAST(tpep_pickup_datetime AS TIMESTAMP) AS pickup_at,
            CAST(tpep_dropoff_datetime AS TIMESTAMP) AS dropoff_at,
            (
                epoch(tpep_dropoff_datetime)
                - epoch(tpep_pickup_datetime)
            ) / 60.0 AS duration_minutes,
            CAST(trip_distance AS DOUBLE) AS trip_distance,
            CAST(total_amount AS DOUBLE) AS total_amount,
            CAST(PULocationID AS INTEGER) AS pickup_location_id
        FROM read_parquet('{parquet_path}')
        WHERE tpep_dropoff_datetime > tpep_pickup_datetime
          AND trip_distance >= 0
          AND total_amount >= 0
          AND PULocationID IS NOT NULL
          AND strftime(tpep_pickup_datetime, '%Y-%m') = ?
        """,
        [month, month],
    )

How does the cleaning query work?

  • source_month records which monthly file supplied every clean row.
  • The timestamp condition removes trips whose drop-off does not occur after pickup.
  • The numeric conditions remove negative distances and negative total amounts.
  • The location condition removes rows without a pickup zone identifier.
  • strftime keeps pickups from the selected source month.
  • CREATE OR REPLACE TABLE rebuilds clean_trips on every run.
  • Save src/pipeline.py.
  • Confirm the editor shows load_clean_trips below the path constants without red error markers.

Seeing error markers in the function?

Check that the function begins at the far-left edge of the file. Confirm that the closing parenthesis after [month, month] aligns with connection.execute.

Need another pair of eyes? Help me compare load_clean_trips with the target function.

Run the persistent pipeline twice

The cleaning function needs a runnable entry point. The entry point creates the data directory before opening the persistent database.

  • Scroll to the end of load_clean_trips in src/pipeline.py.
  • Add the runnable entry point by pasting this code below the function:
def main() -> None:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    month, trip_path, source_url = download_latest_trip()
    with duckdb.connect(str(DATABASE_PATH)) as connection:
        load_clean_trips(connection, trip_path, month)
        row = connection.execute("SELECT COUNT(*) FROM clean_trips").fetchone()
    if row is None:
        raise RuntimeError("Les courses propres sont vides.")
    print(f"Mois source : {month}")
    print(f"URL source : {source_url}")
    print(f"Courses propres : {row[0]:,}")
    print(f"Base créée : {DATABASE_PATH}")


if __name__ == "__main__":
    main()

What does the entry point do?

  • DATA_DIR.mkdir ensures the data directory exists.
  • download_latest_trip selects the source month and downloads its file.
  • duckdb.connect opens the persistent database at DATABASE_PATH.
  • The count query checks how many rows survived the cleaning rules.
  • The final messages expose the source month and the database location.

✔️ Awesome, I've got everything!

Your file now contains the complete clean-load pipeline.

ⓧ I'd like to double check the full code

from pathlib import Path

import duckdb

from source import ROOT, download_latest_trip, sql_path

DATA_DIR = ROOT / "data"
REPORTS_DIR = ROOT / "reports"
DATABASE_PATH = DATA_DIR / "nyc_taxi.duckdb"
MONTHLY_REPORT_PATH = REPORTS_DIR / "monthly_metrics.csv"
ZONES_REPORT_PATH = REPORTS_DIR / "top_zones.csv"


def load_clean_trips(
    connection: duckdb.DuckDBPyConnection,
    trip_path: Path,
    month: str,
) -> None:
    parquet_path = sql_path(trip_path)
    connection.execute(
        f"""
        CREATE OR REPLACE TABLE clean_trips AS
        SELECT
            ? AS source_month,
            CAST(tpep_pickup_datetime AS TIMESTAMP) AS pickup_at,
            CAST(tpep_dropoff_datetime AS TIMESTAMP) AS dropoff_at,
            (
                epoch(tpep_dropoff_datetime)
                - epoch(tpep_pickup_datetime)
            ) / 60.0 AS duration_minutes,
            CAST(trip_distance AS DOUBLE) AS trip_distance,
            CAST(total_amount AS DOUBLE) AS total_amount,
            CAST(PULocationID AS INTEGER) AS pickup_location_id
        FROM read_parquet('{parquet_path}')
        WHERE tpep_dropoff_datetime > tpep_pickup_datetime
          AND trip_distance >= 0
          AND total_amount >= 0
          AND PULocationID IS NOT NULL
          AND strftime(tpep_pickup_datetime, '%Y-%m') = ?
        """,
        [month, month],
    )


def main() -> None:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    month, trip_path, source_url = download_latest_trip()
    with duckdb.connect(str(DATABASE_PATH)) as connection:
        load_clean_trips(connection, trip_path, month)
        row = connection.execute("SELECT COUNT(*) FROM clean_trips").fetchone()
    if row is None:
        raise RuntimeError("Les courses propres sont vides.")
    print(f"Mois source : {month}")
    print(f"URL source : {source_url}")
    print(f"Courses propres : {row[0]:,}")
    print(f"Base créée : {DATABASE_PATH}")


if __name__ == "__main__":
    main()
  • Save src/pipeline.py.

Before you run the pipeline, do you expect the clean trip count to match the raw count exactly?

  • Create the persistent clean table by running this command in the PowerShell terminal from earlier:
.\.venv\Scripts\python.exe src\pipeline.py

What should you see?

  • Mois source : identifies the monthly file selected by the pipeline.
  • Courses propres : shows a strictly positive row count after filtering.
  • Base créée : ends with the path to data\nyc_taxi.duckdb.
  • Expand the data folder in the VS Code file sidebar.
  • Confirm nyc_taxi.duckdb is listed inside the folder.

Pipeline fails before creating the database?

  • Confirm PowerShell is still inside the nyc-taxi-pipeline folder.
  • Confirm source.py sits beside pipeline.py inside src.
  • Check your internet connection if the output stops after Tentative :.

Still blocked? Help me diagnose why src/pipeline.py does not create data/nyc_taxi.duckdb.

Before the second run, do you think the clean trip count will double or stay the same?

  • Process the same source month again by running the pipeline a second time:
.\.venv\Scripts\python.exe src\pipeline.py

What proves idempotence?

CREATE OR REPLACE TABLE rebuilds clean_trips from the selected Parquet on each run. The second Courses propres : value should match the first value.

  • Compare the two clean trip counts in the PowerShell terminal.
  • Confirm the second count is still strictly positive.
  • Confirm data\nyc_taxi.duckdb still exists after the replacement.

That is the reliability milestone: your pipeline can replay the same month without inflating the table.

Your persistent database now contains a reproducible clean layer. Next, you will turn those trips into monthly metrics and ranked pickup zones.

Build Metrics and Export Reports

Your persistent DuckDB database now holds a clean set of Yellow Taxi trips. Re-running the pipeline replaces the monthly table without creating duplicates.

A clean table does not answer the business questions yet. This step turns those trips into monthly averages. It also ranks pickup zones before exporting the results as CSV reports.

In this step, get ready to:
  • Calculate monthly trip metrics from the clean data.
  • Rank pickup zones using the official zone lookup.
  • Export two reports with headers and data.
Calculate monthly and zone metrics

An aggregate condenses many trip rows into a small set of useful measurements. Your SQL calculates the trip count. It also calculates the average charged amount and average trip duration.

  • In Visual Studio Code, switch back to src/pipeline.py from the previous step.
  • Replace the existing import from source with this expanded import:
from source import (
    ROOT,
    download_latest_trip,
    download_zone_lookup,
    sql_path,
)

Why Fetch the Zone Lookup?

The trip data identifies pickup areas with pickup_location_id. The download_zone_lookup function provides the borough and zone names associated with those identifiers.

  • Find the end of load_clean_trips in src/pipeline.py.
  • Add the first part of build_metrics directly below it:
def build_metrics(
    connection: duckdb.DuckDBPyConnection,
    zone_path: Path,
) -> None:
    zone_csv_path = sql_path(zone_path)
    connection.execute(
        """
        CREATE OR REPLACE TABLE monthly_metrics AS
        SELECT
            source_month,
            COUNT(*) AS nombre_courses,
            ROUND(AVG(total_amount), 2) AS revenu_moyen_par_course,
            ROUND(AVG(duration_minutes), 2) AS duree_moyenne_minutes
        FROM clean_trips
        GROUP BY source_month
        """
    )

What Does This Query Calculate?

  • The monthly_metrics table stores one summary row for each source month.
  • The COUNT(*) expression calculates the number of clean trips.
  • The two AVG expressions calculate the mean charged amount and trip duration.
  • The ROUND expressions keep both averages readable to two decimal places.
  • Keep your cursor inside build_metrics.
  • Add the zone activity query directly after the first connection.execute call:
    connection.execute(
        f"""
        CREATE OR REPLACE TABLE zone_activity AS
        SELECT
            trips.source_month,
            COALESCE(zones."Borough", 'Inconnu') AS borough,
            COALESCE(zones."Zone", 'Zone inconnue') AS zone,
            COUNT(*) AS nombre_courses
        FROM clean_trips AS trips
        LEFT JOIN read_csv('{zone_csv_path}', header = true) AS zones
          ON trips.pickup_location_id = zones."LocationID"
        GROUP BY
            trips.source_month,
            zones."Borough",
            zones."Zone"
        """
    )

How Does the Zone Join Work?

  • The read_csv function lets DuckDB query the lookup file directly.
  • The join matches pickup_location_id with LocationID from the lookup.
  • The COALESCE expressions give unmatched locations readable fallback names.
  • The grouped count measures pickup activity for each named zone.
  • Scroll down to the existing main function.
  • Replace the current main function with the next two code blocks in order:
def main() -> None:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    month, trip_path, source_url = download_latest_trip()
    zone_path = download_zone_lookup()

    with duckdb.connect(str(DATABASE_PATH)) as connection:
        load_clean_trips(connection, trip_path, month)
        build_metrics(connection, zone_path)
        metrics = connection.execute(
            """
            SELECT
                source_month,
                nombre_courses,
                revenu_moyen_par_course,
                duree_moyenne_minutes
            FROM monthly_metrics
            """
        ).fetchone()

What Does the First Half Do?

  • The pipeline downloads the zone lookup alongside the latest trip file.
  • The existing database connection rebuilds the clean trips and both metric tables.
  • The final query retrieves the monthly summary for the terminal output.
  • Paste the second half directly below the first half:
    if metrics is None:
        raise RuntimeError("Les métriques mensuelles sont vides.")

    source_month, trip_count, average_revenue, average_duration = metrics
    print(f"Mois source : {source_month}")
    print(f"URL source : {source_url}")
    print(f"Courses propres : {trip_count:,}")
    print(f"Revenu moyen par course : {average_revenue}")
    print(f"Durée moyenne en minutes : {average_duration}")
    print(f"Base créée : {DATABASE_PATH}")

What Does the Second Half Do?

The empty-result check stops the pipeline if no monthly summary exists. The remaining lines unpack the summary before printing each measurement.

  • Save src/pipeline.py.

Before you run the pipeline, what new measurements do you expect beneath the clean trip count?

The monthly Parquet may take a little while to download. The Tentative : line confirms that the process is still moving.

  • Return to PowerShell from the previous step.
  • Build both metric tables by running this command:
.\.venv\Scripts\python.exe src\pipeline.py

What Should You See?

The terminal prints a source month and a positive clean trip count. It now also prints Revenu moyen par course : followed by Durée moyenne en minutes :.

Those values confirm that monthly_metrics contains data. A successful run also creates or replaces zone_activity in the same database.

That is the business layer working. Your clean trips now produce repeatable monthly measurements and named zone activity.

Metrics Missing From the Output?

  • Check that build_metrics(connection, zone_path) sits inside the database connection block.
  • Check that the zone query remains indented inside build_metrics.
  • Compare the imported name download_zone_lookup with the function defined in src/source.py.

Still stuck? Help me debug why my DuckDB metric tables are not being created.

Export the CSV reports

Database tables are useful for analysis. Exported reports make the same results easy to inspect or share without opening the database.

  • In src/pipeline.py, place export_reports directly below build_metrics by adding this code:
def export_reports(connection: duckdb.DuckDBPyConnection) -> None:
    monthly_path = sql_path(MONTHLY_REPORT_PATH)
    zones_path = sql_path(ZONES_REPORT_PATH)
    connection.execute(
        f"COPY monthly_metrics TO '{monthly_path}' (FORMAT csv, HEADER)"
    )
    connection.execute(
        f"""
        COPY (
            SELECT *
            FROM zone_activity
            ORDER BY nombre_courses DESC
            LIMIT 10
        ) TO '{zones_path}' (FORMAT csv, HEADER)
        """
    )

What Does This Export?

  • The first COPY statement exports the complete monthly summary with a header row.
  • The second COPY statement sorts zones by trip count.
  • The LIMIT 10 clause keeps the ten most active pickup zones.
  • The two path variables keep the report destinations consistent with the constants at the top of the file.
  • Replace the current main function with the next two code blocks in order:
def main() -> None:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    REPORTS_DIR.mkdir(parents=True, exist_ok=True)

    month, trip_path, source_url = download_latest_trip()
    zone_path = download_zone_lookup()

    with duckdb.connect(str(DATABASE_PATH)) as connection:
        load_clean_trips(connection, trip_path, month)
        build_metrics(connection, zone_path)
        export_reports(connection)
        metrics = connection.execute(
            """
            SELECT
                source_month,
                nombre_courses,
                revenu_moyen_par_course,
                duree_moyenne_minutes
            FROM monthly_metrics
            """
        ).fetchone()

How Does the Final Pipeline Flow?

  • The pipeline creates the reports directory before writing files.
  • The clean load runs before the metric calculations.
  • The report export runs after both metric tables exist.
  • Paste the second half directly below the first half:
    if metrics is None:
        raise RuntimeError("Les métriques mensuelles sont vides.")

    source_month, trip_count, average_revenue, average_duration = metrics
    print(f"Mois source : {source_month}")
    print(f"URL source : {source_url}")
    print(f"Courses propres : {trip_count:,}")
    print(f"Revenu moyen par course : {average_revenue}")
    print(f"Durée moyenne en minutes : {average_duration}")
    print(f"Base créée : {DATABASE_PATH}")
    print(f"Rapports créés : {REPORTS_DIR}")

How Does the Pipeline Confirm the Export?

The final print statement shows the directory that received both reports. The earlier output still exposes the values written to the monthly report.

  • Save src/pipeline.py.

✔️ Awesome, I've got everything!

Your metric calculations and report export are in place. Continue to the final verification.

ⓧ I'd like to double check the full code

from pathlib import Path

import duckdb

from source import (
    ROOT,
    download_latest_trip,
    download_zone_lookup,
    sql_path,
)

DATA_DIR = ROOT / "data"
REPORTS_DIR = ROOT / "reports"
DATABASE_PATH = DATA_DIR / "nyc_taxi.duckdb"
MONTHLY_REPORT_PATH = REPORTS_DIR / "monthly_metrics.csv"
ZONES_REPORT_PATH = REPORTS_DIR / "top_zones.csv"


def load_clean_trips(
    connection: duckdb.DuckDBPyConnection,
    trip_path: Path,
    month: str,
) -> None:
    parquet_path = sql_path(trip_path)
    connection.execute(
        f"""
        CREATE OR REPLACE TABLE clean_trips AS
        SELECT
            ? AS source_month,
            CAST(tpep_pickup_datetime AS TIMESTAMP) AS pickup_at,
            CAST(tpep_dropoff_datetime AS TIMESTAMP) AS dropoff_at,
            (
                epoch(tpep_dropoff_datetime)
                - epoch(tpep_pickup_datetime)
            ) / 60.0 AS duration_minutes,
            CAST(trip_distance AS DOUBLE) AS trip_distance,
            CAST(total_amount AS DOUBLE) AS total_amount,
            CAST(PULocationID AS INTEGER) AS pickup_location_id
        FROM read_parquet('{parquet_path}')
        WHERE tpep_dropoff_datetime > tpep_pickup_datetime
          AND trip_distance >= 0
          AND total_amount >= 0
          AND PULocationID IS NOT NULL
          AND strftime(tpep_pickup_datetime, '%Y-%m') = ?
        """,
        [month, month],
    )


def build_metrics(
    connection: duckdb.DuckDBPyConnection,
    zone_path: Path,
) -> None:
    zone_csv_path = sql_path(zone_path)
    connection.execute(
        """
        CREATE OR REPLACE TABLE monthly_metrics AS
        SELECT
            source_month,
            COUNT(*) AS nombre_courses,
            ROUND(AVG(total_amount), 2) AS revenu_moyen_par_course,
            ROUND(AVG(duration_minutes), 2) AS duree_moyenne_minutes
        FROM clean_trips
        GROUP BY source_month
        """
    )
    connection.execute(
        f"""
        CREATE OR REPLACE TABLE zone_activity AS
        SELECT
            trips.source_month,
            COALESCE(zones."Borough", 'Inconnu') AS borough,
            COALESCE(zones."Zone", 'Zone inconnue') AS zone,
            COUNT(*) AS nombre_courses
        FROM clean_trips AS trips
        LEFT JOIN read_csv('{zone_csv_path}', header = true) AS zones
          ON trips.pickup_location_id = zones."LocationID"
        GROUP BY
            trips.source_month,
            zones."Borough",
            zones."Zone"
        """
    )


def export_reports(connection: duckdb.DuckDBPyConnection) -> None:
    monthly_path = sql_path(MONTHLY_REPORT_PATH)
    zones_path = sql_path(ZONES_REPORT_PATH)
    connection.execute(
        f"COPY monthly_metrics TO '{monthly_path}' (FORMAT csv, HEADER)"
    )
    connection.execute(
        f"""
        COPY (
            SELECT *
            FROM zone_activity
            ORDER BY nombre_courses DESC
            LIMIT 10
        ) TO '{zones_path}' (FORMAT csv, HEADER)
        """
    )


def main() -> None:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    REPORTS_DIR.mkdir(parents=True, exist_ok=True)

    month, trip_path, source_url = download_latest_trip()
    zone_path = download_zone_lookup()

    with duckdb.connect(str(DATABASE_PATH)) as connection:
        load_clean_trips(connection, trip_path, month)
        build_metrics(connection, zone_path)
        export_reports(connection)
        metrics = connection.execute(
            """
            SELECT
                source_month,
                nombre_courses,
                revenu_moyen_par_course,
                duree_moyenne_minutes
            FROM monthly_metrics
            """
        ).fetchone()

    if metrics is None:
        raise RuntimeError("Les métriques mensuelles sont vides.")

    source_month, trip_count, average_revenue, average_duration = metrics
    print(f"Mois source : {source_month}")
    print(f"URL source : {source_url}")
    print(f"Courses propres : {trip_count:,}")
    print(f"Revenu moyen par course : {average_revenue}")
    print(f"Durée moyenne en minutes : {average_duration}")
    print(f"Base créée : {DATABASE_PATH}")
    print(f"Rapports créés : {REPORTS_DIR}")


if __name__ == "__main__":
    main()

How to Use This Reference

Compare this file with your saved src/pipeline.py. Check the imports, function order, indentation, and final main call.

Verify the exported reports

Before you run the finished pipeline, do you expect both report files to contain the same number of rows?

  • Run the finished pipeline from the repository in PowerShell with this command:
.\.venv\Scripts\python.exe src\pipeline.py

What Should the Final Run Show?

The terminal prints the source month, clean trip count, average revenue, and average duration. The last two lines show the database path and the reports directory.

The reports have different shapes. monthly_metrics.csv contains the monthly summary. top_zones.csv contains up to ten ranked zones.

You have turned millions of trip records into two readable outputs. The same run can now recreate the database and reports whenever the source month changes.

  • Switch back to Visual Studio Code from earlier.
  • Select reports/monthly_metrics.csv from the file list.
  • Confirm that its header includes source_month, nombre_courses, revenu_moyen_par_course, and duree_moyenne_minutes.
  • Select reports/top_zones.csv from the file list.
  • Confirm that its header includes source_month, borough, zone, and nombre_courses.
  • Confirm that the zone report lists ten data rows in descending trip-count order.

What Does Average Revenue Mean Here?

The revenu_moyen_par_course value is calculated from total_amount. This source column represents the amount charged to passengers and excludes cash tips.

Reports Missing or Empty?

  • Check that REPORTS_DIR.mkdir(parents=True, exist_ok=True) runs before the database connection.
  • Check that export_reports(connection) runs after build_metrics(connection, zone_path).
  • Check that both COPY statements include HEADER.

Need another pair of eyes? Help me debug why my monthly metrics or top zones CSV report is missing data.

Your local ETL now produces a persistent database and shareable reports from the latest published month. Next, you will move the same pipeline into GitHub Actions so it can run automatically.

Automate the Pipeline with GitHub Actions

Your local pipeline now turns a monthly Yellow Taxi file into a persistent DuckDB database plus two CSV reports. You have already proved that repeated runs produce reproducible results.

Now you will move the same pipeline into GitHub Actions. A cloud runner will recreate the environment on demand or on a monthly schedule.

An ETL becomes automated when its environment can run away from your computer. Its outputs also need to remain available after the runner finishes.

In this step, get ready to:
  • Configure a cloud workflow that runs on demand or monthly.
  • Document the pipeline in README.md.
  • Prove the published repository with a successful manual run.
Create the monthly workflow

GitHub Actions reads YAML workflow files from .github/workflows. This workflow defines when the pipeline runs plus how its environment is rebuilt.

  • Use the new-file control in the Explorer sidebar of Visual Studio Code to create .github/workflows/etl.yml inside nyc-taxi-pipeline.

You should see the nested .github/workflows folders plus the new etl.yml file in the Explorer sidebar.

  • Add the trigger plus permission settings by pasting this first section into etl.yml:
name: Pipeline NYC Taxi

on:
  workflow_dispatch:
  schedule:
    - cron: "17 8 15 * *"

permissions:
  contents: read

What Does This Configuration Do?

  • The workflow name identifies this automation as Pipeline NYC Taxi in the repository.
  • The workflow_dispatch trigger enables manual runs from GitHub.
  • The 17 8 15 * * cron schedule requests a run at 08:17 UTC on the 15th of every month.
  • The contents: read permission gives the workflow read-only access to the repository contents.
  • Save .github/workflows/etl.yml.
  • Confirm the editor shows one manual trigger plus one monthly schedule.

Workflow File Showing YAML Problems?

Check that workflow_dispatch lines up with schedule. Use spaces for every indentation level.

Need help with the first section? Help me check the trigger and permission indentation in my GitHub Actions YAML file.

The next section defines the runner plus every task it performs. The runner starts clean each time, so the workflow must fetch your repository before rebuilding the Python environment.

  • Add the runner job beneath the existing permissions block by pasting:
jobs:
  etl:
    runs-on: ubuntu-latest
    steps:
      - name: Récupérer le dépôt
        uses: actions/checkout@v7

      - name: Configurer Python
        uses: actions/setup-python@v7
        with:
          python-version: "3.11"
          cache: pip

      - name: Installer les dépendances
        run: python -m pip install -r requirements.txt

      - name: Exécuter le pipeline
        run: python src/pipeline.py

      - name: Publier la base et les rapports
        uses: actions/upload-artifact@v7
        with:
          name: nyc-taxi-output
          path: |
            data/nyc_taxi.duckdb
            reports/*.csv
          retention-days: 30
          compression-level: 0
          if-no-files-found: error

How Does the Job Rebuild Your Pipeline?

  • The ubuntu-latest runner provides a fresh machine for the ETL job.
  • The setup action installs Python 3.11. Its pip cache speeds up later dependency installations.
  • The dependency step installs the pinned package from requirements.txt.
  • The pipeline step runs src/pipeline.py on the runner.
  • The final step preserves the DuckDB database plus both CSV reports as the nyc-taxi-output artifact for 30 days.
  • Save .github/workflows/etl.yml.
  • Confirm the workflow contains five steps under the etl job.
  • Confirm the artifact paths point to data/nyc_taxi.duckdb plus reports/*.csv.

Job Steps Look Misaligned?

Check that every step starts at the same indentation beneath steps. Keep each uses or run entry directly beneath its matching name.

Still unsure about the nesting? Help me check the job indentation in my GitHub Actions workflow.

✔️ Awesome, I've got everything!

Great. Save .github/workflows/etl.yml before moving on.

ⓧ I'd like to double check the full code

name: Pipeline NYC Taxi

on:
  workflow_dispatch:
  schedule:
    - cron: "17 8 15 * *"

permissions:
  contents: read

jobs:
  etl:
    runs-on: ubuntu-latest
    steps:
      - name: Récupérer le dépôt
        uses: actions/checkout@v7

      - name: Configurer Python
        uses: actions/setup-python@v7
        with:
          python-version: "3.11"
          cache: pip

      - name: Installer les dépendances
        run: python -m pip install -r requirements.txt

      - name: Exécuter le pipeline
        run: python src/pipeline.py

      - name: Publier la base et les rapports
        uses: actions/upload-artifact@v7
        with:
          name: nyc-taxi-output
          path: |
            data/nyc_taxi.duckdb
            reports/*.csv
          retention-days: 30
          compression-level: 0
          if-no-files-found: error
Document the pipeline

A repository needs enough context for another person to understand its purpose. The README records the pipeline results plus the commands needed to reproduce them on Windows.

  • Use the new-file control in the Explorer sidebar to create README.md inside nyc-taxi-pipeline.

You should see README.md beside requirements.txt in the repository.

  • Document the finished pipeline by pasting this content into README.md:
# Pipeline NYC Taxi automatisé

Pipeline ETL mensuel construit avec Python, DuckDB et GitHub Actions à partir des Yellow Taxi Trip Records officiels de la NYC Taxi and Limousine Commission.

## Résultats

- Détection du dernier mois publié, avec repli vers un mois antérieur si nécessaire
- Lecture native du Parquet avec DuckDB
- Nettoyage et chargement idempotent dans `data/nyc_taxi.duckdb`
- Revenu moyen par course basé sur `total_amount`
- Durée moyenne des courses
- Top 10 des zones de départ
- Exécution mensuelle et manuelle dans GitHub Actions

## Exécution locale sous Windows

```powershell
python -m venv .venv
.\.venv\Scripts\python.exe -m pip install -r requirements.txt
.\.venv\Scripts\python.exe src\pipeline.py
```

Les rapports sont écrits dans `reports/`. Les fichiers volumineux générés ne sont pas suivis par Git.

Source officielle : https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page

What Does the README Capture?

  • The results section names the observable outputs produced by the pipeline.
  • The Windows section records the commands needed to recreate the local environment.
  • The final note explains why raw data plus generated outputs stay outside the repository history.
  • Save README.md.
  • Confirm the file contains a results section plus a Windows execution section.

README Formatting Looks Broken?

Check that the PowerShell commands sit between matching triple-backtick fences. Keep a blank line before each heading so the sections render separately.

Need help checking the Markdown? Help me find the formatting problem in my pipeline README.

✔️ Awesome, I've got everything!

Your README now explains what the pipeline produces plus how to run it locally.

ⓧ I'd like to double check the full code

# Pipeline NYC Taxi automatisé

Pipeline ETL mensuel construit avec Python, DuckDB et GitHub Actions à partir des Yellow Taxi Trip Records officiels de la NYC Taxi and Limousine Commission.

## Résultats

- Détection du dernier mois publié, avec repli vers un mois antérieur si nécessaire
- Lecture native du Parquet avec DuckDB
- Nettoyage et chargement idempotent dans `data/nyc_taxi.duckdb`
- Revenu moyen par course basé sur `total_amount`
- Durée moyenne des courses
- Top 10 des zones de départ
- Exécution mensuelle et manuelle dans GitHub Actions

## Exécution locale sous Windows

```powershell
python -m venv .venv
.\.venv\Scripts\python.exe -m pip install -r requirements.txt
.\.venv\Scripts\python.exe src\pipeline.py
```

Les rapports sont écrits dans `reports/`. Les fichiers volumineux générés ne sont pas suivis par Git.

Source officielle : https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page
Publish and verify the automation

Your workflow can only run after Git publishes the files to the default branch. The existing .gitignore keeps your local virtual environment plus generated data out of that commit.

Git may ask you to authenticate before the first push completes. Any browser sign-in confirms access without exposing credentials in your terminal history.

  • Publish the complete project from the PowerShell panel from earlier by running these commands:
git add .
git commit -m "Construire le pipeline NYC Taxi"
git push -u origin main

What Do These Commands Publish?

  • The first command stages the project files that are not excluded by .gitignore.
  • The second command records one local snapshot with a descriptive message.
  • The final command publishes the main branch to origin.
  • Wait for the command sequence to finish.
  • Confirm the terminal reports a new commit plus a completed push to main.

Push Did Not Complete?

Confirm that the repository still has an origin remote. Complete any browser authentication request before retrying the push.

Still blocked? Help me diagnose why my Git push to the public origin repository is failing.

The manual trigger now provides the fastest proof that the repository can reproduce your local ETL. Before you trigger it, do you expect the runner to recreate every output without using your local .venv?

  • Return to the public nyc-taxi-pipeline repository in your browser.
  • Select the Actions tab.
  • Select Pipeline NYC Taxi from the workflow list.
  • Select Run workflow.
  • Select Run workflow again in the menu.
  • Wait for the workflow run to finish.

You should see a green completed run. Its page should offer nyc-taxi-output as a downloadable artifact containing the DuckDB database plus both CSV reports.

Workflow Run Is Not Green?

Open the failed step to identify whether the problem occurred during dependency installation, pipeline execution, or artifact upload. Compare the failing step with the matching section in etl.yml.

Need help reading the run log? Help me diagnose the failed GitHub Actions step for my NYC Taxi pipeline.

You did it. Your monthly ETL now runs beyond your computer and preserves its database plus reports after every successful execution.

Secret mission

Mission secrète : Retraiter un mois choisi à la demande

Votre pipeline choisit automatiquement le dernier mois disponible. Cette mission ajoute un mode de retraitement qui sélectionne un mois publié au format YYYY-MM depuis GitHub Actions.

Clean Up Your Resources

Clean Up Your Resources

Le runner standard de GitHub Actions reste gratuit tant que votre dépôt est public. Décidez de garder le workflow actif, de le désactiver pour y revenir plus tard ou de supprimer toutes les ressources.

Cost warning

Les artefacts consomment le quota de stockage de votre compte GitHub. Un dépôt privé ou un dépassement de quota peut entraîner une facturation selon votre forfait.

La pause empêche la création de nouveaux artefacts. La suppression retire les résultats stockés avec le projet.

Resources you used:

  • Le dossier local nyc-taxi-pipeline avec .venv/, data/raw/, la base DuckDB data/nyc_taxi.duckdb et les rapports CSV dans reports/.
  • Les artefacts nyc-taxi-output publiés par les exécutions du workflow avec une rétention de 30 jours.
  • Le dépôt GitHub public nyc-taxi-pipeline avec le workflow actif Pipeline NYC Taxi.

Keep everything running

Aucune action n’est nécessaire. Choisissez cette option si vous souhaitez continuer à produire automatiquement les résultats du dernier mois publié.

  • Laissez le workflow Pipeline NYC Taxi activé pour conserver son exécution mensuelle le 15 à 08:17 UTC.
  • Conservez le dépôt nyc-taxi-pipeline public pour continuer à utiliser gratuitement le runner standard.
  • Gardez le dossier local nyc-taxi-pipeline sur votre Bureau pour relancer le pipeline depuis votre ordinateur.
  • Téléchargez les futurs artefacts nyc-taxi-output avant la fin de leur période de rétention si vous souhaitez les archiver.

Pause - I'll come back to this later

Désactivez le workflow pour arrêter les prochaines exécutions sans supprimer votre code. Les artefacts déjà publiés restent soumis à leur période de rétention de 30 jours.

  • Revenez au dépôt public nyc-taxi-pipeline sur GitHub.
  • Sélectionnez Actions dans la barre du dépôt.
  • Sélectionnez le workflow Pipeline NYC Taxi.
  • Ouvrez le menu du workflow.
  • Choisissez Disable workflow.

Les nouveaux déclenchements sont désormais désactivés. Vos fichiers locaux et distants restent disponibles.

Delete - I don't want to use this again

Cette option efface les résultats hébergés. Elle retire aussi le dépôt distant et le dossier local.

Deletion is permanent

La suppression paraît radicale parce qu’elle l’est. Chaque ressource disparaît seulement après l’action dédiée ci-dessous.

  • Téléchargez les rapports que vous souhaitez conserver depuis l’artefact nyc-taxi-output.
  • Dans le dépôt nyc-taxi-pipeline, sélectionnez Actions.
  • Sélectionnez une exécution de Pipeline NYC Taxi qui contient l’artefact nyc-taxi-output.
  • Supprimez l’artefact nyc-taxi-output depuis la page de cette exécution.
  • Répétez cette suppression pour chaque exécution dont vous ne souhaitez pas conserver les résultats.

Les sorties hébergées de ces exécutions sont maintenant supprimées.

  • Revenez à la page principale du dépôt nyc-taxi-pipeline.
  • Sélectionnez Settings.
  • Sélectionnez General.
  • Descendez jusqu’à Danger Zone.
  • Sélectionnez Delete this repository.
  • Suivez les confirmations affichées pour supprimer définitivement le dépôt.

Le dépôt distant et son workflow ont maintenant disparu. Il reste à retirer la copie stockée sur votre ordinateur.

  • Fermez Visual Studio Code.
  • Fermez PowerShell.
  • Dans l’Explorateur de fichiers, affichez votre Bureau.
  • Supprimez le dossier nyc-taxi-pipeline depuis votre Bureau.
  • Videz la Corbeille pour retirer définitivement la base locale, les fichiers bruts et les rapports.

Nice Work!

Nice Work!

Bravo ! Vous avez construit un pipeline ETL mensuel qui transforme les données Yellow Taxi en rapports reproductibles.

Vous avez appris à :

  • Télécharger automatiquement le dernier mois Yellow Taxi publié au format Parquet. Vous l’avez interrogé directement avec DuckDB pour compter ses lignes brutes.
  • Filtrer les courses invalides. Vous avez matérialisé les résultats dans la table clean_trips d’une base persistante. Une seconde exécution remplace la table pour garantir un chargement idempotent.
  • Calculer des métriques métier à partir des courses nettoyées. Vos deux rapports CSV rendent ces résultats consultables. GitHub Actions exécute le pipeline chaque mois et publie la base avec les rapports dans un artefact téléchargeable.
  • Mission secrète : Ajouté un paramètre mensuel validé au workflow. Vous pouvez désormais retraiter un mois historique au format YYYY-MM sans modifier le code.

Prêt à tester vos connaissances ?