Build a Multi-ERP Platform Foundation

Build a portable SQL Server foundation with idempotent scripts and GitHub CI.

Introduction

30 Second Summary

You will build a repeatable foundation that another engineer can run and trust.

In this project, you will create a public GitHub repository with portable automation and a rerunnable SQL Server database bootstrap. You will deliver the foundation through a protected pull request with an independent CI check.

What You'll Build

Picture a recruiter opening your repository to see portable validation pass, database boundaries appear from one script, and a protected change reach main through a clean pull request.

By the end of this project, you'll have:

  • Portable validation that runs successfully from any working directory.
  • Rerunnable database provisioning that creates the platform database and its domain boundaries without duplicate-object failures.
  • A protected delivery workflow where GitHub Actions validates the same repository contract before a squash merge.
  • Secret Mission: an optional challenge to push your portability and recovery skills further.

Are there any prerequisites?

You need Windows 11 Pro on an x64 processor with administrator access.

You also need internet access and a GitHub account.

Before We Start

This step commits you to building a reproducible platform foundation where portable automation and rerunnable database provisioning prevent workstation-specific drift.

Set Up the Supported Toolchain

Your recruiter-shareable foundation needs a supported SQL Server instance. It also needs an isolated Python environment.

SQL Server 2025 Enterprise Developer is unsupported on Windows 11 Pro. The pinned database adapter officially supports Python 3.13, so you will keep Python 3.14 installed while adding Python 3.13.15.

In this step, get ready to:
  • Install SQL Server 2025 Standard Developer as the local default instance.
  • Install SQL Server Management Studio 22 with Microsoft ODBC Driver 18 for SQL Server.
  • Create an isolated .venv with dbt-sqlserver==1.11.1.
Install SQL Server Standard Developer

SQL Server 2025 Standard Developer provides the local database engine used throughout the project series. A default instance gives later scripts one predictable local target.

Why Standard Developer?

Windows 11 Pro supports SQL Server 2025 Standard. Standard Developer includes the Standard edition features for non-production development.

This choice teaches server administration while matching your workstation's supported path.

  • Visit the official SQL Server downloads page.
  • Select Download Standard Developer edition in the SQL Server 2025 Developer section.
  • Run the downloaded installer with administrator access.

What Will the Installer Ask?

The installation wizard asks which engine features you need. It also asks how the local instance should identify administrators.

The following choices create the supported baseline used by every later database step.

  • Continue to the Feature Selection page.
  • Select Database Engine Services.
  • Continue to the Instance Configuration page.
  • Select Default instance.
  • Continue to the Database Engine Configuration page.
  • Select Windows Authentication.
  • Select Add Current User.
  • Complete the installation.

The completion screen should report that Database Engine Services installed successfully. Your Windows account now administers the local default instance.

Installation Did Not Complete?

Confirm that you launched the installer with administrator access. Complete any pending Windows restart before retrying.

Check that you downloaded Standard Developer. Enterprise Developer does not match the supported Windows 11 Pro path.

Ask for targeted help with troubleshooting the SQL Server installation.

Install SSMS and the ODBC driver

SQL Server Management Studio provides the workspace for running database queries. Microsoft ODBC Driver 18 gives database tools a supported connection layer.

Visual Studio Installer should report a completed SSMS 22 installation. SSMS is now available through Windows search.

  • Press the Windows key to open Windows search.
  • Type PowerShell.
  • Press Enter to open PowerShell.
  • Check for the required 64-bit driver by running this command:
Get-OdbcDriver -Name "ODBC Driver 18 for SQL Server" -Platform "64-bit"

What Does This Command Do?

The command filters installed ODBC drivers by the official SQL Server driver name. The platform filter limits the result to the required 64-bit installation.

✔️ I see the 64-bit driver

The output lists ODBC Driver 18 for SQL Server with the 64-bit platform. Your connection driver is ready.

ⓧ The driver is missing

An empty result means the required driver is missing. Install the Windows x64 package before continuing.

  • Visit the official Microsoft ODBC Driver download page.
  • Download Microsoft ODBC Driver 18 for SQL Server for Windows x64.
  • Run the downloaded installer with administrator access.
  • Complete the installation.
  • Return to PowerShell.
  • Rerun the driver check shown above.

PowerShell Cannot Find the Driver?

Confirm that you installed the Windows x64 package. Start a fresh PowerShell session after the installation finishes.

Ask for help with diagnosing the missing ODBC driver.

SSMS can now test whether the database engine accepts your Windows identity. The version query also confirms the installed SQL Server edition.

  • Press the Windows key to open Windows search.
  • Type SQL Server Management Studio.
  • Press Enter to open SSMS.
  • Connect to the local default instance with Windows Authentication.
  • Select New Query in the toolbar.

Before you run the query, what edition information do you expect the result to include?

  • Verify the installed database engine by running this query:
SELECT @@VERSION;

What Does This Query Do?

The query returns one string containing the SQL Server version. The same string identifies the installed edition.

The result should include SQL Server 2025 Standard Developer information. This proves the local engine is running under the intended edition.

Cannot Run the Version Query?

Confirm that SSMS is connected to the local default instance. Check that the connection uses Windows Authentication.

Ask for help with diagnosing the SSMS connection.

Create the Python environment

A virtual environment keeps project packages separate from your global Python installation. Python 3.13.15 can sit beside Python 3.14 without replacing it.

  • Check the Python runtimes available through the launcher by running this command in PowerShell:
