Configure export and import data for databases

Engine-specific import and export is the practice of matching each Google Cloud database to its supported utility and file format so data moves without losing types or structure. Cloud Spanner (opens in a new tab) uses Dataflow jobs that read from and write to Cloud Storage (opens in a new tab) with Avro or CSV (opens in a new tab) files plus JSON manifests. Firestore (opens in a new tab) in Datastore mode uses managed export and import operations that also stage files in Cloud Storage. Choosing the wrong format or omitting the manifest breaks fidelity on import.

1.1Spanner Avro Export Format and Workflow

A Cloud Spanner Avro export is a Dataflow job that copies table data from the Spanner database to a folder in a Cloud Storage bucket. Data flows from Spanner tables through Dataflow workers to Avro files in the destination folder, so the job depends on both Spanner compute capacity and Dataflow access to the bucket. The export folder created by the job holds everything needed for a later import or inspection.

The export folder contains:

• spanner-export.json that describes the overall export
• TableName-manifest.json for each table that lists its data files
• TableName.avro-#####-of-##### data files where the first number is the file index starting at zero and the second is the total file count, such as Songs.avro-00001-of-00002
• ChangeStreamName-manifest.json and ChangeStreamName.avro-00000-of-00001 for each change stream, where the Avro file holds only the schema with empty data

To import a Spanner Avro export, data flows in reverse from the Cloud Storage folder through a Dataflow import job into a new Spanner database. The operator selects the export folder itself in the console, enters a new database name that does not already exist in the instance, and chooses the dialect for the new database as GoogleSQL or PostgreSQL. The import job creates the database during the run, and the new database can optionally be protected with a customer-managed encryption key.

1.2Spanner CSV Export and Import With Manifest Files

A Cloud Spanner CSV export and import uses CSV data files plus a JSON manifest file stored together in a Cloud Storage bucket. Data flows from Spanner through Dataflow to CSV files on export, and from CSV files through Dataflow to Spanner on import. Because CSV does not carry full Spanner type information on its own, the JSON manifest specifies which destination tables are populated by which CSV files, and the target tables must already exist with the correct schema.

Importing CSV files into Spanner follows an ordered workflow:

1. Export source data to CSV: Export data from the non-Spanner database to CSV files and upload the files to a Cloud Storage bucket if they were not written there directly.
2. Create JSON manifest: Create a JSON manifest file in the same bucket that maps each Spanner destination table to its CSV files.
3. Create Spanner schema: Create the target tables in the Spanner database with column types that match the CSV content.
4. Run Dataflow import: Run a Dataflow import job with gcloud that reads the manifest and CSV files from Cloud Storage and writes rows into Spanner.

This order matters because the Dataflow job depends on both the manifest and the pre-created schema. If the manifest is missing or the table definition does not match the file columns, the import cannot correctly assign data to tables and types.

1.3Importing Non-Spanner Avro Files Into Spanner

Importing Avro files from a non-Spanner database into Cloud Spanner preserves more type fidelity than CSV because Avro carries column types. Data flows from Avro files in Cloud Storage through a Dataflow job into pre-created Spanner tables, so the workflow depends on creating the Spanner schema before running the import. The operator must also create a spanner-export.json file in the bucket that points to the destination tables and their Avro files, then run the Dataflow import with the gcloud CLI.

Avro source columns must use supported types, and each type maps to a specific Spanner column type. A column of type BYTES with logicalType=decimal, precision=38, and scale=9 imports as Spanner NUMERIC; without those exact specifications the same field imports as Spanner BYTES. A LONG or STRING that stores a timestamp value can import as Spanner TIMESTAMP when the target column is defined that way. The table below shows the supported mappings.

No special metadata or file naming convention is required for non-Spanner Avro source files. Any Avro primitive type and the array complex type can be used on export, as long as each column fits one of the supported Avro types in the table.

1.4Firestore Managed Export, Import, and BigQuery Compatibility

Firestore managed export and import operations authorize as the Firestore service agent and stage data in a Cloud Storage bucket. The service agent is service-PROJECT_NUMBER@gcp-sa-firestore.iam.gserviceaccount.com, which replaces the legacy App Engine service account PROJECT_ID@appspot.gserviceaccount.com shared by multiple services. If the Cloud Storage bucket is in the same project as the Firestore database, the service agent can access the bucket by default; if the bucket is in another project, the operator must grant the service agent access to that bucket.

An export of all entities without an entity filter cannot be used for selective restore or for BigQuery loading. Data exported without one or more kind names in the entity filter cannot be loaded into BigQuery, and a subset of kinds and namespaces cannot be imported from an export of all entities. To keep BigQuery compatibility, the export request must include an entity filter with kind names, and each kind generates a BigQuery table schema where each unique property becomes a column.

BigQuery enforces a limit of 10,000 columns per table, which affects wide Firestore kinds. If a kind schema exceeds 10,000 columns, the export tries to stay under the limit by treating embedded entities as blobs, which allows loading but prevents querying properties inside those embedded entities. If the column count still exceeds 10,000, no BigQuery schema is generated for that kind and its data cannot be loaded into BigQuery.

