Microsoft SQL Server 2022 · Docker Compose · PowerShell 7 · T-SQL · data integrity · dimensional modelling · GitHub Actions

This repository is a compact SQL Server analytics lab. It provides a reproducible database environment, a normalized training model, deterministic synthetic data, enforced integrity rules, a dimensional reporting layer and executable verification from source tables through the reporting fact.

The same PowerShell entry point runs locally and in GitHub Actions. The CI workflow provisions SQL Server on a fresh Ubuntu runner and executes the complete lab twice, proving both clean installation and repeatability.

The project is part of my Data/BI portfolio during the IHK retraining program in Data and Process Analysis. Its scope is deliberately bounded: it demonstrates a technically credible path from relational source data to a tested star schema without presenting the lab as a production data platform.

A technical reviewer can inspect the implementation in this order:

1. .github/workflows/sql-server-integration.yml — end-to-end CI execution and repeatability test
2. docker-compose.yml — pinned SQL Server image, volume, bind mount and healthcheck
3. scripts/Initialize-Lab.ps1 — complete orchestration entry point
4. scripts/SqlServerLab.Common.ps1 — shared Docker, configuration and readiness functions
5. sql/02_create_schema.sql — relational schema, constraints, triggers and derived-result view
6. sql/06_test_integrity_rules.sql — executable negative tests
7. sql/07_create_reporting_model.sql — dimensional schema, constraints, indexes and reporting view
8. sql/08_load_reporting_model.sql — transaction-based dimensional upsert
9. sql/09_verify_reporting_model.sql — source-to-reporting reconciliation
10. docs/ci-workflow.md — CI design, security boundaries and validation evidence

local .env or temporary CI .env
              │
              ▼
PowerShell bootstrap
              │
              ├── validate Docker and Compose
              ├── validate configuration
              ├── pull and start SQL Server
              └── wait for container health
                      │
                      ▼
relational source workflow
                      │
                      ├── create or upgrade source schema
                      ├── insert deterministic synthetic data
                      ├── enforce constraints and triggers
                      ├── verify source objects and values
                      └── run rollback-based negative tests
                      │
                      ▼
dimensional reporting workflow
                      │
                      ├── create reporting schema
                      ├── upsert dimensions and fact rows
                      ├── verify grain and key integrity
                      └── reconcile reporting with source

GitHub Actions executes this complete sequence twice in one job. The first run validates provisioning on a fresh runner. The second run uses the same temporary database volume and proves that the setup and load remain repeatable.

The host connection uses port 14333, while SQL Server remains on internal port 1433 inside the container.

• SQL Server 2022 managed through Docker Compose
• pinned cumulative-update image
• persistent named volume for local development
• configurable password, edition, image and host port
• sqlcmd-based healthcheck
• repository SQL directory mounted read-only inside the container
• access through DataGrip or another SQL Server client

The shared PowerShell workflow provides:

• Docker CLI, engine and Compose validation
• .env presence and value checks
• rejection of public placeholder passwords
• optional image-pull skipping for later runs
• container startup and SQL Server health polling
• ordered execution of source and reporting scripts
• non-zero failure propagation from Docker and sqlcmd
• source-model verification
• rollback-based negative integrity tests
• reporting-model reconciliation
• safe stop and container removal without automatic volume deletion

The scripts require PowerShell 7 and are validated on Windows 11 locally and Ubuntu 24.04 in GitHub Actions.

The dpa_training database contains four source tables in the dpa schema:

The source schema enforces:

• surrogate identity keys
• stable business codes
• primary and foreign keys
• unique module and learner codes
• unique assessment names within a module
• one result per learner and assessment
• non-empty required text values
• valid maxima and pass thresholds
• non-negative scores
• cross-table score limits through set-based triggers

dpa.v_assessment_results derives:

• score_percentage from score / max_score
• passed from score >= pass_score

The earlier stored passed column was removed only after a controlled upgrade check confirmed that every existing value agreed with the underlying score and threshold.

sql/06_test_integrity_rules.sql attempts seven invalid writes:

1. set max_score to zero
2. set pass_score above max_score
3. set a result score below zero
4. set a result score above the assessment maximum
5. lower an assessment maximum below an existing result
6. create a duplicate learner-assessment result
7. duplicate an assessment name within one module

Each test runs in a transaction, rolls back any open transaction and checks the expected SQL Server error class. The suite fails closed when an expected error is missing.

The reporting schema contains:

The fact table uses surrogate dimension keys while retaining source_result_id for lineage. It contains:

• score
• max_score
• pass_score
• persisted score_percentage
• persisted passed
• loaded_at_utc

