Build an ML-Ready Churn Pipeline

Build a tested dbt and DuckDB pipeline that trains a churn classifier.

Introduction

30 Second Summary

When customers stop using a service, the warning signs are often scattered across billing, support, and activity records. Repeated records can make one customer look like several different people.

In this project, you will build a local ELT pipeline with dbt Core and DuckDB that turns a messy customer CSV into a tested feature table. A scikit-learn logistic regression classifier consumes that table to produce an evaluation report with a ranked churn-risk list.

What You'll Build

When you run the finished project, your terminal displays a classification report plus the five customers with the highest predicted churn risk from tested data.

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

  • A reproducible data pipeline that loads a version-controlled CSV into DuckDB with inspectable data lineage from raw records to ML features.
  • A data quality gate that visibly rejects duplicate customer IDs before training. The same gate passes after your deduplication repair.
  • A trained churn classifier that prints evaluation metrics plus the five customers with the highest predicted churn risk.
  • Secret Mission: Rank the standardized feature coefficients to reveal which six inputs carry the strongest linear signals.

Are there any prerequisites?

This project requires Windows 10 or 11, Visual Studio Code, basic SQL familiarity, and permission to install local software. No Python experience is required.

Before We Start

This checkpoint helps you commit to building a tested customer churn pipeline before any hands-on work begins. Your goal is to connect clean customer rows to trustworthy classifier training.

Set Up the Local ML Data Stack

Package mismatches can stop this churn project before the data ever reaches the classifier.

An isolated Python virtual environment keeps exact package versions inside this project. Visual Studio Code gives that environment one workspace.

Inside it, dbt Core transforms data in DuckDB. scikit-learn later trains the classifier from the tested table.

In this step, get ready to:
  • Prepare Windows for Python 3.13.15.
  • Create the customer-churn-pipeline workspace.
  • Install the pinned local data stack inside venv.
Confirm Windows compatibility and install Python

The Python Install Manager supports Windows 10 or newer. Confirming compatibility first prevents the runtime installation from failing halfway through.

  • Press the Windows key to open search.
  • Type Settings into the search field.
  • Press Enter to open Settings.
  • Use the Settings search field to locate the Windows edition details.
  • Confirm that the edition is Windows 10 or Windows 11.

That compatibility check clears the first installation hurdle. Your computer can run the Python Install Manager used in this project.

Using an older Windows version?

This project requires Windows 10 or newer. Continue on a compatible Windows computer so the Python installation commands work as documented.

Need help checking your options? Help me understand how to continue this project from an older Windows version.

PowerShell gives you a place to check whether the required Python runtime already exists.

  • Press the Windows key to open search.
  • Type PowerShell into the search field.
  • Press Enter to open PowerShell.
  • Check the installed Python version by running this command:
python --version

What does this command check?

The command asks the active Python executable to report its version. The result determines which setup path you need below.

✔️ I see version 3.13.15

Your Python runtime already matches the project pin. Keep this PowerShell window open until you start working in Visual Studio Code.

ⓧ I see an older version

Install the Python Install Manager first. It provides the runtime command used to add Python 3.13.

  • Install the Python Install Manager by running this command:
winget install 9NQ7512CXL7T -e --accept-package-agreements --disable-interactivity

What does this command install?

WinGet installs the official Python Install Manager package. The flags accept the package agreement without launching an interactive installer.

  • Close PowerShell after the installation finishes.
  • Press the Windows key to reopen search.
  • Type PowerShell into the search field.
  • Press Enter to open a fresh PowerShell window.
  • Install the Python 3.13 runtime by running this command:
py install 3.13

What does this command install?

The manager downloads the CPython 3.13 runtime. This project uses the 3.13.15 maintenance release from that runtime series.

  • Close PowerShell after the runtime installation finishes.
  • Reopen PowerShell through Windows search.
  • Confirm the installed Python version by running this command:
python --version

What should this confirm?

The fresh terminal reloads the available Python commands. The version check should now resolve to Python 3.13.15.

ⓧ Command not found

Python is missing from your command path. Install the Python Install Manager before adding the required runtime.

  • Install the Python Install Manager by running this command:
winget install 9NQ7512CXL7T -e --accept-package-agreements --disable-interactivity

What does this command install?

WinGet installs the official Python Install Manager package. The flags accept the package agreement without launching an interactive installer.

  • Close PowerShell after the installation finishes.
  • Press the Windows key to reopen search.
  • Type PowerShell into the search field.
  • Press Enter to open a fresh PowerShell window.
  • Install the Python 3.13 runtime by running this command:
py install 3.13

What does this command install?

The manager downloads the CPython 3.13 runtime. This project uses the 3.13.15 maintenance release from that runtime series.

  • Close PowerShell after the runtime installation finishes.
  • Reopen PowerShell through Windows search.
  • Confirm the installed Python version by running this command:
python --version

What should this confirm?

The fresh terminal reloads the available Python commands. The version check should now report Python 3.13.15.

Python version still incorrect?

Close every open PowerShell window after installing the runtime. A terminal opened before installation can retain the previous command path.

Run the version check from a fresh PowerShell window. Help me diagnose why Python 3.13.15 is not resolving in PowerShell.

Create the project workspace

A dedicated workspace keeps the dependency file beside every pipeline file you add later. Visual Studio Code also starts its integrated terminal inside the folder you open.

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

You should now see a Visual Studio Code window. The next action gives the pipeline its own folder on your Desktop.

  • Click File in the top menu bar.
  • Click Open Folder....
  • Select your Desktop in the folder picker.
  • Click New Folder.
  • Enter customer-churn-pipeline as the folder name.
  • Select the new customer-churn-pipeline folder.

Visual Studio Code may display a Workspace Trust prompt when the folder opens. This folder contains files you create yourself.

  • Click Select Folder to open the workspace.
  • Select Yes, I trust the authors if the Workspace Trust prompt appears.

