Schedule a Snowflake transfer

The Snowflake connector provided by the BigQuery Data Transfer Service lets you schedule and manage automated transfer jobs to migrate data from Snowflake into BigQuery using public IP allowlists.

Overview

The Snowflake connector engages migration agents in the Google Kubernetes Engine and triggers a load operation from Snowflake to a staging area within the Cloud Storage bucket.

• For Amazon Web Services (AWS) or Azure or Google Cloud-hosted Snowflake accounts, the data is first staged in your Cloud Storage bucket, which is then transferred to BigQuery with the BigQuery Data Transfer Service.

The following diagram illustrates public data transfers from Snowflake accounts hosted on Amazon Web Services (AWS) or Azure or Google Cloud-hosted Snowflake accounts.

Limitations

Data transfers made using the Snowflake connector are subject to the following limitations:

• The Snowflake connector only supports transfers from tables within a single Snowflake database and schema. To transfer from tables with multiple Snowflake databases or schemas, you can set up each transfer job separately.
• The speed of loading data from Snowflake to your Amazon S3 bucket or Azure Blob Storage container or Cloud Storage bucket is limited by the Snowflake warehouse you have chosen for this transfer.
• BigQuery writes data from Snowflake to Cloud Storage as Parquet files. Parquet files don't support the TIMESTAMP_TZ and TIMESTAMP_LTZ data types. If your data contains these types, you can export it to Amazon S3 as CSV files and then import the CSV files into BigQuery. For more information, see Introduction to Amazon S3 data transfers.

Before you begin

The following sections describe the steps you must take to set up a Snowflake transfer.

Prepare your Google Cloud project

Create and configure your Google Cloud project for a Snowflake transfer with the following steps:

1. Create a Google Cloud project or select an existing project.

2. Verify that you have completed all actions required to enable the BigQuery Data Transfer Service.

3. Create a BigQuery dataset to store your data. You don't need to create any tables.

Get required IAM roles

To create and manage Snowflake data transfers, all required user and service accounts must have the roles that are described in the following table:

The following sections provide more details about the required roles and permissions for each identity.

Get user account permissions

To get the permissions that you need to create and manage transfer configurations and run jobs with a user account, ask your administrator to grant you the following IAM roles:

• Create and manage transfer configurations and run jobs: BigQuery User (roles/bigquery.user) on the project
• Manage and grant access to the destination dataset: BigQuery Data Owner (roles/bigquery.dataOwner) on the destination BigQuery dataset
• Create or update transfer configurations with a dedicated service account: Service Account User (roles/iam.serviceAccountUser) on the dedicated service account

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to create and manage transfer configurations and run jobs with a user account. To see the exact permissions that are required, expand the Required permissions section:

You might also be able to get these permissions with custom roles or other predefined roles.

Get dedicated service account permissions

To ensure that the dedicated service account has the necessary permissions to use a service account to read translation metadata, custom schema, or staging files during transfer execution, ask your administrator to grant the Storage Object Viewer (roles/storage.objectViewer) IAM role to the dedicated service account on the Cloud Storage staging bucket, and the bucket or buckets that contain the schema mapping files.

Get BigQuery Data Transfer Service service agent permissions

To ensure that BigQuery Data Transfer Service service agent has the necessary permissions to use a service agent to run the transfer, ask your administrator to grant the following IAM roles to BigQuery Data Transfer Service service agent:

• Read unloaded data files during transfer execution: Storage Object Viewer (roles/storage.objectViewer) on the Cloud Storage staging bucket
• Write transferred data into destination tables: BigQuery Data Editor (roles/bigquery.dataEditor) on the destination BigQuery dataset
• Authorize transfers that use a cross-project service account: Service Account Token Creator (roles/iam.serviceAccountTokenCreator) on the cross-project dedicated service account

If the dedicated service account is in the same project where the BigQuery Data Transfer Service is enabled, Google automatically grants the required iam.serviceAccounts.getAccessToken permission. You only need to grant the Service Account Token Creator role (roles/iam.serviceAccountTokenCreator) manually if the dedicated service account is in a different project. For more information, see Cross-project service account authorization.

Get Snowflake Google Cloud service account permissions

To ensure that Snowflake Google Cloud service account has the necessary permissions to write and manage unloaded data files in the staging bucket, ask your administrator to grant the following IAM roles to Snowflake Google Cloud service account on the Cloud Storage staging bucket:

