AAtsushi's Blog
Data Engineering

Book Notes: Getting Started with Data Engineering on Google Cloud

→ 日本語版を読む

Overview

Notes from reading "Getting Started with Data Engineering on Google Cloud." Chapters 8–11 covering BigQuery data aggregation, BI, real-time analytics, and advanced analytics are skipped. Notes are not in chapter order — organized by service where possible.

BigQuery

BigQuery Internal Architecture

Compute, storage, and memory each scale independently.

Components:

  • Master/Scheduler
  • Workers: distributed compute environment running in containers; destroyed instantly after a query completes
  • Network: a proprietary in-datacenter network called Jupiter; 1.3 Gbps bandwidth
  • Distributed storage: spans multiple zones; data is compressed and stored in a column-oriented file format called Capacitor; multiple Capacitor files are bundled and presented as a table
  • Distributed in-memory shuffle: distributed processing involves shuffle — inter-worker data movement — which has significant overhead; performing this shuffle in distributed in-memory rather than on workers improves efficiency

BigQuery Sandbox

Separate from the Google Cloud free tier. No credit card required.

Advanced Query Features

The following advanced features are available:

  • Queries can be saved and shared.

  • Query scheduling is available.

  • User-defined functions (UDFs) can be used (defined in SQL or JavaScript and callable from SQL).

  • Stored procedures are available (unlike UDFs, they run as independent statements).

  • Query plan visualization enables performance tuning (see official documentation for details; tools like BQ Visualize are also available).

Query Optimization

Key points for query optimization:

  • Select only the necessary columns in SELECT statements.

  • Use partitioning/clustering (details below).

  • Query cache: cached for 24 hours; the same query returns cached results (not subject to scan charges). The cache itself can be accessed as a table. CREATE TEMP TABLE ... creates an explicit cache (temporary table).

Partitioning in BigQuery

Without partitioning, full column scans occur. Partitioning enables efficient scanning. Per-partition expiration can also be set; partitions older than a specified period are automatically deleted.

Partitioning methods:

  • Partition by column value. However, only time (year, month, day, hour) and integer values can be used for partitioning.
  • Partition by data ingestion time (less commonly used).

Clustering in BigQuery

Unlike partitioning, which supports only one column, clustering supports multiple columns. For example, both date and product ID can form a single group. This further reduces scan volume and returns results faster. However, the scan volume displayed in BigQuery looks the same as with partitioning (actual scan volume is smaller with clustering).

BigQuery Cost Control

The following methods can control costs:

BigQuery Reservations for Performance Guarantees

  • BigQuery Reservations enables billing based on dedicated compute resources rather than per-scan usage.

  • The compute unit on a worker is called a slot. In BigQuery Reservations, slots are purchased in units called commitments — available in seconds, months, or years. Longer contract periods offer higher discounts.

  • If a query plan requires 4,000 slots per second but only 2,000 slots/second are available, the job takes 2 seconds instead.

  • When multiple queries run in parallel, fair scheduling allocates slots evenly across queries to process them simultaneously, preventing long wait times.

  • A reservation is the concept of grouping and naming slots. Reservations ensure slots are not used by others.

  • An assignment project is a project linked to a reservation. Fair scheduling operates within assignment projects.

  • If a reservation has unused slots (idle slots), they are temporarily loaned to other reservations by default. To disable idle slot lending, set the ignore_idle_slots option to true.

BigQuery Reservations Cost Control

  • Use BigQuery Slot Recommender to determine the optimal number of slots.

  • Configure BigQuery Slot Autoscaling to scale when the reserved slots are exhausted, minimizing the number of slots that need to be pre-purchased.

  • Set a "maximum concurrent queries per project" limit.

Materialized Views

Views that are updated automatically. Updated every 30 minutes by default. Row insertions are reflected in approximately 5 minutes. Unlike regular views, materialized views can improve query performance (the exact mechanism was unclear to me at the time...).

BigQuery Availability

BigQuery SLA is 99.99%. Storage is replicated across zones, so zone failures are tolerated.

BigQuery Maintenance

BigQuery has no maintenance downtime due to rolling updates. (For managed DB services on public cloud, SLAs generally exclude maintenance windows.)

Disaster Recovery

Two approaches:

  • Copy BigQuery
    • Create a BigQuery instance at the DR site, and use the BigQuery Data Transfer service to copy data at a minimum interval of 12 hours.
  • Store in Cloud Storage and load into BigQuery during a disaster
    • Create a Cloud Storage (GCS) bucket at the local site and export from BigQuery.
    • Use Cloud Storage Transfer to transfer to the DR site's GCS. Delete the local GCS data after transfer.
    • During a disaster, load from the DR site's GCS into BigQuery.

BigQuery Backup/Restore

The time travel feature allows restoring to any state up to 7 days back. SELECT * FROM FOR SYSTEM TIME AS OF Storage costs for the time travel period are not charged. Use time travel to save data to another table as a backup.

Transactions in BigQuery

DML operations either fully succeed or are fully rolled back without being committed. Before committing DML results to a table, BigQuery checks for conflicts with the current table. If there is a conflict, DML execution is retried up to 3 times. DML operations targeting different partitions do not conflict. When DML operations are issued simultaneously against the same partition, only the first to complete is committed. Consolidating DML operations into larger jobs reduces cases where transactions fail and need to be re-executed. Behind the scenes, each DML operation generates a small table file; these are merged and presented as a single table.

BigQuery Overhead

UPDATE, DELETE, and MERGE DML operations have non-negligible overhead. In contrast, INSERT overhead is relatively small.

