Schedule a Snowflake transfer

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The Snowflake connector provided by the BigQuery Data Transfer Service lets youschedule and manage automated transfer jobs to migrate data from Snowflakeinto BigQuery using public IP allow lists.

Overview

The Snowflake connector engages migration agents in theGoogle Kubernetes Engine and triggers a load operation from Snowflake to astaging area within the same cloud provider where Snowflake ishosted.

• For AWS-hosted Snowflake accounts, thedata is first staged in your Amazon S3 bucket, which is then transferredto BigQuery with the BigQuery Data Transfer Service.
• ForGoogle Cloud-hosted Snowflake accounts, the data is firststaged in your Cloud Storage bucket, which is then transferredto BigQuery with the BigQuery Data Transfer Service.
• For Azure-hosted Snowflake accounts, the data is firststaged in your Azure Blob Storage container, which is then transferredto BigQuery with the BigQuery Data Transfer Service.

Limitations

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

• The Snowflake connector doesn't support incremental data transfers.
• The Snowflake connector only supports transfers from tableswithin a single Snowflake database and schema. To transfer fromtables with multiple Snowflake databases or schemas, you canset up each transfer job separately.
• The speed of loading data from Snowflake to your Amazon S3bucket or Azure Blob Storage container or Cloud Storage bucket is limited by the Snowflake warehouse you have chosenfor this transfer.

Before you begin

Before you set up a Snowflake transfer, you must perform all thesteps 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 staging bucket
4. Create a Snowflake user with the required permissions
5. Add network policies
6. Optional:Schema detection and mapping
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 Snowflaketransfer with the following steps:

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

   Note: If you don't plan on keeping the resources created during thisSnowflake transfer, create a new Google Cloud project insteadof selecting an existing one. You can then delete the project once you aredone with your Snowflake transfer.

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

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

Required BigQuery roles

To get the permissions that you need to create a BigQuery Data Transfer Service data transfer, ask your administrator to grant you theBigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a BigQuery Data Transfer Service data transfer. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information, seeGrantbigquery.admin access.

1. Create an Amazon S3 bucket.

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

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

1. Create a dedicatedAmazon IAM userand grant it theAmazonS3ReadOnlyAccesspolicy.

2. Create an Amazon access key pairfor the IAM user.

Staging Azure Blob Storage container for Azure-hosted Snowflake account

For Azure-hosted Snowflake accounts, create aAzure Blob Storage container to stage the Snowflake data before itis loaded into BigQuery.

1. Create an Azure storage account and astorage container within it.
2. Create and configure a Snowflake storage integration objectto allow Snowflake to write data into the Azure storagecontainer as an external stage. Note that 'Step 3: Creating an external stage' can be skipped as we don't use it.

To allow read access on your Azure container,generate a SAS Token for it.

Staging bucket for Google Cloud-hosted Snowflake account

For Google Cloud-hosted Snowflake accounts, create aCloud Storage bucket to stage the Snowflake data before itis loaded into BigQuery.

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

3. To allow access to staging bucket, GrantDTS service agent theroles/storage.objectViewer role withthe following command:

   gcloudstoragebucketsadd-iam-policy-bindinggs://STAGING_BUCKET_NAME\--member=serviceAccount:service-PROJECT_NUMBER@gcp-sa-bigquerydatatransfer.iam.gserviceaccount.com\--role=roles/storage.objectViewer

Create a Snowflake user with the required permissions

During a Snowflake transfer, the Snowflakeconnector connects to your Snowflake account using a JDBCconnection. You must create a new Snowflake userwith a custom role that only has the necessary privileges to perform thedata transfer:

  // Create and configure new role,MIGRATION_ROLE  GRANT USAGE    ON WAREHOUSEWAREHOUSE_NAME    TO ROLEMIGRATION_ROLE;  GRANT USAGE    ON DATABASEDATABASE_NAME    TO ROLEMIGRATION_ROLE;  GRANT USAGE    ON SCHEMADATABASE_NAME.SCHEMA_NAME    TO ROLEMIGRATION_ROLE;  // You can modify this to give select permissions for all tables in a schema  GRANT SELECT    ON TABLEDATABASE_NAME.SCHEMA_NAME.TABLE_NAME    TO ROLEMIGRATION_ROLE;  GRANT USAGE    ONSTORAGE_INTEGRATION_OBJECT_NAME    TO ROLEMIGRATION_ROLE;

Replace the following:

• MIGRATION_ROLE: the name of the custom role you arecreating
• WAREHOUSE_NAME: the name of your data warehouse
• DATABASE_NAME: the name of your Snowflakedatabase
• SCHEMA_NAME: the name of your Snowflakeschema
• TABLE_NAME: the name of the Snowflakeincluded in this data transfer
• STORAGE_INTEGRATION_OBJECT_NAME: the name of yourSnowflake storage integration object.

Generate key pair for authentication

Due to thedeprecation of single factor password sign-ins by Snowflake, we recommend that you use key pair for authentication.

