i

Description

Un long pipeline traversant une vallée de montagne

Source

U.S. Geological Survey · original page

Licence

CC0 Public domain

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

CI/CD for data: foundations, datamarts, pipelines, and reports

You will understand what makes CI/CD different when the product contains data: state persists, rollback is (almost) impossible, realistic environments are expensive and…

In brief

• You will understand what makes CI/CD different when the product contains data: state persists, rollback is (almost) impossible, and realistic environments are expensive and sensitive.
• You will learn the two database deployment philosophies (declarative state: dacpac/SSDT; versioned migrations: Flyway/Liquibase/scripts), idempotence and replay, and deployment in kits.
• You will know how to test data and transformations (schema, rule, reconciliation, and figure non-regression tests), organize environments with anonymized data, and handle volume.
• You will get an overview (as of 2026-10) of dbt, Fabric / Data Factory pipelines, Power BI reports in PBIP/TMDL, and deployment pipelines.
• You will have maturity ratings /5 by practice and a list of data-specific limits.

Prerequisite: the common foundation is in 10 CI/CD; reading 07 Methods Specific to Data Projects (DataOps section) helps.

On this page

1. 1. What changes when the product contains data
   1. 🟢 Novice — code is disposable, data is not
   2. 🟡 Practical — what stays the same
   3. 🔴 Expert — three layers to deploy, three cadences
2. 2. Versioning a database: state or migrations
   1. 🟢 Novice — two ways to describe a database in code
   2. 🟡 Practical — strengths and weaknesses
   3. 🔴 Expert — the hybrid approach, the most common in organizations
3. 3. Idempotence, replay, and restart
   1. 🟢 Novice — why everything must be replayable
   2. 🟡 Practical — idempotence patterns
   3. 🔴 Expert — MERGE, determinism, and replay windows
4. 4. Deployment in installation kits
   1. 🟢 Novice — why “kits”
   2. 🟡 Practical — anatomy of a good kit
   3. 🔴 Expert — compatibility between kit versions
5. 5. Testing data and transformations
   1. 🟢 Novice — four families of tests
   2. 🟡 Practical — transformation tests on controlled datasets
   3. 🔴 Expert — non-regression of metrics
6. 6. Environments, Realistic and Anonymized Data
   1. 🟢 Novice — the dilemma
   2. 🟡 Practical — anonymize without breaking tests
   3. 🔴 Expert — compliance and responsibility
7. 7. Data Volume and Processing Time
   1. 🟢 Novice — testing on “1,000 rows” proves nothing for 100 million
   2. 🟡 Practical — metrics to build in
   3. 🔴 Expert — data volume as a pipeline design dimension
8. 8. dbt and transformation as code
   1. 🟢 Novice — what it is
   2. 🟡 Practical — what it brings to data CI/CD
   3. 🔴 Expert — dbt limitations
9. 9. Fabric and Data Factory pipelines
   1. 🟢 Novice — orchestrating data movement
   2. 🟡 Practical — CI/CD in Fabric (as of 2026-10)
   3. 🔴 Expert — idempotence of an orchestration pipeline
10. 10. Power BI reports: PBIP, TMDL, deployment pipelines
    1. 🟢 Novice — a report is also code
    2. 🟡 Practical — what text enables
    3. 🔴 Expert — limits and subtleties
11. 11. Impossible data rollback
    1. 🟢 Novice — you can't easily “un-load”
    2. 🟡 Practical — safeguards
    3. 🔴 Expert — reversibility as an architecture criterion
12. 12. Continuous quality in production
    1. 🟢 Novice — the pipeline doesn't stop at deployment
    2. 🟡 Practical — the five pillars of data observability
    3. 🔴 Expert — the cost of noise and the service contract
13. 13. Data-specific limitations
    1. 🟢 Novice — what CI/CD does not solve here
    2. 🟡 Practical — deciding on the investment
    3. 🔴 Expert — source drift and contracts
14. 14. Practice maturity ratings
15. 15. AI Contribution
16. 16. See also

Level: 🟢→🔴

1. What changes when the product contains data#

🟢 Novice — code is disposable, data is not#

In traditional CI/CD, deploying a new version replaces the old one: the application has no state of its own, and it can be rebuilt identically. In a datamart, the product is both code (tables, procedures, reports) and state (accumulated, historized rows, sometimes impossible to rebuild because the source changed or was purged).