py list

What Does This Command Do?

The Python launcher lists every runtime it can select. This check reveals whether Python 3.13.15 is already available.

✔️ I see version 3.13.15

Python 3.13.15 is ready. Continue to the repository folder setup below.

ⓧ I see an older version

Install the required maintenance release beside your existing runtimes.

  • Install Python 3.13.15 by running this command:
py install 3.13.15

What Does This Command Do?

Python Install Manager adds the requested runtime. Existing Python releases remain available.

ⓧ Command not found

The Python launcher is unavailable in this PowerShell session. Install Python Install Manager before adding the required runtime.

py install 3.13.15

What Does This Command Do?

Python Install Manager downloads the requested runtime. It registers the runtime for selection through the Python launcher.

The virtual environment belongs inside the future repository folder. Creating that folder now gives the interpreter a stable project-local home.

  • Create the multi-erp-data-platform folder on your Desktop by running these commands:
New-Item -Path "$home\Desktop\multi-erp-data-platform" -ItemType Directory
Set-Location -Path "$home\Desktop\multi-erp-data-platform"

What Do These Commands Do?

The first command creates the future repository folder on your Desktop. The second command moves the PowerShell session into that folder.

PowerShell should print the new directory details. The prompt path should now end with multi-erp-data-platform.

  • Create the repository-local environment with Python 3.13 by running this command:
py -V:3.13 -m venv .venv

What Does This Command Do?

The version selector launches the installed Python 3.13 runtime. The environment module creates an isolated interpreter inside .venv.

The .venv folder should now appear inside multi-erp-data-platform.

The Environment Was Not Created?

Confirm that the PowerShell prompt ends with multi-erp-data-platform. Rerun the Python runtime check if the launcher cannot select Python 3.13.

Ask for help with creating the virtual environment.

  • Install the pinned SQL Server adapter inside .venv by running this command:
.\.venv\Scripts\python.exe -m pip install dbt-sqlserver==1.11.1

What Does This Command Do?

The command uses the Python executable stored inside .venv. The exact adapter pin keeps the project on the tested 1.11.1 release.

The installation output should finish successfully with dbt-sqlserver==1.11.1 installed into the local environment.

The Adapter Installation Failed?

Confirm that the command starts with the interpreter inside .venv. A global interpreter can select an unsupported runtime.

Check your internet connection before retrying the package installation.

Ask for help with diagnosing the adapter installation.

Before you run the final check, which Python release should the repository-local interpreter report?

  • Verify the interpreter inside .venv by running this command:
.\.venv\Scripts\python.exe --version

What Does This Command Do?

The path calls the interpreter stored inside the repository-local environment. This bypasses the global Python 3.14 runtime.

PowerShell should report Python 3.13.15. Together with the SSMS result and ODBC output, this confirms the supported local toolchain.

Your supported database stack is ready. Next, you will turn the empty project folder into a visible monorepo with its first structural validator.

Build the Repository Baseline

Your supported toolchain now needs a repository that makes the platform structure visible. This baseline turns the roadmap into files that Git can track.

The first validator deliberately treats the current PowerShell location as the repository root. You will test that assumption from two locations to expose its portability defect.

In this step, get ready to:
  • Create the monorepo structure and baseline documentation.
  • Add a repository validator that depends on its launch location.
  • Commit the baseline on main and switch to the foundation branch.
Create the monorepo structure

The six component directories separate each delivery stage. Shared directories hold automation, database code, configuration guidance, documentation, and repository governance.

  • Return to the local folder that contains your .venv environment.
  • Rename that folder to multi-erp-data-platform using File Explorer.
  • Open the renamed multi-erp-data-platform folder in Visual Studio Code.
  • Create engineering-foundation using the new-folder control in the Explorer sidebar.
  • Create multi-erp-ingestion using the new-folder control in the Explorer sidebar.
  • Create dimensional-warehouse using the new-folder control in the Explorer sidebar.

The delivery roadmap is taking shape

The Explorer sidebar now shows the first three delivery areas as separate boundaries. Each area can evolve without turning the repository into one undifferentiated folder.

  • Create incremental-data-operations using the new-folder control in the Explorer sidebar.
  • Create master-data-governance using the new-folder control in the Explorer sidebar.
  • Create executive-data-products using the new-folder control in the Explorer sidebar.
  • Create scripts using the new-folder control in the Explorer sidebar.
  • Create database using the new-folder control in the Explorer sidebar.
  • Create config using the new-folder control in the Explorer sidebar.

Shared concerns have clear homes

The new shared directories separate executable automation from database provisioning. Configuration guidance also has its own boundary.

  • Create docs using the new-folder control in the Explorer sidebar.
  • Create .github using the new-folder control in the Explorer sidebar.
  • Confirm that all eleven directories appear under multi-erp-data-platform in the Explorer sidebar.
Add the baseline files

The root files explain the platform roadmap and pin the stable adapter dependency. Ignore rules keep local environments, secrets, generated outputs, and database files outside version control.

  • Create README.md inside multi-erp-data-platform using the new-file control in the Explorer sidebar.
  • Paste the repository overview below into README.md.
# multi-erp-data-platform

A portfolio data platform that consolidates synthetic ERP data from Estonia, Latvia, and Lithuania into governed analytical products.

## Delivery roadmap

