GCP-PDE — Making the Numbers Tell the Truth — Preparing Data for Visualisation (Section 4.1)

A dashboard that loads in 45 seconds isn’t a dashboard. It’s a loading screen with ambitions.

I once built what I thought was a beautiful dashboard. Twelve charts, real-time data, colour-coded by region. The executives loved the design in the demo. Then they tried to use it in a live board meeting and it took 47 seconds to load. The CFO refreshed it twice, then opened a spreadsheet instead.

That dashboard failed not because the data was wrong or the charts were ugly. It failed because nobody prepared the data for visualisation. We were asking a BI tool to run complex multi-table joins against terabytes of raw data, on demand, while twelve executives stared at a spinner.

Section 4.1 is the exam’s way of asking: do you know how to make BigQuery data not just correct, but fast, secure, and accessible enough to actually be used by the people who need it?

We’ll follow Cymbal’s Executive Intelligence Dashboard — the initiative to give 200 business users, 12 executives, and 5 external franchise partners access to clean, fast, secure data visualisations powered by BigQuery.

01 — Plugging In: Connecting BI Tools to BigQuery

Before any chart renders, the BI tool needs to talk to BigQuery. Different tools connect differently, and the exam tests whether you know which connection method is appropriate for each scenario.

Think of BI tool connections like different types of electrical plugs. Some devices need a direct connection to the mains — high power, always on. Some work on batteries — portable, disconnected. Some use an adapter — compatible with multiple socket types. The plug you choose determines what your device can do.

Looker Studio (formerly Data Studio)

• Looker Studio is Google’s free, browser-based BI and dashboarding tool. It connects natively to BigQuery — no JDBC driver, no intermediary, just a BigQuery data source configuration with project, dataset, and table.
• Direct query mode — every chart refresh triggers a live BigQuery query. Data is always current. Cost is incurred per query. This is the default mode.
• Extract mode — Looker Studio extracts a snapshot of data into its own cache (up to 100 MB). Charts query the cache, not BigQuery — no BigQuery costs during dashboard viewing, but data is only as fresh as the last extract. Schedule extracts to control refresh frequency.
• When to use extract mode: Cymbal’s franchise partners view dashboards that show last month’s sales — data doesn’t change during viewing. Extract mode serves them from cache, saving BigQuery query costs.
• Calculated fields — Looker Studio supports creating calculated fields directly in the report (e.g., CONCAT(first_name, " ", last_name) or order_total * 1.1 for tax). Calculated in the BI layer — no BigQuery changes needed. Limited to what Looker Studio's formula language supports.

Looker (the enterprise platform)

• Looker is Google’s enterprise BI platform. It uses LookML — a modelling language that defines dimensions, measures, and relationships as code. The LookML model sits between the BI layer and BigQuery — every Looker explore and dashboard query is generated SQL executed against BigQuery.
• Persistent Derived Tables (PDTs) — Looker can materialise the results of expensive LookML queries into BigQuery tables on a schedule. Similar to BigQuery materialised views but managed from Looker. Cymbal’s weekly cohort analysis is a PDT — Looker rebuilds it nightly, dashboard queries run against the precomputed result.
• BigQuery connection in Looker — configured with a service account that has roles/bigquery.dataViewer and roles/bigquery.jobUser. The service account is what Looker uses to run all generated SQL — individual user access is controlled through Looker's own user management, not BigQuery IAM.

Connecting third-party tools (Tableau, Power BI)

• Third-party BI tools connect to BigQuery via the BigQuery JDBC/ODBC driver or the BigQuery connector specific to each tool.
• Tableau uses the BigQuery connector — it generates SQL that BigQuery executes. Each Tableau user’s queries run under a service account or the user’s own Google identity (via OAuth).
• Power BI uses the BigQuery connector with OAuth — users authenticate with their Google accounts, and Power BI generates M queries that translate to BigQuery SQL.
• The exam pattern: “Cymbal’s Power BI users are connecting to BigQuery — which authentication method gives each user their own audit trail?” → OAuth with user credentials, not a shared service account.

Connected Sheets

