Build a Local Medallion Pipeline

Build a local music analytics pipeline with dbt, DuckDB, and Streamlit.

Introduction

30 Second Summary

Listening totals can look believable even when repeated or broken entries have quietly distorted them. A polished chart only earns trust when every number has a clear path back to the source.

In this project, you will use a local medallion architecture to turn flawed music-listening events into trusted dashboard metrics. Its Bronze, Silver, plus Gold layers reveal how 256 raw rows become 240 trusted events before Streamlit visualizes the result.

What You'll Build

You will demo a local dashboard where changing the genre updates four trusted KPIs, a daily trend, plus an artist ranking from the same Gold data.

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

  • A visible quality check that exposes 12 duplicate rows plus 4 invalid field checks before the pipeline delivers 240 trusted events.
  • A tested transformation trail you can inspect in a local dbt lineage site from unchanged Bronze data to dashboard-ready Gold models.
  • An interactive Streamlit dashboard that filters total plays, listeners, listening minutes, skip rate, daily trends, plus artist rankings by genre.
  • Secret Mission: Add a listener-loyalty mart that ranks listeners by active listening days in a new dashboard chart.

Are there any prerequisites?

Comfort with SQL plus basic Python is enough for this workshop on a Mac with Git plus Visual Studio Code.

The setup step verifies Python plus Xcode Command Line Tools before installing every pinned package locally.

Before We Start

Before you touch the data, commit to the reason for building MusicPulse. The cloud-free pipeline moves flawed events from unchanged Bronze storage through validated Silver data into trusted Gold analytics marts.

Prepare Your Local Workshop

Environment drift can bury the data-engineering lesson under version conflicts. MusicPulse needs a consistent local workshop so every transformation produces the same result.

You will verify Python before isolating the project dependencies. You will then configure dbt Core with DuckDB before generating the flawed source dataset.

In this step, get ready to:
  • Verify the Mac prerequisites for the workshop.
  • Create an isolated workspace with the pinned dependencies.
  • Configure MusicPulse before generating 256 source rows.
Verify the Mac prerequisites

The Xcode Command Line Tools provide system components required by some Python packages. A supported Python version keeps every pinned dependency compatible.

  • Press Cmd+Space to open Spotlight Search.
  • Search for the Terminal app.
  • Open the Terminal result.
  • Check for Xcode Command Line Tools by running this command:
pkgutil --pkg-info=com.apple.pkg.CLTools_Executables

What does this command check?

The command asks macOS for information about the installed package. Package details confirm that the command-line tools are available.

✔️ I see package information

Your Mac has the command-line tools required for this workshop. That clears the first prerequisite.

ⓧ The package is unavailable

macOS can request the official command-line tools through its installation dialog. Expect the download to take several minutes.

  • Request the installer by running this command:
xcode-select --install

What does this command do?

The command opens the macOS installation dialog for Xcode Command Line Tools. Terminal can use the tools after the installation finishes.

  • Complete the installation in the macOS dialog.
  • Return to the Terminal window from earlier.
  • Repeat the package check by running this command:
pkgutil --pkg-info=com.apple.pkg.CLTools_Executables

What confirms the installation?

Package information now confirms that macOS recognizes the installed command-line tools.

Still missing the command-line tools?

Complete any pending macOS installation dialog before repeating the check. Return to a fresh Terminal window if the installation finished while Terminal was open.

Still stuck? Help me verify Xcode Command Line Tools on my Mac.

  • Check the installed Python version by running this command:
python3 --version

What does this command check?

The command prints the Python 3 version used by Terminal. MusicPulse supports versions 3.11 through 3.14.

✔️ I see version 3.11 or higher

Your Python version supports every pinned MusicPulse package. Continue if the minor version is between 3.11 and 3.14.

ⓧ I see an older version

The pinned pandas release requires Python 3.11 or newer. Install Python 3.14.7 before creating the virtual environment.

  • Visit the official Python macOS downloads page.
  • Download the Python 3.14.7 macOS installer.
  • Run the installer from your Downloads folder.
  • Complete the installation prompts.
  • Return to Terminal after the installation finishes.
  • Check the active Python version by running this command:
python3 --version

What should the new check confirm?

The version output should now fall between 3.11 and 3.14.

ⓧ The command is unavailable

Terminal cannot currently find Python 3. The official Python 3.14.7 installer adds a supported runtime to your Mac.

  • Visit the official Python macOS downloads page.
  • Download the Python 3.14.7 macOS installer.
  • Run the installer from your Downloads folder.
  • Complete the installation prompts.
  • Return to Terminal after the installation finishes.
  • Confirm Python is available by running this command:
python3 --version

What should the check confirm?

Terminal should print a Python version between 3.11 and 3.14.

Still seeing the wrong Python version?

Return to a fresh Terminal window after the installer finishes. This gives macOS another chance to load the updated command path.

Still stuck? Help me activate a supported Python installation.

Build the isolated workspace

A virtual environment keeps MusicPulse packages separate from other Python projects. Version pins make the workshop reproducible when you run it again.

  • Return to Finder using the macOS Dock.
  • Select Desktop in the Finder sidebar.
  • Select File in the menu bar.
  • Select New Folder.
  • Enter musicpulse as the folder name.

The empty musicpulse folder now sits on your Desktop. This named location keeps every local artifact together.

You will use Visual Studio Code to create the workshop files. Its integrated terminal opens directly in the selected workspace.

  • Press Cmd+Space on macOS to open Spotlight Search.
  • Search for Visual Studio Code.
  • Open the Visual Studio Code result.
  • Select File in the menu bar.
  • Select Open Folder.
  • Choose the musicpulse folder on your Desktop.
  • Select Open.

The Explorer now shows musicpulse as the open workspace.

  • Select the New Folder icon in the Explorer toolbar.
  • Enter seeds as the folder name.

The seeds folder now appears inside musicpulse. The generator writes its CSV into this folder.

  • Select the New File icon in the Explorer toolbar.
  • Enter requirements.txt as the file name.

The empty requirements.txt file opens in the editor. It will hold the workshop dependency contract.

  • Add the pinned dependencies by pasting this content:
dbt-core==1.12.3
dbt-duckdb==1.11.0
duckdb==1.5.5
pandas==3.0.5
streamlit==1.62.0

Why pin every dependency?

  • Each line fixes one package to a known version.
  • The dbt adapter connects dbt Core to the local DuckDB file.
  • The pandas package supports tabular results for the dashboard.
  • Streamlit turns the Gold tables into an interactive local app.
  • Save requirements.txt.
  • Confirm requirements.txt appears under musicpulse in the Explorer.

Requirements file missing?

Check that the Explorer header shows the musicpulse workspace. Recreate the file there if it was saved elsewhere.

Still stuck? Help me create the MusicPulse requirements file.

  • Select Terminal in the Visual Studio Code menu bar.
  • Select New Terminal.

The integrated terminal opens inside the Desktop musicpulse folder. Commands now create local resources in the correct workspace.

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

What does this command create?

Python creates an isolated environment inside .venv. Packages installed after activation stay inside the MusicPulse workspace.

  • Confirm the .venv folder appears in the Explorer.

Virtual environment missing?

Check that the terminal is open inside the Desktop musicpulse folder. Confirm that the Python version check still reports a supported version.

Still stuck? Help me create the MusicPulse virtual environment.

  • Activate the virtual environment by running this command:
source .venv/bin/activate

What does activation change?

Activation points Python commands at the local environment. Your terminal prompt should include .venv while the environment is active.

Your prompt now identifies the isolated MusicPulse environment. Future package installations stay attached to this project.

  • Install every pinned dependency by running this command:
python -m pip install -r requirements.txt

What does this command install?

Python reads the exact package versions from requirements.txt. The installation can sit quietly while package files download.

  • Confirm the dbt installation by running this command:
dbt --version

What should dbt report?

The version report should list dbt Core 1.12.3. It should also list the DuckDB adapter at 1.11.0.

That is the dependency layer locked in. MusicPulse now has an isolated runtime with the expected analytics tools.

Does dbt fail to run?

Check that your terminal prompt still includes .venv. Repeat the activation command if you opened a different terminal.

Still stuck? Help me fix the pinned dbt installation.

Configure MusicPulse and generate data

dbt needs a project definition plus a local connection profile. The generator then creates the exact flawed event file used throughout the medallion pipeline.

  • Select the New File icon in the Explorer toolbar.
  • Enter dbt_project.yml as the file name.

The empty dbt_project.yml file opens in the editor. This file defines how dbt finds project resources.

  • Configure the dbt project by pasting this content:
