Skip to main content
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
required
A human-readable name for the component.
​
Include input columns
boolean
required
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.To use grid variables, toggle Use Grid Variable on at the top of the dialog. For more information, read Grid variables.
​
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.
Click the Text mode toggle at the bottom of the dialog to open a multi-line editor that lets you add items in a single block. For more information, read Text mode.To use grid variables, toggle Use Grid Variable on at the bottom of the dialog. For more information, read Grid variables.
​
Functions
column editor
required
  • Window Function:
    • Lead: Returns the column from an Offset number of rows after the current row. For more information, read the Snowflake Lead documentation.
    • Lag: Returns the column from an Offset number of rows before the current row. For more information, read the Snowflake Lag 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.
Click the Text mode toggle at the bottom of the dialog to open a multi-line editor that lets you add items in a single block. For more information, read Text mode.To use grid variables, toggle Use Grid Variable on at the bottom of the dialog. For more information, read Grid variables.
​
Ignore nulls
boolean
required
When Yes, disregard null values when determining which row to use. Null values don’t count toward reaching the offset.