The Explorer sidebar now shows customer-churn-pipeline. That folder becomes the home for the complete local pipeline.

The dependency file pins every package used by the project. Reinstalling from this file reproduces the same local stack.

  • Select the Explorer view in the left Activity Bar.
  • Click New File... in the Explorer toolbar.
  • Enter requirements.txt as the file name.
  • Add the pinned dependencies to requirements.txt by pasting this content:
dbt-core==1.12.3
dbt-duckdb==1.11.0
duckdb==1.5.5
scikit-learn==1.9.0

What do these dependencies provide?

  • dbt Core 1.12.3 runs the SQL transformation project.
  • dbt-duckdb 1.11.0 connects dbt Core to the local database.
  • DuckDB 1.5.5 stores the seed data plus transformed tables.
  • scikit-learn 1.9.0 supplies the preprocessing tools plus the classifier.
  • Save requirements.txt.
  • Confirm that requirements.txt appears inside customer-churn-pipeline in the Explorer sidebar.

Dependency file missing?

Check that the Explorer heading shows customer-churn-pipeline. Creating the file before opening that folder can save it somewhere else.

Confirm that the name ends with .txt. Help me check why requirements.txt is missing from my VS Code workspace.

✔️ Awesome, I've got everything!

Great. Double-check that you saved requirements.txt inside customer-churn-pipeline.

ⓧ I'd like to double check the full code

dbt-core==1.12.3
dbt-duckdb==1.11.0
duckdb==1.5.5
scikit-learn==1.9.0
Build the isolated environment

The venv folder isolates this project's packages from other Python projects. Activating it directs installation commands into that isolated environment.

  • Click View in the Visual Studio Code menu bar.
  • Click Terminal to open the integrated PowerShell terminal.

The terminal starts inside the open customer-churn-pipeline workspace. Commands run here can create files directly inside that folder.

  • Create the isolated venv environment by running this command:
python -m venv venv

What does this command create?

Python creates a local environment named venv inside the workspace. Its own interpreter keeps the project packages separate from system-wide installations.

  • Confirm that the venv folder appears in the Explorer sidebar.

Virtual environment folder missing?

Confirm that the terminal path ends inside customer-churn-pipeline. Run the creation command again after Python reports version 3.13.15.

Need another pair of eyes? Help me diagnose why the venv folder was not created.

  • Activate the isolated environment in PowerShell by running this command:
venv\Scripts\Activate.ps1

What does activation change?

Activation points the terminal at the Python executable inside venv. Package installations now stay inside this workspace.

You should see venv at the start of the PowerShell prompt. That prefix confirms the isolated environment is active.

PowerShell blocking activation?

Use a Command Prompt terminal when PowerShell prevents the activation script from running.

  • Select the terminal profile dropdown in the Terminal panel.
  • Choose Command Prompt.
  • Activate the existing environment by running this command:
venv\Scripts\activate.bat

This batch file activates the same venv environment from Command Prompt.

Still blocked? Help me activate my venv from the VS Code terminal.

The first package installation can take a few minutes while Windows downloads the required wheels. A quiet terminal during that window is normal.

  • Upgrade pip inside the active environment by running these commands:
python -m pip install --upgrade pip
python -m pip install -r requirements.txt

What do these commands install?

The first command updates the package installer inside venv. The second command reads every exact package pin from requirements.txt.

Compiled Windows wheels provide the required DuckDB plus scikit-learn components for Python 3.13.

The terminal returns to the active prompt after the dependencies finish installing. Your local ML data stack is now ready for its final version check.

Package installation not completing?

Confirm that venv appears at the start of the terminal prompt. Check that all four lines in requirements.txt match the double-check tab.

If a download stops, rerun the requirements installation from the active environment. Help me troubleshoot this package installation.

Before you run these checks, do you expect both commands to resolve inside the active environment?

  • Verify the Python runtime plus the dbt installation by running these commands:
python --version
dbt --version

What do these checks prove?

  • The Python check confirms which interpreter the active environment uses.
  • The dbt check reports the installed Core version plus its available adapters.

You should see Python 3.13.15. The dbt output should report Core 1.12.3.

You should also see the DuckDB adapter listed. That confirms dbt can connect to the local database engine used by the pipeline.

Version check not matching?

Confirm that venv appears at the start of the terminal prompt. An inactive environment can resolve a different Python or dbt installation.

Rerun the requirements installation if dbt is missing or the DuckDB adapter is absent. Help me diagnose my final version check.

Your isolated data stack is installed with every required version accounted for. Next, you will connect dbt to DuckDB and load the deliberately messy customer data.

Load and Inspect Messy Customer Data

Your Visual Studio Code workspace now has an isolated Python environment with the tools you verified earlier. The classifier still needs a reliable path from source data into storage.

Before transformations can protect the classifier, you need a visible raw-data baseline. dbt loads a deliberately messy CSV into DuckDB.

The terminal preview exposes the duplicate customer records before any transformation changes them.

In this step, get ready to:
  • Configure the local dbt project with its DuckDB profile.
  • Load the raw customer CSV into a persistent database.
  • Preview the customer rows to locate the duplicate C012 records.
Configure the dbt project

A dbt project file defines where your seeds and models live. It also controls how each model folder stores its results.

  • In the Explorer sidebar, select the top-level customer-churn-pipeline folder.
  • Click the New File button in the Explorer toolbar.

You will see a file name field inside the selected folder.

  • Enter dbt_project.yml as the file name.
  • Add the project configuration to dbt_project.yml by pasting this code:
name: customer_churn_pipeline
version: "1.0.0"
config-version: 2
profile: customer_churn_pipeline

model-paths: ["models"]
seed-paths: ["seeds"]

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

What does this configuration do?

  • The project name links this configuration to the matching local profile.
  • The path settings tell dbt to read SQL models from models.
  • The seed path tells dbt to load source CSV files from seeds.
  • The materialization settings store staging models as views. They store mart models as tables.
  • Save dbt_project.yml.
  • Confirm dbt_project.yml appears beside requirements.txt in the Explorer sidebar.

