10.4 Preparing Data for Visualization and BI Tool Connectivity
Key Takeaways
- BI Engine accelerates Looker, Looker Studio, Connected Sheets, Tableau, Power BI, JDBC and ODBC clients, and custom applications, and query stages it cannot optimize fall back to standard slots rather than failing.
- Connected Sheets previews 500 rows, returns up to 200,000 rows in a pivot table, supports extracts up to 500,000 rows with a 5 million cell cap above 50,000 rows, and runs real BigQuery queries that incur normal costs.
- Looker Studio BigQuery data sources cache for 1 to 50 minutes or 1 to 12 hours with a 12-hour default, and BigQuery query charges apply on every refresh.
- Owner's credentials share one cache and evaluate access as the report owner, while viewer's credentials give each viewer an independent cache and apply their own row access policies and policy tags.
- Precalculated fields belong as far left as possible: stored columns from the transformation layer, materialized views, or aggregate tables beat per-report calculated fields on cost, freshness, and metric consistency.
10.4 Preparing Data for Visualization and BI Tool Connectivity
Sub-section 4.1 of the exam guide is titled Preparing data for visualization, and its first two bullets are "connecting to tools" and "precalculating fields." Sub-section 4.3 adds "publishing reports and visualizations." Together these test a distinct skill from query tuning: given a dashboard that is slow, expensive, or leaking data, decide where in the stack the fix belongs — the warehouse, the semantic layer, the acceleration layer, or the tool's own settings.
The governing principle is that computation should move as far left as possible. A calculation performed once during transformation is cheaper and more consistent than the same calculation performed by every viewer of every dashboard, every time they change a filter.
The Connectivity Catalog
| Tool | How It Reaches BigQuery | Best Fit |
|---|---|---|
| Looker | Native connection with a LookML semantic layer over the warehouse | Governed enterprise metrics, consistent definitions, row-level governance through user attributes |
| Looker Studio | Native BigQuery connector, live or extract | Free, fast, self-service dashboards and report sharing |
| Connected Sheets | Runs BigQuery queries on the user's behalf from Google Sheets | Analysts who want spreadsheet ergonomics over billion-row tables |
| JDBC / ODBC drivers | Standard driver interfaces | Tableau, Power BI, and legacy desktop tools |
| BigQuery API / client libraries | Direct programmatic access | Custom applications and embedded analytics |
BI Engine sits underneath all of them. It accelerates queries from BI tools including Looker, Looker Studio, Tableau, and Power BI, from Connected Sheets, from custom applications through the BigQuery API, and through the JDBC and ODBC drivers. Its most important operational property is graceful fallback: query stages that BI Engine cannot optimize automatically fall back to standard BigQuery execution slots rather than failing the query. A dashboard never breaks because BI Engine ran out of capacity — it just gets slower, which is why a sudden latency regression with no errors is a BI Engine capacity symptom.
Connected Sheets: Know the Limits
Connected Sheets is the one connector with limits specific enough to be tested directly:
| Operation | Documented Limit |
|---|---|
| Preview display in the sheet | 500 rows |
| Pivot table results | 200,000 rows |
| Extract, up to 50,000 rows | No cell limit |
| Extract, 50,001 to 500,000 rows | Maximum 5 million cells |
| Extract, above 500,000 rows | Not supported |
Two behavioral details matter as much as the numbers. First, every chart, pivot, formula, or calculated cell runs a real BigQuery query on the user's behalf, so Connected Sheets spends slots and bytes exactly like the console does. Second, scheduled refreshes do not propagate end-user context such as IP address or device information, which can cause them to fail under certain VPC Service Controls configurations even though the same refresh succeeds when run manually — an excellent scenario for a question about a nightly refresh that mysteriously fails.
Looker Studio: Freshness and Credentials
Looker Studio caches results and serves from cache until the configured freshness threshold expires. For BigQuery data sources the documented options are every 1 to 50 minutes, or every 1 to 12 hours, with 12 hours as the default. A one-minute cooldown applies between manual refreshes.
The credential mode is the governance decision:
- Owner's credentials — every viewer sees data through the report owner's access. All viewers share one cache, which is efficient, but row access policies and column-level security are evaluated against the owner, so the report can expose data a viewer could not query themselves.
- Viewer's credentials — each viewer's own IAM, row access policies, and policy tag grants apply, and each viewer gets an independent cache. This is the setting that makes row-level security actually reach the dashboard.
And the fact that surprises teams: BigQuery query costs apply whenever Looker Studio queries the underlying project. A report with a five-minute freshness setting, fifty viewers, and an unpartitioned source table is a recurring cost incident waiting to happen.
Precalculating Fields: The Ladder
When a dashboard computes something expensive, there are five places the computation can live. They are ordered here from cheapest and most consistent to most expensive and most fragile — and the exam almost always rewards moving up this list.
| Rank | Where the Calculation Lives | Cost Profile | Freshness | Consistency |
|---|---|---|---|---|
| 1 | Transformation layer (Dataform / scheduled query writes a stored column) | Paid once per load | As of last run | One definition, shared by every tool |
| 2 | Materialized view | Incremental refresh; queries rewritten automatically | Near-real-time | One definition in BigQuery |
| 3 | Aggregate / rollup table at report grain | Paid once per load; tiny scans | As of last run | One definition |
| 4 | Looker PDT + aggregate awareness | Built on a schedule; Looker routes queries to the smallest usable table | As of last build | One definition in LookML |
| 5 | BI tool calculated field (Looker Studio formula, Sheets formula) | Recomputed on every query, for every viewer | Live | Redefined in every report |
A concrete illustration: profit_margin = (revenue - cost) / NULLIF(revenue, 0) defined as a Looker Studio calculated field is evaluated on every chart render for every user, and the next analyst who builds a report will define it slightly differently. The same expression materialized as a column in the curated table is computed once per load, is identical everywhere, and lets BI Engine cache a simple column read instead of an arithmetic expression.
Materialized views deserve a special mention because they are the only option on this list that BigQuery can substitute automatically: smart tuning rewrites a query against the base table to read the materialized view when the view can satisfy it, so dashboards benefit without being rewritten. That makes them the highest-leverage answer when a scenario says the dashboards cannot be modified.
Publishing Reports and Visualizations
The publishing bullet is mostly a governance bullet. Three controls do the work:
- Credential mode, as above. Viewer's credentials are what carry row access policies and policy tags to the consumer. Owner's credentials deliberately bypass them and should be paired with a curated, pre-filtered view rather than a raw table.
- Authorized views and authorized datasets. Publishing a view that exposes a filtered or masked subset of a sensitive table, and authorizing that view against the source dataset, lets you grant access to the report without granting access to the source. This is the standard answer for sharing a slice with a business unit.
- Looker user attributes. In a Looker deployment, user attributes drive access filters in LookML so the same dashboard renders differently per region, per sales territory, or per tenant, without maintaining a report per audience.
For distribution, scheduled email delivery and embedded reports are the common patterns; for cross-organization publication of the underlying data rather than the picture of it, the answer moves to Analytics Hub and linked datasets, covered separately.
A Dashboard Performance Checklist
| Symptom | First Thing to Check |
|---|---|
| Dashboard got slower with no errors | BI Engine reservation capacity — stages are falling back to standard slots |
| Costs spiked after a dashboard launch | Freshness interval too short; add a partition filter and lengthen the cache window |
| Every filter change scans the full table | Source table lacks partitioning or require_partition_filter; filters are not pruning |
| Identical metric differs between two reports | The metric is a per-report calculated field; move it into the warehouse or the semantic layer |
| Nightly Connected Sheets refresh fails, manual refresh works | Scheduled refreshes do not propagate end-user context; check VPC Service Controls |
| Viewers see rows they should not | Report uses owner's credentials; switch to viewer's credentials or publish an authorized view |
Exam Traps and Antipatterns Summary
| Scenario Cue | Wrong Answer | Correct Approach |
|---|---|---|
| "Dashboards are slow and cannot be rewritten" | Ask analysts to add filters | Materialized view — smart tuning rewrites existing queries automatically |
| "The same KPI shows three different values" | Publish a definitions document | Precalculate in the transformation layer or define it once in the LookML semantic layer |
| "Analysts want to pull 2 million rows into Sheets" | Increase the Connected Sheets extract limit | Extracts above 500,000 rows are not supported; aggregate first or use a pivot within limits |
| "Viewers must only see their own region's rows" | Build one report per region | Row access policies plus viewer's credentials, or Looker user attributes |
| "BI Engine reservation is full" | Queries will start failing | Unsupported stages fall back to standard slots; the query succeeds but runs slower |
| "Report must refresh every minute for 200 viewers" | Set freshness to 1 minute and move on | BigQuery costs apply to every refresh; use a materialized view or aggregate table first |
| "Nightly Sheets refresh fails under VPC Service Controls" | Grant the user a broader IAM role | Scheduled refreshes carry no end-user context; adjust the perimeter configuration |
A company runs 40 Looker Studio dashboards over a 12 TB BigQuery events table. Queries are slow and monthly analysis costs have tripled. The dashboards were built by business users across several departments and the data team has been told it cannot rewrite them. Which change delivers the largest improvement with the least disruption?
A Looker Studio report is shared with 60 regional managers. Each manager should see only their own region's rows, which are protected in BigQuery by a row access policy keyed to the user's identity. Managers report that they can all see every region. What is the most likely cause?
An analytics team schedules a nightly Connected Sheets refresh that pulls a summary from BigQuery into a shared spreadsheet. The refresh succeeds whenever an analyst triggers it manually but fails every night on the schedule. The project sits inside a VPC Service Controls perimeter. What is the most likely explanation?