name: musicpulse
version: "1.0.0"
config-version: 2
profile: musicpulse

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

models:
  musicpulse:
    +persist_docs:
      relation: true
      columns: true
    silver:
      +materialized: table
    gold:
      +materialized: table

What does the project configuration control?

  • The path settings tell dbt where to find models.
  • The path settings also identify seeds plus tests.
  • The folder settings materialize Silver models as tables.
  • The folder settings materialize Gold models as tables.
  • Save dbt_project.yml.
  • Confirm dbt_project.yml appears at the top level of musicpulse.

Project configuration not saving?

Check that the filename ends with .yml. Confirm that indentation uses spaces consistently.

Still stuck? Help me check the dbt project configuration.

  • Select the New File icon in the Explorer toolbar.
  • Enter profiles.yml as the file name.

The empty profiles.yml file opens beside the project definition. This profile keeps the warehouse inside the MusicPulse folder.

  • Configure the local DuckDB connection by pasting this content:
musicpulse:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: musicpulse.duckdb
      schema: main
      threads: 4

How does the local profile work?

The profile points dbt at musicpulse.duckdb in the current folder. DuckDB creates that database file when dbt first needs it.

Every relation uses the main schema. Four threads allow dbt to process independent work concurrently.

  • Save profiles.yml.
  • Confirm profiles.yml appears beside dbt_project.yml.

Profile not appearing?

Check that profiles.yml sits directly inside musicpulse. A profile saved inside another folder will not match this workshop layout.

Still stuck? Help me verify the local DuckDB profile.

  • Select the New File icon in the Explorer toolbar.
  • Enter .gitignore as the file name.

The empty .gitignore file opens in the editor. It will separate source files from generated local state.

  • Add the ignore rules by pasting this content:
.venv/
__pycache__/
.DS_Store
logs/
target/
musicpulse.duckdb
seeds/raw_listening_events.csv

Why ignore these paths?

These paths contain generated files or machine-specific state. Keeping them out of Git leaves the project reproducible from its source files.

  • Save .gitignore.
  • Confirm .gitignore appears in the Explorer.

Ignore file missing?

Check that the filename begins with a full stop. Recreate the file if Visual Studio Code saved it without the leading character.

Still stuck? Help me verify the MusicPulse ignore rules.

  • Select the New File icon in the Explorer toolbar.
  • Enter README.md as the file name.

The empty README.md file opens in the editor. It records the architecture plus the repeatable run sequence.

  • Document the workshop by pasting this content:
# MusicPulse

MusicPulse is a cloud-free portfolio project that turns flawed synthetic music-listening events into trusted dashboard metrics.

## Architecture

1. Bronze: `raw_listening_events` preserves the generated CSV without cleanup.
2. Silver: `silver_listening_events` casts, validates, deduplicates, and adds `is_skipped`.
3. Gold: KPI, daily-trend, and artist-performance models serve the dashboard.
4. Output: Streamlit reads only Gold relations from a local DuckDB file.

The dataset and all artist, listener, and track names are fictional and generated locally.

## Run locally

1. Create and activate `.venv`.
2. Install dependencies with `python -m pip install -r requirements.txt`.
3. Generate data with `python generate_data.py`.
4. Build and test with `dbt build`.
5. Launch the dashboard with `streamlit run app.py`.

Generate the lineage site with `dbt docs generate`, then open it with `dbt docs serve`. Stop a running server with `Ctrl+C`.

## Portfolio talking points

- Why the Bronze result is intentionally misleading
- How Silver enforces event grain and validity
- Why Gold tables match dashboard questions
- How dbt tests and lineage make transformations reviewable
- Why DuckDB is sufficient for a local analytical workload

What does the README capture?

The README records the Bronze-to-Gold architecture. It also captures the commands used to regenerate the project locally.

  • Save README.md.
  • Confirm the rendered preview begins with the MusicPulse heading.

README formatting look wrong?

Check that every heading keeps its leading hash characters. Confirm that each numbered item remains on its own line.

Still stuck? Help me check the MusicPulse README formatting.

  • Select the New File icon in the Explorer toolbar.
  • Enter generate_data.py as the file name.

The empty generate_data.py file opens in the editor. This generator creates the reproducible defects that drive the Bronze-to-Silver lesson.

  • Add the complete generator from the full-code comparison below.
  • Compare every existing workshop file against the same reference.

✔️ Awesome, I've got everything!

Great. Save every project file before returning to the integrated terminal.

ⓧ I'd like to double check the full code

Compare each file against the complete workshop setup below. Copy the generator into generate_data.py if that file is still empty.

dbt-core==1.12.3
dbt-duckdb==1.11.0
duckdb==1.5.5
pandas==3.0.5
streamlit==1.62.0

What belongs in requirements.txt?

This file contains the five pinned package versions used by the workshop.

.venv/
__pycache__/
.DS_Store
logs/
target/
musicpulse.duckdb
seeds/raw_listening_events.csv

What belongs in .gitignore?

This file excludes the local environment plus generated database artifacts. It also excludes generated data plus dbt build output.

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

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

models:
  musicpulse:
    +persist_docs:
      relation: true
      columns: true
    silver:
      +materialized: table
    gold:
      +materialized: table

What belongs in dbt_project.yml?

This file defines the resource paths plus documentation settings. It also defines the Silver and Gold materializations.

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

What belongs in profiles.yml?

This file points dbt at the local DuckDB file. It places relations in the main schema.

# MusicPulse

MusicPulse is a cloud-free portfolio project that turns flawed synthetic music-listening events into trusted dashboard metrics.

## Architecture

1. Bronze: `raw_listening_events` preserves the generated CSV without cleanup.
2. Silver: `silver_listening_events` casts, validates, deduplicates, and adds `is_skipped`.
3. Gold: KPI, daily-trend, and artist-performance models serve the dashboard.
4. Output: Streamlit reads only Gold relations from a local DuckDB file.

The dataset and all artist, listener, and track names are fictional and generated locally.

## Run locally

1. Create and activate `.venv`.
2. Install dependencies with `python -m pip install -r requirements.txt`.
3. Generate data with `python generate_data.py`.
4. Build and test with `dbt build`.
5. Launch the dashboard with `streamlit run app.py`.

Generate the lineage site with `dbt docs generate`, then open it with `dbt docs serve`. Stop a running server with `Ctrl+C`.

## Portfolio talking points

- Why the Bronze result is intentionally misleading
- How Silver enforces event grain and validity
- Why Gold tables match dashboard questions
- How dbt tests and lineage make transformations reviewable
- Why DuckDB is sufficient for a local analytical workload

What belongs in README.md?

This file explains the architecture plus the local run sequence. It also records the portfolio talking points.

import csv
from datetime import datetime, timedelta

TRACKS = [
    ("Neon Skyline", "Aster Vale", "Synthwave", 210000),
    ("Midnight Signals", "Nova Bloom", "Electronic", 198000),
    ("Paper Satellites", "The Quiet Lines", "Indie", 225000),
    ("Golden Hour Drive", "Mira Coast", "Pop", 204000),
    ("Rain on Platform Nine", "Northbound", "Lo-fi", 240000),
    ("Static Hearts", "Echo Avenue", "Rock", 232000),
    ("Blue Orbit", "Aster Vale", "Synthwave", 216000),
    ("Afterglow Letters", "Mira Coast", "Pop", 195000),
]
DEVICES = ["mobile", "desktop", "tablet", "speaker"]
FIELDS = [
    "event_id",
    "listener_id",
    "track_name",
    "artist_name",
    "genre",
    "played_at",
    "track_duration_ms",
    "ms_played",
    "device",
]

base_time = datetime(2026, 1, 1, 8, 0, 0)
rows = []

for index in range(240):
    track_name, artist_name, genre, duration_ms = TRACKS[(index * 3) % len(TRACKS)]
    completion = 0.20 if index % 5 == 0 else 0.55 if index % 7 == 0 else 0.92
    played_at = base_time + timedelta(
        days=index // 12,
        hours=(index * 5) % 24,
        minutes=(index * 7) % 60,
    )
    rows.append(
        {
            "event_id": f"event_{index + 1:04d}",
            "listener_id": f"listener_{(index % 24) + 1:03d}",
            "track_name": track_name,
            "artist_name": artist_name,
            "genre": genre,
            "played_at": played_at.isoformat(timespec="seconds"),
            "track_duration_ms": duration_ms,
            "ms_played": int(duration_ms * completion),
            "device": DEVICES[index % len(DEVICES)],
        }
    )

