CODEWITHSUNDEEP
snowflake-cloud-data-platform
bfmcneill
bfmcneill

Reputation: 127

snowflake sql udtf join performance

Coming from SQLSERVER land I have really come to enjoy querying table value functions with the CROSS APPLY / OUTER APPLY pattern. It appears snowsql does not have that kind of command but I was able to replicate the functionality. Can you tell if I am going to have performance issues with how this is wired up?

TVF

create or replace function udf_user_friends(user_id varchar)
returns table(user_id varchar,friend_id varchar)
as
$$
  select 
      userjson:user_id::string as user_id
    , f.value::string as friend_id
  from yelp.user u,
    lateral flatten (input=>split(u.userjson:friends, ',')) f
  where userjson:user_id = user_id
$$;

Query that uses the udf

SELECT 
      u.userjson:user_id::string as user_id
    , t.friend_id
FROM yelp.user u
LEFT JOIN TABLE(udf_user_friends(u.userjson:user_id::string)) t;

Upvotes: 0

Views: 699

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

Fundamentally this seems rather wrong to me. Your UDTF is join to the table you already have data from.. so it appears to be the class of functions of "hiding the complexity" which also is the same class of problem called "hiding the performance".

I started writing your outer select with the zero value UDTF removed, to discover the result was the content of the UDTF.. sort of proving the UDTF is adding zero value.

SELECT 
    u.userjson:user_id::string as user_id
    ,f.value::string as friend_id
FROM yelp.user u,
lateral flatten (input=>split(u.userjson:friends, ',')) f

Things we use UDTF for is pushing filtering down when doing permissioning, where the optimizer sometimes does not see filters can get pushed down.

Upvotes: 2

Related Questions