• Google Sheets can connect directly to BigQuery via Connected Sheets — analysts write SQL queries or use a table explorer inside Google Sheets, and results are refreshed on demand.
• Useful for analysts who are comfortable with Sheets but not with SQL — they can explore BigQuery data in a familiar interface.
• Connected Sheets queries appear in BigQuery’s job history and cost money per query — the exam tests this: “An analyst uses Connected Sheets to refresh a 5 TB table every hour — who gets billed and how to reduce cost?” → The project containing the BigQuery dataset is billed. Reduce cost by using a materialised view or scheduled extract instead of querying the full table hourly.

💡 Exam Tip:

“Free, browser-based dashboards connected to BigQuery” → Looker Studio

“Looker Studio without BigQuery costs during viewing” → Extract mode

“Enterprise BI with LookML semantic modelling” → Looker

“Precompute expensive Looker queries into BigQuery” → Persistent Derived Tables (PDTs)

“Third-party tool connection to BigQuery” → BigQuery JDBC/ODBC driver or native connector

“Each user has their own BigQuery audit trail” → OAuth with user credentials, not shared service account

02 — Doing the Maths Once: Precalculating Fields

The most expensive query is the one that runs 500 times today, doing the same calculation every single time. Precalculating fields is the practice of computing expensive or frequently needed values once — and storing the result where BI tools can read it instantly.

Think of precalculation like a prep cook in a restaurant. The chef doesn’t chop onions during service — they’re prepped before service starts. When an order comes in, the onions are ready. Precalculated fields are the mise en place of data visualisation.

Why precalculate?

• BigQuery charges for bytes scanned per query. A field that requires a JOIN to a 500 GB reference table costs the same whether it’s calculated in a $10 dashboard query or a $0.01 precalculated batch job. Do it once.
• BI tools have limited transformation capabilities. A complex window function that calculates each store’s rolling 30-day revenue rank cannot be expressed in Looker Studio’s formula language — but it can be precalculated in a BigQuery scheduled query and stored as a simple integer column.
• Dashboard latency is dominated by query time. Precalculated fields return in milliseconds because they’re already computed — no JOIN, no aggregation, no window function at query time.

Where to precalculate in BigQuery

• Scheduled queries — a BigQuery SQL query that runs on a cron schedule (e.g., nightly at 1 AM) and writes results to a summary table. Cymbal’s nightly revenue summary runs at 1 AM, calculates region_revenue, yoy_growth_percent, and category_rank for all regions, and writes to analytics.revenue_summary. Morning dashboard queries scan kilobytes.
• Materialised views — BigQuery stores the precomputed result of a query physically and auto-refreshes when the underlying data changes. For fields that must be current within minutes (not hours), materialised views are better than scheduled queries. Cymbal’s live order count materialised view updates every 5 minutes — the operations dashboard always shows near-current data.
• Dataform transformations — for complex multi-step precalculations (calculate intermediate table A, then use A to calculate B, then join B with C to produce the final field), Dataform manages the dependency chain with version control and automated testing. The final output table is what the BI tool connects to.
• Looker PDTs — precalculated inside Looker’s semantic layer. Useful when the precalculation logic is defined in LookML and managed by the Looker team rather than the data engineering team.

Common precalculated field patterns

• Denormalised labels — store product_category_name directly in the fact table rather than requiring a JOIN to dim_product on every query. Pre-join it during the Dataform transformation.
• Bucketed fields — pre-bucket customer_age into age_group (18–25, 26–35, etc.) during transformation. Dashboard filters on age_group scan the bucket without computing age arithmetic at query time.
• Rank and percentile fields — window functions are expensive at query time. Pre-compute store_revenue_rank, customer_percentile, and product_sales_rank in a nightly batch and store as integer columns.
• Time-based derived fields — days_since_last_purchase, is_within_return_window, quarter_to_date_revenue. Date arithmetic that runs millions of times a day should be precomputed once.

💡 Exam Tip:

“Complex calculation runs 500 times/day — same result each time” → precalculate in scheduled query or materialised view

“Field requires a multi-table JOIN — slow in BI tool” → denormalise into the fact table via Dataform

“Near-real-time precalculation that auto-refreshes” → materialised view

“Complex dependency chain of calculations” → Dataform models

