PDE — Frequently Asked Questions

Use Dataflow with Apache Beam's watermark-based windowing and idempotent writes to BigQuery. Implement a stateful DoFn that buffers events by device_id within a sliding window, applies deduplication logic, and writes to BigQuery using the at-least-once write disposition combined with a deduplication column. Dataflow's built-in checkpointing and BigQuery's transactional inserts (INSERT OVERWRITE with partition pruning) ensure exactly-once semantics. Avoid pure Pub/Sub -> BigQuery streaming inserts as they provide at-most-once delivery. Use withMethod(BigQueryWriteMethod.BATCH) with a triggering frequency to batch writes and reduce duplicate costs.

Create separate reservation assignments for each project tier. Assign a dedicated reservation to the production project with sufficient slots to handle peak SLA queries, using the min-active-projects setting to guarantee minimum capacity. For ad-hoc projects, assign them to a shared reservation pool with burstable capacity. Use the on-demand (pay-as-you-go) pricing model as a fallback when reserved slots are exhausted. Monitor slot utilization via BigQuery monitoring metrics and right-size reservations based on 30-day utilization trends. Consider using the BigQuery Reservation API to programmatically adjust slot allocations during peak hours.

First, increase the worker pod resources (CPU and memory) in the Cloud Composer environment configuration to accommodate the memory footprint of your tasks. Second, implement task-level retry logic with exponential backoff in your DAG definitions. Third, optimize your data processing tasks to reduce memory usage — for example, process files in smaller chunks rather than loading entire files into memory. Fourth, consider using the Cloud Composer 2 autoscaling feature to automatically adjust worker count. If the issue persists, split large DAGs into smaller, independent DAGs and use the XCom mechanism or GCS as an intermediate storage for passing data between tasks instead of in-memory transfer.

Implement a combination of BigQuery reservations and budget alerts. Create a shared reservation pool that all projects draw from, and enforce slot allocation limits per project using reservation assignments. Enable BigQuery job cost controls by setting query bytes limits per user or group using IAM conditions. Set up BigQuery budget alerts linked to Cloud Billing budgets to trigger notifications when spend exceeds thresholds. Additionally, use Data Catalog tags to classify datasets by cost center and enforce governance policies through Dataplex. For fine-grained control, use IAM conditions to restrict which projects can run queries against sensitive datasets based on labels.

Use Database Migration Service (DMS) with continuous replication to stream changes from PostgreSQL to BigQuery. DMS supports both full load and CDC (Change Data Capture) modes, allowing you to maintain a near-real-time copy of your PostgreSQL data in BigQuery without building custom replication pipelines. For schema compatibility, DMS automatically maps PostgreSQL data types to BigQuery types. This approach minimizes infrastructure overhead compared to building custom CDC with Debezium + Pub/Sub + Dataflow. If you need more control over the transformation logic, you can supplement DMS with Dataflow for complex ETL operations.

Implement Google Cloud Data Catalog as a centralized metadata management service. Data Catalog automatically ingests metadata from BigQuery (table schemas, column descriptions, labels), Cloud Storage (file formats, sizes), and can be integrated with Pub/Sub topics. Add custom tags and taxonomies to classify data by domain, sensitivity level, and ownership. Use Data Catalog's search and lineage features to help business users discover available data assets. For Cloud SQL, manually register database metadata in Data Catalog with relevant descriptions and tags. Integrate with Dataplex for automated data discovery and quality monitoring across these services.

Configure your Dataflow pipeline with a windowing strategy that accounts for late data. Use a fixed window (e.g., 1-hour windows) combined with an allowedLateness of 2 hours. Set the trigger to output results both on window watermark completion and when late data arrives. For aggregations, use a trigger with accumulation mode set to ACCUMULATE_AND_FIRE so that late data updates the existing results rather than discarding them. Write late data to a separate 'late data' output sink for auditing or reprocessing. Additionally, enable BigQuery's streaming buffer retention to ensure late-arriving events are captured. Monitor the late data rate via Dataflow metrics to adjust the allowedLateness parameter if needed.

First, create Cloud Key Management Service (KMS) keys in each project that contains BigQuery datasets. Then, for each dataset that needs CMEK encryption, set the encryption configuration to use the corresponding KMS key via the BigQuery API or gcloud CLI (bq update --set_encryption_key). Note that CMEK encryption in BigQuery is applied at the dataset level — all tables created in or copied to that dataset will be encrypted with the specified key. For existing tables, you need to copy them to a new table in the CMEK-enabled dataset. You cannot switch between CMEK and Google-managed encryption on the same table. Ensure the BigQuery service account has the necessary Cloud KMS CryptoKey Decrypter role (roles/cloudkms.cryptoKeyDecrypter) on each key.