rows.extend(dict(row) for row in rows[:12])
rows.extend(
    [
        {
            "event_id": "bad_0001",
            "listener_id": "listener_001",
            "track_name": "Unknown Track",
            "artist_name": "",
            "genre": "Pop",
            "played_at": "2026-01-03T10:00:00",
            "track_duration_ms": "200000",
            "ms_played": "120000",
            "device": "mobile",
        },
        {
            "event_id": "bad_0002",
            "listener_id": "listener_002",
            "track_name": "Broken Clock",
            "artist_name": "Nova Bloom",
            "genre": "Electronic",
            "played_at": "not-a-timestamp",
            "track_duration_ms": "180000",
            "ms_played": "90000",
            "device": "desktop",
        },
        {
            "event_id": "bad_0003",
            "listener_id": "listener_003",
            "track_name": "Reverse Play",
            "artist_name": "Echo Avenue",
            "genre": "Rock",
            "played_at": "2026-01-04T12:00:00",
            "track_duration_ms": "220000",
            "ms_played": "-500",
            "device": "tablet",
        },
        {
            "event_id": "bad_0004",
            "listener_id": "listener_004",
            "track_name": "Zero Length",
            "artist_name": "Northbound",
            "genre": "Lo-fi",
            "played_at": "2026-01-05T14:00:00",
            "track_duration_ms": "0",
            "ms_played": "0",
            "device": "speaker",
        },
    ]
)

with open(
    "seeds/raw_listening_events.csv",
    "w",
    newline="",
    encoding="utf-8",
) as csv_file:
    writer = csv.DictWriter(csv_file, fieldnames=FIELDS)
    writer.writeheader()
    writer.writerows(rows)

print(f"Created seeds/raw_listening_events.csv with {len(rows)} rows.")

How does the generator shape the workshop?

The first loop creates 240 deterministic listening events. Fixed tracks plus listeners and timestamps make every run reproducible.

The final additions create 12 duplicate rows plus four malformed rows. Those defects produce the reporting risk that Bronze exposes.

  • Save generate_data.py.
  • Return to the integrated terminal from earlier.

Before you run the final checks, which Python version details plus generator message do you expect to see?

  • Verify the active Python version by running this command:
python3 --version

What should the Python check show?

The terminal should report a supported Python version from 3.11 through 3.14.

  • Verify the pinned dbt installation by running this command:
dbt --version

What should the dbt check show?

The report should identify dbt Core 1.12.3. It should identify the DuckDB adapter at 1.11.0.

  • Generate the workshop dataset by running this command:
python generate_data.py

What does the generator confirm?

The script writes the synthetic source events to seeds/raw_listening_events.csv. The message reports the number of rows written.

You should see Created seeds/raw_listening_events.csv with 256 rows. The CSV now contains 240 deterministic events plus 12 duplicates plus four malformed records.

Generator did not create 256 rows?

Check that the integrated terminal is inside the Desktop musicpulse folder. Confirm that generate_data.py sits beside requirements.txt.

Confirm that the seeds folder exists. Reopen the full-code comparison if the script reports a syntax error.

Still stuck? Help me troubleshoot the MusicPulse generator.

Your local workshop is reproducible. Next up, you will load the untouched CSV into Bronze before exposing the misleading raw metrics.

Profile the Bronze Data

Your reproducible workshop is ready. The generated CSV gives MusicPulse a fixed source dataset to examine.

The Bronze layer preserves every source row exactly as received. That fidelity carries duplicate events and malformed values into raw metrics.

In this step, get ready to:
  • Inspect the source event grain without changing the dataset.
  • Load the source rows unchanged into the local database.
  • Profile the raw counts to expose the reporting risk.
Inspect the source event grain

An event grain defines what one row is supposed to represent. Here, one event_id should identify one listening event.

  • Switch back to VS Code.
  • Select seeds/raw_listening_events.csv from the project file list.
  • Read the event_id column to identify the intended row grain.
  • Scan artist_name for missing text.
  • Scan played_at for malformed timestamps.
  • Scan ms_played for negative values.
  • Scan track_duration_ms for nonpositive values.
  • Keep seeds/raw_listening_events.csv unchanged.

The file contains claimed listening events at one row per event_id. Several source values already look unsafe for reporting.

Load the unchanged Bronze seed

A dbt Core seed loads a configured CSV into a database table. MusicPulse uses this step to preserve the source rows in a local DuckDB file without applying cleanup.

  • Load the generated dataset into Bronze by running this command:
dbt seed

What does this command do?

dbt Core reads CSV files from the configured seeds path. It loads raw_listening_events.csv into main.raw_listening_events.

The configured path creates musicpulse.duckdb locally. Every source row remains available for the Bronze profile.

Good progress. The untouched CSV now has a matching Bronze table in your local database.

Seed not loading?

  • Confirm that the terminal prompt still shows the activated .venv environment.
  • Confirm that seeds/raw_listening_events.csv remains inside the musicpulse folder.
  • Confirm that profiles.yml points to musicpulse.duckdb in the main schema.
  • Still stuck? Help me troubleshoot my MusicPulse dbt seed.
Build the Bronze profile

The seeded table can now answer a deliberately naive reporting question. The profile counts every raw row before measuring duplicate IDs and malformed fields.

  • Use the project file list in VS Code to create bronze_report.py inside the musicpulse folder.
  • Add the database connection and quality query by pasting this code:
import duckdb

connection = duckdb.connect(database="musicpulse.duckdb", read_only=True)

raw_rows, duplicate_rows, invalid_field_checks = connection.execute(
    """
    SELECT
        COUNT(*) AS raw_rows,
        COUNT(*) - COUNT(DISTINCT event_id) AS duplicate_rows,
        SUM(CASE WHEN artist_name IS NULL OR TRIM(artist_name) = '' THEN 1 ELSE 0 END)
        + SUM(CASE WHEN TRY_CAST(played_at AS TIMESTAMP) IS NULL THEN 1 ELSE 0 END)
        + SUM(CASE WHEN TRY_CAST(ms_played AS BIGINT) < 0 THEN 1 ELSE 0 END)
        + SUM(CASE WHEN TRY_CAST(track_duration_ms AS BIGINT) <= 0 THEN 1 ELSE 0 END)
        AS invalid_field_checks
    FROM main.raw_listening_events
    """
).fetchone()

What does this query measure?

  • The read-only connection protects musicpulse.duckdb from changes while the script profiles it.
  • The first COUNT(*) measures every raw Bronze row.
  • The distinct event_id comparison measures repeated event identifiers.
  • The field checks detect a missing artist name or an invalid timestamp.
  • The remaining checks detect negative play time or nonpositive track duration.
  • TRY_CAST turns malformed values into NULL so the profile can count them safely.
  • Add the raw artist leaderboard and printed report below the existing code:
top_artist, raw_artist_plays = connection.execute(
    """
    SELECT artist_name, COUNT(*) AS raw_artist_plays
    FROM main.raw_listening_events
    WHERE artist_name IS NOT NULL AND TRIM(artist_name) <> ''
    GROUP BY artist_name
    ORDER BY raw_artist_plays DESC, artist_name
    LIMIT 1
    """
).fetchone()

connection.close()

print(f"Raw rows: {raw_rows}")
print(f"Duplicate rows: {duplicate_rows}")
print(f"Invalid field checks: {invalid_field_checks}")
print(f"Naive top artist: {top_artist} with {raw_artist_plays} raw plays")
print("The Bronze count is intentionally not dashboard-ready.")

How does the report expose the risk?

  • The leaderboard groups every usable artist_name without removing duplicated events.
  • The ordering selects the artist with the highest naive raw count.
  • connection.close() releases the read-only database connection after both queries finish.
  • The printed lines place the raw total beside the quality defects that inflated it.
  • Save bronze_report.py.

Before you run the profile, do you think the 256 source rows all represent valid unique plays?

  • Reveal the Bronze quality gap by running this command:
python bronze_report.py

What should I see?

  • The first line says Raw rows: 256.
  • The second line says Duplicate rows: 12.
  • The third line says Invalid field checks: 4.
  • The leaderboard says Naive top artist: Aster Vale with 64 raw plays.
  • The final line confirms that the Bronze count is intentionally unsafe for the dashboard.

That's the reporting risk exposed. Bronze preserved all 256 source rows while the profile found 12 duplicates and 4 invalid field checks.

Profile not running?

  • Confirm that dbt seed completed before the profile accessed main.raw_listening_events.
  • Check that bronze_report.py sits beside musicpulse.duckdb in the musicpulse folder.
  • Compare the table name in both queries with main.raw_listening_events.
  • Still stuck? Help me debug my Bronze profile.

✔️ Awesome, I've got everything!

Your saved bronze_report.py now profiles the unchanged Bronze table through a read-only database connection.

