Designing Data Processing Systems

IAM and Access Control

Google Cloud Identity and Access Management (IAM) follows the principle of least privilege. Permissions are grouped into roles, and roles are bound to members (users, groups, service accounts) at a specific resource level (organization, folder, project, or individual resource).

For data engineering, key IAM considerations include BigQuery dataset-level access, column-level security via policy tags, and row-level security with CREATE ROW ACCESS POLICY.

-- BigQuery row-level security
CREATE ROW ACCESS POLICY region_filter
ON `project.dataset.sales`
GRANT TO ("group:us-analysts@example.com")
FILTER USING (region = 'US');

-- Column-level security with policy tags
-- Applied via Data Catalog taxonomy in the console
-- Then grant access to the policy tag:
-- roles/datacatalog.categoryFineGrainedReader

Encryption Options

All data in Google Cloud is encrypted at rest by default using Google-managed encryption keys (GMEK). For additional control, you can choose between Customer-Managed Encryption Keys (CMEK) and Customer-Supplied Encryption Keys (CSEK).

# Create a CMEK-protected BigQuery dataset
bq mk --dataset \
  --default_kms_key=projects/my-proj/locations/us/keyRings/my-ring/cryptoKeys/my-key \
  my-proj:secure_dataset

# Create a CMEK-encrypted Cloud Storage bucket
gsutil kms encryption \
  -k projects/my-proj/locations/us/keyRings/my-ring/cryptoKeys/my-key \
  gs://my-secure-bucket

PII and Data Governance

Sensitive Data Protection (formerly Cloud DLP) provides APIs for discovering, classifying, and protecting sensitive data. It supports 150+ built-in infoTypes for PII detection and offers multiple de-identification techniques.

Data Sovereignty and Residency

Data sovereignty requirements dictate where data can be stored and processed. GCP offers multiple mechanisms to enforce data residency:

• Dataset/Bucket location — BigQuery datasets and Cloud Storage buckets are created in specific regions or multi-regions
• Organization policies — constraints/gcp.resourceLocations restricts which regions resources can be created in
• VPC Service Controls — Creates security perimeters around GCP resources to prevent data exfiltration
• Assured Workloads — Automates compliance controls for regulated industries (FedRAMP, HIPAA, PCI DSS)

Dataform and SQLX Workflows

Dataform is BigQuery's native SQL transformation service. It uses SQLX (an extension of SQL) with dependency management, testing, and documentation. Dataform brings software engineering best practices to data transformation: version control, CI/CD, and automated testing.

-- Dataform SQLX: Define a transformation with dependencies
-- File: definitions/staging/stg_orders.sqlx

config {
  type: "view",
  schema: "staging",
  description: "Staged orders with data quality filters",
  assertions: {
    uniqueKey: ["order_id"],
    nonNull: ["order_id", "customer_id", "order_date"]
  }
}

SELECT
  order_id,
  customer_id,
  order_date,
  ROUND(total_amount, 2) AS total_amount,
  status,
  CURRENT_TIMESTAMP() AS _loaded_at
FROM ${ref("raw_orders")}
WHERE order_id IS NOT NULL
  AND total_amount > 0

-- Dataform SQLX: Incremental table
-- File: definitions/marts/fct_daily_revenue.sqlx

config {
  type: "incremental",
  schema: "marts",
  uniqueKey: ["order_date", "region"],
  bigquery: {
    partitionBy: "order_date",
    clusterBy: ["region"]
  }
}

SELECT
  DATE(order_date) AS order_date,
  region,
  COUNT(DISTINCT order_id) AS num_orders,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM ${ref("stg_orders")}
${when(incremental(), `WHERE order_date > (SELECT MAX(order_date) FROM ${self()})`) }
GROUP BY 1, 2

Data Validation Strategies

Data validation ensures that data flowing through pipelines meets quality expectations. This includes schema validation, business rule checks, and statistical validation.

-- Dataplex data quality YAML specification
rules:
  - column: order_id
    dimension: uniqueness
    threshold: 1.0
  - column: total_amount
    dimension: validity
    sql_expression: "total_amount > 0 AND total_amount < 1000000"
    threshold: 0.99
  - column: order_date
    dimension: freshness
    sql_expression: "order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)"
    threshold: 0.95
  - dimension: completeness
    sql_expression: "order_id IS NOT NULL AND customer_id IS NOT NULL"
    threshold: 1.0

