Preparing and using data for analysis

Preparing Data for Visualization

Connecting External Business Intelligence Tools

Hybrid and multi-cloud integration connects BigQuery to external business intelligence and visualization tools located on-premises, in other clouds, or with partner environments. The goal is enabling secure, performant connections without moving data from its source.

For tools with native integration like Tableau, a direct connection uses the BigQuery API with OAuth 2.0 authentication. Google provides ODBC and JDBC drivers for tools and custom applications that do not have native BigQuery support, allowing them to connect as if BigQuery were a traditional database.

Securing cross-network connections relies on two Google Cloud frameworks. VPC Service Controls creates a security perimeter that restricts resource access to specific networks and helps prevent data exfiltration. Private Service Connect provides private access to BigQuery from on-premises networks or other VPCs without exposing traffic to the public internet.

BigQuery BI Engine accelerates SQL queries by caching frequently accessed data in memory. It integrates directly with the BigQuery API, so any BI tool like Looker Studio, Tableau, or Power BI can use it without modification. To use BI Engine, you create a reservation that defines dedicated storage capacity at the project and region level.

Dashboard performance improves further when BI Engine combines with table partitioning, clustering, and materialized views. Partitioning by time allows dashboards showing only recent data to load only the latest partitions. Clustering organizes data in columns accessed together, improving retrieval efficiency. Materialized views pre-join and flatten data into optimized structures, eliminating repetitive joins.

Authentication for visualization tools connects through OAuth 2.0 access tokens or service account private keys. Authorization uses Identity and Access Management (IAM) roles applied at the dataset, table, or view level. BigQuery supports predefined roles, custom roles with granular permissions, and basic roles such as Owner, Editor, and Viewer. For sensitive data, row-level security and column-level security restrict access, while authorized views let users query data without direct access to underlying tables.

Precalculating Fields for Better Performance

Materialized views are precomputed views that store SQL query results in BigQuery, drastically reducing processing time and data scanning costs. BigQuery maintains these views through automatic background processing called incremental updates, which only scan changed data rather than rebuilding the entire view. BigQuery also includes smart tuning, which automatically reroutes queries to the materialized view when it can serve the request more efficiently.

Three common use cases drive precalculation optimization:

• Data pre-aggregation combines streaming or transactional data to compute metrics like sums, averages, or counts in advance.
• Pre-filtering reads only a specific subset of a table to reduce scanned data volume during query execution.
• Pre-joining combines large and small tables beforehand to avoid expensive runtime joins.

Beyond materialized views, scheduled queries write precalculated results directly into standard tables through stored procedures that execute on a set frequency. The key consideration is balancing data freshness, storage cost, and query performance.

Maintaining data integrity requires robust pipelines built on Cloud Composer or Dataflow that automate the refresh process while ensuring synchronization with source systems. Idempotent transformations produce the same result regardless of how many times they run, preventing duplicate or inconsistent data when pipelines re-run due to failures or scheduled refreshes.

BigQuery Features for Business Intelligence

Materialized views support partitioning and clustering, incremental refresh, and additional storage. Unlike logical views, they provide zero maintenance because precomputation happens in the background when base tables change. If changes might invalidate the view, BigQuery reads from the base table instead to ensure fresh data.

Incremental updates allow BigQuery to combine cached view data with new data. For single-table materialized views, incremental updates work when the base table is unchanged since the last refresh or when only new data was added. However, if the base table had updates or deletions, or if JOIN right-side tables changed, BigQuery cannot use incremental updates and reverts to the original query.

BI Engine does not accelerate queries using wildcard tables, external tables, JavaScript UDFs, or returning more than 1 GiB of data. For these patterns, saving query results to a permanent table and refreshing via scheduled queries is a common workaround.

Troubleshooting Poor Performing Queries

BigQuery generates a detailed query plan each time a query runs, including critical execution statistics such as bytes read and slot time consumed. The query plan breaks down computational capacity into slots, which are Google's proprietary unit of computational capacity.

Key metrics for identifying bottlenecks include:

• Bytes read (I/O): How much data your query reads from storage
• Bytes shuffled: How much data passes between stages
• Bytes written (materialization): How much data writes to intermediate or final storage
• CPU work (computation): The processing effort required

Performance bottlenecks often manifest through specific patterns. A JOIN stage generating far more output rows than input rows may indicate an opportunity to filter earlier. Data skew and excessive shuffling are common issues impacting performance.

The core optimization principle is that queries doing less work perform better. Avoid SELECT * to reduce processed data, filter early in the query logic, use appropriate join types with properly indexed or partitioned keys, and utilize efficient built-in functions.

Query performance also depends on resource availability. On-demand pricing uses a shared pool of slots with costs based on bytes processed. Capacity-based pricing involves purchasing dedicated slot reservations for predictable monthly expenditure. For consistent or highly concurrent workloads, capacity-based pricing with slot commitments or autoscaling is recommended.

Security for Visualization

Identity and Access Management (IAM) controls who can access what data in BigQuery, with roles applied at the project, dataset, table, or view level. The principle of least privilege should guide all access decisions.

Data masking protects sensitive information by obscuring or transforming it before it reaches visualization tools. BigQuery supports column-level security that masks sensitive columns while allowing authorized users to see full data.

Cloud Data Loss Prevention (Cloud DLP) provides automated discovery and classification of sensitive data such as credit card numbers, social security numbers, and personally identifiable information (PII). Once identified, Cloud DLP can automatically apply masking, tokenization, or transformations to protect this data throughout its lifecycle.

A strong security model separates responsibilities within the data pipeline. For example, a data analyst should have permissions to query tables but never direct read access to underlying storage buckets.

Preparing Data for AI and ML

