First/Last

Project Availability:

Snowflake ✅ Databricks ✅ Amazon Redshift ✅

Component Type:

Transformation

Connection Inputs:

One

Connection Outputs:

Unlimited

The First/Last transformation component lets you create groups of data, sort these groups, and then return only the first or last row of each group. This component is equivalent to writing a SELECT statement with a window function in-line using the OVER keyword.

For more information, read the following documentation:

Snowflake	Databricks	Amazon Redshift
Snowflake Window functions	Databricks Window functions	Amazon Redshift Window functions
Snowflake First Value	Databricks Window function First Value	Amazon Redshift Window function First Value
Snowflake Last Value	Databricks Window function Last Value	Amazon Redshift Window function Last Value

Use case

This component can be used to simplify workflows that rely on identifying the start or end points of a time period or sequence of events. For example, you can use it to:

Group purchase data by customer, sort by purchase date, then use First to find the details of each customer's first purchase.
Group user logins by user, sort by most recent timestamp, then use Last to see when your users last logged in.
Group monthly inventory data by time period, then use two First/Last components to view your inventory levels at the start and end of each month.

Properties

Name = string

A human-readable name for the component.

Grouping Columns = dual listbox

Defines how the input data is grouped. This works like an SQL GROUP BY statement. The first or last element of each group will be selected.

Ordering within partitions = column editor

Input Column: The name of the column to use for sorting within the grouped data.

Ordering: The method of sorting on the chosen column. Options are Ascending, Descending, Nulls First, or Nulls Last.

First/Last Columns = column editor

Column: The name of the input column to be passed to the output.
First Last: Return the First or Last element of the chosen column in the grouped data.

Click the Add all button to automatically populate the Column list with all the columns which were not selected in the Grouping Columns property. First Last is set to First in each row. You can then selectively edit the list if required, but if you click Add all again it will return the list to the default fully populated state, removing any changes you have made.

Ignore Nulls = boolean

Select whether to ignore null values. The default setting is No (do not ignore null values).