Designing data processing systems

Designing for Security and Compliance

Identity and Access Management (Cloud IAM and Organization Policies)

Least-privilege access in Google Cloud is implemented through Cloud Identity and Access Management (IAM), which assigns permissions to users, groups, and service accounts at the resource level. Three types of roles are available: basic roles (Owner, Editor, Viewer) are broad and not recommended for security; predefined roles offer granular permissions for specific services like BigQuery; and custom roles allow bundling individual permissions for unique requirements. For BigQuery, fine-grained controls include column-level security using policy tags, row-level security, and dynamic data masking. Service account impersonation eliminates the need for long-lived keys by allowing authorized users to temporarily act as a service account, requiring either the Service Account User role or the Service Account Token Creator role. Workload Identity Federation provides an even more secure alternative by allowing external workloads to impersonate service accounts without any keys.

VPC Service Controls mitigate data exfiltration by creating service perimeters around Google-managed services such as BigQuery and Cloud Storage. These perimeters control data movement across the boundary, and administrators can configure perimeter bridges to allow authorized communication between separate projects in different perimeters. BigQuery provides native tools: column-level access control uses policy tags to restrict access to specific columns, row-level security filters dataset rows based on user identity, and dynamic data masking obscures column values at query time based on roles. For cell-level encryption, AEAD encryption functions require permissions for both the table and the cryptographic key.

Organization Policies are centralized, declarative rules that enforce security guardrails across the resource hierarchy (Organization, Folders, Projects). A key feature is policy inheritance: a policy set at a higher level automatically applies to all child resources, but can be overridden by a more specific policy on a child resource. Critical constraints include gcp.resourceLocations to restrict geographic regions for resource creation, iam.disableServiceAccountKeyCreation to prevent long-lived service account keys, and iam.automaticIamGrantsForDefaultServiceAccounts to disable broad role grants by default. Tags — metadata labels attached to resources — allow conditional policy enforcement, such as allowing public IP addresses only for resources tagged as "public-facing".

Data Security (Encryption and Key Management)

Cloud Key Management Service (Cloud KMS) and Cloud HSM (FIPS 140-2 Level 3) manage cryptographic keys throughout their lifecycle using envelope encryption. In this approach, a data encryption key (DEK) encrypts the data itself, and a separate key encryption key (KEK) encrypts (wraps) the DEK. The key management hierarchy has three levels: a key ring groups related crypto keys, and each crypto key can have multiple key versions that exist in active, pending, or disabled states. When a new version is created, it becomes the primary version for encryption, while older versions remain available only for decryption of data they previously encrypted, supporting key rotation without data loss. Google recommends a rotation period of typically 90 days.

Access to keys is controlled through IAM with the principle of separation of duties: users who manage keys should be different from those who use them for encryption/decryption. Predefined roles such as Cloud KMS Admin, CryptoKey Encrypter/Decrypter, and CryptoKey Viewer implement granular access. Organizations should create separate projects for key management and data storage to strengthen security boundaries. Customer-managed encryption keys (CMEK) give organizations full control over keys while using Google Cloud infrastructure for key operations, integrating with services like BigQuery, Cloud Storage, and Spanner. Cloud External Key Manager (Cloud EKM) allows key material to remain in customer-controlled environments for data sovereignty. Cloud KMS Autokey automates provisioning of CMEK when resources are deployed, following recommended practices.

Encryption in Google Cloud is applied by default to data at rest and in transit. For data at rest, Google uses multiple layers of encryption, including storage system-level and device-level encryption, with each logical chunk encrypted by a unique DEK wrapped by a KEK. Data in transit is automatically encrypted outside Google's physically secured network. Customers can choose among three key management models:

For pipelines, Cloud Dataflow and Cloud Dataproc support CMEK to encrypt pipeline states, temporary files, and VM disks. During ingestion, Dataflow can integrate with Sensitive Data Protection to tokenize or de-identify columns before storing in BigQuery. Client-side encryption using the Tink library ensures end-to-end protection for sensitive records imported from external environments. The standard key lifecycle workflow is: 1) Create keys using certified hardware security modules. 2) Assign authority by restricting key access to specific service accounts based on least privilege. 3) Rotate keys on a schedule (e.g., every 30 days) to limit exposure.

