Skip to content

Associate Data Practitioner · Domain 2: Data Analysis and Presentation · 27% of the exam

Task 2.1: Identify data trends, patterns, and insights by using BigQuery and Jupyter notebooks.

Writing BigQuery SQL for reports and insights, and analyzing and visualizing data in Colab Enterprise notebooks to answer business questions.

Study it

Sample questions

Try each one before opening the answer. Every option is explained, with the Google Cloud documentation page that proves it.

Question 1 · choose 1

A table `sales.monthly` has one row per region and month with a revenue column. A report must show, on every row, the revenue that region has earned from the start of the year up to and including that month, with each region's total starting again from zero. Which select expression returns that column?

  1. A`SUM(revenue) OVER (PARTITION BY region)`
  2. B`SUM(revenue) OVER (ORDER BY month)`
  3. C`SUM(revenue) OVER (PARTITION BY region ORDER BY month)`
  4. D`SUM(revenue) OVER (PARTITION BY month ORDER BY region ASC)`
Show the answer and why
  • A`SUM(revenue) OVER (PARTITION BY region)`

    Incorrect

    Without an ORDER BY the window covers the whole partition, so every row shows the region's full-year total rather than a total to date.

  • B`SUM(revenue) OVER (ORDER BY month)`

    Incorrect

    This accumulates in month order but across all regions together, so the total does not start again for each region.

  • C`SUM(revenue) OVER (PARTITION BY region ORDER BY month)`

    Correct

    PARTITION BY restarts the calculation for each region, and ORDER BY gives a window from the first month up to the current row, which is a cumulative sum.

  • D`SUM(revenue) OVER (PARTITION BY month ORDER BY region ASC)`

    Incorrect

    This groups the rows by month and accumulates across regions in name order, which mixes regions instead of adding up months.

A window function computes a value for each row over a window of related rows. PARTITION BY defines the groups that restart the calculation; ORDER BY inside OVER sets the order, and with ORDER BY and no explicit frame the window runs from the start of the partition to the current row, which gives a running (cumulative) total.

Question 2 · choose 1

A project uses BigQuery on-demand pricing. One weekly report query has occasionally scanned 20 times more data than usual after upstream table changes. The team wants that query stopped before it runs, at no charge, whenever it would process more than 2 TB, while the analysts' other queries in the project keep running normally. What should the team configure?

  1. AA Cloud Billing budget on the project's BigQuery spend, with a threshold at the cost of 2 TB
  2. BA dry run of the report query before each weekly run
  3. CA LIMIT clause on the report query's final SELECT
  4. DA maximum bytes billed setting of 2 TB on the report query
Show the answer and why
  • AA Cloud Billing budget on the project's BigQuery spend, with a threshold at the cost of 2 TB

    Incorrect

    A budget tracks actual or forecast spend for the period against a set amount and sends alert emails; it does not stop one query before it runs, and a budget amount is not a per-query limit.

  • BA dry run of the report query before each weekly run

    Incorrect

    A dry run only estimates the bytes a query would process; nothing stops the real run when the estimate is too high.

  • CA LIMIT clause on the report query's final SELECT

    Incorrect

    On a table that is not clustered, LIMIT does not reduce the data read or the compute cost; the query still scans the same columns.

  • DA maximum bytes billed setting of 2 TB on the report query

    Correct

    With maximum bytes billed, BigQuery estimates the bytes before execution and fails the query without charge if the estimate is above the limit; it applies to that query only.

BigQuery offers several cost controls: dry runs estimate a query's bytes, Cloud Billing budgets send alerts as spend approaches a planned amount, and the maximum bytes billed setting stops an individual query that would exceed a limit, without incurring a charge. A per-query cap is the control for one query that sometimes balloons.

Question 3 · choose 1

