DE Homework Assignment

What is this repo?

This is a local Airflow development environment. It comes pre-wired so you can focus on writing DAGs and data engineering logic — not on infrastructure. Feel free to modify anything that doesn't suit you.

The repo contains:

• Apache Airflow 2.10.4 running in Docker (webserver + scheduler + Postgres metadata DB)
• packages/template_package — a Python package installed into Airflow in editable mode. Put your reusable logic here (DB connections, transformations, helpers, etc.) and import it in your DAGs
• dags/ — DAG files; everything here is picked up by Airflow automatically (volume-mounted, no rebuild needed)

---

Prerequisites

• Docker + Docker Compose
• uv (for local development and adding packages)

---

First-time setup

docker compose up --build

This builds the image, initialises the Airflow database, creates an admin user, and starts all services. Subsequent starts (without code changes) can skip --build:

docker compose up

---

Airflow UI

Once running, open: http://localhost:8085

Username	Password
admin	admin

---

Project structure

.
├── dags/                         # Your DAG files go here
│   └── example_dag.py
├── packages/
│   └── template_package/         # Your reusable Python package
│       ├── pyproject.toml        # Dependency definitions (edit this)
│       ├── requirements.txt      # Auto-generated — DO NOT edit by hand
│       └── template_package/     # Actual Python source
├── Dockerfile
├── docker-compose.yml
└── update_requirementy.sh        # Regenerates requirements.txt safely

---

Writing DAGs

DAGs are volume-mounted — just create a .py file in dags/ and Airflow will pick it up within seconds. No rebuild needed.

To use code from template_package in a DAG:

from template_package.example_module import my_function

template_package is installed in editable mode inside the containers, so changes to its source files are reflected immediately without a rebuild.

---

Adding a Python dependency

⚠️ Always follow these steps in order. Do not edit requirements.txt by hand.

# 1. Add the package via uv (updates pyproject.toml and uv.lock)
cd packages/template_package
uv add <package-name>

# 2. Go back to the repo root and regenerate requirements.txt
cd ../..
bash update_requirementy.sh

# 3. Rebuild the Docker image to install the new package
docker compose build --no-cache
docker compose up

Why the script? requirements.txt is generated by uv pip compile using the Airflow constraint file. This guarantees your new dependency is compatible with Airflow's own pinned versions — if it isn't, the script will fail with a clear error before anything breaks.

---

Updating requirements

If you've edited pyproject.toml manually or pulled changes from upstream:

bash update_requirementy.sh
docker compose build --no-cache
docker compose up

---

Running tests

cd packages/template_package
uv sync                 # install dev deps locally
uv run pytest

---

Stopping and cleaning up

# Stop containers (keeps data)
docker compose down

# Stop and remove all data (Postgres + MSSQL volumes included)
docker compose down -v

---

Target database (MSSQL)

A Microsoft SQL Server 2022 instance runs as part of the stack. Treat it as you would a remote database server — your task is to connect to it, create a schema, and write data into it.

Connection details

Property	Value
Host (from inside Docker / DAG code)	mssql_server
Host (from your local machine)	localhost
Port	1433
Database	candidate_db
Username	candidate
Password	HwC4ndidate#2026

Connecting from a DAG

The connection details are pre-loaded as Airflow Variables so you can access them without hardcoding anything:

from airflow.models import Variable
import pymssql

conn = pymssql.connect(
    server=Variable.get("mssql_host"),
    port=int(Variable.get("mssql_port")),
    database=Variable.get("mssql_database"),
    user=Variable.get("mssql_user"),
    password=Variable.get("mssql_password"),
)

Or with SQLAlchemy:

from sqlalchemy import create_engine
from airflow.models import Variable

engine = create_engine(
    f"mssql+pymssql://{Variable.get('mssql_user')}:{Variable.get('mssql_password')}"
    f"@{Variable.get('mssql_host')}:{Variable.get('mssql_port')}"
    f"/{Variable.get('mssql_database')}"
)

Why pymssql and not pyodbc?
pymssql ships as a self-contained wheel — no system-level ODBC driver installation needed. It is already included in requirements.txt and installed into the Airflow containers.

---

Dependency system — how it works

requirements.txt is not a plain pip requirements file. It is produced by uv pip compile against the Airflow 2.10.4 constraints file.

This means:

• Every pinned version is guaranteed to be compatible with Airflow
• The comments in requirements.txt show exactly why each version was chosen
• Running bash update_requirementy.sh is always safe — it will error loudly if a conflict exists rather than producing a silently broken image