Creating a Snowflake Zero-Copy Clone
  • Dark
    Light

Creating a Snowflake Zero-Copy Clone

  • Dark
    Light

Note

This article applies to Matillion ETL for Snowflake.

Overview

Zero-Copy Cloning (sometimes called simply "cloning") is a Snowflake feature that makes a copy of a database without duplicating the data it contains. The clone operation takes a snapshot of the source data when the clone is created, and makes this data available to the cloned object. After this point, the clone is independent of the source, so any subsequent changes made to either the source or the clone aren't reflected in the other.

Matillion ETL supports Snowflake's zero-copy clone feature, allowing you to clone a database from within a Matillion ETL instance.

For more information on zero-copy clones, read Cloning Considerations.


Cloning a database in Matillion ETL

To create a clone, follow these steps:

1. In the Environments panel in the bottom-left, right-click the database environment you want to clone and then click CloneClone Database from the menu.

Clone database

Note

You can clone a database from any of the listed environments, even if you aren't currently working in that environment.

2. In the Clone database dialog, enter the New database name. The default will be the source database name with _CLONE_001 appended, but you can overwrite this with a name of your choice.

3. Select Replace the existing database, if any, with the clone database if you want the clone to overwrite (and therefore delete) any existing database that has the name you have entered. If you don't select this option and a database with your selected name already exists, the operation will fail with the error, Object [clone name] already exists.

4. Click Confirm to begin the clone operation.

Clone database dialog

The results of the cloning operation will be shown in the Tasks tab in the lower-right pane.

Note

You must have sufficient account privileges to clone the database. If you don't, the operation fails with an error, Failed to clone database [DEV_DB]: SQL access control error: Insufficient privileges to operate on account 'MATILLION'.


Cloning an environment in Matillion ETL

You can clone the entire Matillion ETL environment along with the database. The process is similar to that described above, but you will select the option CloneClone Environment & Database from the Environment menu. Then, in the Clone environment and database dialog, enter a New environment name in addition to a New database name. The default name will be the source environment name with _CLONE_001 appended.

The new clone will appear in the list of environments and can be edited or deleted from there.


Running a job in a clone

Any transformation or orchestration job can be run using a cloned database. This allows you to test the job on data that mirrors that in your production environment, without affecting your production data.

You don't need to first create a cloned environment or database, as the clone is created automatically when a job is run in this way.

For a general description of jobs in Matillion ETL, read Jobs.

To run a job on a cloned database:

1. Open the job in Matillion ETL and right-click on the job canvas to open the context menu.

2. Click Run Job in Clone. This will open the Run job in clone dialog.

Run job in clone dialog

3. You can choose to have the cloned environment and database deleted after the job runs. In the Run job in clone dialog, you have the following options:

  • Select The job runs successfully if you want the clone to be automatically deleted after the job runs successfully.
  • Select The job fails if you want the clone to be automatically deleted after the job fails to run.
  • Select both options if you want the clone to be automatically deleted regardless of whether the job has succeeded or failed.
  • Clear both options if you don't want the clone to be deleted.

Both options are selected by default.

4. Click Confirm to confirm your choices and run the job.

The cloned environment and database created for the job will have the same names as the source database and environment, with _CLONE_001 appended. There is no option to edit these names.