“Expensive window function in every dashboard query” → precompute in scheduled query, store as column

03 — The Performance Engine: BigQuery Features for BI

A BigQuery table without performance optimisation is a sports car with the handbrake on. The data is all there — but it’s not going anywhere fast. BigQuery provides specific features designed to make BI workloads performant at scale.

BI Engine — the in-memory acceleration layer

BI Engine is an in-memory analysis service built into BigQuery. It caches frequently accessed data in memory — serving dashboard queries in milliseconds without running full BigQuery slot-based queries.

• How it works — BI Engine reserves a dedicated amount of memory (in GB) for a BigQuery project. When a query’s data fits in the BI Engine cache, it’s served from memory — sub-second response. When it doesn’t fit, the query falls back to standard BigQuery execution.
• Configuration — reserve BI Engine capacity in the BigQuery console or via API. Specify the reservation size (1 GB to 250 GB) and the region. BI Engine is priced per GB-hour of reservation.
• What BI Engine accelerates — queries on BigQuery native tables with filter, aggregation, and join patterns typical of BI workloads. It’s optimised for the SQL patterns that Looker Studio, Looker, and Tableau generate. It does NOT accelerate every query type — complex analytical queries with many window functions or recursive CTEs may not benefit.
• BI Engine + materialised views — the most powerful combination. Materialised views reduce the data size that queries need to scan; BI Engine caches the small result in memory. Together they achieve sub-100ms dashboard response on multi-TB datasets.
• Preferred tables — you can configure BI Engine to prefer specific tables for caching, ensuring the most important dashboard data is always in memory.

Materialised views — the precomputed query layer

• A materialised view stores the result of a SELECT query physically in BigQuery storage. When the base table changes, BigQuery automatically refreshes the materialised view (either incrementally or fully, depending on the query).
• Smart query rewrite — when a user’s query matches a materialised view’s definition, BigQuery automatically rewrites the query to read from the materialised view — even if the user referenced the base table. The analyst doesn’t need to know the materialised view exists.
• Incremental refresh — for supported query patterns (aggregations over append-only tables), BigQuery refreshes only the changed partitions — not the full table. Cymbal’s hourly order count materialised view updates in seconds when new orders arrive because only today’s partition needs recomputing.
• Partitioned materialised views — materialised views on partitioned base tables can be partitioned themselves. This enables partition pruning on both the base table and the materialised view — doubly efficient.
• Limitations the exam tests: materialised views must query from a single dataset (in the same project), cannot reference other materialised views, and cannot use non-deterministic functions (like CURRENT_TIMESTAMP() or RAND()).

Partitioning and clustering for BI

• From Section 3.2 — partitioning by date reduces bytes scanned for time-filtered queries; clustering reduces bytes scanned within a partition by filter columns.
• For BI specifically: ensure the partition column matches the most common dashboard filter. Cymbal’s executives always filter by date range — order_date partitioning means every dashboard query only scans the selected date range's partitions.
• Partition pruning requires explicit filter — a query that filters WHERE order_date > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) prunes partitions. A query that joins to a date table without a direct filter on the partition column may NOT prune — the exam tests this.

Slot reservations for BI workloads

• BigQuery’s on-demand pricing charges per TB scanned. For large BI deployments with predictable, high-volume query traffic, slot reservations (capacity pricing) provide guaranteed compute at a flat monthly rate.
• Reservation assignments — assign slot reservations to specific projects, folders, or organisations. Cymbal creates a BI reservation of 500 slots and assigns it to the analytics project — all dashboard queries from that project use the reserved slots, never competing with other workloads.
• Autoscaling with reservations — BigQuery reservations now support autoscaling — a minimum slot count is always available, and the reservation scales up temporarily for query spikes.

💡 Exam Tip:

“Sub-second dashboard response on BigQuery” → BI Engine + materialised views

“BI Engine + materialised view — the combination” → reduces data size (mat. view) + caches in memory (BI Engine)

“Dashboard query automatically reads from materialised view without analyst knowing” → smart query rewrite

“Materialised view cannot reference” → another materialised view, non-deterministic functions, cross-dataset joins

“Guarantee compute for dashboard queries without on-demand competition” → slot reservation with assignment

