Using External Functions in a CTE on Snowflake

Is it possible to call an external function that is integrated with an API within a CTE (Common Table Expressions) within Snowflake?

Please see the example below for what I'm trying.

with question_batch as (
    select *
    from (
        values('who are you?')
            ,('what are you?')
            ,('how are you?')
        ) as t(question)
    )
select 
    qb.question
    ,external_api(qb.question) as response
from question_batch qb;

I get the following error when attempting to call the API as shown in the code:

> Error: "data" JSON array must contain exact number of rows as the
> request. Response batch expected to have 3 rows, but received 1 rows.

I can see in the API logs that the API is called successfully for the first question and it returns a successful response.

The issue you're encountering is not caused by the CTE, but rather by how Snowflake utilises external functions. Snowflake optimises performance by calling external functions with batches of rows from your query, instead of calling them row by row.

For your example code, the request body that your external function received would have looked something like this:

{
  "data": [
    [0, "who are you?"],
    [1, "what are you?"],
    [2, "how are you?"]
  ]
}

The request body is a simple JSON object with a single key named data, which contains a JSON array representing a batch of rows (rowset) from the query that uses the function. Each element in the array is itself a JSON array, with a row index followed by the actual arguments from the function call.

Your external function needs to iterate over each row element and provide a result for each row individually. For instance, if your external function makes API calls, it would make those calls separately for each row.

The error you encountered suggests that your response only included the result for a single row, whereas Snowflake expected a response for each of the 3 rows. Here's an example of how the response should look:

{
  "data": [
    [0, "Quins"],
    [1, "I'm a human"],
    [2, "I'm brilliant!"]
  ]
}

In this response, each row is again represented as a JSON array, where the first element is the index of the corresponding row from the request, followed by the result for that row.

For more detailed information, you can refer to the Snowflake documentation:

https://docs.snowflake.com/en/sql-reference/external-functions-data-format