Five consequences, each expanded on below:

🟡 Practical — what stays the same#

The foundation principles (10 CI/CD) remain: everything versioned, reproducible, traceable, small batches, promotion of the same artifact, fast feedback. Data adds constraints; it does not invalidate anything.

🔴 Expert — three layers to deploy, three cadences#

A BI product is made up of layers with different cadences and risks:

You must adapt the level of control to the layer's risk: a report can be deployed automatically after tests; a migration that deletes a column requires review, verified backup, and a window. See the topic Lead Time and Industrialization (DataOps) and Report Versioning and Industrialization.

2. Versioning a database: state or migrations#

🟢 Novice — two ways to describe a database in code#

🟡 Practical — strengths and weaknesses#

(Expert notes for a team of 3 to 8 SQL Server developers; depends on discipline.)

The BlockOnPossibleDataLoss setting in SqlPackage is a major safeguard: it blocks deployment if the delta may cause data loss (table/column deletion, type narrowing). Documented good practice: disable it in development, keep it enabled in acceptance testing and production with the same dacpac and different publish profiles (as of 2026-10).

# Generate a deployment script WITHOUT executing it, for review (production)
sqlpackage /Action:Script `
  /SourceFile:".\bin\Release\Datamart.dacpac" `
  /TargetConnectionString:"$cnxProd" `
  /OutputPath:".\delta_prod.sql" `
  /p:BlockOnPossibleDataLoss=True `
  /p:DropObjectsNotInSource=False

# Drift report: what differs between the dacpac and the target database
sqlpackage /Action:DeployReport `
  /SourceFile:".\bin\Release\Datamart.dacpac" `
  /TargetConnectionString:"$cnxProd" `
  /OutputPath:".\drift.xml"

DropObjectsNotInSource=False is the cautious setting: the tool does not delete what is not in the project (objects created by a third party, staging tables, client-specific items).

🔴 Expert — the hybrid approach, the most common in organizations#

In practice, you combine:

1. State for objects without data or replayable objects: procedures, views, functions (CREATE OR ALTER), permissions. The repository contains the latest version.
2. Migrations for tables with data: adding a column with a default value, split, merge, type transformation, backfill.
3. An application log (table dbo._SchemaHistory or equivalent) indicating version, date, author, script fingerprint, duration, result.

Template for an idempotent and safe migration script:

-- 2026_10_07__Ajout_colonne_statut_visite.sql
SET XACT_ABORT ON;
BEGIN TRAN;

IF NOT EXISTS (SELECT 1 FROM sys.columns
               WHERE object_id = OBJECT_ID(N'[dbo].[VISITE]')
                 AND name = N'STATUT_COD')
BEGIN
    ALTER TABLE [dbo].[VISITE] ADD [STATUT_COD] varchar(10) NULL;
END;

-- Bounded and replayable backfill (touches only what is not filled yet)
UPDATE v SET v.[STATUT_COD] = 'REALISEE'
FROM   [dbo].[VISITE] AS v
WHERE  v.[STATUT_COD] IS NULL
  AND  v.[DATE_REALISATION] IS NOT NULL;

COMMIT;

(Warning: an ALTER TABLE … ADD followed by an UPDATE referencing the new column in the same batch may fail at compile time (unknown column name); split into GO batches, or use EXEC('…'), depending on the execution tool. On a large table, split the UPDATE into chunks — see section 7.)

Continuous deployment tools for SQL: integration with Azure Pipelines/TFS is done through installation tasks for SqlPackage, sqlcmd, or PowerShell scripts; sqlcmd has options that change behavior (variables, command interpretation) that you should know — if using toolkits, see the next section. See Version Control for Data: SQL and Power BI.

3. Idempotence, replay, and restart#

🟢 Novice — why everything must be replayable#

A script is idempotent if running it twice (or ten times) produces the same state as running it once. In data, this is vital: a load fails at 03:12 on 4 out of 10 tables; the restart must be safe, with no duplicates or gaps. Without idempotence, every incident requires manual cleanup — the main source of production errors.

🟡 Practical — idempotence patterns#

“delete-insert by logical partition” pattern (simple, robust, replayable):

DECLARE @DateExploitation date = '2026-10-06';   -- parameter, never GETDATE() in processing