“Partition pruning fails on dashboard” → check whether the query has an explicit filter on the partition column

04 — When the Dashboard Spins Forever: Troubleshooting Poor Performing Queries

Every data engineer eventually faces the angry message: “The dashboard has been loading for 10 minutes, what’s wrong?” Knowing how to diagnose and fix poor-performing BigQuery queries is a practical skill the exam tests directly.

Think of query troubleshooting like diagnosing a sick engine. You don’t randomly replace parts hoping one fixes it. You read the instruments — oil pressure, temperature, RPM — and identify which system is failing before you act. BigQuery gives you instruments. Use them.

The BigQuery Query Execution Plan

• Every BigQuery query has an execution plan — a breakdown of the stages the query went through, how many bytes were processed at each stage, and where time was spent.
• Access it in the BigQuery console under Query Details → Execution Details. It shows input/output for each stage, shuffle data volume, and worker utilisation.
• What to look for:
• Large shuffle bytes — data being moved between workers. Usually caused by large JOINs or GROUP BY operations on high-cardinality keys. Fix: partition or cluster the table, or filter before joining.
• Skewed stages — one worker processing much more data than others. Usually caused by skewed data distribution (one JOIN key value has millions of rows while others have tens). Fix: pre-aggregate before joining, or use approximate functions.
• Long slot time — workers spending a long time in computation. Usually caused by complex functions (regex, JSON parsing, nested queries). Fix: precompute or simplify.

Common performance problems and fixes

• **SELECT * on large tables** — scanning all columns in a wide table wastes bytes and money. Fix: select only needed columns. BigQuery is columnar — unused columns cost nothing if not selected.
• No partition filter — a query against a 5 TB date-partitioned table without a WHERE order_date clause scans all 5 TB. Fix: always include the partition column in the WHERE clause. Use require_partition_filter table option to enforce this.
• Cross joins and cartesian products — a CROSS JOIN between two tables produces every combination of rows. Often introduced accidentally (a JOIN without a JOIN condition). Fix: always specify JOIN conditions explicitly.
• Repeated subquery evaluation — a subquery in a WHERE clause that references a large table runs once per row. Fix: use CTEs or temp tables to compute the subquery result once.
• Functions on partition columns — WHERE YEAR(order_date) = 2025 prevents partition pruning because BigQuery evaluates the function on every row before filtering. Fix: WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'.
• Large JOIN broadcast threshold exceeded — when BigQuery joins a large table to another large table, it must shuffle rows across workers (expensive). When one side is small (<1 GB), BigQuery broadcasts the small table to all workers (cheap). Fix: filter or aggregate the large JOIN table before joining.

The Information Schema for query analysis

• INFORMATION_SCHEMA.JOBS_BY_PROJECT — query metadata for all jobs in a project: bytes billed, slot milliseconds consumed, query text, user, start time. Used to identify the most expensive queries.
• Cymbal’s BI operations team runs a weekly query against INFORMATION_SCHEMA.JOBS_BY_PROJECT to identify the top 10 most expensive dashboard queries — and prioritises optimisation work based on actual cost data.

EXPLAIN and dry run

• Dry run — submit a query with the --dry_run flag to see how many bytes it would process without actually running it. Used to validate that partitioning and clustering are working before committing to production.
• No formal EXPLAIN command in BigQuery — the execution plan is available post-run in the Query Details tab.

💡 Exam Tip:

“Query scans entire partitioned table — no partition pruning” → function applied to partition column (e.g., YEAR(date)) or missing WHERE clause on partition column

“Dashboard slow — find the most expensive queries” → INFORMATION_SCHEMA.JOBS_BY_PROJECT

“Large shuffle bytes in execution plan” → JOIN or GROUP BY on high-cardinality column without filtering

“Prevent queries without partition filter” → **require_partition_filter table option**

“Validate bytes scanned before running” → dry run

“One worker processing much more than others” → data skew — pre-aggregate the skewed key before joining

05 — Safe Data, Right People: Security, Masking, IAM, and Cloud DLP

A dashboard that shows a customer’s national ID to every analyst isn’t a dashboard. It’s a compliance incident waiting to happen. Preparing data for visualisation means preparing it safely — ensuring the right data reaches the right people in the right form.