• Storage Object User (roles/storage.objectUser)
• Storage Bucket Viewer (roles/storage.bucketViewer)

These predefined roles contain the permissions required to write and manage unloaded data files in the staging bucket. To see the exact permissions that are required, expand the Required permissions section:

Prepare staging bucket

To prepare for a Snowflake data transfer, create a staging bucket and then configure it to allow write access from Snowflake. You must stage the Snowflake data in a Cloud Storage bucket before you can load it into BigQuery. The staging bucket must not have the hierarchical namespace feature enabled.

To create and configure a staging bucket, do the following:

1. Create a Cloud Storage bucket.

2. Create and configure a Snowflake storage integration object to allow Snowflake to write data into the Cloud Storage bucket as an external stage.

   CREATE STORAGE INTEGRATION STORAGE_INTEGRATION_NAME
   TYPE = EXTERNAL_STAGE
   STORAGE_PROVIDER = 'GCS'
   ENABLED = TRUE
   STORAGE_ALLOWED_LOCATIONS = ('gcs://STAGING_BUCKET_NAME/');
   
   -- Describe integration to retrieve Snowflake Google Cloud service account
   DESCRIBE INTEGRATION STORAGE_INTEGRATION_NAME;

   Replace the following:

   • STORAGE_INTEGRATION_NAME: the name of the new storage integration object.
   • STAGING_BUCKET_NAME: the name of the Cloud Storage bucket you created.

3. When you run the DESCRIBE INTEGRATION command, the Cloud Storage service account is listed under the STORAGE_GCP_SERVICE_ACCOUNT field. Grant the Cloud Storage service account the following permissions on the Cloud Storage bucket:

   • storage.buckets.get
   • storage.objects.create
   • storage.objects.delete
   • storage.objects.get
   • storage.objects.list

4. To allow access to the staging bucket, grant the BigQuery Data Transfer Service service agent the roles/storage.objectViewer role:

   gcloud storage buckets add-iam-policy-binding gs://STAGING_BUCKET_NAME \
       --member=serviceAccount:service-PROJECT_NUMBER@gcp-sa-bigquerydatatransfer.iam.gserviceaccount.com \
       --role=roles/storage.objectViewer

For a Snowflake private data transfer, create the staging bucket and configure it for private connectivity.

If your Google Cloud organization enforces domain-restricted sharing with the constraints/iam.allowedPolicyMemberDomains constraint, granting IAM roles on the staging bucket to the STORAGE_GCP_SERVICE_ACCOUNT service account fails because that service account belongs to Snowflake's external organization.

You can't derive Snowflake's Google Workspace customer ID from the service account email address. Obtain the customer ID in one of the following ways:

• If your Snowflake account is hosted on Google Cloud, run SELECT SYSTEM$GET_SNOWFLAKE_PLATFORM_INFO(); in Snowflake with the ACCOUNTADMIN role, and find the snowflake-customer-directory-id value in the output.
• If your Snowflake account is hosted on AWS or Azure, contact Snowflake Support to request the Google Workspace customer ID for your STORAGE_GCP_SERVICE_ACCOUNT service account.

To add Snowflake's customer ID to the allowed list for your project, update the organization policy:

gcloud resource-manager org-policies allow \
    iam.allowedPolicyMemberDomains EXTERNAL_CUSTOMER_ID \
    --project=PROJECT_ID

Replace the following:

• EXTERNAL_CUSTOMER_ID: Snowflake's Google Workspace customer ID.
• PROJECT_ID: the ID of the Google Cloud project that contains your staging bucket.

For more information, see Restrict identities with domain-restricted sharing.

Create a Snowflake user with the required permissions

During a Snowflake transfer, the Snowflake connector connects to your Snowflake account using a JDBC connection. You must create a new Snowflake user with a custom role that only has the necessary privileges to perform the data transfer.

The following table lists the required roles and privileges for the custom role named MIGRATION_ROLE:

To create and configure the custom role and migration user, run the following SQL commands in your Snowflake account:

1. Create the custom role, MIGRATION_ROLE:

   CREATE ROLE MIGRATION_ROLE;

   Replace MIGRATION_ROLE with the name of the custom role that you are creating.

