How can macro handle while loop and doing insert

i want to write a macro which need while loop function. Here is the scenario ,the macro will have two parameter which are begin month and end month.
inside macro, i want to do following step:
1 truncate existing table
2. do insert into this table on month level.
for example, if date range between begin month and end month is 8 months. then do 8 times insert, 1 for each month. i am wondering if we have any while loop in macro like
while(NO_OF_MON > 0 )
do insert into tables
NO_OF_MON+=1;
end

Please, really need someone’s help.

Thanks,
Alex

Hi Alex,

In Jinja we dont have a while as a function, you can use something like this

{% for _ in range(1, large_number) %}

   {% if loop.index > stop_at %}{% break %}{% endif %} 

{% endfor %}

Thanks so much for reply.
i tried below way, but doesn;t work. can you give a little bit details. thanks so much.

{% macro SP_WARD_VILLAGE_YEARLY_SALES() %}

set begindate= TO_VARCHAR(TO_DATE(‘01/01/202013’::date),‘MM/DD/YYYY’) ;

set enddate= TO_VARCHAR(TO_DATE(GETDATE()),‘MM/DD/YYYY’) ;

set NO_OF_MON= (select datediff(month,‘01/01/2013’::date,GETDATE())::int+1) ;

set StartOfMonth = TO_VARCHAR(TO_DATE(CONCAT(DATE_PART(YEAR,GETDATE():: DATE),’-’, DATE_PART(MONTH,GETDATE():: DATE ),’-01’ )),‘YYYY-MM-DD’) ;

set EndOfMonth = TO_VARCHAR(LAST_DAY(TO_DATE(GETDATE())),‘YYYY-MM-DD’) ;

set EndOfMonth= TO_VARCHAR(LAST_DAY(DATEADD(month,-1,$EndOfMonth)),‘YYYY-MM-DD’);

set StartOfMonth= TO_VARCHAR(DATEADD(month,-1,$StartOfMonth),‘YYYY-MM-DD’);

CREATE OR REPLACE TABLE GOVERNED.STG_WARD_VILLAGE_YEARLY_SALES_dbt (

    OPPORTUNITY_ID VARCHAR(255) NULL

    ,TRANSFERRED_FROM VARCHAR(255) NULL

    ,UNIT_TOTAL INT NULL

    ,SALES_TYPE VARCHAR(255) NULL

    ,TRANSACTION_DATE DATE NULL

    ,YEAR INT

    ,MONTH INT

    ,YEAR_MONTH VARCHAR(255) NULL

    ,PURCHASER VARCHAR(255) NULL

    ,BUYERS_AGENT VARCHAR(255) NULL

    ,BUYER_S_AGENT_FIRM VARCHAR(255) NULL

    ,WV_AGENT VARCHAR(255) NULL

    ,WV_AGENT_SECONDARY VARCHAR(255) NULL

    ,BUILDING VARCHAR(255) NULL

    ,UNIT_NUM VARCHAR(16777216) NULL

    ,SPACE_NAME VARCHAR(16777216) NULL

    ,REASON_FOR_PURCHASE VARCHAR(255) NULL

    ,PROFORMA_PRICE NUMBER(18,2) NULL

    ,PURCHASE_PRICE NUMBER(18,2) NULL

    ,CONCESSION NUMBER(18,2) NULL

    ,NET_PRICE NUMBER(18,2) NULL

    ,WITHIN_RECISSION INT NULL

    ,CONTRACT_SIGNED_BY_SELLER DATE

    ,JAPAN_GALLERY_FLAG  VARCHAR(25) NULL

    ,WV_INTERNAL_AGENT  VARCHAR(25) NULL

);

{% for $NO_OF_MON in range(1,$NO_OF_MON) %}

insert into GOVERNED.STG_WARD_VILLAGE_YEARLY_SALES_dbt