Think of data security for dashboards like a hotel key card system. The same hotel has rooms, restaurants, the gym, the pool, and the executive lounge. Your key card opens exactly the doors you’re authorised for — not one more. The hotel didn’t remove the executive lounge from the building just because you can’t access it. It’s there, protected.

BigQuery IAM for BI access

• Dataset-level IAM — the most common BI access pattern. Grant roles/bigquery.dataViewer on a dataset to the analytics team. They can query all tables in the dataset but cannot modify schema or delete tables.
• Table-level IAM — for finer granularity. Grant access to specific tables within a dataset — useful when some tables in the same dataset contain sensitive data that only specific teams should see.
• Row-level security — a row access policy filters which rows a user sees in a query. Cymbal’s regional managers each see only their region’s rows in the sales_facts table — same table, different results per user, no separate tables needed. Row access policies use IAM group membership or individual email addresses to define the filter.
• Column-level security (Policy Tags) — columns tagged with a Policy Tag are only visible to users with the roles/datacatalog.categoryFineGrainedReader role on that tag. Analysts without the role see NULL for those columns. Covered in Section 1.1 — critical for PII in BI contexts.
• Authorised views — a view that has been granted access to query restricted base tables. The view presents a sanitised version of the data — aggregated, filtered, or with sensitive columns removed. Cymbal grants franchise partners access to an authorised view that shows regional totals — they cannot access the underlying order-level data.

Data masking for BI

• Data masking replaces sensitive values with non-sensitive substitutes. Different from column security (which shows NULL) — masking shows a transformed value that looks realistic but isn’t the original.
• BigQuery column data masking — applied through Data Catalog Policy Tags with masking rules. When a column has a masking rule, users without the unmasked data role see the masked value instead of the original.
• Masking types the exam tests:
• Nullify — replace with NULL. Same as column security.
• Default value — replace with the column’s type default (0 for integers, "" for strings).
• SHA-256 hash — replace with a deterministic hash of the original value. The hash is consistent — the same input always produces the same hash. Useful for joining masked data across tables (the hashed customer_id in one table matches the hashed customer_id in another).
• Date year only — for DATE columns, replace the full date with just the year (e.g., 1985-07-22 becomes 1985-01-01). Reduces precision without nullifying.
• Email masking — replace the local part of an email with xxxxx while preserving the domain (john.doe@cymbal.com becomes xxxxx@cymbal.com).
• The masking rule is assigned at the Policy Tag level — all columns with the same tag use the same masking rule consistently across the entire dataset.

Cloud DLP for BI data preparation

• Cloud DLP (Data Loss Prevention) discovers, classifies, and de-identifies sensitive data. For BI data preparation, Cloud DLP serves two roles:
• Discovery — scan BigQuery tables and Cloud Storage files to find columns that contain PII (credit card numbers, national IDs, email addresses, phone numbers) that haven’t been tagged yet. Cymbal runs DLP discovery scans on newly ingested datasets — any table with detected PII gets automatically tagged with the appropriate Policy Tag before it’s exposed to the BI layer.
• De-identification — transform sensitive data into a non-sensitive form before it enters the BI layer. DLP supports:
• Redaction — replace sensitive values with a placeholder ([REDACTED]).
• Tokenisation (format-preserving encryption) — replace a value with a token that has the same format (a credit card number is replaced by a different number that still looks like a credit card number). The token can be reversed by an authorised system — not by BI users.
• Generalisation — reduce precision of sensitive fields (exact age → age range, exact postcode → first 3 digits only).
• Bucketing — group numeric values into ranges.
• DLP in the pipeline — for streaming BI pipelines, Cloud DLP is called inline in the Dataflow job before data lands in the BigQuery BI layer. Cymbal’s order enrichment Dataflow job calls DLP to de-identify buyer_tax_id before writing to the orders_curated BigQuery table that Looker Studio connects to.
• The exam distinction: Cloud DLP discovers PII and de-identifies it. Policy Tags restrict access to it. They work at different layers — DLP acts on the data content; Policy Tags act on query permissions. Both may be needed: DLP to find and mask the data; Policy Tags to control who can see even the masked version.

