Containerize a KEV Analytics Pipeline

Build a tested CISA vulnerability pipeline with dbt, DuckDB, and Docker.

Introduction

30 Second Summary

Security teams face a constant stream of newly exploited vulnerabilities. A flat catalog makes it difficult to see which vendors need attention first.

In this project, you will turn the live CISA Known Exploited Vulnerabilities Catalog into a tested vendor risk summary using Docker, dbt, and DuckDB. You will export the result to Power BI Desktop with inspectable lineage from the source feed to the final dataset.

What You'll Build

Your finished report ranks vendors by known exploited vulnerabilities from a tested dataset whose lineage you can inspect from source to export.

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

  • A reproducible workflow that downloads the live feed inside Docker without a local Python installation on Windows.
  • A data quality proof where a uniqueness test exposes duplicated CVEs from an expanded CWE list. You restore the promised one-row-per-CVE grain. Every test passes.
  • A Power BI handoff built from an exported vendor summary. Its bar chart ranks vendors by vulnerability count.
  • Secret Mission: Add a custom SQL test that catches any remediation due date earlier than its CISA date-added value.

Are there any prerequisites?

You need a Windows computer with Docker Desktop installed and running.

The walkthrough covers setup for Visual Studio Code and Power BI Desktop.

Before We Start

Before the hands-on work begins, lock in what this pipeline produces and why it matters. Cybersecurity or risk analysts can use its vendor summary to prioritize remediation based on known exploited vulnerability counts.

Prepare the Containerized Workspace

The vendor summary depends on a consistent analytics environment. Local Python or package differences could change how the pipeline behaves.

A Docker container gives the project a reproducible runtime. It keeps dbt Core with DuckDB away from your local Windows installation.

Visual Studio Code holds the project files. Power BI Desktop will be ready for the report you build later.

In this step, get ready to:
  • Prepare the Windows project folder in Visual Studio Code.
  • Confirm Power BI Desktop is available.
  • Prove the pinned analytics stack runs inside Docker.
Prepare Visual Studio Code and the project folder

The Windows User setup makes Visual Studio Code available from Windows PowerShell. This lets one command open the correct folder in your editor.

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

✔️ Visual Studio Code appears

Your editor is already installed. You can continue with the project folder.

ⓧ Visual Studio Code is missing

The Windows User setup is recommended for most Windows users. It also makes the command-line launcher available to new PowerShell windows.

  • Open the official Visual Studio Code Windows setup guide in your browser.
  • Download the Windows User setup.
  • Run the downloaded installer.
  • Complete the installer with its default setup choices.
  • Press the Windows key after the installer closes.
  • Type Visual Studio Code into the search field.

Visual Studio Code now appears as an installed app. Your editor is ready for the project files.

An absolute folder path keeps every command tied to the same location. PowerShell can create that path even when the parent folder is missing.

  • Press the Windows key to return to Windows search.
  • Type Windows PowerShell into the search field.
  • Press Enter to open PowerShell.
  • Create C:\Projects\kev-dbt-pipeline by running these commands:
New-Item -ItemType Directory -Force -Path C:\Projects\kev-dbt-pipeline
Set-Location C:\Projects\kev-dbt-pipeline
Get-Location

What Do These Commands Do?

  • The New-Item command creates the complete folder path. The -Force option safely reuses the folder when it already exists.
  • The Set-Location command moves PowerShell into the project folder.
  • The Get-Location command prints the active folder for verification.

PowerShell prints C:\Projects\kev-dbt-pipeline as its current path. Every project command now runs from the intended folder.

Project Folder Not Created?

Check that the path begins with C:\Projects. Confirm that every backslash matches the command.

If Windows blocks the folder creation, close PowerShell before opening it with administrator permissions. Help me troubleshoot this PowerShell folder command.

  • Open the project folder in Visual Studio Code by running this command:
code .

What Does This Command Do?

The code launcher opens Visual Studio Code. The period tells it to use the current PowerShell folder.

Visual Studio Code opens with kev-dbt-pipeline in the Explorer sidebar. That folder is now the shared home for your Windows files and container workspace.

Visual Studio Code Did Not Open?

Close the current PowerShell window after installing Visual Studio Code. Open a fresh PowerShell window so Windows can find the command-line launcher.

Return to C:\Projects\kev-dbt-pipeline before retrying the command. Help me fix the Visual Studio Code launcher.

Confirm Power BI Desktop is available

The final CSV needs a desktop reporting tool for its visual handoff. Power BI Desktop provides that report canvas without requiring a cloud account.

  • Press the Windows key to open Windows search.
  • Type Power BI Desktop into the search field.

✔️ Power BI Desktop appears

  • Select Power BI Desktop from the search results.

Power BI Desktop reaches its start screen. The reporting tool is ready for the exported vendor summary.

ⓧ Power BI Desktop is missing

Microsoft supports the latest Power BI Desktop release from the Microsoft Store. The application is free for this local workflow.

  • Open the official Power BI Desktop installation guide in your browser.
  • Choose the Microsoft Store installation option.
  • Install the latest supported Power BI Desktop release.
  • Press the Windows key after the installation finishes.
  • Type Power BI Desktop into the search field.
  • Press Enter to launch the application once.

Power BI Desktop reaches its start screen. The application is installed for the report step later in the project.

Create the container files and build the image

The dependency file pins package versions. The Dockerfile turns those pins into a reusable Docker image.

The Docker Compose file mounts your Windows project into the container. The ignore file keeps generated artifacts outside source control.

  • Click the New File icon in the Visual Studio Code Explorer toolbar.
  • Enter requirements.txt as the file name.

The new requirements.txt file appears inside kev-dbt-pipeline in the Explorer sidebar.

  • Pin the analytics dependencies in requirements.txt by pasting this code:
dbt-core==1.12.5
dbt-duckdb==1.11.0
duckdb==1.5.5

What Do These Dependencies Provide?

  • The dbt-core==1.12.5 pin provides the model runner and testing framework.
  • The dbt-duckdb==1.11.0 pin connects dbt Core to DuckDB.
  • The duckdb==1.5.5 pin fixes the local analytics engine version.
  • Save requirements.txt.
  • Confirm the file shows three pinned dependency lines.

