Professional Data Engineer · Domain 4: Preparing and using data for analysis · 15% of the exam
Task 4.1: Preparing data for visualization.
Connecting BI tools, precalculating fields, BI Engine and materialized views, troubleshooting slow queries, and securing what dashboards show with IAM, data masking and Sensitive Data Protection.
Study it
Serving dashboards: BI Engine, materialized views, Data Studio and Looker
Try each one before opening the answer. Every option is explained, with the Google Cloud documentation page that proves it.
Question 1 · choose 1
Executives use a Data Studio dashboard over a 40 GB BigQuery table that is refreshed every hour. Each page load sends about a dozen small aggregate queries that take three to five seconds each. The goal is sub-second responses for these queries without changing the dashboard or its queries. What should the data engineer do?
ACreate materialized views for the aggregates and point the dashboard at them
BCreate a BI Engine reservation and add the table as a preferred table
CAssign the dashboard's project to a reservation with more slots
DCluster the table on the columns that the dashboard filters on
Show the answer and why
ACreate materialized views for the aggregates and point the dashboard at them
Incorrect
Repointing the dashboard at new views is a dashboard change, and materialized views alone do not promise sub-second interactive responses.
BCreate a BI Engine reservation and add the table as a preferred table
Correct
BI Engine caches the data in memory and accelerates SQL from BI tools such as Data Studio without changes to them; preferred tables focus the memory on this table.
CAssign the dashboard's project to a reservation with more slots
Incorrect
More slots help queries that are short of compute; small dashboard queries are dominated by fixed overhead that BI Engine removes.
DCluster the table on the columns that the dashboard filters on
Incorrect
Clustering reduces bytes read, but a 40 GB table already scans quickly; it does not bring small queries to sub-second.
BI Engine is BigQuery's in-memory analysis service. It accelerates many SQL queries from BI tools and API clients without modifying them, works best on simple dashboard-style queries, and falls back to normal slots for stages it cannot accelerate. A reservation sets its memory, and preferred tables limit acceleration to the tables that matter.
A sales dashboard in Data Studio reads a BigQuery table whose row access policies give each regional manager only the rows for their region. Queries that managers run directly in BigQuery are filtered correctly, but in the dashboard every manager sees all regions. What should the data engineer change?
ARebuild the data source on an authorized view over the table
BSwitch the data source to service account credentials
CSet the data source to use the viewer's credentials
DGrant each manager BigQuery Data Viewer on the dataset
Show the answer and why
ARebuild the data source on an authorized view over the table
Incorrect
An authorized view would still be queried with the same credentials, so the filtering problem would remain.
BSwitch the data source to service account credentials
Incorrect
Every viewer would then query as the one service account, which again sees whatever that account sees, not each manager's rows.
CSet the data source to use the viewer's credentials
Correct
With viewer's credentials, each viewer's own identity runs the queries, so the row access policies filter rows for that manager.
DGrant each manager BigQuery Data Viewer on the dataset
Incorrect
The managers already read the table directly; the dashboard is not using their identities, so more grants change nothing.
A Data Studio data source runs its queries with the owner's credentials by default, so viewers see what the owner sees. Row-level security filters by the identity that runs the query; to apply it per viewer, the data source must use viewer's credentials, and each viewer then needs their own BigQuery permissions.
A dashboard runs the same query every few minutes for hundreds of viewers. It sums the previous calendar day's sales with the filter DATE(order_ts) = DATE(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)). The table is loaded once a night, yet billing shows that every run is charged and none is served from cached results. What change lets repeated runs use the query cache?
ACompute the date in the dashboard and pass it to the query as a literal
BRun the query as a batch-priority job so that it waits for idle slots
CWrite the query results to a destination table on each run
DAdd a LIMIT clause so that the result set is small enough to cache
Show the answer and why
ACompute the date in the dashboard and pass it to the query as a literal
Correct
CURRENT_TIMESTAMP() is non-deterministic, which stops BigQuery from caching the result; identical query text with a literal date can be served from the cache until the table changes.
BRun the query as a batch-priority job so that it waits for idle slots
Incorrect
Priority changes when a query starts, not whether its results can be cached.
CWrite the query results to a destination table on each run
Incorrect
Specifying a destination table is itself one of the conditions that prevents a query from using cached results.
DAdd a LIMIT clause so that the result set is small enough to cache
Incorrect
The result is already small; the non-deterministic function is what makes BigQuery skip the cache.
BigQuery returns cached results free of charge when the same query text runs again within about 24 hours and the referenced tables have not changed. Queries with non-deterministic functions such as CURRENT_TIMESTAMP(), with a destination table, or on tables with recent streaming are not cached.
Dozens of dashboard tiles run the same daily revenue aggregation over a 20 TB orders table that receives new rows every few minutes. Each tile scans the base table, and costs are rising. The results must reflect new rows quickly, and the dashboard queries should keep targeting the base table. What should the data engineer create?
AA logical view that holds the aggregation query for the tiles
BA scheduled query that rebuilds a summary table each hour
CA materialized view over the orders table with the aggregation
DA table snapshot of the orders table taken at the start of the day
Show the answer and why
AA logical view that holds the aggregation query for the tiles
Incorrect
A logical view stores only the query text, so every tile still scans the 20 TB table.
BA scheduled query that rebuilds a summary table each hour
Incorrect
An hourly rebuild leaves results up to an hour old and requires the dashboards to read the new table.
CA materialized view over the orders table with the aggregation
Correct
Materialized views precompute the result, refresh incrementally as base data changes, and BigQuery can rewrite queries on the base table to use them automatically.
DA table snapshot of the orders table taken at the start of the day
Incorrect
A snapshot is a frozen copy of the table, so it misses the day's new rows and is no smaller to scan.
Materialized views cache the results of a query and keep them current with incremental refreshes that read only changed data. Because BigQuery can automatically rewrite matching queries on the base table to use the view, dashboards gain speed and lower cost without being repointed.
Finance planners do all of their work in Google Sheets with pivot tables and charts. They now need to analyze a 2-billion-row BigQuery table of transactions without learning SQL and without copying the data into spreadsheets, which could not hold it anyway. What should the data engineer set up?
AConnected Sheets, linked to the BigQuery table
BA daily CSV export of the table into a shared Drive folder
CA Data Studio report with filters for each planner
DBigQuery Studio notebooks shared with the planners
Show the answer and why
AConnected Sheets, linked to the BigQuery table
Correct
Connected Sheets lets users analyze BigQuery data from Sheets with pivot tables, charts and formulas while the queries run in BigQuery.
BA daily CSV export of the table into a shared Drive folder
Incorrect
Exports copy the data, go stale, and a 2-billion-row file is far beyond what a spreadsheet can open.
CA Data Studio report with filters for each planner
Incorrect
A report works for viewing, but the planners want to keep working with pivot tables and formulas in Sheets.
DBigQuery Studio notebooks shared with the planners
Incorrect
Notebooks need code, which the planners do not write.
Connected Sheets brings BigQuery into Google Sheets: users build pivot tables, charts and formulas over the full table, and each operation runs as a BigQuery query, so nothing is copied and spreadsheet size limits do not apply.
Three departments report "net revenue" from the same BigQuery tables, and each computes it differently in its own dashboards. Leadership wants each metric defined once, in version-controlled code, and reused by every dashboard and ad hoc exploration. What should the data engineer use?
AA shared spreadsheet that documents each metric's formula
BCalculated fields copied into each department's reports
CBigQuery table descriptions that state each metric's formula
DA Looker model in LookML that defines the metrics centrally
Show the answer and why
AA shared spreadsheet that documents each metric's formula
Incorrect
A document describes the definition but does not make any dashboard use it.
BCalculated fields copied into each department's reports
Incorrect
Copies of the formula drift apart, which is how the three versions came about.
CBigQuery table descriptions that state each metric's formula
Incorrect
Descriptions are metadata for readers; queries do not compute anything from them.
DA Looker model in LookML that defines the metrics centrally
Correct
LookML defines dimensions and measures once in a version-controlled model, and every Explore and dashboard built on it uses those definitions.
A semantic layer separates metric definitions from reports. In Looker, LookML describes how dimensions and measures are calculated from the database, kept in Git, so every query generated from the model uses the same definition of net revenue.
A dashboard's service account must read a curated subset of an HR table: some columns, and only active employees. The service account must not be able to query the base table, and the HR team keeps the dataset that holds the base table locked down. What should the data engineer create?
AA logical view in the HR dataset, with Data Viewer on the HR dataset
BA view in a reporting dataset, authorized on the HR dataset
CA copy of the subset, refreshed by a nightly scheduled query
DRow access policies on the base table for the service account
Show the answer and why
AA logical view in the HR dataset, with Data Viewer on the HR dataset
Incorrect
Data Viewer on the HR dataset lets the service account read the base table directly.
BA view in a reporting dataset, authorized on the HR dataset
Correct
An authorized view can read the source tables on its own authority, so the service account needs access only to the view's dataset.
CA copy of the subset, refreshed by a nightly scheduled query
Incorrect
A copy works but is up to a day old and is one more table to secure and maintain.
DRow access policies on the base table for the service account
Incorrect
Row policies limit rows but still give the service account the base table, with every column the policy does not hide.
An authorized view lives in a separate dataset and is added to the source dataset's access list. Users of the view need access only to the view's dataset; the view reads the base table on their behalf and exposes only the columns and rows its query selects.