Multi-Location Resilience for Data Pipelines¶

Requirements¶

• Edition: Snowflake Business Critical Edition (or higher).

• Supported ingestion methods: This feature exclusively supports file-based data loading through Snowpipe (auto-ingest) and COPY INTO <table>. It does not support Openflow or Snowpipe Streaming.

• Identical path structures: To allow your pipelines to locate new files after failover, you must write them to the secondary storage location using the exact same hierarchy, folder structure, and relative path as your primary location.

Considerations¶

• Billing: This feature incurs standard replication charges (data transfer and compute resources), billed to your target account.

• Stage modification downtime: Changing the RELATIVE_URL property on an existing stage will invalidate dependent objects and halt ingestion. We recommend creating new stages during setup to avoid downtime.

• Multi-Queue Notification Integration (MQNI): Using the same active queue in both source and target accounts is not supported. Doing so can result in notification loss. Snowflake does not check whether the same queue is in use across accounts.

• Directory table: Creating a directory table on a stage using MLSI is currently not supported.

Replication behavior¶

• Asynchronous replication: Snowflake replicates your tables and your pipeline’s load history together in the exact same snapshot. Because they are synchronized, an outage will not result in duplicate data. If your secondary database is four hours behind, the table data is also four hours behind, and processing four hours of queued notifications simply brings the table up to date.

• Dual-write data loss avoidance: Your Recovery Point Objective (RPO) is dictated by your replication refresh interval. To prevent data loss during a failover, your secondary cloud message queue’s message retention period must be longer than your replication interval. If your queue drops messages before your scheduled replication catches up, those files will not be ingested upon failover.

• Single-write data loss risk: If you use single-write routing, any files processed in the primary location after your last successful replication are entirely unknown to the secondary location. Upon failover, this data will be temporarily missing in your target account.

1. Dual-writes - recommended¶

Your producer application writes files to both your primary and secondary cloud storage buckets simultaneously. The secondary message queue accumulates notifications, but Snowflake does not process them because the secondary database is read-only.

• What happens on failover: The secondary database becomes writable. Snowpipe reads the secondary queue and uses the replicated load history to deduplicate files. If an outage prevented a file from finishing in the primary location, the secondary pipeline reads the notification from the secondary queue, sees the file is missing from the load history, and ingests it.

• What happens on failback: When the primary location recovers and you refresh the failover group and then fail back, Snowpipe automatically starts ingesting new files since the load history was synced from the secondary account during your failback preparation.

• Result: No missing data, no duplicates. Snowflake handles reconciliation automatically in both directions.

• Action needed: None, beyond the standard pre-failback replication sync (ALTER FAILOVER GROUP … REFRESH) to ensure the primary account has the latest load history.

2. Single-write routing¶

Your producer only writes to the primary cloud storage. Upon an outage, you reroute the producer to start writing new files to the secondary cloud storage.

• What happens on failover: The secondary account immediately begins processing new files routed to the secondary bucket. However, any in-flight files trapped in the impacted primary location are left behind temporarily.

• What happens on failback: When the primary location recovers and you fail back to your primary Snowflake account, Snowpipe automatically processes any file notifications that successfully reached the queue before the outage.

• Result: No duplicates. However, any files where the cloud notification completely failed to generate because of the outage (or where the outage outlasted your queue’s message retention policy) require manual intervention.

• Action needed: After failing back, compare your primary storage bucket against the COPY_HISTORY view in Snowflake to identify any missing files. Run ALTER PIPE … REFRESH or a manual COPY INTO command to load those specific stranded files.

Step 1: Create a Multi-Location Storage Integration (MLSI)¶

To configure a Multi-Location Storage Integration, you follow the standard steps for configuring a storage integration with a few differences noted in this section.

In your source account, create the MLSI by providing values for each location in the STORAGE_LOCATIONS list. You can mix and match cloud providers for cross-cloud setups.

