Google PDE: Data Ingestion, Integration and Migration — Study Guide

By Laurynas Brazys · 21 August 2026 · #google #PDE #certification #exam-prep #study-guide

Part of the Google Professional Data Engineer — Study Guide. Practice with verified answers in the Google exam hub, or take timed practice tests on ExamRoll.io.

Overview

Data ingestion, integration, and migration in Google Cloud span repeatable patterns, managed services, and operational controls that turn diverse source systems into reliable, queryable datasets. Effective designs separate transport from transformation, decouple producers and consumers, and favor idempotent, checkpointed pipelines with clear lineage and verification. This section covers ingestion patterns, Google Cloud services for movement and CDC, schema and data-quality controls, connectivity and hybrid integration, and cutover strategies, with design trade-offs and failure modes called out throughout.

Ingestion Patterns and Workloads

• Batch ingestion: Periodic pulls or file drops at defined intervals. Good for predictable cost and backfills. Failure mode: large, infrequent batches cause resource spikes, long catch-up windows, and missed SLAs. Mitigation: right-size batch windows, shard by time or key, and use parallelism.
• Bulk load: One-time or large-scale loads (e.g., initial historical backfill). Prefer columnar or self-describing formats (Parquet, Avro) and load directly to analytical storage (BigQuery) or staging in Cloud Storage. Trade-off: external-table querying avoids load steps but shifts cost to query-time scanning.
• Incremental load: Periodic delta loads via timestamps or high-water marks. Requires robust deduplication and idempotent upserts. Failure mode: clock skew or late-arriving records. Use server-side commit timestamps and watermarking.
• Change data capture (CDC): Continuous replication of inserts, updates, and deletes from operational databases. Best for near-real-time analytics and low-downtime migrations. Trade-offs:
  • Ordering: Most CDC tools preserve order within transactions and typically within a shard, but do not guarantee global cross-shard order. Use transaction commit timestamps and primary keys to reconstruct sequence.
  • Delivery semantics: At-least-once is typical; build idempotent sinks or deduplicate using unique change IDs.
  • Snapshot + CDC: Begin with a consistent snapshot, then apply changes from a precise log sequence to reach parity without downtime.

Relational, SaaS, on-premises, and file sources:

• Relational sources: Use native CDC or timestamp columns. For bulk, export to Avro/Parquet and stage in Cloud Storage.
• SaaS sources: Prefer vendor APIs with incremental tokens; integrate via managed connectors (e.g., in Data Fusion). Throttle for rate limits and handle schema drift.
• On-prem sources: Choose from agent-based transfer, VPN/Interconnect + Private Google Access, or offline seeding with Transfer Appliance.
• File ingestion: For many small files, bundle (e.g., tar) to reduce RPC overhead. Use gsutil -m or parallelized clients; compose or transform to larger, columnar files for analytics.

Google Cloud Services for Ingestion, Integration, and Migration

• Datastream (serverless CDC): Captures changes from MySQL, PostgreSQL, and Oracle into Cloud Storage, BigQuery (via templates), or Pub/Sub. It preserves transaction boundaries and commit metadata; global ordering is not guaranteed. Apply downstream ordering by key and commit timestamp. Expect at-least-once delivery; design idempotent consumers (e.g., BigQuery MERGE with change IDs).
• Database Migration Service (DMS): For minimal-downtime database migrations using native replication. DMS creates a consistent snapshot, then continuously replicates changes using GTID/LSN/SCN. It is purpose-built for lift-and-shift, not arbitrary transformation. For analytics, augment DMS with Dataflow or Data Fusion if needed.
• Cloud Data Fusion: A managed integration service with connectors to relational, SaaS, files, and messaging systems. Build pipelines with transformation stages (joins, aggregations, format conversions, custom Wrangler recipes) and capture lineage across sources and fields. Operationally, it schedules, retries, and emits metrics. Use Data Fusion for no/low-code ELT/ETL and to centralize connector management.
• Storage Transfer Service (STS): Managed, scheduled transfers from AWS S3, Azure Blob, on-prem (using agents), SFTP, and URL lists to Cloud Storage. Supports manifests, incremental sync, bandwidth control, and checksummed integrity. Failure modes include small-file inefficiency and API throttling; mitigate with batching and adjustable concurrency.
• Transfer Appliance: Offline, encrypted appliance for multi-terabyte to petabyte-scale initial seeding when network bandwidth is limited or data is too sensitive for prolonged transit. Chain-of-custody and encryption are built in. After seeding, follow with STS or CDC for deltas.
• Cloud Pub/Sub + Dataflow: Pub/Sub decouples producers and consumers for streaming or micro-batch patterns. Dataflow offers autoscaled, stateful stream/batch processing with checkpointing and watermarking. Use the BigQuery Storage Write API for low-latency streaming with exactly-once guarantees per default stream; otherwise rely on insertId deduplication semantics.