SELECT

            O.Opportunity_Id, 

            CASE WHEN CSF.Opportunity_Type IN ('Transfers') THEN O.Transferred_from ELSE NULL END AS Transferred_from,  

            CASE WHEN CSF.Opportunity_TYPE = 'Transfers' THEN 0 WHEN CSF.Opportunity_Type = 'Cancellations' THEN -1 ELSE 1 END as UNIT_TOTAL,  

            CSF.Opportunity_Type AS Sales_Type, 

            CSF.FULLDATE AS Transaction_Date, 

            DATE_PART(YEAR,$EndOfMonth:: DATE), 

            DATE_PART(MONTH,$EndOfMonth:: DATE), 

            concat(TO_CHAR($EndOfMonth::date,'MMMM'),' ' ,DATE_PART(YEAR,$EndOfMonth:: DATE)), 

            A.Name AS Purchaser, 

            C.Name AS Buyers_Agent, 

            O.Buyer_s_Agent_Firm, 

            concat(U.FIRST_NAME,' ',U.LASTNAME) AS WV_Agent, 

            concat(U2.FIRST_NAME,' ',U2.LASTNAME) AS WV_Agent_Secondary, 

            O.Building, 

            S.Name AS Unit_Num, 

            S.Name AS Space_Name, 

            O.Reason_for_Purchase, 

            IFNULL(S.PROFORMA_UNIT_PRICE,S.Unit_Price) AS Proforma_Price, 

            case when CSF.OPPORTUNITY_TYPE = 'Transfers' and O.TRANSFERRED_TO_UNIT <> '' then O.PURCHASE_PRICE_NET_OF_CREDITS * -1 

               when CSF.OPPORTUNITY_TYPE = 'Transfers' and O.TRANSFERRED_TO_UNIT = '' then O.PURCHASE_PRICE_NET_OF_CREDITS * 1  

             when CSF.OPPORTUNITY_TYPE = 'Transfer/Cancel' then O.PURCHASE_PRICE_NET_OF_CREDITS * -1   

              ELSE O.PURCHASE_PRICE_NET_OF_CREDITS * UNIT_TOTAL end Purchase_Price,   

               case when CSF.OPPORTUNITY_TYPE = 'Transfers' and O.TRANSFERRED_TO_UNIT <> '' then O.CREDIT_TOWARDS_PURCHASE_PRICE * -1  

             when CSF.OPPORTUNITY_TYPE = 'Transfers' and O.TRANSFERRED_TO_UNIT = '' then O.CREDIT_TOWARDS_PURCHASE_PRICE * 1  

               when CSF.OPPORTUNITY_TYPE = 'Transfer/Cancel' then O.CREDIT_TOWARDS_PURCHASE_PRICE * -1  

              ELSE O.CREDIT_TOWARDS_PURCHASE_PRICE * UNIT_TOTAL end as Concession,  

             case when csf.OPPORTUNITY_TYPE = 'Transfers' and O.TRANSFERRED_TO_UNIT <> '' then (s.Sale_Price - O.CREDIT_TOWARDS_PURCHASE_PRICE) * -1  

              when csf.OPPORTUNITY_TYPE = 'Transfers' and O.TRANSFERRED_TO_UNIT = '' then (s.Sale_Price - O.CREDIT_TOWARDS_PURCHASE_PRICE) * 1  

               when csf.OPPORTUNITY_TYPE = 'Transfer/Cancel' then (s.Sale_Price - O.CREDIT_TOWARDS_PURCHASE_PRICE) * -1  

              ELSE (s.Sale_Price - O.CREDIT_TOWARDS_PURCHASE_PRICE) * UNIT_TOTAL end as Net_Price, 

             ABS(CASE WHEN DEPOSITS_NONREFUNDABLE = FALSE THEN 0 

                    WHEN DEPOSITS_NONREFUNDABLE = TRUE THEN 1 

                    END -1) AS Within_Recission, 

             O.Contract_Signed_by_Seller, 

             O.JAPAN_GALLERY AS Japan_Gallery_Flag, 

             U.WV_Internal_Agent  

             FROM TABLE (  semantic  .  RPT_SF_CONDO_SALES(to_date($StartOfMonth),to_date($EndOfMonth))) CSF 

             INNER JOIN   semantic  .  DIM_SF_OPPORTUNITY O ON CSF.OPPORTUNITY_ID = O.OPPORTUNITY_ID  

             INNER JOIN   semantic  .  DIM_SF_ACCOUNT A ON O.ACCOUNT_ID = A.Account_ID 

             INNER JOIN   semantic  .  DIM_SF_USER U ON U.USER_ID = O.Owner_Id 

             INNER JOIN   semantic  .  DIM_SF_PROPSPACE S ON O.SPACE_ID = S.SPACE_ID 

             INNER JOIN   semantic  .  LUP_SF_RECORD_TYPE T ON S.RECORD_TYPE_ID = T.RECORD_TYPE_ID 

             LEFT JOIN   semantic  .  DIM_SF_USER U2 ON U2.USER_ID = O.Secondary_Owner 

             LEFT JOIN   semantic  .  DIM_SF_CONTACT C ON C.ID = O.Buyer_s_Agent 



             WHERE CSF.Opportunity_Type IN ('Transfers','Cancellations','New Sales') 

             AND CSF.Opportunity_ID IS NOT NULL AND O.Building <> 'Gateway Cylinder' 