2. Grant the custom role the required permissions:

   GRANT USAGE ON WAREHOUSE WAREHOUSE_NAME TO ROLE MIGRATION_ROLE;
   GRANT USAGE ON DATABASE DATABASE_NAME TO ROLE MIGRATION_ROLE;
   GRANT USAGE ON SCHEMA DATABASE_NAME.SCHEMA_NAME TO ROLE MIGRATION_ROLE;
   GRANT SELECT ON ALL TABLES IN SCHEMA DATABASE_NAME.SCHEMA_NAME TO ROLE MIGRATION_ROLE;
   GRANT USAGE ON INTEGRATION STORAGE_INTEGRATION_OBJECT_NAME TO ROLE MIGRATION_ROLE;

   Replace the following:

   • WAREHOUSE_NAME: the name of your data warehouse.
   • DATABASE_NAME: the name of your Snowflake database.
   • SCHEMA_NAME: the name of your Snowflake schema.
   • STORAGE_INTEGRATION_OBJECT_NAME: the name of your Snowflake storage integration object.

3. Create the migration user as a service user:

   CREATE USER MIGRATION_USER TYPE = SERVICE;

   Replace MIGRATION_USER with the username for the migration user. Setting TYPE = SERVICE creates a service user that authenticates with a key pair and is exempt from interactive multi-factor authentication (MFA).

4. Assign the custom role to the migration user:

   GRANT ROLE MIGRATION_ROLE TO USER MIGRATION_USER;
   ALTER USER MIGRATION_USER SET DEFAULT_ROLE = MIGRATION_ROLE;

Generate key pair for authentication

Due to the deprecation of single-factor password sign-ins by Snowflake, we recommend that you use a key pair for authentication. Configure a key pair by doing the following:

1. Generate a 2048-bit PKCS#8 private key in PEM format. Choose whether to generate an unencrypted or an encrypted private key:

   • To generate an unencrypted private key, run the following command:

     openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

   • To generate an encrypted private key, run the following command and enter a passphrase when prompted:

     openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

2. Generate the corresponding public key:

   openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

3. Assign the public key, excluding the header, footer, and line breaks, to your Snowflake migration user:

   ALTER USER MIGRATION_USER SET RSA_PUBLIC_KEY='PUBLIC_KEY_STRING';

   Replace PUBLIC_KEY_STRING with the body of the generated public key in the rsa_key.pub file.

For more information, see Configuring key-pair authentication.

Allow unloading to inline external locations

The Snowflake connector unloads data to inline external locations during the transfer. If your Snowflake account restricts unloading to inline locations, the transfer fails.

To allow unloading to inline locations, run the following SQL command in your Snowflake account. You must have the ACCOUNTADMIN role in Snowflake to run this command.

ALTER ACCOUNT SET PREVENT_UNLOAD_TO_INLINE_URL = false;

Alternatively, you can use SET to allow unloading on the migration user with the following command:

ALTER USER MIGRATION_USER SET PREVENT_UNLOAD_TO_INLINE_URL = false;

Replace MIGRATION_USER with the username for the migration user.

Additional prerequisites for optional configurations

To configure optional features for your Snowflake transfer, such as private connectivity or incremental transfers, you must complete these prerequisites that are described in the following sections.

Add network policies

For public connectivity, the Snowflake account allows public connection with database credentials by default. However, you might have configured network rules or policies that could prevent the Snowflake connector from connecting to your account. In this case, you must add the necessary IP addresses to your allowlist. For more information, see Configure network policies for Snowflake transfers.

Schema detection and mapping

To define your schema, you can use the BigQuery Data Transfer Service to automatically detect schema and data type mapping when transferring data from Snowflake to BigQuery. Alternatively, you can use the translation engine to define your schema and data types manually.

For more information, see Schema detection and mapping for Snowflake.

Enable incremental transfers

To set up an incremental Snowflake data transfer, see Set up incremental transfers for Snowflake.

Enable private connectivity

If you want to create a private Snowflake data transfer, you must configure your network for private connectivity.

Set up a Snowflake transfer

Before you set up a Snowflake transfer, ensure that you have the following information:

• Your Snowflake account identifier, which is the prefix in your Snowflake account URL. For example, ACCOUNT_IDENTIFIER.snowflakecomputing.com.
• The username and the associated private key with appropriate permissions to your Snowflake database. It can just have the required permissions to execute the data transfer.
• The URI of the staging bucket to use for the transfer. We recommend that you set up a lifecycle policy for this bucket to avoid unnecessary charges.
• The URI of the Cloud Storage bucket where you have stored the schema mapping files obtained from the translation engine.

