Transformer for Snowflake Pipeline Failover

You can enable a Transformer for Snowflake job for pipeline failover when your organization uses a deployed Transformer for Snowflake engine. Enable pipeline failover to prevent Transformer for Snowflake pipelines from failing due to an unexpected engine shutdown.

Note: When your organization uses the Transformer for Snowflake engine hosted by StreamSets, StreamSets automatically handles any failover scenarios for you. As such, failover properties are omitted from Transformer for Snowflake jobs that run on the hosted engine.

When you start a Transformer for Snowflake job, Control Hub sends an instance of the pipeline to one Transformer for Snowflake engine with all labels specified for the job. Transformer for Snowflake generates SQL queries based on your pipeline configuration and passes the queries to Snowflake for execution. Snowflake runs the queries just as it runs any other query, distributing the processing and automatically handling failover as needed.

As the pipeline runs, Transformer for Snowflake monitors the status of the Snowflake queries and then passes the status to Control Hub. If the Transformer for Snowflake engine unexpectedly shuts down, Snowflake continues to run the queries but the engine can no longer monitor the status.

When a job is enabled for failover, Control Hub can reassign the job to a backup Transformer for Snowflake engine when the initial engine becomes unresponsive. In this case, the backup engine submits the queries again if needed, guaranteeing that the queries run at least once.

An available Transformer for Snowflake engine includes any Transformer for Snowflake engine that is assigned all labels specified for the job and that has not exceeded any resource thresholds. When multiple engines are available as a backup, Control Hub prioritizes the engine currently running the fewest number of pipelines.

Failover and Backup Engines

When you enable a Transformer for Snowflake job for failover, use engine labels to define a group of at least two Transformer for Snowflake engines that the job can start on. That way, you reserve a backup engine for pipeline failover.

For example, you want to run a job on the group of two Transformer for Snowflake engines assigned the WesternRegion label. You assign the WesternRegion label to the job, and enable failover for the job. When you start the job, Control Hub sends a single pipeline instance to the Transformer for Snowflake engine currently running the fewest number of pipelines. The second engine serves as a backup and is available for pipeline failover if the first engine unexpectedly shuts down.

Failover Retries

When a Transformer for Snowflake job is enabled for failover, Control Hub retries the failover an infinite number of times by default. If you want the failover to stop after a given number of retries, define the maximum number of retries to perform.

To determine the maximum number of retries, configure the Global Failover Retries property. Control Hub maintains the global failover retry count across all available Transformer for Snowflake engines. When the maximum number of global failover retries is reached, Control Hub stops the job.

Tip: You can create a subscription that triggers an action when a job has exceeded the maximum number of global failover retries.

Enabling Pipeline Failover

You can configure a Transformer for Snowflake job for pipeline failover. Enable pipeline failover when you create a job or when you edit an inactive job.

Important: Before you enable pipeline failover for a job, first define a group of at least two Transformer for Snowflake engines that the job can start on. That way, you reserve a backup engine for pipeline failover.

To enable pipeline failover when you edit an inactive job:

  1. In the Navigation panel, click Run > Job Instances.
  2. Hover over the inactive Transformer for Snowflake job, and click the Edit icon: .
  3. Select the Enable Failover property.
  4. Optionally, set the Global Failover Retries property to the maximum number of retries to attempt across all available engines.

    When the maximum number of global failover retries is reached, Control Hub stops the job. Use -1 to retry indefinitely.

  5. Click Save.