BEGIN TRAN;
DELETE FROM [dm].[FACT_VISITE] WHERE [DATE_VISITE] = @DateExploitation;

INSERT INTO [dm].[FACT_VISITE] ([VISITE_ID], [DATE_VISITE], [COMMERCIAL_ID], [CLIENT_ID], [DUREE_MIN])
SELECT s.[VISITE_ID], s.[DATE_VISITE], s.[COMMERCIAL_ID], s.[CLIENT_ID], s.[DUREE_MIN]
FROM   [staging].[VISITE] AS s
WHERE  s.[DATE_VISITE] = @DateExploitation;
COMMIT;

🔴 Expert — MERGE, determinism, and replay windows#

• SQL Server MERGE: powerful but historically surrounded by documented defects; for critical cases, many teams prefer explicit UPDATE then INSERT statements (in the same transaction, with appropriate locks) or the delete-insert pattern. It’s up to you to evaluate on your version; to verify: the status of bug fixes for your version/CU.
• Determinism: processing that depends on time, on NEWID(), on unspecified ordering (TOP without ORDER BY), or on a non-frozen table cannot be replayed identically. Sequence-generated surrogate keys cause the same problem: they vary from one replay to another, so you keep a stable natural key for reconciliation (see also Data Quality).
• Replay window: define how far back you can replay (7 days? 2 years?) based on what the source retains. Beyond that, replay is impossible and the data becomes non-reconstructible: treat history as an asset to be backed up.
• Partial restart: an orchestrator able to relaunch from the failed step, not from the beginning, saves hours on large volumes — provided each step is idempotent.

4. Deployment in installation kits#

🟢 Novice — why “kits”#

In many B2B contexts, you do not deploy directly at the client site yourself: you deliver a kit (a versioned set of scripts, binaries, reports, and an instruction guide) installed by an administrator — sometimes the vendor, sometimes the client. CI/CD then produces a delivery artifact that can be installed n times, in n environments, without being able to fix things manually.

🟡 Practical — anatomy of a good kit#

Three kit engineering principles:

1. The kit is built in the pipeline, not manually: a single artifact, signed or fingerprinted, published with its version.
2. The kit is first installed by the pipeline in an acceptance testing environment under the same conditions as at the client site (same tools, same command line) — otherwise the kit succeeds in acceptance testing and fails at the client site.
3. The kit contains its own test: a check script that reports “installed and compliant” or the list of gaps.

🔴 Expert — compatibility between kit versions#

Kit N+1 must be installable on N and ideally on N-1 (version skip), because clients do not upgrade every version. This implies:

• an installed version table that the kit reads to decide which migrations to run;
• cumulative migrations (the kit contains the N-2→N-1→N chain, or a validated jump script);
• an automated upgrade test matrix (N-1→N, N-2→N) in the pipeline, with “frozen” sample databases representing each version;
• explicit handling of client customizations (added objects, modified settings): the upgrade must not lose them — hence DropObjectsNotInSource=False and non-destructive configuration.

See also managing parallel versions: Branching Strategies, TFVC In Depth.

5. Testing data and transformations#

🟢 Novice — four families of tests#

🟡 Practical — transformation tests on controlled datasets#

A transformation test is written like a unit test: a tiny, hand-crafted input dataset that covers edge cases, an expected output, and an automated comparison.

-- Test: deduplication of visits keeps the most recent sync
-- Setup (in a test database or a [tests] schema)
TRUNCATE TABLE [tests].[VISITE_ENTREE];
INSERT INTO [tests].[VISITE_ENTREE] (VISITE_ID, CLIENT_ID, DATE_VISITE, DATE_SYNC) VALUES
  ('V1','C1','2026-10-01','2026-10-01T10:00'),
  ('V1','C1','2026-10-01','2026-10-01T12:00'),   -- sync duplicate: keep 12:00
  ('V2','C2','2026-10-02','2026-10-02T09:00');

EXEC [dm].[p_Charge_FACT_VISITE] @Source = 'tests', @DateExploitation = '2026-10-02';

-- Check: expected = 2 rows, V1 with 12:00 sync
IF (SELECT COUNT(*) FROM [tests].[FACT_VISITE_SORTIE]) <> 2
   OR NOT EXISTS (SELECT 1 FROM [tests].[FACT_VISITE_SORTIE]
                  WHERE VISITE_ID='V1' AND DATE_SYNC='2026-10-01T12:00')
    THROW 50001, 'Visit deduplication test failed', 1;