To create a Snowflake transfer configuration, do the following:

bq mk \
  --transfer_config \
  --project_id=PROJECT_ID \
  --data_source=snowflake_migration \
  --target_dataset=DATASET_NAME \
  --display_name='DISPLAY_NAME' \
  --params='{
    "account_identifier": "ACCOUNT_IDENTIFIER",
    "username": "MIGRATION_USER",
    "auth_mechanism": "AUTH_MECHANISM",
    "password": "PASSWORD",
    "private_key": "PRIVATE_KEY_STRING",
    "is_private_key_encrypted": PRIVATE_KEY_ENCRYPTED,
    "private_key_passphrase": "PRIVATE_KEY_PASSPHRASE",
    "service_account": "SERVICE_ACCOUNT_EMAIL",
    "warehouse": "WAREHOUSE_NAME",
    "database": "DATABASE_NAME",
    "schema": "SCHEMA_NAME",
    "table_name_patterns": "TABLE_NAME_PATTERNS",
    "cloud_provider": "CLOUD_PROVIDER",
    "staging_gcs_uri": "STAGING_BUCKET_URI",
    "staging_s3_uri": "STAGING_S3_URI",
    "aws_access_key_id": "AWS_ACCESS_KEY_ID",
    "aws_secret_access_key": "AWS_SECRET_ACCESS_KEY",
    "azure_storage_account": "AZURE_STORAGE_ACCOUNT",
    "staging_azure_container": "STAGING_AZURE_CONTAINER",
    "azure_sas_token": "AZURE_SAS_TOKEN",
    "storage_integration_object_name": "STORAGE_INTEGRATION_NAME",
    "max_file_size_mb": MAX_FILE_SIZE_MB,
    "use_private_network": USE_PRIVATE_NETWORK,
    "service_attachment": "SERVICE_ATTACHMENT_URI",
    "private_network_service": "SERVICE_DIRECTORY_LINK",
    "ingestion_type": "INGESTION_TYPE",
    "specify_translation_output": SPECIFY_TRANSLATION_OUTPUT,
    "translation_output_gcs_path": "TRANSLATION_OUTPUT_GCS_PATH",
    "custom_schema_file_path": "CUSTOM_SCHEMA_PATH",
    "convert_number_to_int64": CONVERT_NUMBER_TO_INT64
  }'

Configure a Snowflake transfer for schema mapping

The BigQuery Data Transfer Service supports three schema mapping methods for Snowflake transfers, automatic schema detection, translation engine output, and manual schema mapping. For more information, see Schema detection and mapping reference.

To see an example of a transfer configuration that supports each of these schema mapping methods, select one of the following options:

bq mk \
  --transfer_config \
  --project_id=PROJECT_ID \
  --data_source=snowflake_migration \
  --target_dataset=DATASET_NAME \
  --display_name='Snowflake Auto Detection Transfer' \
  --params='{
    "account_identifier": "ACCOUNT_IDENTIFIER",
    "username": "MIGRATION_USER",
    "auth_mechanism": "KEY_PAIR",
    "private_key": "PRIVATE_KEY_STRING",
    "service_account": "SERVICE_ACCOUNT_EMAIL",
    "warehouse": "WAREHOUSE_NAME",
    "database": "DATABASE_NAME",
    "schema": "SCHEMA_NAME",
    "table_name_patterns": "orders;lineitem",
    "cloud_provider": "GCP",
    "staging_gcs_uri": "gs://STAGING_BUCKET_NAME/staging/",
    "storage_integration_object_name": "STORAGE_INTEGRATION_NAME",
    "ingestion_type": "Full"
  }'

bq mk \
  --transfer_config \
  --project_id=PROJECT_ID \
  --data_source=snowflake_migration \
  --target_dataset=DATASET_NAME \
  --display_name='Snowflake Translation Output Transfer' \
  --params='{
    "account_identifier": "ACCOUNT_IDENTIFIER",
    "username": "MIGRATION_USER",
    "auth_mechanism": "KEY_PAIR",
    "private_key": "PRIVATE_KEY_STRING",
    "service_account": "SERVICE_ACCOUNT_EMAIL",
    "warehouse": "WAREHOUSE_NAME",
    "database": "DATABASE_NAME",
    "schema": "SCHEMA_NAME",
    "table_name_patterns": "orders;lineitem",
    "cloud_provider": "GCP",
    "staging_gcs_uri": "gs://STAGING_BUCKET_NAME/staging/",
    "storage_integration_object_name": "STORAGE_INTEGRATION_NAME",
    "ingestion_type": "Full",
    "specify_translation_output": true,
    "translation_output_gcs_path": "gs://TRANSLATION_BUCKET/output/metadata/config/DATABASE_NAME/SCHEMA_NAME/"
  }'

