Orchestrate a Security Log ETL Pipeline

Containerize a Python ETL pipeline and orchestrate it with Apache Airflow.

Introduction

30 Second Summary

Security teams deal with thousands of log events every day. When your script for processing those logs only runs when you remember to trigger it manually, critical threats can slip through unnoticed.

In this project, you will package a Python ETL pipeline into a Docker container. You will then orchestrate it with Apache Airflow, triggering and monitoring each step from a web dashboard.

What You'll Build

You trigger a run from the Airflow dashboard, watch three tasks cascade to green across the graph view, and open the output CSV to see every security event classified by severity.

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

  • A Docker container you can run anywhere to process security logs identically, proving your ETL script is portable and reproducible.
  • A live Airflow dashboard where you trigger pipeline runs on demand, watch each task's status update in real time, and inspect detailed logs.
  • A three-task DAG (extract, transform, load) that lights up green across the Airflow graph view when you trigger it.
  • Processed security log output accessible on your host machine, with every event classified by severity.
  • Secret Mission: Add automatic scheduling every 6 hours and retry logic that recovers from transient failures.

Are there any prerequisites?

This project builds on the Security Log ETL Pipeline with NXLog project. Basic Python knowledge is also helpful.

Before We Start

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

Install and Verify Docker Desktop

Your ETL script from Project 1 runs directly on your Windows machine. It depends on your specific Python version, installed packages, and file paths.

Docker solves this by packaging your code and its dependencies into an isolated environment called a container. In this step, you will install Docker Desktop and confirm the Docker Engine is running correctly.

In this step, get ready to:
  • Install Docker Desktop for Windows with the WSL 2 backend.
  • Verify Docker and Docker Compose are working from PowerShell.
  • Run a test container to confirm the Docker Engine works.
Download and install Docker Desktop

Docker Desktop is the application that runs the Docker Engine on your machine. It provides everything you need to build and run containers locally.

What is Docker?

Docker packages an application along with its dependencies into a container. A container is a lightweight, isolated environment that runs the same way on any machine.

This means your ETL pipeline will produce identical results whether it runs on your laptop, a teammate's computer, or a cloud server.

  • The installer asks which installation mode you prefer. Choose the per-user option, which is selected by default.
  • Leave the WSL 2 backend option checked. The installer selects it by default.
  • Follow the remaining prompts to complete the installation.
  • Restart your computer if the installer asks you to.

What is WSL 2?

WSL 2 (Windows Subsystem for Linux) runs a real Linux kernel on your Windows machine. Docker uses it to run Linux containers natively, without a traditional virtual machine.

Once the installation finishes, open Docker Desktop.

  • Press the Windows key to open your search bar.
  • Type Docker Desktop and press Enter to open it.

The whale icon in your system tray animates while the Docker Engine starts. Wait for it to stop animating before continuing.

Docker Desktop won't start?

If you see a message about WSL not being installed or enabled, open PowerShell as Administrator and run wsl --install. Restart your computer after it finishes, then open Docker Desktop again.

If the engine stays stuck on starting, make sure hardware virtualization is enabled in your BIOS/UEFI settings.

Help me troubleshoot Docker Desktop not starting on Windows

Verify Docker and Docker Compose in PowerShell
  • Press the Windows key to open your search bar.
  • Type PowerShell and press Enter to open a terminal.
  • Confirm Docker is installed by running this command:
docker --version
  • Confirm Docker Compose is installed by running this command:
docker compose version

What is Docker Compose?

Docker Compose is a tool for running multi-container applications with a single command. You will use it in the next step to launch Apache Airflow, which needs several containers working together.

✔️ I see version numbers for both

Docker and Docker Compose are installed and ready. You are all set to continue.

ⓧ Command not found or error

Make sure Docker Desktop is open and the engine has finished starting. The whale icon in your system tray should be stable, not animating.

If Docker Desktop is running but the commands still fail, close PowerShell and reopen it. New system PATH entries sometimes require a fresh terminal session.

Before you run the next command, what do you think will happen when Docker tries to run a container image that does not exist on your machine yet?

  • Test the Docker Engine by running this command:
docker run --rm hello-world

Docker automatically pulls (downloads) the hello-world image, then runs it. You should see Hello from Docker! printed in your terminal.

What does this command do?

docker run creates and starts a container from an image. The --rm flag automatically removes the container after it finishes, keeping your system clean.

Don't see 'Hello from Docker!'?

If you see a permissions error, close PowerShell and reopen it. Docker Desktop sometimes needs a moment to register after a fresh install.

If you see a network error, check that your internet connection is active. Docker needs to download the hello-world image the first time you run this command.

Help me troubleshoot docker run hello-world on Windows

