LLI Wiki — My Digital Knowledge Base

Applying data corrections to production through a controlled process

Scripts in git, review, a pipeline that runs them in a transaction with row-count checks and saved output, never by hand from a laptop.

Every DBA knows the situation: a data fix is needed in production, the script is ten lines, and the fastest way is to paste it into a query window from a laptop. That is exactly how a missing WHERE clause ends up in an incident report. I moved data corrections to a controlled process: the script is reviewed in git, a pipeline runs it with the same identity and the same guardrails every time, and the output is kept.

The rule

Nobody runs a correction by hand against production. A correction is a file in a repository. It goes through a pull request, gets approved by someone else, and only the pipeline (or a scheduled runner) executes it.

flowchart LR
    A["Script in git (branch)"] --> B["Pull request + review"]
    B --> C["Merge to main"]
    C --> D["Pipeline: validate"]
    D --> E["Manual approval (production)"]
    E --> F["Runner: execute in a transaction"]
    F --> G{"Row counts as expected?"}
    G -->|"yes"| H["COMMIT"]
    G -->|"no"| I["ROLLBACK"]
    H --> J["Save output as artifact"]
    I --> J
    J --> K["Report"]

What a correction script looks like

I ask for a fixed shape so a reviewer can read it in one minute: a header with the purpose and the expected number of rows, then the change, which refuses to commit when the count is wrong.

-- Purpose: reset status for rows stuck in 'PENDING' since the import bug
-- Expected rows: 42
SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.Orders
SET    Status = 'NEW'
WHERE  Status = 'PENDING'
  AND  ImportBatchId = 1234;

DECLARE @n int = @@ROWCOUNT;
IF @n <> 42
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50001, 'Unexpected row count', 1;
END

COMMIT TRANSACTION;

Careful: @@ROWCOUNT is reset by almost every statement, so copy it into a variable right after the UPDATE. The idea: the script itself refuses to commit when the count is wrong.

The pipeline

The runner does what the author must not forget to do:

  1. Validate: the file exists in the expected folder, naming convention respected, encoding fine, no forbidden statement (for example DROP, TRUNCATE) unless the file is explicitly flagged.
  2. Approve: for production, an environment approval gate in the pipeline.
  3. Execute with a managed identity or service principal, not a person's account, through Invoke-Sqlcmd. The runner can also wrap the script in its own transaction when the file does not manage one, with a timeout and a stop on first error (-ErrorAction Stop, SET XACT_ABORT ON).
  4. Capture the full output (messages, rows affected, errors) into a text file and publish it as a pipeline artifact, or upload it where the requester can read it.
  5. Report: a mail with status, duration and the output file. A failed script never ends silently.
$result = Invoke-Sqlcmd -ServerInstance $server -Database $db -AccessToken $token `
            -InputFile $scriptPath -QueryTimeout 600 -Verbose 4>&1
$result | Out-File -FilePath $outputPath -Encoding utf8

Queue or pipeline trigger

Two designs work. In the first, the correction is requested from a ticketing or release tool and lands in a queue; a scheduled runner takes the next script, marks it "running", executes it, uploads the output, marks it "executed". In the second, merging to main triggers the pipeline. I prefer the queue when the requester is not a developer, and the pipeline when the team already lives in git.

Pitfalls

Lessons learned

The gain is not speed, it is confidence. Every production change has an author, a reviewer, an approval, an output and a date, and the same script can be rehearsed on a restored copy first. Emergency fixes are still possible, they just go through the same short path.

Mastodon LLIMastodon SocialFosstodon