Auditing BigQuery User Query Counts

PCA By islever Editorial Last updated

Familiarity with Google Cloud Observability solutions.

Short answer

D Use Cloud Audit Logging to view Cloud Audit Logs, and create a filter on the query operation to get the required information.

Question

Your BigQuery project has several users. For audit purposes, you need to see how many queries each user ran in the last month. What should you do?
  1. Connect Google Data Studio to BigQuery. Create a dimension for the users and a metric for the amount of queries per user.
  2. In the BigQuery interface, execute a query on the JOBS table to get the required information.
  3. Use ג€˜bq showג€™ to list all jobs. Per job, use ג€˜bq lsג€™ to list job information and get the required information.
  4. Use Cloud Audit Logging to view Cloud Audit Logs, and create a filter on the query operation to get the required information. Correct Answer

Key Takeaways

The question tests the distinction between querying operational metadata for immediate data access versus using centralized audit logs for compliance and historical auditing.

This page clarifies how to audit BigQuery usage by identifying the correct method to count queries per user over a specific period, establishing that Cloud Audit Logging is the standard solution.

Common mistake: Many learners choose Option B because INFORMATION_SCHEMA.JOBS views are excellent for real-time monitoring, but they often lack the retention and security guarantees required for formal audit trails compared to Cloud Audit Logs.

Expert Analysis

Why the Answer Is Correct

Cloud Audit Logging (Option D) is the definitive tool for auditing administrative and data access activities in Google Cloud. BigQuery automatically sends audit logs to Cloud Logging, allowing you to filter for specific query operations and aggregate counts by user identity over time. This approach provides a secure, immutable record suitable for compliance.

Why the Other Options Are Wrong

Option A relies on Data Studio, which is a visualization layer dependent on underlying data sources; it does not inherently provide an audit log mechanism. Option B suggests querying the JOBS table directly; while INFORMATION_SCHEMA.JOBS_BY_USER is useful for current or recent job visibility, it is not designed as a long-term audit trail and may be subject to retention limits. Option C involves command-line tools like bq ls and bq show, which are interactive and not scalable for monthly aggregation across many users.

Community Comment Notes

The community was split between Option B and D. Several commenters argued that INFORMATION_SCHEMA is 'more efficient' for getting query counts, noting that it is a built-in feature for monitoring. However, others pointed out that Cloud Audit Logs are specifically designed for 'audit purposes' and provide a broader view of activity including permissions and system events, making D the more robust answer for compliance scenarios.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When a question specifies 'audit purposes' or 'compliance', prioritize solutions that involve centralized logging (like Cloud Audit Logs or Stackdriver) over direct database queries, as audit logs are immutable and retained for longer periods.

Frequently Asked Questions

Why is INFORMATION_SCHEMA.JOBS_BY_USER not the best choice for auditing?

While useful for monitoring, INFORMATION_SCHEMA tables are not retained as long as audit logs and are not considered an immutable audit trail for compliance.

Can I use bq ls to count monthly queries per user?

No, bq ls lists jobs interactively and is not practical for aggregating historical data across multiple users and months programmatically.

More PCA FAQ →

Discussion 129 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 30 of 129

  1. Googler2 👍 54
    D- reasons: 1.-Cloud Audit Logs maintains audit logs for admin activity, data access and system events. BIGQUERY is automatically send to cloud audit log functionality. 2.- In the filter you can filter relevant BigQuery Audit messages, you can express filters as part of the export https://cloud.google.com/logging/docs/audit https://cloud.google.com/bigquery/docs/reference/auditlogs#ids https://cloud.google.com/bigquery/docs/reference/auditlogs#auditdata_examples
  2. Zarmi 👍 27
    Answer is D: https://cloud.google.com/bigquery/docs/reference/auditlogs#example_query_cost_breakdown_by_identity
  3. checkmate 👍 13
    Please see the C carefully, it should be Use bq ls to list all jobs, use bq show to show the detail. Hence C is not right.
  4. GooglecloudArchitect 👍 10
    D is the right as you can get the monthly view of the query usage across all the users and projects for auditing purpose. C does need appropriate permission to see the detail level data. Monthly view is tough to get directly from the bq ls or bq show commands.
  5. Dannyygcp 👍 10
    Yes..D is correct
