Update a Global Variable from a Logic Step

What does this mean?

In a SQL/Script logic step, variables can be saved at the river level or global level. A river variable will allow for dynamic passing of values from step to step within the same logic river session. A global variable enables the passing of values into an account-level variable, which can then be used in other rivers.

An Example Use Case

Here we’ll walk through a use case using a Logic river to update a Data Source to Target river. Let’s say we want to run the same process across two databases in our data warehouse. Instead of maintaining two separate rivers, we can update a global variable to swap the database names in one template river.

At a high level, this use case requires a Logic river that contains the following:

  1. Logic step to save the database names into a river variable
  2. Logic step to update a global variable with the river variable value we are cycling through
  3. Data Source to Target river where the global variable value is applied

First, create a new Logic river. The first step will call the list of database names and save them into a river variable. This is so that we can loop through these values, updating a global variable each iteration.

The results of the source query looks like this:

Next, add another logic step and wrap it in a container by clicking ‘Container Me’ on the step.

Set the container to ‘Loop over’ and choose the db_list variable created in the first step. Name an iterator (in this case we name it ‘db_name’). In the logic step inside the loop container, call the iterator value and save this to a global variable. This will update the {database_name} variable in the Variables page on the left-side panel.

Lastly, add a Data Source to Target river into the looping container. In this use case, the Data Source to Target is a MySQL river that 1) filters the data to CUSTOMER = {database_name} and 2) saves the resulting tables into the {database_name} database.

In the MySQL river:

  1. Filter on customer = {database_name}

  1. Save to the {database_name} database.

Make sure to set the variable name in the Data Source to Targer river to be the same as the global variable updated in the Logic river. In this case it is called ‘database_name’.

2 Likes