Domain 4 of 5 · Chapter 1 of 3

Data for Visualization

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Professional Data Engineer premium course — no separate purchase.

Included in this chapter:

  • Connecting BI tools to BigQuery
  • Precompute the answer so the dashboard reads less
  • BI Engine: serve hot columns from memory
  • Troubleshooting a slow dashboard query
  • Securing the serving layer, and exam traps

Precompute and serve choices for BI

MechanismWhat it doesRefresh / freshnessReach for it when
Materialized viewStores precomputed query result; auto-rewrites matching base-table queriesIncremental, reads only base-table changesThe same aggregation runs often over slowly-changing data
Logical viewStores only the query text; recomputes on every readAlways current, no precomputeYou want one definition without precompute, freshness over speed
Scheduled query / summary tableRuns SQL on a cron and writes a results tableOn the schedule you setThe shape is too complex for a materialized view
BI Engine reservationServes frequently queried columns from memory, no query changesCache follows the live tablesInteractive dashboards are slow on repeated reads

Decision tree

Dashboard is slowread the query plan firstSame aggregation,slow-changing data?Materialized viewprecompute + auto-rewriteYesSlow on repeatedinteractive reads?NoBI Engine reservationin-memory, no query changeYesOne query scansfar too much?NoLayout fixpartition, cluster, no SELECT *YesSlot contentionadd slots / autoscaleNo
Cheat sheet

Cheat sheet

  • Back a repeated dashboard aggregation with a materialized view
  • Use a scheduled query for a summary too complex to materialize
  • BI Engine accelerates queries with no query or tool change
  • BI Engine fixes repeated reads, not one over-scanning query
  • Looker queries the warehouse live and defines metrics once in LookML
  • Connected Sheets queries BigQuery live from a spreadsheet
  • Third-party BI tools connect to BigQuery over JDBC/ODBC drivers
  • Diagnose a slow query from its plan before resizing the warehouse
  • Slot contention is the one case where adding slots is the fix
  • Large shuffle points to a join or skew, fixed by denormalizing
  • Avoid SELECT * so columnar storage reads only needed columns
  • Partition the table on the column the dashboard filters by
  • Use an authorized view to expose a slice without table access
  • Column-level security hides a column behind a policy tag
  • Row-level security filters which rows a user sees
  • Dynamic data masking hides column values at query time, no copy
  • De-identify the stored data with Sensitive Data Protection
  • Validate only new rows with a Dataplex incremental data-quality scan keyed on a date/timestamp column
  • Use a Dataplex data profile scan to learn null counts, distributions, and common values
  • Generate data-quality rules from profile-based recommendations instead of writing them by hand
  • Investigate failed records with the query Dataplex provides for a failed row-level rule
  • Configure email notifications on a data-quality scan to alert when the score drops below a threshold
  • Publish data-quality results to BigQuery so scores appear on the table and feed Looker
  • Pick anomaly-based sampling in Dataprep to surface mismatched and missing rows
  • Click the red data-quality bar in a Dataprep column to fix mismatched values
  • Package repeated Dataprep steps into a macro and share it by import/export
  • A clustered table is only pruned when the query filters the first clustering column
  • Pin tables to the BI Engine preferred-tables list so only hot partitions load into memory
  • Satisfy a required partition filter in Looker Studio with a custom query or the date-range-dimension checkbox
  • Policy-tag taxonomies are regional, so replicate them with Data Catalog taxonomies.import

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. Looker introduction
  2. BigQuery ODBC and JDBC drivers
  3. Introduction to materialized views
  4. Schedule queries
  5. Introduction to BigQuery BI Engine
  6. Get query performance insights
  7. Introduction to partitioned tables
  8. Introduction to reservations
  9. Create authorized views
  10. Introduction to column-level access control
  11. Introduction to row-level security
  12. Introduction to data masking
  13. Sensitive Data Protection overview