1. `engineering-foundation`
2. `multi-erp-ingestion`
3. `dimensional-warehouse`
4. `incremental-data-operations`
5. `master-data-governance`
6. `executive-data-products`

## Engineering principles

- Changes reach `main` through short-lived branches and pull requests.
- Automation resolves files relative to the repository, not a workstation path.
- Database changes are version controlled and safe to rerun.
- Secrets, generated files, local environments, and database files are not committed.
- CI receives read-only repository contents unless a job proves it needs more.

## Local validation

From any PowerShell working directory, invoke `scripts/Test-Repository.ps1` by its path. A successful run prints `Repository structure is valid.`

## Database bootstrap

Open `database/bootstrap.sql` in SQL Server Management Studio and execute it against the local default instance. It creates the `MultiErpDataPlatform` database and its initial schemas only when they are absent.

What does this file establish?

The root README defines the delivery roadmap and engineering principles. It also gives future contributors one entry point for validation and database provisioning.

  • Save README.md.
  • Confirm that the Markdown preview shows six numbered roadmap entries.
  • Create .gitignore inside multi-erp-data-platform using the new-file control in the Explorer sidebar.
  • Paste the ignore rules below into .gitignore.
.venv/
__pycache__/
*.py[cod]
.env
.env.*
!*.example
config/local.psd1
logs/
target/
dbt_packages/
*.mdf
*.ndf
*.ldf
*.bak
*.trn
.vscode/settings.json
.DS_Store
Thumbs.db

What do these rules protect?

These patterns exclude the local virtual environment and generated data tooling outputs. They also keep credentials, machine settings, and SQL Server data files out of Git.

  • Save .gitignore.
  • Confirm that .venv appears dimmed or hidden from source control in the Explorer sidebar.
  • Create requirements.txt inside multi-erp-data-platform using the new-file control in the Explorer sidebar.
  • Paste the dependency pin below into requirements.txt.
dbt-sqlserver==1.11.1

Why pin the adapter?

The exact dbt-sqlserver version makes dependency installation reproducible. Every future environment starts from the same adapter release.

  • Save requirements.txt.
  • Confirm that the file contains exactly one dependency line.

Each component README records the boundary that its directory owns. These short contracts prevent later projects from scattering responsibilities across unrelated folders.

  • Create engineering-foundation/README.md using the new-file control in the Explorer sidebar.
  • Paste the component contract below into engineering-foundation/README.md.
# Engineering Foundation

This component owns the supported workstation baseline, repository governance, portable validation, and initial SQL Server platform bootstrap.

It feeds every later component and must remain safe to rerun and safe to relocate.

What does this component own?

The engineering foundation owns the workstation baseline and repository governance. It also owns portable validation and the initial database bootstrap.

  • Save engineering-foundation/README.md.
  • Confirm that its preview names portability and rerun safety.
  • Create multi-erp-ingestion/README.md using the new-file control in the Explorer sidebar.
  • Paste the component contract below into multi-erp-ingestion/README.md.
# Multi-ERP Ingestion

Reserved for audited ingestion from synthetic Estonia, Latvia, and Lithuania ERP sources.

What does this component reserve?

This boundary reserves audited ingestion for the three synthetic regional ERP sources.

  • Save multi-erp-ingestion/README.md.
  • Confirm that the preview names Estonia, Latvia, and Lithuania.
  • Create dimensional-warehouse/README.md using the new-file control in the Explorer sidebar.
  • Paste the component contract below into dimensional-warehouse/README.md.
# Dimensional Warehouse

Reserved for conformed dimensions and business-grain fact tables.

What does this component reserve?

This boundary reserves conformed dimensions and fact tables at defined business grains.

  • Save dimensional-warehouse/README.md.
  • Confirm that the preview names dimensions and fact tables.
  • Create incremental-data-operations/README.md using the new-file control in the Explorer sidebar.
  • Paste the component contract below into incremental-data-operations/README.md.
# Incremental Data Operations

Reserved for incremental loading, history tracking, reconciliation, recovery, testing, and documentation.

What does this component reserve?

This boundary reserves repeatable loading operations and their quality controls. Recovery and reconciliation belong beside the loading process.

  • Save incremental-data-operations/README.md.
  • Confirm that the preview names recovery and reconciliation.
  • Create master-data-governance/README.md using the new-file control in the Explorer sidebar.
  • Paste the component contract below into master-data-governance/README.md.
# Master Data Governance

Reserved for matching, survivorship, golden records, auditability, and security.

What does this component reserve?

This boundary reserves master-data matching and survivorship. It also keeps auditability and security inside the governance scope.

  • Save master-data-governance/README.md.
  • Confirm that the preview names golden records.
  • Create executive-data-products/README.md using the new-file control in the Explorer sidebar.
  • Paste the component contract below into executive-data-products/README.md.
# Executive Data Products

Reserved for governed business marts and Power BI reporting.

What does this component reserve?

This boundary reserves governed marts and reporting for executive consumers.

  • Save executive-data-products/README.md.
  • Confirm that the preview names business marts and Power BI reporting.

The shared documentation files define where safe local guidance and engineering evidence belong. The pull-request template turns future changes into reviewable engineering decisions.

  • Create config/README.md using the new-file control in the Explorer sidebar.
  • Paste the configuration guidance below into config/README.md.
# Local Configuration

Commit safe examples only. Keep machine-specific configuration in `config/local.psd1`, which Git ignores.

What belongs in configuration?

Safe examples can enter version control. Machine-specific values stay in the ignored local configuration file.

  • Save config/README.md.
  • Confirm that the preview names config/local.psd1.
  • Create docs/README.md using the new-file control in the Explorer sidebar.
  • Paste the documentation guidance below into docs/README.md.
# Documentation

Architecture decisions, data contracts, runbooks, and verification evidence belong here.

What belongs in documentation?

Architecture decisions and data contracts belong beside operational runbooks. Verification evidence stays reviewable inside the repository.

  • Save docs/README.md.
  • Confirm that the preview names verification evidence.
  • Create .github/pull_request_template.md using the new-file control in the Explorer sidebar.
  • Paste the review template below into .github/pull_request_template.md.
## Purpose

Describe the problem and the outcome of this change.

## Scope

- In scope:
- Out of scope:

## Validation evidence

- [ ] Local repository validation passed
- [ ] CI passed
- [ ] Database changes were rerun successfully, if applicable

## Data impact

Describe affected sources, schemas, tables, grains, keys, history, and reconciliation checks. Write `None` when not applicable.

## Security and privacy impact

Describe credentials, permissions, personal data, residency, and logging implications. Write `None` when not applicable.

## Rollback

Describe how to reverse the change without losing trusted data.

What does the template capture?

The template records purpose, scope, evidence, data impact, security impact, and rollback. Those prompts turn a code change into an engineering decision that another person can inspect.

  • Save .github/pull_request_template.md.
  • Confirm that the preview shows three validation checkboxes.

The database file places the future platform boundary under version control. Its guarded statements make the database and five schemas safe to create when absent.

  • Create database/bootstrap.sql using the new-file control in the Explorer sidebar.
  • Paste the bootstrap contract below into database/bootstrap.sql.
USE [master];
GO

IF DB_ID(N'MultiErpDataPlatform') IS NULL
BEGIN
    EXEC(N'CREATE DATABASE [MultiErpDataPlatform];');
END;
GO

USE [MultiErpDataPlatform];
GO

IF SCHEMA_ID(N'raw') IS NULL
    EXEC(N'CREATE SCHEMA [raw] AUTHORIZATION [dbo];');
GO

IF SCHEMA_ID(N'staging') IS NULL
    EXEC(N'CREATE SCHEMA [staging] AUTHORIZATION [dbo];');
GO

IF SCHEMA_ID(N'analytics') IS NULL
    EXEC(N'CREATE SCHEMA [analytics] AUTHORIZATION [dbo];');
GO

IF SCHEMA_ID(N'mdm') IS NULL
    EXEC(N'CREATE SCHEMA [mdm] AUTHORIZATION [dbo];');
GO

IF SCHEMA_ID(N'ops') IS NULL
    EXEC(N'CREATE SCHEMA [ops] AUTHORIZATION [dbo];');
GO

SELECT name AS schema_name
FROM sys.schemas
WHERE name IN (N'raw', N'staging', N'analytics', N'mdm', N'ops')
ORDER BY name;
GO

What does the bootstrap define?

The script guards database and schema creation with existence checks. Its final query exposes the intended schema inventory after execution.

  • Save database/bootstrap.sql.
  • Confirm that Visual Studio Code shows five SCHEMA_ID checks.
Add and test the location-dependent validator

A repository validator converts the folder layout into an executable contract. This first version assigns the current PowerShell location to $repoRoot, so its result depends on where you launch it.

  • Create scripts/Test-Repository.ps1 using the new-file control in the Explorer sidebar.
  • Paste the validator below into scripts/Test-Repository.ps1.
$ErrorActionPreference = "Stop"

$repoRoot = (Get-Location).Path

$requiredPaths = @(
    "README.md",
    ".gitignore",
    "requirements.txt",
    "database/bootstrap.sql",
    "engineering-foundation/README.md",
    "multi-erp-ingestion/README.md",
    "dimensional-warehouse/README.md",
    "incremental-data-operations/README.md",
    "master-data-governance/README.md",
    "executive-data-products/README.md",
    "config/README.md",
    "docs/README.md",
    ".github/pull_request_template.md"
)

$missingPaths = foreach ($relativePath in $requiredPaths) {
    $candidate = Join-Path -Path $repoRoot -ChildPath $relativePath
    if (-not (Test-Path -LiteralPath $candidate)) {
        $relativePath
    }
}

if ($missingPaths.Count -gt 0) {
    Write-Host "Repository validation failed. Missing paths:"
    $missingPaths | ForEach-Object { Write-Host " - $_" }
    exit 1
}

Write-Host "Repository structure is valid."

What does this validator check?

  • The $requiredPaths array defines the repository contract.
  • Each Test-Path check records a missing file.
  • A failed contract returns exit code 1.
  • A complete contract prints Repository structure is valid..
  • Save scripts/Test-Repository.ps1.
  • Confirm that the file contains thirteen entries in $requiredPaths.

The repository contains more than thirty lines across its baseline files. Use this comparison checklist to confirm that every artifact is present.

✔️ Awesome, I've got everything!

Great. Save every open file before you initialize Git.

ⓧ I'd like to double check the full code

Scroll up and confirm that these files appear in order with the exact content shown above.

  • Check README.md, .gitignore, and requirements.txt.
  • Check all six component README.md files.
  • Check config/README.md and docs/README.md.
  • Check .github/pull_request_template.md and database/bootstrap.sql.
  • Check scripts/Test-Repository.ps1 against the validator block above.

A focused baseline commit gives the repository a clean starting point. The short-lived branch keeps later foundation work away from main until it is reviewed.

  • Initialize the repository with main as its first branch by running:
git init -b main
  • Stage the baseline files by running:
git add .
  • Record the focused baseline commit by running:
git commit -m 'message'

What did the baseline commit capture?

The commit records the complete repository contract as one starting snapshot. Future changes can now show a focused difference from that baseline.

  • Create the foundation branch by running:
git switch -c chore/bootstrap-repository

Having trouble creating the branch?

Confirm that the baseline commit completed before creating the branch. If Git reports an environment-specific problem, ask for help diagnosing the Git output.

The validator is ready for its designed test. Before you run it from the repository root, what result do you predict from a complete file tree?

  • Run the validator from multi-erp-data-platform using:
.\scripts\Test-Repository.ps1

What should you see?

The terminal prints Repository structure is valid. because the current location contains every required path.

Now change only the launch location. Before you rerun the validator, do you expect the same files to pass from the parent directory?

  • Move the PowerShell terminal to the parent directory by running:
Set-Location -Path .. -PassThru
  • Launch the repository validator from the parent directory by running:
.\multi-erp-data-platform\scripts\Test-Repository.ps1

The failure is intentional

The terminal prints Repository validation failed. Missing paths: followed by repository paths. The files still exist, so this failure proves that $repoRoot points at the launch location.

Did the parent-directory run pass?

Confirm that the terminal moved outside multi-erp-data-platform before you launched the script. If the result still differs, ask for help checking the working directory.

Your repository baseline is now tracked on chore/bootstrap-repository. Next, you will remove the launch-location assumption from its validator.

Make Validation Portable

Your validator exposed a portability defect in the previous step. Its result changes when you launch it outside the repository.

A portable PowerShell script derives paths from its own location. This gives local runs and future automation the same repository contract.

In this step, get ready to:
  • Anchor repository paths to the validator script.
  • Confirm the validator works from two launch locations.
  • Inspect and commit the portability fix.
Anchor paths to the script

The automatic variable $PSScriptRoot contains the directory of the executing script. Combining it with the parent path points to multi-erp-data-platform from any launch location.

  • Select scripts/Test-Repository.ps1 in the VS Code Explorer sidebar.
  • Find the line that assigns the current location to $repoRoot.
  • Replace that line with the portable path calculation below:
$repoRoot = (Resolve-Path (Join-Path -Path $PSScriptRoot -ChildPath "..")).Path

How Does This Fix Portability?

  • $PSScriptRoot starts from the directory containing the validator.
  • Join-Path combines that directory with its parent.
  • Resolve-Path produces the repository's resolved path.
  • Find ".github/pull_request_template.md" in the $requiredPaths array.
  • Add the workflow contract directly below that entry using this line:
    ".github/workflows/validate.yml"

Why Validate Every Required Path?

The array defines the repository structure that every run must enforce. The existing loop collects every missing path before returning exit code 1.

  • Save scripts/Test-Repository.ps1.
Verify both launch locations

The first check proves that the updated validator still works from its familiar launch location. The second check proves that its result no longer depends on the terminal's current directory.

  • Before running the root check, predict which success message the validator should print.
  • Verify the repository from its root by running this command:
.\scripts\Test-Repository.ps1

What Should You See?

The validator prints Repository structure is valid. because every required path resolves from the script's location.

Does the root check fail?

Check that $repoRoot uses $PSScriptRoot exactly. Confirm that every path reported by the validator exists in the repository.

Help me diagnose my portable repository validator.

  • Move to the repository's parent directory by running this command:
Set-Location -Path ..

What Does This Command Do?

Set-Location changes the terminal's current directory. The .. path selects the parent of multi-erp-data-platform.

  • Before running the parent-directory check, predict whether changing the launch location should affect the result.
  • Verify portability from the parent directory by running this command:
.\multi-erp-data-platform\scripts\Test-Repository.ps1

What Proves Portability?

The validator prints Repository structure is valid. again. The script reaches the same files without storing an absolute workstation path.

Does the parent check fail?

Confirm that the terminal is in the directory directly above multi-erp-data-platform. Check that the repository folder name matches the command exactly.

Help me fix the validator from the parent directory.

Inspect and commit the fix

A focused Git commit records the portability decision separately. Inspecting the staged diff catches accidental machine paths before they enter history.

  • Return to the multi-erp-data-platform repository by running this command:
Set-Location -Path .\multi-erp-data-platform

What Does This Command Do?

The command returns your terminal to the repository. The following Git commands now act on multi-erp-data-platform.

  • Stage the portability fix by running this command:
git add .

What Does This Command Do?

The command stages the validator update for the next commit. This creates a reviewable snapshot of the proposed change.

  • Inspect the staged portability fix by running this command:
git diff --cached

What Should You Inspect?

The diff should replace the current-directory assignment with the $PSScriptRoot calculation. It should also add .github/workflows/validate.yml to the required paths.

Confirm that the diff contains no absolute path from your workstation.

✔️ Awesome, I've got everything!

Your validator is saved. Its staged diff contains only the intended portability changes.

ⓧ I'd like to double check the full code

The complete script exceeds the code-block line limit. Scroll up through scripts/Test-Repository.ps1 and confirm these sections appear in order:

  • Set $ErrorActionPreference to "Stop".
  • Derive $repoRoot from $PSScriptRoot with Join-Path and Resolve-Path.
  • List every required repository path in $requiredPaths.
  • Collect missing entries with Test-Path.
  • Print all missing paths before returning exit code 1.
  • Print Repository structure is valid. after a successful check.
  • Commit the inspected portability fix by running this command:
git commit -m "Make validation portable"

What Does This Command Do?

The command records the staged validator changes on chore/bootstrap-repository. The commit message identifies portability as the single purpose.

Does Git report nothing to commit?

Confirm that you saved scripts/Test-Repository.ps1 before staging it. Check that your terminal is inside multi-erp-data-platform.

Help me inspect why my portability commit is empty.

Your validator now carries its own location context. Next, you can turn the database boundary into a rerunnable platform contract.

Bootstrap the Platform Database

Your portable validator now proves the repository contract from any launch location.

Manual database clicks leave no reviewable record. You will turn the platform boundary into a rerunnable T-SQL contract for SQL Server. You will prove that contract in SQL Server Management Studio (SSMS).

In this step, get ready to:
  • Create the platform database only when it is absent.
  • Establish five guarded domain schemas.
  • Prove rerun safety through a committed database contract.
Create the database safely

Idempotent DDL checks the current database state before making a change. The database guard makes repeated execution safe.

  • In SSMS from earlier, load database/bootstrap.sql from the multi-erp-data-platform folder.
  • Replace the file contents with the database creation chunk below.
USE [master];
GO

IF DB_ID(N'MultiErpDataPlatform') IS NULL
BEGIN
    EXEC(N'CREATE DATABASE [MultiErpDataPlatform];');
END;
GO

USE [MultiErpDataPlatform];
GO

What Does This Code Do?

  • The first batch uses master as the context for checking the database.
  • DB_ID returns a database identifier when MultiErpDataPlatform already exists.
  • The guarded block creates the database only when the identifier is absent.
  • The final batch changes the context to MultiErpDataPlatform for the remaining work.
  • Save database/bootstrap.sql.
  • Run the query in SSMS.
  • Refresh the database list in SSMS.

You should see one database named MultiErpDataPlatform.

Database Missing?

  • Confirm that the query is connected to your local default SQL Server instance.
  • Confirm that you ran every batch through the final GO line.
  • Help me diagnose why the database creation chunk failed in SSMS.
Establish the domain schemas

Schemas give each data-platform responsibility a named boundary. Each guard creates its schema once while keeping ownership with dbo.

  • Place your cursor after the final GO line in database/bootstrap.sql.
  • Add the guarded schema definitions below.
IF SCHEMA_ID(N'raw') IS NULL
    EXEC(N'CREATE SCHEMA [raw] AUTHORIZATION [dbo];');
GO

IF SCHEMA_ID(N'staging') IS NULL
    EXEC(N'CREATE SCHEMA [staging] AUTHORIZATION [dbo];');
GO

IF SCHEMA_ID(N'analytics') IS NULL
    EXEC(N'CREATE SCHEMA [analytics] AUTHORIZATION [dbo];');
GO

IF SCHEMA_ID(N'mdm') IS NULL
    EXEC(N'CREATE SCHEMA [mdm] AUTHORIZATION [dbo];');
GO

IF SCHEMA_ID(N'ops') IS NULL
    EXEC(N'CREATE SCHEMA [ops] AUTHORIZATION [dbo];');
GO

How Do the Boundaries Work?

  • raw holds source landing data.
  • staging supports transformation work.
  • analytics provides the reporting boundary.
  • mdm owns master-data responsibilities.
  • ops provides the operational namespace.
  • Save database/bootstrap.sql.
  • Run the complete query in SSMS.
  • Expand the schema list under MultiErpDataPlatform.

You should see raw, staging, analytics, mdm, and ops in the database.

Schemas Missing?

  • Confirm that the schema chunk appears after the batch that changes context to MultiErpDataPlatform.
  • Confirm that every schema definition ends with its own batch separator.
  • Help me find why one or more guarded schemas are missing.
Prove rerun safety and commit the contract

A schema inventory turns the bootstrap result into visible evidence. Running the completed script twice tests whether every guard prevents duplicate-object failures.

  • Place your cursor after the final schema batch in database/bootstrap.sql.
  • Add the schema inventory query below.
SELECT name AS schema_name
FROM sys.schemas
WHERE name IN (N'raw', N'staging', N'analytics', N'mdm', N'ops')
ORDER BY name;
GO

What Does the Inventory Prove?

  • The query reads the database schema catalog.
  • The filter limits the result to the five platform domains.
  • The ordering keeps repeated verification easy to compare.
  • Save database/bootstrap.sql.
  • Predict what the schema inventory will show after the second run.
  • Run the completed script once in SSMS.
  • Run the completed script a second time in SSMS.

Both runs should complete successfully. The inventory should show exactly one row for each of the five domain schemas.

Second Run Failing?

  • Compare every database guard with the matching creation statement.
  • Compare every schema guard with the schema name inside its creation statement.
  • Help me diagnose why database/bootstrap.sql succeeds once but fails on its second run.

✔️ Awesome, I've got everything!

Great. Double-check that you saved database/bootstrap.sql after the successful second run.

ⓧ I'd like to double check the full code