For Hadoop-to-Dataproc migrations, minimize Persistent Disk by storing data in Cloud Storage with the GCS connector and use ephemeral or autoscaling clusters. This avoids large block storage costs while preserving HDFS-compatible semantics for processing.

Schema, Validation, and Data Quality at the Boundary

• Schema mapping and type conversion: Standardize to strongly typed schemas early. Avro or Parquet preserve schema and evolve cleanly. In BigQuery, favor partitioned and clustered tables to reduce scan cost. Example: create a partitioned table for daily analysis CREATE TABLE dataset.tracking_table ( event_ts TIMESTAMP, device_id STRING, payload STRING ) PARTITION BY DATE(event_ts) CLUSTER BY device_id;
• Malformed-record handling: Route rejects to a dead-letter queue (Pub/Sub) or quarantine bucket in Cloud Storage. Use side outputs in Dataflow or error collectors in Data Fusion. Log parse errors with sample payloads and schema versions for triage.
• Validation: Perform boundary checks before persistence:
  • Structural: schema conformance, required fields, data types, enum domains.
  • Referential: foreign key existence via cached dimension lookups.
  • Reasonableness: ranges for timestamps, geofences, non-negative amounts.
  • Uniqueness: primary key or composite key collisions.
• Idempotent loading: Use deterministic keys and upsert operations. In BigQuery, implement MERGE with a natural or surrogate change key. Example: MERGE dataset.orders T USING dataset.orders_stage S ON T.order_id = S.order_id WHEN MATCHED THEN UPDATE SET amount = S.amount, status = S.status WHEN NOT MATCHED THEN INSERT (order_id, amount, status) VALUES (S.order_id, S.amount, S.status);
• Watermarking and lateness: In streaming pipelines, configure event-time watermarks and allowed lateness to balance completeness and latency. Late data routes to corrective paths or triggers backfills.
• Reconciliation: Track row counts and checksums per partition/window from source to sink. Capture CDC log positions (LSN/SCN) and commit timestamps; store in a control table to prove continuity and to identify gaps.

Connectivity, Reliability, and Operations

• Network connectivity and private access:

  • Hybrid: Use Cloud VPN or Dedicated/Partner Interconnect for private connectivity. Enable Private Google Access or Private Service Connect for private access to Google APIs like Cloud Storage.
  • Security: Use service accounts for workload identity, least-privilege IAM, VPC Service Controls for data exfiltration prevention, and CMEK where required.
  • Throughput: Scale parallelism at the client, but ultimately bandwidth governs throughput. For massive transfers, prefer Transfer Appliance for initial bulk, then STS or CDC for incremental updates.

• Checkpoints and backpressure: Dataflow manages checkpoints and autoscaling; design sinks that can absorb bursts (buffer to Cloud Storage, batch writes to BigQuery). For Pub/Sub, tune flow control and ack deadlines to prevent message redelivery storms.

• Ordering and consistency with CDC:

  • Datastream preserves intra-transaction order and emits commit metadata; consumers reconstruct per-key order using commit timestamps. Expect at-least-once; build idempotency.
  • DMS ensures database consistency across snapshot and replication cutover using native logs. Use read replicas or dual-write strategies for phased cutover.

• File strategy for analytics: For large, multi-engine access, store canonical data in Cloud Storage and, where cost-effective, expose permanent external tables for ad hoc queries. For production analytics, load to partitioned BigQuery tables to minimize per-query scan cost.

• Small-file optimization: Bundle small files (e.g., ~1,000 per tar) before transfer, then expand in cloud. Use parallel gsutil and lifecycle rules to tier and expire staging artifacts.

• Operational pitfalls and mitigations:

  • Schema drift from SaaS: enable schema evolution in Data Fusion and enforce compatibility. Alert on breaking changes.
  • Timezone and encoding: normalize to UTC, UTF-8 at ingress.
  • Gaps in CDC: monitor source log retention; alert when replica lag approaches retention limits.
  • Quotas: BigQuery streaming insert, API rate limits; batch when near limits.

Cutover, Backfill, and Verification

• Cutover planning:
  • Big bang: short freeze, single switch. Lowest operational complexity; highest risk if rollback is needed.
  • Phased or blue/green: dual-run with mirrored writes, progressive traffic shifting, and shadow reads. Higher cost; safer rollback.
• Backfill:
  • Perform an initial bulk load (Transfer Appliance or STS) using Avro/Parquet to preserve schema. Partition and cluster during load to avoid rework.
  • Start CDC at a known log position concurrent with snapshot to capture deltas during bulk transfer. Reconcile at a common watermark before opening to production.
• Rollback:
  • Maintain read-only legacy during verification. For dual-write scenarios, gate writes behind a feature flag to revert quickly. Keep a consistent checkpoint to replay or unwind CDC changes if required.