Testing frameworks exist (tSQLt for SQL Server, in particular; check its maintenance status for your version); the key is the habit: a case that caused an incident becomes a non-regression test.

Data tests run at every load (blocking checks or warnings):

-- Post-load checks: returns one row per anomaly
SELECT 'Key duplicate' AS controle, COUNT(*) AS nb
FROM (SELECT VISITE_ID FROM [dm].[FACT_VISITE] GROUP BY VISITE_ID HAVING COUNT(*) > 1) d
HAVING COUNT(*) > 0
UNION ALL
SELECT 'Fact without salesperson', COUNT(*)
FROM [dm].[FACT_VISITE] f
LEFT JOIN [dm].[DIM_COMMERCIAL] c ON c.COMMERCIAL_ID = f.COMMERCIAL_ID
WHERE c.COMMERCIAL_ID IS NULL
HAVING COUNT(*) > 0
UNION ALL
SELECT 'Today''s volume out of tolerance', 1
WHERE (SELECT COUNT(*) FROM [dm].[FACT_VISITE] WHERE DATE_VISITE = CAST(GETDATE()-1 AS date))
      < 0.5 * (SELECT AVG(n*1.0) FROM (SELECT COUNT(*) n FROM [dm].[FACT_VISITE]
                WHERE DATE_VISITE >= DATEADD(day,-35,CAST(GETDATE() AS date))
                  AND DATE_VISITE <  CAST(GETDATE()-1 AS date) GROUP BY DATE_VISITE) x);

