Additional SQL on events this run

There may be times when the events this run table requires additional fields on it for you to make use of to add to your derived tables. It is not recommended to alter this model directly, as it is a core part of the packages, but you can use the snowplow__custom_sql variable in packages that support it to add custom sql into the select block of the events this run model.

To find out if your package supports this, check the configuration page.

Using snowplow__custom_sql

The snowplow__custom_sql variable is passed as-is through to the select block of the events this run macro . Because of where this is passed in, fields such as the session and user identifiers are not available at this point, but any other field should be. In the case of Redshift and self-describing events or entities provided in the snowplow__entities_or_sdes variable will be available with the provided prefix and table alias.

Say you wanted to add a calculated field based on your app id, you would set this as such:

dbt_project.yml

...
vars:
  snowplow_<package_name>:
    snowplow__custom_sql: "case when app_id like '%_test' then 'test' else 'prod' end as app_type"
...

Utilizing custom contexts or SDEs

Suppose you have a custom context called contexts_com_mycompany_click_1 which contains a click_id that you want to concat with Snowplow's domain_sessionid. If you want to make this available in the events this run table directly you could do the following:

dbt_project.yml

vars:
    ...
    snowplow__custom_sql: "CONCAT(com_mycompany_click_1[0].click_id, '_', domain_sessionid) as click_session_id"
    ...
...

dbt_project.yml

vars:
    ...
    snowplow__custom_sql: "CONCAT(com_mycompany_click_1[safe_offset(0)].click_id, '_', domain_sessionid) as click_session_id"
    ...
...

dbt_project.yml

vars:
    ...
    snowplow__custom_sql: "com_mycompany_click_1.click_id || '_' || domain_sessionid as click_session_id"
    ...
...

Adding multiple fields

If you'd like to add multiple lines of SQL, you can do that as well by making this string a multi-line string, ending each non-final line with a comma. You can do that as follows:

dbt_project.yml

vars:
    ...
    snowplow__custom_sql: |
        com_mycompany_click_1.click_id || '_' || domain_sessionid as click_session_id,
        case when app_id like '%_test' then 'test' else 'prod' end as app_type
    ...
...