Continuous integration for analytics code

Consider the two ways an organisation changes something that affects reported revenue.

An application change goes through a branch, a pull request, a reviewer, an automated test suite and a deployment with a rollback path. A change to the SQL that defines a conversion is typed into a browser by one analyst, saved, and takes effect on the next run. No review, no test, no version, no way back to what it said yesterday.

The second change is at least as consequential as the first, and frequently more so, because a broken application is visible within minutes and a subtly wrong metric is not visible for a quarter.

The gap persists because the tooling for analytics work does not push you toward engineering practice the way an application repository does. You have to choose it, and choosing it feels like ceremony until the first time somebody needs to know what a definition said in March.

Microsoft's description of what Azure Pipelines is is written for application delivery, and every word of it applies to a transformation layer. The pipeline does not care what the code produces.

The second row is where the value concentrates and it is worth being specific about why. Upstream schema changes are the most common cause of a broken analytics pipeline, and the failure is asymmetric: sometimes the query errors, which is fine because you find out, and sometimes it returns a plausible wrong answer, which is not fine at all.

A compile check against the real schema, run on every change, converts the second case into the first. That is the whole benefit and it is large.

The uniqueness assertion in row four deserves its own mention because of what it prevents. A duplicate key on a join is the classic way a revenue figure doubles, and it produces a number that is wrong in a direction people like, which means it gets questioned late or never.

Do not build all six at once. Build the first, get it running on every change, and add the others when a specific failure justifies each one.

This is the part people get backwards. They build an elaborate pipeline and leave the branch unprotected, so the pipeline runs after the change has already reached production and reports on something it cannot prevent.

The enforcement lives in branch policy. Microsoft documents setting and managing branch policies, covering required reviewers, required build validation, and blocking direct pushes to a protected branch.

Three settings, and I would argue for them in this order. Block direct pushes to the branch that feeds production, so every change arrives as a pull request. Require build validation to pass, so the checks above are a gate rather than a report. Require at least one reviewer, so a human reads it.

The reviewer requirement is the highest-value item on this page and it costs nothing to configure. In a one-person analytics team it feels absurd, and there is a version that still works: the change sits as a pull request until the next working day, and the author reviews their own diff cold. That catches a surprising amount, because most analytics defects are visible in the diff and invisible while you are writing it.

It also produces the artefact that makes the rest tractable. A pull request history is a record of what changed, when, and why, which is the question you cannot answer today when a stakeholder asks why the number moved.

Once changes are gated, the deployment needs somewhere to go that is not production.

Azure DevOps models this with environments, which Microsoft describes as a collection of resources you target with a deployment, with deployment history and traceability. For analytics work an environment is a separate dataset or schema, and the value is that a change runs somewhere real before it runs somewhere that matters.

Layer approvals on the production environment. Microsoft's deployment approvals documentation covers requiring a named person to approve before a stage runs, and this is where the audit trail comes from: who approved this change to the revenue definition, and when.

For a small team the useful configuration is deliberately light. Automatic deployment to a development schema on every merge, approval required for production, and one named approver rather than a group. A group approval at this size means everyone assumes somebody else will look.

The thing to avoid is approval theatre, meaning a required approval on a change nobody actually reads. If the approver approves everything within thirty seconds, you have added latency without adding a control, and it would be more honest to remove the step than to keep it.

The predictable way a first analytics pipeline goes wrong is a connection string or an API key committed to the repository, usually while making something work at seven in the evening.

Once committed, it is in the history. Rotating the credential is the only real fix, and rotating a credential that six scheduled jobs depend on is a job in itself.

Use variable groups. Microsoft documents managing variable groups including marking values as secret and linking to a key vault, and the pipeline references the variable rather than the value.

Two habits make this stick. Scope the credential to what the pipeline actually needs, so a leaked pipeline credential cannot drop tables it never reads. And use a separate credential per environment, so the development pipeline physically cannot write to production, which removes an entire class of mistake rather than warning about it.

Add a check that fails the build if something that looks like a credential appears in a diff. It is imperfect and it catches the seven in the evening version, which is the one that happens.

The half of analytics work that never gets version controlled is the measurement layer: tags, triggers, conversion definitions, the container configuration.

It is edited in a web interface, published by whoever has access, and the record of what changed is whatever the platform's own history offers. When a conversion count moves, the question of what changed in the tag configuration two weeks ago is frequently unanswerable.

Where the platform exposes a configuration export or an API, put that export in the same repository as the SQL and diff it on a schedule. You do not need to deploy from the repository to get most of the benefit. Detecting that the live configuration no longer matches the recorded one is most of the value, and it is a small amount of work.

This is the same class of problem as GA4 events that break silently, and the same reason measurement implementation deserves a defined configuration rather than accumulated changes. A tag nobody recorded changing is a metric nobody can explain.

Instrument the runs too, wherever they execute. A pipeline that stopped triggering looks identical to a pipeline with nothing to do, which is the argument for a heartbeat on any scheduled job regardless of what runs it.

The failure mode of this whole idea is scope. Someone reads a page like this, designs a full deployment pipeline with four environments and eleven checks, and it is still not finished six weeks later while the browser editing continues unchanged.

Week one: get the SQL into a repository. Nothing else. Just having it under version control answers the what did this say last month question, and that question arrives sooner than you expect.

Week two: protect the branch and require a pull request. No automated checks yet. The review, even a self-review a day later, is already the biggest single improvement available.

Week three: add a parse or compile check as build validation. One check, gating the pull request.

Then stop and let it run for a month before adding anything. Each subsequent check should be justified by a defect that actually reached production, rather than by a list somebody wrote.

The reason to sequence it this way is that a partly built pipeline that everyone uses beats a complete one nobody finished. On a clinical research programme moving database change management from hand-applied edits to a version-controlled pipeline, the work was decomposed into 159 tracked items with acceptance criteria and explicit dependencies, and the sequencing was what made it fundable rather than the tooling.

The same discipline applies to the transformation layer itself, where incremental models can be built without adopting a framework first. And all of it presumes the definitions are worth protecting, which means they live in the warehouse rather than inside a report. Version control on a definition that only exists inside a dashboard protects nothing.