how to dynamically set default value for those columns to 'NULL' in table 1 if exists in table 2 in snowflake

Question

I have profile and exclusion table in Snowflake and want to extract profile_id and profile_name as they are, while extracting transaction_count as 'null' as this column exists in exclusion table.

I want to do this dynamically for all columns in the profile table as columns names for profile table in exclusion table will change as per business requirement.

Example tables:

Profile:

profile_id	profile_name	transaction_count
1	priya	3

Exclusion:

table_name	column_name
profile	transaction_count

Is this possible to do in SQL? Or in a stored procedure?

Answer 1

You can build more dynamically your update sql stmt using anonymous block , right now I'm getting all columns from Information schema of table and using that to build column list .Use same column list for updating in TBL profile. In case if you want to your own table "Exclusion' which hold column list , simple change to you select query and makes work. Below is block of code:

    execute immediate $$
      declare
        accumulator varchar default '';
        res1 RESULTSET default (Select column_name   from "LIBRARY_CARD_CATALOG"."INFORMATION_SCHEMA"."COLUMNS" where table_schema='PUBLIC' and table_name='PROFILE' );  
        cur1 CURSOR for res1;     
        upd_stmt varchar;
      begin
        for row_variable in cur1 do
          accumulator := row_variable.column_name||'=NULL'||','||accumulator;
        end for;
        accumulator:=RTRIM(accumulator,',');
        upd_stmt:= 'update "LIBRARY_CARD_CATALOG"."PUBLIC"."PROFILE" set '|| accumulator||';';
        execute immediate  :upd_stmt;
         return upd_stmt;
      end;
    $$;

Answer 2

You can query the excluded columns and convert to an array, then, you can cast each field to NULL if the field name appears in that array.

Note: I realized, as I began solving, that my solution is verbose and not ideal. I'm assuming there's a better method, perhaps with JOINS or with a UDF. Leaving my answer here, because it does work, until someone replaces with a better solution.

Create a CTE for exclude columns and apply to query of your business table:

WITH EXCLUDE(EXCLUDE_COLUMNS) AS (
    SELECT
        TO_ARRAY(column_name) AS EXCLUDE_COLUMNS
    FROM
        exclusion
)
-- Now make your query
SELECT
    CASE
       WHEN 
           'profile_id' IN EXCLUDE.EXCLUDE_COLUMNS
       THEN
           profile_id
       ELSE NULL
    END AS profile_id,
    ....  -- Continue for each column
FROM
    profile