Appendix A
Script Index & Gotchas
Lab:Ubuntu 24.04 VM on AWS+Docker · docker-compose+SQL Server 2022 on Linux (container)+Airflow 2.7 (containers)Every gotcha below was hit on this Linux + Docker stack.
Every file in the lab, where it runs and what it does, followed by the surprises found while building it. Each one cost real debugging time, so they're collected here.
Course build: ~/sbecoltddw on the Linux host
| File | Runs as | Does |
|---|
| run_sbecoltddw.sh | shell → docker exec → sqlcmd | Runs the course scripts in order inside the SQL Server Linux container; REBUILD=1 drops the database first |
| reset_source.sh | shell → docker exec → sqlcmd | Undoes the simulator's changes in AdventureWorks2022 so the scenarios can be replayed |
| 00_CreateDatabase | sa | Creates SBEcoLTDDW (simple recovery) |
| 01_CreateDW · 02_Dimension · 03_Fact | sa | Course Ch. 2: schemas, Date + Customer, Orders + foreign keys (Year fixed to smallint) |
| 05_View · 06_Columnstore · 08_Rebuild | sa | Course Ch. 2–3: indexed view, nonclustered columnstore, online rebuild |
| 09_ClassifyWorkloads | sa | Course Ch. 5: DataLoader login + Resource Governor pool, group and classifier |
| 13_GrantSourceRead | sa | DataLoader gets db_datareader on AdventureWorks2022 |
| 99_Verify | sa | Row counts, indexes, foreign keys, orphan check, Resource Governor stats |
Pipeline: ~/airflow, bind-mounted into the Airflow containers
| File | Airflow task | Chapter |
|---|
| dags/sbecoltddw_e2e_pipeline.py | the 26-task DAG | 11 |
| dags/sbecoltddw_source_changes.py | the source simulator | 12 |
| plugins/sbecoltddw_operators.py | MsSqlFileOperator | 11 |
| 00_control/ start_run · end_run · fail_run | start_run, end_run, fail_run | 11, 12 |
| 01_deploy/ 01–11 | deploy.* | 3, 5, 6, 8, 13 |
| 02_extract/ customers · sales_orders | extract.* | 5 |
| 03_stage_checks/check_staging.sql | stage_checks | 5 |
| 04_dimensions/ date · customer_scd2 | dimensions.* | 6 |
| 05_facts/load_fact_orders.sql | facts.load_fact_orders | 7 |
| 06_post_load/ columnstore · statistics · indexed_view | post_load.* | 8 |
| 07_dq/dq_gate.sql | dq_gate | 10 |
| 08_publish/ advance_watermarks · run_summary | publish.* | 5, 12 |
| 90_source_changes/ 5 files | simulator tasks | 12 |
| setup_airflow_sbecoltddw.sh · sbdw_run.sh · sbdw_clear.sh · sbdw_capture.sh · sbdw_scenarios.sh | helpers (shell, on the Linux host) | 2, 12 |
Rebuild from nothing
bash ~/sbecoltddw/reset_source.sh # original AdventureWorks2022 data
REBUILD=1 COURSE_ONLY=1 bash ~/sbecoltddw/run_sbecoltddw.sh # course objects, empty tables
bash ~/sbecoltddw/airflow/setup_airflow_sbecoltddw.sh # compose v11, files, containers, connection
~/sbecoltddw/airflow/sbdw_run.sh sbecoltddw_e2e_pipeline # initial load
~/sbecoltddw/airflow/sbdw_scenarios.sh move # SCD2 scenario (then: bad, fix, reload)
Gotchas
SQL Server 2022 on Linux, in Docker
| Symptom | Cause | Fix |
|---|
No sqlcmd on the Linux host | The client tools live inside the container image (/opt/mssql-tools18) | docker exec -i labenv_mssql_1 /opt/mssql-tools18/bin/sqlcmd -C …, piping the SQL file on stdin |
Msg 1934 … SET options have incorrect settings: 'QUOTED_IDENTIFIER' on AdventureWorks tables | sqlcmd defaults QUOTED_IDENTIFIER to OFF; the source tables have indexes on computed columns | sqlcmd -I |
| Container uses all of the host's RAM | SQL Server sizes its memory from what it can see | MSSQL_MEMORY_LIMIT_MB = 2048 in the compose file |
| Data lost when the container is recreated | Files written inside the container layer | A named Docker volume on /var/opt/mssql |
T-SQL and the engine
| Symptom | Cause | Fix |
|---|
| Arithmetic overflow loading years | Course Year tinyint (max 255) | smallint |
Indexed view creation fails from sqlcmd | QUOTED_IDENTIFIER is OFF by default in sqlcmd | sqlcmd -I (ODBC clients are fine) |
Incorrect syntax near the keyword 'statistics' | Reserved word as a column alias | Rename (stats_objects) or bracket it |
Columnstore stays OPEN, about 3× larger | Fewer than 102,400 rows per insert thread | REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) after the load |
| Can't change the Resource Governor classifier | The active classifier function can't be altered | ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL) first |
Readers of rpt denied on the tables | Schemas created by DataLoader are owned by DataLoader, which breaks the ownership chain | CREATE SCHEMA … AUTHORIZATION dbo, or ALTER AUTHORIZATION |
| Columns added in the same batch "don't exist" | A batch compiles before ALTER TABLE ADD runs | Put dependent statements in the next file, or in EXEC() |
Airflow in Docker and the lab
| Symptom | Cause | Fix |
|---|
A failing THROW reported as success | The error arrives in a later result set (SQLMoreResults) | Loop over cursor.nextset() (the operator does) |
| New SQL folder not visible in tasks | Bind mounts are fixed when the container is created | Compose Version10/11, then docker-compose rm -sf + up -d (compose v1) |
Python sent via docker exec … python - <<EOF does nothing | No -i, so stdin isn't attached | docker exec -i |
JSONDecodeError on dags trigger -c | The JSON's double quotes were stripped by an extra shell layer (PowerShell → ssh) | Put the call in a script on the server (sbdw_scenarios.sh) |
| Re-running a scenario does nothing | A DAG run with that run_id already exists, so dags trigger -r fails | Use a new run ID, or airflow dags delete first (and remove its log folders) |
| New deploy file, no new task | DAG not re-parsed yet | Wait for the parse interval or run airflow dags reserialize |