Show 25 more comments
  1. omermahgoub 👍 7
    Option A, connecting Google Data Studio to BigQuery and creating a dimension for the users and a metric for the amount of queries per user, is a valid method of visualizing data, but it would not provide the specific information about the number of queries that were run by each user in the last month. Option B, executing a query on the JOBS table to get the required information, is not a viable option because the JOBS table does not contain information about the user who ran the query. Option C, using the 'bq show' and 'bq ls' commands to list job information, is not a viable option because these commands do not provide information about the user who ran the query.
  2. eff12c1 👍 7 Answered B
    Querying the INFORMATION_SCHEMA.JOBS_BY_USER view in BigQuery is the most efficient and straightforward way to obtain the number of queries each user ran in the last month. This method leverages built-in BigQuery capabilities designed specifically for auditing and monitoring query jobs. Cloud Audit Logs provide detailed logging information but are more complex to query for specific metrics like the number of queries run by each user. BigQuery’s INFORMATION_SCHEMA.JOBS_BY_USER is designed for this purpose and is easier to use for querying job data.
  3. awsgcparch 👍 7 Answered B
    Using the INFORMATION_SCHEMA.JOBS_BY_USER table within BigQuery is the most efficient and straightforward method to get the required audit information about the number of queries each user ran in the last month. Therefore, option B is the best choice.. D.While Cloud Audit Logs can provide detailed logs of activities, querying them directly for this purpose is less efficient than using the JOBS table in BigQuery. Additionally, setting up and querying audit logs involves more steps and may require exporting logs to BigQuery for complex queries.
  4. AD2AD4 👍 7
    Final Decision to go with Option D as per https://cloud.google.com/bigquery/docs/reference/auditlogs#example_query_cost_breakdown_by_identity
  5. VarunGo 👍 7 Answered B
    B is correct. here's the link - https://cloud.google.com/bigquery/docs/information-schema-jobs
  6. awsgcparch 👍 6 Answered B
    Why B is the Best Answer: Direct Access to Job Metadata: BigQuery maintains metadata about jobs (including query jobs) in the INFORMATION_SCHEMA views, specifically in the INFORMATION_SCHEMA.JOBS table. Detailed Information: This table contains information about all jobs, including who ran them, when they were run, and the type of job. This makes it easy to filter and count queries by user. Querying JOBS Table: You can write a SQL query to count the number of queries executed by each user over the specified period.
  7. AshwathD 👍 6
    It is B. ex: SELECT project_id, user_email,count(*) as CNT FROM region-us.INFORMATION_SCHEMA.JOBS group by project_id, user_email ;
  8. KouShikyou 👍 6
    I think C is correct.
  9. omermahgoub 👍 5
    The correct answer is D. Use Cloud Audit Logging to view Cloud Audit Logs, and create a filter on the query operation to get the required information. Google Cloud's Cloud Audit Logging service allows you to view, search, and export audit logs for your Google Cloud projects. These audit logs contain information about the actions that are performed in your project, including queries that are run in BigQuery. To see how many queries each user ran in the last month, you can use Cloud Audit Logging to view the Cloud Audit Logs for your BigQuery project. Then, you can create a filter on the query operation to see only the queries that were run. You can also create a filter on the user field to see the queries that were run by each user. This will allow you to see the number of queries that were run by each user in the last month, which can be useful for audit purposes.
  10. gcppandit 👍 5 Answered D
    A is not possible. B is possible if VIEW is used instead of TABLE in the description. I use this view to get this information regularly. C. I have no cloud how this can be right answer. D. Only possible as per text descriptions.
  11. JC0926 👍 4 Answered D
    D. Use Cloud Audit Logging to view Cloud Audit Logs, and create a filter on the query operation to get the required information. Cloud Audit Logging records activities and API calls in Google Cloud services, including BigQuery. You can use Cloud Audit Logging to view logs and filter them based on specific operations, such as queries in BigQuery. By filtering on the query operation, you can gather the required information about how many queries each user ran in the last month, which is essential for audit purposes.
  12. lynx256 👍 4
    There is no JOBS table in the INFORMATION_SCHEMA - so B is not correct. (ref: https://cloud.google.com/bigquery/docs/information-schema-jobs#schema) C needs some extra effort -- so it isn't so good. IMO D is correct. "BigQuery automatically sends audit logs to Cloud Logging" (ref: https://cloud.google.com/bigquery/docs/reference/auditlogs#stackdriver_logging_exports) Ref: https://cloud.google.com/logging/docs/audit Ref: https://cloud.google.com/logging/docs/audit#viewing_audit_logs
  13. 1P5811 👍 4 Answered B
    BigQuery's INFORMATION_SCHEMA: BigQuery provides metadata about datasets, tables, and jobs through the INFORMATION_SCHEMA. The JOBS_BY_USER view within this schema is specifically designed to give you information about jobs run by each user. You can easily query this view to get the number of queries run by each user in the last month.
  14. rajrocks171 👍 4 Answered D
    Option D. Cloud audit logs track all BiqQuery jobs including who ran each query, when and how. You can filter logs by operation type and group by user identity. Not Option B - Unless you are a project admin, by default JOBS table shows jobs for the current user. Also, retention period here is 7 days.
  15. checkmate 👍 4
    In the CLI, you can run bq ls -j -a to retrieve jobs for all users in a project. Then you can run for each job id a bq show -j <job_id> and in order to have more details you will choose to use the json response: Hence, C is not right, Use bq ls to list all jobs, use bq show to show the detail.
  16. Ayzen 👍 4
    If you read the blog post carefully you can notice that before starting analyzing data in Data Studio they import data from Stackdriver logs (basically, cloud audit logs). Since the task was not to create a beautiful charts, I'd go directly to audit logs.
  17. GunjGupta 👍 4
    Big query by default logs into cloud logging. we can add a filter to get data groups by users for specific months. This seems to be a more efficient way of getting data. I will go for option D
  18. shandy 👍 4
    C is right. https://stackoverflow.com/questions/30993782/how-retrieve-the-user-and-sql-code-associated-with-a-bigquery-job/30995665#30995665
  19. alii 👍 4
    yep, D is right, https://cloud.google.com/bigquery/docs/reference/auditlogs#data_access_data_access
  20. kumarp6 👍 4
    Try audit logs, D is OK
  21. pawel_ski 👍 3
    The retention period for the data access audit logs is 30 days. So you can’t get the information about queries each user ran in the LAST MONTH. D is incorrect. B is incorrect because of the word TABLE instead of VIEW. But when you assume that they ask about views JOBS_BY* the solution is ok for a one time action. You run once the query, get the result, present it to someone and … forget ;-). When you need to use it every month and present it to someone better is prepare something more permanent. In such case I would use Data Studio chart based on a data source: Custom Query from BQ. I did this in practice and it works. For me answer A looks correct, but it misses expression “use Custom Query as a data source” So we can’t be sure in 100%. ;-) https://cloud.google.com/bi-engine/docs/getting-started-data-studio
  22. mawsman 👍 3
    B. I say that since you can query bigquery.jobs table with the client libraries and query for the time range of last month.The correct CLI syntax would be bq ls -a -j -max_creation_time <max-age-in-unix-time> project-name. The -a would simply list all jobs and the -j will limit the age to what ever the unix date of the first day of the month in question was. The bq show command just examines the shcema so answer C is not correct.
  23. alpay 👍 3 Answered D
    "Audit logs versus INFORMATION_SCHEMA views Audit logs help you answer the question "Who did what, where, and when?" within your Google Cloud resources. Audit logs are the definitive source of information for system activity by user and access patterns and should be your primary source for audit or security questions." https://cloud.google.com/bigquery/docs/introduction-audit-workloads
  24. francisco94 👍 3 Answered D
    I agree B could be the solution, but the best option is D. This is the correct and scalable approach: Cloud Audit Logs capture who ran what, including queries. You can filter on methodName = "jobservice.jobcompleted" and analyze logs in Logs Explorer or export to BigQuery for querying. Supports organization-wide, project-wide, and per-user visibility.
  25. zh31427 👍 3
    I think it's an outdated question. the easiest way is to use the console to query the metadata table. I am only guessing that the INFORMATION_SCHEMA table used to be called JOBS and hterefore the correct answer was B. Let's hope exam questions are up to date. https://cloud.google.com/bigquery/docs/information-schema-jobs

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Multi-Regional vs Regional Cloud Storage for Global Software Downloads

S-grade

Surviving a Zone Outage With SQL Server on GCP

S-grade

Fixing BigQuery connection errors from a Compute Engine VM

S-grade

Moving 75 TB into Cloud Storage with Google-recommended practices

S-grade

Practice All PCA Questions

Access 81 questions with complete answers and detailed explanations.

View Full PCA Practice Test →

← Back to PCA Study Guide