Project file in the wrong folder?

Make sure dbt_project.yml sits directly inside customer-churn-pipeline. Check that the file name ends with .yml.

If the indentation looks uneven, replace the file with the reference version below. Help me check my dbt project configuration.

✔️ Awesome, I've got everything!

Great. Double-check that you saved dbt_project.yml in the top-level project folder.

ⓧ I'd like to double check the full code

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

model-paths: ["models"]
seed-paths: ["seeds"]

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

The profile gives dbt the connection details for your local database. Its path keeps the warehouse inside the project folder.

  • In the Explorer sidebar, select the top-level customer-churn-pipeline folder.
  • Click the New File button in the Explorer toolbar.

You will see another file name field inside the selected folder.

  • Enter profiles.yml as the file name.
  • Add the local DuckDB profile to profiles.yml by pasting this code:
customer_churn_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: customer_features.duckdb
      schema: main
      threads: 4

What does this profile do?

  • The profile name matches the project configuration.
  • The dev target uses the DuckDB adapter.
  • The database path stores the warehouse in customer_features.duckdb.
  • The profile places relations in the main schema.
  • Save profiles.yml.
  • Confirm profiles.yml appears beside dbt_project.yml in the Explorer sidebar.

Profile not appearing beside the project file?

Check that you selected the top-level customer-churn-pipeline folder before creating profiles.yml.

Confirm that the profile name matches customer_churn_pipeline exactly. Help me check my local dbt profile.

✔️ Awesome, I've got everything!

Good. Double-check that you saved profiles.yml beside dbt_project.yml.

ⓧ I'd like to double check the full code

customer_churn_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: customer_features.duckdb
      schema: main
      threads: 4

The configured paths now need matching folders. The staging and marts folders separate raw cleanup from machine learning features.

  • In the Explorer sidebar, select the top-level customer-churn-pipeline folder.
  • Click the New Folder button in the Explorer toolbar.

You will see a folder name field inside the selected project folder.

  • Enter models as the folder name.

The models folder now appears in the Explorer sidebar.

  • Select the new models folder.
  • Click the New Folder button in the Explorer toolbar.

You will see a folder name field nested under models.

  • Enter staging as the folder name.

The Explorer now shows models/staging.

  • Select the models folder again.
  • Click the New Folder button in the Explorer toolbar.

You will see another folder name field nested under models.

  • Enter marts as the folder name.

The Explorer now shows models/marts beside models/staging.

  • Select the top-level customer-churn-pipeline folder.
  • Click the New Folder button in the Explorer toolbar.

You will see a folder name field at the top level of the project.

  • Enter seeds as the folder name.

Your Explorer now shows models/staging and models/marts under models. It also shows seeds at the top level.

Add the messy customer seed

A dbt seed turns a version-controlled CSV into a relation that SQL models can reference. This seed contains 25 raw snapshots.

One customer has two snapshots. Keeping both records intact creates the quality problem you will test in the next step.

  • In the Explorer sidebar, select the seeds folder.
  • Click the New File button in the Explorer toolbar.

You will see a file name field inside seeds.

  • Enter customer_activity.csv as the file name.
  • Add the raw customer snapshots to seeds/customer_activity.csv by pasting this data:
customer_id,snapshot_version,tenure_months,monthly_spend,support_tickets,days_since_last_activity,logins_last_30d,contract_type,churned
C001,1,48,120.00,0,2,34,annual,0
C002,1,36,95.00,1,4,28,annual,0
C003,1,60,150.00,0,1,40,annual,0
C004,1,24,80.00,2,8,22,monthly,0
C005,1,42,110.00,1,5,30,annual,0
C006,1,30,90.00,0,3,26,annual,0
C007,1,54,160.00,1,2,38,annual,0
C008,1,18,72.00,2,12,18,monthly,0
C009,1,27,88.00,1,7,24,annual,0
C010,1,45,130.00,0,4,32,annual,0
C011,1,21,76.00,2,15,16,monthly,0
C012,1,33,102.00,1,6,27,annual,0
C012,2,34,104.00,1,5,29,annual,0
C013,1,4,35.00,6,90,1,monthly,1
C014,1,8,48.00,5,70,3,monthly,1
C015,1,12,55.00,4,60,4,monthly,1
C016,1,3,25.00,8,120,0,monthly,1
C017,1,18,68.00,5,45,6,annual,1
C018,1,6,40.00,7,85,2,monthly,1
C019,1,10,52.00,4,65,5,monthly,1
C020,1,2,20.00,8,110,0,monthly,1
C021,1,24,78.00,3,38,7,annual,1
C022,1,5,32.00,6,95,1,monthly,1
C023,1,14,60.00,5,55,4,monthly,1
C024,1,7,44.00,7,80,2,monthly,1

What does this seed contain?

  • Each row represents one customer snapshot with activity and spending fields.
  • The churned column records the binary label used by the classifier later.
  • Customer C012 appears with snapshot versions 1 and 2.
  • The duplicate creates a visible table-grain problem for the quality gate.
  • Save seeds/customer_activity.csv.
  • Confirm customer_activity.csv appears inside seeds in the Explorer sidebar.

CSV rows shifted into the wrong columns?

Check that the header is the first line. Confirm that every customer record occupies one line.

Replace the file with the reference version below if a comma or line break is missing. Help me inspect the customer seed formatting.

✔️ Awesome, I've got everything!

Excellent. Double-check that customer_activity.csv is saved inside seeds.

ⓧ I'd like to double check the full code