A unique (assessment_key, learner_key) constraint enforces the declared fact grain. Foreign keys connect every fact row to all three dimensions.

sql/08_load_reporting_model.sql performs a transaction-based upsert:

• existing dimension attributes are refreshed
• existing surrogate keys are preserved
• new source members are inserted
• every source result must resolve to all dimension keys
• existing facts are refreshed through source_result_id
• new facts are inserted

The load does not silently remove reporting rows. Unexpected stale or additional rows are surfaced by reconciliation instead.

sql/09_verify_reporting_model.sql checks:

• presence of all reporting objects
• exact source-to-dimension and source-to-fact count parity
• bidirectional dimension equality
• absence of orphaned keys
• uniqueness of the fact grain
• bidirectional equality between source results and fact values
• enabled and trusted constraints and foreign keys
• persisted computed measures

Validated values for the included dataset are:

The workflow at .github/workflows/sql-server-integration.yml runs for relevant pull requests and pushes to main, and can also be started manually.

It performs:

• checkout with read-only repository permission
• PowerShell, Docker and Compose version reporting
• syntax parsing of every PowerShell script
• runtime generation of a temporary CI-only .env
• one complete initialization on a fresh Ubuntu runner
• a second complete initialization for repeatability
• SQL Server diagnostics on failure
• unconditional container and temporary-file cleanup

The first pull-request run completed successfully on GitHub-hosted Ubuntu 24.04.4. Both complete database runs passed, including source verification, seven negative integrity tests and reporting reconciliation.

The CI workflow uses no repository secrets and receives no write permission. See docs/ci-workflow.md for details.

sql-server-docker-basics/
├── .github/
│   └── workflows/
│       └── sql-server-integration.yml
├── README.md
├── LICENSE
├── .env.example
├── .gitignore
├── docker-compose.yml
├── docs/
│   ├── ci-workflow.md
│   ├── datagrip-workflow.md
│   ├── project-notes.md
│   └── reporting-model.md
├── scripts/
│   ├── Initialize-Lab.ps1
│   ├── Invoke-SqlScript.ps1
│   ├── SqlServerLab.Common.ps1
│   ├── Stop-Lab.ps1
│   ├── Test-DataIntegrity.ps1
│   ├── Test-LabConnection.ps1
│   └── Test-ReportingModel.ps1
└── sql/
    ├── 01_create_database.sql
    ├── 02_create_schema.sql
    ├── 03_insert_sample_data.sql
    ├── 04_analysis_queries.sql
    ├── 05_verify_setup.sql
    ├── 06_test_integrity_rules.sql
    ├── 07_create_reporting_model.sql
    ├── 08_load_reporting_model.sql
    ├── 09_verify_reporting_model.sql
    └── examples/

• Windows 11 or another environment with PowerShell 7
• Docker Desktop or Docker Engine with Compose
• Git
• optional: DataGrip or another SQL Server client

Copy-Item .env.example .env
notepad.exe .env

Replace the password placeholder before starting the lab. .env is ignored by Git and must remain local.

& ".\scripts\Initialize-Lab.ps1"

For later runs, the image pull can be skipped:

& ".\scripts\Initialize-Lab.ps1" -SkipImagePull

& ".\scripts\Test-LabConnection.ps1"
& ".\scripts\Test-DataIntegrity.ps1"
& ".\scripts\Test-ReportingModel.ps1"

See docs/datagrip-workflow.md for the detailed client procedure.

Preserve the container and volume:

& ".\scripts\Stop-Lab.ps1"

Remove the container and Compose network while preserving the named volume:

& ".\scripts\Stop-Lab.ps1" -RemoveContainer

The normal workflow deliberately does not remove the named volume.

This repository does not claim:

• production deployment or high availability
• production user and role design
• a general-purpose migration framework
• slowly changing dimension handling
• incremental, watermark-based or deletion-aware warehouse loading
• database backup and restore automation
• a finished Power BI report
• Azure or Microsoft Fabric deployment
• a deployment or release pipeline

The star schema is a compact reporting layer for one deterministic synthetic dataset, not a production enterprise warehouse. The GitHub Actions workflow is an integration test, not a deployment pipeline.

1. document and validate a Power BI connection against the reporting layer
2. build a compact Power BI report with explicit KPI definitions
3. add screenshots or a short demo for portfolio review
4. evaluate versioned migrations and incremental loading when project scope creates a concrete need

Only synthetic training data belongs in this repository. The following remain excluded:

• .env files and real credentials
• personal or customer data
• private database dumps
• SQL Server database volumes
• local exports and backup files

See docs/project-notes.md, docs/reporting-model.md and docs/ci-workflow.md for design details and validation evidence.