Privacy (Handling Personally Identifiable Information)

Transformational de-identification modifies sensitive records (PII) so organizations can analyze data without exposing original identities. Google Cloud provides these capabilities through Sensitive Data Protection. Two-way tokenization methods — format-preserving encryption (FPE) and deterministic encryption (DE) — replace raw values with encrypted values using a key, preserving referential integrity for joins. One-way tokenization uses cryptographic hashing to generate irreversible tokens, ideal for matching when original values never need decryption.

Non-cryptographic transformations include masking (replacing sensitive characters with a fixed symbol), bucketing (grouping exact values into ranges), and date shifting (moving timestamps by a consistent pseudorandom offset per individual). Scalable de-identification architectures use Cloud Dataflow pipelines that execute transformation rules defined in Sensitive Data Protection templates, which decouple logic from application code. Cryptographic techniques depend on a token encryption key managed through Cloud KMS, regularly rotated and assigned separately to distinct data fields.

Granular access controls in BigQuery secure sensitive attributes through column-level security using policy tags managed in Dataplex Universal Catalog. Only users with specific IAM roles can view unmasked data. BigQuery also supports row-level access policies, authorized views (which grant access to aggregated results without exposing base tables), and dynamic data masking. VPC Service Controls build a security perimeter around resources to prevent unauthorized data exfiltration. Automated governance practices include scanning for sensitive data, policy tagging upon discovery, and retention control via lifecycle rules. Cloud Audit Logs and Access Transparency logs provide immutable records of user actions, and Dataplex tracks data lineage to trace the flow of sensitive data.

Automated detection uses Sensitive Data Protection with built-in infoTypes — detectors that identify patterns like credit card numbers and Social Security numbers using regular expressions and checksums. Administrators can create custom infoTypes for proprietary formats. Two complementary scanning mechanisms exist: 1) Sensitive data profiling continuously scans entire organizations to generate risk profiles; 2) On-demand inspection runs targeted scans against specific tables or buckets. Inspection templates standardize detection rules across pipelines, and when integrated into Cloud Dataflow or Cloud Storage, they ensure all incoming data is classified before reaching analytical storage.

Regional Considerations (Data Sovereignty)

Data sovereignty requires digital information to adhere to the legal frameworks of the geographic location where it is collected, stored, and processed. In Google Cloud, administrators enforce data sovereignty using the gcp.resourceLocations organization policy constraint, which restricts provisioning of storage and compute resources to an approved list of regions, multi-regions, or zones. The policy blocks creation of non-compliant resources but does not affect existing resources. Allowed locations can be defined as individual zones, specific regions (e.g., europe-west1), or multi-region identifiers like eu. Predefined location value groups such as in:eu-locations simplify administration. Assured Workloads automatically enforces pre-configured regional boundaries for compliance regimes like the EU Data Boundary. Global resources (e.g., IAM policies, global load balancers) cannot be restricted by gcp.resourceLocations, so security teams combine this constraint with gcp.restrictServiceUsage to block unauthorized services.

Storage topologies vary by geographic distribution:

During disaster recovery, secondary backup targets must remain inside compliant jurisdictions. BigQuery provides dedicated regional API endpoints (e.g., bigquery.us-central1.rep.googleapis.com) that confine request traffic and query execution to the target region. Compute workloads like Dataproc and Dataflow must operate in the same region as their input data to prevent unauthorized cross-border transfers. Customer-managed encryption keys (CMEK) must be provisioned in the same region as the resource they protect, and the Cloud KMS CryptoKey Encrypter/Decrypter role must be granted to the Compute Engine, Cloud Storage, and Dataproc service agents. VPC Service Controls create secure perimeters that prevent data exfiltration, and internal DNS records route API requests to restricted.googleapis.com to keep traffic on private Google networks.

Legal and Regulatory Compliance

Data engineers must understand regulations such as GDPR, HIPAA, PCI-DSS, and other industry-specific compliance frameworks. Compliance involves implementing appropriate technical controls, maintaining audit trails, and ensuring data handling practices meet legal obligations. Google Cloud provides compliance certifications and attestations, but organizations remain responsible for their own compliance posture.

Designing Project, Dataset, and Table Architecture for Data Governance

