Lead/Lag
The Lead/Lag transformation component retrieves values from table rows preceding or following the current row.
- The LEAD function returns the values for a row at a given offset after the current row in the partition.
- The LAG function returns the values for a row at a given offset before the current row in the partition.
In SQL terms, these are equivalent to using the LEAD ... OVER or LAG ... OVER clauses of a SELECT statement.
You can combine multiple LEAD and/or LAG operations in a single instance of the component, letting you obtain a range of offset data from the input dataset.
The component uses the window functions supported by your cloud data warehouse. Window functions operate on a partition or "window" of a dataset. More details of these functions can be found in your cloud data warehouse documentation:
Use case
Because Lead/Lag allows you to look forward or backward at other rows in a dataset, it's helpful in time series and analytical processing. Some common uses for this include:
- Calculating changes over time. For example, using LAG to obtain the difference between the current day and the previous day when days are expressed as different rows in the dataset.
- Detecting trends and identifying outliers. For example, using LEAD to obtain this month's spending and compare it to next month's, and flagging instances where the trend is downward.
- Building cumulative or rolling metrics. For example, using multiple LAG functions to obtain data from one day ago, two days ago, and so on.
Properties
Name = string
A human-readable name for the component.
Include Input Columns = boolean
When Yes, the component passes all input columns into the output.
Partition Data = dual listbox
Defines how the input data is partitioned to perform the rank calculation. The calculation is then performed on each partition.
Orderings Within Partitions = column editor
- Input Column: The input column name for sorting within the partitioned data. You can drag columns to reorder them.
- Ordering: The order of the sorting: Ascending, Descending, Nulls First, or Nulls Last.
Functions = column editor
- Window Function:
- Lead: Returns the column from an Offset number of rows after the current row. For more information, read the following documentation:
- Lag: Returns the column from an Offset number of rows before the current row. For more information, read the following documentation:
- Input Column: The name of the input column that the Lead/Lag function will retrieve data from.
- Offset: The number of rows to look forward (Lead) or backward (Lag) in the partition.
- Output Column: The name of a column that will be created in the output dataset for the retrieved Lead/Lag data to populate.
Ingore Nulls = boolean
When Yes, disregard null values when determining which row to use. Null values don't count toward reaching the offset.