CREATE STORAGE INTEGRATION my_mlsi
  TYPE = EXTERNAL_STAGE
  STORAGE_LOCATIONS =
  (
    (
      NAME = 'my-s3-us-west-1'
      STORAGE_PROVIDER = 'S3'
      STORAGE_BASE_URL = 's3://myBucketWest'
      STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::12345:role/myrole'
      STORAGE_AWS_EXTERNAL_ID = 'mlsi-external-id'
      ENCRYPTION = ( TYPE = 'AWS_SSE_S3' )
    ),
    (
      NAME = 'my-s3-us-east-1'
      STORAGE_PROVIDER = 'S3'
      STORAGE_BASE_URL = 's3://myBucketEast'
      STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::67890:role/myrole'
      STORAGE_AWS_EXTERNAL_ID = 'mlsi-external-id'
      ENCRYPTION = ( TYPE = 'AWS_SSE_S3' )
    )
  )
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('*')
  ACTIVE = 'my-s3-us-west-1';

Where:

• STORAGE_LOCATIONS: Specifies a list of one or more storage locations (S3 bucket, GCS bucket, or Azure container) for the storage integration. To view the parameters for each cloud provider, see cloud provider parameters (cloudProviderParams) on the CREATE STORAGE INTEGRATION reference page.

• NAME: String that specifies the identifier (name) for the storage location.

• ENCRYPTION: Specifies encryption for the storage location. You must specify encryption for the storage location at the storage integration level instead of at the stage level. Required only for loading from encrypted files; not required if the storage location and files are unencrypted. To view the encryption options for each cloud provider, see ENCRYPTION on the CREATE STAGE reference page.

• ACTIVE: Specifies the name of the storage location to set as the active location for the storage integration in the current account.

  For the active storage location in your source account, you must set up access control and grant Snowflake permission to access your storage. Use the instructions in the following topics:

  • Configuring a Snowflake storage integration to access Amazon S3

  • Configure an integration for Google Cloud Storage

  • Configure an Azure container for loading data

Step 2: Associate MLSI with an external stage¶

We highly recommend creating a new stage rather than altering an existing one.

Use the CREATE STAGE command to associate the multilocation storage integration that you created with one or more external stages:

CREATE STAGE my_ext_stage
  RELATIVE_URL = '/my_folder/my_sub_folder/'
  STORAGE_INTEGRATION = 'my_mlsi';

Where:

• RELATIVE_URL: The relative path to your external stage location from the storage location defined in your storage integration. To allow your pipelines to locate new files after failover, you must write them to the secondary storage location using the same hierarchy, folder structure, and relative path as your primary location.

• STORAGE_INTEGRATION: The name of your Multi-Location Storage Integration.

For example:

ALTER STAGE my_ext_stage SET
  RELATIVE_URL = '/my_folder/my_sub_folder/'
  STORAGE_INTEGRATION = 'my_mlsi';

Step 3: Configure a Multi-Queue Notification Integration (MQNI)¶

If you use automated data loading through cloud messaging and have configured a Multi-Location Storage Integration for your external stage, you must also use a Multi-Queue Notification Integration for seamless failover of your Snowpipe pipelines.

For each queue that you define for the notification integration, you must prepare your messaging service using the steps in the following topics:

• Configuring Amazon SNS to Automate Snowpipe using SQS Notifications. Create an SNS topic for each AWS region in which your MLSI has storage locations.

• Configuring Automation Using GCS Pub/Sub

• Configuring Automation With Azure Event Grid

CREATE NOTIFICATION INTEGRATION my_mqni
  ENABLED = TRUE
  TYPE = MULTI_QUEUE
  DIRECTION = INBOUND
  QUEUES = (
    (
      NAME = 'my-us-west-1'
      NOTIFICATION_PROVIDER = AWS_SNS
      AWS_SNS_TOPIC_ARN = 'arn:aws:sns:us-west-1:12345:my-snowpipe-mlsi-west'
    ),
    (
      NAME = 'my-us-east-1'
      NOTIFICATION_PROVIDER = AWS_SNS
      AWS_SNS_TOPIC_ARN = 'arn:aws:sns:us-west-1:12345:my-snowpipe-mlsi-east'
    )
  )
  ACTIVE = 'my-us-west-1';

CREATE PIPE my_pipe
  AUTO_INGEST = TRUE
  INTEGRATION = my_mqni
  AS COPY INTO my_table FROM @my_ext_stage/my_pipe/;