Create a Dataplex Lake for each data domain (e.g., 'sales', 'marketing', 'operations'). Within each lake, create zones that represent data lifecycle stages: 'raw' zone for ingested data, 'curated' zone for cleaned and validated data, and 'shared' zone for data products exposed to other teams. Assign Dataplex Lake IAM roles at the lake level for domain team ownership, and use Dataplex Asset associations to link Cloud Storage buckets and BigQuery datasets to the appropriate zones. Use Dataplex Data Policies to enforce access control rules and data quality constraints. Enable Data Catalog integration for unified metadata discovery across all lakes. This structure supports the data mesh principles of domain ownership, data as a product, and self-serve data infrastructure.

Use a partitioned and clustered table design. Partition the table by a time-based column (e.g., ingestion_date or event_timestamp truncated to day) to enable partition pruning. Cluster the table by device_id to optimize filtering on device-specific queries. Use the PARTITION BY clause with TIMESTAMP_TRUNC(event_timestamp, DAY) and CLUSTER BY device_id. For cost optimization, set a partition expiration time to automatically delete old partitions instead of running DELETE queries. Consider using a table decoration approach with _PARTITIONDATE pseudo-column in queries. For high-frequency aggregations, create materialized views that pre-compute common metrics per device per time window. This design minimizes both query cost (via partition pruning) and latency (via clustering).

The most common causes are: (1) The file format is not supported — Dataplex requires files to have recognized extensions (.csv, .json, .avro, .parquet, etc.) or to be explicitly registered with a data type. (2) The Cloud Storage bucket is not properly associated as an asset to the Dataplex zone — verify the asset association in the Dataplex console. (3) The files are in a nested folder structure that the discovery rule doesn't cover — check the asset's discovery filter pattern. (4) IAM permissions — the Dataplex service account (service-project-number@dataplex-data-resource.iam.gserviceaccount.com) needs storage.objects.get permissions on the bucket. (5) For CSV files, ensure the delimiter is properly configured in the data type specification. Fix by verifying asset associations, checking file extensions, and reviewing the discovery rule configuration.

The Dataflow service agent ([email protected]) in the service project needs the following roles: Storage Object Viewer (roles/storage.objectViewer) on the host project's Cloud Storage buckets, BigQuery Data Editor (roles/bigquery.dataEditor) or BigQuery Job User (roles/bigquery.jobUser) on the host project, and Compute Network User (roles/compute.networkUser) on the Shared VPC subnetworks. On the host project's VPC, grant the service project's Compute Engine service agent ([email protected]) the Network User role. Additionally, configure the Dataflow job with --serviceAccount and --network parameters to ensure workers use the correct service account and attach to the Shared VPC. Use VPC Service Controls to define perimeters that restrict data exfiltration between the host and service projects.

Use a combination of BigQuery column-level security with row-level access policies and Data Catalog tagging. First, apply Data Catalog tags to classify columns containing PII (Personally Identifiable Information). Then, use BigQuery's row-level access control (RLA) with IAM conditions to restrict who can see actual data values. For searchability without exposing raw data, create a separate metadata table that stores column names, data types, tag classifications, and sample statistics (e.g., value count, distinct count, format patterns) without actual PII values. Use BigQuery's data de-identification features (hashing, tokenization) to create anonymized versions of sensitive columns that can be used for join operations and analytics while preventing reverse engineering. Grant users access to the metadata table and the de-identified columns only.

Implement a cross-region BigQuery replication strategy using BigQuery's native export capabilities combined with Cloud Storage cross-region replication. Schedule regular BigQuery table exports (using bq extract or Dataflow) to a Cloud Storage bucket in a secondary region (e.g., if primary is us-central1, replicate to europe-west1). Use Cloud Storage's cross-region replication feature to automatically copy new objects to the backup region. Set up a monitoring job that checks BigQuery query success rates and triggers recovery procedures if the primary region becomes unavailable. For RTO under 4 hours, maintain hourly exports of critical tables and store them with a lifecycle policy that transitions to Coldline storage after 7 days to reduce costs. Document and regularly test the recovery runbook including IAM setup, reservation creation, and data validation procedures in the secondary region.

