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.1.1
   

   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	❌	no plans to implement, please submit an issue if you need it

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.