Manage Sequences

Sequences are a function of Snowflake that allows users to create an automatically iterating value that can be loaded into tables. The official documentation on Sequences can be found here.

Matillion ETL allows users to create and use Sequences through the client. To begin, open the Manage Sequences dialog by right-clicking an environment and selecting Manage Sequences.

Clicking the + button will bring up a new dialog to create a new sequence. Give it a Schema and a Name. This name can be referenced from SQL and holds a Next value. This value increments by a set amount every time data is loaded into the table that the sequence is used in.

In the example below, we're creating a sequence to count up from 1.

Clicking OK will create the sequence. This sequence is now ready to be used in a table. By way of example, consider the job below.

This job will be creating a new table and loading user data from Jira into it. If we'd like to add our own numerical unique key to that table, Sequences might be an ideal solution as its iterative nature means no two values will be identical. In the Create Table component we can set one of the columns to use our previously created Sequence.

We reference the Next value of our Sequence using the format:

<SEQUENCENAME>.NEXTVAL

In some cases the entire reference may require capitalisation but in others (such as in the case below) the component will capitalise these in the SQL automatically.

Other columns represent the data we'll be bringing in through the Jira Query component. An important point to note is that in the Jira Query properties, we have set the Recreate Target Table Load Option to 'Off' such that the component will load data directly into the created table as it is.

Running this job will load the data. This data can be sampled through the Table Input component in a Transformation job. Note that we now have a column named tag after our Sequence, with sequential values for each row.

Whenever a row has been loaded into the table, the sequence has been automatically incremented and its value at the time of loading has been recorded in the tag column.

It is also worth mentioning that if we now navigate back to the Manage Sequences dialog, we can see that the Next value for our Sequence is now 70. Sequences do not reset between uses and will always iterate when as data.

To reset a Sequence, the user must first delete is (using the Dustbin Icon at the right-side of the dialog) and then recreate it.