VPC Service Controls for BI

• VPC Service Controls creates a security perimeter around GCP services. BigQuery inside a VPC-SC perimeter can only be accessed from within the approved network — users outside the perimeter (including BI tools running on external networks) are blocked.
• For Cymbal’s executive BI environment: VPC-SC ensures that dashboard queries can only originate from Cymbal’s corporate network or approved Cloud Run services — a freelance consultant with a valid Google account but an external IP address cannot query the protected BigQuery datasets, even with IAM permissions.
• Access levels — VPC-SC supports conditional access levels that allow specific external users (e.g., a specific IP range, a specific device state) to access protected resources. Used to allow franchise partners to query Analytics Hub linked datasets from their external networks while blocking general internet access.

💡 Exam Tip:

“Different users see different rows in the same table” → Row-level security (row access policy)

“Column returns NULL for unauthorised users” → Policy Tags + Fine-Grained Reader role

“Column returns a hash instead of the original value” → Data masking — SHA-256 masking rule

“Discover PII columns in newly ingested BigQuery tables automatically” → Cloud DLP discovery scan

“De-identify credit card numbers in a streaming Dataflow pipeline” → inline Cloud DLP de-identification

“Block BI tool access from external networks even with valid IAM” → VPC Service Controls

“Franchise partner sees regional totals but not raw transactions” → authorised view

DLP ≠ Policy Tags: DLP acts on data content (finds and transforms PII). Policy Tags act on query permissions (controls who sees which columns). The exam tests this distinction.

Practice Questions

Q1 — BI Tool Connection

Cymbal’s analytics team uses Looker Studio to build dashboards connected to BigQuery. 150 franchise partners view these dashboards but never interact with the underlying data. The franchise dashboards refresh daily at 6 AM. The data team wants to minimise BigQuery query costs during the 12-hour peak viewing window (8 AM–8 PM) when all 150 partners are active. Which Looker Studio configuration achieves this?

• A. Direct query mode — each partner refresh triggers a BigQuery query, sharing the compute cost
• B. Extract mode with a daily refresh at 6 AM — partners view from cached extract during the viewing window, no BigQuery queries during 8 AM–8 PM
• C. Connect Looker Studio to a Cloud Storage export instead of BigQuery
• D. Create 150 separate Looker Studio reports, one per partner, to distribute the query load

Answer: B

• Extract mode caches the dashboard data in Looker Studio’s own storage at 6 AM. During the 8 AM–8 PM viewing window, all 150 partners read from the cache — zero BigQuery queries, zero BigQuery costs during peak viewing. Direct query mode (A) triggers a BigQuery query on every refresh by every partner — 150 partners refreshing during a 12-hour window generates significant query volume and cost. Cloud Storage export © adds operational complexity and doesn’t integrate with Looker Studio’s native features. Separate reports per partner (D) doesn’t reduce query volume — it just adds report management overhead.

Q2 — Precalculation Strategy

Cymbal’s Looker Studio dashboard shows each store’s revenue rank nationally (1 = highest revenue store) updated daily. The ranking requires a RANK() window function across all 500 stores. Currently this window function runs on every dashboard query — each of the 80 store managers refreshing their dashboard executes the full window function against 2 TB of sales data. What is the correct optimisation?

• A. Enable BI Engine — it accelerates window functions in memory
• B. Create a scheduled query that runs nightly, computes store_revenue_rank using RANK() and stores it as an integer column in a summary table — Looker Studio reads the rank directly
• C. Create a materialised view with the RANK() window function
• D. Ask store managers to refresh less frequently

Answer: B

• A scheduled nightly query computes RANK() once across all 500 stores and writes the result as a simple integer column. All 80 store managers' dashboard queries read store_revenue_rank directly — no window function, no 2 TB scan, no repeated computation. BI Engine (A) accelerates filter/aggregation BI patterns but doesn't eliminate the window function computation — it still runs, just in memory. Materialised views (C) cannot include RANK() as a window function in supported incremental refresh patterns — materialised views have significant SQL restrictions. Asking managers to refresh less (D) is not a technical solution.

Q3 — Partition Pruning Failure

