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 allow lists.

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 same cloud provider where Snowflake is hosted. For AWS-hosted Snowflake accounts, the data is first staged in your Amazon S3 bucket, which is then transferred to BigQuery with the BigQuery Data Transfer Service.

The following diagram shows how data is transferred from an AWS-hosted Snowflake data warehouse into BigQuery.

Limitations

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

• Data transfers are only supported from Snowflake accounts hosted on AWS. Data transfers from Snowflake accounts hosted on Google Cloud or Microsoft Azure aren't supported.
• The Snowflake connector doesn't support incremental data transfers.
• 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 is limited by the Snowflake warehouse you have chosen for this transfer.

• Data is extracted from Snowflake in the Parquet data format before it is loaded into BigQuery:

  • The following Parquet data types are unsupported:
    • TIMESTAMP_TZ, TIMESTAMP_LTZ
    • OBJECT, VARIANT, ARRAY
    • For more information, see Assess Snowflake data.

  • The following Parquet data type is unsupported, but can be converted:

    • TIMESTAMP_NTZ

    Use the global type conversion configuration YAML to override the default behavior of DATETIME conversion to TIMESTAMP when you generate metadata and run the translation engine.

    The configuration YAML might look similar to the following example:

    type: experimental_object_rewriter
    global:
      typeConvert:
        datetime: TIMESTAMP
    

Before you begin

Before you set up a Snowflake transfer, you must perform all the steps listed in this section. The following is a list of all required steps.

1. Prepare your Google Cloud project
2. Required BigQuery roles
3. Prepare your Amazon S3 bucket
4. Create a Snowflake user with the required permissions
5. Add network policies
6. Generate metadata and run translation engine
7. Assess your Snowflake for any unsupported data types
8. Gather transfer information

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.

Required BigQuery roles

To get the permissions that you need to create a transfer, ask your administrator to grant you the BigQuery Admin (roles/bigquery.admin) IAM role. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a transfer. 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.

Prepare your Amazon S3 bucket

To complete a Snowflake data transfer, you must create a Amazon S3 bucket and then configure it to allow write access from Snowflake.

1. Create an Amazon S3 bucket. The Amazon S3 bucket is used to stage the data before it is loaded into BigQuery.

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

To allow read access on your Amazon S3 bucket, you must also do the following:

1. Create a dedicated Amazon IAM user and grant it the AmazonS3ReadOnlyAccess policy.

2. Create an Amazon access key pair for the IAM user.

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:

  // Create and configure new role, MIGRATION_ROLE
  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;

  // You can modify this to give select permissions for all tables in a schema
  GRANT SELECT
    ON TABLE DATABASE_NAME.SCHEMA_NAME.TABLE_NAME
    TO ROLE MIGRATION_ROLE;

  GRANT USAGE
    ON STORAGE_INTEGRATION_OBJECT_NAME
    TO ROLE MIGRATION_ROLE;

Replace the following:

• MIGRATION_ROLE: the name of the custom role you are creating
• WAREHOUSE_NAME: the name of your data warehouse
• DATABASE_NAME: the name of your Snowflake database
• SCHEMA_NAME: the name of your Snowflake schema
• TABLE_NAME: the name of the Snowflake included in this data transfer
• STORAGE_INTEGRATION_OBJECT_NAME: the name of your Snowflake storage integration object.

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.

The following table is a list of IP addresses for the regional and multi-regional locations used for public transfers. You can either add the IP addresses that only correspond to your dataset's location, or you can add all the IP addresses listed in the table. These are IP addresses reserved by Google for BigQuery Data Transfer Service data transfers.

To add an IP address to an allowlist, do the following:

1. Create a network rule with type = IPV4. The BigQuery Data Transfer Service uses a JDBC connection to connect to the Snowflake account.
2. Create a network policy with the network rule that you created earlier and the IP address from the following table.

Regional locations

Multi-regional locations

¹ Data located in the EU multi-region is not stored in the europe-west2 (London) or europe-west6 (Zürich) data centers.

