Backend specific considerations

Differences in implementation

Different backends have different implementations for the same function, resulting in different outputs.

For example, ARRAY_UNIQUE returns sorted arrays in TRINO but not in DuckDB...

We have then created some safe function that return the same output for all backends, to the price of some performance.

Inline data through BIND_SQL and EXTERNAL_SQL

For functions that embed a SQL query to execute directly on the target system:

• BIND_SQL
• EXTERNAL_SQL
• EXTERNAL_VIEW

the query syntax is different for each backend.

Standard SQL

Use for single feature:

SELECT FEATURE1 FROM UNNEST(ARRAY[1,2,3]) AS t(FEATURE1)

Use for multiple features:

SELECT FEATURE1, FEATURE2 FROM (VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C')
) AS t(FEATURE1, FEATURE2)

BigQuery

Use for single feature:

SELECT * FROM UNNEST([1,2,3]) AS FEATURE1

Use for multiple features:

SELECT AS STRUCT customer_id, attribute
FROM UNNEST(CAST([
    STRUCT(1, 'attribute'),
    STRUCT(2, 'attribute'),
    STRUCT(3, 'attribute'),
] AS ARRAY<STRUCT<customer_id INT64, attribute STRING>>))

DuckDB

An alternative syntax for DuckDB is to use the UNNEST function with the recursive option:

SELECT UNNEST(ARRAY[
    (1, 'A'),
    (2, 'B'),
    (3, 'C')
]::ROW(FEATURE1 BIGINT, FEATURE2 VARCHAR)[], recursive:=true)