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

string
required
A human-readable name for the component.
boolean
required
When Yes, the component passes all input columns into the output.
object selector
Select columns to partition the data by. The calculation is then performed on each partition. If you select multiple columns, the data is partitioned by the unique combination of values across all selected columns.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.
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.
Toggle Text mode on 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. For more information, read Grid variables.
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.
Toggle Text mode on 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. For more information, read Grid variables.
boolean
required
When Yes, disregard null values when determining which row to use. Null values don’t count toward reaching the offset.