Passthrough functions

AggVariant

View as Markdown

The AggVariant function calls a warehouse aggregate function that returns a Variant data type. This function is the aggregate version of CallVariant and can be applied to identify distinct values from a lower grouped level in the parent/higher level grouping of a table.

AggVariant is a passthrough function.

The functions that can be used with a passthrough function depend on your connected data platform’s native SQL functions. For example, on Snowflake connections, browse the Snowflake SQL function reference to find functions that return a value that matches the datatype of the passthrough function.

With AggVariant, you can use a function that returns variant data.

This function isn’t compatible with all data platform connections. To check if your connection supports it, see Supported data platforms and feature compatibility.

Syntax

AggVariant(function name, arguments...)

Function arguments:

  • function name (required): The name of an aggregate function supported by your data warehouse.
  • arguments (required): One or more arguments to be passed to the warehouse function. All arguments must meet the warehouse function’s input requirements.

Example

A table contains an ARRAY_UNIQUE_AGG column that returns an array containing all the distinct customers who purchased at least one or more items per Product Type. You can pass Snowflake’s ARRAY_UNION_AGG function to the AggVariant function to identify all distinct customers who made at least one purchase that week.

The ARRAY_UNION_AGG function takes in one column containing the arrays with distinct values as produced by Snowflake’s ARRAY_UNIQUE_AGG function.

AggVariant("ARRAY_UNION_AGG", [ARRAY_UNIQUE_AGG])
  • Return an array that contains the union of distinct customers from the input arrays in the ARRAY_UNIQUE_AGG column.