Use a sliding (hopping) window of 5 minutes with a 1-minute advance interval to compute rolling averages. Set allowedLateness to 3 minutes to accommodate out-of-order events. Configure the trigger with a fixed delay of 1 minute after the window end, combined with accumulation mode set to ACCUMULATE_AND_FIRE. This ensures that: (1) Results are emitted every minute for each sliding window position, (2) Late data arriving within 3 minutes of the window end updates the results, (3) The pipeline maintains state for all active windows. Write the final aggregated results to BigQuery using batch writes with the withTriggeringFrequency(1 minute) parameter. Monitor the late data rate metric to verify that the 3-minute allowedLateness covers the majority of late events.

Use Dataproc as the primary migration target since it supports running Apache Spark jobs with minimal code changes. First, migrate HDFS data to Cloud Storage using Storage Transfer Service or gsutil, organizing the data in a Hadoop-compatible path structure (e.g., gs://bucket/hdfs/path). Update the HDFS paths in your Spark code to point to Cloud Storage (gs://) instead of HDFS. Use Dataproc's Cloud Storage connector which provides Hadoop-compatible file system access to Cloud Storage. For IAM, configure the Dataproc service account with Storage Object Viewer and BigQuery roles as needed. Enable Dataproc's autoscaling feature to optimize costs. For Spark jobs that use Hadoop configuration, set the fs.gs.impl property to com.google.cloud.hadoop.fs.gcs.GoogleHadoopFileSystem. If jobs use HBase, consider migrating to Cloud Bigtable with the HBase emulator or native Bigtable client.

Implement a tiered storage strategy using BigQuery's automatic storage tiering combined with partition expiration. First, ensure your tables are partitioned by a time-based column (e.g., event_timestamp truncated to DAY). For the last 30 days of hot data, keep it in the default storage tier for fast query performance. For data older than 30 days, use the partition expiration feature to automatically delete partitions that exceed a certain age, or alternatively, export old partitions to Cloud Storage Nearline or Coldline storage and delete them from BigQuery. Set a partition expiration of 90 days as a safety net. Additionally, use table clustering on frequently filtered columns to reduce scan sizes. Consider using materialized views for the most common aggregations on recent data to further reduce query costs. This approach can reduce storage costs by 60-80% while maintaining fast access to recent data.

Configure the Pub/Sub subscription with the following settings: (1) Set message ordering to true to preserve order within the same message key (partition key). (2) Set the acknowledgment deadline to 600 seconds (10 minutes) to allow long-running processing. (3) Configure the dead-letter topic and dead-letter topic attribute for failed messages. (4) Set maxDeliveryAttempts to 5 for retry logic. (5) Enable exactly-once processing semantics by using at-least-once delivery with idempotent processing in your subscriber — implement a deduplication mechanism using message IDs or a unique processing key stored in BigQuery or Cloud SQL. (6) Use a push subscription with an endpoint that returns HTTP 200 only after successful processing, or a pull subscription with explicit acknowledge() calls. Monitor the dead-letter queue and set up alerting for subscription lag to ensure timely processing.

Enable Cloud Storage cross-region replication to automatically replicate objects to a bucket in a secondary region within 15 minutes. Configure the replication rule with the appropriate filters (prefix, tags) to target only the critical datasets. Set up versioning on both the source and destination buckets to protect against accidental deletions and enable point-in-time recovery. For the 15-minute RPO, ensure that replication is configured at the bucket level (not object-level) and that objects are not too large (individual objects over 5GB may take longer to replicate). Additionally, implement a monitoring solution using Cloud Monitoring to track replication lag and trigger alerts if replication exceeds the 15-minute threshold. For disaster recovery testing, periodically copy a sample of replicated objects to verify data integrity in the secondary region.

Implement a soft-delete pattern using a deletion timestamp column (deleted_at) in all tables that contain PII. When a deletion request arrives, update the deleted_at column with the current timestamp instead of physically deleting rows. Modify all queries to filter out records where deleted_at is not null. For materialized views, create a base view that excludes soft-deleted records and have materialized views query from this base view rather than the raw table. To ensure the 72-hour SLA, set up a Cloud Scheduler job that periodically scans for and processes deletion requests. Additionally, use BigQuery's time-travel feature (SYSTEM_TIME AS OF) to verify deletions were applied correctly. For complete GDPR compliance, also implement a data retention policy that automatically expires and deletes records older than the retention period.