1.5Permissions and Quotas for Dataflow Import and Export Jobs

Dataflow import and export jobs for Cloud Spanner require specific Identity and Access Management roles on the Dataflow worker service account. The worker identity must be able to read Spanner, run Dataflow, and read and write Cloud Storage objects, with an extra admin role for imports that create databases. Without these grants, data cannot flow between Spanner and the Cloud Storage staging bucket.

The required roles on the Dataflow worker service account are:

• Cloud Spanner Viewer (roles/spanner.viewer)
• Dataflow Worker (roles/dataflow.worker)
• Storage Admin (roles/storage.admin)
• Spanner Database Reader (roles/spanner.databaseReader)
• Spanner Database Admin (roles/spanner.databaseAdmin), which is required only for import jobs

Compute Engine quotas bound how many resources Dataflow can use, and these quotas represent the maximum allowed rather than guaranteed usage. Recommended starting values are 200 CPUs, 200 in-use IP addresses, and 50 TB of standard persistent disk. Dataflow provides autoscaling so the job pays only for resources actually used, and the Dataflow UI shows a warning icon if the job could use more resources, though the job should still finish.

Optimizing performance and validating data integrity means moving large volumes quickly without losing accuracy. BigQuery (opens in a new tab) uses Cloud Storage (opens in a new tab) as the staging area for load and export operations, efficient file formats and incremental transfers to reduce movement size, and partitioning and clustering to keep queries fast after the load. After import, schema alignment and data completeness are verified by comparing types, structures, counts, and query results between source and target.

2.1Stage Large Movements Through Cloud Storage

Cloud Storage is the staging layer that data flows through when moving data into or out of BigQuery. Data flows from the source system to a Cloud Storage bucket and then from the bucket into BigQuery, and exports flow in the reverse direction from BigQuery to the bucket. For a Snowflake (opens in a new tab) to BigQuery movement, the export is prepared with a named file format set to PARQUET, a storage integration that permits the bucket locations, and an external stage that references the integration and file format. This staged approach lets large datasets land in durable object storage first, so the load into BigQuery can read from files rather than from a live source connection.

2.2Reduce Transfer Volume and Preserve Query Performance

The BigQuery Data Transfer Service with the Snowflake connector reduces movement size by supporting incremental transfer. An incremental transfer moves only data that changed since the last transfer instead of loading the entire dataset each time, which lowers transfer time and cost for large tables. The connector can also automatically detect table schema and data types during transfer, so schema discovery does not require a separate manual step.

Physical design preserves performance after the data arrives. Both BigQuery and Snowflake use a massively parallel processing (MPP) architecture where queries are distributed across multiple servers, and BigQuery adds slots as the unit of computational capacity for query execution. BigQuery supports explicit partitioning and clustering to control how data is distributed and organized, which can improve runtime performance and cost. The SQL translation service automatically handles table clustering translation when migrating data definition language (DDL) and can be configured to customize partitioning and clustering.

2.3Verify Schema Alignment After Import

Schema alignment verification confirms that tables, data types, and security controls mean the same thing in the target as in the source. Translated queries must be reviewed to verify that data types and table structures were correctly handled, and views and user-defined functions (UDFs) must be verified so their controls become authorized views and authorized UDFs in BigQuery. Row-level and column-level controls are rebuilt with BigQuery policy tags, authorized views, and related access controls mapped from the source roles through Identity and Access Management (IAM).

Firestore (opens in a new tab) and Datastore mode exports have strict schema rules for BigQuery loads:

• An export must include an entity filter with one or more kind names to be loadable into BigQuery.
• An export of all namespaces and all kinds cannot be loaded into BigQuery and cannot be used to import a subset of kinds and namespaces.
• BigQuery allows 10,000 columns per table, where each unique property in a kind becomes a column.
• If a kind exceeds 10,000 columns, the export treats embedded entities as blobs to stay under the limit, and properties inside those blobs cannot be queried.
• If the schema still exceeds 10,000 columns, no BigQuery schema is generated and the kind cannot be loaded.

2.4Confirm Data Completeness and Query Correctness

Data completeness validation confirms that the imported data is whole and returns the same answers as the source. A repeatable validation sequence moves from test design to comparison to correction:

1. Create test cases: Build a wide range of test cases with different scenarios and data.
2. Run on BigQuery: Execute the translated queries and loads against BigQuery tables.
3. Compare results: Compare BigQuery results to the original Snowflake results for row counts, values, and structures.
4. Analyze and fix: Investigate any differences and correct the converted queries, types, or table definitions.

This post-transfer work is part of normal migration planning. The timeline accounts for validating, auditing, and testing tasks after data transfer takes place, and duration depends on database size, business logic complexity, application usage, and team availability. The technical design document records business requirements, recovery time objective (RTO), recovery point objective (RPO), migration details, effort estimates, and migration validation recommendations, so validation is planned before the first load runs.

