CI/CD for BigQuery Views

Denis Shevelev — Mon, 02 Mar 2026 20:58:40 +0000

How I Automated What I Was Tired of Doing Manually

I'm a freelance data analyst. I work with multiple clients simultaneously: building dashboards in Power BI and Tableau, writing SQL queries, and automating reports.

How I Got Here

One of my projects involves web analytics. The data lives in BigQuery, and I've built a layer of views on top of it for Power BI. As the project grew, I needed a way to roll back to previous versions, hand off work to clients, and be sure I was always working with the latest version.

The Solution

Git becomes the single source of truth. All SQL code is stored in a repository, and CI/CD automatically applies changes to BigQuery on every commit. Sounds like something only DevOps engineers do? This article will walk you through the setup process step by step.

What You'll Need:

• GitLab (free account is enough)
• Google Cloud Console
• Google Cloud project with BigQuery
• About 2 hours for the initial setup

What's Covered in This Article:

Setting up access (GitLab Token + GCP Service Account)
Repository structure for SQL files
CI/CD pipeline (YAML configuration)
Python integration script
Runner setup
Testing the workflow

Part 1:

Setting Up Access

Before You Click — Let's Understand What's Happening
Our CI/CD pipeline is a program that runs on GitLab's server. It needs to:

Read the changed SQL files from your repository
Write those SQL queries to BigQuery

The problem: GitLab's server knows nothing about your repository, let alone your BigQuery. It needs "keys" — proof that you've authorized it to act on your behalf.

Two different keys for two different systems. Let's break down each one.

Block A:

GitLab Access Token

First, we need to generate a token for programmatic access to the repository. There are two options (Personal Token and Project Token) — let's look at each.

which one should you choose?

A Project Token is tied to the repository and works better for teams. But for your first setup, a Personal Token is simpler — fewer steps, less room for mistakes. Once everything is working, you can migrate to a Project Token.

How to create a Personal Token:
1․ Click your Avatar → Preferences → Personal access tokens

2․ Click Add new token
3․ Fill in the form:

Token name: cicd-bigquery-reader (use a clear name)
Expiration: I recommend 1 year
Scopes: api, read_api, read_repository

4․ Click Create personal access token
5․ Copy the token — it's only shown once

glpat-xxxxxxxxxxxxxxxxxxxx ← save this somewhere safe

How to create a Project Token:
Note: Project Tokens may not be available in your GitLab setup (see how to fix this below)

Open your project in GitLab
Go to Settings → Access Tokens
Click Add new token
Fill in the form just like you did for the Personal Token above
Click Create project access token

6․ Copy the token — GitLab will never show it again

Done, the token is created. Save it somewhere safe.

What to do if you can't create a Project Token:

On GitLab's free plan, Project Access Tokens may be disabled at the group level.

In this case, you can create a new project in your personal namespace (outside the group)

Creating a project in your personal namespace

Step 1: Click + in GitLab's top panel → New project/repository
Step 2: Select Create blank project
Step 3: Fill in the form:

• Project name: bigquery_views_test (or your name)
• In the dropdown list select your username (not the group!)
• Project URL:

Should look like: gitlab.com/< **User name** >/< **Project name** > ❌ Not like this: gitlab.com/< **Group name** >/...

• Visibility level: Private (recommended for work projects)
• ✓ Initialize repository with a README

Step 4: Click Create project

Now you have access to create a Project Token

Block B:

Google Cloud Service Account

Why you need it: A service account is a "technical user" in Google Cloud. Unlike your personal account, it has no password or 2FA — just a JSON key. Perfect for automation.

Open Google Cloud Console

1 At the top, select the correct project (the one with your BigQuery)
Rule: Create the service account in the same GCP project where your BigQuery lives — the one where you'll be deploying views.
2 Verify that the URL shows the correct project-id

3 Now navigate to: IAM & Admin → Service Accounts → Create Service Account
4 Fill in the form:
• Name: gitlab-cicd-bigquery (This generates an email like: gitlab-cicd-bigquery@your-project.iam.gserviceaccount.com)
• Description: GitLab CI/CD for BigQuery views deployment
5 Click Create and Continue
6 Grant access: select BigQuery Data Editor
7 Continue and Done

Now let's create the JSON key:

Click on the service account you just created
Go to Keys tab → Add Key → Create new key
Choose JSON format
The file downloads automatically — save it somewhere safe!