Generate metadata and run translation engine

The BigQuery Data Transfer Service for Snowflake connector uses the BigQuery migration service translation engine for schema mapping when migrating Snowflake tables into BigQuery. To complete a Snowflake data transfer, you must first generate metadata for translation, then run the translation engine:

1. Run the dwh-migration-tool for Snowflake. For more information, see Generate metadata for translation and assessment.
2. Upload the generated metadata.zip file to a Cloud Storage bucket. The metadata.zip file is used as input for the translation engine.
3. Run the batch translation service, specifying the target_types field as dts-mapping. For more information, see Translate SQL queries with the translation API.
   • The following is an example of a command to run a batch translation for Snowflake:

     curl -d "{
     "name": "sf_2_bq_translation",
     "displayName": "Snowflake to BigQuery Translation",
     "tasks": {
       string: {
         "type": "Snowflake2BigQuery_Translation",
         "translation_details": {
             "target_base_uri": "gs://sf_test_translation/output",
             "source_target_mapping": {
               "source_spec": {
                   "base_uri": "gs://sf_test_translation/input"
               }
             },
             "target_types": "dts-mapping",
         }
       }
     },
     }" 
     
     -H "Content-Type:application/json" 
     
     -H "Authorization: Bearer TOKEN" -X POST https://bigquerymigration.googleapis.com/v2alpha/projects/project_id/locations/location/workflows
     

   • You can check the status of this command in the SQL Translation page in BigQuery. The output of the batch translation job is stored in gs://translation_target_base_uri/dts/config/.

Required service account permissions

In a Snowflake transfer, a service account is used to read data from the translation engine output in the specified Cloud Storage path. You must grant the service account the storage.objects.get and the storage.objects.list permissions.

If the service account is from a Google Cloud project that is different from the project that created the BigQuery data transfer, then you must also enable cross-project service account authorization.

For more information, see BigQuery IAM roles and permissions.

Assess Snowflake data

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 Overview of Amazon S3 transfers.

Gather transfer information

Gather the information that you need to set up the migration with the BigQuery Data Transfer Service:

• Your Snowflake account identifier, which is the prefix in your Snowflake account URL. For example, ACCOUNT_IDENTIFIER.snowflakecomputing.com.
• The username and password of a user with appropriate permissions to your Snowflake database. It can just have the required permissions to execute the data transfer.
• The URI of the Amazon S3 bucket that you want to use for the transfer, and the AWS user access key pair. 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.

Set up an Snowflake transfer

Select one of the following options:

bq mk \
    --transfer_config \
    --project_id=project_id \
    --data_source=data_source \
    --target_dataset=dataset \
    --display_name=name \
    --service_account_name=service_account \
    --params='parameters'

  PARAMS='{
  "account_identifier": "your_account_identifier",
  "aws_access_key_id": "your_access_key_id",
  "aws_secret_access_key": "your_aws_secret_access_key",
  "cloud_provider": "AWS",
  "database": "your_sf_database",
  "password": "your_sf_password",
  "schema": "your_snowflake_schema",
  "service_account": "your_service_account",
  "storage_integration_object_name": "your_storage_integration_object",
  "staging_s3_uri": "s3://your/s3/bucket/uri",
  "table_name_patterns": ".*",
  "translation_output_gcs_path": "gs://sf_test_translation/output/dts/database_name/schema_name/",
  "username": "your_sf_username",
  "warehouse": "your_warehouse"
}'

bq mk --transfer_config \
    --project_id=your_project_id \
    --target_dataset=your_bq_dataset \
    --display_name='snowflake transfer config' \
    --params="$PARAMS" \
    --data_source=snowflake_migration

Quotas and limits

BigQuery has a load quota of 15 TB for each load job for each table. Internally, Snowflake compresses the table data, so the exported table size is larger than the table size reported by Snowflake. If you plan to migrate a table larger than 15 TB, please contact contact dts-migration-preview-support@google.com.

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

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.