• Migration verification:
  • Structural: row counts and per-partition checksums match; schema and constraints equivalent.
  • Temporal: no gaps from snapshot boundary to cutover; CDC positions continuous.
  • Business parity: compare aggregates and KPIs over windows; run acceptance queries.
  • Performance: validate ingestion throughput, query latency, and cost against budgets.

Practical Problem Scenario

Northstar Retail must consolidate a global mix of on-prem Oracle and MySQL transactional systems, SaaS CRM events, and daily CSV drops into Google Cloud to power near-real-time analytics and machine learning. They also need to migrate a legacy Hadoop cluster without incurring high block storage expense, and achieve a zero-to-low downtime cutover.

1. Establish secure hybrid connectivity

• Use Partner Interconnect for primary bandwidth and Cloud VPN as fallback. Enable Private Google Access so on-prem workloads can access Cloud Storage and Pub/Sub privately. Rationale: Private paths minimize egress exposure and latency, and Private Google Access avoids public IP requirements while meeting security policy.

2. Seed historical data efficiently

• For 800 TB of historical HDFS data, copy to Cloud Storage using Transfer Appliance (initial bulk). Post-seed, run Storage Transfer Service daily from the on-prem NFS export to pick up changes until cutover. Rationale: Transfer Appliance avoids prolonged network saturation; STS provides scheduled, checksummed incremental sync. Storing in Cloud Storage with the GCS connector allows Dataproc processing without 50 TB Persistent Disk per node.

3. Migrate operational databases with CDC

• Use DMS to migrate MySQL and PostgreSQL with minimal downtime. For Oracle-to-analytics CDC, use Datastream to Cloud Storage landing, then a Google-provided Dataflow template to load into BigQuery. Rationale: DMS leverages native replication for reliable snapshot + continuous sync; Datastream provides serverless CDC with commit metadata, while the Dataflow template ensures ordered, idempotent BigQuery writes.

4. Ingest SaaS and file-based feeds

• Build Cloud Data Fusion pipelines using SaaS connectors for CRM events with incremental tokens, and a file pipeline to ingest daily CSVs from a vendor SFTP via STS. Normalize to Avro in a curated Cloud Storage bucket, then load partitioned BigQuery tables. Rationale: Data Fusion centralizes connectors, transformation, and lineage. Standardizing on Avro preserves schema and eases evolution; partitioned BigQuery tables reduce query cost.

5. Stream real-time events

• Publish web and store events to Pub/Sub. Process with Dataflow for parsing, validation, enrichment, and watermarking; write to BigQuery via the Storage Write API and archive raw Avro to Cloud Storage. Rationale: Pub/Sub decouples producers/consumers; Dataflow provides autoscaling, stateful processing, checkpoints, and late data handling; dual-write ensures both low-latency analytics and durable raw retention.

6. Enforce boundary data quality and schema controls

• Implement schema registry and validation in Dataflow/Data Fusion. Route malformed records to a GCS quarantine bucket and Pub/Sub dead-letter topic. Apply domain checks (e.g., currency codes, UTC timestamps) and deduplicate using composite keys. Rationale: Early rejection and quarantine prevent bad data from propagating; idempotency and dedup guard against at-least-once delivery from CDC and streaming sources.

7. Optimize analytics storage and access

• Load curated datasets into partitioned and clustered BigQuery tables. Expose raw archives as permanent external tables for low-frequency exploration. For OLTP workloads that remain transactional, keep Cloud SQL with read replicas. Rationale: Partitioning and clustering minimize scan cost; external tables avoid unnecessary loads for occasional access; Cloud SQL preserves ACID semantics for transactional apps.

8. Plan cutover, backfill, and rollback

• Execute snapshot + CDC for each RDBMS; reach a reconciliation point where row counts and checksums match. Run blue/green with dual-writes for 48 hours, shifting reads to BigQuery gradually. Maintain a feature flag to revert writes if discrepancies are detected. Rationale: Blue/green reduces risk; verification at a known watermark ensures completeness; flags enable rapid rollback.

9. Verification and observability

• Build control tables capturing source LSN/SCN, commit timestamps, row counts, and checksums per partition. Monitor Datastream lag, DMS replication state, Dataflow watermarks, Pub/Sub backlog, STS job status, and BigQuery streaming insert metrics. Rationale: End-to-end lineage and quantitative controls provide auditable proof of correctness and timely alerting on gaps or lag.

By separating landing, curation, and serving layers; using Cloud Storage as durable, low-cost staging and archive; leveraging DMS/Datastream for CDC with idempotent consumers; and enforcing schema and quality at ingress, Northstar Retail achieves secure, scalable ingestion and a low-risk, verifiable migration with predictable cost.

← Spark · All domains · Workflow Orchestration and Pipeline Automation →

Practice these questions → · Timed practice on ExamRoll.io →

Browse Google →