Bigquery Sink Connector
A bigquery sink connector reads streaming events from a message queue and writes them into warehouse tables. You set it up by linking a worker cluster to your cloud project with a service key. Most teams start by granting broad project roles to get data flowing fast. This approach works at first, but wide permissions create risk if a key leaks. Data engineers use this setup to move high volumes of events into tables for reporting. When a team gives the worker role full admin rights, any task on
BigQuery IAM: the complete guide
Architecture patterns, implementation strategies, and security best practices. Delivered to your inbox.
A bigquery sink connector reads streaming events from a message queue and writes them into warehouse tables. You set it up by linking a worker cluster to your cloud project with a service key. Most teams start by granting broad project roles to get data flowing fast. This approach works at first, but wide permissions create risk if a key leaks.
Data engineers use this setup to move high volumes of events into tables for reporting. When a team gives the worker role full admin rights, any task on that cluster can alter tables. A small bug or bad script can drop data across the whole project. Good security starts with knowing how the native workflow runs.
How the bigquery sink connector works
The bigquery sink connector runs as a background task inside your data streaming cluster. It pulls batches of records from source topics and maps them to target tables. Before writing, the task checks the target schema to make sure all columns match. If the setup allows it, the task can create new tables or add missing columns on the fly.
To talk to the cloud warehouse, the worker uses a service account role. You generate a key file or bind a cloud identity to the worker node. The cloud platform checks this role when the worker starts a write job. You must grant permissions to read dataset details and insert rows into tables.
Data flows from the streaming queue into memory buffers on the worker nodes. When a buffer fills up or a timer ends, the worker sends the batch over the network. The warehouse API receives the payload and appends the rows to the target table. If a batch fails, the worker can send bad records to a separate error topic for review.
What native roles solve and what they miss
Cloud identity roles do a good job of controlling which service can log in. You can lock a service account down to a single dataset instead of the whole project. You can also separate the role that creates tables from the role that writes rows. This stops a rogue worker task from deleting tables in other parts of your warehouse.
Still, static cloud roles only check who logs in at the start of a request. They do not inspect what individual queries or write jobs do while in flight. Once a worker or engineer has a valid role, the database trusts every command they send. If source topics contain raw emails, phone numbers, or card numbers, the worker writes them straight to disk.
BigQuery IAM: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Teams also need to check the tables after the data lands to fix pipeline bugs. Engineers often share the same broad service keys or get permanent read roles on production datasets. When an engineer runs a query to check a broken batch, raw national IDs and card numbers appear in the results. Native roles alone cannot rewrite sensitive fields in query results or require peer approval for risky commands.
Managing human and service access to warehouse tables
Securing a data pipeline means looking at both the ingest worker and the people who support it. Start by removing project-wide roles from your streaming workers. Create one dedicated service account for each pipeline and scope its role to a single dataset. Turn off automatic table creation in production so bad event schemas cannot pollute your warehouse.
Next, separate how machines write data from how engineers read and fix that data. Engineers should not use shared service keys to run ad hoc checks on sink tables. Every person on the team should log in with their own identity so logs show who ran each query. Adding field-level masking rewrites matched fields like emails or phone numbers in query results during debugging.
Govern statements and responses across databases and APIs
Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev runs beside each supported service as an open-source proxy, a sidecar for your database or API.
First, hoop.dev guardrails block banned queries before they run. Then hoop.dev data masking rewrites matched sensitive values in responses in memory. The audit trail records every statement and verdict per resource. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.
Frequently asked questions
What permissions does a bigquery sink connector need?
The worker service account needs permission to read dataset metadata and write data into tables. If you turn on automatic table creation, it also needs permission to update dataset schemas.
How do you handle bad records in a sink pipeline?
You can set up a dead letter queue in your streaming worker settings. When the warehouse rejects a record due to a schema mismatch, the worker sends that record to a separate topic.
Should engineers use the sink service account to check data?
No, sharing service account keys makes it impossible to see which person ran a query. Engineers should use their own accounts with read rules that rewrite card numbers and emails in results.