Docker is up and running on your machine. Next up, you will package your Python ETL script into a Docker image and run it as a container.

Build and Run Your ETL Container

Docker is installed and ready to go. Now it is time to put it to work.

Your ETL script from the NXLog project runs directly on your machine, tied to your specific Python version and file paths. Before adding orchestration with Apache Airflow, you need to prove the pipeline works inside a Docker container. This step packages your Python script and sample data into a Docker image and runs it, giving you an immediate visible result.

In this step, get ready to:
  • Create a project folder with sample security log data, a Python ETL script, and a Dockerfile.
  • Build a Docker image and run it with volume mounts.
  • Verify the processed output CSV on your host machine.
Create the project folder and sample data

Every Docker project starts with a folder that holds the code and configuration Docker needs to build an image. You will create one on your Desktop so it is easy to find.

  • Open a PowerShell terminal by pressing the Windows key, typing PowerShell, and pressing Enter.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir security-etl-docker
cd security-etl-docker
  • Create a data subdirectory to hold your sample log file by running:
mkdir data
  • Open this folder in VS Code by running:
code .

What does code . do?

The code . command tells VS Code to open the current directory as a project. The dot (.) means "this folder."

Your sample data is a set of Windows Event Log entries in the same JSON format that NXLog produces. Each entry contains an event ID, timestamp, hostname, and account name.

  • In the VS Code Explorer sidebar, expand the data folder.
  • Right-click inside the data folder and select New File.
  • Name the file sample_logs.json and paste the following content:
[
  {"EventTime": "2026-08-27T10:15:32Z", "Hostname": "WORKSTATION-01", "EventID": 4624, "Channel": "Security", "EventType": "AUDIT_SUCCESS", "AccountName": "jsmith", "LogonType": 10, "SourceIP": "192.168.1.50"},
  {"EventTime": "2026-08-27T10:16:45Z", "Hostname": "WORKSTATION-01", "EventID": 4625, "Channel": "Security", "EventType": "AUDIT_FAILURE", "AccountName": "admin", "LogonType": 3, "SourceIP": "10.0.0.99"},
  {"EventTime": "2026-08-27T10:18:03Z", "Hostname": "SERVER-DC01", "EventID": 4672, "Channel": "Security", "EventType": "AUDIT_SUCCESS", "AccountName": "svc_backup", "LogonType": 5, "SourceIP": ""},
  {"EventTime": "2026-08-27T10:20:11Z", "Hostname": "WORKSTATION-02", "EventID": 4625, "Channel": "Security", "EventType": "AUDIT_FAILURE", "AccountName": "root", "LogonType": 10, "SourceIP": "203.0.113.42"},
  {"EventTime": "2026-08-27T10:21:30Z", "Hostname": "WORKSTATION-01", "EventID": 4624, "Channel": "Security", "EventType": "AUDIT_SUCCESS", "AccountName": "jsmith", "LogonType": 2, "SourceIP": ""},
  {"EventTime": "2026-08-27T10:25:00Z", "Hostname": "SERVER-DC01", "EventID": 4625, "Channel": "Security", "EventType": "AUDIT_FAILURE", "AccountName": "administrator", "LogonType": 3, "SourceIP": "198.51.100.7"},
  {"EventTime": "2026-08-27T10:30:15Z", "Hostname": "WORKSTATION-03", "EventID": 4624, "Channel": "Security", "EventType": "AUDIT_SUCCESS", "AccountName": "mgarcia", "LogonType": 2, "SourceIP": ""},
  {"EventTime": "2026-08-27T10:32:44Z", "Hostname": "SERVER-DC01", "EventID": 4672, "Channel": "Security", "EventType": "AUDIT_SUCCESS", "AccountName": "domain_admin", "LogonType": 3, "SourceIP": "192.168.1.10"}
]

What is this data?

This JSON contains 8 Windows Security Event Log entries. Three event IDs appear in the data:

  • 4624 is a successful logon.
  • 4625 is a failed logon attempt.
  • 4672 is a special privilege assignment (like an admin logging in).
  • Save sample_logs.json with Ctrl+S.
  • Confirm the file exists by switching to your PowerShell terminal and running:
ls data

You should see sample_logs.json listed in the output.

Don't see sample_logs.json?

  • Make sure you created the file inside the data subdirectory, not in the project root.
  • Check that you saved the file in VS Code (Ctrl+S).

Still stuck? Help me find my sample_logs.json file.

Write the ETL pipeline script

The ETL script has three functions that form a pipeline: extract reads raw logs, transform classifies each event by severity, and load writes the results to a CSV file.

  • In VS Code, right-click in the Explorer sidebar (in the project root, not inside data) and select New File.
  • Name the file etl_pipeline.py.
  • Paste the following imports and extract function:
import json
import csv
import os


def extract(input_path):
    """Extract raw security logs from JSON file."""
    with open(input_path, "r") as f:
        raw_logs = json.load(f)
    print(f"Extracted {len(raw_logs)} log events")
    return raw_logs

What does this code do?

  • The three imports bring in Python's built-in modules for JSON parsing, CSV writing, and file system operations.
  • The extract function opens a JSON file, loads all the log entries into a Python list, and prints how many events it found.
  • Below the extract function, add the transform function:
def transform(raw_logs):
    """Transform logs: enrich with severity classification."""
    processed = []
    for log in raw_logs:
        severity = "CRITICAL" if log["EventID"] == 4625 else "INFO"
        if log["EventID"] == 4672:
            severity = "HIGH"
        processed.append({
            "timestamp": log["EventTime"],
            "hostname": log["Hostname"],
            "event_id": log["EventID"],
            "event_type": log["EventType"],
            "account": log["AccountName"],
            "logon_type": log["LogonType"],
            "source_ip": log["SourceIP"] if log["SourceIP"] else "LOCAL",
            "severity": severity,
        })

    failed_logons = [e for e in processed if e["event_id"] == 4625]
    print(f"Transformed {len(processed)} events, found {len(failed_logons)} failed logons")
    return processed

What does the transform function do?

This is the core analysis logic. It loops through every raw log entry and assigns a severity level based on the event ID.

  • Event 4625 (failed logon) gets CRITICAL severity because failed logons can signal brute-force attacks.
  • Event 4672 (special privileges) gets HIGH severity because privilege escalation is a key indicator of compromise.
  • Empty SourceIP values are replaced with LOCAL to indicate local logins.
  • Save etl_pipeline.py with Ctrl+S.

Scroll through the file in VS Code. You should see the three imports at the top, followed by the extract and transform functions.

  • Below the transform function, add the load function and the main entry point:
def load(processed_logs, output_path):
    """Load processed logs to CSV."""
    os.makedirs(os.path.dirname(output_path), exist_ok=True)
    fieldnames = [
        "timestamp", "hostname", "event_id", "event_type",
        "account", "logon_type", "source_ip", "severity",
    ]
    with open(output_path, "w", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(processed_logs)
    print(f"Loaded {len(processed_logs)} events to {output_path}")


if __name__ == "__main__":
    input_path = "/app/data/sample_logs.json"
    output_path = "/app/output/processed_logs.csv"

    raw = extract(input_path)
    processed = transform(raw)
    load(processed, output_path)
    print("ETL pipeline complete!")

What does this code do?

  • The load function creates the output directory if it does not exist, then writes all processed events to a CSV file with a header row.
  • The if __name__ == "__main__" block is the entry point. It chains the three functions together: extract, transform, then load.
  • The paths /app/data/ and /app/output/ are container paths. Docker volume mounts will map your local folders to these paths inside the container.
  • Save etl_pipeline.py.

Now you need a Dockerfile to tell Docker how to package this script into an image.

  • In the VS Code Explorer sidebar, right-click in the project root and select New File.
  • Name the file Dockerfile (no file extension) and paste the following:
FROM python:3.12-slim

WORKDIR /app

COPY etl_pipeline.py .

CMD ["python", "etl_pipeline.py"]

What does this Dockerfile do?

  • FROM python:3.12-slim starts from a lightweight official Python image. The slim variant strips out extras to keep the image small.
  • WORKDIR /app sets the working directory inside the container.
  • COPY etl_pipeline.py . copies your script into the container at /app/etl_pipeline.py.
  • CMD ["python", "etl_pipeline.py"] tells Docker to run the script when the container starts.
  • Save Dockerfile.
  • Confirm all your project files exist by running this in your PowerShell terminal:
ls

You should see data, Dockerfile, and etl_pipeline.py listed.

✔️ Awesome, I've got everything!

Great. Make sure you have saved all three files before moving on.

ⓧ I'd like to double check the full code

Your etl_pipeline.py should have three sections in order: the imports and extract function, then the transform function, then the load function and the if __name__ block. Scroll through the file and confirm each section is present.

Your Dockerfile should look like this:

FROM python:3.12-slim

WORKDIR /app

COPY etl_pipeline.py .

CMD ["python", "etl_pipeline.py"]
Build the Docker image and run the container

With all three files in place, you can now build a Docker image and run it. The image packages your script into a portable unit that works the same on any machine with Docker installed.

  • Build the Docker image by running this command in your PowerShell terminal:
docker build -t security-etl .

What does this command do?

  • docker build reads your Dockerfile and creates an image.
  • -t security-etl tags (names) the image so you can reference it later.
  • The dot (.) tells Docker to use the current directory as the build context. Docker looks for the Dockerfile and any files it references (like etl_pipeline.py) in this directory.

You should see Docker download the python:3.12-slim base image (this takes a minute the first time) and then copy your script into the new image.

Seeing a "Cannot connect to the Docker daemon" error?

  • Make sure Docker Desktop is running. Check for the whale icon in your system tray.
  • If Docker Desktop just started, wait 30 seconds for the engine to fully initialize before retrying.

Still stuck? Help me fix my Docker build error.

The image is built. Before you run it, think about what will happen. The script reads from /app/data/sample_logs.json and writes to /app/output/processed_logs.csv. But the data file is on your host machine, not inside the image. You need volume mounts to bridge the gap.

  • Run the container with volume mounts by running this command:
docker run --rm -v "${PWD}/data:/app/data" -v "${PWD}/output:/app/output" security-etl

What does this command do?

  • docker run starts a new container from the security-etl image.
  • --rm automatically deletes the container after it finishes. The image stays.
  • -v "${PWD}/data:/app/data" maps your local data folder to /app/data inside the container. This is how the script finds sample_logs.json.
  • -v "${PWD}/output:/app/output" maps a local output folder to /app/output inside the container. Docker creates this folder on your host automatically.

Volume mounts let a container read and write files on your host machine. Without them, files created inside a container disappear when the container stops.

You should see output like this in your terminal:

Extracted 8 log events
Transformed 8 events, found 3 failed logons
Loaded 8 events to /app/output/processed_logs.csv
ETL pipeline complete!

Seeing a path or permission error?

  • Make sure you are running the command from inside the security-etl-docker folder. Run pwd to check.
  • Confirm your data/sample_logs.json file exists by running ls data.
  • If you see a Docker Desktop prompt asking to share the folder, click Share it.

Still stuck? Help me fix my Docker volume mount error.

What do you expect to find in the output folder on your host machine now?

  • Open the output/processed_logs.csv file in VS Code by clicking on it in the Explorer sidebar.

You should see 8 rows of processed security events (plus a header row) with columns: timestamp, hostname, event_id, event_type, account, logon_type, source_ip, and severity. Three events have CRITICAL severity (the failed logons), two have HIGH (the privilege escalations), and three have INFO.

Why does this matter?

You just ran a Python script inside a Docker container without installing Python on your host machine. The python:3.12-slim image provided the runtime, and volume mounts let the container read your data and write results back to your host.

Anyone with Docker can run this exact same image and get identical results. That is the core promise of containerization.

Your ETL pipeline works inside a container. Next up, you will spin up Apache Airflow to orchestrate this pipeline with scheduling, monitoring, and a web dashboard.

Launch Apache Airflow with Docker Compose

In Step 2, you proved your ETL pipeline works inside a Docker container. You ran it, saw the output CSV, and confirmed the pipeline processes all 8 events correctly.

Your containerized ETL runs once and exits. In production, a data pipeline needs scheduling, monitoring, retry logic, and a dashboard so you can see what happened when things go wrong. Apache Airflow provides all of this, and its official Docker Compose setup spins up the entire platform in one command.

In this step, get ready to:
  • Download the official Airflow Docker Compose file and prepare the project directory.
  • Initialize the Airflow database and create an admin account.
  • Start all Airflow services and log into the web dashboard.
Create the Airflow project directory

Docker Compose lets you define and run multi-container applications with a single YAML file. Airflow needs several services running together: a scheduler, a worker, an API server, a PostgreSQL database, and a Redis message broker. The official Airflow Docker Compose file defines all of these for you.

  • Navigate to your Desktop and create a new project folder by running these commands:
cd ~/Desktop
mkdir airflow-etl
cd airflow-etl
  • Download the official Airflow 3.3.1 Docker Compose file by running this command:
curl.exe -LfO "https://airflow.apache.org/docs/apache-airflow/3.3.1/docker-compose.yaml"
  • Confirm the file downloaded by running:
ls

You should see docker-compose.yaml listed in the output.

What is Docker Compose?

Docker Compose is a tool for defining multi-container applications in a single YAML file. Instead of running each container manually with separate docker run commands, you describe all the services, networks, and volumes in one docker-compose.yaml file and start everything with a single command.

Airflow uses seven services that all need to talk to each other. Docker Compose handles the wiring so you can focus on building your pipeline.

  • Create the required subdirectories and the environment file by running these commands:
mkdir dags, logs, plugins, config
Set-Content .env "AIRFLOW_UID=50000"
  • Confirm everything is in place by running:
ls

You should see docker-compose.yaml, the .env file, and four folders: config, dags, logs, and plugins.

Why set AIRFLOW_UID?

The AIRFLOW_UID environment variable tells the Airflow containers which user ID to run as. Setting it to 50000 prevents a permissions warning when you initialize the database. On Windows with WSL 2, this value works without side effects.

Not seeing the .env file?

Files starting with a dot are hidden by default in some file explorers. Running ls in PowerShell will show them. If .env is missing, re-run the Set-Content command above.

Help me troubleshoot my Airflow project directory setup.

Initialize the Airflow database

Before Airflow can run, it needs a metadata database to track your pipelines, task statuses, and run history. The initialization command sets up this database and creates the default admin account.

Memory requirements

Airflow's containers need at least 4 GB of memory. Most modern Windows machines with 8 GB or more of RAM handle this fine. If your machine has limited RAM, close other heavy applications before proceeding.

  • Initialize the Airflow database by running this command:
docker compose up airflow-init

This command downloads several Docker images and sets up the database. It takes 1-3 minutes depending on your internet speed.

What should I see?

You will see a stream of log messages as Docker pulls images and Airflow configures its database. Look for the final line that contains airflow-init-1 exited with code 0. This confirms the database is ready and the admin account was created successfully.

Seeing errors during initialization?

If you see an error about ports already in use, another application may be running on port 8080. Close that application and try again.

If the command fails with a timeout or network error, check that Docker Desktop is running by looking for the whale icon in your system tray.

Help me troubleshoot Airflow database initialization errors.

Start Airflow and log into the web dashboard

The database is initialized. Now you can start all of Airflow's services in the background.

  • Start all Airflow services in detached mode by running this command:
docker compose up -d

The -d flag runs the containers in the background so you get your terminal back.

  • Wait about 30 seconds for all services to start, then check their health by running:
docker ps

You should see seven running containers: the scheduler, worker, API server, triggerer, dag-processor, a PostgreSQL database, and a Redis instance. Look for (healthy) in the status column. If some containers still show (health: starting), wait another 30 seconds and run docker ps again.

Why so many containers?

Airflow separates its responsibilities into independent services. The scheduler decides when tasks run. The worker executes them. The API server powers the web dashboard. The dag-processor reads your DAG files. PostgreSQL stores metadata. Redis handles message queuing between services.

This architecture mirrors how production Airflow deployments work. You are learning the real thing, not a simplified version.

Before you open the dashboard, what do you think you will see at http://localhost:8080? Will there be any pipelines listed, or will it be empty?

  • Open your web browser.
  • Navigate to http://localhost:8080.
  • Enter airflow as the username.
  • Enter airflow as the password.
  • Click Sign In.

You should see the Airflow dashboard with a list of example DAGs. These are sample pipelines that Airflow includes by default. Your own pipeline will appear here after you create it in the next step.

Can't reach the Airflow dashboard?

If the page does not load, the API server container may still be starting. Run docker ps and check that all containers show (healthy). If not, wait another minute and refresh the page.

If you see a connection refused error, verify that Docker Desktop is still running and that no other application is using port 8080.

Help me troubleshoot the Airflow web dashboard not loading.

Airflow is alive and running. Next up, you will write your first DAG to translate your ETL pipeline into three orchestrated tasks that Airflow can discover and execute.

Write Your First Airflow DAG

Your Airflow dashboard is live, but every DAG you can see is a built-in example. To orchestrate your own ETL pipeline, you need to give Airflow a Python file that describes which tasks to run and in what order.

Airflow uses the TaskFlow API to let you write plain Python functions decorated with @task. Airflow handles data passing between tasks automatically, so you can focus on the ETL logic you already know.

In this step, get ready to:
  • Create a DAG file using the @dag and @task decorators.
  • Define three task functions: extract, transform, and load.
  • Verify the DAG appears in the Airflow web dashboard with three connected tasks.
Create the DAG file with the extract task

A DAG (Directed Acyclic Graph) is Airflow's blueprint for a workflow. It defines which tasks run and in what order, without allowing circular dependencies.

What is a DAG?

Think of a DAG as a flowchart with strict rules. Each box is a task (a Python function), and the arrows between them define the execution order. "Acyclic" means no task can loop back to a previous one.

Airflow's dag-processor scans the dags/ directory for Python files containing DAG definitions. Any valid DAG it finds automatically appears in the web dashboard.

  • Switch back to VS Code.
  • In the Explorer sidebar, expand the airflow-etl folder, then right-click the dags folder.
  • Click New File and name it security_log_etl_dag.py.

This file will hold all three ETL tasks wrapped in a single DAG definition. Your ETL logic is similar to the standalone etl_pipeline.py, but restructured so Airflow can manage each task independently.

Why TaskFlow API instead of PythonOperator?

Airflow offers two ways to define tasks. The classic approach wraps each function in a PythonOperator, which requires boilerplate code to pass data between tasks. The TaskFlow API uses @task decorators instead, so you write plain Python functions and Airflow handles data passing automatically.

Airflow's official documentation recommends the @task decorator over the classic PythonOperator for Python callables. It produces cleaner, more readable DAG files.

  • Add the imports, DAG definition, and first task to security_log_etl_dag.py by pasting this code:
import json
import csv
import os

import pendulum

from airflow.sdk import dag, task


@dag(
    dag_id="security_log_etl",
    schedule=None,
    start_date=pendulum.datetime(2026, 1, 1, tz="UTC"),
    catchup=False,
    tags=["security", "etl"],
)
def security_log_etl():
    """Security Log ETL Pipeline - Extracts, transforms, and loads
    Windows Event Logs collected by NXLog."""

    @task()
    def extract():
        """Extract raw security logs from JSON file."""
        input_path = "/opt/airflow/dags/data/sample_logs.json"
        with open(input_path, "r") as f:
            raw_logs = json.load(f)
        print(f"Extracted {len(raw_logs)} log events")
        return raw_logs

What does this code do?

  • pendulum is a datetime library that ships with Airflow. The start_date tells Airflow the earliest date this DAG could have run.
  • schedule=None means the DAG only runs when you trigger it manually. catchup=False prevents Airflow from running the DAG for every missed interval since the start date.
  • The @task() decorator turns the extract() function into an Airflow task that reads JSON logs from a path inside the Airflow container.
  • The path /opt/airflow/dags/data/ is inside the container. This maps to your host's dags/data/ folder through the Docker Compose volume mount.
Add the transform and load tasks

The extract task reads raw data, but you still need the transform task to classify each event by severity and the load task to write the results to a CSV.

  • In security_log_etl_dag.py, place your cursor after the return raw_logs line at the end of the extract() function.
  • Add a blank line, then paste the transform task below it:
    @task()
    def transform(raw_logs):
        """Transform logs: enrich with severity classification."""
        processed = []
        for log in raw_logs:
            severity = "CRITICAL" if log["EventID"] == 4625 else "INFO"
            if log["EventID"] == 4672:
                severity = "HIGH"
            processed.append({
                "timestamp": log["EventTime"],
                "hostname": log["Hostname"],
                "event_id": log["EventID"],
                "event_type": log["EventType"],
                "account": log["AccountName"],
                "logon_type": log["LogonType"],
                "source_ip": log["SourceIP"] if log["SourceIP"] else "LOCAL",
                "severity": severity,
            })
        failed_logons = [e for e in processed if e["event_id"] == 4625]
        print(
            f"Transformed {len(processed)} events, "
            f"found {len(failed_logons)} failed logons"
        )
        return processed

What does the transform task do?

This function takes the raw log list from extract() and classifies each event. Failed logons (Event ID 4625) are marked CRITICAL. Privilege escalations (Event ID 4672) are marked HIGH. Everything else is INFO.

Airflow passes the return value of extract() into the raw_logs parameter automatically using a feature called XCom. You just call the functions in order and Airflow handles the data transfer behind the scenes.

  • Place your cursor after the return processed line at the end of the transform() function.
  • Add a blank line, then paste the load task, the wiring calls, and the bottom function call:
    @task()
    def load(processed_logs):
        """Load processed logs to CSV output file."""
        output_path = "/opt/airflow/dags/output/processed_logs.csv"
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        fieldnames = [
            "timestamp", "hostname", "event_id", "event_type",
            "account", "logon_type", "source_ip", "severity",
        ]
        with open(output_path, "w", newline="") as f:
            writer = csv.DictWriter(f, fieldnames=fieldnames)
            writer.writeheader()
            writer.writerows(processed_logs)
        print(f"Loaded {len(processed_logs)} events to {output_path}")

    # Wire the tasks together: extract -> transform -> load
    raw = extract()
    processed = transform(raw)
    load(processed)


security_log_etl()

What do the load task and wiring calls do?

  • load() takes the processed event list and writes it to a CSV file at /opt/airflow/dags/output/processed_logs.csv inside the container.
  • The three lines raw = extract(), processed = transform(raw), and load(processed) wire the tasks into a chain. Each return value flows into the next function's parameter.
  • security_log_etl() at the bottom of the file calls the DAG function at module level. Airflow requires this call to discover the DAG.
  • Save security_log_etl_dag.py by pressing Ctrl+S.

How does Airflow find this file?

The Docker Compose setup mounts your host's ./dags folder to /opt/airflow/dags inside the containers. When you save a file in dags/ on your host, it instantly appears inside the container. Airflow's dag-processor scans this directory and picks up new DAG files automatically.

Before you check the Airflow dashboard, what do you expect to see when you search for your DAG? How many tasks should appear in the graph view?

  • Switch back to your browser and refresh the Airflow dashboard at http://localhost:8080.
  • Look for security_log_etl in the DAG list.
  • Click on the DAG name to open it, then click the Graph tab.

You should see three connected tasks in the graph view: extract, transform, and load. The arrows between them show the data flow you defined with the wiring calls.

Don't see your DAG in the list?

  • Airflow's dag-processor may take 30-60 seconds to scan new files. Wait a moment and refresh the page.
  • Check that your file is saved at airflow-etl/dags/security_log_etl_dag.py. If it is saved outside the dags/ folder, Airflow will not find it.
  • If you see a red "Import Errors" banner at the top of the DAGs page, click it to read the error message. A common cause is a missing security_log_etl() call at the bottom of the file.

Help me debug why my DAG is not showing up in the Airflow UI

✔️ Awesome, I've got everything!

Great. Double check you have saved security_log_etl_dag.py and that all three tasks appear in the graph view.

ⓧ I'd like to double check the full code

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

  • Imports at the top: json, csv, os, pendulum, and from airflow.sdk import dag, task.
  • @dag decorator: The @dag(...) block with dag_id="security_log_etl", schedule=None, and catchup=False.
  • extract() task: Reads from /opt/airflow/dags/data/sample_logs.json and returns raw_logs.
  • transform() task: Classifies events by severity (CRITICAL, HIGH, INFO) and returns processed.
  • load() task: Writes the processed logs to a CSV at /opt/airflow/dags/output/processed_logs.csv.
  • Wiring and bottom call: raw = extract(), processed = transform(raw), load(processed) inside the function, then security_log_etl() at the very bottom of the file with no indentation.

Your DAG is wired up and visible in the Airflow dashboard. Next up, you will trigger your first pipeline run and discover what happens when the container tries to read a file that does not exist yet.

Trigger and Monitor Your Pipeline

Your security_log_etl DAG is live in the Airflow dashboard with three connected tasks ready to go. It is time to trigger it and see the pipeline in action.

Triggering a DAG tells Airflow to start executing each task in sequence. You will monitor the run from the graph view and inspect task logs to verify the results.

In this step, get ready to:
  • Trigger the DAG and investigate the result.
  • Debug and fix a Docker volume mounting issue.
  • Re-run the pipeline and verify the output.
Trigger the DAG for the first time

Before you trigger the DAG, do you think all three tasks will succeed on the first run?

  • Return to the Airflow dashboard at http://localhost:8080.
  • Find security_log_etl in the DAG list.
  • Click the Pause/resume toggle next to the DAG name to unpause it.
  • Click on the DAG name to open the Dag Details page.
  • Click Trigger Dag in the action buttons to start a manual run.
  • Click the Graph View tab and watch the task status update.

The extract task turns red. The pipeline stopped at the very first task.

  • Click the red extract task node in the graph view.
  • Click the Logs tab to see what went wrong.

You see a FileNotFoundError pointing to /opt/airflow/dags/data/sample_logs.json. The file does not exist inside the container.

Why did the extract task fail?

Your DAG code tries to open /opt/airflow/dags/data/sample_logs.json, but the dags/data/ directory does not exist on your host machine yet.

Docker Compose mounts your host's ./dags directory to /opt/airflow/dags inside the containers. If a file is missing on your host, it is also missing inside the container. The container does not have its own separate copy.

Fix the missing data file

The fix is straightforward. Create the data directory inside dags on your host machine and place sample_logs.json there.

  • Switch back to your PowerShell terminal.
  • Make sure you are in your airflow-etl directory.
  • Create the data directory inside dags by running this command:
mkdir dags\data
  • Copy sample_logs.json from the project folder you created in Step 2 into the new directory. Open File Explorer, navigate to your Step 2 project folder on the Desktop, and copy sample_logs.json.
  • Navigate to airflow-etl\dags\data\ in File Explorer and paste sample_logs.json there.

What is a volume mount?

A volume mount connects a directory on your host machine to a directory inside a Docker container. Changes on either side are instantly visible to the other.

In this project, Docker Compose mounts ./dags to /opt/airflow/dags inside the Airflow containers. The sample_logs.json file you just placed in dags\data\ is now available at /opt/airflow/dags/data/sample_logs.json inside every running container. No restart needed.

  • Confirm the file is in place by running this command:
ls dags\data

You should see sample_logs.json listed in the output.

Don't see sample_logs.json?

  • Make sure you pasted the file into airflow-etl\dags\data\, not airflow-etl\dags\ or airflow-etl\data\.
  • Check that your Step 2 project folder still contains sample_logs.json. If you moved it instead of copying, it will no longer be in the original location.
  • Help me get sample_logs.json into my Airflow dags/data directory.
Trigger the pipeline again and verify the output

Before you trigger the DAG again, what do you expect to happen now that the data file is in place?

  • Switch back to the Airflow dashboard in your browser.
  • Click Trigger Dag to start a new run.
  • Click the Graph View tab and watch each task execute in sequence.

Each task turns green one after the other: extract, then transform, then load. Your entire pipeline succeeded.

  • Click the green load task node in the graph view.
  • Click the Logs tab.

You should see Loaded 8 events to /opt/airflow/dags/output/processed_logs.csv in the log output. This confirms the pipeline processed all 8 security events and wrote the results.

  • Switch back to your PowerShell terminal.
  • Verify the output file exists on your host machine by running this command:
ls dags\output

You should see processed_logs.csv listed. This file was created by the load task inside the container and appeared on your host through the volume mount.

How did the CSV appear on my host?

The load task wrote to /opt/airflow/dags/output/processed_logs.csv inside the container. Because ./dags is volume-mounted, the file is immediately visible at dags\output\processed_logs.csv on your host. The same volume mount that caused the missing-file error now works in your favor.

Tasks still failing?

  • Confirm that sample_logs.json is in dags\data\ (not dags\ or data\) by running ls dags\data.
  • Check the extract task's Logs tab for the exact file path in the error message. It should match /opt/airflow/dags/data/sample_logs.json.
  • My Airflow DAG tasks are still failing after I added the data file. Help me debug.

Secret mission

Schedule Your Pipeline with Retries

Your ETL pipeline only runs when you click Trigger DAG. In production, data pipelines need to run on a fixed schedule and recover automatically from transient failures. Take this mission to add cron scheduling, retry logic, and external threat detection to your Airflow DAG.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing cloud costs. The Airflow containers do use approximately 4 GB of memory while running.

Resources you used:

  • Airflow Docker Compose services running in the airflow-etl directory (scheduler, worker, API server, triggerer, dag-processor, PostgreSQL, Redis).
  • Docker image security-etl built in Step 2.
  • Project files on your Desktop (Dockerfile, etl_pipeline.py, sample_logs.json, and the airflow-etl directory).
  • Docker Desktop installed on your machine (free for personal use and small businesses).

Keep everything running

No action needed. Choose this if you are still actively building or experimenting with new DAGs.

  • Your Airflow containers continue running in the background and the web dashboard stays available at http://localhost:8080.
  • The security_log_etl DAG is now scheduled to run every 6 hours. It will trigger automatically as long as the containers are running.
  • Docker Desktop will continue using approximately 4 GB of memory on your machine.

Pause - I'll come back to this later

Stop the running containers to free up memory without losing any of your data or configuration. You can resume everything later with one command.

  • Navigate to your airflow-etl directory in PowerShell and stop all containers by running:
docker compose stop
  • This stops the containers without deleting your data, volumes, or DAG files.
  • When you are ready to resume, navigate back to the airflow-etl directory and run:
docker compose start
  • Your Airflow dashboard and DAGs will be back exactly where you left them.

Delete - I don't want to use this again

Remove all project resources and start fresh. This frees all disk space used by Docker images, containers, and volumes.

  • Navigate to your airflow-etl directory in PowerShell and run this command to stop all containers, remove volumes, and delete downloaded images:
docker compose down --volumes --rmi all
  • Remove the standalone Docker image you built in Step 2 by running:
docker rmi security-etl
  • Navigate to your Desktop and delete the airflow-etl directory and the standalone project files (Dockerfile, etl_pipeline.py, sample_logs.json) by running:
cd ~\Desktop
Remove-Item -Recurse -Force airflow-etl
Remove-Item -Force Dockerfile, etl_pipeline.py, sample_logs.json
  • If you no longer need Docker on your machine, you can uninstall Docker Desktop. Open Settings on your Windows machine, select Apps, then Installed apps. Find Docker Desktop in the list and click Uninstall.

Nice Work!

Nice Work!

Nice work! You just built a production-style orchestration layer for a security log ETL pipeline using Docker and Apache Airflow.

What you learned:

  • Built a Docker image that packages a Python ETL script into a portable, reproducible container, proving it runs identically regardless of the host machine.
  • Launched a full Apache Airflow 3.3.1 instance locally using Docker Compose and wrote a DAG with the TaskFlow API that breaks the pipeline into three orchestrated tasks: extract, transform, and load.
  • Debugged a real Docker volume mounting issue by tracing a FileNotFoundError to a missing data file, then triggered and monitored pipeline runs from the Airflow web dashboard.
  • Secret Mission: Added automatic scheduling every 6 hours with retry logic (3 retries, 2-minute delays) and enhanced the transform step to flag external security threats.

Ready to quiz yourself?