The completed file exceeds the code-block limit. Scroll up and confirm these chunks appear in order:

  1. The guarded MultiErpDataPlatform database creation chunk.
  2. The guarded definitions for raw, staging, analytics, mdm, and ops.
  3. The ordered schema inventory query.
  • Stage only database/bootstrap.sql with Git.
  • Commit the staged file with a message describing the executable database contract.
  • Confirm that the commit contains only database/bootstrap.sql.

Your platform boundary is now executable from source control. Next, you will make independent validation a requirement before changes reach main.

Merge Through a Protected Pull Request

Your database contract is version controlled. Its rerunnable behavior is proven locally.

Local success cannot protect main. In this step, GitHub Actions independently validates each pull request before a protected branch accepts it.

In this step, get ready to:
  • Add pull request guidance and Windows repository validation.
  • Publish the foundation through a pull request with passing CI.
  • Protect main before squash-merging the foundation.
Add the pull request template

A pull request template turns engineering evidence into a repeatable checklist. It prompts every future change to explain its purpose, impact, validation, security considerations, and rollback path.

  • In the VS Code Explorer, create .github/pull_request_template.md inside the multi-erp-data-platform folder.
  • Add the repository review checklist by pasting this content:
## Purpose

Describe the problem and the outcome of this change.

## Scope

- In scope:
- Out of scope:

## Validation evidence

- [ ] Local repository validation passed
- [ ] CI passed
- [ ] Database changes were rerun successfully, if applicable

## Data impact

Describe affected sources, schemas, tables, grains, keys, history, and reconciliation checks. Write `None` when not applicable.

## Security and privacy impact

Describe credentials, permissions, personal data, residency, and logging implications. Write `None` when not applicable.

## Rollback

Describe how to reverse the change without losing trusted data.

What does this template capture?

  • The purpose and scope sections define the intended change boundary.
  • The validation checklist records local, CI, and database evidence.
  • The impact sections expose data and security consequences before merging.
  • The rollback section records a safe recovery path.
  • Save .github/pull_request_template.md.
  • Confirm the VS Code Explorer lists pull_request_template.md under .github.

Template missing from the repository?

Check that the file is directly inside .github. Confirm its name uses underscores exactly as shown.

Ask for help checking the template path.

✔️ Awesome, I've got everything!

Great. Double-check that you saved the pull request template.

ⓧ I'd like to double check the full code

## Purpose

Describe the problem and the outcome of this change.

## Scope

- In scope:
- Out of scope:

## Validation evidence

- [ ] Local repository validation passed
- [ ] CI passed
- [ ] Database changes were rerun successfully, if applicable

## Data impact

Describe affected sources, schemas, tables, grains, keys, history, and reconciliation checks. Write `None` when not applicable.

## Security and privacy impact

Describe credentials, permissions, personal data, residency, and logging implications. Write `None` when not applicable.

## Rollback

Describe how to reverse the change without losing trusted data.
Add the Windows validation workflow

The CI workflow runs the same portable validator used locally. Read-only repository permissions keep the job aligned with least privilege.

  • In the VS Code Explorer, create .github/workflows/validate.yml inside the multi-erp-data-platform folder.
  • Add the Windows validation workflow by pasting this content:
name: Repository Validation

on:
  pull_request:
    branches:
      - main
  push:
    branches:
      - main

permissions:
  contents: read

jobs:
  repository-validation:
    runs-on: windows-latest
    steps:
      - name: Check out repository
        uses: actions/checkout@v7
      - name: Validate portable repository structure
        shell: pwsh
        run: ./scripts/Test-Repository.ps1

What does this workflow do?

  • The triggers run validation for pull requests targeting main and pushes to main.
  • The permissions block grants read-only access to repository contents.
  • The job uses windows-latest to match the project workstation platform.
  • The final step runs the repository's portable PowerShell validator.
  • Save .github/workflows/validate.yml.
  • Predict whether the local validator recognizes both governance files.
  • Run the repository validator from the multi-erp-data-platform folder with this command:
.\scripts\Test-Repository.ps1

What should you see?

PowerShell prints Repository structure is valid.. This proves the repository contract includes the pull request template and workflow.

Repository validation failed?

Read each missing path in the output. Confirm that validate.yml is inside .github/workflows.

Ask for help comparing the reported paths.

✔️ Awesome, I've got everything!

Great. Your saved workflow now matches the repository contract.

ⓧ I'd like to double check the full code

name: Repository Validation

on:
  pull_request:
    branches:
      - main
  push:
    branches:
      - main

permissions:
  contents: read

jobs:
  repository-validation:
    runs-on: windows-latest
    steps:
      - name: Check out repository
        uses: actions/checkout@v7
      - name: Validate portable repository structure
        shell: pwsh
        run: ./scripts/Test-Repository.ps1
Publish the protected pull request

The topic branch now contains the complete foundation. Publishing both branches lets GitHub compare the proposed work against the baseline on main.

  • Create a public GitHub repository named multi-erp-data-platform.
  • Copy the new repository URL.
  • Commit the governance files and publish both branches by running these commands from the multi-erp-data-platform folder:
git add .
git commit -m 'Add repository governance and Windows validation'
git remote add origin [[YOUR_REPO_URL="https://github.com/YOUR_USERNAME/multi-erp-data-platform.git"]]
git push -u origin main
git push -u origin chore/bootstrap-repository

