i

Description

Coffre-fort ancien en bois précieux, renforcé de ferrures de laiton ouvragées.

Source

14GTR · original page

Licence

CC0 Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

Source control for data: SQL and Power BI

Version SQL objects so they can be replayed: idempotent scripts, state-based vs migration-based, SSDT/dacpac, Flyway, Liquibase.

In brief

• Version SQL objects so they can be replayed: idempotent scripts, state-based vs migration-based, SSDT/dacpac, Flyway, Liquibase.
• Version Power BI in a merge-friendly way: PBIP, PBIR, TMDL, diffs and merges, binary pitfalls.
• Master encodings, BOM, line endings and .gitattributes so you never corrupt an accent.
• Manage large files, secrets, reference data, installation kits and release tags.
• Know where AI helps (schema diff, documentation, review) and where it is dangerous.

Illustrative running thread: a SQL Server datamart fed by a field sales force application, with multilingual Power BI reports, delivered as installation kits.

On this page

1. What changes with data
2. What gets versioned
3. State-based and migration-based for SQL
4. Idempotent scripts
5. SSDT, dacpac, Flyway, Liquibase tools
6. Versioning Power BI (PBIP, PBIR, TMDL)
7. Diffs and merges for TMDL and report JSON
8. Encodings, BOM, and line endings
9. Large files and binaries
10. Secrets and Reference Data
11. Installation Packages and Release Tags
12. Repository Organization
13. AI Contribution
14. Summary Pitfalls
15. See also

Level: 🟡→🔴

What changes with data#

🟡 Practical level

Versioning application code is “stateless”: binaries are replaced. Versioning a database is different: the database contains state (populated tables, history, client settings) that cannot be replaced. So you must version how it evolves (migrations) as much as the target. Three consequences:

1. Each change must be replayable on an existing environment (idempotence, non-destructive behavior).
2. Two branches that modify the same table produce order-conflicting migrations, even without file conflicts.
3. Reports and the semantic model consume the schema: versioning must connect layers (monorepo, coordinated PRs, shared tags).

See also CI/CD for data: foundations, datamarts, pipelines, and reports and the matrix Versioning and Industrialization of Reports.

What gets versioned#

🟡 Practical level

State-based and migration-based for SQL#

🟡 Practical level

Two philosophies for describing schema evolution:

A common and healthy hybrid approach: target state for stateless objects (views, procedures, functions: CREATE OR ALTER), migrations for stateful objects (tables: add column, type change, data backfill).

Idempotent scripts#

🟡 Practical level

A script is idempotent if it can be replayed multiple times with the same result and without error. This is the condition for reliable deployment across environments in different states.

-- Procédure : état cible
CREATE OR ALTER PROCEDURE [dm].[p_Exemple]
AS
BEGIN
    SET NOCOUNT ON;
    -- ...
END;
GO

-- Colonne : migration idempotente
IF COL_LENGTH('dm.DIM_CLIENT', 'CLI_SEGMENT') IS NULL
    ALTER TABLE [dm].[DIM_CLIENT] ADD [CLI_SEGMENT] nvarchar(20) NULL;
GO

-- Donnée de référence : MERGE
MERGE [dbo].[_Parametre] AS T
USING (VALUES (N'SEUIL_COUVERTURE', N'0.80')) AS S([Nom], [Valeur])
   ON T.[Nom] = S.[Nom]
WHEN NOT MATCHED THEN INSERT ([Nom], [Valeur]) VALUES (S.[Nom], S.[Valeur]);
-- (pas de WHEN MATCHED UPDATE : ne pas écraser la personnalisation client)
GO

Rules:

• Protect with IF EXISTS/IF NOT EXISTS or CREATE OR ALTER; avoid unguarded DROP.
• Do not overwrite client-customized values: non-destructive installation, separate standard and specific settings.
• Order: number scripts (010_Core, 020_…) so replay order is deterministic.
• Transactions: wrap multi-step schema changes; watch out for non-transactional DDL operations.
• One script, one topic; header comments (object, version, tickets) but no correction log in the body: history is in the VCS.

SSDT, dacpac, Flyway, Liquibase tools#

🔴 Expert level

Choosing: an SSDT project is the best safety net for a complex schema (verified references, target/base comparison); Flyway/Liquibase for teams that favor explicit migration; custom scripts when the product generates a large part of the schema through programs (like a metadata-driven datamart) — provided you automate checks (syntax, order, idempotence, dry-run replay test).