A wall-screen tile in an operations room re-runs the same aggregate query on `ops.events` every five minutes under one service account. Rows reach the table continuously as streaming inserts, and every run is billed in full. The screen may show figures up to an hour old, and the tile must keep refreshing every five minutes. Which change lets most of the refreshes be served from cached results?

  1. APoint the tile at a summary table that a scheduled query rebuilds from ops.events once an hour
  2. BKeep querying ops.events, but filter out the rows that arrived in the last hour so the result stays the same between runs
  3. CHave the tile's query write its result to a permanent destination table that keeps it between runs
  4. DRun the tile's query at batch priority instead of interactive priority
Show the answer and why
  • APoint the tile at a summary table that a scheduled query rebuilds from ops.events once an hour

    Correct

    The summary table receives no streaming inserts and changes only once an hour, so identical refreshes between rebuilds can be answered from the cache at no charge.

  • BKeep querying ops.events, but filter out the rows that arrived in the last hour so the result stays the same between runs

    Incorrect

    Results are not cached while a referenced table has recently received streaming inserts, even if no new rows match; the filter does not change that.

  • CHave the tile's query write its result to a permanent destination table that keeps it between runs

    Incorrect

    Query results are not cached when a destination table is specified in the job configuration, so this removes cache hits instead of creating them.

  • DRun the tile's query at batch priority instead of interactive priority

    Incorrect

    Cached results are kept for both interactive and batch queries; the priority does not lift the streaming-insert exception.

BigQuery returns cached results, at no charge, when the query text is identical and the referenced tables have not changed, typically for up to 24 hours. Results are not cached when a referenced table has recently received streaming inserts (data in write-optimized storage), when a destination table is specified, or when other listed exceptions apply. A dashboard over a constantly streamed table can read a periodically rebuilt summary table instead.

Question 4 · choose 1

In a Jupyter notebook that is connected to the BigQuery project, an analyst wants to run a SQL query on weekly sales in one cell and get the result as a pandas DataFrame named `weekly`, which the next cell will chart with matplotlib. What should the first cell contain?

  1. AA shell command `!bq query` with the SQL, followed by the DataFrame name
  2. BThe `%%bigquery weekly` cell magic, followed by the SQL statement
  3. CAn `EXPORT DATA` statement that writes the result to a file named weekly
  4. DA `CREATE TEMP TABLE weekly AS` statement with the SQL statement
Show the answer and why
  • AA shell command `!bq query` with the SQL, followed by the DataFrame name

    Incorrect

    The bq tool prints the result to the cell output as text; it does not create a Python DataFrame that the next cell can plot.

  • BThe `%%bigquery weekly` cell magic, followed by the SQL statement

    Correct

    The %%bigquery magic runs the query in BigQuery and returns the results in a DataFrame under the name given after the magic.

  • CAn `EXPORT DATA` statement that writes the result to a file named weekly

    Incorrect

    EXPORT DATA writes query results to Cloud Storage; a file in a bucket is not a DataFrame in the notebook's Python session.

  • DA `CREATE TEMP TABLE weekly AS` statement with the SQL statement

    Incorrect

    A temporary table lives in BigQuery for the session; it does not place the rows in a Python variable for matplotlib.

Notebooks combine SQL with Python. The %%bigquery cell magic sends the cell's SQL to BigQuery and stores the result as a DataFrame variable, which Python libraries such as pandas and matplotlib can then use for analysis and charts.

Question 5 · choose 1

A new analyst wants to glance at some rows of a 3 TB BigQuery table to see what the columns contain before writing any report queries. The project uses on-demand pricing. What is the cheapest way to look at sample rows?

  1. ARun `SELECT *` on the table in the query editor and stop it early
  2. BOpen the table's Preview tab in the console, or use `bq head`
  3. CRun the query as a batch query, which BigQuery does not bill
  4. DCreate a copy of the table and query the copy instead