Dependency Pins Look Different?

Check that each package uses two equals signs before its version. Remove any spaces around the equals signs.

Confirm the file name ends with .txt. Help me check these dependency pins.

  • Click the New File icon in the Visual Studio Code Explorer toolbar.
  • Enter Dockerfile as the file name.

The extensionless Dockerfile now appears beside requirements.txt.

  • Define the pinned runtime in Dockerfile by pasting this code:
FROM python:3.12.14-slim-trixie

WORKDIR /workspace

COPY requirements.txt .
RUN python -m pip install --no-cache-dir -r requirements.txt

CMD ["dbt", "--version"]

What Does This Dockerfile Do?

  • The FROM instruction starts with the Python 3.12.14 slim image.
  • The WORKDIR instruction makes /workspace the container's active folder.
  • The COPY instruction places the dependency file inside the image.
  • The RUN instruction installs every pinned package without retaining the package download cache.
  • The CMD instruction makes the dbt version check the image's default command.
  • Save Dockerfile.
  • Confirm Dockerfile has no file extension in the Explorer sidebar.

Dockerfile Has a File Extension?

Rename the file to exactly Dockerfile. Remove any hidden .txt suffix.

Keep the capital D at the start of the file name. Help me correct my Dockerfile name or contents.

YAML spacing is easy to misread. The nested indentation defines which settings belong to the pipeline service.

  • Click the New File icon in the Visual Studio Code Explorer toolbar.
  • Enter compose.yaml as the file name.

The new compose.yaml file appears beside Dockerfile.

  • Define the pipeline service in compose.yaml by pasting this configuration:
services:
  pipeline:
    build:
      context: .
    working_dir: /workspace
    volumes:
      - .:/workspace
    ports:
      - "8080:8080"

What Does This Compose File Do?

  • The pipeline service builds the Dockerfile from the current folder.
  • The working_dir setting runs container commands from /workspace.
  • The volume maps the Windows project folder to /workspace inside the container.
  • The port mapping reserves 8080 for the documentation site used later.
  • Save compose.yaml.
  • Confirm the file shows one pipeline service with aligned nested settings.

Compose File Showing a Spacing Problem?

Use spaces for every indentation level. Keep pipeline nested below services.

Check that the volume line begins with a hyphen below volumes. Help me fix the indentation in compose.yaml.

  • Click the New File icon in the Visual Studio Code Explorer toolbar.
  • Enter .gitignore as the file name.

The .gitignore file appears with the other project files. Its leading period is part of the file name.

  • Exclude generated data and dbt artifacts in .gitignore by pasting these entries:
target/
logs/
dbt_packages/
data/raw/
data/output/
data/*.duckdb

What Does This Ignore File Protect?

  • The first three entries exclude generated dbt build artifacts and downloaded packages.
  • The data entries exclude the downloaded feed and exported files.
  • The final pattern excludes persistent DuckDB database files inside data.
  • Save .gitignore.
  • Confirm the Explorer sidebar lists all four project files.

Ignore File Missing From the Explorer?

Confirm the file name begins with a period. Check that Visual Studio Code did not append .txt.

Make sure .gitignore sits beside compose.yaml. Help me check the four workspace files.

✔️ Awesome, I've got everything!

Great. Double-check that all four files are saved inside C:\Projects\kev-dbt-pipeline.

ⓧ I'd like to double check the full code

dbt-core==1.12.5
dbt-duckdb==1.11.0
duckdb==1.5.5
FROM python:3.12.14-slim-trixie

WORKDIR /workspace

COPY requirements.txt .
RUN python -m pip install --no-cache-dir -r requirements.txt

CMD ["dbt", "--version"]
services:
  pipeline:
    build:
      context: .
    working_dir: /workspace
    volumes:
      - .:/workspace
    ports:
      - "8080:8080"
target/
logs/
dbt_packages/
data/raw/
data/output/
data/*.duckdb

Docker Desktop is already running from earlier. A client and server check confirms that PowerShell can reach its Linux container engine.

  • Switch back to the PowerShell window in C:\Projects\kev-dbt-pipeline.
  • Check the Docker client and server by running this command:
docker version

What Does This Command Check?

The command asks Docker Desktop for version details from both the local client and the running server. A response from both components confirms that containers can start.

The output includes version details for both Docker components. PowerShell can communicate with the running Docker Desktop engine.

Docker Server Details Missing?

Return to the running Docker Desktop window. Wait until its engine finishes starting before retrying the command.

Confirm Docker Desktop is configured to run Linux containers. Help me troubleshoot this Docker connection.

The first image build can take a few minutes while Docker downloads the Python base image and installs the pinned packages. A stream of build activity means the process is still working.

  • Build the pipeline image by running this command:
docker compose build pipeline

What Does This Build Command Do?

Docker Compose reads compose.yaml and finds the pipeline service. Docker then follows the Dockerfile to install the pinned Python packages into an image.

The terminal shows the image build completing. The reusable pipeline environment now exists on your computer.

Pipeline Image Not Building?

Confirm PowerShell is still inside C:\Projects\kev-dbt-pipeline. Check that compose.yaml and Dockerfile are saved there.

A package download failure can come from an interrupted internet connection. Retry the build after the connection is stable. Help me diagnose this pipeline image build.

Before you run the final check, predict whether PowerShell will use a local Windows installation or the packages inside the new container.

  • Verify the pinned dbt stack by running this command:
docker compose run --rm pipeline dbt --version

What Does This Verification Prove?

  • The run command starts a one-off container from the built pipeline image.
  • The --rm option removes that temporary container after the version check.
  • The final arguments ask the containerized dbt installation to report its version and installed adapter.

You see dbt Core at 1.12.5 and the DuckDB adapter at 1.11.0. The dependency file also pins DuckDB to 1.5.5 inside the Python 3.12.14 image.

That is the environment problem solved. Your analytics stack now runs inside a reproducible container without a local Windows Python installation.

Version Check Not Completing?

Confirm the image build completed before running the version command. Check that the service name remains pipeline in compose.yaml.

If the reported versions differ, compare requirements.txt with the full-code tab before rebuilding the image. Help me fix this container version check.

Your containerized workspace is ready. Next, you will download the live CISA Known Exploited Vulnerabilities Catalog and expose a model-grain mistake with a dbt test.

Build a Naive Staging Model

Your pinned Docker environment is ready to run the pipeline without a local Python installation.

The CISA Known Exploited Vulnerabilities Catalog arrives as nested JSON. A dbt staging model must preserve its promised grain before analysts can trust downstream counts.

Repeated lists can quietly alter that grain. You will build the tempting first transformation so an automated test can evaluate its one-row-per-CVE promise.

In this step, get ready to:
  • Download the live CISA catalog through the container.
  • Configure the dbt project with a persistent DuckDB database.
  • Test whether expanding the CWE list preserves one row per CVE.
Download the live CISA catalog

The extraction script runs inside the existing container. It saves the public feed to a bind-mounted folder so later container runs can read the same local file.

  • Select the kev-dbt-pipeline folder in the Explorer view in Visual Studio Code.
  • Use the folder creation control in the Explorer toolbar.
  • Enter scripts as the folder name.
  • Select the new scripts folder.
  • Use the file creation control in the Explorer toolbar.
  • Enter extract.py as the file name.
  • Build the extractor in scripts/extract.py by pasting this code:
from pathlib import Path
from urllib.request import urlopen

SOURCE_URL = "https://www.cisa.gov/sites/default/files/feeds/known_exploited_vulnerabilities.json"
TARGET_PATH = Path("data/raw/kev.json")
OUTPUT_DIRECTORY = Path("data/output")


def main():
    TARGET_PATH.parent.mkdir(parents=True, exist_ok=True)
    OUTPUT_DIRECTORY.mkdir(parents=True, exist_ok=True)

    with urlopen(SOURCE_URL, timeout=30) as response:
        payload = response.read()

    TARGET_PATH.write_bytes(payload)
    print(f"Wrote {len(payload):,} bytes to {TARGET_PATH}")


if __name__ == "__main__":
    main()

What Does This Script Do?

  • The constants hold the official feed URL plus the two local output paths.
  • The directory calls create data/raw plus data/output when they are missing.
  • The network request reads the catalog as bytes inside the container.
  • The final write persists those bytes at data/raw/kev.json.
  • Save scripts/extract.py.

The saved extractor now appears inside the scripts folder.

Extractor File in the Wrong Place?

Confirm that extract.py sits inside scripts. The resulting path must be scripts/extract.py.

Check that the file does not have a hidden text-file extension. Help me verify the extractor path in Visual Studio Code.

✔️ Awesome, I've got everything!

Great. Double-check that you saved scripts/extract.py.

ⓧ I'd like to double check the full code

from pathlib import Path
from urllib.request import urlopen

SOURCE_URL = "https://www.cisa.gov/sites/default/files/feeds/known_exploited_vulnerabilities.json"
TARGET_PATH = Path("data/raw/kev.json")
OUTPUT_DIRECTORY = Path("data/output")


def main():
    TARGET_PATH.parent.mkdir(parents=True, exist_ok=True)
    OUTPUT_DIRECTORY.mkdir(parents=True, exist_ok=True)

    with urlopen(SOURCE_URL, timeout=30) as response:
        payload = response.read()

    TARGET_PATH.write_bytes(payload)
    print(f"Wrote {len(payload):,} bytes to {TARGET_PATH}")


if __name__ == "__main__":
    main()
  • Switch back to the PowerShell terminal from earlier.
  • Download the catalog through the pipeline container by running this command:
docker compose run --rm pipeline python scripts/extract.py

What Does This Command Do?

  • Docker Compose starts a one-off container from the existing pipeline service.
  • The container runs the extractor from the bind-mounted project folder.
  • The temporary container is removed after the script finishes.

You will see a line beginning with Wrote. It includes the downloaded byte count plus the path data/raw/kev.json.

  • Return to the Visual Studio Code Explorer.
  • Expand the new data folder.

You will see raw/kev.json plus the empty output folder. The live catalog is now available to every later container run.

Catalog Download Not Completing?

Confirm that Docker Desktop is still running. Check that PowerShell is still using the kev-dbt-pipeline folder from earlier.

A network or certificate-related failure means the container could not retrieve the public feed. Help me troubleshoot the containerized CISA download.

Configure the dbt project and naive model

The dbt project file controls model discovery plus materialization. The profile connects those models to a persistent DuckDB file inside the bind-mounted workspace.

How the Configuration Fits Together

The source definition points dbt at the downloaded catalog. The schema file describes the model contract that automated tests evaluate.

The staging SQL then transforms the nested vulnerability records. Together these files turn a raw download into a testable model.

  • Select the kev-dbt-pipeline folder in the Explorer view.
  • Use the file creation control in the Explorer toolbar.
  • Enter dbt_project.yml as the file name.
  • Configure the project in dbt_project.yml by pasting this code:
name: kev_pipeline
version: '1.0.0'
config-version: 2
profile: kev_pipeline

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

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

What Does This Project Configuration Do?

  • The project name matches the profile name that dbt loads.
  • The path settings tell dbt where to find models plus tests.
  • Staging models become views.
  • Mart models become persistent tables.
  • Save dbt_project.yml.

The project configuration now appears beside compose.yaml in the top-level project folder.

Project Configuration Not Recognized?

Confirm that the file is named dbt_project.yml. Check that it sits beside compose.yaml.

Preserve the spaces shown under models because YAML indentation defines nesting. Help me check this dbt project file.

✔️ Awesome, I've got everything!

Great. Double-check that you saved dbt_project.yml.

ⓧ I'd like to double check the full code

name: kev_pipeline
version: '1.0.0'
config-version: 2
profile: kev_pipeline

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

models:
  kev_pipeline:
    staging:
      +materialized: view
    marts:
      +materialized: table
  • Select the kev-dbt-pipeline folder in the Explorer view.
  • Use the file creation control in the Explorer toolbar.
  • Enter profiles.yml as the file name.
  • Configure the database connection in profiles.yml by pasting this code:
kev_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: /workspace/data/kev.duckdb
      schema: analytics
      threads: 4

What Does This Profile Configure?

  • The dev target uses the DuckDB adapter.
  • The database persists at /workspace/data/kev.duckdb.
  • The analytics schema holds the transformed models.
  • The profile allows four dbt tasks to run concurrently.
  • Save profiles.yml.

The profile now appears beside dbt_project.yml in the top-level project folder.

Profile File Not Connecting Later?

Confirm that kev_pipeline matches the profile value in dbt_project.yml exactly. Check the indentation beneath outputs.

Keep the database path inside /workspace/data so the bind mount persists it on Windows. Help me compare the project and profile names.

✔️ Awesome, I've got everything!

Great. Double-check that you saved profiles.yml.

ⓧ I'd like to double check the full code

kev_pipeline:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: /workspace/data/kev.duckdb
      schema: analytics
      threads: 4
  • Select the kev-dbt-pipeline folder in the Explorer view.
  • Use the folder creation control in the Explorer toolbar.
  • Enter models as the folder name.
  • Select the new models folder.
  • Use the file creation control in the Explorer toolbar.
  • Enter sources.yml as the file name.
  • Define the catalog source in models/sources.yml by pasting this code:
version: 2

sources:
  - name: cisa
    description: CISA Known Exploited Vulnerabilities catalog downloaded as JSON.
    tables:
      - name: kev_catalog
        description: The catalog envelope containing the vulnerabilities list.
        config:
          external_location: "read_json_auto('/workspace/data/raw/kev.json')"

What Does This Source Definition Do?

  • The source is named cisa inside dbt.
  • The table name kev_catalog represents the catalog envelope.
  • The external location tells DuckDB to read the downloaded JSON file directly.
  • Save models/sources.yml.

The source definition now appears inside the models folder.

Source Definition in the Wrong Folder?

Confirm that the full path is models/sources.yml. Check that external_location remains nested beneath config.

Keep the quoted path exactly as shown so DuckDB reads the file inside the container. Help me check this source definition.

✔️ Awesome, I've got everything!

Great. Double-check that you saved models/sources.yml.

ⓧ I'd like to double check the full code

version: 2

sources:
  - name: cisa
    description: CISA Known Exploited Vulnerabilities catalog downloaded as JSON.
    tables:
      - name: kev_catalog
        description: The catalog envelope containing the vulnerabilities list.
        config:
          external_location: "read_json_auto('/workspace/data/raw/kev.json')"
  • Select the models folder in the Explorer view.
  • Use the file creation control in the Explorer toolbar.
  • Enter schema.yml as the file name.
  • Add the staging model contract to models/schema.yml by pasting this code:
version: 2

models:
  - name: stg_kev
    description: One typed row per known exploited vulnerability in the CISA catalog.
    columns:
      - name: cve_id
        description: The CVE identifier and grain of the staging model.
        data_tests:
          - unique
          - not_null
      - name: vendor_project
        description: The vendor or project associated with the vulnerability.
        data_tests:
          - not_null
      - name: known_ransomware_campaign_use
        description: Whether CISA confirms use in a ransomware campaign.
        data_tests:
          - accepted_values:
              arguments:
                values: ['Known', 'Unknown']
      - name: forensic_triage
        description: Whether forensic triage is required under CISA guidance.
        data_tests:
          - accepted_values:
              arguments:
                values: ['Yes', 'No']

What Does the Staging Contract Check?

  • The unique test expresses the one-row-per-CVE grain.
  • The null tests require identifiers plus vendor values on every record.
  • The accepted-value tests protect the two documented CISA categories.
  • Save models/schema.yml.

The schema file now holds a complete contract for stg_kev.

Tests Nested Under the Wrong Column?

Align each data_tests key beneath its column description. Keep each test indented beneath data_tests.

Check the nested arguments block for each accepted-value test. Help me check the staging test indentation.

  • Place the cursor after the final line in models/schema.yml.
  • Press Enter twice to leave one blank line.
  • Add the downstream model contracts by pasting this code:
  - name: mart_kev_vendor_summary
    description: One row per vendor or project with counts for analyst prioritization.
    columns:
      - name: vendor_project
        data_tests:
          - unique
          - not_null
      - name: vulnerability_count
        data_tests:
          - not_null

  - name: kev_vendor_summary_export
    description: CSV-backed export of the vendor summary for Power BI Desktop.

Why Define Later Models Now?

The same schema file records the contracts for the vendor mart plus its CSV export. dbt applies those entries when the matching models are added.

Keeping the contracts together gives the project one documentation surface for staging plus downstream analytics.

  • Save models/schema.yml.

The schema file now contains the staging contract plus the planned mart and export descriptions.

Downstream Entries Appearing Inside stg_kev?

Align both downstream model names with the stg_kev entry. They must remain separate items beneath models.

Confirm that one blank line separates the staging contract from the mart entry. Help me check the full schema structure.

✔️ Awesome, I've got everything!

Great. Double-check that you saved the complete models/schema.yml file.

ⓧ I'd like to double check the full code

version: 2

models:
  - name: stg_kev
    description: One typed row per known exploited vulnerability in the CISA catalog.
    columns:
      - name: cve_id
        description: The CVE identifier and grain of the staging model.
        data_tests:
          - unique
          - not_null
      - name: vendor_project
        description: The vendor or project associated with the vulnerability.
        data_tests:
          - not_null
      - name: known_ransomware_campaign_use
        description: Whether CISA confirms use in a ransomware campaign.
        data_tests:
          - accepted_values:
              arguments:
                values: ['Known', 'Unknown']
      - name: forensic_triage
        description: Whether forensic triage is required under CISA guidance.
        data_tests:
          - accepted_values:
              arguments:
                values: ['Yes', 'No']

  - name: mart_kev_vendor_summary
    description: One row per vendor or project with counts for analyst prioritization.
    columns:
      - name: vendor_project
        data_tests:
          - unique
          - not_null
      - name: vulnerability_count
        data_tests:
          - not_null

  - name: kev_vendor_summary_export
    description: CSV-backed export of the vendor summary for Power BI Desktop.
  • Select the models folder in the Explorer view.
  • Use the folder creation control in the Explorer toolbar.
  • Enter staging as the folder name.
  • Select the new staging folder.
  • Use the file creation control in the Explorer toolbar.
  • Enter stg_kev.sql as the file name.
  • Start the staging query in models/staging/stg_kev.sql by pasting this first section:
with catalog as (
    select *
    from {{ source('cisa', 'kev_catalog') }}
),

vulnerabilities as (
    select unnest(vulnerabilities, recursive := true)
    from catalog
),

expanded_cwes as (
    select
        *,
        unnest(cwes) as cwe
    from vulnerabilities
)

What Do These Transformations Do?

  • The first transformation reads the catalog through the named dbt source.
  • The second transformation expands the top-level vulnerability list into records.
  • The third transformation expands each record's list of CWE values into individual rows.
  • Place the cursor directly below the closing parenthesis in models/staging/stg_kev.sql.
  • Complete the staging query by pasting this section:
select
    "cveID" as cve_id,
    "vendorProject" as vendor_project,
    product,
    "vulnerabilityName" as vulnerability_name,
    cast("dateAdded" as date) as date_added,
    "shortDescription" as short_description,
    "requiredAction" as required_action,
    cast("dueDate" as date) as due_date,
    "knownRansomwareCampaignUse" as known_ransomware_campaign_use,
    "forensicTriage" as forensic_triage,
    cwe,
    notes
from expanded_cwes

What Does the Final Selection Produce?

  • The aliases convert source field names into analytics-friendly column names.
  • The date casts turn the two source strings into date values.
  • The selected cwe field carries one expanded list item on each output row.
  • Save models/staging/stg_kev.sql.

The complete staging query now appears inside models/staging.

Staging Query Looks Incomplete?

Confirm that the first section ends with the expanded_cwes transformation. The final selection must follow directly beneath it.

Check every quoted CISA field name for matching capitalization. Help me compare the full staging query.

✔️ Awesome, I've got everything!

Great. Double-check that you saved the complete models/staging/stg_kev.sql file.

ⓧ I'd like to double check the full code

with catalog as (
    select *
    from {{ source('cisa', 'kev_catalog') }}
),

vulnerabilities as (
    select unnest(vulnerabilities, recursive := true)
    from catalog
),

expanded_cwes as (
    select
        *,
        unnest(cwes) as cwe
    from vulnerabilities
)

select
    "cveID" as cve_id,
    "vendorProject" as vendor_project,
    product,
    "vulnerabilityName" as vulnerability_name,
    cast("dateAdded" as date) as date_added,
    "shortDescription" as short_description,
    "requiredAction" as required_action,
    cast("dueDate" as date) as due_date,
    "knownRansomwareCampaignUse" as known_ransomware_campaign_use,
    "forensicTriage" as forensic_triage,
    cwe,
    notes
from expanded_cwes
Run the grain test

The schema promises that cve_id identifies one staging row. The build now checks that promise against the live catalog.

Before you run the build, do you expect every cve_id value to remain unique after the nested CWE list is expanded?

  • Switch back to the PowerShell terminal from earlier.
  • Build the staging model plus its selected tests by running this command:
docker compose run --rm pipeline dbt build --select stg_kev

What Does This Build Check?

  • dbt resolves the CISA source before building stg_kev as a view.
  • The selected tests evaluate nullability plus accepted categories.
  • The uniqueness test compares the resulting cve_id values with the model's declared grain.

The staging model builds successfully. The unique test for cve_id reports an error.

This Failure Is the Intended Result

Some vulnerabilities contain multiple CWE values. Expanding that list creates multiple staging rows with the same CVE identifier.

The SQL is valid while the model contract is broken. The uniqueness test has exposed the grain change before those duplicates can distort vendor totals.

That failing test did useful work. Next, you will preserve each CWE list as one field so the staging model can keep one row per CVE.

Restore the Correct Grain

The failed uniqueness test in your dbt staging model exposed repeated CVE rows. Expanding the CWE list changed the promised one-row-per-CVE contract.

Model grain defines what one row represents. DuckDB's array_to_string(cwes, ', ') keeps every CWE value inside its original CVE row.

In this step, get ready to:
  • Replace the repeated CWE expansion with one joined field.
  • Rerun the staging model tests against the corrected grain.
  • Preview the typed records in the corrected staging model.
Preserve one row per CVE

The current expanded_cwes CTE creates a separate row for every CWE value. Removing that second expansion preserves each vulnerability row created by unnest(vulnerabilities, recursive := true).

  • In the VS Code Explorer sidebar, return to models/staging/stg_kev.sql.
  • Select all the code in the editor.
  • Replace the selected code with the corrected staging model by pasting this code:
with catalog as (
    select *
    from {{ source('cisa', 'kev_catalog') }}
),

vulnerabilities as (
    select unnest(vulnerabilities, recursive := true)
    from catalog
)

select
    "cveID" as cve_id,
    "vendorProject" as vendor_project,
    product,
    "vulnerabilityName" as vulnerability_name,
    cast("dateAdded" as date) as date_added,
    "shortDescription" as short_description,
    "requiredAction" as required_action,
    cast("dueDate" as date) as due_date,
    "knownRansomwareCampaignUse" as known_ransomware_campaign_use,
    "forensicTriage" as forensic_triage,
    array_to_string(cwes, ', ') as cwes,
    notes
from vulnerabilities

How Does This Restore the Grain?

  • The catalog CTE reads the JSON catalog through the existing dbt source.
  • The vulnerabilities CTE expands the catalog into individual vulnerability rows.
  • array_to_string(cwes, ', ') converts the CWE list into one descriptive string within each row.
  • The date casts keep date_added and due_date ready for date comparisons.
  • Save models/staging/stg_kev.sql.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

with catalog as (
    select *
    from {{ source('cisa', 'kev_catalog') }}
),

vulnerabilities as (
    select unnest(vulnerabilities, recursive := true)
    from catalog
)

select
    "cveID" as cve_id,
    "vendorProject" as vendor_project,
    product,
    "vulnerabilityName" as vulnerability_name,
    cast("dateAdded" as date) as date_added,
    "shortDescription" as short_description,
    "requiredAction" as required_action,
    cast("dueDate" as date) as due_date,
    "knownRansomwareCampaignUse" as known_ransomware_campaign_use,
    "forensicTriage" as forensic_triage,
    array_to_string(cwes, ', ') as cwes,
    notes
from vulnerabilities
Prove the corrected model contract

The previous SQL completed its model build before the uniqueness test failed. Running the same selection again checks the corrected row shape against every declared staging test.

Before you run the build, do you expect the uniqueness test to find any repeated cve_id values?

  • Test the corrected staging model in PowerShell by running this command:
docker compose run --rm pipeline dbt build --select stg_kev

What Does This Command Check?

  • docker compose run starts a one-off container for the pipeline service.
  • --rm removes that container after the command finishes.
  • dbt build --select stg_kev rebuilds the selected staging model before running its declared tests.

Your terminal reports a successful model build. The unique, not_null, and accepted_values tests all pass.

That is the contract restored. Your staging view now keeps one CVE per row while retaining its CWE information.

Is the uniqueness test still failing?

Confirm that the final line reads from vulnerabilities. Remove the old expanded_cwes CTE if it still appears in the file.

Check that the selected field is array_to_string(cwes, ', ') as cwes. Help me diagnose why the corrected stg_kev model still has duplicate CVE IDs.

Preview the corrected records

Passing tests confirms that the model satisfies its declared constraints. A row preview lets you inspect the typed dates and joined CWE values directly.

Before you preview the model, what shape do you expect each CVE record to have?

  • Preview five corrected staging records in PowerShell by running this command:
docker compose run --rm pipeline dbt show --select stg_kev --limit 5

What Does This Preview Show?

  • dbt show compiles the selected model before displaying its result.
  • --select stg_kev targets the corrected staging model.
  • --limit 5 keeps the terminal preview to five records.

You will see up to five staging rows with cve_id, vendor_project, typed date fields, and the joined cwes field.

Each displayed cve_id represents one vulnerability. Records with multiple CWE values keep those values together in one field.

Can't see the corrected columns?

Confirm that models/staging/stg_kev.sql is saved. An unsaved editor tab leaves the container reading the previous file contents.

Check the command still selects stg_kev. Help me troubleshoot the corrected dbt staging preview.

Your staging layer now has a tested one-row-per-CVE contract. Next, you will aggregate those trusted records into a vendor risk summary.

Build and Export the Vendor Risk Mart

Your corrected staging layer now preserves one typed row per CVE. Analysts still need a view that ranks vendors at a decision-friendly business grain.

You will use dbt to aggregate those records into a vendor-level mart in DuckDB. You will export the tested result as a CSV for the later Power BI handoff.

In this step, get ready to:
  • Aggregate the corrected staging records to one row per vendor or project.
  • Export the tested vendor summary as a CSV.
  • Verify the output fields and descending sort order.
Build the vendor-level mart

A model's grain defines what one row represents. This mart changes the grain from one CVE to one vendor or project.

The ref('stg_kev') dependency keeps the mart connected to your tested staging model. The aggregation turns individual vulnerabilities into counts and date ranges that analysts can compare.

  • In the Visual Studio Code Explorer view from earlier, select the models folder.
  • Click New Folder in the Explorer toolbar.

You'll see a folder name field inside models.

  • Enter marts as the folder name.

The marts folder now appears under models.

  • Select the new marts folder.
  • Click New File in the Explorer toolbar.

You'll see a file name field inside marts.

  • Enter mart_kev_vendor_summary.sql as the file name.

Visual Studio Code opens the new models/marts/mart_kev_vendor_summary.sql file in the editor.

  • Build the vendor aggregation by pasting this code into models/marts/mart_kev_vendor_summary.sql:
with vulnerabilities as (
    select *
    from {{ ref('stg_kev') }}
)

select
    vendor_project,
    count(*) as vulnerability_count,
    sum(case when known_ransomware_campaign_use = 'Known' then 1 else 0 end) as ransomware_known_count,
    sum(case when forensic_triage = 'Yes' then 1 else 0 end) as forensic_triage_count,
    min(date_added) as first_added_date,
    max(date_added) as latest_added_date
from vulnerabilities
group by vendor_project
order by vulnerability_count desc, vendor_project

What does this model do?

  • The vulnerabilities CTE reads from the corrected stg_kev model through a dbt dependency.
  • The group by vendor_project clause establishes one row per vendor or project.
  • The vulnerability_count field counts every staged CVE assigned to each vendor.
  • The conditional sums count records marked 'Known' or 'Yes'.
  • The date fields show the earliest and latest catalog additions for each vendor.
  • The final ordering places the highest vulnerability counts first.
  • Save models/marts/mart_kev_vendor_summary.sql.
  • Return to the PowerShell terminal from earlier.
  • Build the current project by running this command:
docker compose run --rm pipeline dbt build

What does this build check?

  • Docker Compose runs the command inside the pinned pipeline service.
  • The --rm option removes the one-off container after the command finishes.
  • dbt follows the dependency from stg_kev to mart_kev_vendor_summary.
  • The existing schema tests check the mart's vendor grain and required fields.

You'll see successful results for the staging model and the new vendor mart. You'll also see the configured mart tests complete successfully.

That is the business layer working. Your DuckDB database now contains a tested vendor-level summary.

Mart build not completing?

Confirm that mart_kev_vendor_summary.sql sits inside models/marts. Check that ref('stg_kev') matches the existing staging model name.

Compare the aggregation with the reference below if a test fails. Help me troubleshoot the vendor mart build.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

with vulnerabilities as (
    select *
    from {{ ref('stg_kev') }}
)

select
    vendor_project,
    count(*) as vulnerability_count,
    sum(case when known_ransomware_campaign_use = 'Known' then 1 else 0 end) as ransomware_known_count,
    sum(case when forensic_triage = 'Yes' then 1 else 0 end) as forensic_triage_count,
    min(date_added) as first_added_date,
    max(date_added) as latest_added_date
from vulnerabilities
group by vendor_project
order by vulnerability_count desc, vendor_project
Configure the CSV export

The dbt-duckdb external materialization writes a model to a file outside the database. This creates an analytics-ready handoff while DuckDB remains the persistent analytical store.

The export model selects from your tested mart. Its configured location places the CSV inside the existing data/output folder.

  • In the Explorer view, select the models folder.
  • Click New Folder in the Explorer toolbar.

You'll see another folder name field inside models.

  • Enter exports as the folder name.

The exports folder now appears beside marts.

  • Select the new exports folder.
  • Click New File in the Explorer toolbar.

You'll see a file name field inside exports.

  • Enter kev_vendor_summary_export.sql as the file name.

Visual Studio Code opens the new models/exports/kev_vendor_summary_export.sql file in the editor.

  • Configure the external CSV model by pasting this code into models/exports/kev_vendor_summary_export.sql:
{{ config(
    materialized='external',
    location='/workspace/data/output/kev_vendor_summary.csv',
    format='csv'
) }}

select *
from {{ ref('mart_kev_vendor_summary') }}

What does this export model do?

  • The external materialization tells dbt-duckdb to write the result outside the database.
  • The location value targets /workspace/data/output/kev_vendor_summary.csv inside the container.
  • The bind mount makes that file available at data/output/kev_vendor_summary.csv on Windows.
  • The format='csv' setting creates the supported Power BI handoff format.
  • The ref('mart_kev_vendor_summary') call exports the tested business-grain model.
  • Save models/exports/kev_vendor_summary_export.sql.
  • Confirm that kev_vendor_summary_export.sql appears inside models/exports in the Explorer view.

Can't find the export model?

Make sure the file is inside models/exports. A file outside models is not included in this dbt project.

Check that the file ends with .sql. Help me check the export model location.

✔️ Awesome, I've got everything!

Great. Double-check that you saved models/exports/kev_vendor_summary_export.sql.

ⓧ I'd like to double check the full code

{{ config(
    materialized='external',
    location='/workspace/data/output/kev_vendor_summary.csv',
    format='csv'
) }}

select *
from {{ ref('mart_kev_vendor_summary') }}
Run the pipeline and inspect the CSV

The full build now follows the dependency chain from staging to mart to export. It also runs every configured data test before the handoff is complete.

Before you run the build, do you expect every model and test to succeed with the corrected staging grain?

  • Return to the PowerShell terminal from earlier.
  • Build the complete pipeline by running this command:
docker compose run --rm pipeline dbt build

What does the complete build prove?

  • dbt builds the staging view before the dependent vendor mart.
  • dbt runs the configured tests against the materialized models.
  • The export model writes the tested mart to the configured CSV path.
  • A successful run proves the pipeline can rebuild from its declared dependencies.

You'll see successful results for every model and configured test. The build also writes data/output/kev_vendor_summary.csv to your Windows project folder.

You now have the analyst handoff. The same build that validates the data also produces the file Power BI can consume.

  • In the Visual Studio Code Explorer view, expand the data folder.
  • Expand the output folder.
  • Select kev_vendor_summary.csv to open the export.

The first row reads vendor_project,vulnerability_count,ransomware_known_count,forensic_triage_count,first_added_date,latest_added_date.

  • Compare the first several values in the vulnerability_count column.

You'll see the larger vulnerability counts first. Vendors with equal counts are ordered by vendor_project.

CSV missing or unsorted?

Confirm that the complete build finished successfully. Check that the export model uses ref('mart_kev_vendor_summary').

Compare the configured location with /workspace/data/output/kev_vendor_summary.csv. Help me troubleshoot the missing CSV export.

Your tested vendor mart now reaches a portable analytics file. Next, you'll inspect its dbt lineage and turn the CSV into a ranked Power BI chart.

Inspect Lineage and Visualize the Result

Your tested vendor summary now turns the live CISA Known Exploited Vulnerabilities Catalog into one row per vendor or project. The next question is whether another person can trace that result back to its source.

dbt lineage makes that path visible from source through export. Power BI Desktop turns the trusted CSV into a ranking a stakeholder can explore.

In this step, get ready to:
  • Serve dbt documentation from the existing container.
  • Verify the lineage from the CISA source through the CSV export.
  • Create a saved Power BI vendor ranking.
Generate and inspect dbt lineage

The documentation site turns project references into an interactive dependency graph. Source declarations also appear in that graph.

The existing Docker Compose service keeps the documentation tied to the same pinned environment. The docs server stays attached to the PowerShell session until you stop it with Ctrl+C.

  • Generate the dbt documentation files and serve them on port 8080 by running this command in the PowerShell panel from earlier:
docker compose run --rm --service-ports pipeline sh -c "dbt docs generate && dbt docs serve --host 0.0.0.0 --port 8080 --no-browser"

What does this command do?

  • The docker compose run command starts a one-off container for the pipeline service.
  • The --rm flag removes that container after you stop the server.
  • The --service-ports flag exposes the configured service port to Windows.
  • The shell generates the documentation files before serving the site on port 8080.
  • Wait for PowerShell to confirm that the documentation site is being served.
  • Keep the PowerShell session running.
  • Open http://localhost:8080 in your browser.

You will see the generated dbt documentation site. Its project graph represents the dependency path behind the exported vendor summary.

  • Select the cisa.kev_catalog source in the project graph.
  • Trace the graph through stg_kev, mart_kev_vendor_summary, and kev_vendor_summary_export.
  • Select the stg_kev model.
  • Inspect its model description.
  • Inspect its configured data tests.
  • Inspect its compiled SQL.

The graph connects the CISA source to staging. It continues through the vendor mart to the CSV export.

You can now show where the report data came from. You can also show which checks protected it.

Docs page not loading?

Confirm that the PowerShell command is still running. The browser cannot reach the site after the container stops.

Confirm that Docker Desktop is still running. Help me diagnose why the dbt documentation site is not loading on localhost port 8080.

Load the vendor summary into Power BI

The Text/CSV connector reads the tested export directly from the project folder. The stable header gives each chart field a clear analytical role.

  • Press the Windows key to open the Windows search bar.
  • Type Power BI Desktop into the search bar.
  • Press Enter to launch Power BI Desktop.
  • Select Get data.
  • Choose Text/CSV.
  • Select C:\Projects\kev-dbt-pipeline\data\output\kev_vendor_summary.csv in the file picker.
  • Select Load in the Navigator.

You will see vendor_project, vulnerability_count, ransomware_known_count, forensic_triage_count, first_added_date, and latest_added_date in the Fields pane.

Each imported row represents one vendor or project. The dataset is ready for a ranking visual.

CSV not appearing in Power BI?

Confirm that the selected file ends with kev_vendor_summary.csv. The file must come from the data\output folder.

If the preview does not contain the expected fields, rebuild the export from the PowerShell panel after stopping the docs server. Help me troubleshoot this Power BI CSV import.

Build and verify the vendor ranking

A bar chart makes the vendor-level grain visible. Its descending order places the largest vulnerability counts first.

  • Select a bar-chart visual from the Visualizations pane on the Report canvas.
  • Drag vendor_project from the Fields pane to the chart category field.
  • Drag vulnerability_count from the Fields pane to the chart value field.
  • Use the chart's sort control to select vulnerability_count.
  • Set the chart's order to descending.
  • Save the report locally using the Power BI Desktop save control.

Before you inspect the finished result, make a mental prediction about which vendor has the longest bar.

  • Switch back to the browser tab serving the local dbt documentation site.
  • Trace the graph from cisa.kev_catalog through kev_vendor_summary_export once more.
  • Switch back to Power BI Desktop.
  • Inspect the bars from top to bottom.

The browser shows the CISA source flowing through staging. The graph continues through the mart to the export.

Power BI shows the longest bar first. The remaining bars decrease according to vulnerability_count.

Chart not ranking vendors correctly?

Confirm that vendor_project supplies the chart category. Confirm that vulnerability_count supplies the chart value.

Check that the sort field is vulnerability_count. Help me fix this Power BI vendor ranking.

You now have browsable lineage for your tested pipeline. You also have a saved stakeholder-facing vendor ranking.

Secret mission

Add a Cross-Column Business Rule Test

Add a singular SQL test that rejects impossible remediation chronology. You will prove the CISA feed has zero invalid rows and connect the rule to staging in dbt documentation.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your local pipeline ready, pause its running services, or remove it completely. This project creates no cloud resources, so keeping its files does not create ongoing cloud charges.

Check Docker Desktop Licensing

Under Docker Personal, Docker Desktop costs $0 for personal use, education, non-commercial open source, and qualifying small businesses.

Larger-enterprise or government use may require a paid subscription.

Resources you used:

  • A running dbt documentation server at http://localhost:8080.
  • A local Docker image built with Docker Compose from compose.yaml.
  • The C:\Projects\kev-dbt-pipeline folder containing your source code plus generated dbt artifacts.
  • The live CISA Known Exploited Vulnerabilities Catalog data at data/raw/kev.json.
  • The DuckDB database at data/kev.duckdb plus the exported CSV at data/output/kev_vendor_summary.csv.
  • A locally saved Power BI Desktop report containing your vendor ranking chart.

Keep everything running

No action is needed while you are actively using the pipeline. The documentation site remains available for lineage demonstrations.

  • Leave the dbt documentation server running in PowerShell.
  • Retain the local Docker image for future pipeline builds.
  • Keep C:\Projects\kev-dbt-pipeline with its source code plus generated data.
  • Retain the saved Power BI Desktop report.

Pause - I'll come back to this later

Pausing stops the local documentation server. Your project files plus local image stay ready for another session.

  • Return to the PowerShell window serving the dbt documentation.
  • Press Ctrl+C to stop the documentation server.
  • Stop Compose resources by running this command:
docker compose down

What Does This Command Do?

This stops and removes resources created by Compose. The local service image plus bind-mounted project files remain on your computer.

  • Refresh http://localhost:8080.

The documentation site no longer loads. Your source code, downloaded feed, database, exported CSV, report, and local image remain available.

Docs Server Still Available?

  • Confirm the PowerShell process that served the documentation has stopped.
  • Confirm Docker Desktop is still running if the cleanup command cannot reach Docker.
  • Ask for help with the local cleanup command:

Delete - I don't want to use this again

Deleting this workspace is permanent. The removal is limited to the local image, project folder, and saved report listed here.

Remove the Docker resources:

  • Return to the PowerShell window serving the dbt documentation.
  • Press Ctrl+C to stop the documentation server.
  • Remove the Compose resources plus local service image by running this command:
docker compose down --rmi local

What Does This Command Do?

This stops and removes Compose resources. The --rmi local option also removes locally tagged service images.

PowerShell returns to the prompt after the Compose resources plus local service image are removed.

Remove the saved report:

  • Close Power BI Desktop.
  • Press the Windows key to open the search bar.
  • Type File Explorer.
  • Press Enter to open File Explorer.
  • Return to the folder where you saved the Power BI report.
  • Select the saved Power BI report.
  • Press Delete.

The report is no longer listed in its former folder.

Remove the project folder:

  • Enter C:\Projects in the File Explorer address bar.
  • Select the kev-dbt-pipeline folder.
  • Press Delete.

You should no longer see kev-dbt-pipeline inside C:\Projects. This removes the source code, raw feed, DuckDB database, exported CSV, generated documentation, and custom chronology test.

Unable to Remove Everything?

  • Confirm Docker Desktop is running if PowerShell cannot remove the local image.
  • Close Visual Studio Code if Windows reports that the project folder is in use.
  • Close PowerShell if it is still using C:\Projects\kev-dbt-pipeline.
  • Ask for help removing the remaining local resource:

Nice Work!

Nice Work!

You did it. Your containerized dbt pipeline now powers a ranked Power BI Desktop report from tested CISA Known Exploited Vulnerabilities Catalog data stored in DuckDB.

You've learned how to:

  • Run the complete pinned analytics stack inside a reproducible Docker Compose environment on Windows.
  • Model the live CISA JSON at one row per CVE. Use a dbt uniqueness test to catch the grain-breaking CWE expansion.
  • Deliver a tested vendor risk mart as a CSV for Power BI Desktop. Trace its path from source to export through dbt lineage.
  • Secret Mission: Add a singular SQL test that proves due_date never precedes date_added.

Ready to quiz yourself?