bq mk \
  --transfer_config \
  --project_id=PROJECT_ID \
  --data_source=snowflake_migration \
  --target_dataset=DATASET_NAME \
  --display_name='Snowflake Custom Schema Transfer' \
  --params='{
    "account_identifier": "ACCOUNT_IDENTIFIER",
    "username": "MIGRATION_USER",
    "auth_mechanism": "KEY_PAIR",
    "private_key": "PRIVATE_KEY_STRING",
    "service_account": "SERVICE_ACCOUNT_EMAIL",
    "warehouse": "WAREHOUSE_NAME",
    "database": "DATABASE_NAME",
    "schema": "SCHEMA_NAME",
    "table_name_patterns": "orders;lineitem",
    "cloud_provider": "GCP",
    "staging_gcs_uri": "gs://STAGING_BUCKET_NAME/staging/",
    "storage_integration_object_name": "STORAGE_INTEGRATION_NAME",
    "ingestion_type": "Full",
    "custom_schema_file_path": "gs://CONFIG_BUCKET/schemas/custom_schema.json"
  }'

Specify encryption key with transfers

When you specify a CMEK with a transfer, the BigQuery Data Transfer Service applies the CMEK to any intermediate on-disk cache of ingested data so that the entire data transfer workflow is CMEK compliant.

You cannot update an existing transfer to add a CMEK if the transfer was not originally created with a CMEK. For example, you cannot change a destination table that was originally default encrypted to now be encrypted with CMEK. Conversely, you also cannot change a CMEK-encrypted destination table to have a different type of encryption.

You can update a CMEK for a transfer if the transfer configuration was originally created with a CMEK encryption. When you update a CMEK for a transfer configuration, the BigQuery Data Transfer Service propagates the CMEK to the destination tables at the next run of the transfer, where the BigQuery Data Transfer Service replaces any outdated CMEKs with the new CMEK during the transfer run. For more information, see Update a transfer.

You can also use project default keys. When you specify a project default key with a transfer, the BigQuery Data Transfer Service uses the project default key as the default key for any new transfer configurations.

Quotas and limits

BigQuery has a load quota of 15 TB for each load job for each table by default. Internally, Snowflake compresses the table data, so the exported table size is larger than the table size reported by Snowflake.

To improve load times for larger tables and to remove the 15TB BigQuery load limit, specify the PIPELINE job type for your reservation assignment.

Because of Amazon S3's consistency model, it's possible that some files won't be included in the transfer to BigQuery.

Optimize data transfer performance

You can monitor the performance of your data transfers by viewing the logs for the data transfer. To improve the performance of your data transfers, we recommend that you perform the following optimization steps:

• Keep your Snowflake instance, staging bucket, and BigQuery dataset in the same region
• You can improve table unload speeds by doing the following:
  • Increase the size of your Snowflake virtual warehouse, especially when transferring large Snowflake tables (1 TiB or higher).
  • Adjust the MAX_FILE_SIZE option in the transfer configuration.
    • Smaller file sizes can improve transfer speeds, but making it too small can lead to too many files.
• You can improve table load speeds by increasing the number of BigQuery slot reservations for PIPELINE and QUERY job types.
• When making a full transfer, avoid clustering and partitioning on the destination BigQuery table.
• When making an incremental transfer in Upsert mode, consider clustering and partitioning on primary key columns to improve transfer performance.
  • However, avoid clustering and partitioning on non-primary key columns to prevent slower merge operations.

Pricing

For information on BigQuery Data Transfer Service pricing, see the Pricing page.

• If the Snowflake warehouse and the Amazon S3 bucket are in different regions, then Snowflake applies egress charges when you run a Snowflake data transfer. There are no egress charges for Snowflake data transfers if both the Snowflake warehouse and the Amazon S3 bucket are in the same region.
• When data is transferred from AWS to Google Cloud, inter-cloud egress charges are applied.

What's next

• Learn more about the BigQuery Data Transfer Service.
• Migrate SQL code with the Batch SQL translation.