Chapter 1 · Foundations

The Big Picture

Lab:Ubuntu 24.04 VM on AWS+Docker · docker-compose+SQL Server 2022 on Linux (container)+Airflow 2.7 (containers)The whole warehouse (SQL Server, Airflow and Airflow's own metadata database) runs as Docker containers on one Ubuntu Linux VM deployed in AWS.

A data warehouse is a database built for questions instead of transactions. This chapter starts with the infrastructure the lab runs on and how it is deployed, then shows the whole SBEcoLTDDW pipeline (source, layers, orchestration and consumers) so every later chapter has a place to hang on.

Infrastructure: Linux + Docker + SQL Server on Linux

The lab is one Ubuntu Linux virtual machine deployed in AWS. Docker runs every service in a container: SQL Server 2022 for Linux in one, Apache Airflow in six more. Your PC only edits files, opens SSH sessions and a browser.

SQL Server 2022 on Linux (container) Airflow (containers) Your PC Linux host layers
YOUR PC (WINDOWS 11) Developer PC VS Code edits C:\WorkFolder\SBEcoLTDDW_Lab ssh / scp (OpenSSH) deploys files, runs scripts MobaXterm live view of the SSH session log Browser Airflow UI · http://…:8080 SSMS / Azure Data Studio optional · SQL Server on :1433 22 8080 1433 LAN 192.168.10.0/24 UBUNTU 24.04 LTS SERVER · 192.168.10.150 labenv_mssql_1 · SQL Server 2022 for Linux image mcr.microsoft.com/mssql/server:2022-latest CU27 · Ubuntu 22.04 base · port 1433 → host 1433 volume airflow_mssql-data → /var/opt/mssql MSSQL_MEMORY_LIMIT_MB = 2048 AdventureWorks2022 sales OLTP · the source Other sample DBs not used by the pipeline tempdb · msdb system databases SBEcoLTDDW the warehouse Fact · Dimension stg · rpt · audit · etl columnstore index 2 indexed views master Resource Governor classifier Linux file system inside the container /var/opt/mssql/data .mdf .ldf files /var/opt/mssql/log error log Apache Airflow 2.7.2 · CeleryExecutor webserver:8080 schedulerparses DAGs workerruns tasks triggererdeferrables postgres 13metadata DB redisbroker bind mounts ~/airflow → /opt/airflow dags/ plugins/ sql/sbecoltddw/ logs/ pyodbc 4.0.39 + ODBC Driver 18 providers common-sql 1.7.2, odbc 4.0.0 connection mssql_sbecoltddw_etl (login DataLoader) ODBC → mssql:1433 (Docker DNS) Shares the same Linux host postgres-15 lab DB (:5433) · dbt lab microk8s · Nextcloud (snap) Docker Engine + docker-compose v1.29 project ~/airflow/docker-compose.yaml (Version11) · network airflow_default · named volumes · restart policies Ubuntu 24.04 LTS · Linux kernel systemd · OpenSSH server (key auth) · user devserveruser · ~/sbecoltddw (lab files) · ~/airflow (stack) Virtual machine on AWS Ubuntu VM deployed in AWS · 2 vCPU · 9.5 GB RAM · 98 GB disk (LVM) · shared by all of the workloads above
Figure 1.1 — Infrastructure: one Ubuntu Linux VM deployed in AWS, Docker Engine, a SQL Server 2022 for Linux container and the Airflow containers. Ports 22, 8080 and 1433 are the only ones the PC uses.

Deployment: from the PC into the containers

Nothing is installed by hand inside a container. Files are edited on the PC, copied to the Linux server, and reach the containers either through bind mounts (Airflow) or through docker exec / docker cp (SQL Server).

Source of the files Linux host folders Airflow containers SQL Server Linux container
PC · SBEcoLTDDW_Lab master copy of every file sql/ course build run_sbecoltddw.sh reset_source.sh airflow/dags/ airflow/plugins/ airflow/sql/sbecoltddw/ airflow/*.sh helpers docker-compose_Version11 Linux host · ~/sbecoltddw staging area for deployments sql/ run_sbecoltddw.sh reset_source.sh airflow/ (dags, plugins, sql) .dataloader.env (chmod 600) logs/ captures/ scp Linux host · ~/airflow docker-compose.yaml (v11) dags/sbecoltddw_*.py plugins/sbecoltddw_operators.py sql/sbecoltddw/**.sql dags_backup/ (old versions) setup_airflow_sbecoltddw.sh (cp) Airflow containers /opt/airflow/dags /opt/airflow/plugins /opt/airflow/sql metadata: 1 connection, 2 DAGs, run history bind mount SQL Server 2022 on Linux · container labenv_mssql_1 docker exec … sqlcmd -I -b ← run_sbecoltddw.sh creates SBEcoLTDDW, DataLoader login, Resource Governor docker exec … sqlcmd -I ← reset_source.sh undoes the simulator's changes in AdventureWorks2022 AdventureWorks2022 (restored once from .bak) the source system every extract reads from ODBC :1433 (login DataLoader) ← every Airflow task 1 2 3 4 5 6 All copies and commands go through one SSH wrapper that writes every command and its output to a session log, so each deployment step is visible and auditable. Re-deploying = copy the changed file(s) again; SQL files are read at task run time, so no container restart is needed for SQL changes.
Figure 1.2 — Deployment: numbered markers match the steps below.
  1. Copy the lab to the Linux server. scp puts the master copy from the PC into ~/sbecoltddw; shell scripts get their line endings converted to LF.
  2. Build the warehouse objects. run_sbecoltddw.sh pipes each course SQL file into sqlcmd inside the SQL Server Linux container with docker exec. There is no SQL Server client installed on the host.
  3. Reset the source when replaying. reset_source.sh pipes SQL into sqlcmd inside the container to undo the simulator's changes in AdventureWorks2022, so the scenarios of Chapter 12 can be run again from the original data.
  4. Install the pipeline into Airflow. setup_airflow_sbecoltddw.sh installs compose Version11, copies DAGs, plugin and SQL into ~/airflow, recreates the Airflow containers once, and creates the connection.
  5. Bind mounts make them live. The containers see ~/airflow/{dags,plugins,sql} as /opt/airflow/…; the scheduler picks up the DAGs within seconds.
  6. Run. Every task opens an ODBC connection from the Airflow worker container to mssql:1433 and runs one SQL file in SBEcoLTDDW.

The pipeline on one diagram

In 60 seconds. Everything runs on one Ubuntu Linux VM deployed in AWS, with Docker: SQL Server 2022 runs in a Linux container, and Airflow in its own containers. The source is AdventureWorks2022, the shop's sales database. Each run copies customers and changed orders into stg, builds the dimensions (Date, and Customer with history) and the fact (Orders), maintains the indexes, checks data quality, and only then marks the data as published. Airflow runs the 26 steps in order. Each step is one SQL file, and every step writes to an audit table.

Source SQL Server 2022 on Linux · SBEcoLTDDW Airflow: orchestration Data flow
Apache Airflow 2.7 · sbecoltddw_e2e_pipeline Docker containers · decides what runs when, retries, records state · no business logic deploy → start_run → extract → stage_checks → dimensions → facts → post_load → dq_gate → publish → end_run (fail_run if anything fails) runs one .sql file per task (ODBC → mssql:1433, login DataLoader) SOURCE AdventureWorks2022 sales OLTP · same container Sales.Customer Person.Person Person.Address … → customers (full) Sales.SalesOrderHeader → orders (incremental, ModifiedDate watermark) changed by the simulator DAG (Chapter 12) SBEcoLTDDW SQL Server 2022 on Linux · Docker container labenv_mssql_1 stg one run's data only Customer SalesOrderHeader full + watermark extracts, checked before use Dimension · Fact (the star) conformed, historical, indexed Dimension.Date 2010–2030 Dimension.Customer SCD 1 + 2 Fact.Orders MERGE, columnstore dbo.OrdersByDate indexed view dbo.SalesByYear indexed view rpt what consumers read vSalesByMonth vSalesByState vCustomerHistory vLoadHistory stable names over changing tables audit + etl audit.BatchRun · audit.StepLog · audit.DQResult · etl.Watermark every task logs rows read / inserted / updated, its login and Resource Governor group CONSUMERS Reports / BI Power BI, Excel, SSRS Analysts ad-hoc T-SQL on rpt.* Data team run history, DQ results Resource Governor login DataLoader → group DataLoads → DataLoadPool the ETL can't starve report queries Data quality gate 5 rules incl. reconciliation with the live source fail → nothing published, watermark kept Re-runnable by design idempotent DDL, MERGE on business keys, clear any failed task and run it again 1 2 3 4 5
Figure 1.3 — SBEcoLTDDW end to end. Numbered markers match the steps below.

How one run flows

  1. Extract into staging. Customers are copied in full (7,819 rows is cheap). Orders are copied incrementally: only rows whose ModifiedDate is newer than the stored watermark. Both are cross-database INSERT … SELECTs inside the one SQL Server Linux container. 02_extract/*.sql · Chapter 5
  2. Build the dimensions. The calendar is generated, and customers are compared with a row hash and get a new version when they move (SCD Type 2). 04_dimensions/*.sql · Chapter 6
  3. Load the fact. Orders are matched to the customer version that was valid on the order date and merged on their business key, so a re-run never duplicates. The indexed views update themselves. 05_facts/*.sql · Chapter 7
  4. Maintain, check, publish. Columnstore delta stores are compressed and statistics refreshed. Then 5 data quality rules run, including a reconciliation against the live source. Only when they pass is the watermark advanced and the batch marked Succeeded. 06_post_load, 07_dq, 08_publish · Chapters 8, 10 and 12
  5. Serve. Consumers read the rpt views, never the staging or load tables. 01_deploy/07–10 · Chapter 13

Why a separate warehouse at all?

AdventureWorks2022 is an OLTP database: it is designed so the shop can insert an order in a millisecond. Answering "sales by state per year, using the address the customer had at the time" from it means joining six normalised tables, it competes with the shop for resources, and the history is gone because an address update overwrites the old one. A warehouse is shaped for the opposite workload.

OLTP (AdventureWorks2022)Data warehouse (SBEcoLTDDW)
Optimised forMany small writesLarge reads and aggregations
ShapeNormalised (3NF): Customer → Person → BusinessEntityAddress → Address → StateProvinceStar schema: one fact table, a few wide dimensions
HistoryCurrent state only; updates overwriteKept on purpose (SCD Type 2, append-only facts)
KeysSource IDs (CustomerID)Surrogate keys (CustomerKey) plus the source ID as an alternate key
IndexesB-trees for point lookupsColumnstore and indexed views for scans and totals
Loaded byThe applicationA scheduled, audited pipeline

The layers and their contracts

LayerContainsContract
stgCopies of source rows for this runEmptied at the start of each extract; never read by consumers; checked before use
Dimension / FactThe star schema (course objects + pipeline columns)Keys are stable; history is never lost; loads are idempotent
rptViewsThe only objects consumers depend on, so tables can change behind them
audit, etlRun, step and DQ logs; watermarksWritten by every task; the source of truth for "what happened"

ETL or ELT?

In classic ETL a separate engine (SSIS, Informatica) transforms rows before they reach the warehouse. In ELT the raw data is landed first and transformed inside the database with SQL. SBEcoLTDDW is ELT on a single engine: INSERT … SELECT moves source rows into stg, and all transformations are T-SQL running where the data already is, inside the SQL Server Linux container. Airflow never touches a row. It only tells SQL Server which file to run next.

Design rule used throughout

Keep orchestration (order, retries, schedules, alerts) in Airflow and logic (joins, rules, history) in SQL files. The DAG file is about 100 lines and contains no SQL. You can open any task's Rendered Template in Airflow and run exactly that SQL by hand with sqlcmd in the container, or from SSMS.

Five properties every step has

  • Idempotent: running a step twice gives the same result. DDL uses IF NOT EXISTS, the fact uses MERGE on the business key, and dimensions only add missing rows.
  • Restartable: a failed task can be cleared and re-run alone (Chapter 12 does this for real).
  • Audited: each step writes one row to audit.StepLog with its row counts, login and Resource Governor group.
  • Safe on failure: the watermark only moves in the last publish step, so a failed run is simply picked up again next time.
  • Checked: nothing is published until the data quality gate passes.

Key takeaways

  • The lab is one Ubuntu Linux VM deployed in AWS; Docker runs SQL Server 2022 for Linux and Airflow in containers.
  • Files are deployed from the PC to the Linux host and reach the containers through bind mounts or docker exec.
  • A warehouse is shaped for questions: a star schema, history, and indexes built for scans.
  • SBEcoLTDDW has four layers: stg → star → rpt, plus audit/etl for control.
  • Every step is idempotent, restartable, audited and gated.