I’m looking to see what is the most efficient way of writing SQL in snowflake. Below is the problem statement.

1. Table A with events data, which has 3 columns X, Y. Table will have duplicate X values.
2. Table B with Mapping data, which has 2 columns X1, T
3. Snowflake UDF function which makes API call to generate value.
4. Input for function is value from X or X1 column and output value will be of T column.

Output could be new table C or Table A which consists of one extra column T with corresponding values to column X

Ideally if all mapping values available in mapping table B, just a left join between TABLE A AND TABLE B would be sufficient but in some cases only few mapping values exist in TABLE B, so i need to make an apply API call over distinct column X of TABLE A which doesn’t have mapping values in mapping table B.

Only bottle neck is i need to make as less as possible API calls, that is why using distinct function on column X and applying only ones which doesn’t have mapping value.

and also i need to update the mapping table B with new values.

TABLE A

Table B

UDF function taking each row value:
def some_functin(X):
……
return T

output tables:

Table B

I tried

1. Getting result of TABLE A LEFT JOIN TABLE B to get all mapped values (T columns) which are existing
2. Taking the above result and deriving another result set where T column is NULL and doing distinct on column X.
3. Calling UDF on above deduplicated result and getting Column T and updating MAPPING table with new T and X column values
4. Finally again doing LEFT join TABLE A AND TABLE B ( this time mapping table will have all X, T values)
5. planning to keep above sqls in an procedure and schedule it with task when ever new data arrives in TABLE A.

This doesn’t seem rite and trying to see if there is an efficient way to solve this.