How to Dynamically Create and Populate Tables Using Rivery’s Logic Rivers

As a leading ELT platform, Rivery offers customers unprecedented flexibility and bespoke data pipelines through features such as Logic Rivers.

Using a code-free interface, Logic Rivers empower customers to orchestrate custom data workflows and harness data warehouses for in-database transformations.

Although Logic Rivers offer near limitless possibilities, there are some use cases that just about any data team can adopt.

For instance, here’s how to dynamically create and populate tables on the fly using a Logic River. This method is perfect for automatically building tables from data sets in just a few clicks!

No More Hassle: Build Tables Dynamically Using Loop Through Variables in a Logic River

Logic Rivers are designed to orchestrate the ingestion and transformation of an entire data pipeline.

But Logic Rivers also have a few “tricks up their sleeve” that enable the development of user-friendly data processes.

One helpful trick: Logic Rivers can store data values in variables and use them throughout a workflow.

Customers can also use Containers in Rivery as a mechanism for looping or adding conditional logic to the river.

Now add those two methods together. This offers a truly dynamic way to develop a data pipeline, since the variable values themselves are recalculated every time the river runs.

As an example, let’s say a customer wants to loop through a list of values and create a corresponding database table for each value.

Consider this Google Adwords sample, which retrieves a geographical dimension table. The table has three fields: ID, Name, and Geo_Level.

The format of the data makes it harder to join on to a fact table, as the identifiers are all at different geographical levels (Province, Region, City, etc.). Instead, the ideal setup is to have a separate dimension table for each distinct Geo_Level.

But how can Rivery generate this in a dynamic way? Easy: by looping through a variable list!

To do this, customers must first create the variable list in a Logic River. Then:

• Add a SQL/Script Logic Step with a source query that returns a list of distinct geographical levels.
• Save these values into a variable.
• Use the REPLACE() function to remove whitespace.

Once the variable is created, it will appear in the Variables List. Make sure “Is Multiple Values” is checked.

Next, create the loop. First, add a new “Logic Step.” Then wrap it in a container and set the container to “Loop over.”

From the “for each value in _” dropdown, select the variable that was just created. Now, type in a different name to use for this iterator.

Next, enter a source query that calls the iterator using curly brackets. Use this twice – once in the WHERE clause, and once in the table name.

Now each iteration in the loop will create a new table and only contain the necessary data (‘dim_city’ table will only have cities, etc.).

Run the Logic River and watch all the tables appear! Results will show one table per Geo_Level, each containing the corresponding data for that Geo_Level.

And remember, the loop you just created is completely dynamic, based on a variable list created from the data itself. If a new GeoLevel is added to the dim_geo table, an additional dim_* table will be created without any manual intervention.

This is the power of Logic Rivers in Rivery.

Logic Rivers: Customizing and Clarifying Your Data

With Logic Rivers, customers can build data workflows for any use case.

Whether customers are dynamically creating tables like in the example above, or embedding some other critical logic, the feature puts the power of customization and incisiveness in the hands of our customers.

Logic Rivers are just another example of how data teams can build bespoke data pipelines using Rivery!