You can configure a key pair by generating an encrypted or unencrypted RSA key pair, then assigning the public key to a Snowflake user. For more information, seeConfiguring key-pair authentication.

Add network policies

For public connectivity, the Snowflake account allows public connectionwith database credentials by default. However, you might have configured network rules orpolicies that could prevent the Snowflake connector fromconnecting to your account. In this case, you must add the necessary IPaddresses to your allowlist.

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

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

1. Create a network rulewithtype =IPV4. The BigQuery Data Transfer Service uses a JDBC connection toconnect to the Snowflake account.
2. Create a network policywith the network rule that you created earlier and the IP address from thefollowing table.

The Snowflake connector can automatically detect your Snowflake table schema. To use automatic schema detection, you can leave theTranslation output GCS path field blank when youset up a Snowflake transfer.

The following list shows how the Snowflake connector maps your Snowflake data types into BigQuery:

• The following data types are mapped asSTRING in BigQuery:
  • TIMESTAMP_TZ
  • TIMESTAMP_LTZ
  • OBJECT
  • VARIANT
  • ARRAY
• The following data types are mapped asTIMESTAMP in BigQuery:
  • TIMESTAMP_NTZ

All other Snowflake data types are mapped directly to their equivalent types in BigQuery.

Using translation engine output for schema

To define your schema manually (for example, to override certain schema attributes), you can generate your metadata and run the translation engine with the following steps:

Limitations

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

  • The following Parquet data types are unsupported:
    • TIMESTAMP_TZ,TIMESTAMP_LTZ
    • For more information, seeAssess Snowflake data.

  • The following Parquet data types are unsupported, but can be converted:

    • TIMESTAMP_NTZ
    • OBJECT,VARIANT,ARRAY

    Use theglobal type conversion configuration YAMLto override the default behavior of these data types when you run translation engine.

    The configuration YAML might look similar to the following example:

    type:experimental_object_rewriterglobal:typeConvert:datetime:TIMESTAMPjson:VARCHAR

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

1. Run thedwh-migration-tool for Snowflake. For moreinformation, seeGenerate metadata for translation and assessment.
2. Upload the generatedmetadata.zip file to a Cloud Storage bucket. Themetadata.zip file is used as input for the translation engine.

3. Run the batch translation service, specifying thetarget_types field asmetadata. For more information, seeTranslate 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\": \"metadata\",        }      }  },  }"\-H"Content-Type:application/json"\-H"Authorization: Bearer TOKEN"-XPOSThttps://bigquerymigration.googleapis.com/v2alpha/projects/project_id/locations/location/workflows

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

Required service account permissions

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

We recommend that the service account belongs to the same Google Cloud projectwhere the transfer configuration and destination dataset is created. If theservice account is in a Google Cloud project that is different from theproject that created the BigQuery data transfer, then you mustenable cross-project service account authorization.

For more information, seeBigQuery IAM roles and permissions.

Assess Snowflake data

BigQuery writes data from Snowflake toCloud Storage as Parquet files. Parquet files don't support theTIMESTAMP_TZ andTIMESTAMP_LTZdata types. If your data contains these types, you can export it toAmazon S3 as CSV files and then import the CSV files intoBigQuery. For more information, seeOverview ofAmazon 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 yourSnowflake account URL. For example,ACCOUNT_IDENTIFIER.snowflakecomputing.com.
• The username and the associated private key with appropriate permissions to yourSnowflake database. It can just have therequired permissionsto execute the data transfer.
• The URI of the staging bucket that you want to use for the transfer:
  • For anAWS-hosted Snowflake account, anAmazon S3 bucket URI is required along with access credentials.
  • For an Azure-hosted Snowflake, anAzure Blob Storage account and container is required.
  • For aGoogle Cloud-hosted Snowflake account, aCloud Storage bucket URI is required.We recommend that you set up a lifecycle policyfor this bucket to avoid unnecessary charges.
• The URI of the Cloud Storage bucket where you have stored theschema mapping files obtained from the translation engine.

Set up a Snowflake transfer

Select one of the following options:

bqmk\--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",  "auth_mechanism": "KEY_PAIR",  "aws_access_key_id": "your_access_key_id",  "aws_secret_access_key": "your_aws_secret_access_key",  "cloud_provider": "AWS",  "database": "your_sf_database",  "private_key": "-----BEGIN PRIVATE KEY----- privatekey\nseparatedwith\nnewlinecharacters=-----END PRIVATE KEY-----",  "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/metadata/config/database_name/schema_name/",  "username": "your_sf_username",  "warehouse": "your_warehouse"}'bqmk--transfer_config\--project_id=your_project_id\--target_dataset=your_bq_dataset\--display_name='snowflake transfer config'\--params="$PARAMS"\--data_source=snowflake_migration

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

Because ofAmazon S3's consistency model,it's possible that some files won't be included in the transfer toBigQuery.

Pricing

For information on BigQuery Data Transfer Service pricing, see thePricingpage.

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

What's next

• Learn more about theBigQuery Data Transfer Service.
• Migrate SQL code with theBatch SQL translation.