How Do You Keep Dev, Staging and Prod Consistent?

A data warehouse automation tool keeps development, staging and production consistent by putting two gates in the build, so consistency doesn't depend on someone being careful at 6 p.m. on release day. The first gate refuses bad metadata. In BimlFlex, nearly 100 error-level checks run before any code is generated, and a single failure stops the build. The second gate is the output itself. Every successful build writes its own deploy scripts, ordered by warehouse layer, with the environment-specific values filled in from settings. Promoting to production means pointing those settings at production and building again. Nobody opens a script and edits it. One boundary up front: BimlFlex writes the scripts. Your release pipeline runs them.

‍

(A note on words: "staging" here means the staging environment, the one between dev and prod. BimlFlex also has a staging layer inside the warehouse, and it shows up later in this post. Context will keep them apart.)

‍

We've written before about versioning BimlFlex projects in Git and about codifying contracts and gates at design time. This post sits between those two. It covers what the build does with your metadata once it's committed, and what it hands to your pipeline afterwards.

‍

Where Drift Comes From

‍

Environments rarely drift because of one big mistake. It's a stack of small ones. A deploy script gets a hand edit for production that never makes it back to source control. A hotfix lands in prod and nowhere else. The data mart database is published before the Data Vault it reads from, fails, and someone reruns it manually in a different order. Or a generator runs against a model with a broken reference and produces code that compiles in dev and fails in test.

‍

Each of those has the same shape: a person stepping in between the model and the environment. So make that step unnecessary. If everything that reaches an environment is generated, and generated only from metadata that passed validation, the remaining differences between environments are just values. Values can live in metadata. Hand edits can't.

‍

Gate One: The Build Refuses Bad Metadata

‍

Before BimlFlex generates a single table or load, it validates the whole model. The checks run in a fixed sequence across configurations, connections (including the BimlCatalog connection), projects, objects, columns and parameters. Close to a hundred of them report at error severity. A couple more are warnings, and warnings don't stop anything.

‍

Some examples of what an error-level check catches:

‍

• A column and its reference column disagree on data type, length, precision or scale (flat-file objects are exempt).
• A project has no batch.
• The BimlCatalog connection isn't defined.
• An object has Delete Detection turned on, but no column is marked as a Source Key or a non-derived Primary Key.
• On Databricks, Use SQL Scripting and Use Temporary Views are both enabled, which the platform doesn't allow.

‍

The message tells you which rule fired and where:

‍

COL_26005007 - Column: 'CustomerCode' - The data types of a column and its reference column must match. 

‍

(CustomerCode is an example column name. The code and text are what the build reports.)

‍

When any error-level check fails, the build generates nothing. No DDL, no packages, no pipelines, and no fresh deploy scripts either, because the deploy output sits behind the same gate. On a command-line MSBuild build, a reported error makes the build task fail, so a CI job stops at the build step instead of carrying half-valid output forward.

‍

That's the behavior you want. A type mismatch between a key and its reference is the kind of thing that works fine on a small dev dataset and truncates on real data in production. Catching it in the build means it can't be deployed to any environment, which also means it can't be deployed to only one of them.

‍

The BimlFlex app also validates while you edit. The build check is the backstop that runs no matter how the metadata changed: through the app, an import, a merge from another branch, or a script.

‍

Gate Two: Every Build Writes a Deploy Folder

‍

A successful build writes a Deploy folder under the output folder. Its contents depend on what the project targets:

‍

ssdt-deploy.<Database>.ps1: One per SQL Server family database (SQL Server, Azure SQL Database, Azure SQL Managed Instance, Synapse dedicated SQL pool). Builds the generated SSDT project, then publishes the dacpac with SqlPackage.

‍

_ssdt-deploy-all.ps1: Calls every per-database script in warehouse layer order.

‍

adf-deploy.<DataFactory>.ps1: Deploys the generated arm_template.json with its separate arm_template_parameters.json.

‍

adf-deploy.<DataFactory>.linkedTemplates.ps1: The linked-template alternative: uploads the template parts to a storage container, then deploys the master template.

‍

ssis-deploy.<Project>.ps1: Deploys the compiled .ispac to the SSIS catalog with the SSIS deployment wizard in silent mode.

‍

_ssis-deploy-all.ps1: Calls every SSIS project script, five seconds apart.

‍

With the SsdtIncludeNetCoreSupport setting on, each database also gets a dotnet build variant of its script.

‍

Here's the core of a generated per-database script. It stops on the first failure rather than publishing a dacpac that didn't build:

‍

$ssdtProjName = "BFX_STG" 

 

cmd.exe /c `" $($msbuildPath) $($ssdtProjPath) /target:Clean /target:Build `" 

 

if (!$?) { 

Write-Error "Build fail for: ""$ssdtProjPath""" -ErrorAction Stop 

} 

 

& "$sqlPackagePath" /Action:Publish /SourceFile:$dacpacPath /TargetConnectionString:$targetConnectionString /Properties:AllowIncompatiblePlatform=True 

 

if (!$?) { 

Write-Error "Deploy fail for: ""$dacpacPath""" -ErrorAction Stop 

} 

‍

