Using the Spread Operator in the WHERE clause:

create or replace table customers (id int, name string);
INSERT INTO customers (id, name) VALUES
  (1, 'Saqib'), (2, 'Ali'), (3, 'Uroosa')
  , (4, 'Maryam'), (5, 'Fatima'), (6, 'Alia') ;

-- We want the customer IDs 1, 3 and 6, where these ids are in an array i.e. [1,3,6]
-- Now we can use the Spread Operator in the WHERE clause as following

SELECT *
FROM customers
where ID IN (** [1, 3, 6]) --> Expanding an ARRAY [1,3,6] using the Spread Operator;

Output:

User Based Access Control (UBAC) is now available in Snowflake.

here is an example of how to use the spread operator in a UDF:

CREATE OR REPLACE FUNCTION udf__multiply(a NUMBER DEFAULT 1, b NUMBER DEFAULT 1, c NUMBER DEFAULT 1, d NUMBER DEFAULT 1)
RETURNS NUMBER
AS
  $$
    a * b * c * d
  $$
;

-- Spread Operator to convert the array into individual arguments
SELECT udf__multiply(**[1, 2]);
-- Returns: 2

SELECT udf__multiply(**[1, 2, 8]);
-- Returns: 16

SELECT udf__multiply(**[1, 2, 10, 10]);
-- Returns: 200

For this SQL query:

SELECT component_id, COUNT(*)
FROM ticketing_system_table
WHERE
  assignee_user.email = 'username@email.com'
  AND status IN ('NEW', 'ASSIGNED', 'ACCEPTED')
GROUP BY component_id
ORDER BY component_id DESC;

The Pipe query alternative would look like this:

FROM ticketing_system_table
|> WHERE
    assignee_user.email = 'username@email.com'
    AND status IN ('NEW', 'ASSIGNED', 'ACCEPTED')
|> AGGREGATE COUNT(*)
   GROUP AND ORDER BY component_id DESC;

Pipe query syntax