set EndOfMonth=dateadd(month,-1,$EndOfMonth)

set NO_OF_MON=(select datediff(month,‘01/01/2021’::date,$EndOfMonth::date)

{% endfor %}

{% endmacro %}

Hi Alex,

Try below 2 loop statements, and you would not need a insert nor the marco, just create this as a model

{% set max_counter = 10 %}
{% set counter = 1 %}

{% for counter in range(1, max_counter+1) %}
   
  {% if counter < max_counter %}
   SELECT {{counter}} AS CNT
   UNION ALL
  {% elif counter == max_counter %}
   SELECT {{counter}} AS CNT
  {% endif %}
  
  {%set counter = counter+1 %}

{% endfor %}

or

{% set max_counter = 10 %}

{% for _ in range(1, max_counter+1) %}
	
   {% if loop.index < max_counter %}
	SELECT {{loop.index}} AS CNT
	UNION ALL
   {% elif loop.index == max_counter %}
    SELECT {{loop.index}} AS CNT
   {% endif %}

{% endfor %}

output will be :

	SELECT 1 AS CNT
	UNION ALL
	SELECT 2 AS CNT
	UNION ALL
	SELECT 3 AS CNT
	UNION ALL
	SELECT 4 AS CNT
	UNION ALL
	SELECT 5 AS CNT
	UNION ALL
	SELECT 6 AS CNT
	UNION ALL
	SELECT 7 AS CNT
	UNION ALL
	SELECT 8 AS CNT
	UNION ALL
	SELECT 9 AS CNT
	UNION ALL
    SELECT 10 AS CNT

https://jinja.palletsprojects.com/en/2.11.x/templates/#loop-controls

That is a perfect example. thanks so much. still have a little confuse, in my case, i need to update the initial counter value using sql, is that possible? for example,
the script i want is to create a table and do mutiple time insert. but the insert times based on begin date and enddate parameter. for example, i need something like counter = (select datediff(month, begin date::date, enddate )::int+1) %} .
How can i do that? thanks again for the help. really save my day.

Thanks,
Alex

Hi Alex,

When you create a DBT Model

  1. with Materialization = ‘table’, every time the table is dropped (if existing) and a new table is created
  2. with Materialization = ‘incremental’, If table does not exists, then create else just insert the data (when a unique key is added / incremental strategy ) you can switch to merge & just insert the data.

For implementation Year & Month - I will say you to use the loop on a list of values

Thanks so much for the reply.
the incremental probably doesn’t work fine to me. i have many cases that need to do multiple insert and will do update, delete right after that. so using macro to rewrite a sp would fix my situation.

