dbt-resto

This is dbt package for FUN!

Supported warehouses: SQL Server

Developer's Guide

See integration_tests

Macros:

get_table_alias (source)

Suffix the table name with your configured variable table_suffix.

Usage:

{{ config(alias=dbt_resto.get_table_alias(this)) }}

generate_schema_name (source)

Override the schema strategy. MAKE-A-COPY to your local project under your-project/macros/ directory.

If the model has no config schema then it will use target.schema to be its schema name, else will use the exact schema name configured.

dateadd (source)

Add value to date

Usage:

select {{ dbt_resto.dateadd('day', 1, 'column') }}

datepart (source)

Get date part

Usage:

select {{ dbt_resto.datepart('column', 'second') }}

get_base_times (source)

Prepare the select statement of the datetime values in each level = hour, minute.

NOTE: It can genrate per second level but NOT recommend to do so.

Usage:

with base_times as (
  {{ dbt_resto.get_base_times('hour') }}
)
select * from base_times;

with base_times as (
  {{ dbt_resto.get_base_times('minute') }}
)
select * from base_times;

get_time_dimension (source)

Prepare the select statement of all columns required in a time dimension table.

NOTE: It can genrate per second level but NOT recommend to do so.

Usage:

# models/my_model.yml
{{ dbt_resto.get_time_dimension() }}

get_time_key (source)

Convert the time part to the string of {HOUR}{MINUTE}{SECOND} e.g. 092733

Usage:

select {{ dbt_resto.get_time_key('column', parts=['hour','minute','second'], h24=True) }}

str_to_date (source)

Convert a string formatted in a specific pattern to the date value.

Usage:

select   {{ dbt_resto.str_to_date('column') }} as date_column
from     table

len (source)

Get length of column value

Usage:

select   {{ dbt_resto.len('column') }} as column_len
from     table

Generic Tests:

if_column_value_to_match_regex (source)

Generic test function to check if a column value is matched to a regular experssion.

Currently support Snowflake only.

Usage:

models:
  - name: table_name
    columns:
      - name: column_name
        tests:
          - dbt_resto.if_column_value_to_match_regex:
              regex_expr: '[a-zA-Z]' # matching text only

Materialization:

materialized_view (source)

Model materialization for Materialized View (it's called Indexed View in SQL Server)

• NOTE - Supported editions:

  • Snowflake Enterprise and above
  • SQL Server all editions

• NOTE: Use this with knowledge of the Limitations

Usage:

• Snowflake

  {{
    config(
      materialized = 'materialized_view'
    )
  }}
  
  select  *
  from    {{ ref('your_model') }}

• SQL Server:

  {{
    config(
      materialized = 'materialized_view',
      unique_key = 'your_model_key',
    )
  }}
  
  select  *
  from    {{ ref('your_model').include(database=False) }}

  • The ref relation must go with include(database=False)
  • The config must have unique_key

Repo Beats