Jinja2 + SQL - How to pass a list of columns to use in a macro

Viewed 421

I would like to create some data sanity checks for random integrations. To do so I need to create queries that are flexible enough to be run in different tables. I'm using jinja2 to create some macros so I can pass columns and tables to those queries. Now my problem is if I want to check for duplicate data-points, in all columns like:

SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1

I would need to create a macro that allowed me to pass multiple columns to select statement and i don't know how to get it. I was wondering if I could create a .yaml with the table name + columns and make a macro that would fetch that data

For now I have this working

 {%- macro duplicates_ids(table, column) -%}
    select{{ column }}
    ,count(*)
    from {{ table }}
    group by {{ column }}
    having count(*) >1
    {%- endmacro -%}

So then I can just define the table name and the column to test the table. Thank you all in advance

0 Answers
Related