gemma-dbt-utils

Gemma Analytics utilities for dbt

This packages is expected to be used in addition to dbt-utils and expands upon it rather than replacing it.

Macros

The following macros are available in this package:

• select_star_except

select_star_except

Use this model in a PostgreSQL DWH to emulate the SELECT * EXCEPT(table_name [, table_names]) behavior. Only works when referencing another table, does not work within CTEs.

Arguments

Name	Example	Meaning
table_relation	ref("a_model")	a dbt relation object (source or ref macro)
exceptions_list	["column_name_a", "column_name_b"]	an iterable that contains the names of columns to be excepted
return_query	TRUE	Boolean: returns a full SELECT query if TRUE, only the comma-separated list of columns otherwise

Example usages

-- Variant with return_query = False
SELECT {{ gemma_dbt_utils.select_star_except(
      ref("data_test_numeric_constraints")
    , ["type", "vat_pct"]
    , False
  )}}
FROM ref("data_test_numeric_constraints")

-- Is equivalent to this:

{{ gemma_dbt_utils.select_star_except(
    ref("data_test_numeric_constraints")
  , ["type", "vat_pct"]
  , True
) }}

Models

These models are deactivated by default, but can be activatd and configured using variables in your dbt_project.yml file.

FX aka Exchange Rates

The gemma_fx model is a single model for exchange rates. It works best with raw data from Yahoo Finance, such as supplied by the FX operator by the EWAH library. It takes the data for one or multiple currencies and combines it in a table, filling in data for days that miss data (usually because the date was a bank holiday), so that there is an exchange rate for each currency for each day.

You can configure it using variables:

Variables	Default	Purpose
gemma:fx:currencies	---	Required Dictionary. Dictionary of currencies and their source.
gemma:fx:base_currency	USD	String. Primary currency ticker. Model will calculate exchange rates relative to this currency. If not USD, must be a key in gemma:fx:currencies.
gemma:fx:enabled	false	Boolean. Set to true to activate the model.

The gemma:fx:currencies variable takes the currency as key and the table as value. To use the sources macro, which is recommended, supply the name of another variable as value, and set the variable to use the sources macro. It may look like this in your project's dbt_project.yml file:

vars:
  # Set variables to configure the Gemma dbt utils package
  'gemma:fx:currencies':
    EUR: 'gemma:fx:eur'  # Give name of another variable
    CHF: 'gemma:fx:chf'  # (One could also directly give a schema.table string instead)
  'gemma:fx:eur': "{{ source('fx', 'eur') }}"  # Must do this to use source macro, though
  'gemma:fx:chf': "{{ source('fx', 'chf') }}"  # as dicts don't compile macros in values!
  'gemma:fx:base_currency': EUR
  'gemma:fx:enabled': true

(Dim) Dates

This model will create the table gemma_dates in the schema YOUR_SCHEMA_gemma_dbt_utils. Starting from a defined date it will create a date series with different date columns until a specified number of days, e.g. 30 days after the current date. It works for postgres and bigquery - specified through the target type in the profiles.yml The configurations are:

Variables	Default	Purpose
gemma:dates:timezone	'Europe/Berlin'	Optional String. Sets the timezone for this model. By defaul, it's set to 'Europe/Berlin'
gemma:dates:enabled	false	Required Boolean. Set to true to activate the model
gemma:dates:start_date	'2020-01-01'	Optional String. Sets the start_date for the date series. By defaul, it's set to '2020-01-01'
gemma:dates:end_date	'30 day'	Optional String. It is an interval relative to current_date, which sets the end_date for the date series. By defaul, it's set to '30 day'
gemma:dates:table	'gemma_dates'	Optional String. Sets an alias for the model. By default, it's set to 'gemma_dates'
gemma:dates:schema	'gemma_dbt_utils'	Optional String. Sets the a custom schema for the model. By default, it's set to 'gemma_dbt_utils'

Example dbt_project.yml:

vars:
  'gemma:dates:timezone': 'Europe/Berlin' # overwrite to get a different default value
  'gemma:dates:enabled': false # overwrite this variable to enable the date model
  'gemma:dates:start_date': '2020-01-01' # overwrite to get a different default value
  'gemma:dates:end_date': '30 day' # 30 days after the current date
  'gemma:dates:table': 'gemma_dates' # overwrite to get a different default value
  'gemma:dates:schema': 'gemma_dbt_utils' # overwrite to get a different default value

Schema Tests

numeric_constraints (source)

This schema test asserts that a column of numerical value is within specified bounds.

Usage:

version: 2
models:
  - name: model_name
    columns:
      - name: a_number
        tests:
          - gemma_dbt_utils.numeric_constraints:
              gte: 0
              lt: 180

Possible options:

eq:   equal
ne:   not equal
gt:   greater than
gte:  greater than or equal
lt:   lower than
lte:  lower than or equal

The macro accepts an optional parameter condition that allows for asserting the expression on a subset of all records.

Usage:

version: 2
models:
  - name: model_name
    columns:
      - name: a_number
        tests:
          - gemma_dbt_utils.numeric_constraints:
              eq: 1
              condition: "status = 'success'"