Designing data processing systems

Security and compliance

IAM for data systems

• Grant roles to groups, at the lowest level that works (dataset or table rather than project). Use predefined roles; custom roles only when no predefined one fits.
• BigQuery roles you must tell apart: bigquery.dataViewer (read data), bigquery.dataEditor (read/write tables), bigquery.dataOwner, bigquery.user (run jobs, create datasets), bigquery.jobUser (run jobs only, no data access), bigquery.admin. Running a query needs jobUser on the project that runs the job plus read access on the data – the two can be in different projects.
• Service accounts run pipelines. Give each pipeline its own account (Dataflow worker service account, Dataproc VM service account, Composer environment service account) rather than the default Compute Engine account. Never use exported keys; use attached accounts, Workload Identity Federation for outside workloads, and impersonation (serviceAccountTokenCreator) for people and CI.
• Organization policies are guardrails that IAM can’t express: constraints/gcp.resourceLocations (allowed regions), constraints/iam.disableServiceAccountKeyCreation, constraints/gcp.restrictNonCmekServices (force CMEK), constraints/storage.uniformBucketLevelAccess, constraints/sql.restrictPublicIp.
• VPC Service Controls build a perimeter around BigQuery, Cloud Storage, Pub/Sub and others to stop data exfiltration even by someone with valid IAM. Use access levels and ingress/egress rules to allow exceptions. This is the answer to “prevent copying data to a project outside the organization”.
• Private connectivity: Private Google Access for VMs without external IPs (Dataflow workers and Dataproc nodes often run without public IPs), Private Service Connect, Cloud NAT for outbound internet, Dedicated/Partner Interconnect or Cloud VPN to on-premises.

Encryption and keys

• Services using CMEK in data work: BigQuery (dataset or table default key; the BigQuery service agent needs cloudkms.cryptoKeyEncrypterDecrypter), Cloud Storage buckets, Pub/Sub topics, Dataflow (pipeline state and temp storage), Dataproc cluster disks, Spanner, Bigtable, Cloud SQL, Composer. The key and the resource must be in the same location (a multi-region dataset US needs a key in us).
• Destroying or disabling a CMEK key makes data unreadable – the mechanism behind “crypto-shredding”.
• Application-layer encryption: BigQuery supports AEAD functions (AEAD.ENCRYPT, keysets wrapped by KMS) for column-level encryption inside SQL. Deterministic AEAD allows joins and GROUP BY on encrypted values.

Privacy and PII

Sensitive Data Protection (the Cloud DLP API) finds and de-identifies sensitive data:

• Inspect with built-in infoTypes (credit card, email, national IDs) or custom infoTypes and dictionaries. Discovery profiles BigQuery, Cloud Storage, Cloud SQL and others on a schedule and creates data profiles with sensitivity scores, and can tag columns automatically.
• De-identification transformations: redaction, masking (replace characters), bucketing (generalise ranges), date shifting, tokenisation (replace with a surrogate; format-preserving encryption keeps the shape; deterministic encryption keeps joinability) and pseudonymisation with a crypto hash. Re-identification is possible only with reversible methods (FPE, deterministic encryption).
• Risk analysis: k-anonymity, l-diversity, k-map estimate re-identification risk.
• Typical pipeline: raw landing bucket (restricted) → Dataflow or DLP job de-identifies → curated dataset analysts can read. Use the DLP template feature to reuse inspection and de-identification settings.
• Inside BigQuery, prefer policy tags with dynamic data masking to hide columns without copying data (see the Preparing and using data for analysis notes). For text pipelines, Dataflow has a ready Data Masking/Tokenization template that calls DLP.

Regional and legal considerations

• Data sovereignty and residency: choose a region (single location) or multi-region (US, EU) for BigQuery datasets and buckets at creation. A dataset’s location can’t be changed afterwards – you copy the data to a new dataset. Queries can’t join across locations without copying.
• Use the gcp.resourceLocations organization policy to stop engineers creating resources elsewhere. Assured Workloads applies control packages (FedRAMP, CJIS, EU sovereign controls, data residency, personnel access) to a folder.
• Data Access audit logs (BigQuery data access logs are on by default; for most other services they must be enabled), Access Transparency and Access Approval show and control Google personnel access.
• For HIPAA, PCI and GDPR: sign the BAA, use CMEK where required, restrict regions, de-identify, keep audit logs, define retention (Cloud Storage retention policies and Bucket Lock for WORM, BigQuery table expiration) and right-to-erasure (design so a user’s data can be deleted – partition by user or use DELETE and DML with soft limits in mind).

Project, dataset and table architecture for governance

• A project is the main boundary for quotas, billing, IAM and VPC-SC. Common pattern: separate projects per environment (dev, test, prod) and often per data tier or domain; a central project for compute reservations and another for shared datasets.
• A dataset is the unit of IAM, location, default encryption key, default table expiration and (optionally) storage billing model. Split datasets by sensitivity and by audience: raw, staging, curated/mart, plus per-team sandboxes with expiry.
• Multi-environment: promote through the same code with parameters (Terraform modules, Dataform workspaces and release configurations, Composer variables). Don’t give developers write access to prod datasets; CI/CD service accounts deploy. Use authorized views or Analytics Hub (not copies) to expose prod data to dev in a controlled way, and masked or sampled copies for test.
• Separate the project that bills queries from the project that stores data when consumers run jobs in their own projects (their jobUser role, their reservation).

