CI/CD can stop your reports from breaking every other week

Software engineers adopted it years ago. Data teams are still catching up. It’s a one-time setup that keeps paying off.

Your team just spent the whole week fixing a broken report.

But one of your team members made changes directly in the production system and forgot to commit them to the source code repository.

Your report looks fine at the end of the week. All the KPIs match, and another senior developer pushes all the changes to production, thinking the source code is in sync with what’s running in production. You email your clients to say the reports are working and ask for their review.

Boom.

Monday morning hits and the numbers don’t match. Even worse, reports that were working on Friday night have gone completely blank. Your clients are frustrated and your week is ruined before your first cup of coffee.

You finally decide to do something, and you start asking how this could have been prevented.

If you look closely, this was not a technology problem. It was a technical culture problem.

Bringing DevOps to data engineering

Many organizations still haven’t adopted DevOps practices in their data engineering work. The reasons vary, and one of the biggest is that these practices can slow down time to production in the short term.

But as the team grows and the project scales, which it almost certainly will, the investment pays off.

It builds trust among developers, because everyone knows changes are tested and reviewed before they reach production. It also lets leadership bring less experienced engineers onto the project and have them contribute early, without worrying about the impact of a mistake. The pipeline catches problems before they reach production.

Case study

To show why this matters, I’ll tell you about a real incident at my day job where our reports broke, and how a CI/CD pipeline helped us fix it and, eventually, build trust in our dev-to-prod workflow.

We had a client doing a data warehouse migration project with us. The warehouse was hosted on an on-premises Microsoft SQL Server database, and they wanted it migrated to Google Cloud’s BigQuery.

All the business logic was written in T-SQL, in around 80 stored procedures, and orchestration was handled by around 30 SSIS jobs.

We decided to replace the orchestration tool with Apache Airflow on Google Cloud, which is Cloud Composer, and to use BigQuery as the data warehouse, with BigQuery’s SQL dialect. We converted the T-SQL transformation logic into BigQuery-compatible SQL and deployed it to BigQuery. The SSIS jobs were replaced by Airflow DAGs, one DAG for each SSIS job.

There was a lot of other infrastructure for ingestion and change data capture, which I’ll leave aside for this article.

During the data reconciliation phase, we found discrepancies between the new reports and the client’s existing reports, which were connected to the SQL Server database.

The data team was asked to identify the code issues and fix them. A few developers found the problems and fixed them directly in the production BigQuery environment. That meant production was now ahead of the source Git repository in terms of code changes.

The developers reported that the issues were fixed and the numbers matched. Everyone was happy, the project manager marked the issue as closed and notified the client. Then, on a Friday night, someone deployed the whole source Git repo to BigQuery, which undid all the changes the developers had made. We had a client call set up for the coming Monday morning.

And guess what.

Every report that had been marked as resolved showed incorrect numbers on that Monday morning call. Everyone panicked, the client began to distrust our services, and all the developers on the call were in shock.

This incident put all the focus on building reliable DataOps workflows in our project. We took help from our infrastructure team to set up a reliable CI/CD pipeline for our BigQuery deployments. They quickly understood our requirements and pain points, and immediately implemented the following:

Dedicated projects were created for development and production, following naming conventions.
Access control was enforced on the production project. No write or object-creation access was given to user accounts.
A dedicated service account was created for deployments, and all objects and scripts were executed by this service account.
The whole source code was parameterised by the dev team, so the CI/CD pipeline could dynamically deploy objects to dev or prod environments.
Git branches were protected and access-controlled.

What changed

With these five things in place, the Friday night mistake can’t happen again.

Nobody can change production by hand, because user accounts have no write access to it. The only way a change reaches production is through the repository, and the only one who deploys it is the service account. That means production and the source code can no longer drift apart. There is only one door in, and it goes through Git.

A fix now follows the same path every time. The developer makes the change in the dev project, it goes through a protected branch where it can be reviewed, and the pipeline deploys it to production. Nobody has to remember to commit it afterwards, because the commit is the deployment.

It did add a few steps. A quick fix is no longer a quick edit in production, which is exactly the short-term cost I mentioned earlier. But over time the team started trusting the dev-to-prod workflow, and that trust is the real return. When a ticket is marked as resolved, the fix stays in place.

If you’re a small team

You don’t need a big infrastructure team to get most of this value. Three habits go a long way:

Keep every query and script in a Git repository.
Take production write access away from people and give it to one deployment account.
Make the repository the only way anything gets deployed.

Even this small setup would have prevented our Friday night problem.

The takeaway

Reports usually break for one of two reasons: the code is wrong, or the process around the code is. Fixing the code only helps until the next manual change. A CI/CD pipeline fixes the process, so the right way becomes the easy way and the risky way is closed off.