ⓧ I'd like to double check the full code

Compare your saved bronze_report.py with this complete version.

import duckdb

connection = duckdb.connect(database="musicpulse.duckdb", read_only=True)

raw_rows, duplicate_rows, invalid_field_checks = connection.execute(
    """
    SELECT
        COUNT(*) AS raw_rows,
        COUNT(*) - COUNT(DISTINCT event_id) AS duplicate_rows,
        SUM(CASE WHEN artist_name IS NULL OR TRIM(artist_name) = '' THEN 1 ELSE 0 END)
        + SUM(CASE WHEN TRY_CAST(played_at AS TIMESTAMP) IS NULL THEN 1 ELSE 0 END)
        + SUM(CASE WHEN TRY_CAST(ms_played AS BIGINT) < 0 THEN 1 ELSE 0 END)
        + SUM(CASE WHEN TRY_CAST(track_duration_ms AS BIGINT) <= 0 THEN 1 ELSE 0 END)
        AS invalid_field_checks
    FROM main.raw_listening_events
    """
).fetchone()

top_artist, raw_artist_plays = connection.execute(
    """
    SELECT artist_name, COUNT(*) AS raw_artist_plays
    FROM main.raw_listening_events
    WHERE artist_name IS NOT NULL AND TRIM(artist_name) <> ''
    GROUP BY artist_name
    ORDER BY raw_artist_plays DESC, artist_name
    LIMIT 1
    """
).fetchone()

connection.close()

print(f"Raw rows: {raw_rows}")
print(f"Duplicate rows: {duplicate_rows}")
print(f"Invalid field checks: {invalid_field_checks}")
print(f"Naive top artist: {top_artist} with {raw_artist_plays} raw plays")
print("The Bronze count is intentionally not dashboard-ready.")

How should I compare the file?

Check the connection settings and both SQL queries first. Then compare the five printed lines at the bottom of the file.

Your Bronze layer has revealed exactly why raw counts can mislead a dashboard. Next, you'll repair the event grain in a tested Silver layer.

Build the Silver Layer

Your Bronze layer exposed duplicate event IDs. It also exposed malformed values that inflate the raw result.

The Silver layer restores one trustworthy row per listening event. It applies safe types before rejecting invalid records.

In this step, get ready to:
  • Normalize the raw event fields.
  • Deduplicate invalid Bronze records.
  • Test the 240-row Silver result.
Normalize and deduplicate the events

A dbt model expresses each transformation as a query. The model uses DuckDB functions to clean the raw values safely.

  • Press Cmd+Space to open macOS search.
  • Type Visual Studio Code into the search bar.
  • Press Enter to open Visual Studio Code.
  • Select the existing musicpulse folder with the folder picker.
  • Create models/silver/silver_listening_events.sql from the file sidebar.
  • Add the typing and deduplication stages by pasting this code at the top of silver_listening_events.sql:
WITH typed AS (
    SELECT
        TRIM(event_id) AS event_id,
        TRIM(listener_id) AS listener_id,
        NULLIF(TRIM(track_name), '') AS track_name,
        NULLIF(TRIM(artist_name), '') AS artist_name,
        NULLIF(TRIM(genre), '') AS genre,
        TRY_CAST(played_at AS TIMESTAMP) AS played_at,
        TRY_CAST(track_duration_ms AS BIGINT) AS track_duration_ms,
        TRY_CAST(ms_played AS BIGINT) AS ms_played,
        LOWER(TRIM(device)) AS device
    FROM {{ ref('raw_listening_events') }}
),

deduplicated AS (
    SELECT *
    FROM typed
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY event_id
        ORDER BY event_id
    ) = 1
)

What do these stages do?

  • The typed CTE standardizes the source fields before reporting uses them.
  • TRY_CAST turns malformed dates or numbers into NULL without stopping the query.
  • NULLIF converts blank descriptive fields into NULL so validation can detect them.
  • LOWER gives every device value consistent casing.
  • ROW_NUMBER() assigns a position to each record within its event ID.
  • QUALIFY retains one record from each event ID group.
Filter invalid events and define skips

Typed values make invalid records visible as missing or impossible values. The final query keeps valid events before deriving a reusable skip flag.

  • Complete silver_listening_events.sql by pasting this query below the deduplicated CTE:
SELECT
    event_id,
    listener_id,
    track_name,
    artist_name,
    genre,
    played_at,
    track_duration_ms,
    ms_played,
    device,
    ms_played < track_duration_ms * 0.30 AS is_skipped
FROM deduplicated
WHERE event_id IS NOT NULL
  AND listener_id IS NOT NULL
  AND track_name IS NOT NULL
  AND artist_name IS NOT NULL
  AND genre IS NOT NULL
  AND played_at IS NOT NULL
  AND track_duration_ms > 0
  AND ms_played BETWEEN 0 AND track_duration_ms

How does the final query restore trust?

  • The WHERE conditions remove events with missing identity or reporting fields.
  • track_duration_ms > 0 removes tracks with impossible durations.
  • ms_played BETWEEN 0 AND track_duration_ms keeps play time within the track duration.
  • is_skipped becomes true when the listener completed less than 30 percent of a track.
  • Save models/silver/silver_listening_events.sql.

✔️ Awesome, I've got everything!

Your Silver model now contains its typing stage. It also contains its deduplication and validation logic.

ⓧ I'd like to double check the full code

The complete model should match this reference.

WITH typed AS (
    SELECT
        TRIM(event_id) AS event_id,
        TRIM(listener_id) AS listener_id,
        NULLIF(TRIM(track_name), '') AS track_name,
        NULLIF(TRIM(artist_name), '') AS artist_name,
        NULLIF(TRIM(genre), '') AS genre,
        TRY_CAST(played_at AS TIMESTAMP) AS played_at,
        TRY_CAST(track_duration_ms AS BIGINT) AS track_duration_ms,
        TRY_CAST(ms_played AS BIGINT) AS ms_played,
        LOWER(TRIM(device)) AS device
    FROM {{ ref('raw_listening_events') }}
),

deduplicated AS (
    SELECT *
    FROM typed
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY event_id
        ORDER BY event_id
    ) = 1
)

SELECT
    event_id,
    listener_id,
    track_name,
    artist_name,
    genre,
    played_at,
    track_duration_ms,
    ms_played,
    device,
    ms_played < track_duration_ms * 0.30 AS is_skipped
FROM deduplicated
WHERE event_id IS NOT NULL
  AND listener_id IS NOT NULL
  AND track_name IS NOT NULL
  AND artist_name IS NOT NULL
  AND genre IS NOT NULL
  AND played_at IS NOT NULL
  AND track_duration_ms > 0
  AND ms_played BETWEEN 0 AND track_duration_ms

What should match?

This reference preserves the Bronze relation through ref(). It produces the validated event columns plus is_skipped.

Before you build the model, do you expect the duplicate and malformed records to survive the Silver filters?

  • Build the Silver model from the terminal by running:
dbt build --select silver_listening_events

What should I see?

dbt reports that silver_listening_events built successfully. This confirms that the SQL compiled into main.silver_listening_events.

Silver model not building?

  • Check that silver_listening_events.sql sits inside models/silver.
  • Check that the closing parentheses for typed and deduplicated match the reference.
  • Still stuck? Help me troubleshoot my Silver dbt model.

That is the first trust repair complete. Your raw Bronze records now feed a typed Silver table.

Add tests and preview Silver

A successful query proves that the model can run. Data tests prove that its trust rules continue to hold.

  • Create models/silver/silver.yml from the Visual Studio Code file sidebar.
  • Add the first set of column tests by pasting this code into silver.yml:
version: 2

models:
  - name: silver_listening_events
    description: One validated and deduplicated row per music listening event.
    columns:
      - name: event_id
        description: Unique identifier for a listening event.
        data_tests:
          - unique
          - not_null
      - name: listener_id
        description: Synthetic listener identifier.
        data_tests:
          - not_null
      - name: artist_name
        description: Clean artist name used for reporting.
        data_tests:
          - not_null
      - name: played_at
        description: Valid listening timestamp.
        data_tests:
          - not_null
      - name: device
        description: Normalized playback device.
        data_tests:
          - accepted_values:
              arguments:
                values: ["mobile", "desktop", "tablet", "speaker"]

What do these tests protect?

  • unique confirms that every retained event ID appears once.
  • not_null protects the fields required for listener and artist analysis.
  • accepted_values limits normalized device values to the four generated device types.
  • Complete the schema tests by pasting this code below the device test:
      - name: is_skipped
        description: True when less than 30 percent of the track was played.
        data_tests:
          - not_null