Governance architecture includes organizing resources logically, applying appropriate access controls at each level, implementing labeling and tagging strategies for classification, and establishing clear ownership boundaries. Projects serve as the primary isolation boundary, datasets contain related tables and support access controls, and tables hold the actual data. The architecture should consider separation between development and production environments, audit requirements, and the ability to apply organization policies consistently across resources.

Multi-Environment Use Cases (Development vs. Production)

Development environments typically require broader access for testing and debugging, while production environments demand strict access controls, comprehensive logging, and robust security measures. Best practices include using separate projects or folders for each environment, applying organization policies that restrict resource creation in production, implementing different IAM roles and permissions between environments, and ensuring that data used in development is either synthetic or properly de-identified. Environment separation prevents accidental changes to production systems and provides a safe space for experimentation.

Designing for Flexibility and Portability

Mapping Business Requirements to Architecture

Translating business objectives into technical requirements means interpreting stakeholder needs and regulatory constraints to define specific targets for data systems. The core architectural principle is decoupling storage from computation. Keeping storage and compute separate improves flexibility for migrating data and changing tools while reducing costs. To minimize latency and egress fees, both platforms should be deployed within the same Google Cloud zone and region. When expanding to multiple regions, an architect performs an environment assessment that inventories existing data infrastructure and analyzes storage platform availability, computation configurations, and network latency between source and target environments. BigQuery exemplifies decoupled design using its Capacitor columnar format, with processing power allocated dynamically via slots that scale automatically. Security and governance are aligned using IAM with predefined roles for least privilege and VPC Service Controls to prevent data exfiltration.

Assessing and planning for technological evolution means evaluating how new technologies impact existing architecture and creating migration strategies that minimize disruption. Traditional systems like Amazon Redshift tie compute and storage together, requiring manual resizing, while BigQuery's serverless architecture separates them for independent scaling. When planning evolution, evaluate impact on operational overhead, cost models, and performance. Migration strategies include: 1) Extract and Load (EL) using BigQuery Data Transfer Service; 2) Extract, Transform, and Load (ETL) using Dataflow, Dataproc, or Cloud Data Fusion; 3) Change Data Capture (CDC) for real-time replication. A hybrid strategy using BigQuery Omni allows querying data stored in other clouds without moving it. To ensure portability, use open columnar formats like Parquet or Avro in Cloud Storage, implement BigLake for a unified interface, and use the batch SQL translator to convert legacy SQL dialects.

Designing for scalability and future growth requires separating storage from compute so each component can scale independently. Use Cloud Monitoring to analyze current resource usage and predict needs, understand service quotas, request increases when needed, and perform regular load testing. Build modular components (e.g., Cloud Storage for ingestion, Dataflow for processing, BigQuery for analytics) that can be upgraded separately. Scaling strategies include horizontal scaling (adding instances), vertical scaling (increasing power), autoscaling, and serverless services. For peak events, configure autoscaling policies in Compute Engine and GKE, use Cloud Load Balancing to distribute traffic, and Pub/Sub to decouple system components.

Data and Application Portability (Multi-Cloud and Data Residency)

Data residency requires organizations to store and process PII within designated geographic boundaries. Administrators configure this by selecting specific regions when creating managed database instances like Cloud SQL, AlloyDB, and Cloud Spanner. For analytical workloads, BigQuery supports regional and multi-region dataset configurations. Customer-Managed Encryption Keys (CMEK) managed through Cloud KMS enforce data sovereignty by controlling where keys are generated and stored. Regional service endpoints ensure data-in-transit remains within a defined location. Organization policy constraints (e.g., gcp.resourceLocations) restrict resource creation to authorized regions but do not relocate existing infrastructure. Google Cloud platforms maintain compliance with standards like ISO/IEC 27001, SOC 1, and FedRAMP.

Open-source standards eliminate vendor lock-in by separating data transformation logic from execution infrastructure. Apache Beam provides an open-source standard for unified batch and streaming pipelines; running them on Cloud Dataflow provides a serverless execution platform, and the same code can run on Apache Flink or Apache Spark elsewhere. Cloud Dataproc provides a managed environment for Apache Spark and Apache Hadoop clusters, allowing existing on-premises jobs to run in the cloud without rewriting code. Cloud Dataproc uses the Hadoop Cloud Storage Connector and Hadoop BigQuery Connector for direct access.

