--- title: "Custom Variable Loop Execution" slug: "loop-using-custom-variables" updated: 2025-07-28T04:45:05Z published: 2025-07-28T04:45:05Z canonical: "documents.trocco.io/loop-using-custom-variables" --- > ## Documentation Index > Fetch the complete documentation index at: https://documents.trocco.io/llms.txt > Use this file to discover all available pages before exploring further. # 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**. About Custom Variables and Workflow Definitions See below for an overview of each and details on how to set them up. - [About Custom Variables](/docs/custom-variables) - [About Workflow Definitions](/docs//about-workflow) ## 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 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. ![image.png](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/loop-using-custom-variables-2024-08-29-7-5-0.png) Value of a Custom Variable of type String To embed a Custom Variable of type string, enter a **value**. This value is overwritten by the value that is expanded during loop execution. Therefore, **this value is not used during loop execution. Please note** 1. Create a Workflow definition. 2. Incorporate Tasks (ETL Configuration and Data Mart Configuration) with Custom Variables embedded in them on the Flow Edit screen. ![image.png](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/loop-using-custom-variables-2024-08-29-7-5-1.png) 3. Click on the three-point reader > pencil icon on the right side of the task. The task edit screen will appear. ![image.png](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/loop-using-custom-variables-2024-08-29-7-5-2.png) 4. On the Edit Task screen, enable **Loop Execution in Custom Variable** and select the **type of loop**. ![image.png](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/loop-using-custom-variables-2024-08-29-7-5-3.png) 5. Select the **target Custom Variable** and set each accordingly. For details on each setting item, see "Setting Items for Each Loop Type" below. 6. 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. ![image.png](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/loop-using-custom-variables-2024-08-29-7-5-4.png) ### 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. ![image.png](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/loop-using-custom-variables-2024-08-29-7-5-5.png) Copy and paste for batch input The following text can be batch-entered by copying and pasting ``` 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-using-custom-variables-2024-08-29-7-5-6](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/loop-using-custom-variables-2024-08-29-7-5-6.png) #### 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. ![image.png](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/loop-using-custom-variables-2024-08-29-7-5-7.png)