BigQuery Access Control

IAM can be used to control access at the project, dataset, and table level. Additionally, by creating policy tags in Data Catalog and assigning them to columns, then granting users the "Fine-grained reader" role per project, column-level access control can be enforced. Even organization owners and project owners cannot read column data without this role. Using authorized views, you can share query results without granting access to the underlying tables.

Auditing with INFORMATION_SCHEMA

INFORMATION_SCHEMA is a set of views for querying dataset and table metadata. With INFORMATION_SCHEMA, you can retrieve data access history — "when, who, and what queries were run."

  • INFORMATION_SCHEMA.JOBS_BY_USER: jobs submitted by the current user
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT: jobs submitted in the current project
  • INFORMATION_SCHEMA.JOBS_BY_FOLDER: jobs submitted in the project's parent folder
  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION: jobs submitted in the organization associated with the project

Dataproc

  • Dataproc is a managed Hadoop/Spark service.

  • Storage and compute are separated.

  • Fast startup (within 90 seconds).

  • Thanks to storage separation and fast startup, it can be used as an ephemeral cluster. Start the cluster when a job runs, and destroy it when the job completes.

  • Hadoop/Spark cluster VMs support autoscaling.

Dataproc Hub

Starts a Dataproc cluster and provides a JupyterLab environment.

Data Catalog

A managed service for technical and business metadata management. Technical metadata is synced automatically. Business metadata requires manual tagging by users.

Dataflow

A fully managed, serverless distributed data processing platform using Apache Beam. ETL processing is written in Python. The job graph (workflow) is visible in the Cloud Console. Automatically selects the optimal and available zone. Autoscales based on data processing volume (though specifying a fixed worker count can sometimes complete jobs faster). Supports both batch and streaming processing.

Supplement: Apache Beam

A unified programming model specialized for data processing. Batch and streaming can be written with the same syntax. Apache Beam can run on distributed processing platforms other than Dataflow, such as Apache Spark and Apache Flink. Can be written in Java, Python, Go, etc. Provides connectors called Built-in I/O for a wide variety of data sources.

Cloud Composer

A fully managed workflow management service using Apache Airflow. A Cloud Composer environment consists of a Kubernetes Engine cluster, Cloud Storage bucket, Cloud Logging, and Cloud Monitoring (Cloud SQL is also used in the background). You can choose machine types for the Airflow web server and database. Workflows are defined in Python. Uploading Python files to Cloud Storage automatically deploys the workflow. Created workflows can be viewed in the Airflow Web UI. Best practice: let workers (e.g., BigQuery) do the transformation work and keep DAGs as simple as possible.

  • DAG: a series of tasks organized as a directed acyclic graph
  • Task: an individual unit of processing in a DAG; individual tasks use operators to define their logic
  • Operators: BashOperator for executing Bash commands, PythonOperator for Python code, and many more

Access Control in Cloud Composer

Environment options allow specifying an IP range for web server access. Restricting to private IPs is also possible.

Managing Connection Credentials in DAGs

DB and API connection credentials can be saved as Connections in the Airflow Admin menu. However, allowing all users with web UI access to view and edit credentials is not ideal. Saving connection credentials in Secret Manager provides more secure management.

Cloud Data Fusion

A fully managed data integration service built on an OSS data pipeline platform called CDAP by CaskData. No-code ETL pipeline construction. Individual tasks (data reading, transformation) are defined as nodes; nodes are connected to form a pipeline. Node definitions use plugins; diverse plugins are available for cloud services, databases, and storage integration. Dataproc is used as the pipeline execution environment, creating an ephemeral cluster. Supports data lineage management.

Cloud Data Fusion Usage

  • First, create an instance.
  • Create a pipeline in the GUI.
  • Execute the pipeline.

Comparison: Cloud Composer vs. Cloud Data Fusion

Cloud Composer focuses on workflow orchestration; individual ETL jobs use BigQuery or Dataflow. Cloud Data Fusion focuses on data integration. Since individual pipelines are independent, Cloud Composer is used when multiple pipelines need orchestration.

VPC Service Controls for Access Control

Set a virtual security perimeter for Google Cloud services like Cloud Storage and BigQuery, enabling context-based access control such as "allow access only from specific VPC networks or source IP addresses" and "prevent data copying to unauthorized resources outside the perimeter." Communication within the service perimeter is unrestricted; cross-boundary communication is blocked by default. For example, to add IP restrictions to BigQuery, use VPC Service Controls. Supported services: https://cloud.google.com/vpc-service-controls/docs/supported-products?hl=ja

Auditing with Cloud Logging

A service for storing, searching, and managing the following three types of logs:

  • Admin Activity logs
  • System Event logs
  • Data Access logs

Admin Activity logs include IAM permission changes, VM instance creation, etc. Enabled by default and cannot be disabled. System Event logs record Google Cloud system events unrelated to user actions. Enabled by default and cannot be disabled. Data Access logs are access logs for resources containing data, such as BigQuery and Cloud Storage. BigQuery is enabled by default; other services are disabled. Read access logs can be voluminous — be careful. Enable/disable via "IAM & Admin" → "Audit Logs."

Cloud Logging Aggregated Sink

A feature that creates a sink at the organization or folder level to export logs from all resources below it, ensuring comprehensive log collection.

Auditing with Cloud Asset Inventory

Search and export the state of all resources on Google Cloud — "at what point in time, what resources existed, and what their configuration was" — as assets.

Why Was the Data Lake Invented?

To answer unknown questions that may arise in the future. A data mart can only answer pre-defined questions.

← Back to articles