• Snowflake is a versatile cloud-based data warehouse many organizations use for data management and analytics. It supports various data types, including date, time, and timestamp. Using Snowflake date functions, you can construct, convert, extract, and modify such data types.
• To perform mathematical operations like sum or average, you can use aggregate functions that accept multiple rows as input and give a single output.
• `MAX` is one such aggregate function in Snowflake, which, when applied to date data types, gives you the most recent date from your dataset.

This article comprehensively explains the Snowflake `MAX` date operations through various example use cases.

## What is Snowflake MAX?

• `MAX` is a function in Snowflake that returns a maximum value when you apply it to any expression in a data record. It ignores the NULL values unless all the values of your data record are NULL, in which case it returns a NULL value.
• The `MAX` function comes under an aggregate as well as a window function in Snowflake. An aggregate function accepts multiple rows as an input and gives a single output.

The window function operates on a group of related rows called windows to return one output row for each input row. The syntaxes of `MAX` for aggregate and window functions are as follows:

• Aggregate Function Syntax:
``MAX( <expr> )``
• Window Function Syntax:
``MAX( <expr> ) [ OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ <window_frame> ] ] ) ]``

Here is an example of how to use `MAX` in Snowflake:

• First, create a table in Snowflake and add data to it using the following code:
``````CREATE OR REPLACE TABLE sample_table(k CHAR(4), d CHAR(4));

INSERT INTO sample_table VALUES

('1', '1'), ('1', '5'), ('1', '3'),

('2', '2'), ('2', NULL),

('3', NULL),

(NULL, '7'), (NULL, '1');``````
• To display the table, use the command below:
``````SELECT k, d

FROM sample_table

ORDER BY k, d;``````
``````Output:

+------+------+

| K    | D    |

|------+------|

| 1    | 1    |

| 1    | 3    |

| 1    | 5    |

| 2    | 2    |

| 2    | NULL |

| 3    | NULL |

| NULL | 1    |

| NULL | 7    |

+------+------+``````
• Now, apply the `MAX` function to get the largest value in the column named d using the following code:
``SELECT MAX(d) FROM sample_table;``
``````The output displays the largest value in the column.

+--------+

| MAX(D) |

|--------|

| 7      |

+--------+``````

## What is Snowflake Max Date?

You can use the `MAX` function in Snowflake to get the latest date from your datasets. Consider the example below on how to find the maximum date in Snowflake. `Dateandtime1` is a table containing a column named `Date_updated`. To find the max date in Snowflake from this table, you can use the code given below:

``````SELECT MAX(Date_updated) AS max_date
FROM Dateandtime1;``````

The query gives the output as 2014-03-25, the latest date, as seen in the `Date_updated` column above.

You can use the `MAX` function to understand the recent activities of your target users or customers. It enables you to find the date on which they last purchased your product or when they logged in to explore your website. Using the output from the function gives you information about your customers’ actions and preferences regarding your products and services. You can apply insights from the `MAX` function in education, finance, human resources, and supply chain management.

## Use Cases of Snowflake Max Date Function with Examples

Here are some example use cases of Snowflake `MAX` date functions:

1. Using `MAX` to Find the Latest Date Per Group

The `MAX` function can help you extract the latest date from each group of the dataset. You can use it to analyze retail sales data by finding the latest purchase date of each customer. Banks can also use the `MAX` function to find the latest transaction date for each account and detect dormant accounts.

Here is an example query to find the most recent date a customer placed an order. It uses the `customer_id column` in a table named `orders`.

``````SELECT customer_id, MAX(order_date) AS latest_order_date

FROM orders

GROUP BY customer_id;``````
1. Finding the Latest Date From Multiple Date Columns

The `MAX` function enables you to find the latest date from multiple columns containing date data types in any dataset. You can use it in the health sector to keep track of all the important dates in patients’ data records, such as the last appointment date, last lab test date, and last medication refill date. The output from the function allows you to provide continuous and timely healthcare services to your patients.

In the example below, `MAX` helps you to find recent dates from the columns date1, date2, and date3.

``````SELECT MAX(GREATEST(date1, date2, date3)) AS latest_date

FROM table1;``````