customer_id,snapshot_version,tenure_months,monthly_spend,support_tickets,days_since_last_activity,logins_last_30d,contract_type,churned
C001,1,48,120.00,0,2,34,annual,0
C002,1,36,95.00,1,4,28,annual,0
C003,1,60,150.00,0,1,40,annual,0
C004,1,24,80.00,2,8,22,monthly,0
C005,1,42,110.00,1,5,30,annual,0
C006,1,30,90.00,0,3,26,annual,0
C007,1,54,160.00,1,2,38,annual,0
C008,1,18,72.00,2,12,18,monthly,0
C009,1,27,88.00,1,7,24,annual,0
C010,1,45,130.00,0,4,32,annual,0
C011,1,21,76.00,2,15,16,monthly,0
C012,1,33,102.00,1,6,27,annual,0
C012,2,34,104.00,1,5,29,annual,0
C013,1,4,35.00,6,90,1,monthly,1
C014,1,8,48.00,5,70,3,monthly,1
C015,1,12,55.00,4,60,4,monthly,1
C016,1,3,25.00,8,120,0,monthly,1
C017,1,18,68.00,5,45,6,annual,1
C018,1,6,40.00,7,85,2,monthly,1
C019,1,10,52.00,4,65,5,monthly,1
C020,1,2,20.00,8,110,0,monthly,1
C021,1,24,78.00,3,38,7,annual,1
C022,1,5,32.00,6,95,1,monthly,1
C023,1,14,60.00,5,55,4,monthly,1
C024,1,7,44.00,7,80,2,monthly,1

The configuration files now point to the folders you created. A connection check proves that dbt can read the project and reach the local DuckDB target.

  • Return to the active Visual Studio Code terminal from earlier.
  • Check the local project connection by running this command:
dbt debug --profiles-dir .

What does this command check?

  • The command reads dbt_project.yml from the current project folder.
  • The --profiles-dir option directs dbt to the local profiles.yml file.
  • The connection check verifies that the DuckDB adapter can use the configured target.

The terminal confirms that the connection test succeeds. Your local warehouse configuration is ready to load data.

Connection check not succeeding?

Confirm that the active terminal is still inside customer-churn-pipeline. Check that the virtual environment remains active.

Compare the profile name in both YAML files if dbt cannot find the target. Help me diagnose my dbt connection check.

Load and preview the raw seed

The seed command turns the saved CSV into a queryable relation. The DuckDB adapter persists that relation inside customer_features.duckdb.

  • Load the customer seed into the local database by running this command:
dbt seed --profiles-dir .

What does this command create?

  • dbt reads CSV files from the configured seeds folder.
  • The DuckDB adapter creates customer_features.duckdb in the top-level project folder.
  • The seed becomes the customer_activity relation in the main schema.

That is the ingestion path working. The terminal confirms that the seed loaded successfully.

  • Confirm customer_features.duckdb now appears in the Explorer sidebar.

Seed not loading?

Confirm that customer_activity.csv sits inside seeds. Check that the terminal is using the active virtual environment.

If the terminal mentions a file lock, close any other process using customer_features.duckdb. Help me troubleshoot the failed dbt seed.

Before you preview the relation, do you expect every customer ID to appear once?

  • Preview the loaded customer records by running this command:
dbt show --select customer_activity --profiles-dir .

What does this preview prove?

  • The selection targets the loaded customer_activity seed.
  • dbt queries the relation through the configured DuckDB connection.
  • The terminal displays the raw values before a staging model changes them.

The preview includes two rows for C012. Their snapshot versions are 1 and 2.

You have proved that the warehouse preserves the messy source data. The duplicate is now visible enough for a quality test to catch.

Duplicate rows missing from the preview?

Compare seeds/customer_activity.csv with the full reference file. Confirm that both C012 lines were saved before loading the seed.

Reload the seed after correcting the CSV. Help me find the missing C012 record.

Your raw customer data now lives in a persistent local warehouse. Next, you will place a quality gate in front of the duplicate records and watch it fail.

Expose the Duplicate Customer Records

Your raw-data preview proved that dbt Core can read the customer_activity seed from DuckDB. The next goal is to protect the classifier from repeated customer records.

Two snapshots for one customer would create duplicate training examples. A data quality test turns the one-row-per-customer requirement into an executable gate.

In this step, get ready to:
  • Preserve every raw row in a first-pass staging view.
  • Attach uniqueness and completeness tests to the customer identifier.
  • Run the quality gate against the staging view.
Create the first-pass staging model

A staging model gives the raw seed a stable place in the transformation graph. This first pass preserves the source grain so the quality gate can evaluate it.

  • Select the models/staging folder in the Explorer sidebar of Visual Studio Code.
  • Click the New File icon in the Explorer toolbar.
  • Enter stg_customers.sql as the file name.
  • Fill stg_customers.sql with the first-pass query by pasting this code:
select * from {{ ref('customer_activity') }}

What Does This Model Do?

  • The select * clause passes every seeded column into the staging view.
  • The ref('customer_activity') call records the seed as this model's dependency.
  • The staging view preserves both records for C012.
  • Save models/staging/stg_customers.sql.
  • Confirm stg_customers.sql appears inside models/staging in the Explorer sidebar.

Can't Find the Staging Model?

  • Confirm the file name ends with .sql.
  • Check that the file sits inside models/staging.

Need another pair of eyes? Help me check my staging model location.

✔️ Awesome, I've got everything!

Your first-pass staging model now preserves the raw customer records. Double-check that you saved stg_customers.sql.

ⓧ I'd like to double check the full code

select * from {{ ref('customer_activity') }}
Define the customer ID tests

A YAML schema file attaches tests to model columns. The unique test checks the model grain.

The not_null test checks that every row has a customer identifier. Together these checks define the minimum quality required before feature engineering.

  • Select the models folder in the Explorer sidebar.
  • Click the New File icon in the Explorer toolbar.
  • Enter schema.yml as the file name.
  • Define the staging tests in schema.yml by pasting this configuration:
version: 2

models:
  - name: stg_customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

What Does This Configuration Do?

  • The models section identifies stg_customers as the model under test.
  • The customer_id entry selects the column that defines each customer row.
  • The data_tests list requires every customer identifier to be present.
  • The same list requires every customer identifier to appear once.
  • Save models/schema.yml.
  • Confirm schema.yml appears directly inside models beside the staging and marts folders.

Tests Not Attached to the Model?

  • Confirm schema.yml sits directly inside models.
  • Check that the model name is exactly stg_customers.
  • Check that data_tests is indented beneath customer_id.

Still unsure about the indentation? Help me inspect my dbt schema file.

✔️ Awesome, I've got everything!

Your staging model now has tests for customer ID uniqueness and completeness. Double-check that you saved schema.yml.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: stg_customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
Run the quality gate

The build resolves the seed dependency before creating the staging view. It then evaluates the tests attached to customer_id.

Before you run the build, do you expect one row per customer to pass the uniqueness check?

  • Build the staging model with its attached tests by running this command in the active terminal:
dbt build --select stg_customers --profiles-dir .

What Does This Command Do?

  • The dbt build command creates the selected model before evaluating its attached tests.
  • The --select stg_customers option limits the build to the staging model.
  • The --profiles-dir . option uses the local DuckDB profile from your project folder.

The stg_customers view builds successfully. The unique test fails because C012 appears twice.

This is a useful failure. Your quality gate has caught duplicate training examples before they reach the classifier.

Seeing a Different Result?

  • Confirm the terminal still uses the active venv environment.
  • Check that stg_customers.sql still contains the first-pass query.
  • Confirm the seed contains both rows for C012.

If the build reports another problem, help me diagnose this dbt build result.

The duplicate is now visible as a blocked data-quality issue. Next, you'll repair the table grain so every customer contributes one tested feature row.

Deduplicate Customers and Build Features

The failed uniqueness test exposed the wrong table grain. Two snapshots for C012 would become two training examples for the same customer.

This step uses a DuckDB window function to keep the latest snapshot. You will then use dbt to turn the cleaned rows into tested numeric features.

In this step, get ready to:
  • Retain the latest snapshot for each customer.
  • Build a numeric feature table for the churn classifier.
  • Verify the tested pipeline through its generated lineage.
Deduplicate customer snapshots

A window function can rank rows inside each customer group. Sorting snapshot_version from highest to lowest gives the newest snapshot a rank of 1.

  • Select models/staging/stg_customers.sql in the Visual Studio Code Explorer sidebar.
  • Replace the file contents by pasting this code:
with ranked_customers as (
    select
        customer_id,
        snapshot_version,
        tenure_months,
        monthly_spend,
        support_tickets,
        days_since_last_activity,
        logins_last_30d,
        contract_type,
        churned,
        row_number() over (
            partition by customer_id
            order by snapshot_version desc
        ) as row_rank
    from {{ ref('customer_activity') }}
)

select
    customer_id,
    tenure_months,
    monthly_spend,
    support_tickets,
    days_since_last_activity,
    logins_last_30d,
    contract_type,
    churned
from ranked_customers
where row_rank = 1

How does the ranking remove duplicates?

  • The ranked_customers CTE groups snapshots by customer_id.
  • The descending snapshot_version order gives the latest snapshot the first position.
  • The final filter retains only rows where row_rank = 1.
  • Save models/staging/stg_customers.sql.

Before you rebuild, do you expect the duplicate customer to satisfy the uniqueness test now?

  • Rebuild the staging model from the active terminal by running this command:
dbt build --select stg_customers --profiles-dir .

What does this build prove?

  • The command rebuilds stg_customers from the updated SQL.
  • The configured tests check that every customer_id has a value.
  • The uniqueness test proves that the view now contains one row per customer.

You should see the staging model build successfully. Both tests now pass because only the latest C012 snapshot remains.

Is the uniqueness test still failing?

Confirm that where row_rank = 1 appears at the bottom of models/staging/stg_customers.sql. Check that you saved the file before rebuilding.

Check that partition by customer_id appears inside the window function. Help me debug why stg_customers still contains duplicate customer IDs.

✔️ Awesome, I've got everything!

Great. Double-check that you saved models/staging/stg_customers.sql.

ⓧ I'd like to double check the full code

with ranked_customers as (
    select
        customer_id,
        snapshot_version,
        tenure_months,
        monthly_spend,
        support_tickets,
        days_since_last_activity,
        logins_last_30d,
        contract_type,
        churned,
        row_number() over (
            partition by customer_id
            order by snapshot_version desc
        ) as row_rank
    from {{ ref('customer_activity') }}
)

select
    customer_id,
    tenure_months,
    monthly_spend,
    support_tickets,
    days_since_last_activity,
    logins_last_30d,
    contract_type,
    churned
from ranked_customers
where row_rank = 1
Build and test numeric features

Feature engineering converts business fields into values a classifier can consume. The feature table preserves customer_id for identification while keeping churned as the prediction label.

  • Select the models/marts folder in the Explorer sidebar.
  • Use the Explorer toolbar to create ml_customer_features.sql inside models/marts.
  • Build the numeric feature table by pasting this code:
select
    customer_id,
    cast(tenure_months as double) as tenure_months,
    cast(monthly_spend as double) as monthly_spend,
    cast(support_tickets as double) as support_tickets,
    cast(days_since_last_activity as double) as days_since_last_activity,
    cast(logins_last_30d as double) as logins_last_30d,
    cast(
        case when contract_type = 'monthly' then 1 else 0 end
        as double
    ) as is_monthly_contract,
    cast(churned as integer) as churned
from {{ ref('stg_customers') }}

How does this model prepare features?

  • The numeric casts give the classifier consistent input types.
  • The case expression converts monthly contracts to 1. It converts every other contract type to 0.
  • The ref('stg_customers') call records the dependency between the cleaned staging view and the feature table.
  • Save models/marts/ml_customer_features.sql.
  • Confirm ml_customer_features.sql appears inside models/marts in the Explorer sidebar.

Can't find the feature model?

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

Make sure the models folder you selected belongs to customer-churn-pipeline. Help me check the location of my dbt feature model.

