Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
Your organization is migrating a 2 TB PostgreSQL database from an on-premises data center to Cloud Spanner to support global horizontal scaling. Your migration plan must address several key requirements:
Which tool combination should you select for schema conversion and data migration?
pg_dump is a native PostgreSQL utility for exporting database schemas and data into dump files, while the CSV to Spanner Dataflow template is a prebuilt batch pipeline that reads CSV files from Cloud Storage and loads them into Cloud Spanner.
This approach fails both the automated schema conversion and minimal-downtime live migration requirements. The CSV to Spanner Dataflow template does not automatically convert schemas, detect hotspot risks, or refactor triggers and stored procedures—it requires manual Spanner schema creation beforehand and handles only basic data type conversion. Moreover, exporting a 2 TB production database via pg_dump to static CSV files only captures a point-in-time snapshot and provides no mechanism to stream ongoing CDC writes during migration, resulting in prolonged downtime or data loss.
pg_dump with SMT in proof-of-concept mode is intended for smaller sample datasets (under 100 GB), whereas production 2 TB migrations require direct source connectivity.You already had a manually designed Cloud Spanner schema and needed a scalable, one-time batch load of static historical CSV exports from Cloud Storage where downtime and ongoing write synchronization were not concerns.
When migrating semi-structured or flat-file data that requires complex SQL-based transformations before loading into Spanner, BigQuery reverse ETL to Spanner is preferred over basic CSV to Spanner templates.
Database Migration Service (DMS) is a serverless migration service on Google Cloud designed to move relational operational databases using an initial snapshot followed by continuous change data capture (CDC) replication.
DMS does not support Cloud Spanner as a migration destination. For PostgreSQL sources, DMS only migrates workloads into Cloud SQL for PostgreSQL or AlloyDB for PostgreSQL. In addition, DMS does not perform the Spanner-specific architectural schema refactoring required for a globally distributed database—such as converting sequential integer primary keys into UUIDs to avoid write hotspots or designing interleaved tables.
You were migrating your on-premises PostgreSQL database to Cloud SQL for PostgreSQL or AlloyDB for PostgreSQL, where PostgreSQL engine compatibility is preserved and minimal-downtime CDC replication is needed.
Because Cloud Spanner uses a fundamentally different distributed storage and transaction architecture than traditional PostgreSQL engines, you must use Spanner migration tool (SMT) for schema conversion and Datastream with Dataflow for live CDC migration.
This combination pairs Spanner migration tool (SMT)—an open-source assessment, schema conversion, and migration utility—with a live data migration pipeline powered by Datastream (a serverless change data capture and replication service) and Dataflow (a fully managed Apache Beam data processing engine).
Unlike Database Migration Service (which targets Cloud SQL and AlloyDB) or BigQuery Data Transfer Service (which targets analytical warehouses), SMT with Datastream and Dataflow is purpose-built for heterogeneous, low-downtime PostgreSQL-to-Spanner production migrations.
BigQuery Data Transfer Service (DTS) is a fully managed service that automates scheduled batch data ingestion from cloud storage, SaaS applications, and third-party data warehouses (such as Amazon Redshift, Teradata, and Snowflake) into BigQuery. Paired with the batch SQL translator, it helps teams transition analytical workloads and SQL dialects into GoogleSQL.
Although BigQuery Data Transfer Service includes a PostgreSQL connector, it is strictly an analytical ingestion service that loads data into BigQuery, not into Cloud Spanner. Furthermore, batch SQL translation converts analytical warehouse queries into GoogleSQL for BigQuery rather than refactoring operational OLTP schemas, triggers, or stored procedures for Cloud Spanner.
Your goal was to offload or transition analytical reporting tables from PostgreSQL or an enterprise data warehouse like Teradata or Amazon Redshift into BigQuery on a recurring schedule.
Choosing between analytical and operational migration tools depends on the target engine: BigQuery DTS serves BigQuery data warehousing workloads, whereas SMT, Datastream, and Dataflow serve operational Cloud Spanner migrations.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated