Stored procedure with delete based on if else

Archive
1

Hi Community,

I have a stored procedure(SP) in our leagy system, which we migrated to snowflake.
The SP has the following steps:

Step 1: variable_a = Select from table_A
step 2: variable_b = Select from table_B
step 3: Conditional Construct
                if count( table_A > 10) then
                         INSERT INTO table_C
                         select ......from table_A join table_B
                else
                        DELETE FROM table_C

If I want to implement the same in dbt, I was planning to refactor it by having table_A and table_B as ephemeral models and maybe insert the data in table_C

The problem i have is with delete that should happen based on the condition, Can someone please help me achieve this delete by using conditional construct

Below is the code snippet with sample data for SP in snowflake

execute immediate $$
declare
    tab_a RESULTSET;
    tab_b RESULTSET;
    cnt_a integer;
    cnt_b integer;
    cnt_c integer;
begin
    tab_a := (CREATE OR REPLACE TEMPORARY TABLE TAB_A AS (SELECT SA.SALES_AGENT_ID, SA.NAME AS AGENT_NAME,SA.SALEC_COMMISION_PCT FROM SALES_AGENT AS SA));
    tab_b := (CREATE OR REPLACE TEMPORARY TABLE TAB_B AS (SELECT ORD.PURCHASE_AMT,ORD.SALES_AGENT_ID,CUST.CUSTOMER_ID,CUST.CUST_NAME FROM ORDERS ORD INNER JOIN CUSTOMER CUST ON CUST.CUSTOMER_ID=ORD.CUSTOMER_ID));
    cnt_a := (select count(*) from TAB_A);
    cnt_b := (SELECT COUNT(*) FROM TAB_B);
    cnt_c := (select count(*) from TAB_C);
        if (cnt_a > 5) then
        /*logic to insert data into a new table tab_c from tab_a and tab_b created above*/
        INSERT INTO VATSAV_DB.SALES_SCHEMA.TAB_C SELECT A.SALES_AGENT_ID,B.CUSTOMER_ID,A.AGENT_NAME,B.CUST_NAME,SUM((A.SALEC_COMMISION_PCT*B.PURCHASE_AMT)::number(32,2))COMMISION_EARNED FROM TAB_A AS A INNER JOIN TAB_B AS B ON A.SALES_AGENT_ID=B.SALES_AGENT_ID  GROUP BY 1,2,3,4;
        return 'ONLY INSERT';
    else
    /*logic to delete data into a new table tab_c*/
    DELETE FROM VATSAV_DB.SALES_SCHEMA.TAB_C;
    
    return 'DELETE & INSERT';
  end if;
end;
$$;