Analytical functions are at the core of cloud data warehousing.
With DLH.io we use a number of these across our functions to give customers greater speed and flexibility consolidating their data. The analytical functions provide smoother code than lower level (joins, etc.) logic that may get to the same answer but perhaps uses more compute.
It’s hard to believe that AWS just got functionality for MERGE (and also, OLAP, CUBE, and GROUPING SETS) in 2022. Well it was in public preview in June 2022 based on this article, https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-sql-capabilities-speed-data-warehouse-migrations-preview/
Having the MERGE SQL command give an easier way to conditionally insert, update, and delete from target tables based on existing and new source data. And, of course, it uses the internal database logic to optimize what otherwise you would have to code by hand to provide the matching logic which could be sub-optimal.
The multi-dimensional or analytical functions help to drive large datasets into quickly processed insights which can then be stored inside of Redshift along with other relational data.
DLH.io takes advantage of the Redshift MERGE statement when doing incremental loads of data from other databases like PostgreSQL, SQL Server, Salesforce.com, etc. in order to effectively provide delta records instead of having to bulk load data or have missing or duplicate records.