Reliability and fidelity

Preparing and cleaning data

• Gemini in BigQuery and prompts can generate SQL from natural language, explain and fix queries, and suggest transformations; the exam treats these as productivity aids. Validate generated SQL, give the model schema and descriptions (good column descriptions improve results), and don’t send sensitive data to services you haven’t approved.
• Data validation: Dataform assertions (uniqueKey, nonNull, rowConditions, custom SQL that returns offending rows), Dataplex automatic data quality scans (rules for null, range, set, regex, uniqueness, SQL expressions; results to BigQuery, alerts), BigQuery constraints are informational only (primary and foreign keys aren’t enforced), Great Expectations or custom Beam checks, and dead-letter outputs in Dataflow so bad rows are quarantined, not dropped.

Monitoring and orchestration

• Orchestrate with Cloud Composer (Airflow DAGs; dependencies, retries, backfills, many service operators), Workflows (serverless HTTP/API step orchestration with retries and conditional logic; lighter than Airflow), Dataform workflow configurations or BigQuery pipelines for SQL-only chains, Cloud Scheduler for a simple cron trigger, and Eventarc or Pub/Sub for event-driven starts.
• Monitor with Cloud Monitoring and Logging: Dataflow job metrics (system lag, data watermark age), Pub/Sub backlog, Composer task failures, BigQuery job errors. Use alerting policies and notification channels. Details are in the maintenance notes.

Disaster recovery and fault tolerance

• Define RPO (data loss tolerated) and RTO (time to recover) first; they pick the tier.
• Cloud Storage: multi-region and dual-region buckets are geo-redundant; turbo replication on dual-region gives a 15-minute RPO target. Versioning, soft delete and retention guard against deletion.
• BigQuery: data is replicated inside the region across zones automatically. Time travel (default 7 days, configurable 2 to 7) restores earlier states; fail-safe keeps deleted data 7 days more for Google-assisted recovery; table snapshots keep long-term point-in-time copies cheaply; cross-region dataset replication and managed disaster recovery (Enterprise Plus, with a paired reservation and failover) cover region loss.
• Spanner multi-region configurations (for example nam-eur-asia1) offer 99.999% availability with RPO of zero; Cloud SQL HA uses a synchronous standby in another zone with automatic failover, plus cross-region read replicas for DR; Bigtable uses multi-cluster replication with app profiles; AlloyDB has cross-region replication.
• Pub/Sub stores messages redundantly across zones; use message retention, replay (seek) and snapshots to reprocess.
• Dataflow workers are replaced automatically; streaming jobs checkpoint state. Design for replay: keep the source of truth (Pub/Sub retention, raw files in Cloud Storage) so you can rerun after a bug.
• Make writes idempotent (MERGE on keys, deduplicate on insertId or message id) so retries and replays don’t duplicate data.

ACID, consistency and availability

CAP framing: Spanner is CP but engineered for very high availability; Bigtable single-cluster routing is strongly consistent, multi-cluster routing is eventually consistent. A question stressing “transactions across rows and regions” is Spanner; “analytics” is never an OLTP database.

Flexibility and portability

• Map business requirements (latency, freshness, volume growth, users, compliance, budget) to the design, and decouple stages with Pub/Sub and Cloud Storage so a component can be swapped.
• Open formats and open source reduce lock-in: Parquet, ORC, Avro and Apache Iceberg in Cloud Storage; Apache Beam runs on Dataflow, Flink or Spark; Apache Kafka (Managed Service for Apache Kafka) is portable; Dataproc runs standard Spark/Hadoop; BigLake lets BigQuery, Spark and other engines query the same lake files with one security model; BigQuery Omni queries data in AWS S3 and Azure Blob without moving it.
• Multi-cloud and residency: keep data where the rule says and bring compute to it (Omni, cross-cloud transfer for allowed copies, Cross-Cloud Interconnect). Cloud Composer/Airflow and Kubernetes stay portable.
• Staging, catalog, profiling, discovery: land data in a raw zone, then curate. Dataplex Universal Catalog (successor to Data Catalog) discovers and catalogs metadata automatically for BigQuery, Cloud Storage, Pub/Sub and more; supports business glossary, aspects (custom metadata), lineage and data profile scans.

Designing data migrations

Plan first

1. Assess: inventory sources, volumes, change rates, dependencies, users, SLAs, query patterns and security rules. Use the BigQuery Migration Service assessment for Teradata, Snowflake, Redshift, Oracle and others.
2. Choose an approach: lift and shift (fast, little change), improve and move, or rebuild. Decide on downtime allowed (offline copy vs continuous replication).
3. Migrate in waves, run old and new in parallel, validate (row counts, checksums, sample queries; the Data Validation Tool compares tables across systems), then cut over and decommission.

Tool map

• Networking: Dedicated Interconnect (10 or 100 Gbps), Partner Interconnect, HA VPN (up to 3 Gbps per tunnel). Compute transfer time: size divided by sustained bandwidth; if it takes weeks, ship an appliance. For repeat transfers over a good link, STS is better.
• Teradata and similar warehouses: the BigQuery Data Transfer Service migration agent extracts to Cloud Storage and loads. Hadoop jobs can usually run unchanged on Dataproc; move storage from HDFS to Cloud Storage first.