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.
Got feedback or spotted something we can improve?
We'd love to hear from you. Join the conversation in the Documentation forum!