Multi-cloud data processing is enabled by BigQuery Omni, which extends the BigQuery query engine to run inside AWS and Azure environments. By defining a BigLake table over external data stored in Amazon S3 or Azure Blob Storage, analysts execute standard SQL queries as if the data were local. BigQuery Omni authenticates using the host cloud's native IAM, avoiding stored keys and eliminating egress charges. Three query mechanisms exist: 1) Cross-cloud joins that correlate data between Google Cloud and an external cloud; 2) Cross-cloud materialized views that incrementally replicate pre-aggregated results; 3) Cross-cloud transfer that writes the full result set into BigQuery storage. For private hybrid connectivity, Cross-Cloud Interconnect provides dedicated physical links with optional MACsec encryption.

Data Staging, Cataloging, Profiling, and Discovery (Data Governance)

Enterprise metadata cataloging creates a centralized, searchable inventory of all data assets. The Dataplex Universal Catalog (successor to Data Catalog) automatically ingests, organizes, and indexes metadata from BigQuery, Cloud Storage, and Pub/Sub. Metadata is enriched with business context using schematized Tag Templates that define data sensitivity, owner, department, quality scores, and retention policies. This enables secure, policy-based semantic search where users can search using business terms, with access governed by IAM policies and sensitive data classifications.

Automated data profiling with Dataplex Universal Catalog analyzes statistical characteristics across data lakes and warehouses, identifying where sensitive data resides and automatically recommending data quality rules for BigQuery. Sensitive Data Protection (formerly Cloud DLP) discovers, classifies, and protects sensitive data using over 150 predefined detectors for patterns like PII and credit card numbers. It provides de-identification transformations including masking, tokenization, and redaction. Data classification involves automatically tagging assets based on sensitivity levels using policy tags in BigQuery. The taxonomy is hierarchical: access to a higher sensitivity level (e.g., 3_Confidential) includes access to all lower levels (e.g., 2_Private). Column-level security and dynamic data masking enforce restrictions at query time. Data de-identification techniques like tokenization are typically implemented in Dataflow pipelines that process data from Cloud Storage and store results in BigQuery.

Governed data staging uses a multi-tiered lakehouse architecture built with Dataplex lakes and virtual asset zones. Storage is organized into three zones: 1) Raw zone stores unprocessed data in its original format; 2) Staging zone holds intermediate datasets undergoing validation; 3) Curated zone contains fully enriched, clean data ready for consumption. Automated transitions between zones enforce distinct schema evolution rules. The Dataplex Universal Catalog automates metadata collection, and data engineers use aspects to enrich entries with business metadata. For protecting sensitive data, implement policy tags, dynamic data masking, and AEAD encryption functions using customer-managed encryption keys (CMEKs). To optimize cost, use object lifecycle management or Autoclass in Cloud Storage to transition older staging data into lower-cost tiers.

Designing Data Migrations

Analyzing Current Stakeholder Needs, Processes, and Technologies

Stakeholder and user-centric analysis is the first step in designing a data migration to Google Cloud. It examines current needs, workflows, and technologies to create a plan that supports business objectives. Analysts identify functional requirements (what the system must do) and non-functional requirements (scalability, latency, security, cost). They map user personas and interaction patterns, then translate findings into measurable success criteria (e.g., reduction in report generation time). This analysis informs decisions like whether to start with an offload or full migration.

Assessing the current technological landscape involves documenting existing data warehouse schemas, source systems, and data flows. Two main migration approaches exist: an offload moves schema and data to BigQuery while keeping original upstream pipelines active, using incremental copies to synchronize data; a full migration moves both data and upstream pipelines to Google Cloud, allowing the legacy system to be retired. Organizations often start with an offload for specific use cases and fully migrate later. The assessment must examine existing pipelines (batch or streaming), identify dependencies (fan-in, fan-out), and document current ETL and ELT processes. Understanding whether Change Data Capture (CDC) patterns are used is also important.

