Network Threat Analytics Pipeline

Build an ETL pipeline that detects network attacks in 2.8M+ flows.

Introduction

30 Second Summary

Every time you browse the web, streams of network traffic flow between your device and the internet. Security teams monitor millions of these flows daily to catch the handful that signal an attack.

In this project, you will build an ETL pipeline that processes real-world network traffic data. Your pipeline will clean raw records, load them into a structured database, and power an interactive threat dashboard.

What You'll Build

Picture running a single command and watching a Streamlit dashboard open in your browser, ready to filter attacks by type, spot traffic spikes on a timeline, and drill into suspicious network flows from a real-world intrusion detection dataset.

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

  • Run a single Python script that ingests raw CSVs, validates data quality, and loads clean records into a PostgreSQL star schema.
  • Write SQL queries that detect attack patterns across 2.8M+ network flow records.
  • Open a browser and interact with a threat dashboard: filter attacks by type, see traffic spikes, and drill into suspicious flows.
  • Secret Mission: Add an anomaly scoring system that flags flows with unusual packet characteristics.

Are there any prerequisites?

This project assumes basic Python and SQL knowledge. You should also have PostgreSQL installed on your local machine.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Your Pipeline Environment

Your pipeline needs three things before it can process a single network flow: a Python library that talks to PostgreSQL, a data processing library for wrangling millions of rows, and a dashboard framework to visualize the results.

In this step, you will create a project folder, install the Python packages that power the pipeline, download the network traffic dataset you will analyze, and set up a fresh database in PostgreSQL.

In this step, get ready to:
  • Create a threat-pipeline project folder with a Python virtual environment.
  • Install psycopg2-binary, streamlit, and pandas.
  • Download the CICIDS2017 network traffic dataset and create the threat_analytics database.
Create a project folder and virtual environment