The `GREATEST` function is used to get the largest value from the list of expressions, which in this case is <date1, date2, date3>. It gives NULL as an output if any values across the list are NULL. The major difference between `MAX` and `GREATEST` is that `MAX` is an aggregate function while `GREATEST` is not an aggregate function.

1. Finding the Latest Date From the Date and Time Columns

You can select the maximum time using the Snowflake `MAX` date function. It finds applications in customer relationship management (CRM), human resources, healthcare, and financial services. For instance, as an HR, you can use the Snowflake `MAX` date function to access the login times of all employees for attendance management.

Once again, consider the example of the `Dateandtime1` table containing columns `Date_updated` and `Time_updated` with date and times data type, respectively, as shown in the table below:

Executing the following query will give you the output as 2014-03-25, 20:13:27.0000000.

``````SELECT Date_updated,Time_updated

FROM Dateandtime1  WHERE Time_updated
IN(SELECT MAX(Time_updated) FROM Dateandtime1 WHERE Date_updated IN(SELECT        MAX(Date_updated) FROM Dateandtime1))``````
1. Using the `MAX` Function in Snowflake to Select a Max Date From the Date Array
• The `MAX` function and the `LATERAL FLATTEN` function in Snowflake can help you select the latest date from a column containing arrays of dates.
• You can use it for inventory and order tracking in supply chain management. For instance, suppose your inventory dataset consists of a column named `restocking_date` containing all the dates when products were restocked in an array.
• You can use the `MAX` function to track the latest date a particular product was restocked in your inventory.
• The `MAX` function can also be used in e-commerce to analyze customer login history.
• In the financial sector, you may store multiple transaction dates for a particular account number in an array. You can use the `MAX` function to find the date of the latest transaction of any account from this dataset.

Here is a simple example query for selecting a maximum date from an array of dates. Consider a table named t1 containing dates in the form of arrays, as shown below:

You can use the following query to retrieve the maximum date value from the `alldate` column containing dates in array form:

``````select

distinct

col_1

, col_2

from

t1

, lateral flatten(input => date_array) g

qualify

max(max_date) over () = g.value;``````

The output will be 2021-02-13. You can also use the Snowflake qualify max date function, as shown below:

``````select

distinct

col_1

,col_2

from

t1

qualify

array_contains(max(max_date) over () :: variant, date_array);``````
1. Handling NULL Values
• You can use `MAX` with the `COALESCE` function if you have any NULL value in your dataset’s date data type column. The `COALESCE` function gives first non-NULL values from the arguments or gives NULL only if all the arguments have a NULL value.
• When used with the `MAX` function, the `COALESCE` gives another specified value for the NULL values in the date data type column.
• You can use the `MAX` function with `COALESCE` to detect missing values in datasets. For instance, while analyzing customer interaction data for CRM, you may find that some values are missing because of data entry errors.
• You can use `MAX` and `COALESCE` functions to handle NULL values and retrieve the last interaction of such customers. In school records, you can use it to find if any student has missed the latest sports activity or assignment.

Consider an earlier example of the `Dateandtime1` table containing column `Date_updated` with some NULL values. To find the latest date values from the `Date_updated` column, you can use the `COALESCE` with the `MAX` function in the following way:

``````SELECT MAX(

COALESCE(Date_updated, '1999-01-01')

) AS max_date

FROM Dateandtime1;``````

Here, the `COALESCE` functions check the `Date_updated column` and replace any NULL value with the default value of 1999-01-01 while processing. If all the non-NULL values are less than the default date value specified, then the `MAX` function will return the latest date from all the non-NULL date values.

If all the values of date type columns are NULL, the `MAX` function returns the default date value as the maximum date value.

## What is MAX_BY?

In Snowflake, select a row with a max date in a column using the `MAX_BY` function. It will give you the value of another column in the same row as an output.

The syntax for the `MAX_BY` function is as follows:

``MAX_BY( <col_to_return>, <col_containing_maximum> [ ,<maximum_number_of_values_to_return> ] )``
• `col_to_return` is a column containing the value to return.
•  `col_containing_maximum` is the column containing the maximum value.
• maximum_number_of_values_to_return is optional and mentions an integer specifying the number of values to return. It gives as an output an ARRAY containing values of the same type as `col_to_return`. The values in the ARRAY are arranged by their corresponding `col_containing_maximum` values. If you do not specify the number of values as return, it gives the value of the same type as `col_to_return`

