How to Configure BigQuery Replication User

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a bigquery replication user by creating a service account in Google Cloud. Next, you attach data editor and job runner roles to that account. Teams often rush this step when they build new data pipelines. They grant broad project owner roles just to make the sync job run. This shortcut gives the sync tool full power over every dataset. A leaked key or bad script can then wipe out production tables. How a bigquery replication user works in Google Cloud A replication tool moves rows

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up a bigquery replication user by creating a service account in Google Cloud. Next, you attach data editor and job runner roles to that account.

Teams often rush this step when they build new data pipelines. They grant broad project owner roles just to make the sync job run. This shortcut gives the sync tool full power over every dataset. A leaked key or bad script can then wipe out production tables.

How a bigquery replication user works in Google Cloud

A replication tool moves rows from a source database into your warehouse. In Google Cloud, this tool does not log in as a human. Instead, it uses a machine identity called a service account.

The service account needs two main types of permission to do its work. First, it needs project level permission to start and run load jobs. Without job rights, the account can see tables but cannot run queries. Second, it needs dataset level permission to create tables and write rows.

Good setup keeps these two permission levels separate. You grant the job runner role at the project level. Then you grant the data editor role only on the target dataset. This stops the sync tool from touching other datasets in your project.

After you assign the roles, the sync tool must log in. Teams can use workload identity federation if the tool supports it. Many third party sync tools still ask for a static JSON key file. You generate this file in the cloud console and upload it to the tool.

Why you should separate read and write roles

Some pipelines only pull data out of the warehouse to another app. Other pipelines push fresh data in from an outside source. You should never use the same service account for both jobs.

User Provisioning (SCIM) + BigQuery IAM: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Create one account for inbound loads and another for outbound reads. Give the inbound account write rights on the staging dataset only. Give the outbound account read rights on the reporting dataset only. If one tool fails, the blast radius stays small.

This split also makes your cloud audit logs much easier to read. When a sudden spike in billing happens, you can check the account name. You will know right away which specific pipeline caused the heavy query load.

Where native cloud roles fall short

Native roles do a good job of controlling who can log in. They also limit which datasets an account can see or change. Still, native roles stop checking once the session starts.

Cloud IAM does not inspect the actual SQL statements in flight. An account with data editor rights can run a drop table command. It can also read raw sensitive columns if it queries the target dataset. If an engineer copies the JSON key to a laptop, they get those same rights.

Static keys also create a long term chore for your security team. You must find every place the key lives and rotate it often. If a key leaks, anyone can run queries until you revoke it. Native permissions alone cannot mask emails or card numbers in query results.

Check database and API queries on the wire

Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.

Frequently asked questions

Should a replication account have project level editor rights?

No, project level editor rights are far too broad for data sync jobs. Grant job runner rights on the project and data editor rights on one dataset.

How can we avoid long lived JSON service account keys?

Use workload identity federation or service account impersonation whenever your sync tool supports it. If you must use JSON keys, rotate them on a strict schedule.

Can one service account run multiple data pipelines?

It can, but sharing one account across tools is a bad practice. Give each pipeline its own account so you can revoke rights without breaking other jobs.

The query runs. The emails stay hidden.