Skip to content

Teradata/dbt-teradata-utils

Repository files navigation

dbt package teradata_utils

This dbt package provides compatibility with dbt_utils package for Teradata Vantage.

Installation Instructions

  1. Add the package as a dependency to your project in packages.yml:
    packages:
    - package: Teradata/teradata_utils
      version: 1.3.0
    
    For more information about installing packages, see dbt documentation.
  2. Set the dispatch config in your root project (on dbt v0.20.0 and newer) and let teradata_utils package intercept macros from the dbt_utils namespace:
    dispatch:
      - macro_namespace: dbt_utils
        search_order: ['teradata_utils', 'dbt_utils']
    

Additional steps for surrogate_key macro

surrogate_key macro needs an md5 function implementation. Teradata doesn't support md5 natively. You need to install a User Defined Function (UDF):

  1. Download the md5 UDF implementation from Teradata (registration required): https://downloads.teradata.com/download/extensibility/md5-message-digest-udf.
  2. Unzip the package and go to src directory.
  3. Start up bteq and connect to your database.
  4. Create database GLOBAL_FUNCTIONS that will host the UDF. You can't change the database name as it's hardcoded in the macro:
    CREATE DATABASE GLOBAL_FUNCTIONS AS PERMANENT = 60e6, SPOOL = 120e6;
  5. Create the UDF. Replace <CURRENT_USER> with your current database user:
    GRANT CREATE FUNCTION ON GLOBAL_FUNCTIONS TO <CURRENT_USER>;
    DATABASE GLOBAL_FUNCTIONS;
    .run file = hash_md5.btq
  6. Grant permissions to run the UDF to your dbt user. Replace <DBT_USER> with the user id you use in dbt:
    GRANT EXECUTE FUNCTION ON GLOBAL_FUNCTIONS TO <DBT_USER>;

Compatibility

Macro Group Macro Name Status Comment
Schema tests equal_rowcount no customization needed
Schema tests fewer_rows_than custom macro provided
Schema tests equality no customization needed
Schema tests expression_is_true no customization needed
Schema tests recency custom macro provided
Schema tests at_least_one no customization needed
Schema tests not_constant no customization needed
Schema tests cardinality_equality no customization needed
Schema tests unique_where no longer supported by dbt-utils, use built-in unique test instead
Schema tests not_null_where no longer supported by dbt-utils, use built-in not_null test instead
Schema tests not_null_proportion custom macro provided
Schema tests not_accepted_values no customization needed
Schema tests relationships_where no customization needed
Schema tests mutually_exclusive_ranges custom macro provided
Schema tests sequential_values no customization needed
Schema tests unique_combination_of_columns no customization needed
Schema tests accepted_range no customization needed
Schema tests at_least_one custom macro provided
Schema tests expression_is_true custom macro provided
Introspective macros get_column_values custom macro provided
Introspective macros get_relations_by_pattern custom macro provided
Introspective macros get_relations_by_prefix custom macro provided
Introspective macros get_query_results_as_dict custom macro provided
SQL generators date_spine custom macro provided
SQL generators haversine_distance no customization needed
SQL generators group_by no customization needed
SQL generators star no customization needed
SQL generators union_relations custom macro provided
SQL generators generate_series custom macro provided
SQL generators generate_surrogate_key custom macro provided, additional install steps required
SQL generators safe_add no customization needed
SQL generators pivot no customization needed
SQL generators unpivot no customization needed, see compatibility note
SQL generators deduplicate custom macro provided
Web macros get_url_parameter no customization needed
Web macros get_url_host no customization needed
Web macros get_url_path custom macro provided
Cross-database macros current_timestamp custom macro provided
Cross-database macros dateadd custom macro provided
Cross-database macros datediff custom macro provided
Cross-database macros split_part custom macro provided
Cross-database macros date_trunc custom macro provided
Cross-database macros last_day no customization needed, see compatibility note
Cross-database macros width_bucket no customization needed
Jinja Helpers pretty_time no customization needed
Jinja Helpers pretty_log_format no customization needed
Jinja Helpers log_info no customization needed
Jinja Helpers slugify no customization needed
Materializations insert_by_period custom macro provided

unpivot

unpivot uses value as the default name for the value column. value is a reserved word in Teradata. Make sure you specify a different value in value_name parameter, e.g.:

{{ 
  dbt_utils.unpivot(
    relation=ref('table_name'),
    cast_to='datatype',
    exclude=[<list of columns to exclude from unpivot>],
    remove=[<list of columns to remove>],
    field_name=<column name for field>,
    value_name='_value'
  ) 
}}

last_day

last_day in teradata_utils, unlike the corresponding macro in dbt_utils, doesn't support quarter datepart.

Note to maintainers of other packages

The teradata-utils package may be able to provide compatibility for your package, especially if your package leverages dbt-utils macros for cross-database compatibility. This package does not need to be specified as a dependency of your package in packages.yml. Instead, you should encourage anyone using your package on Teradata to:

  • Install teradata_utils alongside your package
  • Add a dispatch config in their root project, like the one above

Contributing

We welcome contributions to this repo! To contribute a new feature or a fix, please open a Pull Request with 1) your changes and 2) updated documentation for the README.md file.