Cymbal’s sales dashboard runs this query against a date-partitioned BigQuery table: SELECT store_id, SUM(revenue) FROM sales_facts WHERE EXTRACT(YEAR FROM sale_date) = 2025 GROUP BY store_id. The query scans the entire table — 8 TB — instead of just 2025's partitions. What is the cause and fix?

• A. The table is not partitioned — add partitioning by sale_date
• B. EXTRACT(YEAR FROM sale_date) applies a function to the partition column, preventing partition pruning. Fix: replace with WHERE sale_date BETWEEN '2025-01-01' AND '2025-12-31'
• C. SUM(revenue) requires a full table scan regardless of the partition filter
• D. The GROUP BY store_id clause forces BigQuery to scan all partitions

Answer: B

• When a function is applied to a partition column in a WHERE clause (EXTRACT(YEAR FROM sale_date)), BigQuery cannot use the partition metadata to prune — it must evaluate the function on every row in every partition. The fix is to use a range filter directly on the partition column value: sale_date BETWEEN '2025-01-01' AND '2025-12-31'. This allows BigQuery to skip all non-2025 partitions entirely. The table is already partitioned (A is wrong). Aggregation (C) and GROUP BY (D) don't affect partition pruning.

Q4 — Row-Level Security

Cymbal has a store_performance BigQuery table containing performance data for all 500 stores. Each of the 50 regional managers should only see data for stores in their region. Creating 50 separate tables or views is operationally unacceptable. Which BigQuery feature provides the correct solution?

• A. Create 50 authorised views — one per region — and grant each manager access to their region’s view
• B. Apply column-level Policy Tags to the store_region column to restrict access
• C. Create a row access policy on store_performance that filters rows based on the querying user's region group membership — each manager sees only their region's rows automatically
• D. Partition the table by store_region and grant partition-level IAM to each manager

Answer: C

• Row access policies define a filter expression that is automatically applied when a user queries the table. Cymbal maps each regional manager to their region via IAM group membership — the row access policy evaluates the user’s group and returns only matching rows. One table, one policy, 50 managers automatically served their own data. 50 authorised views (A) create significant maintenance overhead — every schema change requires updating 50 views. Policy Tags (B) restrict column access, not row access. Partition-level IAM (D) is not a supported BigQuery access control primitive — partitions cannot be individually access-controlled by user.

Q5 — Cloud DLP in Pipeline

Cymbal’s Dataflow pipeline processes customer order events containing buyer_national_id and buyer_credit_card fields. These fields must be de-identified before the data lands in the BigQuery table that Looker Studio connects to. The de-identified values must be consistent — the same buyer_national_id must always produce the same de-identified token so records can be joined across tables. Which Cloud DLP technique is correct?

• A. Redaction — replace sensitive values with [REDACTED]
• B. Format-preserving encryption (tokenisation) — replace values with a deterministic token of the same format; the same input always produces the same token
• C. Nullification — replace values with NULL
• D. Generalisation — reduce buyer_national_id to its first 4 digits

Answer: B

• Format-preserving encryption (tokenisation) produces a deterministic token — the same buyer_national_id always maps to the same token. This preserves the ability to join records across tables using the token (the tokenised ID in the orders table matches the tokenised ID in the returns table). Redaction (A) replaces with a literal string [REDACTED] — not joinable across tables. Nullification (C) makes all records look identical (NULL = NULL) — unusable for joining. Generalisation (D) reduces precision — the first 4 digits of a national ID may not be unique enough to serve as a join key.

Q6 — BI Engine Configuration

Cymbal’s Looker Studio executive dashboard runs 5 key queries against a 500 GB BigQuery dataset. The queries are simple filters and aggregations — no complex window functions. Currently each query takes 4–6 seconds. The team wants sub-second response. After enabling BI Engine with a 10 GB reservation, queries still take 4–6 seconds. What is the most likely cause?

• A. BI Engine only works with Looker, not Looker Studio
• B. 10 GB is insufficient to cache the 500 GB dataset — the queries fall back to standard BigQuery execution; increase the BI Engine reservation or use materialised views to reduce the data size that needs caching
• C. BI Engine requires slot reservations to be configured alongside it
• D. The queries use GROUP BY which BI Engine does not support

Answer: B