Formulating a target state migration plan involves creating a phased roadmap. An iterative approach ensures zero downtime. During an offload, incremental synchronization keeps cloud and legacy environments aligned; a full migration redirects upstream pipelines directly to Google Cloud. Selecting transformation patterns: ETL transforms data using external compute engines before loading; ELT leverages BigQuery's parallel processing to transform using SQL after loading; CDC feeds clean historical versions of data in real-time. The plan must address pipeline dependencies by analyzing the orchestration DAG (Directed Acyclic Graph). A phased approach to migrate orchestration: 1) Migrate the legacy structure as-is; 2) Analyze and parallelize tasks; 3) Extract common tasks into their own DAGs. Once workloads are in BigQuery, use facade views to protect downstream analytics from schema changes. Adopt Infrastructure as Code (IaC) using Terraform to deploy cloud resources securely and repeatedly.

Planning Migration and Validation to Google Cloud

Continuous data validation and replication ensure data consistency throughout migration. Change Data Capture (CDC) tracks data changes from source systems in real-time. Google Cloud's Datastream service enables CDC by continuously replicating data from on-premises or cloud databases to BigQuery, Cloud Storage, and Cloud SQL, providing minimal downtime, data consistency, and real-time synchronization. A comprehensive validation framework includes automated checks that compare record counts, validate schema consistency, and verify data values between source and target using reconciliation queries. Organizations choose between an offload approach (migrate schema and data first, keep incremental copies synchronized) and a full migration approach (migrate use case and upstream pipelines, then deprecate legacy system). For continuous replication, data pipeline patterns include EL (Extract and Load without transformation), ELT (Load then transform using SQL), and ETL (Transform before loading).

Designing network architecture for secure data migration impacts security, cost, and performance. A Virtual Private Cloud (VPC) provides the isolated network foundation. For connectivity to on-premises, choose between Cloud VPN (encrypted tunnels over the public internet) and Cloud Interconnect (dedicated, high-bandwidth connections). Key principles: minimize latency and egress costs by preferring Cloud Interconnect for large-volume migrations; establish secure pathways with VPC firewall rules, IAM policies, and VPC Service Controls to prevent data exfiltration. The network must support the chosen migration service; for example, Database Migration Service requires VPC peering between source and Cloud SQL instances. Before migration, re-evaluate existing Cloud Interconnect or VPN connections, test connectivity and performance, and design a rollback strategy that maintains connectivity to the source environment.

Evaluating and selecting migration services depends on data volume, network bandwidth, and transfer windows. For automated scheduled ingestion from SaaS applications or legacy warehouses like Teradata and Redshift, use BigQuery Data Transfer Service (DTS). For online data transfers from other cloud providers or on-premises filesystems to Cloud Storage, use Storage Transfer Service (STS). When network bandwidth is limited and datasets exceed 20 TB, Transfer Appliance provides secure physical hardware for offline transfers. For online transfers under 1 TB, the Google Cloud CLI (gcloud storage) can be used. STS supports large-scale parallel transfers up to petabytes and can be controlled by a bandwidth cap. The target Cloud Storage bucket should be in the same region as the destination BigQuery dataset. Migrating from Teradata uses BigQuery DTS with an on-premises migration agent; for Amazon Redshift, BigQuery DTS unloads to an intermediate Amazon S3 bucket before loading. After transfer, use the Data Validation Tool (DVT), an open-source Python CLI tool, to automate multi-level comparison of source and target tables. To optimize query performance, evaluate denormalization strategies such as nested and repeated fields.

Key Facts

• Envelope encryption uses a Data Encryption Key (DEK) to encrypt data and a Key Encryption Key (KEK) managed in Cloud KMS or Cloud HSM to wrap the DEK.
• VPC Service Controls create security perimeters around Google-managed services to mitigate data exfiltration risks.
• The gcp.resourceLocations organization policy constraint restricts the geographic provisioning of new storage and compute resources but does not affect existing resources.
• BigQuery Omni executes SQL queries directly on data stored in Amazon S3 or Azure Blob Storage using the host cloud's native IAM, eliminating outbound data egress costs.
• The Dataplex Universal Catalog automatically ingests and indexes metadata from BigQuery, Cloud Storage, and Pub/Sub to create a centralized, searchable inventory for data governance.
• Datastream enables continuous change data capture (CDC) for real-time replication from source databases to Google Cloud services like BigQuery and Cloud Storage.
• Transfer Appliance is the recommended service for offline data transfers when network bandwidth is limited and datasets exceed 20 TB.