Why test the skip flag?

The is_skipped test confirms that every retained event receives a classification. Later metrics can calculate skip rates without handling missing flags.

  • Save models/silver/silver.yml.

✔️ Awesome, I've got everything!

Your schema file now documents the Silver model. It also attaches reusable tests to its important columns.

ⓧ I'd like to double check the full code

The complete schema file should match this reference.

version: 2

models:
  - name: silver_listening_events
    description: One validated and deduplicated row per music listening event.
    columns:
      - name: event_id
        description: Unique identifier for a listening event.
        data_tests:
          - unique
          - not_null
      - name: listener_id
        description: Synthetic listener identifier.
        data_tests:
          - not_null
      - name: artist_name
        description: Clean artist name used for reporting.
        data_tests:
          - not_null
      - name: played_at
        description: Valid listening timestamp.
        data_tests:
          - not_null
      - name: device
        description: Normalized playback device.
        data_tests:
          - accepted_values:
              arguments:
                values: ["mobile", "desktop", "tablet", "speaker"]
      - name: is_skipped
        description: True when less than 30 percent of the track was played.
        data_tests:
          - not_null

What should match?

This reference documents six Silver columns. Each data_tests entry enforces a rule used by the final analytics tables.

Before you run the tests, do you expect any duplicate event IDs or unsupported device values to remain?

  • Build the model with its column tests by running:
dbt build --select silver_listening_events

What should I see?

dbt reports passing results for the model and its column tests. The checks confirm unique event IDs plus complete reporting fields.

Seeing a failed column test?

  • Check that every data_tests entry is nested beneath the intended column.
  • Check that the accepted device values match the generated lowercase values exactly.
  • Still stuck? Help me diagnose my Silver column tests.

Your Silver columns now defend their own reporting contract. The next assertion protects the expected event count.

  • Create tests/assert_silver_has_240_rows.sql from the Visual Studio Code file sidebar.
  • Add the row-count assertion by pasting this code into the new file:
SELECT COUNT(*) AS actual_rows
FROM {{ ref('silver_listening_events') }}
HAVING COUNT(*) <> 240

How does this assertion work?

A singular dbt test passes when its query returns no rows. This query returns a row only when the Silver count differs from 240.

  • Save tests/assert_silver_has_240_rows.sql.

✔️ Awesome, I've got everything!

Your singular test now guards the expected Silver event count.

ⓧ I'd like to double check the full code

The complete singular test should match this reference.

SELECT COUNT(*) AS actual_rows
FROM {{ ref('silver_listening_events') }}
HAVING COUNT(*) <> 240

What should match?

This reference links the assertion to silver_listening_events. A count of 240 produces no failing row.

Before you rebuild, do you think the Silver table now contains exactly 240 trustworthy events?

  • Build the Silver model with every dependent test by running:
dbt build --select silver_listening_events

What should I see?

dbt reports passing results for the model and every selected test. The singular assertion confirms that Silver contains exactly 240 rows.

Row-count assertion failing?

  • Check that the deduplicated CTE keeps only the first row for each event ID.
  • Check that the final filter rejects missing artist names and invalid timestamps.
  • Still stuck? Help me trace the unexpected Silver row count.

That is the quality gate in place. Your Silver model now proves its event grain every time it builds.

Before you preview the result, which fields do you expect to look different from their raw Bronze versions?

  • Preview the tested Silver relation by running:
dbt show --select "silver_listening_events"

What should I see?

You will see a terminal preview of the Silver rows. The preview includes typed played_at timestamps plus the Boolean is_skipped field.

The passing row-count assertion proves that the full relation contains 240 events. Bronze remains unchanged with its original 256 source rows.

Silver preview not appearing?

  • Check that the previous build completed successfully before requesting the preview.
  • Check that your terminal remains inside the musicpulse folder.
  • Still stuck? Help me preview my tested Silver model.

Your pipeline now has a tested 240-row Silver foundation. Next up, you will shape these trusted events into dashboard-ready Gold data products.

Create Gold Analytics Marts

Your tested Silver layer now contains 240 trusted listening events. Those event-level rows give every metric a reliable foundation.

A clean event table is still too detailed for repeated dashboard questions. Calculating the same metrics inside every chart would scatter business logic across the final product.

You will use dbt to create reusable Gold marts. Each mart answers a defined group of analytics questions.

In this step, get ready to:
  • Build KPI rows for each reporting grain.
  • Create dashboard-ready trend marts.
  • Publish a tested lineage site.
Build the KPI mart

A KPI mart stores frequently requested business metrics at a predictable grain. The dashboard can retrieve one matching row for the selected genre.

Why Gold marts?

Gold marts package trusted Silver events into stable business-facing tables. This keeps aggregation logic inside dbt models that can be tested before a dashboard reads them.

The first mart supports headline metrics at the overall grain or the genre grain. One filter value selects the correct result.

  • Use macOS search to open Visual Studio Code.
  • Use the folder picker to select the existing musicpulse folder where your terminal is already working.

You should see the existing models/silver folder. This confirms that Visual Studio Code has the correct workspace.

  • Create a gold folder inside the existing models folder.
  • Create models/gold/gold_kpis.sql by pasting this code into a new file:
WITH genre_metrics AS (
    SELECT
        genre,
        COUNT(*) AS total_plays,
        COUNT(DISTINCT listener_id) AS unique_listeners,
        ROUND(SUM(ms_played) / 60000.0, 1) AS listening_minutes,
        ROUND(
            100.0 * SUM(CASE WHEN is_skipped THEN 1 ELSE 0 END) / COUNT(*),
            1
        ) AS skip_rate_pct
    FROM {{ ref('silver_listening_events') }}
    GROUP BY genre
),

overall_metrics AS (
    SELECT
        'All' AS genre,
        COUNT(*) AS total_plays,
        COUNT(DISTINCT listener_id) AS unique_listeners,
        ROUND(SUM(ms_played) / 60000.0, 1) AS listening_minutes,
        ROUND(
            100.0 * SUM(CASE WHEN is_skipped THEN 1 ELSE 0 END) / COUNT(*),
            1
        ) AS skip_rate_pct
    FROM {{ ref('silver_listening_events') }}
)

SELECT * FROM overall_metrics
UNION ALL
SELECT * FROM genre_metrics

What Does This Model Calculate?

  • The genre_metrics CTE calculates one KPI row for each genre.
  • The overall_metrics CTE calculates one summary row with the genre value All.
  • The ref('silver_listening_events') call creates a dependency on the tested Silver model.
  • The final union places both reporting grains in one relation.
  • Save models/gold/gold_kpis.sql.
  • Build the KPI mart by running this command in the terminal from earlier:
dbt build

What Does This Build Prove?

The command evaluates the dbt dependency graph. It materializes gold_kpis from silver_listening_events.

You should see the build finish successfully with gold_kpis included. Your first Gold mart can now answer headline questions for the complete dataset or one genre.

KPI Mart Not Building?

  • Confirm that the file is saved at models/gold/gold_kpis.sql.
  • Check the closing parentheses around both ROUND calculations.
  • Still stuck? Help me troubleshoot the Gold KPI mart.

✔️ Awesome, I've got everything!

Your gold_kpis model is saved in the correct folder. The successful build has materialized it in the local database.

ⓧ I'd like to double check the full code

Compare models/gold/gold_kpis.sql with this complete file.

WITH genre_metrics AS (
    SELECT
        genre,
        COUNT(*) AS total_plays,
        COUNT(DISTINCT listener_id) AS unique_listeners,
        ROUND(SUM(ms_played) / 60000.0, 1) AS listening_minutes,
        ROUND(
            100.0 * SUM(CASE WHEN is_skipped THEN 1 ELSE 0 END) / COUNT(*),
            1
        ) AS skip_rate_pct
    FROM {{ ref('silver_listening_events') }}
    GROUP BY genre
),

overall_metrics AS (
    SELECT
        'All' AS genre,
        COUNT(*) AS total_plays,
        COUNT(DISTINCT listener_id) AS unique_listeners,
        ROUND(SUM(ms_played) / 60000.0, 1) AS listening_minutes,
        ROUND(
            100.0 * SUM(CASE WHEN is_skipped THEN 1 ELSE 0 END) / COUNT(*),
            1
        ) AS skip_rate_pct
    FROM {{ ref('silver_listening_events') }}
)

SELECT * FROM overall_metrics
UNION ALL
SELECT * FROM genre_metrics
Create trend and artist marts

Headline KPIs summarize a selected slice of listening activity. A daily mart reveals how that activity changes over time.

  • Create models/gold/gold_daily_trends.sql by pasting this code into a new file:
WITH genre_daily AS (
    SELECT
        CAST(DATE_TRUNC('day', played_at) AS DATE) AS listen_date,
        genre,
        COUNT(*) AS total_plays,
        COUNT(DISTINCT listener_id) AS unique_listeners
    FROM {{ ref('silver_listening_events') }}
    GROUP BY listen_date, genre
),

overall_daily AS (
    SELECT
        CAST(DATE_TRUNC('day', played_at) AS DATE) AS listen_date,
        'All' AS genre,
        COUNT(*) AS total_plays,
        COUNT(DISTINCT listener_id) AS unique_listeners
    FROM {{ ref('silver_listening_events') }}
    GROUP BY listen_date
)

SELECT * FROM overall_daily
UNION ALL
SELECT * FROM genre_daily

How Does the Daily Grain Work?

  • The DATE_TRUNC expression reduces each timestamp to its calendar day.
  • The genre_daily CTE creates one row for each date and genre combination.
  • The overall_daily CTE creates one All row for each date.
  • The final relation supports the same genre filter as the KPI mart.
  • Save models/gold/gold_daily_trends.sql.
  • Build the daily trend mart by running this command:
dbt build

What Changed in the DAG?

The dependency graph now contains a second Gold model sourced from silver_listening_events. The new relation stores daily play counts plus daily listener counts.

You should see gold_daily_trends finish successfully. Your warehouse now has a dashboard-ready time series.

Daily Trend Mart Not Building?

  • Confirm that both CTEs return their columns in the same order.
  • Check that listen_date appears in both grouping clauses.
  • Still stuck? Help me troubleshoot the daily trend mart.

✔️ Awesome, I've got everything!

Your daily trend mart is saved. The successful build has materialized its overall rows plus genre rows.

ⓧ I'd like to double check the full code

Compare models/gold/gold_daily_trends.sql with this complete file.

WITH genre_daily AS (
    SELECT
        CAST(DATE_TRUNC('day', played_at) AS DATE) AS listen_date,
        genre,
        COUNT(*) AS total_plays,
        COUNT(DISTINCT listener_id) AS unique_listeners
    FROM {{ ref('silver_listening_events') }}
    GROUP BY listen_date, genre
),

overall_daily AS (
    SELECT
        CAST(DATE_TRUNC('day', played_at) AS DATE) AS listen_date,
        'All' AS genre,
        COUNT(*) AS total_plays,
        COUNT(DISTINCT listener_id) AS unique_listeners
    FROM {{ ref('silver_listening_events') }}
    GROUP BY listen_date
)

SELECT * FROM overall_daily
UNION ALL
SELECT * FROM genre_daily

Daily metrics explain when listening happens. Artist metrics reveal which performers attract plays from the trusted event set.

  • Create models/gold/gold_artist_performance.sql by pasting this code into a new file:
SELECT
    artist_name,
    genre,
    COUNT(*) AS total_plays,
    COUNT(DISTINCT listener_id) AS unique_listeners,
    ROUND(AVG(100.0 * ms_played / track_duration_ms), 1) AS avg_completion_pct,
    ROUND(
        100.0 * SUM(CASE WHEN is_skipped THEN 1 ELSE 0 END) / COUNT(*),
        1
    ) AS skip_rate_pct
FROM {{ ref('silver_listening_events') }}
GROUP BY artist_name, genre

What Does Artist Performance Measure?

  • The grouping creates one row for each artist and genre combination.
  • The total_plays metric measures valid event volume.
  • The unique_listeners metric measures audience reach.
  • The completion percentage describes how much of each artist's tracks listeners played.
  • The skip rate reuses the trusted is_skipped field from Silver.
  • Save models/gold/gold_artist_performance.sql.
  • Build the artist performance mart by running this command:
dbt build

What Does This Build Add?

The build materializes gold_artist_performance from the trusted Silver relation. Every artist metric therefore follows the same validation rules.

You should see gold_artist_performance finish successfully. All three dashboard-facing marts now exist in the local DuckDB database.

Artist Mart Not Building?

  • Check the closing parentheses around both percentage calculations.
  • Confirm that the grouping contains artist_name plus genre.
  • Still stuck? Help me troubleshoot the artist performance mart.

✔️ Awesome, I've got everything!

Your artist performance mart is saved. The successful build has materialized its artist-level metrics.

ⓧ I'd like to double check the full code

Compare models/gold/gold_artist_performance.sql with this complete file.

SELECT
    artist_name,
    genre,
    COUNT(*) AS total_plays,
    COUNT(DISTINCT listener_id) AS unique_listeners,
    ROUND(AVG(100.0 * ms_played / track_duration_ms), 1) AS avg_completion_pct,
    ROUND(
        100.0 * SUM(CASE WHEN is_skipped THEN 1 ELSE 0 END) / COUNT(*),
        1
    ) AS skip_rate_pct
FROM {{ ref('silver_listening_events') }}
GROUP BY artist_name, genre
Document and inspect the Gold lineage

Model descriptions explain each mart outside its SQL file. Column tests protect the fields that the dashboard depends on.

  • Create models/gold/gold.yml by pasting this first model definition into a new file:
version: 2

models:
  - name: gold_kpis
    description: Overall and genre-level headline metrics for the dashboard.
    columns:
      - name: genre
        description: Genre filter value, including the overall All row.
        data_tests:
          - unique
          - not_null
      - name: total_plays
        description: Count of valid listening events.
        data_tests:
          - not_null

What Do the KPI Tests Protect?

  • The unique test protects the one-row-per-filter-value grain.
  • The not_null tests protect the genre filter plus its play count.
  • The descriptions appear in the generated documentation site.
  • Save models/gold/gold.yml.
  • Validate the KPI documentation by running this command:
dbt build

What Does This Validation Cover?

dbt parses the new YAML definition during the build. It also runs the tests attached to gold_kpis.

You should see the build pass. The KPI mart now has documented fields plus tested reporting values.

KPI Documentation Not Validating?

  • Check the indentation beneath models.
  • Confirm that the model name is exactly gold_kpis.
  • Still stuck? Help me troubleshoot the KPI documentation.

The daily trend mart has a compound reporting grain. Its documentation protects the date dimension plus the genre filter value.

  • Place your cursor one blank line below the gold_kpis definition.
  • Append the daily trend definition by pasting this code:
  - name: gold_daily_trends
    description: Daily play and listener counts at overall and genre grains.
    columns:
      - name: listen_date
        description: Calendar date of listening activity.
        data_tests:
          - not_null
      - name: genre
        description: Genre filter value, including overall All rows.
        data_tests:
          - not_null

What Do the Daily Tests Protect?

Every trend row needs a date plus a genre filter value. The two not_null tests protect that grain.

  • Save models/gold/gold.yml.
  • Validate the daily trend documentation by running this command:
dbt build

What Does This Build Check?

The build parses both documented Gold definitions. It runs the daily trend tests against the materialized relation.

You should see the build pass. The trend mart now has documented date fields plus documented genre fields.

Daily Documentation Not Validating?

  • Align the gold_daily_trends entry with the existing gold_kpis entry.
  • Confirm that both column definitions sit beneath the daily model's columns key.
  • Still stuck? Help me troubleshoot the daily model documentation.

The artist mart needs one stable row per artist. Its tests protect that reporting grain plus the metric used for rankings.

  • Place your cursor one blank line below the gold_daily_trends definition.
  • Append the artist performance definition by pasting this code:
  - name: gold_artist_performance
    description: Artist-level reach, completion, and skip metrics.
    columns:
      - name: artist_name
        description: Artist represented by the metric row.
        data_tests:
          - unique
          - not_null
      - name: total_plays
        description: Count of valid plays for the artist.
        data_tests:
          - not_null

What Do the Artist Tests Protect?

  • The unique test protects the one-row-per-artist reporting grain.
  • The not_null tests protect the artist label plus its play count.
  • The descriptions expose the artist metrics in the documentation site.
  • Save models/gold/gold.yml.

Before you run the complete build, do you expect dbt to validate only the Gold layer or every connected layer?

  • Build the complete dependency graph by running this command:
dbt build

What Does the Full Build Prove?

dbt processes resources in dependency order. It also runs the tests attached to those resources.

A passing build proves that the unchanged Bronze seed flows through the tested 240-row Silver table. The same flow now reaches all three documented Gold marts.

You should see the full DAG pass. The local database now contains main.gold_kpis, main.gold_daily_trends, plus main.gold_artist_performance.

Full DAG Not Passing?

  • Find the first failed resource in the terminal output.
  • Compare that resource name with its matching SQL file.
  • Check models/gold/gold.yml for indentation problems.
  • Still stuck? Help me troubleshoot the complete dbt build.

