Custom Variable Loop Execution

summary

Custom Variable Loop Execution is a feature that can be set when an ETL Configuration/Data Mart Configuration with embedded Custom Variables is embedded on a Workflow definition.
This page provides an overview of Custom Variable loop execution and its settings with use cases.

What is Custom Variable Loop Execution?

Custom Variable Loop Execution can be set to expand multiple values for a Custom Variable embedded in a task.
When a task with this Job Setting is executed in a Workflow Job, the process of "expanding the value to a Custom Variable and executing the Job in a manner that takes this into account" is repeated in turn.
As a result, multiple jobs can be executed in a single task run.

use case

Here are some typical use cases for Custom Variable loop execution.

Data Source Advertising System Connector to transfer data from multiple account IDs at once.

When multiple ad account IDs are used in an ad-based Connector, data from multiple ad account IDs can be transferred together.

Setting Example

When creating ETL Configuration, embed Custom Variables in the Ad Account ID.
Incorporate the above ETL Configuration into your Workflow Definition and set it to expand the list of ad account IDs to be forwarded to a Custom Variable.

Data Source File Storage System Connector to transfer a file with a specific date and time.

If the file name prefix or suffix contains a date and time, files containing the specified date and time can be transferred together.
Similarly, files under a folder containing a specific date and time can be transferred together.

Setting Example

Embed Custom Variables in the path prefix when creating ETL Configuration.
Incorporate the above ETL Configuration into your Workflow Definition and set it to expand the list of dates and times to be transferred into a Custom Variable.

Split and transfer large files from the Data Source database-based Connector.

When you want to transfer a large amount of data from a database-based Connector, you can split the file and transfer it.
By splitting the ETL Job into separate jobs, the transfer may be completed in less time than if the jobs were combined into a single job.

Setup procedure

Embed Custom Variables in the query when creating ETL Configuration.
For example, to split a column based on a column of type timestamp, the following WHERE clause would be used in the query

WHERE column_timestamp BETWEEN "$start_date$" AND "$end_date$"

Incorporate the above ETL Configuration into your Workflow Definition and set a Custom Variable to the date to be deployed so that the Job will be split any number of times.

Transfer a group of tables with the same schema from the Data Source Database Connector.

When a database-based Connector has multiple tables with the same schema, multiple tables can be transferred together.

Setup procedure

When creating an ETL Configuration, embed Custom Variables in the FROM clause in the query.
Incorporate the above ETL Configuration into your Workflow Definition and set it to expand the list of table names to be transferred into a Custom Variable.

Prevent sampling of data transferred from Google Analytics 4

Due to a specification on the API side of Google Analytics 4, sampling occurs when the size of the data to be acquired in Data Source Google Analytics 4 increases beyond a certain size.
Sampling acquisition can be avoided by dividing the data to be acquired.

Setup procedure

When creating an ETL Configuration, Custom Variables are embedded in the start and end dates of the Data Retrieval Period.
Incorporate the above ETL Configuration into your Workflow Definition and set a Custom Variable for the date to be deployed so that the Job is divided into small enough pieces that it is not sampled. (e.g., split in 1-day increments)

Using Data Mart Configuration, partition a table with a specific column as a key.

A table can be partitioned using specific columns contained in the table as keys.
For example, if you had a table containing column company_id with values such as Company A, Company B, Company C... and the table contains a column company_id with a value such as "Company A, Company B, Company C...", the table can be split into separate tables for each company.

Setup procedure

Custom Variables are embedded in the query and destination tables when creating Data Mart Configurations.
Below is an example query and destination table.

• Query Example

SELECT *
FROM
  <Ex-Table>
WHERE
  company_id == $companyId$

• Example of output destination table

$date$_$companyId$

Incorporate the above Data Mart Configuration into the Workflow Setting to expand the list of values contained in the key columns into Custom Variables.

In addition to the above, there are many other use cases for Custom Variable loop execution.
Contact Customer Success for more information.

Setup procedure

1. Create ETL Configuration and Data Mart Configuration. At this time, Custom Variables are embedded as appropriate.
   

2. Create a Workflow definition.

3. Incorporate Tasks (ETL Configuration and Data Mart Configuration) with Custom Variables embedded in them on the Flow Edit screen.
   

4. Click on the three-point reader > pencil icon on the right side of the task. The task edit screen will appear.
   

5. On the Edit Task screen, enable Loop Execution in Custom Variable and select the type of loop.
   

6. Select the target Custom Variable and set each accordingly.
   For details on each setting item, see "Setting Items for Each Loop Type" below.

7. Execute Workflow Job.
   Task jobs with Loop Execution enabled for Custom Variables are executed in a loop, with values being expanded in turn in the Custom Variable.
   The following is an execution log of a looped task job.
   

Setting items for each loop type

Loop with string expansion

Enter the string you wish to expand into a Custom Variable.
Click on Add Loop if you wish to add the string you wish to expand.
When looping, strings are expanded in the order in which they are entered.

hoge
fuga
piyo

Loop by period (relative specification)

Set the period, loop interval, and deployment settings accordingly.
Note that when a loop is executed, the date is expanded in the order of the Custom Variable output image.

Loop in query results

You can run a query on the DWH and expand the resulting values into Custom Variables.
Loops in query results are supported by the following DWHs.

• Amazon Redshift
• Google BigQuery
• Snowflake

The following screen capture is an example of "looping through BigQuery query results.