• BI Engine caches data in memory — if the data accessed by the queries is larger than the reservation, it falls back to standard BigQuery. A 10 GB BI Engine reservation cannot cache a 500 GB dataset. The fix: either increase the reservation significantly, or (more cost-effectively) create materialised views that precompute the aggregations, reducing the cached data from 500 GB to a few MB of aggregated results. BI Engine works with both Looker and Looker Studio (A is wrong). Slot reservations are not required for BI Engine ©. GROUP BY is one of the aggregation patterns BI Engine is specifically optimised for (D is wrong).

Q7 — Data Masking vs Column Security

Cymbal’s analysts need to work with customer data for cohort analysis. The customer_email column is tagged with a PII Policy Tag — analysts without Fine-Grained Reader see NULL. The analytics lead requests that analysts should see a masked email (e.g., xxxxx@cymbal.com) rather than NULL — so they can at least verify the email domain without exposing personal information. Which configuration change achieves this?

• A. Grant all analysts roles/datacatalog.categoryFineGrainedReader on the PII tag
• B. Apply an email masking rule to the customer_email Policy Tag — analysts without Fine-Grained Reader see xxxxx@[domain] instead of NULL
• C. Create a new column customer_email_domain that extracts just the domain — Policy Tag the original column
• D. Remove the Policy Tag from customer_email and use a separate restricted view for compliance purposes

Answer: B

• Data masking rules applied at the Policy Tag level replace the sensitive value with a masked form rather than NULL. The email masking type preserves the domain while replacing the local part — john.doe@cymbal.com becomes xxxxx@cymbal.com. Analysts can verify domain patterns without seeing personal information. Granting Fine-Grained Reader (A) gives full unmasked access — the opposite of what's requested. A domain column (C) is a workaround that adds schema complexity and still doesn't address the original column. Removing the Policy Tag (D) exposes the raw PII to all analysts — a compliance violation.

Q8 — Troubleshooting: Query Skew

Cymbal’s BigQuery query joins a 5 TB order_facts table with a dim_customer table on customer_id. The execution plan shows that one shuffle stage is taking 10x longer than expected — one worker is processing 80% of the data while others sit idle. Investigation reveals that 60% of all orders belong to a single corporate customer (customer_id = 'CORP-001'). What is the correct fix?

• A. Increase the number of Dataflow workers to process the skewed key faster
• B. Add a secondary clustering column to order_facts to distribute the CORP-001 rows
• C. Pre-aggregate order_facts by customer_id before the JOIN — reducing the rows for CORP-001 from millions to one aggregated row
• D. Partition order_facts by customer_id to isolate the CORP-001 partition

Answer: C

• Data skew occurs when one key value has disproportionately more rows than others. Pre-aggregating before the JOIN reduces CORP-001's millions of rows to a single aggregated row — the JOIN then operates on a balanced dataset with no skew. Increasing Dataflow workers (A) is wrong context — this is a BigQuery SQL query, not a Dataflow job; also, more workers don't help if all rows for one key must go to the same worker. Clustering (B) improves scan efficiency but doesn't resolve shuffle skew — the JOIN still produces skewed distribution. Partitioning by customer_id (D) has 10 million+ distinct values — hitting the 10,000 partition limit immediately.

A Final Reflection

The gap between data that is technically correct and data that is actually used by the business is often a dashboard performance problem, a security concern, or a connection configuration issue. None of these are data quality issues — the data is fine. They’re data preparation issues.

What I’ve come to appreciate is that a data engineer’s job doesn’t end when the pipeline writes clean records to BigQuery. It ends when the executive can click refresh and see an accurate number in under a second, confident that what they’re seeing is exactly what they’re supposed to see — and nothing they’re not.

Cymbal’s Executive Intelligence Dashboard isn’t just a collection of charts. It’s a system of precalculations, security policies, BI Engine reservations, masking rules, and materialised views — all working together to turn terabytes of raw data into a number the CFO trusts enough to make a decision with.

That’s what preparing data for visualisation actually means.

Section 4.2 covers Preparing Data for Machine Learning — feature engineering, BigQuery ML, embeddings, and RAG for Cymbal’s AI-powered retail intelligence.