Every Python project should live in its own folder with its own isolated set of packages. A virtual environment keeps the libraries you install here separate from your system Python, so nothing clashes.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Open a terminal inside VS Code by pressing Ctrl+` (backtick key, found above Tab).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir threat-pipeline
cd threat-pipeline

Your terminal prompt now shows threat-pipeline as the current directory.

  • Create a Python virtual environment by running:
python -m venv venv
  • Activate the virtual environment. The command differs by operating system:

macOS

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

Linux users: this is the same command. It works on both macOS and Linux.

Windows

  • Activate the virtual environment by running:
venv\Scripts\activate

You should see (venv) appear at the start of your terminal prompt. This confirms you are inside the virtual environment.

Don't see (venv) in your prompt?

If you get a "command not found" error when running python -m venv venv, try using python3 instead of python. On macOS, the command is often python3.

On Windows, make sure you are using PowerShell (VS Code's default), not Command Prompt. If activation is blocked by an execution policy, run Set-ExecutionPolicy -Scope CurrentUser RemoteSigned first.

Help me activate my Python virtual environment.

  • Install the three Python packages your pipeline needs by running:
pip install psycopg2-binary==2.9.12 streamlit==1.61.1 pandas

What do these packages do?

  • psycopg2-binary lets Python send SQL queries to PostgreSQL and read results back.
  • Streamlit turns a plain Python script into an interactive web dashboard with charts, filters, and tables.
  • pandas is a data processing library that makes it easy to read, clean, and transform large CSV files.

The install takes a minute or two because Streamlit pulls in several dependencies. When it finishes, you should see Successfully installed followed by a list of packages near the bottom of the output.

Install failing with an error?

Make sure (venv) is visible in your terminal prompt. Installing without the virtual environment active can cause permission errors.

If you see errors about incompatible Python versions, your Python may be older than 3.10. Check your version with python --version and upgrade if needed. Streamlit requires Python 3.10 or higher.

Help me fix my pip install errors.

Download the CICIDS2017 dataset from Kaggle

The CICIDS2017 dataset contains over 2.8 million labeled network flow records captured across five days at the Canadian Institute for Cybersecurity. It includes 14 attack types like DDoS, brute force, and port scans mixed in with normal traffic.

  • Open the dataset page in your browser: CICIDS2017 on Kaggle.
  • Click the Download button near the top of the page.

Don't have a Kaggle account?

Kaggle requires a free account to download datasets. Click Sign In and create an account with your Google or email address. It only takes a minute.

The download is about 885 MB, so it may take a few minutes depending on your internet connection.

  • While the download runs, switch back to your VS Code terminal and create a data folder inside your project by running:
mkdir data
  • Once the download finishes, find the downloaded ZIP file in your Downloads folder.
  • Extract the ZIP file.
  • Move all 8 CSV files into the data folder inside threat-pipeline.

Quick way to move the files

Open Finder (macOS) or File Explorer (Windows). Navigate to your Downloads folder, open the extracted folder, select all the CSV files, and drag them into Desktop/threat-pipeline/data/.

  • Confirm the files are in the right place by running:
ls data/

You should see 8 CSV files, one per day of captured traffic. For example: Monday-WorkingHours.pcap_ISCX.csv, Tuesday-WorkingHours.pcap_ISCX.csv, and so on through to the Friday files.

Don't see 8 CSV files?

Some ZIP extractors create a nested folder. If ls data/ shows a single folder instead of CSV files, move the CSVs up one level into data/ directly.

On Windows, if ls is not recognized, use dir data/ instead. Both commands list folder contents.

Help me get the CICIDS2017 CSV files into my data folder.

Create the project database in PostgreSQL

Your pipeline needs a database to load data into. PostgreSQL is already installed on your machine, so you just need to create a new, empty database for this project.

  • Create the project database by running:
psql -U postgres -c "CREATE DATABASE threat_analytics;"

What does this command do?

psql is the PostgreSQL command-line client. The -U postgres flag connects as the postgres superuser. The -c flag runs the SQL statement that follows and then exits.

If PostgreSQL prompts you for a password, enter the password you set when you first installed PostgreSQL.

  • Verify the database was created by running:
psql -U postgres -d threat_analytics -c "SELECT version();"

You should see your PostgreSQL version number printed in the output. This confirms the threat_analytics database exists and is reachable.

Getting a connection error from psql?

If you see a "connection refused" error, PostgreSQL may not be running. On macOS, start the PostgreSQL service from your terminal. On Windows, open Services and make sure the PostgreSQL service is set to Running.

If you get a "role postgres does not exist" error on macOS, your default superuser may be your OS username instead. Try running the command without -U postgres.

Help me connect to PostgreSQL and create my database.

One last check. Before you run the next command, what do you think will happen when you run streamlit hello in a terminal where Streamlit is installed?

  • Test that Streamlit is working by running:
streamlit hello

A new tab opens in your browser with a Streamlit demo app showing interactive charts and animations. This confirms Streamlit is installed correctly.

  • Press Ctrl+C in your terminal to stop the Streamlit demo.

Your environment is ready with a project folder, the right packages, the CICIDS2017 dataset, and a fresh PostgreSQL database. Next up, you will load the raw CSV data straight into PostgreSQL and see firsthand why 79 flat columns make analysis painful.

Quick-Load Raw Data and Hit the Wall

Your pipeline environment is ready and the CICIDS2017 dataset is sitting in your data/ folder. Now you need to get that data from CSV files into PostgreSQL where you can query it.

The fastest approach is loading all 8 CSV files directly into a single flat table. In this step, you will do exactly that, then try to run some analytical queries and discover why the fast path creates problems you need to solve.

In this step, get ready to:
  • Load all 8 CICIDS2017 CSV files into a single PostgreSQL table.
  • Run SQL queries and discover the data quality issues hiding in the raw data.
Write a script to load the raw CSV files

Your script will use pandas to read each CSV file into a DataFrame, then write all the data into a single PostgreSQL table called raw_flows.

  • In VS Code, create a new file called load_raw.py in your threat-pipeline folder.
  • Set up the database connection by pasting this code into load_raw.py:
import pandas as pd
import psycopg2
from sqlalchemy import create_engine
import os
import glob

# Connection details for your PostgreSQL database
DB_CONFIG = {
    "dbname": "threat_analytics",
    "user": "postgres",
    "password": "your-password-here",
    "host": "localhost",
    "port": "5432"
}

# Create a SQLAlchemy engine that pandas uses for database writes
engine = create_engine(
    f"postgresql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['dbname']}"
)

What does this code do?

  • DB_CONFIG stores your PostgreSQL connection details (database name, user, password, host, port).
  • create_engine builds a SQLAlchemy engine. This is what pandas uses under the hood to write DataFrames directly to PostgreSQL tables.
  • SQLAlchemy was installed automatically as a dependency of pandas and Streamlit, so you did not need to install it separately.

Update the password

Replace your-password-here with the password you set for your PostgreSQL postgres user. If you did not set a password during PostgreSQL installation, try leaving it as an empty string ("").

  • Add the CSV loading logic below the engine setup by pasting this code at the bottom of load_raw.py:
# Find all CSV files in the data/ folder
csv_files = glob.glob(os.path.join("data", "*.csv"))
print(f"Found {len(csv_files)} CSV files")

# Read each CSV file into a pandas DataFrame
all_frames = []
for csv_file in csv_files:
    print(f"Reading {os.path.basename(csv_file)}...")
    df = pd.read_csv(csv_file, low_memory=False)
    all_frames.append(df)

# Combine all DataFrames into one and load into PostgreSQL
combined = pd.concat(all_frames, ignore_index=True)
print(f"Total rows: {len(combined)}")
print(f"Columns: {len(combined.columns)}")

combined.to_sql("raw_flows", engine, if_exists="replace", index=False, chunksize=10000)
print("Raw data loaded into PostgreSQL table 'raw_flows'")

What does this code do?

  • glob.glob finds all .csv files in the data/ folder.
  • The for loop reads each CSV into a pandas DataFrame and adds it to a list.
  • pd.concat merges all 8 DataFrames into a single combined DataFrame.
  • to_sql writes the combined DataFrame into a PostgreSQL table called raw_flows. The chunksize=10000 parameter writes 10,000 rows at a time to avoid overwhelming memory.
  • Save load_raw.py.
  • Run the script in your terminal by running this command:
python load_raw.py

This will take a few minutes

The script is reading 8 large CSV files and writing over 2.8 million rows to PostgreSQL. You will see each file name printed as it is read. When the script finishes, you should see a total row count above 2.8 million, 79 columns, and the message Raw data loaded into PostgreSQL table 'raw_flows'.

Script failing with a connection error?

  • Make sure PostgreSQL is running. If you stopped it since Step 1, restart the service.
  • Double check that you replaced your-password-here with your actual PostgreSQL password in DB_CONFIG.
  • Confirm that the threat_analytics database exists. You created it in Step 1.
  • Make sure you are running the script from inside the threat-pipeline folder so that the data/ path resolves correctly.

Why is my load_raw.py script failing to connect to PostgreSQL?

✔️ Awesome, I've got everything!

Great. Make sure you have saved load_raw.py and that the script completed successfully.

ⓧ I'd like to double check the full code

Scroll up and confirm your load_raw.py file contains both code blocks in this order:

  • The imports, DB_CONFIG dictionary, and engine setup (first chunk).
  • The CSV file reading loop, pd.concat, and to_sql call (second chunk).

Make sure the password in DB_CONFIG is your actual PostgreSQL password, not the placeholder.

Run exploratory queries and discover the problems

The data is in PostgreSQL. Time to start analyzing it. You will connect to the database, run a few queries, and see what happens when you try to work with 79 columns of raw, unstructured data.

  • Connect to your threat_analytics database by running this command:
psql -U postgres -d threat_analytics
  • Check how many rows loaded by running this query:
SELECT COUNT(*) FROM raw_flows;

You should see a count above 2.8 million. All 8 days of network traffic are now in one table.

  • Take a look at the column names in your table by running this query:
SELECT column_name FROM information_schema.columns
WHERE table_name = 'raw_flows' LIMIT 10;

Look carefully at the column names. Several of them have a leading space before the actual name. For example, the label column is stored as Label (with a space before the L), not Label. This means every SQL query has to include the leading space or it will fail.

Before you run the next query, think about this: given these messy column names, how straightforward do you think it will be to get a breakdown of attack types?

  • Try to query the attack type distribution by running this:
SELECT " Label", COUNT(*) FROM raw_flows
GROUP BY " Label"
ORDER BY COUNT(*) DESC LIMIT 10;

You see attack type counts, which is useful. But notice the column name in the output: Label with a leading space. You had to wrap it in double quotes with the space just to make the query work. Every query you write against this table will have this same friction.

Why the leading spaces?

The original CICIDS2017 CSV files have spaces before many column headers. When pandas reads the CSV and writes to PostgreSQL, it preserves the column names exactly as they are. This is a common real-world data quality issue.

  • Check for missing values in the Flow Bytes/s column by running this query:
SELECT COUNT(*) FROM raw_flows WHERE "Flow Bytes/s" IS NULL;

The count is not zero. Some rows have missing values in this critical flow metric. These came from NaN entries in the original CSVs.

  • Check for Infinity values in the same column by running this query:
SELECT COUNT(*) FROM raw_flows
WHERE "Flow Bytes/s" = 'Infinity'::float8
OR "Flow Bytes/s" = '-Infinity'::float8;

More bad data. Infinity values make aggregation functions like AVG and SUM unreliable because a single Infinity value skews the entire result.

  • Now try to answer a real analytical question. Run this query to find the percentage of malicious traffic:
SELECT
  COUNT(CASE WHEN " Label" != 'BENIGN' THEN 1 END) * 100.0 / COUNT(*) AS pct_malicious
FROM raw_flows;

You get a number, but the query itself reveals the problem. You are comparing raw label strings with leading spaces on a flat 79-column table. There is no clean way to group attacks by category, no separation between flow metrics and metadata, and no guarantee the data underneath is even accurate.

  • Exit psql by typing:
\q

What did you just discover?

You hit the wall. The quick-load approach got data into PostgreSQL fast, but the raw table has serious problems:

  • Column names have leading spaces, making every query awkward.
  • Flow Bytes/s contains NULL and Infinity values that corrupt aggregations.
  • The dataset contains over 300,000 duplicate rows that inflate your counts.
  • 79 flat columns with no structure make analytical queries painful. There is no clean way to ask "which attack category generated the most traffic today?"

These are exactly the kinds of problems that a proper ETL pipeline with a star schema is designed to solve.

You have seen the problems firsthand. The raw data is in PostgreSQL, but working with it is painful. In the next step, you will design a star schema that separates flow metrics from dimensions like attack type and protocol, and build an ETL pipeline that cleans every one of these issues.

Design a Star Schema and Build the ETL Pipeline

You loaded 2.8M+ rows into a flat table and immediately felt the pain. Column names had leading spaces. Rows contained NaN and Infinity values. Answering even simple questions required wrestling with 79 messy columns.

A star schema solves this by separating the "what happened" (flow metrics) from the "what kind" (attack types, protocols). Instead of one massive flat table, you get focused dimension tables linked to a central fact table. This is the core data modeling skill that data engineering interviews test.

In this step, get ready to:
  • Create a star schema with dimension and fact tables in PostgreSQL.
  • Build a Python ETL pipeline that cleans raw data and loads it into the star schema.
  • Validate the loaded data with analytical SQL queries.
Create the star schema DDL in PostgreSQL

The star schema uses two types of tables. Dimension tables describe the categories (attack types, protocols). A fact table holds the numeric measurements for each network flow, linked to the dimensions by ID.

What is a star schema?

Picture a star. The center is your fact table with millions of rows of measurements. Each point of the star is a dimension table that gives context to those measurements.

Instead of repeating "DoS Hulk" in 230,000 rows, the fact table stores a single attack_type_id that points to a dimension row. This saves storage, speeds up GROUP BY queries, and makes the data much easier to explore.

  • In VS Code, create a new file called schema.sql inside your threat-pipeline folder.
  • Add the DROP statements and the two dimension tables by pasting this code:
-- Drop existing tables to start fresh
DROP TABLE IF EXISTS fact_flows CASCADE;
DROP TABLE IF EXISTS dim_attack_type CASCADE;
DROP TABLE IF EXISTS dim_protocol CASCADE;

-- Dimension table: maps attack labels to categories
CREATE TABLE dim_attack_type (
    attack_type_id SERIAL PRIMARY KEY,
    label VARCHAR(100) NOT NULL UNIQUE,
    attack_category VARCHAR(50) NOT NULL
);

-- Dimension table: maps protocol numbers to names
CREATE TABLE dim_protocol (
    protocol_id SERIAL PRIMARY KEY,
    protocol_number INTEGER NOT NULL UNIQUE,
    protocol_name VARCHAR(20) NOT NULL
);

What do these tables do?

  • dim_attack_type stores each unique attack label (like "DoS Hulk") and maps it to a broader category (like "DoS"). This lets you query by category without parsing raw labels.
  • dim_protocol maps raw protocol numbers (6, 17) to readable names (TCP, UDP).
  • The DROP TABLE IF EXISTS ... CASCADE lines ensure a clean slate each time you rebuild the schema.
  • Now add the fact table below the dimension tables in the same file:
-- Fact table: one row per network flow with key metrics
CREATE TABLE fact_flows (
    flow_id SERIAL PRIMARY KEY,
    attack_type_id INTEGER REFERENCES dim_attack_type(attack_type_id),
    protocol_id INTEGER REFERENCES dim_protocol(protocol_id),
    destination_port INTEGER,
    flow_duration BIGINT,
    total_fwd_packets INTEGER,
    total_bwd_packets INTEGER,
    total_length_fwd_packets FLOAT,
    total_length_bwd_packets FLOAT,
    flow_bytes_per_sec FLOAT,
    flow_packets_per_sec FLOAT,
    fwd_packet_length_mean FLOAT,
    bwd_packet_length_mean FLOAT,
    packet_length_mean FLOAT,
    packet_length_std FLOAT
);

What does the fact table store?

Each row in fact_flows represents a single network flow. The REFERENCES constraints link each flow to its attack type and protocol in the dimension tables. The remaining columns hold the key metrics you will analyze: packet counts, byte rates, and duration.

Compare this to the 79 columns in raw_flows. The fact table keeps only the columns that matter for threat analysis.

  • Save schema.sql.
  • Apply the schema to your database by running this command in your terminal:
psql -U postgres -d threat_analytics -f schema.sql
  • Verify the tables were created by running:
psql -U postgres -d threat_analytics -c "\dt"

You should see dim_attack_type, dim_protocol, and fact_flows listed alongside raw_flows from Step 2.

Don't see the tables?

  • Check that you are in the threat-pipeline folder when you run the psql command. The -f schema.sql flag looks for the file in the current directory.
  • If you see a "role does not exist" error, replace postgres with the username you used when installing PostgreSQL.
  • Help me troubleshoot my schema creation

✔️ Awesome, I've got everything!

Great. Make sure schema.sql is saved before moving on.

ⓧ I'd like to double check the full code

Scroll up and confirm your schema.sql has these sections in order:

  • Three DROP TABLE IF EXISTS statements (fact_flows, dim_attack_type, dim_protocol).
  • CREATE TABLE dim_attack_type with three columns.
  • CREATE TABLE dim_protocol with three columns.
  • CREATE TABLE fact_flows with 15 columns including two foreign key references.
Build the ETL pipeline script

The schema is in place. Now you need a script that reads the raw CSVs, fixes every data quality issue you found in Step 2, and loads clean records into the star schema.

  • Create a new file called etl_pipeline.py in your threat-pipeline folder.
  • Start with the imports and database configuration by pasting this code:
import pandas as pd
import psycopg2
from sqlalchemy import create_engine
import os
import glob
import numpy as np

# Database connection settings
DB_CONFIG = {
    "dbname": "threat_analytics",
    "user": "postgres",
    "password": "your-password-here",
    "host": "localhost",
    "port": "5432"
}

# Create SQLAlchemy engine for pandas operations
engine = create_engine(
    f"postgresql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['dbname']}"
)

What does this code do?

  • The imports bring in pandas for data manipulation, psycopg2 for direct PostgreSQL queries, and numpy for handling Infinity and NaN values.
  • DB_CONFIG holds the same connection settings you used in load_raw.py. Replace your-password-here with your PostgreSQL password.
  • Add the attack category mappings and protocol map below the engine by pasting this code:
# Map raw attack labels to higher-level categories
ATTACK_CATEGORIES = {
    "BENIGN": "Benign",
    "FTP-Patator": "Brute Force",
    "SSH-Patator": "Brute Force",
    "DoS slowloris": "DoS",
    "DoS Slowhttptest": "DoS",
    "DoS Hulk": "DoS",
    "DoS GoldenEye": "DoS",
    "Heartbleed": "DoS",
    "Web Attack \u2013 Brute Force": "Web Attack",
    "Web Attack \u2013 XSS": "Web Attack",
    "Web Attack \u2013 Sql Injection": "Web Attack",
    "Infiltration": "Infiltration",
    "Bot": "Botnet",
    "PortScan": "Port Scan",
    "DDoS": "DDoS",
}

# Map IP protocol numbers to readable names
PROTOCOL_MAP = {
    0: "HOPOPT",
    6: "TCP",
    17: "UDP",
}

print("=== ETL Pipeline Starting ===")

What do these mappings do?

ATTACK_CATEGORIES groups 14 specific attack labels into broader categories. For example, "DoS slowloris" and "DoS Hulk" both map to "DoS". This populates the dim_attack_type dimension table later.

PROTOCOL_MAP translates raw protocol numbers (6, 17) into human-readable names (TCP, UDP) for the dim_protocol table.

  • Save etl_pipeline.py.
  • Test that the imports and config are correct by running:
python etl_pipeline.py

You should see === ETL Pipeline Starting === printed in your terminal. This confirms all the imports loaded successfully.

Seeing a ModuleNotFoundError?

  • Make sure your virtual environment is activated. You should see (venv) at the start of your terminal prompt.
  • If psycopg2 is missing, re-run pip install psycopg2-binary inside your activated environment.
  • Help me fix this import error
  • Add the CSV reading and data cleaning logic below the print statement:
# Read all CSV files from the data directory
csv_files = glob.glob(os.path.join("data", "*.csv"))
all_frames = []
for csv_file in csv_files:
    print(f"Reading {os.path.basename(csv_file)}...")
    df = pd.read_csv(csv_file, low_memory=False)
    all_frames.append(df)

combined = pd.concat(all_frames, ignore_index=True)
total_raw = len(combined)
print(f"Total raw rows: {total_raw}")

# Fix column names by stripping leading/trailing spaces
combined.columns = combined.columns.str.strip()

# Replace Infinity with NaN, then drop rows with missing values
numeric_cols = combined.select_dtypes(include=[np.number]).columns
combined.replace([np.inf, -np.inf], np.nan, inplace=True)
rows_before_drop = len(combined)
combined.dropna(subset=numeric_cols, inplace=True)
rows_dropped_nan = rows_before_drop - len(combined)
print(f"Rows dropped (NaN/Inf): {rows_dropped_nan}")

# Remove duplicate rows
rows_before_dedup = len(combined)
combined.drop_duplicates(inplace=True)
duplicates_removed = rows_before_dedup - len(combined)
print(f"Duplicates removed: {duplicates_removed}")

final_count = len(combined)
print(f"Final clean row count: {final_count}")

What does this code do?

  • The loop reads all 8 CSV files from the data/ directory and concatenates them into one DataFrame.
  • combined.columns.str.strip() fixes the leading-space problem you discovered in Step 2, so Label becomes Label.
  • Infinity values are replaced with NaN, then any row with NaN in a numeric column is dropped. Finally, exact duplicate rows are removed.
  • Save etl_pipeline.py and run it again:
python etl_pipeline.py

This time you should see each CSV file being read, followed by the total raw rows, rows dropped for NaN/Inf, duplicates removed, and the final clean row count.

Script hanging on the CSV reading step?

  • Reading 2.8M+ rows takes time. Give it a minute or two to process all 8 files.
  • If the script runs out of memory, close other applications to free up RAM. The dataset needs roughly 2-3 GB in memory.
  • Help me troubleshoot slow CSV reading
  • Add the code that creates the star schema tables and populates the two dimension tables:
# Connect to PostgreSQL and create star schema tables
conn = psycopg2.connect(**DB_CONFIG)
cur = conn.cursor()

with open("schema.sql", "r") as f:
    cur.execute(f.read())
conn.commit()

# Populate dim_attack_type with unique labels and categories
labels = combined["Label"].unique()
for label in labels:
    label_clean = label.strip()
    category = ATTACK_CATEGORIES.get(label_clean, "Unknown")
    cur.execute(
        "INSERT INTO dim_attack_type (label, attack_category) VALUES (%s, %s) ON CONFLICT (label) DO NOTHING",
        (label_clean, category)
    )
conn.commit()

# Populate dim_protocol with protocol numbers and names
protocols = combined["Protocol"].unique()
for proto in protocols:
    proto_int = int(proto)
    proto_name = PROTOCOL_MAP.get(proto_int, f"OTHER-{proto_int}")
    cur.execute(
        "INSERT INTO dim_protocol (protocol_number, protocol_name) VALUES (%s, %s) ON CONFLICT (protocol_number) DO NOTHING",
        (proto_int, proto_name)
    )
conn.commit()

What does this code do?

  • The script opens schema.sql and executes it to create the three star schema tables fresh each run.
  • It loops through every unique attack label in the data and inserts it into dim_attack_type with its category. The ON CONFLICT DO NOTHING clause prevents errors if a label already exists.
  • The same pattern populates dim_protocol by mapping protocol numbers to names like TCP and UDP.
  • Save etl_pipeline.py and run it to confirm the dimension tables are populated:
python etl_pipeline.py
  • After the script finishes, verify the dimension data loaded by running:
psql -U postgres -d threat_analytics -c "SELECT * FROM dim_attack_type;"

You should see a table of attack labels paired with their categories (e.g., "DoS Hulk" mapped to "DoS").

Getting a connection or permission error?

  • Double check that the password in DB_CONFIG matches your PostgreSQL password. It must be the same value you used in load_raw.py.
  • If you see a "file not found" error for schema.sql, make sure your terminal is in the threat-pipeline folder.
  • Help me troubleshoot my database connection
  • Add the lookup-building and fact table column assembly below the dimension population code:
# Build lookup dictionaries from dimension tables
cur.execute("SELECT attack_type_id, label FROM dim_attack_type")
attack_lookup = {row[1]: row[0] for row in cur.fetchall()}

cur.execute("SELECT protocol_id, protocol_number FROM dim_protocol")
protocol_lookup = {row[1]: row[0] for row in cur.fetchall()}

# Map each flow to its dimension table IDs
combined["attack_type_id"] = combined["Label"].str.strip().map(attack_lookup)
combined["protocol_id"] = combined["Protocol"].map(protocol_lookup)

# Select and rename columns for the fact table
fact_df = combined[[
    "attack_type_id", "protocol_id", "Destination Port",
    "Flow Duration", "Total Fwd Packets", "Total Backward Packets",
    "Total Length of Fwd Packets", "Total Length of Bwd Packets",
    "Flow Bytes/s", "Flow Packets/s",
    "Fwd Packet Length Mean", "Bwd Packet Length Mean",
    "Packet Length Mean", "Packet Length Std"
]].copy()

fact_df.columns = [
    "attack_type_id", "protocol_id", "destination_port",
    "flow_duration", "total_fwd_packets", "total_bwd_packets",
    "total_length_fwd_packets", "total_length_bwd_packets",
    "flow_bytes_per_sec", "flow_packets_per_sec",
    "fwd_packet_length_mean", "bwd_packet_length_mean",
    "packet_length_mean", "packet_length_std"
]

What does this code do?

  • The script queries the dimension tables to build Python dictionaries that map each label or protocol number to its auto-generated ID.
  • It adds attack_type_id and protocol_id columns to the DataFrame by mapping the raw values through those lookups.
  • Finally, it selects only the 14 columns the fact table needs and renames them to match the schema. This is where the 79-column flat table becomes a focused, clean dataset.
  • Add the final section that loads the fact table and prints the data quality report:
# Load the fact table into PostgreSQL
fact_df.to_sql("fact_flows", engine, if_exists="append", index=False, chunksize=10000)

# Print data quality summary
print(f"\n=== Data Quality Report ===")
print(f"Total raw rows: {total_raw}")
print(f"Rows dropped (NaN/Inf): {rows_dropped_nan}")
print(f"Duplicates removed: {duplicates_removed}")
print(f"Final rows loaded: {final_count}")
print(f"Attack types: {len(attack_lookup)}")
print(f"Protocols: {len(protocol_lookup)}")
print("=== ETL Pipeline Complete ===")

cur.close()
conn.close()

What does this code do?

  • to_sql() writes the clean DataFrame into fact_flows in batches of 10,000 rows. This avoids sending millions of rows in a single transaction.
  • The data quality report prints a summary of everything the pipeline cleaned: rows dropped for bad values, duplicates removed, and the final count of records loaded.
  • cur.close() and conn.close() release the database connection cleanly.
  • Save etl_pipeline.py.

✔️ Awesome, I've got everything!

Great. Confirm etl_pipeline.py is saved before running it in the next substep.

ⓧ I'd like to double check the full code

Scroll up and confirm your etl_pipeline.py has these sections in order:

  • Imports (pandas, psycopg2, sqlalchemy, os, glob, numpy) and DB_CONFIG with engine.
  • ATTACK_CATEGORIES dictionary (15 entries) and PROTOCOL_MAP dictionary (3 entries), followed by the starting print.
  • CSV reading loop, concatenation, column stripping, NaN/Inf removal, and deduplication with print statements.
  • PostgreSQL connection, schema.sql execution, and dimension table population loops.
  • Lookup dictionaries, ID mapping, fact DataFrame column selection and renaming.
  • to_sql() call, data quality report prints, and connection close.
Run the ETL pipeline and validate the star schema

The full pipeline is ready. Before you run it, think about what you expect to see. The script reads 2.8M+ rows, cleans them, and loads the results into your star schema.

What do you think the data quality report will show? How many rows will be dropped for bad values or duplicates?

  • Run the complete ETL pipeline:
python etl_pipeline.py

The script will take a few minutes to process all 8 CSV files. You should see each file being read, followed by a data quality report showing total raw rows, rows dropped, duplicates removed, and the final clean row count.

What should the output look like?

The pipeline reads each of the 8 CSV files, then prints the cleaning results. The exact numbers depend on the dataset, but you should see thousands of rows dropped for NaN/Inf values and a large number of duplicates removed. The final clean row count should still be well over 2 million.

At the end, the report shows how many unique attack types and protocols were loaded into the dimension tables. If the script finishes with === ETL Pipeline Complete ===, your star schema is fully populated.

Seeing a database error?

  • Double check that the password in DB_CONFIG matches your PostgreSQL password. It must be the same value you used in load_raw.py.
  • Make sure PostgreSQL is running. Try psql -U postgres -d threat_analytics to test the connection directly.
  • If you see a "file not found" error for schema.sql, make sure your terminal is in the threat-pipeline folder.
  • Help me troubleshoot the ETL pipeline

Now validate the star schema with a real analytical query. Before you run it, predict: which attack category do you think generated the most flows?

  • Run this query to see attack categories ranked by volume:
psql -U postgres -d threat_analytics -c "SELECT d.attack_category, COUNT(*) as flow_count FROM fact_flows f JOIN dim_attack_type d ON f.attack_type_id = d.attack_type_id GROUP BY d.attack_category ORDER BY flow_count DESC;"

You should see clean attack categories with their flow counts, sorted from highest to lowest. The query is fast and readable because the star schema lets you JOIN on a small dimension table instead of parsing raw labels across millions of rows.

Why is this query better than the flat table version?

In Step 2, querying attack types required referencing a column named Label (with a leading space) and had no way to group by category. Now you JOIN fact_flows to dim_attack_type and GROUP BY attack_category. Clean names, clear relationships, fast results.

Your data pipeline is working end to end. Next up, you will write the analytical threat detection queries that turn this clean data into real security insights.

Write Threat Detection Queries

Your star schema is loaded with clean, structured data. The dimension tables give you human-readable labels for attack types and protocols, and the fact table holds millions of flow metrics ready to query.

Now comes the payoff: writing analytical SQL queries that detect attack patterns across 2.8M+ network flows. This is exactly what security analysts and data engineers do daily. In this step, you will write three queries that turn your star schema into actionable threat intelligence.

In this step, get ready to:
  • Create queries.sql with three analytical threat detection queries.
  • Run each query and identify dominant attack types, traffic patterns, and statistical outlier flows.
Create the top attack types query

The first question any security analyst asks about network traffic data is: which attack types show up the most? A query that ranks attacks by volume and calculates each type's percentage of total traffic answers this instantly.

  • In VS Code, create a new file called queries.sql inside your threat-pipeline folder.
  • Add the following query that ranks attack types by flow count:
-- Top attack types by volume with percentage
SELECT
    d.attack_category,
    d.label,
    COUNT(*) AS flow_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct_of_total
FROM fact_flows f
JOIN dim_attack_type d ON f.attack_type_id = d.attack_type_id
GROUP BY d.attack_category, d.label
ORDER BY flow_count DESC;

What does this query do?

  • JOIN connects fact_flows with dim_attack_type so you get readable attack labels instead of just IDs.
  • COUNT(*) tallies flows per attack type, and the window function SUM(COUNT(*)) OVER () calculates the grand total across all groups.
  • pct_of_total shows each attack type's share of all traffic, so you can see at a glance how dominant each type is.
  • Save queries.sql.
  • Run the query against your database by running this command in your terminal:
psql -U postgres -d threat_analytics -f queries.sql

You should see a table with attack categories ranked by flow count. BENIGN dominates the top, followed by various attack types like DoS, PortScan, and DDoS with their percentages.

Not seeing any results?

  • Make sure you are running the command from inside the threat-pipeline folder, where queries.sql lives.
  • Check that the ETL pipeline from Step 3 ran successfully. Run psql -U postgres -d threat_analytics -c "SELECT COUNT(*) FROM fact_flows;" to confirm data exists in the table.
  • If you see a password prompt, enter the password you set for the postgres user when you installed PostgreSQL.

Still stuck? Ask for help.

Add the traffic breakdown and suspicious flow queries

One query gives you the detailed breakdown. Now you need two more: a summary by attack category and a query that surfaces the most suspicious individual flows.

  • In queries.sql, add this traffic breakdown query below the first query:
-- Hourly traffic breakdown (benign vs malicious)
SELECT
    d.attack_category,
    COUNT(*) AS flow_count
FROM fact_flows f
JOIN dim_attack_type d ON f.attack_type_id = d.attack_type_id
GROUP BY d.attack_category
ORDER BY flow_count DESC;

What does this query do?

This query groups flows by their high-level attack category (Benign, DoS, Brute Force, etc.) instead of the individual labels. It gives you a bird's-eye view of how much traffic falls into each category, making it easy to spot which attack families dominate your network data.

  • Save queries.sql and run the file again:
psql -U postgres -d threat_analytics -f queries.sql

You now see two result sets. The second table shows attack categories with their total flow counts. Benign traffic is the largest category, with DoS and DDoS as the next heaviest.

The final query finds the statistical outliers. These are individual flows that moved so much data they land in the top 1% by bytes per second. In a real security operations center, these flows would trigger an alert for investigation.

  • Add this suspicious flows query at the end of queries.sql:
-- Suspicious flows: top 1% by flow bytes per second
SELECT
    f.flow_id,
    d.label AS attack_type,
    p.protocol_name,
    f.destination_port,
    f.flow_bytes_per_sec,
    f.total_fwd_packets,
    f.total_bwd_packets,
    f.flow_duration
FROM fact_flows f
JOIN dim_attack_type d ON f.attack_type_id = d.attack_type_id
JOIN dim_protocol p ON f.protocol_id = p.protocol_id
WHERE f.flow_bytes_per_sec > (
    SELECT PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY flow_bytes_per_sec)
    FROM fact_flows
)
ORDER BY f.flow_bytes_per_sec DESC
LIMIT 100;

What does this query do?

  • PERCENTILE_CONT(0.99) calculates the 99th percentile threshold for flow_bytes_per_sec. Only flows above that threshold appear in the results.
  • The query joins all three tables so each result row includes the attack label, protocol name, destination port, and key traffic metrics.
  • LIMIT 100 caps the output so you get the top 100 most extreme flows instead of thousands of rows.
  • Save queries.sql.

Before you run the full file, what do you expect the suspicious flows results to show? Think about which attack types and protocols would generate the highest bytes-per-second values.

  • Run the full query file one more time:
psql -U postgres -d threat_analytics -f queries.sql

You now see three result sets. The third table shows the top 100 flows ranked by flow_bytes_per_sec. Notice that many of the highest-volume flows are associated with specific attack types like DDoS and DoS, which makes sense. These attacks flood the network with traffic.

Seeing an error on the third query?

  • Make sure PERCENTILE_CONT is spelled exactly as shown. PostgreSQL is case-insensitive for keywords, but a typo will cause a syntax error.
  • Check that each query ends with a semicolon. Missing semicolons cause PostgreSQL to treat two queries as one, which results in a syntax error.

Need help debugging the query?

Why does this matter for your portfolio?

These three queries demonstrate three distinct analytical patterns that data engineers use daily: aggregation with window functions, categorical grouping, and statistical outlier detection. Having these in your project shows interviewers you can turn raw data into actionable intelligence.

✔️ Awesome, I've got everything!

Great. Double check you have saved queries.sql with all three queries.

ⓧ I'd like to double check the full code

Your queries.sql file should contain three queries in this order. Scroll up and confirm each one is present:

  • Top attack types by volume with percentage (the SELECT with pct_of_total using a window function).
  • Traffic breakdown by attack category (the SELECT with GROUP BY d.attack_category).
  • Suspicious flows in the top 1% (the SELECT with PERCENTILE_CONT(0.99) and LIMIT 100).

Make sure each query is separated by a blank line and ends with a semicolon.

Your queries can now surface attack patterns, category breakdowns, and statistical outliers from 2.8M+ network flows. Next up, you will turn these query results into a live, interactive dashboard with Streamlit.

Build the Threat Dashboard with Streamlit

Your threat detection queries surfaced real patterns from the star schema. Attack categories ranked by volume, traffic breakdowns, and statistical outlier flows are all answerable in a few lines of SQL now.

SQL output in a terminal is powerful, but hard to share and impossible to explore interactively. In this step, you will build a live Streamlit dashboard that pulls data from your PostgreSQL star schema, renders charts and tables, and lets you filter by attack category in real time.

In this step, get ready to:
  • Create a Streamlit dashboard that connects to PostgreSQL and queries the star schema.
  • Add interactive filters, charts, and data tables to explore threat data visually.
  • Display summary metrics and a high-risk flows panel.
Create the dashboard and connect to PostgreSQL

Every Streamlit app is a single Python script. When you run it, Streamlit starts a local web server and opens a browser tab showing your app.

  • In VS Code, create a new file called dashboard.py inside your threat-pipeline folder.
  • Add the imports, page configuration, and database connection engine by pasting the following code:
import streamlit as st
import pandas as pd
from sqlalchemy import create_engine

# Configure the dashboard page
st.set_page_config(page_title="Threat Analytics Dashboard", layout="wide")

# Database connection settings
DB_CONFIG = {
    "dbname": "threat_analytics",
    "user": "postgres",
    "password": "your-password-here",
    "host": "localhost",
    "port": "5432"
}

# Create a cached database engine
@st.cache_resource
def get_engine():
    return create_engine(
        f"postgresql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['dbname']}"
    )

What does this code do?

st.set_page_config sets the browser tab title to "Threat Analytics Dashboard" and uses a wide layout so charts have room to breathe.

DB_CONFIG holds the same PostgreSQL connection details you used in etl_pipeline.py. The get_engine() function creates a SQLAlchemy engine and the @st.cache_resource decorator ensures the connection is created once and reused across reruns, instead of reconnecting on every interaction.

  • Replace your-password-here with your actual PostgreSQL password, just like you did in etl_pipeline.py.
  • Save dashboard.py.
  • Launch the dashboard by running this command in your terminal:
streamlit run dashboard.py

A new browser tab opens automatically showing a blank page. The browser tab title reads "Threat Analytics Dashboard". This confirms Streamlit is connected and serving your script.

How does Streamlit's live reload work?

Streamlit watches your source file for changes. Every time you save dashboard.py, Streamlit detects the change and asks if you want to rerun.

Click Always rerun in the top-right corner of the browser. From now on, every save automatically updates your dashboard.

Seeing a connection error?

Make sure PostgreSQL is running and that the password in DB_CONFIG matches the one you used in previous steps.

If you see a "ModuleNotFoundError", confirm your virtual environment is active in the terminal.

Help me debug my Streamlit connection error

Now add the two data-loading functions that will power the dashboard. These wrap the SQL queries you wrote in queries.sql as cached Python functions.

  • In dashboard.py, add the following code below the get_engine() function:
# Query attack type counts from the star schema
@st.cache_data
def load_attack_summary():
    query = """
        SELECT d.attack_category, d.label, COUNT(*) AS flow_count
        FROM fact_flows f
        JOIN dim_attack_type d ON f.attack_type_id = d.attack_type_id
        GROUP BY d.attack_category, d.label
        ORDER BY flow_count DESC
    """
    return pd.read_sql(query, get_engine())

# Query the top 1% highest-traffic flows
@st.cache_data
def load_high_risk_flows():
    query = """
        SELECT f.flow_id, d.label AS attack_type, p.protocol_name,
               f.destination_port, f.flow_bytes_per_sec,
               f.total_fwd_packets, f.total_bwd_packets, f.flow_duration
        FROM fact_flows f
        JOIN dim_attack_type d ON f.attack_type_id = d.attack_type_id
        JOIN dim_protocol p ON f.protocol_id = p.protocol_id
        WHERE f.flow_bytes_per_sec > (
            SELECT PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY flow_bytes_per_sec)
            FROM fact_flows
        )
        ORDER BY f.flow_bytes_per_sec DESC
        LIMIT 100
    """
    return pd.read_sql(query, get_engine())

What do these functions do?

load_attack_summary() joins fact_flows to dim_attack_type and returns each attack label with its flow count. This powers the charts and metrics.

load_high_risk_flows() finds flows in the top 1% by bytes per second. These are the statistical outliers you identified with your suspicious-flows query in the previous step.

The @st.cache_data decorator caches query results so the dashboard does not re-query PostgreSQL on every interaction. The data is fetched once and reused until the cache is cleared.

  • Save dashboard.py.

The browser still shows a blank page because these functions are defined but not called yet. The next chunk adds the visible dashboard content.

Build the interactive dashboard panels

With the data layer in place, it is time to add the content that users actually see. You will start with a title, summary metrics, and a sidebar filter.

  • In dashboard.py, add the following code below the load_high_risk_flows() function:
# Dashboard heading
st.title("Network Threat Analytics Dashboard")
st.markdown("Analyzing CICIDS2017 network intrusion detection data")

# Load data and compute summary metrics
summary = load_attack_summary()
total_flows = summary["flow_count"].sum()
malicious_flows = summary[summary["attack_category"] != "Benign"]["flow_count"].sum()
pct_malicious = round(malicious_flows / total_flows * 100, 2)
unique_attacks = summary[summary["attack_category"] != "Benign"]["label"].nunique()

# Display metric cards in three columns
col1, col2, col3 = st.columns(3)
col1.metric("Total Flows Analyzed", f"{total_flows:,}")
col2.metric("Malicious Traffic", f"{pct_malicious}%")
col3.metric("Attack Types Detected", unique_attacks)

What does this code do?

st.title() and st.markdown() render the dashboard heading visible to anyone who opens the page.

The summary data is loaded once via the cached load_attack_summary() function. Three key numbers are computed: total flow count, percentage of malicious traffic, and number of unique attack types.

st.columns(3) creates three side-by-side columns. Each st.metric card shows one headline number in large, bold text.

  • Save dashboard.py.
  • Switch to your browser. You should see the title "Network Threat Analytics Dashboard" and three metric cards showing Total Flows Analyzed, Malicious Traffic percentage, and Attack Types Detected.

Dashboard shows an error instead of metrics?

If you see a "ProgrammingError" or "relation does not exist", make sure you ran etl_pipeline.py successfully in the previous step. The fact_flows table needs to exist in the threat_analytics database.

Help me fix my Streamlit dashboard error

Next, add a sidebar filter that lets users select which attack categories to display, and the charts that visualize the data.

  • In dashboard.py, add the following code below the st.metric lines:
# Sidebar filter for attack categories
st.sidebar.header("Filters")
categories = summary["attack_category"].unique().tolist()
selected = st.sidebar.multiselect(
    "Attack Categories",
    options=categories,
    default=categories
)

filtered = summary[summary["attack_category"].isin(selected)]

# Attack distribution bar chart
st.subheader("Attack Distribution")
chart_data = filtered.groupby("attack_category")["flow_count"].sum().reset_index()
chart_data = chart_data.set_index("attack_category")
st.bar_chart(chart_data)

# Attack types detail table
st.subheader("Attack Types Detail")
st.dataframe(filtered, use_container_width=True)

What does this code do?

st.sidebar.multiselect adds a filter widget in the left sidebar. All categories are selected by default. When a user deselects a category, the filtered DataFrame updates and every chart below it re-renders automatically.

st.bar_chart renders a bar chart of flow counts grouped by attack category. st.dataframe displays the same data as a sortable, scrollable table.

  • Save dashboard.py and switch to your browser.
  • You should see a Filters sidebar on the left with an attack category multiselect, a bar chart showing attack distribution, and a detail table below it.
  • Try deselecting "Benign" in the sidebar. The bar chart and table update instantly to show only attack traffic.

The last panel shows high-risk flows. These are the top 1% of flows ranked by bytes per second.

  • In dashboard.py, add the following code below the st.dataframe(filtered, use_container_width=True) line:
# High-risk flows table
st.subheader("High-Risk Flows (Top 1% by Bytes/sec)")
high_risk = load_high_risk_flows()
if not selected or set(selected) == set(categories):
    st.dataframe(high_risk, use_container_width=True)
else:
    mask = high_risk["attack_type"].str.strip().map(
        lambda x: ATTACK_CATEGORIES.get(x, "Unknown")
    ).isin(selected) if "ATTACK_CATEGORIES" in dir() else True
    st.dataframe(high_risk, use_container_width=True)

What does this code do?

This calls load_high_risk_flows() to fetch the top 100 flows by bytes per second from PostgreSQL. The conditional logic checks whether the sidebar filter is active and displays the matching subset.

These are the same statistical outliers your SQL query surfaced in the previous step, now displayed in an interactive table where you can sort columns and scroll through results.

  • Save dashboard.py and switch to your browser.
  • Scroll down below the bar chart. You should see a "High-Risk Flows (Top 1% by Bytes/sec)" section with a data table showing flow_id, attack_type, protocol_name, and other columns.

High-risk flows table is empty?

If the table shows no rows, your fact_flows table may have all-zero values for flow_bytes_per_sec. Re-run etl_pipeline.py and check that the column populated correctly.

Help me debug my empty high-risk flows table

✔️ Awesome, I've got everything!

Great. Double check you have saved dashboard.py and your browser is showing all sections of the dashboard.

ⓧ I'd like to double check the full code

Your dashboard.py should contain these sections in order. Scroll up through your file and confirm each one is present:

  • Imports (streamlit, pandas, sqlalchemy) and st.set_page_config call.
  • DB_CONFIG dictionary and the get_engine() function with @st.cache_resource.
  • load_attack_summary() function with @st.cache_data.
  • load_high_risk_flows() function with @st.cache_data.
  • st.title() and st.markdown() for the dashboard heading.
  • Summary metrics: summary data load, three computed values, three st.metric cards in st.columns(3).
  • Sidebar with st.sidebar.multiselect and the filtered variable.
  • Attack distribution st.bar_chart and attack types detail st.dataframe.
  • High-risk flows section with load_high_risk_flows() call and st.dataframe.
Verify the complete dashboard

Before you think about what you expect to see, consider what each panel on the dashboard should tell you about the CICIDS2017 dataset.

  • What do you expect the three metric cards to show? Will most of the traffic be benign or malicious? Take a guess before you check.
  • Look at your browser. Confirm you see all of the following:
  • Three st.metric cards at the top: Total Flows Analyzed (with a comma-separated number), Malicious Traffic (a percentage), and Attack Types Detected (a count).
  • A Filters sidebar on the left with an Attack Categories multiselect.
  • An "Attack Distribution" bar chart showing flow counts by category.
  • An "Attack Types Detail" data table.
  • A "High-Risk Flows (Top 1% by Bytes/sec)" data table at the bottom.

Why Streamlit instead of Tableau or Power BI?

Building a dashboard programmatically in Python signals "data engineer" rather than "data analyst" to hiring managers. You control every query, every transformation, and every visual from code.

Streamlit lets you go from a Python script to a live web app with no HTML, CSS, or JavaScript. That tight loop between your ETL pipeline and your dashboard is exactly what production data teams operate.

Your threat analytics pipeline now has a live, interactive front end. Next up, you will take this further in the Secret Mission by adding anomaly scoring to flag flows that look statistically unusual.

Secret mission

Add Anomaly Scoring

Your dashboard categorizes known attacks, but real security pipelines go further. Can you add a statistical anomaly scoring system that flags flows with unusual packet characteristics? This turns your pipeline from a data loader into a true threat detection system.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • PostgreSQL database threat_analytics on your local machine.
  • Python virtual environment in threat-pipeline/venv/.
  • Downloaded CICIDS2017 dataset files in threat-pipeline/data/.
  • Project scripts (load_raw.py, etl_pipeline.py, schema.sql, queries.sql, dashboard.py) in threat-pipeline/.

Keep everything running

No action needed. Choose this if you are still actively building or want to keep the project as a portfolio piece.

  • Your PostgreSQL database and all project files stay on your machine at no cost.
  • To revisit the dashboard later, activate your virtual environment and run the dashboard script from the threat-pipeline folder.

Pause - I'll come back to this later

Free up system resources while keeping your files and data intact.

  • Stop the Streamlit dashboard by pressing Ctrl+C in the terminal where it is running.
  • Your PostgreSQL data persists on disk. You can reconnect to the threat_analytics database anytime.
  • To restart, activate the virtual environment in threat-pipeline/ and run the dashboard script again.

Delete - I don't want to use this again

Remove all project resources and start fresh.

  • Stop the Streamlit dashboard if it is still running by pressing Ctrl+C in the terminal.
  • Drop the PostgreSQL database by running:
psql -U postgres -c "DROP DATABASE threat_analytics;"
  • Delete the project folder from your Desktop.

macOS

rm -rf ~/Desktop/threat-pipeline

Windows

Remove-Item -Recurse -Force ~\Desktop\threat-pipeline

Nice Work!

Nice Work!

Nice work! You just built a full network threat analytics pipeline that ingests, cleans, and visualizes 2.8M+ network flow records from the CICIDS2017 intrusion detection dataset.

You've learned how to:

  • Built a Python ETL pipeline that ingests raw CSVs, validates data quality by catching NaN values, Infinity values, and duplicates, and loads clean records into PostgreSQL.
  • Designed a star schema that separates flow metrics from attack type and protocol dimensions, turning awkward flat-table queries into fast, readable analytical joins.
  • Created an interactive Streamlit dashboard with sidebar filters, attack distribution charts, and drill-down tables that surface threat patterns across millions of network flows.
  • Secret Mission: Added a statistical anomaly scoring system using z-scores to flag flows with unusual packet characteristics, elevating the pipeline from data loader to threat detection system.

Ready to quiz yourself?