Secure access for data transfer means that both the person who starts an export or import and the service account that moves the bytes have only the permissions they need. In Google Cloud, Identity and Access Management (IAM) controls who can act, while Cloud Storage bucket permissions control where the service account can read and write. A secure pipeline therefore connects three parts in order: the user identity to the database service, the database service identity to the bucket, and the bucket policy back to both identities.

3.1Identities That Move Data Between Databases and Storage

Every database transfer uses two separate identities with different jobs. The user account is the Google Account that signs in to create connection profiles, upload files, and start export, import, or migration jobs. The service account is the Google-managed identity that actually reads and writes data, such as the Firestore (opens in a new tab) service agent service-PROJECT_NUMBER@gcp-sa-firestore.iam.gserviceaccount.com, the Database Migration Service (DMS) (opens in a new tab) service account service-PROJECT_NUMBER@gcp-sa-datamigration.iam.gserviceaccount.com, or the Google SecOps export service account. You can see which identity will run Firestore jobs on the Databases page by selecting a database, clicking Import/Export, and viewing the authorization account next to the Import/Export jobs run as label.

3.2IAM Roles That Control Who Can Start and View Transfers

IAM roles for users define who can create, cancel, and view data export jobs without granting broad Viewer, Editor, or Owner access to the whole project. For SecOps data exports (opens in a new tab) , the user needs data export permissions that are grouped into predefined roles for full or read-only management:

• chronicle.dataExports.fetchServiceAccountForDataExport to discover the export service account
• chronicle.dataExports.create to create export jobs
• chronicle.dataExports.cancel to cancel export jobs
• chronicle.dataExports.get to view a single job
• chronicle.dataExports.list to view job history

The Chronicle API Admin role grants full permission to create, update, cancel, and view export jobs with global access, while the Chronicle API Viewer role grants read-only access to job configurations and history. For DMS Oracle migrations, the user needs the Database Migration Admin role (roles/datamigration.admin) plus a destination role such as AlloyDB Admin (roles/alloydb.admin) or Cloud SQL Editor (roles/cloudsql.editor). When predefined roles grant too much access, create a custom IAM role with only the needed permissions and add the Chronicle API Restricted Data Access role with an IAM condition such as resource.name.endsWith("/dataAccessScopes/scope_test") to limit the user to specific data access scopes.

3.3Bucket Permissions That Let Service Agents Read and Write

Cloud Storage bucket permissions determine whether the service account can deliver exported files or read files for import. The destination bucket should be private to prevent unauthorized access and, for SecOps exports, created in the same region as the tenant. The SecOps service account then needs bucket-level roles so data flows from the SecOps service to the bucket, specifically Storage Object Admin (roles/storage.objectAdmin) to write objects and Legacy Bucket Reader (roles/storage.legacyBucketReader) to read bucket metadata.

Firestore follows a project-boundary rule that separates default access from explicit grants. Import and export workflows that stay within the same project do not require permission changes because the Firestore service agent can access buckets in the same project by default. Operations that use a bucket in another project fail unless you grant that bucket access to the source project's Firestore service agent. The Firestore Service Agent role grants both read and write permission on the bucket, so use a custom role when you need to grant only read for imports or only write for exports.

3.4Migrating Firestore Projects to the Dedicated Service Agent

Migration to the Firestore service agent moves Firestore import and export jobs from the older App Engine service account to the dedicated Firestore service agent. The recommended project-level migration localizes the effect to one Firestore project, while an organization-wide policy constraint enforces compliance across all projects but does not fix existing bucket permissions:

The project-level migration job (opens in a new tab) follows an ordered check, fix, and confirm workflow:

1. Check status: Open Import/Export, click Check Bucket Status, and review the list of recently used buckets that lack read and write permission for the service agent shown under Service agent to give access to.
2. Fix each bucket: For each bucket needed for future jobs, click Fix, add the Firestore service agent as a new principal, select Service Agents > Firestore Service Agent, and save.
3. Confirm migration: Click Migrate to Firestore Service Agent and confirm again if any checks still fail.

Migration cannot be undone, so complete all needed bucket fixes before the final confirmation and verify the new authorization account on the Import/Export page after the alert reports completion.

Key Facts

• Cloud Spanner Avro exports flow from Spanner tables through Dataflow workers to a Cloud Storage folder containing spanner-export.json, per-table manifests, and TableName.avro-#####-of-##### data files.
• Spanner CSV imports require a JSON manifest mapping tables to CSV files and pre-created target tables, while non-Spanner Avro imports preserve more type fidelity through explicit Avro-to-Spanner type mappings.
• A Firestore export without an entity filter containing kind names cannot be loaded into BigQuery, and BigQuery allows a maximum of 10,000 columns per table.
• Dataflow jobs for Spanner require Cloud Spanner Viewer, Dataflow Worker, Storage Admin, and Spanner Database Reader roles, plus Spanner Database Admin for imports.
• The Firestore service agent can access Cloud Storage buckets in the same project by default, but cross-project buckets require an explicit grant or the job fails.