✔️ Awesome, I've got everything!

Great. Double-check that you saved models/marts/ml_customer_features.sql.

ⓧ I'd like to double check the full code

select
    customer_id,
    cast(tenure_months as double) as tenure_months,
    cast(monthly_spend as double) as monthly_spend,
    cast(support_tickets as double) as support_tickets,
    cast(days_since_last_activity as double) as days_since_last_activity,
    cast(logins_last_30d as double) as logins_last_30d,
    cast(
        case when contract_type = 'monthly' then 1 else 0 end
        as double
    ) as is_monthly_contract,
    cast(churned as integer) as churned
from {{ ref('stg_customers') }}

The feature table now has a defined shape. Its data tests turn that shape into a quality contract for every future build.

  • Select models/schema.yml in the Explorer sidebar.
  • Replace the file contents with the documented staging model definition below:
version: 2

models:
  - name: stg_customers
    description: Latest available customer snapshot with one row per customer.
    columns:
      - name: customer_id
        description: Stable customer identifier and model grain.
        data_tests:
          - unique
          - not_null

What does this definition protect?

  • The model description records the one-row-per-customer grain.
  • The column description explains why customer_id defines that grain.
  • The existing tests continue to protect completeness and uniqueness.
  • Save models/schema.yml.
  • Build the current project state by running this command:
dbt build --profiles-dir .

What does this build check?

The command builds the deduplicated staging view and the new feature table. It also runs the staging tests in dependency order.

You should see stg_customers pass its tests. You should also see ml_customer_features build as a table.

Is the feature table failing to build?

Confirm that ml_customer_features.sql uses ref('stg_customers'). Check the indentation under models: in models/schema.yml.

Review the first error shown in the terminal. Help me diagnose this dbt feature-table build failure.

  • Add one blank line below the final not_null entry in models/schema.yml.
  • Add the first group of feature-table tests by pasting this code:
  - name: ml_customer_features
    description: Numeric customer features and binary churn label used by the classifier.
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
      - name: tenure_months
        data_tests:
          - not_null
      - name: monthly_spend
        data_tests:
          - not_null
      - name: support_tickets
        data_tests:
          - not_null

What do these tests cover?

  • The feature-table description identifies its machine learning purpose.
  • The customer_id tests protect the one-row-per-customer grain after materialization.
  • The numeric feature tests reject missing values before model training.
  • Save models/schema.yml.
  • Run the expanded test set by rebuilding the project:
dbt build --profiles-dir .

What changed in this build?

The build now tests the feature table's identifier and its first three numeric inputs. A successful result proves those columns are populated.

You should see the expanded set of tests pass. The feature table remains one row per customer.

Are the new tests missing?

Confirm that ml_customer_features aligns with stg_customers under models:. Check that each data_tests entry is indented beneath its column.

Save the YAML file before rebuilding. Help me fix the indentation in my dbt model tests.

  • Place the cursor below the support_tickets test in models/schema.yml.
  • Add the remaining feature and label tests by pasting this code:
      - name: days_since_last_activity
        data_tests:
          - not_null
      - name: logins_last_30d
        data_tests:
          - not_null
      - name: is_monthly_contract
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: [0, 1]
                quote: false
      - name: churned
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: [0, 1]
                quote: false

How do the final tests protect the model?

  • The remaining not_null tests reject incomplete model inputs.
  • The accepted-value test limits is_monthly_contract to binary values.
  • The accepted-value test also limits the churned label to binary values.
  • Save models/schema.yml.

Before you run the full build, do you expect the repaired grain and every feature constraint to pass together?

  • Build every model and run every configured test by running this command:
dbt build --profiles-dir .

What does the completed build prove?

  • dbt follows the dependency graph from the seed to the staging view.
  • The build materializes ml_customer_features after its upstream model succeeds.
  • The full test suite checks uniqueness and completeness. It also checks both binary columns.

You should see the command complete successfully with every configured test passing. Your customer_features.duckdb database now contains one tested numeric feature row per customer.

Is the final build still failing?

Start with the first failed node in the terminal. Confirm that each column name in models/schema.yml matches models/marts/ml_customer_features.sql exactly.

Check the YAML indentation around both accepted_values sections. Help me diagnose my final dbt build failure.

✔️ Awesome, I've got everything!

Strong work. Your staging view now has the correct grain. Your feature table is protected by the complete test suite.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: stg_customers
    description: Latest available customer snapshot with one row per customer.
    columns:
      - name: customer_id
        description: Stable customer identifier and model grain.
        data_tests:
          - unique
          - not_null

  - name: ml_customer_features
    description: Numeric customer features and binary churn label used by the classifier.
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
      - name: tenure_months
        data_tests:
          - not_null
      - name: monthly_spend
        data_tests:
          - not_null
      - name: support_tickets
        data_tests:
          - not_null
      - name: days_since_last_activity
        data_tests:
          - not_null
      - name: logins_last_30d
        data_tests:
          - not_null
      - name: is_monthly_contract
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: [0, 1]
                quote: false
      - name: churned
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: [0, 1]
                quote: false
Generate and inspect the lineage

Generated documentation turns model references into a visible data lineage graph. The graph shows how raw customer records become tested machine learning features.

  • Generate the project documentation from the active terminal by running this command:
dbt docs generate --profiles-dir .

What does this command generate?

dbt compiles the project's model descriptions and dependency metadata. It writes the generated documentation artifacts to target.

You should see the documentation generation complete successfully. The target folder now contains the generated project artifacts.

Did documentation generation fail?

Confirm that the previous full build completed successfully. Check the indentation and descriptions in models/schema.yml.

Run the generation command from the terminal attached to customer-churn-pipeline. Help me diagnose why dbt documentation generation is failing.

The documentation server keeps the terminal busy while you inspect the project. You will stop that process after checking the lineage so the DuckDB file is available for model training.

  • Serve the generated documentation locally by running this command:
dbt docs serve --profiles-dir .

What does the documentation server do?