Show the answer and why
  • ARun `SELECT *` on the table in the query editor and stop it early

    Incorrect

    Under on-demand pricing a query is billed for the bytes it processes, so exploring a 3 TB table with queries can be expensive.

  • BOpen the table's Preview tab in the console, or use `bq head`

    Correct

    Table preview options show sample data at no charge and without affecting quotas; the Preview tab and bq head are the documented ones.

  • CRun the query as a batch query, which BigQuery does not bill

    Incorrect

    Batch priority only changes when the query starts; batch queries are billed for the bytes they process like interactive ones.

  • DCreate a copy of the table and query the copy instead

    Incorrect

    A copy adds storage for a second table, and a query on the copy reads the same data and is billed the same way.

Exploring data with queries costs money under on-demand pricing. BigQuery provides free preview options for that: the Preview tab on the table details page in the console and the bq head command, neither of which is charged or counted against quotas.

Question 6 · choose 1

In a BigQuery Studio notebook, an analyst has run a query whose result is available as the DataFrame `monthly_revenue`. Her manager wants a bar chart of revenue by region right in the notebook, and the analyst does not write Python plotting code. What should she add to the notebook?

  1. AA visualization cell that uses the monthly_revenue DataFrame
  2. BA text cell that describes the chart in Markdown
  3. CA second SQL cell that sorts the result by region
  4. DA chart template copied from the notebook gallery
Show the answer and why
  • AA visualization cell that uses the monthly_revenue DataFrame

    Correct

    A visualization cell generates a chart from any DataFrame in the notebook without Python code; she picks the chart type, the columns and the aggregation, and can set colors, labels and titles.

  • BA text cell that describes the chart in Markdown

    Incorrect

    Text cells hold explanations and images written in Markdown; they do not draw a chart from the data.

  • CA second SQL cell that sorts the result by region

    Incorrect

    A SQL cell runs GoogleSQL and saves its output as a DataFrame; sorting the rows still leaves a table, not a bar chart.

  • DA chart template copied from the notebook gallery

    Incorrect

    The notebook gallery offers prebuilt notebook templates for common tasks; copying one does not chart this DataFrame without code changes.

Each kind of notebook cell has its own job. A visualization cell turns a DataFrame into a chart through a chart interface, so analysts can explore query results visually without writing matplotlib or other plotting code.

Question 7 · choose 1

In a BigQuery Studio notebook, an analyst wrote two SELECT statements in one SQL cell titled `sales`: one for 2025 orders and one for 2026 orders. The Python cells that follow need both results to compare the two years. How should she set up the SQL?

  1. AKeep both statements in the cell; each result becomes its own DataFrame
  2. BKeep both statements in the cell and end each one with a semicolon
  3. CPut each SELECT statement in its own SQL cell, each with its own title
  4. DAdd a visualization cell after the SQL cell to split the results
Show the answer and why
  • AKeep both statements in the cell; each result becomes its own DataFrame

    Incorrect

    A SQL cell can run several statements, but only the results of the last statement are saved to a DataFrame, so the 2025 result would be lost.

  • BKeep both statements in the cell and end each one with a semicolon

    Incorrect

    Separating the statements lets both run, yet the cell still saves only the result of the last statement.

  • CPut each SELECT statement in its own SQL cell, each with its own title

    Correct

    Each SQL cell saves its output as a DataFrame named after the cell's title, so two cells give two DataFrames that the Python cells can use.

  • DAdd a visualization cell after the SQL cell to split the results

    Incorrect

    A visualization cell charts a DataFrame that already exists; it cannot recover a result that the SQL cell did not save.

In BigQuery Studio notebooks, a SQL cell runs GoogleSQL and saves the output of its last statement as a DataFrame with the same name as the cell's title. To work with several result sets in Python, give each query its own SQL cell.

Question 8 · choose 1

Before a privacy review, an analyst must list every table in the dataset `crm` that has a column named `email`, using one SQL query. Which view should the query read?

  1. A`crm.INFORMATION_SCHEMA.JOBS`
  2. B`crm.INFORMATION_SCHEMA.TABLE_STORAGE`
  3. C`crm.INFORMATION_SCHEMA.COLUMNS`
  4. D`crm.INFORMATION_SCHEMA.ROUTINES`
