DBT
update_org_segments
/*
This macro is to used as a pre-hook only for WC_ORG_D
The purpose is to update the segment information.
To check whether a source table exist, see below
https://discourse.getdbt.com/t/writing-packages-when-a-source-table-may-or-may-not-exist/1487
For update statement...
update_org_children_string
/*
This macro is to used as a pre-hook only for WC_ORG_D
The purpose is to update the children string.
To check whether a source table exist, see below
https://discourse.getdbt.com/t/writing-packages-when-a-source-table-may-or-may-not-exist/1487
For update statement in...
surrogate_key - MD5
-----------------------------------------------------------------------------------
--- Snowflake Surrogate key Generation Macro using MD5 function.
---...
phone_number
/*
Parses oracle flex phone numbers of formatting postfix
*/
{% macro phone_number(column_name) %}
case
when charindex(CHAR(10), {{ column_name }}, 1) > 0 then
left({{...
nvl_flag
/*
if <value> is NULL:
return "N"
else:
if <value> is an integer returns 'Y'
elif upper <value> = 'NULL' return NULL
else 'Y'
Code sample:
{{ nvl_flag('r.column_name') }} AS...
logging
{% macro insert_audit(JOB_STATUS) %}
{% if JOB_STATUS == 'In Progress' %}
insert into {{ target.database }}.LOGGING.PLATFORM_LOGS(PROJECT_NAME,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_END_TIME,INVOCATION_ID)
SELECT '{{ project_name }}',IFF('{{ this...
latest_model_refresh
/*
Used to get the latest refresh date of the current model. This looks up the date from the DBT_MODEL_LAST_RUN table in EDW_CONFIG in snowflake using {{this}} to determine the model
Returns a date and if the date is null then it will return 01/01/2000
*/
{% macro...
compare_new_and_old
{% macro compare_new_and_old(new_table, old_table, join_keys, exceptions) %}
{% set all_columns = adapter.get_columns_in_relation(
new_table
) %}
select
--new.row_wid new_id,
--new.integration_id new_int_Id,
...
check_source_existence
/*
This macro is to check the existence of a source
To check whether a source table exist, see below
https://discourse.getdbt.com/t/writing-packages-when-a-source-table-may-or-may-not-exist/1487
*/
{% macro check_source_existence(schema_table_IN, object_table_IN)...
check_null_str
/*
if <value> is an integer returns 'Y'
elif upper <value> = 'NULL' return NULL
else 'Y'
*/
{% macro check_null_str(column_name) %}
case
when {{ column_name }} is...