`MAX_BY` does not output NULL if the column_containing_maximum has a mix of NULL and non-NULL values. It gives NULL only if all the column values are NULL.

You can use `MAX_BY` to identify a customer’s new purchase based on the latest purchase date. It can also be used in the finance sector to determine the latest transactions made by any account holder.

Consider an instance where you want to use `MAX_BY` on a date data type in Snowflake. Here is an example:

``````+-------------+------------+-------------+

|          ID |       DATE | STATUS      |

|-------------+------------+-------------+

|         101 | 2021-04-03 | STATE1      |

|         101 | 2022-05-19 | STATE2      |

|         101 | 2022-03-21 | STATE3      |

|         101 | 2020-01-18 | STATE4      |

|         102 | 2019-08-09 | STATE5      |

|         102 | 2020-01-03 | STATE6      |

|         102 | 2022-07-05 | STATE7      |

+-------------+------------+-------------+``````

You can use the below query to apply the `MAX_BY` function:

``````SELECT ID, MAX_BY(STATUS, DATE)

FROM myTable

GROUP BY ID``````

The output will be as follows:

``````+-------------+-------------+

|          ID | STATUS      |

|-------------+-------------+

|         101 | STATE2      |

|         102 | STATE7      |

+-------------+-------------+``````

## Role of Hevo Data in Snowflake MAX Date

To seamlessly use the Snowflake `MAX` date function, you should convert your date data type into a consistent format. To achieve this, you can use a versatile data integration tool like Hevo Data, which contains several effective features to standardize your data.

Hevo Data is a no-code ELT platform providing real-time data integration and a cost-effective way to automate your data pipeline workflow. With over 150 source connectors, you can integrate your data into multiple platforms, conduct advanced analysis on your data, and produce useful insights.

Here are some of the most important features provided by Hevo Data:

• Data Transformation: Hevo Data allows you to transform your data for analysis with simple Python-based and drag-and-drop data transformation techniques. These transformations can clean and convert your data into a uniform or well-defined form.
• Automated Schema Mapping: Hevo Data automatically arranges the destination schema to match the incoming data. It also lets you choose between Full and Incremental Mapping. The auto schema mapping feature ensures that the structure of date data in the source system aligns with the date structures accepted by Snowflake. It reduces the need to put manual efforts into schema mapping, saves time, and ensures data integrity.
• Incremental Data Load: The incremental data loading feature enables you to keep your Snowflake data updated in accordance with the changes in the source. It ensures proper bandwidth utilization at both the source and the destination by allowing near real-time data transfer of the modified data. You can leverage this feature to apply the Snowflake MAX function to the latest data and gain valuable insights for your business.

ROW_NUMBER function in snowflake

## Conclusion

This blog gives an in-depth overview of the Snowflake MAX Date function. It explains the various scenarios where you can use this function to get the latest date information data type. You can use third-party integration tools like Hevo Data to standardize your data from any source to Snowflake before querying for maximum date values. The extensive connector library, zero-code data pipeline building, data transformation, and security features make it one of the best available integration tools. You can schedule a demo today to gain an advantage from such versatile features!

## FAQs

1. What is the valid date range for Snowflake?
• The valid date range for Snowflake is between the years from 1582 and 9999. It accepts some years beyond this range, but due to the limitations of the Gregorian calendar, you should avoid using years before 1582.
1. What is the maximum timestamp in Snowflake?
• A timestamp data type is used to store date and time values. In Snowflake, the timestamp data type offers a time precision of 0 to 9 nanoseconds, while the date ranges from 1582 to 9999 years.
• So, the maximum timestamp accepted by Snowflake may consist of 9999 as a year with a time precision of up to 9 nanoseconds.
Customer Experience Engineer, Hevo

Sarthak is a skilled professional with over 2 years of hands-on experience in JDBC, MongoDB, REST API, and AWS. His expertise has been instrumental in driving Hevo's success, where he excels in adept problem-solving and superior issue management. Sarthak's technical proficiency and strategic approach have consistently contributed to optimizing operations and ensuring seamless performance, making him a vital asset to the team.

Category