SYSTEM$CONVERT_PIPES_TO_MULTI_QUEUE(
  '<new_mqni_name>',
  '<original_sns_topic_arn_or_int_name>',
  '<new_sns_topic_arn_or_int_name>'
)

SELECT SYSTEM$CONVERT_PIPES_TO_MULTI_QUEUE(
  'my_mqni',
  'arn:aws:sns:us-west-1:12345:my-snowpipe-mlsi-west',
  'arn:aws:sns:us-east-1:67890:my-snowpipe-mlsi-east'
);

SELECT SYSTEM$CONVERT_PIPES_TO_MULTI_QUEUE(
  'my_azure_mqni',
  'my_azure_ni_1',
  'my_azure_ni_2'
);

Step 4: Replicate your MLSI and MQNI to target account¶

1. To replicate your multi-location storage integration and multi-queue notification integration, alter your existing replication or failover group to include STORAGE INTEGRATIONS and NOTIFICATION INTEGRATIONS in the ALLOWED_INTEGRATION_TYPES list.

For example, use the ALTER FAILOVER GROUP command:

ALTER FAILOVER GROUP my_fg SET
  OBJECT_TYPES = DATABASES, ROLES, INTEGRATIONS
  ALLOWED_INTEGRATION_TYPES = API INTEGRATIONS, STORAGE INTEGRATIONS,
    NOTIFICATION INTEGRATIONS;

2. Then, in your target account, perform a refresh operation:

ALTER FAILOVER GROUP my_fg REFRESH;

Step 5: Configure active states in target account¶

After you perform a refresh operation, to ensure a seamless failover during an actual outage, configure the active storage location and queue (if using a notification integration) in your target account.

In your target account:

1. For the storage location that you want to set as the active location in your target account, use the instructions in the following topics to grant Snowflake access to your storage:

   • Option 1: Configure a Snowflake storage integration to access Amazon S3

   • Configure an integration for Google Cloud Storage

   • Configure an Azure container for loading data

2. Activate secondary storage: Set the MLSI to use your secondary backup storage location in the target account.

   CopyExpand

   ALTER STORAGE INTEGRATION my_mlsi SET ACTIVE = 'my-s3-us-east-1';
   

   Show lessSee more

   Scroll to top

3. If you are using a Multi-Queue Notification Integration, grant Snowflake permission to access your messaging service for the queue that you want to set as active in your target account. Follow the instructions for your cloud provider:

   • For AWS, see Subscribe the Snowflake SQS Queue to the SNS Topic under Prerequisite: Create an Amazon SNS Topic and Subscription.

   • For Google Cloud, see Grant Snowflake Access to the Pub/Sub Subscription under Configuring Automation Using GCS Pub/Sub.

   • For Azure, see Grant Snowflake Access to the Storage Queue under Configuring Automation With Azure Event Grid.

4. Activate secondary queue (if using MQNI): Set the active queue to your secondary location in the target account.

   CopyExpand

   ALTER INTEGRATION my_mqni
     SET ACTIVE = 'MY_MQNI-queue2';
   

   Show lessSee more

   Scroll to top

1. Verify active integration states¶

Confirm that your integrations are pointing to the correct storage and queues by checking their properties. Look for the ACTIVE property in the output:

-- Check the active storage location
DESCRIBE STORAGE INTEGRATION my_mlsi;

-- Check the active message queue
DESCRIBE INTEGRATION my_mqni;

2. Check pipe status (Snowpipe only)¶

Use the SYSTEM$PIPE_STATUS function to ensure your pipe is running and to check whether it is actively queueing new files from your secondary location.

SELECT SYSTEM$PIPE_STATUS('my_pipe');

Look for “executionState”:”RUNNING” and check “pendingFileCount” to confirm it is actively recognizing new files dropped into your secondary bucket.

3. Validate successful ingestion (load history)¶

To guarantee that data is loading without errors or duplicates, query the COPY_HISTORY view. This shows exactly which files were ingested, their source path, and when they were loaded.

SELECT file_name, status, row_count, last_load_time
FROM TABLE(information_schema.copy_history(
  table_name => 'my_table',
  start_time => DATEADD(hours, -1, CURRENT_TIMESTAMP())
));

Verify that the file_name paths reflect your active storage location and that status shows as LOADED.