ACID Guarantees Across GCP Services

Understanding ACID (Atomicity, Consistency, Isolation, Durability) behavior across services is critical for choosing the right storage:

Multi-Cloud and Hybrid Strategies

GCP provides several approaches for multi-cloud and hybrid data processing:

-- BigQuery Omni: Query S3 data from BigQuery
CREATE EXTERNAL TABLE `project.dataset.s3_sales`
WITH CONNECTION `aws-us-east-1.my-connection`
OPTIONS (
  format = 'PARQUET',
  uris = ['s3://my-bucket/sales/*.parquet']
);

SELECT region, SUM(revenue) AS total_revenue
FROM `project.dataset.s3_sales`
GROUP BY region
ORDER BY total_revenue DESC;

Data Governance with Dataplex

Dataplex provides a unified data fabric for organizing, managing, and governing data across data lakes, data warehouses, and data marts. Key concepts:

• Lakes — Logical grouping of data representing a business domain
• Zones — Subdivisions within a lake (raw zone, curated zone) with data format expectations
• Assets — Mapped Cloud Storage buckets or BigQuery datasets
• Data quality tasks — Automated quality checks defined in YAML
• Auto-discovery — Automatically catalogs and classifies data assets

BigQuery Data Transfer Service

The BigQuery Data Transfer Service automates data loading into BigQuery from SaaS applications (Google Ads, YouTube, etc.), Amazon S3, Amazon Redshift, and Teradata. It runs on a schedule and handles incremental transfers.

# Create a scheduled transfer from S3 to BigQuery
bq mk --transfer_config \
  --project_id=my-project \
  --data_source=amazon_s3 \
  --target_dataset=imported_data \
  --display_name="Daily S3 Import" \
  --schedule="every 24 hours" \
  --params='{
    "destination_table_name_template": "s3_events_{run_date}",
    "data_path": "s3://source-bucket/events/*.parquet",
    "access_key_id": "AKIAIOSFODNN7EXAMPLE",
    "file_format": "PARQUET"
  }'

Database Migration Service (DMS)

DMS provides continuous replication from source databases to GCP managed databases. It supports homogeneous migrations (MySQL to Cloud SQL for MySQL) and heterogeneous migrations (Oracle to Cloud SQL for PostgreSQL, or to AlloyDB).

Datastream for Change Data Capture (CDC)

Datastream is a serverless CDC and replication service. Unlike DMS (which targets managed databases), Datastream streams changes into BigQuery or Cloud Storage for analytics.

# Datastream architecture flow:
# Source DB (Oracle/MySQL/PostgreSQL/AlloyDB)
#   |
#   v [CDC via log reading]
# Datastream
#   |
#   v [Real-time streaming]
# BigQuery (merge tables) or Cloud Storage (Avro/JSON files)
#   |
#   v [Optional: Dataflow template for processing]
# Final destination (cleaned, transformed)

Visual ETL Pipeline Builder

Cloud Data Fusion is a fully managed, code-free data integration service built on the open-source CDAP platform. It provides a visual drag-and-drop interface for building ETL/ELT pipelines with 200+ pre-built connectors and transformations.

Data Fusion pipelines execute on Dataproc clusters under the hood. You can configure ephemeral (auto-created) or persistent clusters based on workload patterns.

Lambda vs Kappa Architecture

Two foundational patterns for data processing systems:

Data Mesh Principles on GCP

Data mesh decentralizes data ownership to domain teams while maintaining interoperability:

• Domain ownership — Each team owns their data products (BigQuery datasets with quality SLAs)
• Data as a product — Datasets published with documentation, SLAs, and access patterns via Analytics Hub
• Self-serve platform — Dataplex provides the federated governance layer across domains
• Federated governance — Central policies (encryption, PII handling) with domain-specific implementation

-- Data mesh pattern: Domain team publishes a data product
-- Step 1: Create a curated, documented dataset
CREATE SCHEMA `sales-domain.product_catalog`
OPTIONS (
  description = "Sales domain data product: Product catalog with daily refresh",
  labels = [("domain", "sales"), ("tier", "gold"), ("sla", "daily")]
);

-- Step 2: Share via Analytics Hub
-- Create a listing in Analytics Hub for cross-domain consumption
-- Consumers get a linked dataset (zero-copy, governed access)

Key Comparisons to Memorize