The command previews the generated project documentation locally. The terminal remains attached to the server until you stop it.

Before you inspect the graph, which three project nodes do you expect to see connected?

  • Open the documentation link shown in the terminal in your browser.
  • Select ml_customer_features in the documentation site.
  • Open the lineage view for ml_customer_features.
  • Confirm that customer_activity feeds stg_customers.
  • Confirm that stg_customers feeds ml_customer_features.

You should see the complete path from the raw seed through the deduplicated staging view to the numeric feature table. That lineage proves the classifier's inputs have a traceable source.

  • Return to the VS Code terminal that is serving the documentation.
  • Press Ctrl+C to stop the documentation server.

The terminal prompt returns after the server stops. Your customer_features.duckdb file is now unlocked for the classifier.

Can't see the complete lineage?

Confirm that ml_customer_features.sql contains ref('stg_customers'). Confirm that stg_customers.sql contains ref('customer_activity').

Stop the documentation server before regenerating the artifacts. Help me find the missing dependency in my dbt lineage.

Your tested feature table is ready inside DuckDB. Next, you will train a churn classifier directly from those engineered rows.

Train the Churn Classifier

The quality gates have produced one clean numeric row per customer. That stable grain gives the classifier trustworthy inputs.

Now you will connect that table to a scikit-learn pipeline. A useful classifier needs numeric inputs without customer identifiers.

The script keeps customer_id outside the feature matrix. It reserves churned as the label.

In this step, get ready to:
  • Read the tested feature rows from DuckDB.
  • Train a scaled logistic regression classifier.
  • Inspect classification metrics plus ranked churn probabilities.
Read the tested feature table

The DuckDB file already contains the tested ml_customer_features table. A read-only connection lets Python consume those rows without changing the warehouse.

  • Create train_model.py inside the customer-churn-pipeline folder using Visual Studio Code.
  • Confirm train_model.py appears with the existing project files.

The new file gives the model its own repeatable entry point.

  • Add the imports plus the read-only database query to train_model.py by pasting this code:
import duckdb
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import classification_report
from sklearn.model_selection import train_test_split
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler

DATABASE_PATH = "customer_features.duckdb"

with duckdb.connect(DATABASE_PATH, read_only=True) as connection:
    rows = connection.execute(
        """
        select
            customer_id,
            tenure_months,
            monthly_spend,
            support_tickets,
            days_since_last_activity,
            logins_last_30d,
            is_monthly_contract,
            churned
        from ml_customer_features
        order by customer_id
        """
    ).fetchall()

What does this code do?

  • The imports bring in the database client plus the tools required for splitting, scaling, training, prediction, and evaluation.
  • DATABASE_PATH points to the warehouse created by the dbt pipeline.
  • read_only=True protects the database from changes during model training.
  • The SQL query selects the identifier first.
  • The six numeric model inputs occupy the middle columns.
  • The churned label occupies the final column.
  • Save train_model.py.

Before you run it, predict whether the read-only query can reach the database without a file-lock error.

  • Check the database connection by running this command in the active terminal from earlier:
python train_model.py

What should you see?

Good, the terminal prompt returns without a Python traceback. The script has imported its dependencies plus read the tested feature rows.

Can't read the DuckDB file?

If you see IO Error: Could not set lock on file, confirm the documentation server from earlier is still stopped. Close any other DuckDB client connected to customer_features.duckdb.

Check that train_model.py sits beside customer_features.duckdb. Help me troubleshoot why train_model.py cannot read customer_features.duckdb.

Train the scaled classifier

A train/test split reserves part of the data for evaluation. Feature scaling gives each numeric input a comparable range.

The scikit-learn Pipeline applies scaling inside the training workflow. It then fits a logistic regression classifier.

  • Add the data preparation plus model training code directly below ).fetchall() by pasting this code:
customer_ids = [row[0] for row in rows]
X = [list(row[1:-1]) for row in rows]
y = [row[-1] for row in rows]

X_train, X_test, y_train, y_test = train_test_split(
    X,
    y,
    test_size=0.25,
    random_state=42,
    stratify=y,
)

model = Pipeline(
    [
        ("scaler", StandardScaler()),
        ("classifier", LogisticRegression(max_iter=1000)),
    ]
)
model.fit(X_train, y_train)
predictions = model.predict(X_test)

How does the training flow work?

  • customer_ids stores the first value from each row for the later risk ranking.
  • X takes only the middle values, which excludes customer_id plus churned from the feature matrix.
  • y stores the final value from each row as the target label.
  • stratify=y preserves the label balance when the rows are split.
  • StandardScaler() learns scaling values from the training data inside the pipeline.
  • LogisticRegression(max_iter=1000) fits the classifier before predict() assigns test-set labels.
  • Save train_model.py.

Before you run it, predict whether the tested rows contain enough examples from both classes for the stratified split.

  • Confirm the split plus training pipeline works by running this command:
python train_model.py

What should you see?

That training loop is working when the terminal prompt returns without a traceback. The classifier has fitted on the scaled training rows plus generated test-set predictions.

Seeing a split or training error?

Check that X uses row[1:-1]. Including customer_id would place text inside the numeric feature matrix.

Confirm that y uses row[-1]. Help me debug the train/test split or model fitting code in train_model.py.

Print and inspect churn risk

The fitted model now needs an observable result. Classification metrics summarize test-set predictions while churn probabilities rank every customer.

  • Add the evaluation plus risk-ranking code directly below predictions = model.predict(X_test) by pasting this code:
print("Test-set classification report")
print(
    classification_report(
        y_test,
        predictions,
        target_names=["stayed", "churned"],
        zero_division=0,
    )
)

probabilities = model.predict_proba(X)[:, 1]
ranked_customers = sorted(
    zip(customer_ids, probabilities),
    key=lambda item: item[1],
    reverse=True,
)

print("Highest predicted churn risk")
for customer_id, probability in ranked_customers[:5]:
    print(f"{customer_id}: {probability:.1%}")

