Chapter 2 · Foundations

The Lab

Everything in this tutorial runs on one small Ubuntu Linux virtual machine deployed in AWS, with Docker: SQL Server 2022 for Linux in one container, and an Airflow stack in six more. This chapter shows what runs where, how the folders map into the containers, and how to rebuild the warehouse from nothing.

What runs where

The folder layout: logic and orchestration apart

The one rule of this design is that no SQL lives in the DAG. Compose Version10 added a single line (Version11 only renamed comments), a new bind mount, so the SQL folder appears inside every Airflow container next to the DAGs:

  volumes:
    - ${AIRFLOW_PROJ_DIR:-.}/dags:/opt/airflow/dags
    - ${AIRFLOW_PROJ_DIR:-.}/logs:/opt/airflow/logs
    - ${AIRFLOW_PROJ_DIR:-.}/config:/opt/airflow/config
    - ${AIRFLOW_PROJ_DIR:-.}/plugins:/opt/airflow/plugins
    - ${AIRFLOW_PROJ_DIR:-.}/dbt:/opt/airflow/dbt
    - ${AIRFLOW_PROJ_DIR:-.}/sql:/opt/airflow/sql      # Version10: plain SQL for SBEcoLTDDW

dags/
  sbecoltddw_e2e_pipeline.py     # the pipeline (orchestration only)
  sbecoltddw_source_changes.py   # source-system simulator
plugins/
  sbecoltddw_operators.py        # MsSqlFileOperator
sql/sbecoltddw/
  00_control/      start_run, end_run, fail_run
  01_deploy/       11 idempotent DDL files
  02_extract/      source → stg
  03_stage_checks/ stop early on bad staging
  04_dimensions/   Date, Customer SCD2
  05_facts/        Orders MERGE
  06_post_load/    columnstore, stats, view check
  07_dq/           the data quality gate
  08_publish/      watermark, run summary
  90_source_changes/  the simulator's SQL

run_sbecoltddw.sh        # course build (Chapters 2–6 of the course)
reset_source.sh          # undo the simulator's source changes
sql/                     # course scripts, 99_Verify
airflow/                 # master copy of dags, plugins, sql
  setup_airflow_sbecoltddw.sh
  sbdw_run.sh             # trigger a DAG, wait, print states
  sbdw_clear.sh           # clear a failed task + downstream
  sbdw_capture.sh         # pull SQL output out of task logs
  sbdw_scenarios.sh       # the Chapter 12 scenarios
captures/                # outputs quoted in this tutorial
.dataloader.env          # DataLoader password (chmod 600)

Connections and logins

Rebuilding everything from nothing

1. Course objects, empty. REBUILD=1 COURSE_ONLY=1 bash ~/sbecoltddw/run_sbecoltddw.sh drops and recreates SBEcoLTDDW with the course's schemas, tables, indexed view and columnstore index, plus the DataLoader login with its Resource Governor setup. Every statement is run by sqlcmd inside the SQL Server Linux container. course-build/run_sbecoltddw.sh · ~30 s
2. Wire up Airflow. bash ~/sbecoltddw/airflow/setup_airflow_sbecoltddw.sh backs up the compose file, installs Version11, copies the SQL folder, plugin and DAGs, recreates the Airflow containers and creates the connection. airflow/setup_airflow_sbecoltddw.sh · ~2 min
3. Load. ~/sbecoltddw/airflow/sbdw_run.sh sbecoltddw_e2e_pipeline triggers the DAG and waits. The first run deploys the pipeline objects and loads everything. ~1 min per run

echo ">> 3. recreate Airflow containers (docker-compose v1: rm -sf, then up -d)"
SERVICES="airflow-webserver airflow-scheduler airflow-worker airflow-triggerer"
docker-compose rm -sf $SERVICES
docker-compose up -d $SERVICES
echo "waiting for the scheduler..."
for i in $(seq 1 30); do
  if docker exec airflow_airflow-scheduler_1 test -d /opt/airflow/sql/sbecoltddw 2>/dev/null &&
     docker exec airflow_airflow-scheduler_1 airflow version >/dev/null 2>&1; then break; fi
  sleep 5
done
docker ps --format '{{.Names}}  {{.Status}}' | grep airflow_

echo ">> 4. connections (passwords are read from files/connections, never printed)"
set -a; source "$HOME/sbecoltddw/.dataloader.env"; set +a
docker exec -i -e DL_PASSWORD airflow_airflow-scheduler_1 python - <<'PY'
import os
from airflow.hooks.base import BaseHook
from airflow.models import Connection
from airflow.settings import Session

wanted = [
    Connection(conn_id="mssql_sbecoltddw_etl", conn_type="odbc", host="mssql", port=1433,
               schema="SBEcoLTDDW", login="DataLoader", password=os.environ["DL_PASSWORD"],
               description="SBEcoLTDDW pipeline steps (Resource Governor group DataLoads)"),
]
s = Session()
for c in wanted:
    s.query(Connection).filter(Connection.conn_id == c.conn_id).delete()
    s.add(c)
    print(f"connection {c.conn_id}: {c.conn_type}://{c.login}@{c.host}:{c.port}/{c.schema}")
s.commit()
PY