✔️ Awesome, I've got everything!

Your three Gold models are documented. The complete DAG now passes its tests.

ⓧ I'd like to double check the full code

Compare models/gold/gold.yml with this complete file.

version: 2

models:
  - name: gold_kpis
    description: Overall and genre-level headline metrics for the dashboard.
    columns:
      - name: genre
        description: Genre filter value, including the overall All row.
        data_tests:
          - unique
          - not_null
      - name: total_plays
        description: Count of valid listening events.
        data_tests:
          - not_null

  - name: gold_daily_trends
    description: Daily play and listener counts at overall and genre grains.
    columns:
      - name: listen_date
        description: Calendar date of listening activity.
        data_tests:
          - not_null
      - name: genre
        description: Genre filter value, including overall All rows.
        data_tests:
          - not_null

  - name: gold_artist_performance
    description: Artist-level reach, completion, and skip metrics.
    columns:
      - name: artist_name
        description: Artist represented by the metric row.
        data_tests:
          - unique
          - not_null
      - name: total_plays
        description: Count of valid plays for the artist.
        data_tests:
          - not_null

Generated documentation combines descriptions with data lineage. The resulting site makes the complete medallion flow reviewable in a browser.

  • Generate the local documentation artifacts by running this command:
dbt docs generate

What Does Documentation Generation Create?

dbt compiles model metadata plus warehouse details into local documentation artifacts. The generated dependency information powers the lineage site.

You should see documentation generation finish successfully. The target folder now contains generated catalog files plus manifest files.

Documentation Generation Failing?

  • Confirm that the complete dbt build passed first.
  • Check models/gold/gold.yml for indentation problems.
  • Still stuck? Help me troubleshoot documentation generation.

The next command starts a local documentation server. It keeps control of the terminal until you stop it.

Before you start the site, which dependency path do you expect the lineage graph to reveal?

  • Start the local documentation site by running this command:
dbt docs serve

What Does the Docs Server Provide?

The command serves the generated documentation as a local website. The site combines model descriptions with the dbt dependency graph.

  • Wait for the local documentation site to open in your browser.
  • Search the site for raw_listening_events.
  • Select the matching seed result.
  • Inspect its lineage graph for every downstream Gold model.

You should see raw_listening_events connect to silver_listening_events. The Silver model should connect to all three Gold models.

The downstream Gold models are gold_kpis, gold_daily_trends, plus gold_artist_performance.

Lineage Site Not Opening?

  • Check the terminal for the local address produced by the running documentation server.
  • Confirm that dbt docs generate completed before the server started.
  • Still stuck? Help me troubleshoot the local lineage site.
  • Return to the terminal running the documentation server.
  • Stop the server by pressing Ctrl+C.

That is a major milestone. Your local warehouse now has three tested Gold marts plus a lineage site that proves where every metric comes from.

Next, you will turn those marts into a dashboard where stakeholders can filter trusted KPIs, trends, plus artist rankings.

Launch Your Portfolio Dashboard

Your Gold layer now answers the core reporting questions with trusted data. A stakeholder still needs a way to explore those answers without reading SQL or touching raw records.

This step turns the three Gold tables into a local dashboard with Streamlit. The app reads your DuckDB database in read-only mode.

In this step, get ready to:
  • Connect Streamlit to the local DuckDB database.
  • Query the three Gold relations for dashboard data.
  • Render a filterable dashboard from trusted metrics.
Connect the dashboard to Gold data

The dashboard begins with a read-only database connection. You will also build the genre options from main.gold_kpis so the filter always matches the available Gold data.

You will add app.py to the existing musicpulse workspace in Visual Studio Code.

  • Press Cmd+Space to open the macOS search bar.
  • Type Visual Studio Code and press Enter.
  • Click File in the menu bar.
  • Click Open Folder.
  • Select the existing musicpulse folder.
  • Click the new-file control in the Explorer sidebar.
  • Enter app.py as the file name.
  • Initialize the dashboard connection and genre selector by adding this code:
import duckdb
import streamlit as st

connection = duckdb.connect(database="musicpulse.duckdb", read_only=True)

genres = ["All"] + [
    row[0]
    for row in connection.execute(
        """
        SELECT genre
        FROM main.gold_kpis
        WHERE genre <> 'All'
        ORDER BY genre
        """
    ).fetchall()
]

st.write("# MusicPulse")
st.write("Trusted listener and artist metrics from a local medallion pipeline.")
selected_genre = st.selectbox("Genre", genres)

How does the dashboard start?

  • The connection opens musicpulse.duckdb in read-only mode. The dashboard can query trusted data without changing it.
  • The genre query reads distinct filter options from main.gold_kpis.
  • The All value gives the dashboard an overall view.
  • The selected_genre variable stores the current filter choice.
  • Save app.py.
  • Switch back to the terminal from earlier.
  • Launch the local dashboard by running this command:
streamlit run app.py

Your browser displays the MusicPulse heading with a Genre selector underneath. You have the dashboard shell running against your Gold data.

Dashboard not opening?

  • Confirm that the terminal prompt still shows the activated .venv environment.
  • Check that app.py is saved inside the musicpulse folder.
  • Still stuck? Help me troubleshoot my local Streamlit dashboard launch.
Query the three Gold relations

Each Gold relation has a dashboard-facing purpose. The KPI table provides headline values while the daily table provides the time series.

  • In app.py, add the KPI query below the selected_genre = st.selectbox("Genre", genres) line:
kpi = connection.execute(
    """
    SELECT total_plays, unique_listeners, listening_minutes, skip_rate_pct
    FROM main.gold_kpis
    WHERE genre = ?
    """,
    [selected_genre],
).fetchone()

How does the KPI query work?

  • The query selects the four values needed for the KPI cards.
  • The ? placeholder receives the value stored in selected_genre.
  • The fetchone() call returns the single KPI row for that filter.
  • Save app.py.
  • Refresh the dashboard in your browser.

The Genre selector still shows All. This confirms that the overall KPI row loaded without interrupting the app.

KPI query interrupting the app?

  • Check that the relation name is exactly main.gold_kpis.
  • Confirm that [selected_genre] appears as the second argument to connection.execute().
  • Still stuck? Help me debug the Gold KPI query in app.py.
  • Add the daily trend query directly below the kpi query:
daily_data = connection.execute(
    """
    SELECT listen_date, total_plays, unique_listeners
    FROM main.gold_daily_trends
    WHERE genre = ?
    ORDER BY listen_date
    """,
    [selected_genre],
).fetchdf()

What does the daily query return?

  • The query returns one row for each listening date in the selected genre.
  • The result includes total_plays for event volume.
  • The result includes unique_listeners for daily audience reach.
  • The fetchdf() call returns a data frame that Streamlit can chart directly.
  • Save app.py.
  • Refresh the dashboard in your browser.

The dashboard reloads with the genre options intact. This confirms that main.gold_daily_trends returned chart-ready rows.

Daily query causing a problem?

  • Check that the selected columns are listen_date, total_plays, and unique_listeners.
  • Confirm that the relation name is exactly main.gold_daily_trends.
  • Still stuck? Help me debug the daily trend query in app.py.

Artist rankings need one extra branch. The overall view includes every artist while an individual genre view filters the rows before sorting them.

  • Add the artist query below the daily_data query:
if selected_genre == "All":
    artist_data = connection.execute(
        """
        SELECT artist_name, total_plays, unique_listeners, avg_completion_pct, skip_rate_pct
        FROM main.gold_artist_performance
        ORDER BY total_plays DESC, artist_name
        """
    ).fetchdf()
else:
    artist_data = connection.execute(
        """
        SELECT artist_name, total_plays, unique_listeners, avg_completion_pct, skip_rate_pct
        FROM main.gold_artist_performance
        WHERE genre = ?
        ORDER BY total_plays DESC, artist_name
        """,
        [selected_genre],
    ).fetchdf()

Why does the artist query branch?

  • The All branch returns every artist from the Gold relation.
  • The other branch filters the artist rows with selected_genre.
  • Both branches sort by total_plays so the strongest performers appear first.
  • Save app.py.
  • Refresh the dashboard in your browser.
  • Select Electronic in the Genre selector.

The selector displays Electronic without showing an app error. This confirms that the filtered artist branch executes successfully.

Genre selection breaking the app?

  • Check that the unfiltered query appears inside the if selected_genre == "All": branch.
  • Check that the filtered query appears inside the else: branch.
  • Still stuck? Help me fix the artist query branches in app.py.
Render and verify the dashboard