The exam content does not provide specific study material for this section. Students should refer to the official exam study guide for coverage of data preparation topics related to artificial intelligence and machine learning workflows in Google Cloud.

Sharing Data

Defining Rules to Share Data

Defining rules to share data establishes the identity, perimeter, and access policies required to distribute Google Cloud datasets securely across organizational boundaries.

Policy inheritance across the Google Cloud resource hierarchy determines how data sharing controls apply across projects and folders:

• Organization level: Enforces global sharing and compliance constraints across all folders and projects.
• Folder level: Groups nested sub-folders and projects to apply department-specific data sharing rules.
• Project level: Inherits all parent constraints while managing local data resource bindings.

Granular access controls enforce least privilege by restricting visibility to specific rows and columns. Administrators implement column-level security using Data Catalog policy tags arranged in hierarchical taxonomies. When a policy tag attaches to a column, only users with the Fine-Grained Reader role can view unmasked data.

For row-level filtering, BigQuery row-level security (RLS) creates row access policies based on user identity or group membership. Dynamic data masking (DDM) automatically obscures outputs for users with the BigQuery Masked Reader role, such as showing only the last four digits of a number, without requiring duplicate tables.

VPC Service Controls establish security perimeters around BigQuery and Cloud Storage APIs to isolate sensitive workloads. Perimeter bridges enable secure data movement between isolated environments with custom ingress and egress rules. Access Context Manager evaluates contextual request attributes such as client IP subnetworks, user identity, and device state before granting access.

Organizations configure Workforce Identity Federation using open standard protocols like OIDC or SAML to authenticate external partners through identity providers. All data access requests generate immutable entries within Cloud Audit Logs.

Publishing Datasets

Publishing datasets involves packaging structured data assets so external or internal consumers can discover, query, and consume them without duplicating underlying storage.

A zero-copy architecture separates storage from compute, allowing publishers to maintain data ownership while subscribers run queries using their own compute resources. When modifying source schemas, publishers preserve query compatibility through versioning strategies:

• Dataset versioning: Updates the root dataset name while preserving consistent view and table names inside.
• View versioning: Maintains a stable dataset identifier while appending version tags to individual view names.

Dataplex Universal Catalog automatically harvests schemas, table names, and technical metadata from BigQuery tables and views. Publishers enrich assets with business tags, descriptions, and classification labels to make them searchable.

Cost-sharing models determine whether the publisher or subscriber pays for storage and computational query processing:

Publishers enable data egress restrictions on listings within BigQuery Analytics Hub to block subscribers from executing commands like CREATE TABLE AS SELECT that move data outside the governed environment.

Publishing Reports and Visualizations

Looker Studio creates visual reports and dashboards connected directly to BigQuery, Cloud SQL, and Cloud Storage. Organizations handling sensitive data must establish a Business Associate Agreement (BAA) and restrict domain sharing via the Google Admin console.

Two credential methods configure data access:

• Viewer's Credentials: Requires each dashboard viewer to have direct IAM read permissions on the underlying data source.
• Service Account Credentials: Uses a centralized service account to query source data on behalf of dashboard viewers.

Looker uses the LookML modeling language to define centralized business logic, metrics, and relationships. Database connections are secured through SSH tunnels, SSL encryption, and IP allowlists. User access uses SAML, LDAP, or Google OAuth for federated authentication.

Row-level security in Looker uses the access_filter parameter with user attributes to restrict record visibility by department or territory. The access_grants parameter restricts access to specific LookML Explores, views, joins, and fields based on assigned user attributes.

BigQuery Analytics Hub

BigQuery Analytics Hub is a fully managed data exchange platform enabling organizations to publish, discover, and query shared datasets securely across project and organizational boundaries.

Access management uses specialized IAM roles:

• Exchange Administrators: Manage lifecycle, configuration, and permissions of data exchanges.
• Publishers: Create, update, and manage data listings within an exchange.
• Subscribers: Discover listings, subscribe to shared assets, and access linked datasets.

When a subscriber links a listing, BigQuery creates a read-only linked dataset in the subscriber's project that dynamically queries the publisher's source data. Shared listings can expose three object types:

• Data Lake Objects (DLO): Raw, unprocessed data assets.
• Data Model Objects (DMO): Structured, normalized data models ready for reporting.
• Calculated Insights Objects (CIO): Pre-aggregated metrics and transformed dimensional calculations.

The lifecycle of a shared data listing follows four steps:

1. Exchange creation: An Exchange Administrator creates a private or public data exchange.
2. Listing publication: A Publisher packages tables, views, or materialized views into a listing with metadata.
3. Discovery and subscription: A Subscriber discovers and subscribes, creating a read-only linked dataset.
4. Continuous synchronization: The linked dataset references publisher source objects directly.

The zero-copy model divides costs cleanly: publishers pay for storage, while subscribers pay for computational slots or on-demand query bytes. Publishers enforce data egress restrictions to disable operations like CREATE TABLE AS SELECT that copy data outside BigQuery.

Key Facts

• BigQuery BI Engine accelerates dashboard queries by caching data in memory; performance combines with table partitioning, clustering, and materialized views.
• Materialized views precompute query results with smart tuning, where BigQuery automatically reroutes base-table queries to the view for better performance.
• VPC Service Controls create security perimeters that prevent data exfiltration, while perimeter bridges permit authorized cross-project data transfers.
• Authorized views allow consumers to query filtered subsets without direct access to underlying source tables.
• BigQuery Analytics Hub provides zero-copy data sharing where publishers pay for storage while subscribers pay for compute used to execute queries.
• Looker enforces row-level security using the access_filter parameter and controls access to Explores and fields using access_grants paired with user attributes.