jeremyyeo

{% macro my_macro() %}

    {% if var('my_var', none) is not none %}
        {% set thing = var('my_var') %}
        {{ dbt_utils.log_info(thing) }}
    {% else %}
        {{ dbt_utils.log_info("no variables set") }}
    {% endif %}

{% endmacro %}

jeremyyeo

{% macro test_macro() %}

    {% set contains_1 = [] %}
    {% set contains_2 = [] %}
    {% set my_dict = [{'a': [1]}, {'b': [1, 2]}, {'c': [2]}] %}

    {% for item in my_dict %}
    
        {{ log(item, info=True) }}
        

jeremyyeo

Versions

Tested with dbt versions:

• 0.20.0
• 0.20.1
• 0.20.2

Python version 3.8.7

jeremyyeo

Building SCD-2 models using the default incremental materialization

1. Bootstrap the source / snapshot table source_users and the initial state of our scd2 table dim_users by running:

dbt run -m source_users dim_users

2. Copy over contents of dim_users_inc.sql replacing contents in dim_users.sql (or just rename them). Then run:

jeremyyeo

Customising dbt snapshots

It is currently not possible to modify the actual names of the snapshot metafields (dbt_valid_from and friends) even if you go down this path to customize the built in macros below - dbt-labs/dbt-core#7018

Customising dbt snapshots so that dbt_valid_from dates can use a variable.

Macros that need to be overridden are in the materializations/snapshots folder in dbt-core.

1. Setup initial snapshot:

jeremyyeo

Creating jobs with the dbt Cloud API

As of 2021-12-03, the docs for the "Create job" end point does not reflect the reality of what is actually required in the POST request payload (see relevant github issue).

See attached main.py for an example of a working payload - all the keys in the example payload must be included (e.g. "id", "triggers", "schedule") even though they are not specied as required in the API docs.

jeremyyeo

Overriding dbt Cloud default database / schema on CI runs

-!  🚨                                          WARNING                                          🚨  !-
You probably do not want to do this because dbt Cloud will not be able to drop the relevant schema 
upon PR merge / close so you will end up with clutter if you are not on top of this.

The following is the default behaviour of [dbt Cloud CI runs][1] when:

jeremyyeo

Customising the dbt-event-logging package

As of dbt-event-logging 0.6.0, the only customisation that can be done to it is to redefine the schema where the dbt_audit_log table is put into (see readme). If you want to further customise the columns that get's recorded in the dbt_audit_log table itself, for example adding dbt_cloud_run_id, you will have to get knee deep into the source macros.

To make thing's easier, we can just copy most of the code in the source audit.sql macro, put it in your macros folder (i.e. macros/audit.sql) and make some tweaks to it - this also means that you do not need to install the dbt-event-logging package via specifying it in your packages.yml file since the macro is part of our project.

The example audit.sql file in this gist shows how to add 2 additional c

jeremyyeo

Making a dbt materialization that ignores certain columns

-!  🚨                                          WARNING                                          🚨  !-
This is an advanced dbt feature and is not recommended for users who are new to dbt.

The default dbt materialization that does inserts into a target table by selecting from another source table is the incremental materialization. By default, the incremental materialization requires that all columns in the target table are accounted for in the model code - this means that it may be challenging if you want exclude certain columns from being inserted by dbt (perhaps your target table has a self incrementing integer primary key column and you want the database to increment this automatically instead of having dbt do it on your behalf).

We can try to solve this by creating our [own custom materialization](https://docs.getdbt.com/docs/gui

jeremyyeo

Testing local Python connection to Snowflake

Instructions adapted from the first few steps in the official "Getting Started with Python" quickstart from Snowflake.

1. Check that your Python version is > 3.5.

python --version