How to Configure BigQuery Slow Query Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a bigquery slow query log by reading built-in metadata views and cloud audit logs. BigQuery does not have a single toggle for slow queries like traditional databases. Instead, platform teams query job history views to find runs that take too long. Teams often grant broad read roles so engineers can check these system views. Broad roles let users see query text and sensitive data across the project. How the bigquery slow query log works in native views Every query in BigQuery runs a

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 slow query log by reading built-in metadata views and cloud audit logs. BigQuery does not have a single toggle for slow queries like traditional databases. Instead, platform teams query job history views to find runs that take too long. Teams often grant broad read roles so engineers can check these system views. Broad roles let users see query text and sensitive data across the project.

How the bigquery slow query log works in native views

Every query in BigQuery runs as a distinct job inside a cloud project. The platform tracks each job and saves its details in internal system tables. You can read these system tables to build your own slow query report. These views record the start time, end time, and total bytes billed.

To find slow jobs, you filter these system views by total elapsed time. You can also check how much slot time each job used during execution. High slot usage with low elapsed time means the query ran across many workers. High elapsed time with low slot usage often points to resource contention or waiting.

Teams also route cloud audit logs to a central dataset for long-term storage. System views only keep job history for a set period before aging it out. Routing audit logs into a dedicated table lets you track slow queries over months. You can then connect dashboard tools to this table to spot bad trends.

Setting up roles and permissions for query logs

Reading job metadata requires specific identity and role bindings in your cloud project. A regular data viewer role only lets a user read standard data tables. To see all project jobs, an admin must grant a resource viewer role. Without this role, an engineer can only see the jobs they ran themselves.

Granting project-wide metadata roles creates a real security problem for data teams. System views contain the full SQL text of every job run by anyone. Developers sometimes put customer emails or phone numbers right into their SQL filters. Anyone who reads the slow query log can see those raw filter values.

Teams try to fix this by creating custom views that hide the query text. That approach hides the exact SQL statement that caused the performance drop. Engineers then have to ask an admin to pull the full text anyway. This back-and-forth slows down the team when a production report gets stuck.

BigQuery IAM + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native logging solves and what it misses

Native job views and audit logs are great for looking at past events. They tell you which dashboard or user ran a heavy job yesterday. You can use this data to partition tables or rewrite bad joins later. It also helps finance teams see which departments drive up cloud compute costs.

Still, a log only records the problem after the query finishes running. Native permissions decide who can log in and submit a job to the project. They do not inspect what individual SQL statements do while in flight. Once a user has table permissions, they can run an unindexed full scan.

Native logs also do not mask sensitive fields in the results returned to users. If a slow query pulls millions of card numbers, the log just records the runtime. Data masking rewrites matched fields like emails or national IDs in query results. Teams need a way to check queries and mask results before data leaves.

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 sits inline in the protocol beside each supported service as an open-source proxy, a sidecar for your database or API. For every query or command an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human approval.

First, hoop.dev guardrails block banned queries before they run or hold risky ones for review, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Does BigQuery have a dedicated slow query log file?

No, the platform does not write slow queries to a separate text file. You query the built-in job metadata views or cloud audit logs to find slow runs.

How do I tell if a slow query is waiting on slots?

Check the job timeline metadata in the system views for pending time vs run time. A long pending time means the project ran out of available compute slots.

Can users see sensitive data inside BigQuery job logs?

Yes, job logs store the exact SQL text submitted by every user or app. If a query includes raw emails or card numbers, log readers will see them.

The query runs. The emails stay hidden.