The last check is behavioral (today's volume compared with the recent average): it catches partial loads that integrity rules do not see.

🔴 Expert — non-regression of metrics#

The most valuable and most neglected test: compare key metrics before/after a change. Principle: a set of reference queries (10 to 30 metrics × a few periods × a few scopes) is run on the reference environment and on the modified environment; the difference is calculated; every difference must be explained (intended, documented change) or fixed.

# Comparison of reference metrics between two databases (skeleton)
$requetes = Get-Content .\temoins.json | ConvertFrom-Json   # { nom, sql, tolerance }
foreach ($r in $requetes) {
    $ref = Invoke-Sqlcmd -ServerInstance $srv -Database $baseRef -Query $r.sql
    $new = Invoke-Sqlcmd -ServerInstance $srv -Database $baseNew -Query $r.sql
    $ecart = [math]::Abs($ref.valeur - $new.valeur)
    if ($ecart -gt $r.tolerance) {
        Write-Warning "$($r.nom) : référence $($ref.valeur), nouveau $($new.valeur)"
        $echecs++
    }
}
if ($echecs) { exit 1 }

Watch points: tolerances (rounding, floating point) must be defined; fixed date sets (do not compare “yesterday”); a written explanation of expected differences (otherwise the test gets disabled at the first failure). Same idea for semantic model measures: run reference DAX queries before/after and compare. See Testing and Metrics Reliability and UAT and Business Validation.

6. Environments, Realistic and Anonymized Data#

🟢 Novice — the dilemma#

To seriously test data processing, you need data that looks like reality: same volumes, same edge cases, same distributions. But reality contains personal and sensitive business data (GDPR). Three options, each with trade-offs:

(Expert notes, context: sales force data with customer and contact names; teams of 3 to 8 people.)

🟡 Practical — anonymize without breaking tests#

Principles:

• Preserve structure: relationships (foreign key, hierarchies), distributions (split by region, by salesperson), edge cases (null values, special characters, duplicates) — otherwise you are testing something other than production.
• Replace deterministically: the same customer must get the same pseudonym in all tables and across all reruns (otherwise joins break). Use a hash with a secret salt or a mapping table.
• Handle free-text fields (visit comments): they often contain unstructured personal data; clear or replace them.
• Keep what is not personal: amounts, dates, quantities are generally not personal but can enable re-identification in combination; assess the risk.

-- Deterministic pseudonymization of a customer name (educational example)
UPDATE c
SET    c.NOM = CONCAT('Client_', CONVERT(varchar(12), HASHBYTES('SHA2_256', CONCAT(c.CLIENT_ID, @sel)), 2)),
       c.ADRESSE = 'Adresse anonymisée',
       c.EMAIL = CONCAT(c.CLIENT_ID, '@exemple.invalid')
FROM   [dbo].[CLIENT] AS c;
-- @sel is a secret kept outside the repository; the script runs on the COPY, never on production.

🔴 Expert — compliance and responsibility#

Pseudonymization (GDPR) remains personal data as long as the mapping key exists; only irreversible anonymization moves it out of scope. Implication: an acceptance testing environment with pseudonymized data is still subject to access, retention, and logging rules. See the topic Security, Privacy, and Compliance. Consult the DPO and your organization's policy: this document does not constitute legal advice.

7. Data Volume and Processing Time#

🟢 Novice — testing on “1,000 rows” proves nothing for 100 million#

A process that works on a small dataset can be unusable at real scale: different execution plan, locking, transaction log bloat, missed processing window. Data volume is a project risk, not an implementation detail.

🟡 Practical — metrics to build in#

• Processing time by step, tracked on every run (log table): without history, drift is invisible.
• Periodic load testing in an environment with data volume close to production (at least monthly, or with every major change).
• Alert thresholds: total duration > X % of available window; time growth > Y % over 4 weeks.
• Batching: split massive operations (UPDATE, DELETE) into chunks to limit logs and locks.

-- Delete/update in chunks to avoid excessive logs and locks
DECLARE @n int = 1;
WHILE @n > 0
BEGIN
    UPDATE TOP (50000) [dbo].[VISITE]
       SET [STATUT_COD] = 'REALISEE'
     WHERE [STATUT_COD] IS NULL AND [DATE_REALISATION] IS NOT NULL;
    SET @n = @@ROWCOUNT;
END;

🔴 Expert — data volume as a pipeline design dimension#

When processing time becomes a constraint, the first levers are algorithmic (reduce processed data: incremental load instead of full load, partitioning, appropriate indexes, avoid per-row scalar functions) before hardware levers. Every lever comes with a complexity cost: incremental load requires reliable change tracking (a source row changed but not detected is a silent error); combine it with periodic full control reloads. Time measurements are sensitive to shared environments (variable resources): measure CPU time, repeat, do not compare two simultaneous runs. See Performance and Data Volume.

8. dbt and transformation as code#

🟢 Novice — what it is#

dbt (data build tool) is a tool that lets you write data transformations as SQL queries (“models”) versioned in a repository, with automatically inferred dependencies, declarative tests (uniqueness, non-null, relationship, accepted values), and generated documentation (including the lineage graph). It runs in the data warehouse (ELT approach): dbt orchestrates SQL; it does not move data.

🟡 Practical — what it brings to data CI/CD#

Example of a declarative test:

models:
  - name: fact_visite
    columns:
      - name: visite_id
        tests: [unique, not_null]
      - name: commercial_id
        tests:
          - relationships: { to: ref('dim_commercial'), field: commercial_id }

Relevance for SQL Server: adapters exist for SQL Server, Azure SQL, Fabric Warehouse (as of 2026-10, to be verified for your version and the maturity of the corresponding adapter); orchestration in Fabric or Azure DevOps is possible. A valid alternative without dbt remains: versioned stored procedures with a homegrown orchestrator + SQL tests (see section 5). dbt is not mandatory; its ideas (graph, declarative tests, environments, documentation) are more important.

🔴 Expert — dbt limitations#

• Its model assumes pure SQL transformations; complex procedural processing (cursors, dynamic SQL, loops) does not adapt well.
• Persistent schema management (indexes, partitions, permissions, unmanaged objects) is limited: it creates tables, it does not replace a database project for infrastructure.
• Additional skills to acquire (Jinja, packages); in a traditional SQL Server team, the benefit depends on the size of the transformation graph (from a few dozen models onward, the gain becomes clear — indicative order of magnitude).

9. Fabric and Data Factory pipelines#

🟢 Novice — orchestrating data movement#

A data pipeline (Azure Data Factory, or Data Factory in Microsoft Fabric) orchestrates activities: copy, notebook call, stored procedure, data flow, conditions and loops, with scheduled or event-based triggers. It is itself an artifact to version and deploy — a pipeline modified in the interface without versioning is production code with no history.

🟡 Practical — CI/CD in Fabric (as of 2026-10)#

Fabric offers two complementary mechanisms:

• Git integration: a workspace is linked to a repository branch (Azure DevOps or GitHub); items are serialized into files, commit and update are done from the interface or via API.
• Deployment pipelines: promotion of content from one workspace to another (development → test → production) with deployment rules (for example, changing the data source by stage).

Each workspace corresponds to one environment. Supported items evolve quickly (as of 2026-10: support for Data Factory items, especially data pipelines, is documented; other items remain in preview or partial) — check the list of supported items in Microsoft Learn documentation before designing. For full automation, the fabric-cicd library (Python, maintained by Microsoft; status to be verified) and Fabric REST APIs make it possible to script deployment from Azure DevOps or GitHub Actions.

🔴 Expert — idempotence of an orchestration pipeline#

A pipeline that copies data must be re-runnable: copy to a staging area then idempotent load (section 3), a time window parameter passed at runtime, a batch control table (watermark), handling of partial failures (restart only the failed activity). SaaS source systems impose quotas and pagination: a large replay can exceed API limits — plan for throttling and resume from the last cursor. For the Fabric/OneLake architecture, see 04 Power BI Reporting, OneLake + SaaS Mobile Apps: Architecture and OneLake + SaaS Mobile Apps: Challenges and Pitfalls.

10. Power BI reports: PBIP, TMDL, deployment pipelines#

🟢 Novice — a report is also code#

Historically, a Power BI report was a binary file (.pbix): impossible to compare, merge, or review. The Power BI project format (PBIP) breaks down the report and semantic model into text files in a folder, making them versionable with Git (or TFVC). The semantic model is described in TMDL (Tabular Model Definition Language), a readable, indented text format, one file per table, well suited to diffs and reviews. The report itself is serialized in PBIR format (one definition per page and visual, in JSON).

MonRapport.pbip
MonRapport.SemanticModel/
  definition/
    model.tmdl
    tables/
      FACT_VISITE.tmdl
      DIM_COMMERCIAL.tmdl
    relationships.tmdl
    cultures/ fr-FR.tmdl ...
MonRapport.Report/
  definition/
    pages/ ... visuals/ ...

(as of 2026-10: PBIP, TMDL, and PBIR are the reference development formats; check the GA/preview status of each component and the PBIR report feature in the current Microsoft documentation.)

🟡 Practical — what text enables#

Typical chain:

Power BI/Fabric deployment pipelines: promote content from one stage to the next while keeping links; deployment rules substitute data source and parameters by stage (for example, the development database becomes the production database). Stage comparisons (“what differs?”) help with review.

🔴 Expert — limits and subtleties#

• Data is not in the project: promoting the model does not move data; a refresh is required at the destination, with its own duration and potential failures.
• Permissions and security (RLS): security roles are part of the model; testing role-based security in the pipeline (queries run under role identity) is an essential integrity check. See RLS Security and Access.
• Multilingual support: translations (cultures) are text files like any others; they are generated from a metadata source and validated (no missing keys). See Accessibility and Multilingual Support.
• Storage modes: Import, DirectQuery, Direct Lake, composite — deployment and testing vary (in Direct Lake, dependency on underlying Fabric items requires deploying in the right order; to be verified depending on feature status). See 10 Cross-Cutting Power BI Best Practices and 11 Power BI Reporting Project Checklists.
• Online editing vs repository: modifying a model directly in the service creates drift relative to the repository; decide what is the source of truth (the repository) and lock editing in production.

11. Impossible data rollback#

🟢 Novice — you can't easily “un-load”#

Rolling back code means redeploying the old version. Rolling back data means restoring a previous state… and losing everything that happened since. Three situations:

🟡 Practical — safeguards#

1. Expand/contract (parallel change): add the new thing (column, table) without removing the old one, switch consumers over, remove the old one in a later version, once nothing reads it anymore. Rollback remains possible throughout the entire intermediate phase.
2. Verified backup before destructive operations: a backup restored in testing is a backup; a backup never restored is hope.
3. Targeted safety copy: SELECT * INTO [archive].[VISITE_20261007] FROM [dbo].[VISITE] before a heavy migration (if volume allows).
4. Temporal table (system-versioned temporal table) in SQL Server to retrieve the past state of certain tables: useful, but not a backup (storage cost, retention).
5. Snapshot / clone when the platform allows it (to be checked depending on the platform: Azure SQL, Fabric Warehouse offer point-in-time restore and cloning features; limits vary).
6. Roll-forward: fix with a corrective migration rather than rolling back.

🔴 Expert — reversibility as an architecture criterion#

Design for reversibility: never overwrite, always append (append-only fact tables with versions, historization), separate the raw zone (immutable, replayable) from the transformed zone (rebuildable). If the raw zone is kept, everything derived from it is recomputable: rollback becomes “recompute from raw with the previous code version.” This is the strongest argument in favor of a retained raw data layer; its cost (storage, GDPR: retention period, right to erasure) must be arbitrated with the DPO. See Data Sources and Data Contracts.

12. Continuous quality in production#

🟢 Novice — the pipeline doesn't stop at deployment#

In traditional CI/CD, production is monitored for outages. In data, production is monitored for silent degradations: the load succeeds, but the data is wrong or late. Hence continuous quality: automated checks at every run of the production pipeline.

🟡 Practical — the five pillars of data observability#

Continuous quality loop:

🔴 Expert — the cost of noise and the service contract#

• Alert fatigue: too many alerts → no one reads them anymore. Rule: every alert must lead to an action; otherwise remove it or downgrade it. Measure the actionable alert rate.
• Data SLO: “the datamart is available at 7 a.m., 99% of business days” is a measurable commitment; freshness becomes a service indicator. See Operations and Support and Operations, Monitoring, and Capacity.
• Processing log (run tracking table: batch, start, end, rows, status, error message): the foundation of all performance and incident analysis; the same table is used for recovery.

13. Data-specific limitations#

🟢 Novice — what CI/CD does not solve here#

In addition to the general limits of CI/CD (10 CI/CD, section 8), there are limitations specific to data:

🟡 Practical — deciding on the investment#

The marginal gain from data CI/CD is highest where there are: multiple environments, multiple parallel versions, repeated deliveries, past installation incidents, team turnover. It is lowest for a single stable process, a prototype, or an environment with no recovery value. Assess hours lost per month today (installations, fixes, waiting) against the implementation cost.

🔴 Expert — source drift and contracts#

SaaS or application sources change without warning: added column, modified type, new enum value. The pipeline must detect (schema comparison, domain checks), isolate (tolerant landing zone, strict transformations), and alert — the data contract (schema, semantics, SLA) turns surprise into managed change. See Data Sources and Data Contracts and SQL Server in SaaS — Challenges and Pitfalls.

14. Practice maturity ratings#

Expert assessments, context-dependent (team of 3 to 8 people, SQL Server/Azure SQL datamart, Power BI reports, kit-based delivery, status as of 2026-10). See RATING. Three columns: value delivered, effort to implement (5 = very high), maturity of the tooling ecosystem.

Takeaway: the best value/effort ratios are versioning, idempotency, logging, testing data on every load, automating deployment in acceptance testing. The most trendy practices (dbt, continuous deployment, automatic rollback) are lower priority than these fundamentals.

15. AI Contribution#

Example prompt:

Here is the current schema of table [dbo].[VISITE] and the requested change (adding a STATUT_COD column
with a default value calculated from DATE_REALISATION). Write an IDEMPOTENT T-SQL script that:
- checks existence before modifying;
- performs backfill in batches of 50,000 rows;
- contains no destructive statement;
- ends with a control query (number of NULL rows).
Then list the risks (locks, log, compatibility with the previous code version) and the rollback plan.

16. See also#

• Foundation: 10 CI/CD: Continuous Integration and Delivery · 07 Methods Specific to Data Projects · 04 Scrum Applied to Data, BI, and Datamarts · 13 Project Management Pitfalls and Best Practices (Cross-Cutting Summary)
• Tools: VCS and SQL/BI Data · Git vs TFVC: Compare, Migrate, and Coexist · Azure DevOps Server (Formerly TFS) In Depth
• Matrices: Maturity Matrix — Data Project Management (PD) · Maturity Matrix — BI Tool/Application Development · Continuous Integration and Delivery
• Reporting: 04 Power BI Reporting — OneLake and Managed SQL Server · OneLake + SaaS Mobile Apps: Architecture · SQL Server in SaaS — Architecture
• Glossary · Bibliography and Resources

✻ Written by Claude Sonnet 5.5