I85PREP
⌘ K
GCP Data Practitioner study materials
GCP Data Practitioner study resources

GCP Data Practitioner: Analyze and Present Data

GCP Data Practitioner · Work through your exam, one topic at a time.

All guide sections

Analyze and Present Data

What this domain tests

Analyze and present data covers querying BigQuery correctly, controlling cost, and turning results into dashboards in Looker Studio. Questions often test SQL logic, partition pruning, joins, aggregation, and how a BI tool refreshes data.

Core rules

  • BigQuery on-demand queries are billed by bytes scanned. Filter on the partition column in WHERE, select only needed columns, and avoid SELECT * in persistent views.
  • LIMIT reduces rows returned, not bytes scanned. A dry run or the query validator shows estimated bytes before execution.
  • Use GROUP BY for aggregation and window functions such as SUM() OVER (PARTITION BY ... ORDER BY ...) for running totals without collapsing rows.
  • Joins multiply rows when the join key is not unique. Check cardinality first with COUNT(*) and COUNT(DISTINCT key).
  • Use APPROX_COUNT_DISTINCT for large cardinality estimates where exact counts are unnecessary.
  • Materialized views pre-aggregate repeated queries; BI Engine can accelerate dashboard queries. Both add cost or capacity considerations.
  • Looker Studio connects live to BigQuery by default; extracts copy data and can be stale. Dashboards should use date-range controls and filters that push down to the query.

Worked example

You need the top five product categories by revenue in the last 30 days from an orders table partitioned by order_date. Write: SELECT category, SUM(amount) AS revenue FROM orders WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY category ORDER BY revenue DESC LIMIT 5. Reasoning: the WHERE clause prunes partitions; SUM aggregates before LIMIT; ORDER BY uses the alias. If LIMIT were applied before aggregation the result would be wrong. In Looker Studio, connect live, add a date-range control bound to order_date, and avoid an extract unless the dashboard tolerates staleness.

Traps

  • Putting LIMIT on a subquery and assuming it lowers cost.
  • Using SELECT * in a view that feeds a dashboard.
  • Joining a fact table to a table with duplicate keys and then summing inflated revenue.
  • Filtering with a function on the partition column, such as DATE(order_date) = ..., which can prevent pruning.
  • Forgetting that Looker Studio extracts do not refresh automatically in real time.
  • Counting with COUNT(DISTINCT ...) on huge columns when an approximate count would do.