but thanks to point out that feature to me, it would be nice to be used for my other places.

so back to my question, when i am using macro to do the looping, how can i dynamically update my parameters using database result? like i need to know how many month data need to be inserted based on the parameters. and then set the the loop and do the insert/update/delete month by month under business logic, do you have any example or correct syntax to do that? really need this to save my life. lol. looking forward to your help.

Thanks,
Alex

Hi Alex,

Check this - execute | docs.getdbt.com

thanks Man, this link really help. feel like so close.
now i am able to run query and get result and assign it to the parameter. the only blocker is how to convert list to interger. below is the macro i got. i got error message: ‘tuple’ object cannot be interpreted as an integer
obviously, the result will assuming result will return a column list. actually, i only need column1 row 1 data which should be int type. tried mutiple way like results.tables[][], results.columns[].rows[]. none of them works.
thanks again for the help. really make me believe it is close. can you help on final one? how to make this macro work? not sure how to convert list to be int. thanks again really save me day. .

{% macro SP_WARD_VILLAGE_YEARLY_SALES() %}

{% set get_date_parameter %}

SELECT TO_VARCHAR(TO_DATE(‘01/03/2021’),‘MM/DD/YYYY’),TO_VARCHAR(TO_DATE(‘03/03/2075’),‘MM/DD/YYYY’),

      datediff(month,'01/03/2021','03/03/2075'),   

      TO_VARCHAR(TO_DATE(CONCAT(DATE_PART(YEAR,'03/03/2075':: DATE),'-', DATE_PART(MONTH,'03/03/2075':: DATE ),'-01' )),'YYYY-MM-DD'),    

      TO_VARCHAR(LAST_DAY(TO_DATE('03/03/2075')),'YYYY-MM-DD')

{% endset %}

{% set results = run_query(get_date_parameter) %}

{% if execute %}

{% set get_total_loop = results.columns[0].values() %}

{% else %}

{% set get_total_loop = 1 %}

{% endif %}

{% set counter = 1 %}

create or replace table semantic.test(id int)

{% for counter in range(1, get_total_loop) %}

{% if counter < max_counter %}

insert into semantic.test

select 1

{% endif %}

{%set counter = counter+1 %}

{% endfor %}

{% endmacro %}

Thanks,
ALEX

Hi @xzheng ,

Sorry late reply, for conversion to int in jijna2

StrValue|int

No problem man, you help me a lot thanks so much.the reason i why want to script doing that way is because i want to convert while loop stored preocedure to dbt… now i am facing get first value of list and then convert it to int issue. is |int is the only way to convert to int? i tried this in some test file, but after convert, all i got is 0 for no reason, really weird. then i attempted to try another approach to do converting while loop stored procedure to dbt. this ideal is from someone online.
right now, i am facing how to pass parameter when call the macro, is this you can help sir? please see below macro: in this macro, i tried to pass parameter like test(begin_date, end_date), and created a model file to call it, always gave me error like Invalid isoformat string: ‘11/22/2021’ . do you know why? can we pass a single value to the macro instead of whole columns?
--------model script to call macro
{{

config(

materialized = "view",

post_hook = test('11/20/2021','11/01/2021'),

)

}}
select ‘test’ macro


-------below is the macro script

{% macro test(end_date,begin_date) %}

{% set counter = 1 %}

{% set max_counter = 1 %}

{% set date = modules.datetime.date %}

{% set max_counter = (date.fromisoformat(end_date) - date.fromisoformat(begin_date)).days %}

create or replace table semantic.test(id int);

{% for counter in range(1,max_counter) %}

{% if counter < max_counter %}

insert into semantic.test(id)

select {{ (date.fromisoformat(end_date) - date.fromisoformat(begin_date)).days }} ;

{% endif %}

{%set counter = counter+1 %}

{% endfor %}

{% endmacro %}