What do these commands do?

  • The first command stages the new governance files.
  • The second command records them on chore/bootstrap-repository.
  • The remote command connects the local repository to GitHub.
  • The push commands publish main and the topic branch.
  • Confirm the GitHub repository branch selector lists main.
  • Confirm the branch selector also lists chore/bootstrap-repository.

Push rejected?

Confirm the copied URL belongs to the empty multi-erp-data-platform repository. Check that Git can authenticate to the signed-in GitHub account.

Ask for help diagnosing the push.

A pull request records the decision behind the change. Its CI result supplies evidence from an independent Windows runner.

  • Open a pull request from chore/bootstrap-repository into main.
  • Enter Bootstrap multi-ERP data platform foundation as the pull request title.
  • Complete every section supplied by the repository template.
  • Record the successful local repository validation under validation evidence.
  • Record that database/bootstrap.sql completed twice under validation evidence.
  • Create the pull request.
  • Wait for the repository-validation check to finish.

The check passes on windows-latest. The pull request now carries local SQL evidence plus independent repository validation.

CI check failed?

Open the failed job log. Compare each reported missing path with the files committed on chore/bootstrap-repository.

Ask for help reading the workflow log.

Branch protection turns the passing check into a merge gate. Linear history keeps the final main timeline easy to inspect.

  • Open the branch protection controls for main in the repository settings.
  • Enable Require a pull request before merging.
  • Leave the approving review requirement disabled.
  • Enable Require status checks to pass before merging.
  • Select repository-validation as the required status check.
  • Enable Require linear history.
  • Keep force pushes blocked.
  • Save the branch protection settings.

Before merging, what history and branch state do you expect GitHub to show afterward?

  • Return to the bootstrap pull request.
  • Confirm repository-validation is passing.
  • Select Squash and merge.
  • Select Confirm squash and merge.
  • Delete chore/bootstrap-repository.
  • Open the commit history for main.

The complete foundation now appears on main as one squash-merged foundation commit. The topic branch is gone.

Secret mission

Prove Relocation and Rerun Safety

Move the repository to a different parent directory. Prove that its validator and database bootstrap still work without script changes.

Clean Up Your Resources

Clean Up Your Resources

This project uses a local SQL Server instance plus local files. The required path costs $0, so keeping these resources creates no ongoing charges.

Resources you used:

  • A local SQL Server 2025 Standard Developer instance.
  • The MultiErpDataPlatform database with the raw, staging, analytics, mdm, plus ops schemas.
  • A repository-local .venv environment.
  • The public GitHub repository named multi-erp-data-platform.
  • The original local multi-erp-data-platform folder.
  • The relocated clone or copy of multi-erp-data-platform.

Keep everything running

No action needed. Choose this if you plan to continue with the multi-ERP data platform series.

  • Keep the local SQL Server instance available for multi-erp-ingestion.
  • Keep the MultiErpDataPlatform database as the foundation for later components.
  • Keep the public repository on protected main so its CI evidence remains available.
  • Keep one validated local copy of multi-erp-data-platform for future development.

The database remains ready to extend. GitHub Actions only runs when its configured repository events occur.

Pause - I'll come back to this later

Stop the local SQL Server service to free up memory. Your instance files remain available for the next project.

  • Press the Windows key to open search.
  • Search for the Windows services manager.
  • Open the services manager from the search results.
  • Select the SQL Server service whose instance identifier is MSSQLSERVER.
  • Stop the selected service.
  • Keep the multi-erp-data-platform folders on disk.
  • Keep the public GitHub repository unchanged.

The SQL Server service now shows a stopped state. The MultiErpDataPlatform database remains stored locally.

Delete - I don't want to use this again

Remove the learning database for a clean rebuild. Remove the wider toolchain only if you are abandoning the project series.

  • Return to SQL Server Management Studio from earlier.
  • Connect to the local default SQL Server instance.
  • Expand the database list in the object explorer.
  • Select the MultiErpDataPlatform database.
  • Use the database deletion action to remove the selected database.
  • Refresh the database list.

The MultiErpDataPlatform database no longer appears. Removing it also removes its five domain schemas.

  • Open File Explorer from the Windows taskbar.
  • Return to the parent directory containing the original multi-erp-data-platform folder.
  • Delete the original multi-erp-data-platform folder.
  • Return to the different parent directory used for the relocation test.
  • Delete the relocated multi-erp-data-platform folder.
  • Open the public multi-erp-data-platform repository on GitHub.
  • Use the repository deletion control in its settings to remove the public repository.

The local folders no longer appear in File Explorer. The public repository URL no longer opens the project.

  • Open Windows installed-app management if you are abandoning the series.
  • Uninstall SQL Server 2025 Standard Developer.
  • Uninstall SQL Server Management Studio 22.
  • Uninstall Python 3.13.15.

Nice Work!

Nice Work!

Nice work! You've built the first engineering foundation in your multi-ERP data platform series.

You've learned how to:

  • Build portable automation that resolves files relative to scripts/Test-Repository.ps1.
  • Provision SQL Server database boundaries through an idempotent bootstrap in database/bootstrap.sql.
  • Deliver the foundation through GitHub Flow with a protected pull request backed by a passing Windows CI/CD check.
  • Secret Mission: Prove portability by validating a relocated clone from an unrelated directory. Confirm rerun safety by executing database/bootstrap.sql without duplicate-object errors.

Ready to quiz yourself? Let's check what stuck.