# Exemple : déployer un dacpac en bloquant toute perte de données
sqlpackage /Action:Publish /SourceFile:Datamart.dacpac `
  /TargetConnectionString:"..." /p:BlockOnPossibleDataLoss=True /p:DropObjectsNotInSource=False

Versioning Power BI (PBIP, PBIR, TMDL)#

🟡 Practical level

The .pbix file is a binary zip: no diff, no merge, opaque history. The Power BI project format (PBIP) breaks the artifact into text folders:

MonRapport.pbip                      (pointeur)
MonRapport.SemanticModel/
    definition.pbism
    definition/
        model.tmdl
        relationships.tmdl
        tables/
            FAITS.tmdl
            CLIENT.tmdl
        cultures/
            fr-FR.tmdl  en-US.tmdl     (traductions)
MonRapport.Report/
    definition.pbir
    definition/
        report.json
        pages/
            pages.json
            <id-page>/page.json
            <id-page>/visuals/<id-visuel>/visual.json

• TMDL (Tabular Model Definition Language): readable text syntax for the semantic model (tables, columns, DAX measures, relationships, RLS roles, cultures). One table = one file; measures are readable line by line.
• PBIR: report definition format in JSON files (one visual = one file): targeted diffs, possible merges. The format replaces the old monolithic report.json definition (PBIR-Legacy): according to the Power BI documentation (as of 2026-10), PBIR is generally available and is the default report format; a modified then saved PBIR-Legacy report is automatically converted to PBIR.
• Git integrated into Fabric / Power BI: syncs a workspace with a Git repository (Azure DevOps or GitHub); serializes in these formats. No TFVC support: one more reason to migrate for a project that wants native integration with the service.
• Client reports linked to a model by identifier: measure/column lineageTag values ensure continuity for reports connected to them; modify a measure in place (and never delete/recreate) preserves the link. This is an invariant to protect through review.

Diffs and merges for TMDL and report JSON#

🔴 Expert level

TMDL: significant indentation (like YAML) and multi-line measures. Merges mainly fail because of:

• Modified indentation on one side (reformatting) → conflict on the entire block.
• Reordering of columns/measures by Desktop → noisy diff.
• Generated lineageTag identifiers: two concurrent additions do not mix them, but an object copy duplicates the tag (must be regenerated).
• Regenerated internal annotations (PBI_QueryRelationships, etc.).

Mitigations: single formatting conventions, edit distinct blocks (one person per table), git diff --word-diff, manual resolution then open in Desktop to validate, and keep model merges small and frequent.

Report JSON (PBIR): one visual per file limits conflicts; but positioning (x, y, width) and page identifiers change as soon as an object is moved. In a conflict, favor one side's version (entire page) rather than a fine-grained merge.

git diff --word-diff=color -- "*.tmdl"
git diff --stat main...feature -- "*.Report/*"
# Outil de fusion : configurer un mergetool pour TMDL/JSON
git config merge.tool vscode
git config mergetool.vscode.cmd 'code --wait --merge $REMOTE $LOCAL $BASE $MERGED'

Context: team of 3-15 people, multilingual enterprise reports; expert assessments (RATING).

Encodings, BOM, and line endings#

🔴 Expert level

A vital topic for multilingual content with accents: an encoding error destroys accents without an error message.

Reading: UTF-8 with BOM is, for .sql and .ps1 run on Windows, the only encoding correctly decoded in all cases; UTF-8 without BOM is the standard expected by other tools (Node, Markdown/YAML, TMDL/JSON written by Power BI Desktop). Team rule: one encoding per file type, documented, automatically checked.

Line endings: LF in the repository (normalization); CRLF on Windows disk if the tool wants it. TMDL files written by Desktop use CRLF.

Typical .gitattributes:

# Normalisation par défaut : texte détecté, LF dans le dépôt
* text=auto

# Sources SQL et PowerShell : texte
*.sql  text working-tree-encoding=UTF-8 eol=lf
*.ps1  text eol=lf

# Fichiers écrits par Power BI Desktop : ne pas modifier les fins de ligne
*.tmdl text eol=crlf
*.pbir text eol=crlf
*.json text

# Binaires : jamais touchés
*.pbix binary
*.zip  binary
*.png  binary
*.bak  binary

Expert notes:

• working-tree-encoding converts on read/write (useful for cp1252 ↔ UTF-8) but does not manage BOM for you: BOM presence must be checked with a hook or validation script.
• *.sql files “with BOM” should be detected in a pre-commit: read the first 3 bytes EF BB BF.
• Under TFVC, there is no automatic normalization; encoding is that of the stored file (a file type property: encoding detected at add). A tool that “rewrites” a file with another encoding causes a noisy changeset.
• Never read then rewrite a file with unknown encoding using a tool that assumes UTF-8: all accents in the file are corrupted, not just the edited line.
• sed -i and some tools remove CRs from CRLF files: favor binary processing (Python) or the project editor.

# Contrôle : tous les .sql ont-ils un BOM UTF-8 ?
Get-ChildItem -Recurse -Filter *.sql | ForEach-Object {
    $b = [System.IO.File]::ReadAllBytes($_.FullName) | Select-Object -First 3
    if (-not ($b[0] -eq 0xEF -and $b[1] -eq 0xBB -and $b[2] -eq 0xBF)) { $_.FullName }
}
# Écriture d'un fichier avec BOM
$enc = New-Object System.Text.UTF8Encoding $true
[System.IO.File]::WriteAllText($chemin, $contenu, $enc)

Large files and binaries#

🔴 Expert level

• Do not version: .bak backups, massive exports, Power BI caches (cache.abf), localSettings.json, bin/obj/node_modules folders.
• .pbix when unavoidable (legacy reports): Git LFS (with locking), or artifact storage outside the repository; better: convert them to PBIP.
• Sample datasets: keep small (a few MB); otherwise, use a versioned generator script instead of data.
• History size: a binary added then removed remains in history: git filter-repo to purge (02).
• TFVC accepts extensionless binaries, but the collection database grows; monitor it.

Order-of-magnitude thresholds (to adapt): alert starting around ~10 MB per file, reject above ~100 MB without LFS (host limits to be checked).

Secrets and Reference Data#

🔴 Expert level

Secrets: connection strings, passwords, storage keys, tokens. In a data project, they hide in: appsettings configuration files, deployment PowerShell scripts, Power BI data source parameters (expressions.tmdl with server and database), pipelines. Rules: integrated authentication/managed identities rather than passwords; a vault for everything else; environment-specific settings outside the repository; secret scanning on every PR (details).

Reference data (settings, value lists, translated labels):

• Version them as replayable scripts (MERGE without overwriting customer settings) or sorted, stable .csv files (columns in a fixed order, sorted by key) for a clean diff.
• Do not version personal data (GDPR); for test datasets, anonymize/synthesize.
• Separate standard (delivered, versioned) from customized/project (customer, outside product or in a dedicated folder); inheritance and priority order documented.
• Translations: single source file (label table), generated into model cultures; translation diffs must be readable (one line per key and per language).

Installation Packages and Release Tags#

🔴 Expert level

An installation package is the ordered set of scripts (SQL, PowerShell) that installs or upgrades the product. It is generated from sources and replayed on existing environments.

Invariants to guarantee:

1. Reproducibility: a version's package must be able to be regenerated exactly from the corresponding tag.
2. Deterministic order and idempotence (see above).
3. Traceability: the installed version is written in the database (version table) and in the installation log; link version ↔ tag ↔ changeset/commit.
4. Integrity: package checksum; tag signature.
5. Reliable execution: documented execution options (e.g., sqlcmd -x -X to disable variable substitution and commands); a package that “installs successfully” while corrupting accents or skipping files is worse than a clear failure: fail loudly.

Release tags:

git tag -s v9.1.2 -m "Kit 9.1.2 — consolidation fixes"
git push origin v9.1.2
git describe --tags        # v9.1.2-3-gabc1234 : 3 commits after the tag
git archive --format=zip --prefix=kit-9.1.2/ v9.1.2 -o kit-9.1.2.zip

Under TFVC: a documented reference changeset + release branch + (possibly) label; record the changeset number in release notes.

Repository Organization#

🔴 Expert level

Proposed tree structure for a datamart + BI project in a monorepo:

/Datamart/        numbered SQL scripts (010_Core, 020_..)
/Reports/         PBIP projects (SemanticModel + Report)
/Tools/           PowerShell scripts, internal tools
/Docs/            Markdown documentation
/Releases/        (optional) frozen snapshots, one folder per version
/.gitattributes   /.gitignore   /.tfignore (if TFVC)
/CHANGELOG.md     versioned business tracking

• One README per structuring folder (role, content, conventions); a README that contradicts the code is a defect just like a failing test.
• Changelog: separate the daily work log (local, not archived) from business tracking by version (archived).
• No correction log in code: VCS history is enough; the header carries version and metadata.

AI Contribution#

Prompt examples:

Here are two versions of a TMDL table (before/after). List the functional
changes (added measures, modified DAX expressions, changed relationships)
and flag any breaking risk for reports linked by lineageTag.
Do not invent anything: cite the relevant line.

Write an idempotent T-SQL script that adds column [CLI_SEGMENT] nvarchar(20)
NULL to [dm].[DIM_CLIENT] if it does not exist, without touching the data.
Add a final verification test that raises an error if the column is missing.

Limits to keep in mind: AI does not execute your migrations and does not know your data; it readily “fixes” an accent or indentation that needed to be preserved; it may suggest deleting and recreating an object whose identity matters (measure, bound column). Responsibility for production deployment remains human. See AI in Planning and Version Control Tools and the Cross-Functional Summary — AI Contribution.

Summary Pitfalls#

🔴 Expert level

See also#

• Overview of Version Control Systems · Git In Depth · TFVC in Depth · Git vs TFVC: compare, migrate, coexist · Branching Strategies
• CI/CD for data: foundations, datamarts, pipelines, and reports · CI/CD: Continuous Integration and Continuous Delivery
• BI Matrix: Versioning and Industrialization of Reports
• Cross-Cutting Power BI Good Practices · Glossary

✻ Written by Claude Sonnet 5.5