[Analytics Engineering] NULL-safe GREATEST and LEAST now available in Snowflake
Ali, Saqib docbook.xml at gmail.com
Thu Mar 28 04:22:52 UTC 2024
Snowflake now supports GREATEST_IGNORE_NULLS and LEAST_IGNORE_NULLS. In the past if you used LEAST or GREATEST on a list of expressions that contained NULL these functions always returned NULL, which was very problematic. You had to do something like COALESCE(GREATEST(BILL_DATE, SHIP_DATE), SHIP_DATE, BILL_DATE) to correctly handle NULLs. This was cumbersome. Now you can simply use GREATEST_IGNORE_NULLS or LEAST_IGNORE_NULLS. For e.g.
ORDERS_FACT ╔══════════════╤════════════╤════════════╗ ║ ORDER_NUMBER │ BILL_DATE │ SHIP_DATE ║ ╠══════════════╪════════════╪════════════╣ ║ 111 │ 2023-01-01 │ 2023-01-05 ║ ╟──────────────┼────────────┼────────────╢ ║ 222 │ 2023-01-01 │ NULL ║ ╚══════════════╧════════════╧════════════╝
SQL Query: select order_number , greatest_ignore_nulls(bill_date, ship_date) as last_activity_date from orders_fact;
Query Output: ╔══════════════╤════════════════════╗ ║ ORDER_NUMBER │ LAST_ACTIVITY_DATE ║ ╠══════════════╪════════════════════╣ ║ 111 │ 2023-01-05 ║ ╟──────────────┼────────────────────╢ ║ 222 │ 2023-01-01 ║ ╚══════════════╧════════════════════╝
- Previous message (by thread): [Wranglers] Datavault4dbt v 1.3.0 now available
- Next message (by thread): [Wranglers] NULL-safe GREATEST and LEAST now available in Snowflake