The three query results are ready to present. You will turn the single kpi row into headline cards before charting the two data frames.

  • Add the KPI card layout below the artist_data query branches:
column_1, column_2, column_3, column_4 = st.columns(4)
column_1.metric("Total plays", int(kpi[0]))
column_2.metric("Unique listeners", int(kpi[1]))
column_3.metric("Listening minutes", f"{float(kpi[2]):,.1f}")
column_4.metric("Skip rate", f"{float(kpi[3]):.1f}%")

How do the KPI cards map to the data?

  • The four columns keep the headline metrics together in one row.
  • Each metric reads the matching position from the kpi result.
  • Listening minutes use one decimal place for readability.
  • Skip rate includes a percentage sign so its meaning is visible at a glance.
  • Save app.py.
  • Refresh the dashboard in your browser.

You will see four cards labeled Total plays, Unique listeners, Listening minutes, and Skip rate.

KPI cards missing?

  • Confirm that all four metric calls appear below the artist query branches.
  • Check that the indexes run from kpi[0] through kpi[3].
  • Still stuck? Help me fix the four KPI cards in app.py.
  • Add the daily listening chart below the KPI cards:
st.write("## Daily listening trend")
st.line_chart(
    daily_data,
    x="listen_date",
    y=["total_plays", "unique_listeners"],
    y_label="Events and listeners",
)

What does the trend chart compare?

  • The horizontal axis uses listen_date to show activity over time.
  • The chart plots total_plays as the daily event volume.
  • The chart plots unique_listeners as the daily audience size.
  • Save app.py.
  • Refresh the dashboard in your browser.

You will see a Daily listening trend chart with separate lines for plays and listeners.

Daily trend chart missing?

  • Check that daily_data is the first argument to st.line_chart().
  • Confirm that the y-axis list contains total_plays and unique_listeners.
  • Still stuck? Help me debug the daily listening chart.
  • Add the artist chart and close the database connection with this final code:
st.write("## Artist performance")
st.bar_chart(
    artist_data,
    x="artist_name",
    y="total_plays",
    x_label="Artist",
    y_label="Valid plays",
    horizontal=True,
    sort="-total_plays",
    height=400,
)

st.write("Showing Gold-layer metrics for:", selected_genre)
connection.close()

How does the artist chart finish the dashboard?

  • The horizontal bars compare valid plays across artists.
  • The negative sort value places the highest play count first.
  • The final message displays the active genre filter.
  • The close() call releases the database connection after each completed app run.
  • Save app.py.
  • Refresh the dashboard in your browser.

You will see a sorted horizontal Artist performance chart. The active Gold-layer genre appears below it.

Artist chart not rendering?

  • Check that artist_data is the first argument to st.bar_chart().
  • Confirm that the chart uses artist_name for the horizontal categories.
  • Still stuck? Help me debug the artist performance chart.

✔️ Awesome, I've got everything!

Your saved app.py now connects the selector, queries, KPI cards, and charts.

ⓧ I'd like to double check the full code

  • Compare your saved app.py with the complete file below:
import duckdb
import streamlit as st

connection = duckdb.connect(database="musicpulse.duckdb", read_only=True)

genres = ["All"] + [
    row[0]
    for row in connection.execute(
        """
        SELECT genre
        FROM main.gold_kpis
        WHERE genre <> 'All'
        ORDER BY genre
        """
    ).fetchall()
]

st.write("# MusicPulse")
st.write("Trusted listener and artist metrics from a local medallion pipeline.")
selected_genre = st.selectbox("Genre", genres)

kpi = connection.execute(
    """
    SELECT total_plays, unique_listeners, listening_minutes, skip_rate_pct
    FROM main.gold_kpis
    WHERE genre = ?
    """,
    [selected_genre],
).fetchone()

daily_data = connection.execute(
    """
    SELECT listen_date, total_plays, unique_listeners
    FROM main.gold_daily_trends
    WHERE genre = ?
    ORDER BY listen_date
    """,
    [selected_genre],
).fetchdf()

if selected_genre == "All":
    artist_data = connection.execute(
        """
        SELECT artist_name, total_plays, unique_listeners, avg_completion_pct, skip_rate_pct
        FROM main.gold_artist_performance
        ORDER BY total_plays DESC, artist_name
        """
    ).fetchdf()
else:
    artist_data = connection.execute(
        """
        SELECT artist_name, total_plays, unique_listeners, avg_completion_pct, skip_rate_pct
        FROM main.gold_artist_performance
        WHERE genre = ?
        ORDER BY total_plays DESC, artist_name
        """,
        [selected_genre],
    ).fetchdf()

column_1, column_2, column_3, column_4 = st.columns(4)
column_1.metric("Total plays", int(kpi[0]))
column_2.metric("Unique listeners", int(kpi[1]))
column_3.metric("Listening minutes", f"{float(kpi[2]):,.1f}")
column_4.metric("Skip rate", f"{float(kpi[3]):.1f}%")

st.write("## Daily listening trend")
st.line_chart(
    daily_data,
    x="listen_date",
    y=["total_plays", "unique_listeners"],
    y_label="Events and listeners",
)

st.write("## Artist performance")
st.bar_chart(
    artist_data,
    x="artist_name",
    y="total_plays",
    x_label="Artist",
    y_label="Valid plays",
    horizontal=True,
    sort="-total_plays",
    height=400,
)

st.write("Showing Gold-layer metrics for:", selected_genre)
connection.close()

Before you check the finished dashboard, make a prediction about whether Gold still reports all 256 Bronze rows.

  • Select All in the Genre selector.

The Total plays card shows 240. Silver removes 12 duplicate rows and rejects 4 invalid rows.

  • Select an individual genre in the Genre selector.

The four KPI values change for the selected genre. The daily trend and artist ranking change with the same filter.

That is the complete portfolio demo working. Your stakeholder can move from trusted headline metrics to listener trends and artist rankings without touching the source data.

Secret mission

Measure Listener Loyalty

Build a tested listener-loyalty mart that measures how often each listener returns. Add an active-days chart that ranks the top 15 listeners in your MusicPulse dashboard.

Clean Up Your Resources

Clean Up Your Resources

Everything in MusicPulse runs on your Mac with no ongoing cost. Choose the cleanup option that matches what you want to do next.

Resources you used:

  • The local Streamlit process serving your dashboard.
  • The .venv virtual environment containing the pinned Python packages.
  • The generated seeds/raw_listening_events.csv dataset.
  • The local DuckDB database stored in musicpulse.duckdb.
  • The dbt models in models/.
  • The dbt tests in tests/.
  • The dbt build artifacts in target/.
  • The dashboard code in app.py.

Keep everything running

No cleanup is needed while you are actively using the dashboard. Your local files remain ready for the next change.

  • Continue using the running dashboard while you are actively building.
  • Return to the terminal running Streamlit when you finish.
  • Press Ctrl+C to stop the local server.
  • Retain the musicpulse folder for future work.

Pause - I'll come back to this later

Shut down the running process to free memory. Keep every project file ready for another session.

  • Return to the terminal running Streamlit.
  • Press Ctrl+C to stop the local server.
  • Type deactivate in the same terminal to leave the virtual environment.
  • Keep the musicpulse folder on your Mac.

You'll see the (.venv) marker disappear from the terminal prompt. The musicpulse folder remains unchanged.

Delete - I don't want to use this again

Remove every local artifact when you want a clean start.

Deleting the project folder becomes permanent after you empty Trash. This cleanup targets only the musicpulse folder.

  • Return to the terminal running Streamlit.
  • Press Ctrl+C to stop the local server.
  • Type deactivate in the same terminal to leave the virtual environment.
  • Locate the musicpulse project folder in your Mac file browser.
  • Move the entire musicpulse folder to Trash.
  • Open Trash from the Dock.
  • Confirm the musicpulse folder is listed in Trash.

Nice Work!

Nice Work!

You did it! MusicPulse now turns flawed listening events into trusted metrics through a local medallion analytics pipeline.

You've learned how to:

  • Expose the reporting risk in Bronze through 12 duplicate rows plus 4 invalid field checks. Repair that risk with a tested Silver layer containing 240 typed events.
  • Publish Gold data products for KPIs. Add daily trends plus artist comparisons. Prove each transformation with dbt tests, documentation, and lineage.
  • Deliver a filterable Streamlit dashboard backed only by trusted DuckDB Gold tables. Use the genre selector to update the KPI cards. Compare daily listening patterns. Rank artist performance.
  • Complete the Secret Mission by building a tested listener-loyalty mart. Rank the top 15 listeners by active listening days. Use listening minutes as the tie-breaker.

Ready to quiz yourself?