Collect and Organize queries where clauses in dBT

Help
1

Hi,
I am collecting data coming from multiple data sources, the data lands in my data lake.
I need to organize all the where clause of all the queries, for example one dataset is about employee data, when i create models for this dataset my query would look like

– Employees hired in 1986
SELECT hire_date, first_name, last_name, emp_no
FROM employees
WHERE Year(hire_date) = ‘1986’

– All employees in Sales (employee number, last name, first name, and department name)
SELECT e.emp_no, e.last_name, e.first_name, x.dept_name
FROM employees e
LEFT JOIN dept_emp d ON e.emp_no = d.emp_no
LEFT JOIN departments x ON d.dept_no = x.dept_no
WHERE x.dept_name = ‘Sales’

Each dataset have its own set of where clauses, what is the best practice to organize all those where clauses, in order to store them in an organized way and embed those rules dynamically in the model. I got lots of those where clauses , some are unique for the dataset and few are common between datasets.

Thank You,
Ossama

3

Hi @Ossama,

Maybe you can use a macro? Something like

{% macro get_filter(type='employees') %}

WHERE

{%- if type = 'employees' %}
    Year(hire_date) = ‘1986’
{%- elif type = 'sales' %}
    x.dept_name = ‘Sales’
...
{%- else %}
    {# Default filter here #}
{% endif -%}

{% endmacro -%}

And then call it in your models:

-- Employees hired in 1986
SELECT hire_date, first_name, last_name, emp_no
FROM employees
{{ get_filter(type='employees') }}

Does this approach make any sense?

4

Hi @hvignolo , All what I am after is to document the extraction rules and use them dynamically.
for example, if I have multiple queries like the two above, I mean by multiple hundreds of those queries, I need to put the extraction rules of Year(hire_date) and the x.dept_name in one place documented , so when i look to queries for employees, i will have employee hired as one extraction rule and employee sales as another extraction rule and passing values to the where clause dynamically like 1986 and ‘Sales’ etc…
So, I might use the macro as you showed me below to document those extraction rules. I hope what i have explained is making sense and not confusing.

5

Hi @hvignolo , you can look at what I am trying to achieve , to find/build/simulate extraction business rule engine.

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.