Data Warehousing track

End-to-End Data Warehouse on Linux + Docker: SQL Server 2022 + Airflow

Lab:Ubuntu 24.04 VM on AWS+Docker · docker-compose+SQL Server 2022 on Linux (container)+Airflow 2.7 (containers)Every chapter, script and number in this tutorial comes from this Linux + Docker lab. There is no Windows server anywhere in it.

You will build SBEcoLTDDW, a sales data warehouse, from an operational database all the way to reporting views. SQL Server 2022, running in a Docker container on an Ubuntu Linux VM in AWS, stores and transforms the data. Apache Airflow, also in Docker containers on the same VM, only decides what runs when: every task is one SQL file kept in its own folder, so you can read, test and change the SQL without touching Python.

For: data engineers, BI developers, SQL developers Prerequisite: basic SQL ~4 hours

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 whole pipeline in one line

Source: AdventureWorks2022 (sales OLTP)→ stg: full and watermark extracts→ Dimensions: Date, Customer (SCD2)→ Fact: Orders (MERGE)→ DQ gate→ rpt views

Where this comes from

The warehouse design follows the LinkedIn Learning course Implementing a Data Warehouse with SQL Server 2022 (schemas, the Date/Customer/Orders star, the indexed view, columnstore and workload classification). This tutorial runs those exercises on SQL Server for Linux, fixes what breaks there, and wraps them in a production-style pipeline.

Chapters

What you will have at the end

PieceWhat it isWhere
DWSBEcoLTDDWStar schema with an SCD2 customer dimension, an incremental fact table, two indexed views and a columnstore indexSQL Server 2022 container labenv_mssql_1
AFsbecoltddw_e2e_pipeline26-task Airflow DAG in 6 task groups, orchestration only~/airflow/dags
AFsbecoltddw_source_changesA source-system simulator: customer moves, new orders, a bad order and its fix~/airflow/dags
SQL31 SQL filesDeploy, extract, check, load, maintain, DQ and publish steps, one batch each~/airflow/sql/sbecoltddw
PYMsSqlFileOperator~120 lines of plumbing that run a SQL file and log its output~/airflow/plugins

All of the code is in this site's code folder; Appendix A lists every file.