What does the output code do?

  • classification_report() compares the test labels with the classifier's predictions.
  • predict_proba(X)[:, 1] extracts the predicted churn probability for every customer.
  • zip(customer_ids, probabilities) reconnects each probability to the customer it describes.
  • reverse=True places the highest predicted probability first.
  • ranked_customers[:5] limits the printed ranking to five customers.
  • Save train_model.py.

Before you run the finished script, predict which customer behaviors will appear most often near the top of the ranking.

  • Verify the finished classifier by running this command:
python train_model.py

What should you see?

You will see Test-set classification report followed by the test-set performance summary. Below it, you will see Highest predicted churn risk followed by five customer IDs with percentages.

You closed the loop from tested SQL features to model predictions. The local pipeline now trains plus evaluates a working churn classifier.

Missing the report or risk ranking?

Confirm the reporting code sits below predictions = model.predict(X_test). Check that each print statement matches the completed file.

If the database lock message returns, close any process connected to customer_features.duckdb. Help me diagnose why train_model.py does not print the classification report and churn ranking.

✔️ Awesome, I've got everything!

Great. Double-check that you saved train_model.py after the successful run.

ⓧ I'd like to double check the full code

import duckdb
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import classification_report
from sklearn.model_selection import train_test_split
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler

DATABASE_PATH = "customer_features.duckdb"

with duckdb.connect(DATABASE_PATH, read_only=True) as connection:
    rows = connection.execute(
        """
        select
            customer_id,
            tenure_months,
            monthly_spend,
            support_tickets,
            days_since_last_activity,
            logins_last_30d,
            is_monthly_contract,
            churned
        from ml_customer_features
        order by customer_id
        """
    ).fetchall()

customer_ids = [row[0] for row in rows]
X = [list(row[1:-1]) for row in rows]
y = [row[-1] for row in rows]

X_train, X_test, y_train, y_test = train_test_split(
    X,
    y,
    test_size=0.25,
    random_state=42,
    stratify=y,
)

model = Pipeline(
    [
        ("scaler", StandardScaler()),
        ("classifier", LogisticRegression(max_iter=1000)),
    ]
)
model.fit(X_train, y_train)
predictions = model.predict(X_test)

print("Test-set classification report")
print(
    classification_report(
        y_test,
        predictions,
        target_names=["stayed", "churned"],
        zero_division=0,
    )
)

probabilities = model.predict_proba(X)[:, 1]
ranked_customers = sorted(
    zip(customer_ids, probabilities),
    key=lambda item: item[1],
    reverse=True,
)

print("Highest predicted churn risk")
for customer_id, probability in ranked_customers[:5]:
    print(f"{customer_id}: {probability:.1%}")
  • Select seeds/customer_activity.csv from the project files in Visual Studio Code.
  • Match each of the five printed customer IDs to its row in the CSV.
  • Compare their tenure_months values.
  • Compare their support_tickets values.
  • Compare their days_since_last_activity values.
  • Compare their logins_last_30d values.
  • Compare their contract_type values.

This comparison connects each predicted probability to the behavior stored in the source data. Recurring feature patterns help you inspect what the model associates with churn risk.

What do these metrics prove?

This small synthetic dataset demonstrates the full pipeline from tested rows to model output. The printed metrics confirm that the training plus evaluation workflow runs.

Real-world model quality requires representative data. It also requires broader validation before predictions guide customer decisions.

Secret mission

Explain the Model's Strongest Signals

Inspect the fitted classifier to reveal which engineered features carry the strongest linear signals. You will rank signed standardized coefficients while keeping each result aligned with its feature name.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your local project ready, pause your work, or remove it entirely. Every resource in this project stays on your computer. There are no ongoing service charges.

Resources you used:

  • A local Python virtual environment stored in venv.
  • The customer_features.duckdb DuckDB database containing the loaded seed data plus tested feature tables.
  • The generated target folder containing dbt Core build artifacts plus documentation.
  • The source pipeline files inside customer-churn-pipeline.

Keep everything running

No action is needed while you are actively building. Your local project remains available for more pipeline experiments.

  • Leave the customer-churn-pipeline folder intact.
  • Keep customer_features.duckdb so you can rerun the classifier without rebuilding the database.

Pause - I'll come back to this later

The dbt documentation server is already stopped. No local process needs to remain active while you take a break.

  • Close Visual Studio Code to end the active terminal session.
  • Leave the customer-churn-pipeline folder in place for your return.

Your pipeline files remain ready on your computer.

Delete - I don't want to use this again

Remove generated local outputs if you want a lightweight source copy. Remove the entire project folder if you want to start fresh.

Deleting local files can feel final. The first option preserves every source file required to rebuild the generated outputs.

For a lightweight source copy:

  • Press the Windows key to open search.
  • Search for File Explorer.
  • Select File Explorer from the results.
  • Locate the customer-churn-pipeline folder currently open in Visual Studio Code.
  • Remove the venv folder.
  • Remove the target folder.
  • Remove the customer_features.duckdb file.

You should no longer see venv, target, or customer_features.duckdb in the project folder. Your source files remain.

For complete removal:

  • Remove the entire customer-churn-pipeline folder in File Explorer.

You should no longer see the customer-churn-pipeline folder in File Explorer.

Nice Work!

Nice Work!

You did it! You built a local ELT pipeline with dbt Core plus DuckDB. Tested numeric customer features now feed a scikit-learn logistic regression classifier.

You've learned how to:

  • Build a local ELT pipeline that turns a version-controlled customer CSV into documented SQL models.
  • Expose duplicate customer IDs through a deliberate data quality failure. Repair the table grain to produce one tested numeric row per customer.
  • Train a scaled churn classifier with scikit-learn. Compare its classification metrics with ranked churn probabilities.
  • Secret Mission: Rank standardized coefficients by absolute size to inspect the model's strongest linear signals.

Ready to quiz yourself?