Show the answer and why
  • A`crm.INFORMATION_SCHEMA.JOBS`

    Incorrect

    The JOBS view describes query and load jobs that have run, not the columns that tables contain.

  • B`crm.INFORMATION_SCHEMA.TABLE_STORAGE`

    Incorrect

    TABLE_STORAGE reports storage figures such as row counts and bytes per table; it does not list column names.

  • C`crm.INFORMATION_SCHEMA.COLUMNS`

    Correct

    The COLUMNS view has one row for each column of each table in the dataset, with table_name and column_name, so a WHERE column_name = 'email' filter answers the question.

  • D`crm.INFORMATION_SCHEMA.ROUTINES`

    Incorrect

    The ROUTINES view lists stored procedures and functions, not table columns.

INFORMATION_SCHEMA views expose BigQuery metadata through SQL. COLUMNS lists the columns of every table in a dataset, TABLES lists the tables, TABLE_STORAGE gives storage sizes, and JOBS describes jobs that have run.

Question 9 · choose 1

In one pass over an orders table, a report must show, per region, the total number of orders and how many of them had an amount above 100. Which expression gives the second figure in a query grouped by region?

  1. A`COUNT(amount > 100)`
  2. B`SUM(amount) WHERE amount > 100`
  3. C`MAX(amount > 100)`
  4. D`COUNTIF(amount > 100)`
Show the answer and why
  • A`COUNT(amount > 100)`

    Incorrect

    COUNT counts non-NULL values, and the comparison is TRUE or FALSE (not NULL) for every order with an amount, so this counts nearly all orders.

  • B`SUM(amount) WHERE amount > 100`

    Incorrect

    An aggregate cannot carry its own WHERE clause, and a sum of amounts is not a count of orders.

  • C`MAX(amount > 100)`

    Incorrect

    MAX returns a single largest value, TRUE if any order is above 100, not a count.

  • D`COUNTIF(amount > 100)`

    Correct

    COUNTIF returns the number of TRUE values of its expression, so it counts the orders above 100 alongside COUNT(*) in the same grouped query.

BigQuery's aggregate functions include COUNTIF, which counts the rows where a condition is TRUE. It lets one grouped query return several conditional counts without separate filtered queries.

Question 10 · choose 1

The BigQuery table `sales.order_lines` has one row per order line, with the columns order_id, product_id and line_amount; an order has between 1 and 20 lines. A manager asks for the average value of an order placed in October. Which expression over October's rows answers that question?

  1. A`SUM(line_amount) / COUNT(DISTINCT order_id)`
  2. B`AVG(line_amount)`
  3. C`SUM(line_amount) / COUNT(order_id)`
  4. D`AVG(DISTINCT line_amount)`
Show the answer and why
  • A`SUM(line_amount) / COUNT(DISTINCT order_id)`

    Correct

    SUM adds up every line, and COUNT(DISTINCT order_id) counts each order once, so the ratio is the total value divided by the number of orders.

  • B`AVG(line_amount)`

    Incorrect

    AVG averages the values of the input rows, here order lines, so it gives the average line amount, which is lower than the average order value whenever orders have several lines.

  • C`SUM(line_amount) / COUNT(order_id)`

    Incorrect

    COUNT(order_id) counts every row with a non-NULL order_id, one per line, so this is again the average per line rather than per order.

  • D`AVG(DISTINCT line_amount)`

    Incorrect

    With DISTINCT, AVG averages each distinct line amount once, which drops repeated amounts and still works at the line level.

Turn the business question into the grain it asks about. Here the unit is an order, but each row is an order line, so the numerator is the total of all lines and the denominator is the number of distinct orders. AVG and COUNT over the rows answer a question about lines instead.

Practice domain 2 →Practice all domains →