The key looks something like this

{ 
"type": "service_account",
 "project_id": "your-project", 
"private_key_id": "abc123...", 
"private_key": "-----BEGIN PRIVATE KEY-----\nMIIEv...", ← this is what we need
"client_email": "gitlab-cicd-bigquery@your-project.iam.gserviceaccount.com", 
... 
}

Store the key somewhere safe. No need to modify it!

Block C:

CI/CD Variables in GitLab

Hardcoding secrets in code is a bad idea — anyone with repository access can see your keys. To avoid this, we'll create a CI/CD variable in GitLab. These variables are stored separately from the code, masked in logs, and only accessible during pipeline execution.

Before creating the variable, we need to prepare the private_key from the JSON file. The problem is that private_key contains \n characters (line breaks), and GitLab can't mask strings containing \n.

What to do:

• Copy the private_key value from the JSON file:

"-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBg… T2ZDRX61+y \nWmi...A3C\n1YIw Sb…K0/jQqFRHXPKc=\n-----END PRIVATE KEY-----\n "

• Remove the wrapper — delete these parts: "-----BEGIN PRIVATE KEY-----\n and \n-----END PRIVATE KEY-----\n"
• Keep only the key "body":

MIIEvQIBADANBg…T2ZDRX61+y\nWmi...A3C\n1YIwSb…K0/jQqFRHXPKc=