(Database names in these examples are illustrative. The license header and path discovery lines are trimmed.)

‍

Layer Order Is Decided for You

‍

The all-databases script doesn't list databases alphabetically or in the order someone created them. It sorts by each database's integration stage, the same stage you set on its connection:

‍

1. Landing
2. Persistent Staging (PSA)
3. Staging
4. Data Vault
5. Business Vault
6. Target Staging Area (used by SSIS data warehouse and data mart templates)
7. Data Mart

‍

Anything outside those stages goes last. Source databases aren't included, because you don't deploy to a source. The BimlCatalog isn't included either. It's deployed from its own dacpac and usually has its own release cadence.

‍

A generated _ssdt-deploy-all.ps1 for a typical Data Vault project looks like this:

‍

& "C:\BimlFlex\Output\Deploy\ssdt-deploy.BFX_LND.ps1" 

& "C:\BimlFlex\Output\Deploy\ssdt-deploy.BFX_ODS.ps1" 

& "C:\BimlFlex\Output\Deploy\ssdt-deploy.BFX_STG.ps1" 

& "C:\BimlFlex\Output\Deploy\ssdt-deploy.BFX_DV.ps1" 

& "C:\BimlFlex\Output\Deploy\ssdt-deploy.BFX_BV.ps1" 

& "C:\BimlFlex\Output\Deploy\ssdt-deploy.BFX_DM.ps1" 

‍

Later layers read from earlier ones. Deploying them upstream first is what an experienced engineer would do by hand. The difference is that the script does it the same way in every environment, every time.

‍

What Changes Between Environments

‍

The parts of a deploy that legitimately differ between dev, staging and production are values, and BimlFlex reads those values from metadata:

‍

AzureSubscriptionId, AzureResourceGroup: Variables at the top of the Data Factory deploy script

‍

AzureDataFactoryName: The factoryName value in the ARM parameters file

‍

AzureDeploymentAccountName, AzureDeploymentContainer: The linked-templates deploy script

‍

SsisServer (default localhost), SsisDb (default SSISDB), SsisFolder (defaults to the project name): The SSIS deploy target path

‍

SsisCreateFolder: Adds a command that creates the catalog folder if it's missing

‍

The connection string on each connection: The SSDT publish target

‍

If a value is blank, the script says so instead of guessing. Here's a Data Factory script from a build where the Azure settings weren't filled in:

‍

# Provide your Subscription and Resource Group below, or specify them in the BimlFlex Settings 

$azureSubscriptionId = "<YourSubscriptionId>" 

$azureResourceGroup = "<YourResourceGroup>" 

 

$outputBasePath = "C:\BimlFlex\Output"; 

$deploymentLabel = "BFX-ADF-$(Get-Date -Format "yyyyMMddmmss")" 

 

$armTemplatePath = "$($outputBasePath)\DataFactories\BFX-ADF\arm_template.json" 

$armTemplateParamsPath = "$($outputBasePath)\DataFactories\BFX-ADF\arm_template_parameters.json" 

Set-AzContext -Subscription $azureSubscriptionId 

New-AzResourceGroupDeployment -Name $deploymentLabel -ResourceGroupName $azureResourceGroup -TemplateFile $armTemplatePath -TemplateParameterFile $armTemplateParamsPath 

‍

Fill in the settings, build again, and the placeholders become real values. The SSIS script works the same way:

‍

& "${env:ProgramFiles(x86)}\Microsoft SQL Server\160\DTS\Binn\isdeploymentwizard.exe" /S /SP:"C:\BimlFlex\Output\BFX_SSIS\bin\BFX_SSIS.ispac" /DS:localhost /DP:"/SSISDB/BFX_SSIS/BFX_SSIS/" 

‍

So moving from staging to production is a change to settings and connections, then a rerun of the build. The load logic comes from the same validated model in both places. Only the targets move.

‍

Two practical cautions. First, whatever you put in a connection string or a storage account key setting gets written into the generated script as plain text. Keep passwords and keys out of those values: use integrated or managed identity authentication where the platform supports it, and let your pipeline's secret store supply the rest. Second, build into a clean output folder in CI. A failed build doesn't write new scripts, and you don't want last week's scripts sitting in the folder looking current.

‍

Where BimlFlex Stops

‍

BimlFlex doesn't promote anything between environments, and it doesn't run the deploy scripts. Approvals and subscription credentials aren't its job either. That work belongs to your release pipeline, whether it's Azure DevOps, GitHub Actions or something else, and we think that's the right split. The pipeline owns when and where. The build owns what.

‍

A workable flow: commit metadata changes to source control, and have CI run the command-line build so any error-level check fails the job early. Because target servers and connection strings are written into the scripts at build time, each environment gets its own build with that environment's values. The release stage for staging runs a staging build and calls the scripts from its Deploy folder. Production does the same with production values. For the broader pattern, see CI/CD for metadata-driven automation. For the Data Factory side in detail, see building and deploying Azure Data Factory pipelines.

‍

This post covers the SQL Server family, Data Factory and SSIS outputs. Other targets have their own deployment artifacts, and those deserve their own walkthrough.

‍

If someone has to open a deploy script and change it before production, the script is where your environments start to differ.