All Products
Search

This Product

    Platform For AI:Time window SQL

    Document Center

    Platform For AI:Time window SQL

    Last Updated:Apr 01, 2026

    Backfilling historical data typically requires a loop that runs the same SQL query once per day, which adds orchestration logic to every pipeline. Time Window SQL eliminates that loop: it runs the same SQL query against each day in a specified date range in parallel, spawning one subtask per date automatically. A common use case is extracting daily feature data from the past seven days to backfill a recommendation engine.

    How it works

    When you run a pipeline with Time Window SQL, the component spawns one subtask per date in the execution time window. Each subtask receives the same SQL script, but the system variable ${pai.system.cycledate} resolves to a different data date for each subtask — the date whose data the subtask processes, not the time the subtask actually runs.

    For example, with Business base date 20230210 and Execution time window (-4,-2],0, three subtasks run in parallel:

    Subtask${pai.system.cycledate} (with yyyyMMdd format)Data processed
    120230207Feb 7, 2023
    220230208Feb 8, 2023
    320230210Feb 10, 2023

    Feb 9 (offset -1) is not included because the window (-4,-2] covers offsets -4 through -2 (inclusive on the right), and 0 adds the base date itself.

    Limitations

    • Multi-date loop execution supports day-level data backfill only. Finer-grained intervals (hourly, weekly) are not supported.

    • Before using Periodic Scheduling to schedule your pipeline in production, disable multi-date loop execution. Leaving it enabled causes the component to generate extra backfill subtasks on every scheduled run.

    • The Maximum number of concurrent setting on the Parameters Setting tab applies only to the node where it is configured. If your pipeline has multiple Time Window SQL nodes, account for the total concurrency supported by your project's resources.

    Usage notes

    Time Window SQL accepts up to four inputs and produces one output. Keep the following in mind.

    Input and output variables

    When you connect an upstream component to Time Window SQL, the input port you select determines the variable name mapped to that input table. Reference input tables in your SQL script using ${t1}, ${t2}, ${t3}, and ${t4}. Reference the output table using ${o1}.

    Lifecycle variable

    Use ${lifecycle} in your SQL script to get the lifecycle setting for temporary tables in the workspace. The default is 28 days. To change this value, see Manage workspaces.

    Configure the component in Machine Learning Designer

    Open your pipeline in Machine Learning Designer and configure the following parameters.

    ParameterDescription
    Business base dateThe anchor date for the time window. Enter the date directly (for example, 20230210) or reference a global variable. See Global variable.
    Whether to open multi-date loop executionEnabled by default. When disabled, the component behaves the same as the SQL script component and runs a single SQL execution.
    Execution time windowSpecifies which dates to process, expressed as offsets relative to Business base date. Accepts integers and half-open intervals, comma-separated. The system generates one subtask per date. Up to 100 subtasks can run per pipeline execution. See Time window syntax below.
    Maximum number of concurrentMaximum number of subtasks that run simultaneously. Keep this value low to avoid resource contention.
    Date formatControls the format of ${pai.system.cycledate}. Options: yyyyMMdd (default), yyyy-MM-dd, yyyy/MM/dd.
    Whether the system adds a create table statementWhen enabled, the last statement in your SQL script must be a SELECT statement. The system creates a temporary table to store the results automatically. When disabled, create the output table ${o1} explicitly in your SQL.
    SQL ScriptThe SQL query to run for each date. Use ${pai.system.cycledate} where you need the data date. Other variables (${t1}–${t4}, ${o1}, ${lifecycle}) are also available.

    Time window syntax

    The Execution time window value is a comma-separated list of offsets and half-open intervals relative to the business base date. Positive integers are future offsets; negative integers are past offsets.

    SyntaxMeaningDates generated (base date: 20230210)
    (-4,-2],0Three through two days ago, plus base date20230207, 20230208, 20230210

    Bracket reference:

    • [ / ] — inclusive bound

    • ( / ) — exclusive bound

    SQL script example

    With Business base date 20230210, Execution time window (-4,-2],0, and Date format yyyy-MM-dd, the following SQL script runs three times:

    SELECT * FROM ${t1} WHERE dt = ${pai.system.cycledate}

    The three subtasks execute:

    -- Subtask 1
SELECT * FROM ${t1} WHERE dt = 2023-02-07

-- Subtask 2
SELECT * FROM ${t1} WHERE dt = 2023-02-08

-- Subtask 3
SELECT * FROM ${t1} WHERE dt = 2023-02-10
    Tip: To validate your time window configuration before a full run, set Execution time window to 0 (base date only). This runs a single subtask so you can verify your SQL logic and variable substitution without processing the full date range.

    What's next