• Replace all \n with @ (there are many — use find & replace, don't do it manually)

MIIEvQIBADANBg…T2ZDRX61+y@Wmi...A3C@1YIwSb…K0/jQqFRHXPKc=

• Make sure there are no spaces at the beginning or end of the string

How to add a variable

Go back to GitLab, open the needed project
Settings → CI/CD
Expand the Variables section
Click Add variable
Fill in: Visibility: ✓ Mask variable; Flags: ✓ Protect variable; Key: variable name (for example, BQ_KEY); Value: the prepared key "body"
Add variable

Part 2:

The YAML Configuration File

What it's for: This is a script for GitLab CI/CD — it tells GitLab what to do when you commit to the repository.

Now we need to create a YAML file for our CI/CD pipeline. Create a file called .gitlab-ci.yml in the root of your repository.

and tell GitLab where to find it. To do this, go to Settings → CI/CD → General pipelines and enter the path in CI/CD configuration file field. However, if you create .gitlab-ci.yml in the root of your repository, GitLab will find it automatically — no extra configuration needed.

We'll create a very simple pipeline with just one stage — deploying a view to BigQuery. But you can make it as complex as you need! For example, you could add stages for query validation or other checks.
Our pipeline will run whenever you push to the main branch. Here's what the YAML code looks like:

You can download the file here, and here you'll find the full documentation of all YAML commands and features for GitLab.

Part 3:

The BigQuery Deployment Script

Let's create the folder structure in GitLab
Step 1: Open your repository in GitLab
Step 2: Click + button → New file

Step 3: In Filename field, enter the full path at once:
projects/ <project>/<dataset> /views/.gitkeep

Where and are the actual names of your project and dataset in BigQuery (where your views are stored). Git doesn't store empty folders, so .gitkeep is an empty placeholder file that keeps the folder in the repository.

Now let's add a SQL file

Step 1: Navigate to the folder projects///views/
Step 2: Click + → New file
Step 3: The file name should be your view's name
Note: The view doesn't have to exist in BigQuery yet

Step 4: Create a file following these repository code organization rules:
• View code is stored in files with the .sql extension
• Views are located at:
projects/<BQ Project>/<BQ Dataset>/views/<View name>.sql
• File name (without extension) must match the view name
• View code must start with CREATE OR REPLACE VIEW

The script that handles deployment to production performs a few simple checks and writes all modified views to BigQuery.

You can find the full script with comments here.

Part 4:

GitLab Runner

A Runner is an application that executes the jobs defined in your .gitlab-ci.yml. When you commit, GitLab sends the job to a runner, which then runs your script.
You commit → GitLab → Runner executes the script → View updates in BigQuery

Two options: Shared or self-hosted

Shared Runners - Already set up, no action needed

Project Runner - Full control, runs on your server

Option 1: Shared Runners (recommended to start)
Good news: Shared Runners are already enabled on GitLab.com!
How to check:

Go to Settings → CI/CD → expand Runners section
Click on Instance tab
If you see runners listed (e.g., "117") — you're all set

Done! You don't need to install anything. When you commit, one of the Shared Runners will automatically execute your pipeline.

Shared Runners security:

Each pipeline runs in an isolated container that is deleted after completion. Your BQ_KEY is masked in logs. For most projects, this is secure enough.

Option 2: Project Runner (if you need your own)
The final step is creating a runner — an application that will execute the CI/CD process.
To create a runner, go to Settings → CI/CD → Runners → Create project runner.

Filling out the form:

Tags (field at the top) Leave it EMPTY (don't enter anything)
✓ Run untagged jobs MUST be checked! (Without this, the runner won't execute your jobs)
Runner description Enter: Windows Runner (or your OS)
Other checkboxes: Paused — do NOT check; Protected — do NOT check; Lock to current projects — do NOT check
Click Create runner button

You need to download and install the runner. A detailed description of runners and their setup process can be found, for example, here. (I chose GitLab Runner; below are the steps for Windows OS)

Step A: Downloading GitLab Runner

Open PowerShell as Administrator: Press Win + X → Select Terminal (Admin)
Create a folder:

In the PowerShell window in Terminal (Admin) mode, enter:
• Create folder
New-Item -ItemType Directory -Path "C:\GitLab-Runner"
• Navigate to the folder
cd C:\GitLab-Runner
• Download gitlab-runner.exe
Invoke-WebRequest -Uri "https://gitlab-runner-downloads.s3.amazonaws.com/latest/binaries/gitlab-runner-windows-amd64.exe" -OutFile "gitlab-runner.exe"

If the exe file appears in the folder on your computer — the file was downloaded successfully!

Step B: Registering the Runner in GitLab
Now you need to register the runner using a token from GitLab.
Go back to GitLab. Open the registration instructions page (where you were before):
• Settings → CI/CD → Runners → find the created runner
Or you should have the Register Windows Runner runner page open

In GitLab on the Step 1 page:

Click the copy button to the right of the command
This will copy the entire command with the token

Step C

Go back to PowerShell (should be open in C:\GitLab-Runner)
Paste the copied command
Press Enter

The first question will appear:
Enter the GitLab instance URL (for example, https://gitlab.com/):
[https://gitlab.com]:
Your answer:
Just press Enter (uses the default value)

The second question will appear:
Enter a name for the runner. This is stored only in the local config.toml file: [your_computer_name]
Your answer (the name of the runner created in the previous step):
Windows Runner

The third question will appear:
Enter an executor: custom, docker, ...
Your answer:
shell

Explanation: shell = the runner will execute commands via PowerShell/CMD on your computer

Alternatives:
• docker — runs in Docker containers (requires Docker Desktop)
• custom — custom configuration

After entering, you should see:
Runner registered successfully … PS C:\GitLab-Runner>

Runner successfully registered!

Command 1: Install the service
In PowerShell (where you are now) run:
.\gitlab-runner.exe install

Information about the platform and runner version will appear

Command 2: Start the service
Run the final command:
.\gitlab-runner.exe start

A similar message with platform and runner version information will appear

Service started successfully!

After the runner is created and registered, make sure it's running. On the CI/CD settings page in the Runners section, your runner should have the status "online."

Final test: Let's verify it works.

Let's make sure the pipeline runs on your Windows Runner!
As long as Shared Runners are enabled, they may intercept the job

Disable Shared Runners

In your project: Settings → CI/CD → Runners
Go to the Instance tab
Turn off the option Turn on instance runners for this project

Instructions:

In your project, open any SQL file in projects/.../views/
Click Edit (pencil icon)
Make any change to the file
Commit changes
Navigate to: Build → Pipelines
Open the latest pipeline → validate job
Check the log — you should see: Running with gitlab-runner 18.9.0 (...) on Windows Runner z12DMWWg, system ID: ... Preparing the "shell" executor

What the result should look like

If everything is set up correctly, when you merge a branch (i.e., push to master), a running job will appear in the Jobs tab in GitLab. The list of completed jobs looks like this:

In BigQuery, in the Views section, the updated view will appear with SQL code matching the latest commit in GitLab. In this example, the SQL query for creating a view in BigQuery was executed successfully — exactly what we were aiming for!

DEV Community: Denis Shevelev