DBT Denodo Adapter - User Manual
You can translate the document:
Overview
Data Build Tool (dbt), commonly referred to as dbt, is an open-source framework designed to facilitate data transformations by implementing analytics engineering best practices. Dbt is the T in ELT. Using dbt you can organize, clean, filter and rename the raw data in your platform so that it's ready for analysis.
The dbt-denodo adapter for dbt Core implements the logic required to bridge dbt with the Denodo platform. Operating through Denodo's Virtual Query Language (VQL), this adapter enables users to create views inside the Denodo platform, facilitating data transformations on available datasets through the adapter.
The Denodo adapter for dbt allows extending dbt’s reach across any data source via a unified, governed ELT/ETL execution engine. Denodo unified language (VQL) decouples dbt models from the underlying data sources, so teams can build highly reusable models that remain resilient to changes in the source systems, providing flexibility and accelerating delivery.
The dbt adapter helps organizations bring Denodo into existing data engineering workflows with less friction. Data engineers can continue using the dbt tools and practices they already know, including development, version control, testing, and orchestration workflows, while Denodo provides governed access to data across warehouses, lakehouses, cloud platforms, operational systems, and other distributed sources. For organizations already standardized on dbt, it provides a practical way to extend familiar engineering practices to the Denodo Platform while preserving Denodo’s strengths in logical data access, governance, and reuse.
Prerequisites
In order to use the dbt-denodo adapter, ensure the following requirements are met:
- Denodo Platform version 9.4 or higher
- Access to the Denodo platform with valid credentials for one of the supported authentication mechanisms: Basic (login/password) or OAuth.
- Activation of the cache system is mandatory for the Denodo virtual database if table or incremental materialization strategies are employed.
- Python environment configured to run dbt-core 1.11.11.
Installation from Denodo Support Site
The dbt-denodo adapter is available for download via the Denodo Support Site.
Following the extraction of the package, execute the command below to install the adapter:
pip install route/to/dbt_denodo-<version>-py3-none-any.whl
Configuration
To establish a connection between dbt and the Denodo platform, you must create a profiles.yml file. This configuration file defines the connection details, allowing dbt to authenticate and interact with the Denodo Virtual Data Port (VDP) server. By default, profiles.yml is located in ~/.dbt/profiles.yml (on Windows: C:\Users\<username>\.dbt\profiles.yml)
The connection between dbt and the Denodo Virtual DataPort (VDP) needs this file, which requires a set of mandatory parameters within its configuration block. These common parameters are detailed below, followed by the specific requirements for both Basic and OAuth authentication methods.
- type: Defines the adapter to be used, which in this case must be denodo.
- host: The IP or hostname of the Denodo server to which dbt will connect.
- port: VDP (Virtual DataPort) port. For a standard Denodo installation, this value is typically set to 9996. Confirm which port your VDP deployment exposes for the PostgreSQL-wire interface (also named "ODBC port" in some parts of the Denodo documentation)
- database: Specifies the Denodo Virtual Database (VDB) where models will be materialized. Implementation of table or incremental strategies requires the cache functionality to be enabled for this specific database.
- username: The username required for authentication.
- password: The password associated with the user.
- auth_type: Specifies the authentication mechanism. Supported values are basic or oauth.
The adapter supports two primary authentication mechanisms: Basic (standard login and password) and OAuth.
1. Basic Authentication
This method uses the standard login mechanism with credentials.
- Configuration for this method requires the username and password fields, with the auth_type explicitly defined as basic.
2. OAuth Authentication
By utilizing this approach, users can leverage token-based identity management for enhanced security. This requires setting the auth_type parameter to oauth.
- auth_type: Explicitly requires the value oauth.
The adapter supports two distinct methods for implementing OAuth:
Method A: Direct token injection via password
With this configuration, the adapter treats the provided password string as the active OAuth bearer token.
denodo:
target: dev
outputs:
dev:
type: denodo
host: localhost # IP or hostname of the Denodo server
port: 9996 # Default VDP JDPBC port
database: dbtmaterialized
password: ******* # OAuth access token
auth_type: oauth # Supported types: basic or oauth
Method B: Automated token retrieval from OAuth provider
To utilize this approach, the following additional parameters must be defined:
- token_url: The authentication endpoint responsible for issuing tokens (e.g., the /as/token.oauth2 route on an identity server).
- client_id: The registered identifier for the OAuth application.
- client_secret: The private key linked with the client_id.
- scope_field_name: The specific field within the response payload that designates scopes.
- scope_field_value: The requested permissions required for the session.
- grant_type: The specific OAuth flow used for authentication.
Omission of these fields causes the adapter to assume the password field contains a pre-authenticated token.
Failure to provide either a valid endpoint configuration or a manual token will result in a runtime exception.
The following configuration block should be added to your profiles.yml file to configure the Denodo adapter:
denodo:
target: dev
outputs:
dev:
type: denodo
host: localhost # IP or hostname of the Denodo server
port: 9996 # Default VDP JDPBC port
database: dbtmaterialized
username: puser
password: *******
auth_type: oauth # Supported types: basic or oauth
token_url: https://******.******.com:9031/as/token.oauth2
client_id: test
client_secret: [your_secret]
scope_field_name: scope
scope_field_value: controller openid
grant_type: password
Usage
Start a project
To initialize a new dbt project for use with the Denodo platform, you must use the standard dbt initialization command from your terminal. This process sets up the necessary directory structure and configuration files required for your analytics engineering workflow.
Run the following command, replacing projectName with your desired project name:
dbt init projectName --profile denodo
Upon execution, dbt will prompt you to select an adapter. This initialization creates a project folder containing sample models and the dbt_project.yml configuration file, which you will then need to align with your Denodo profiles.yml settings.
Create a model
In dbt, a model is a single SELECT statement defined in a .sql file within your project's models/ directory. When you run dbt, the adapter transforms these SELECT statements into Virtual DataPort (VDP) views or tables within the Denodo platform.
To create your first model, navigate to the models/ folder and create a new file, such as my_first_model.sql. Inside this file, write the VQL query that defines your transformation:
SELECT *
FROM actor
WHERE status = 'active'
The dbt-denodo adapter will use Denodo's Virtual Query Language (VQL) to execute these transformations, facilitating the creation of robust datasets directly through the platform. Depending on the materialization strategy, data could be stored in the database configured as a cache, which allows data access from outside the platform
VDP Configuration
The dbt-denodo adapter triggers the creation of physical objects within a virtual database. When utilizing table or incremental materialization strategies, records are persisted inside the configured cache system for that VDB; consequently, users should be aware of the specific cache configuration for their virtual database. To ensure optimal performance and configuration alignment, consult the ‘Best Practices to Maximize Performance III: Caching’ guide, which details recommended standards for the platform.
When designing dbt models, early performance planning is crucial: when the source data and the materialization target reside in the same physical database, you must use the exact same VDP data source object, rather than separate VDP data sources pointing to the same underlying system. This allows VDP to recognize they are on the same engine and execute query pushdown and data movement optimizations.
Transformations are executed against datasources available in VDP; however, configurations within the underlying source systems can significantly influence metadata resolution. For instance, modifying the UTF-8 toggle located in the Read & Write tab might alter the resulting view data types. For a comprehensive review of these configurations, please refer to the Virtual DataPort Administration Guide. This resource also provides documentation on the Bulk Data Load feature; organizations aiming to optimize cache loading performance for large datasets should consider configuring this capability for the cache database. Detailed installation steps and environmental prerequisites are available in the Bulk Data Load — Virtual DataPort Administration Guide 9.5.
Run a model
After defining your models and configuring the connection, you can execute the models from the terminal. This process triggers the dbt-denodo-adapter to communicate with the Denodo VDP server and materialize the specified objects.
To run every model within the project, trigger the following command:
dbt run
For more granular control, you can run a specific model by using the --select flag:
dbt run --select my_first_model
Database and schema name resolution
To improve compatibility with dbt projects that define only a schema, the adapter uses the schema name as a fallback when the database name is not provided in the macros list_relations_without_caching and get_columns_in_relation.
This allows relation discovery and column introspection to work correctly in environments where objects are referenced by schema only.
Materializations
Overview
Materializations are strategies for persisting dbt models. The dbt-denodo adapter maps these strategies to Denodo-specific consolidation mechanisms, persisting records inside the configured cache database, whenever the selected materialization activates cache capabilities within the Denodo platform.
Configuring materializations
By default, dbt models are materialized as views. You can change the materialization for a specific model by adding a config block with the parameter materialized at the top of the .sql file:
{{
config(
materialized='table'
)
}}
SELECT * from ...
View
The view materialization is the default and creates a Denodo view. These are virtual tables that do not store data physically but provide real-time access to the underlying logical model.
{{
config(
materialized = 'view'
)
}}
SELECT * from ...
Table
Standard dbt table materialization involves physical creation in the target database. Denodo adapter implements this by materializing the model as a view with Full Cache enabled. This architectural choice ensures that the resulting physical table is managed within Denodo's configured cache database, which also remains accessible to external systems with the defined table name and schema. The vdb cache target must be enabled before running dbt.
This functional mapping enables physical data consolidation while preserving full data lineage within the Denodo platform.
{{
config(
materialized='table'
)
}}
SELECT * from ...
Incremental
Incremental models allow dbt to update tables by only processing new or updated records. In Denodo, these are managed transparently via the Full Cache materialization, which handles different strategies based on the primary key and incremental conditions provided in the model. The vdb cache target must be enabled before running dbt.
In the dbt-denodo adapter, incremental logic is managed through the incremental_condition parameter within the model's configuration block. This approach provides a robust mechanism for isolating incremental filters from the primary query, ensuring they are integrated directly into the Denodo platform's Full Cache configuration.
The adapter utilizes the @LAST_REFRESH_DATE variable, which automatically tracks the timestamp of the most recent cache refresh. By referencing this variable within the incremental_condition, users can efficiently synchronize data by targeting only records updated since the last materialization, as shown in the following examples.
This materialization is not transactional, meaning if a failure occurs during execution, the table could be compromised.
append strategy
Adds new rows to the existing table without modifying existing data. This is used when no primary key is defined.
{{
config(materialized = 'incremental',
incremental_strategy = 'append',
incremental_condition = "dbt_updated_at >= '@LAST_REFRESH_DATE'"
)
}}
SELECT
id AS dbt_id, project_name AS dbt_project_name,amount AS dbt_amount, updated_at AS dbt_updated_at
FROM sqlserverrancher.incremental_test
WHERE id < 300
merge strategy
Using this strategy, Denodo updates existing records and inserts new ones based on the primary key. The Denodo platform utilizes the view's primary key to verify if specific records are already present in the cache. Consequently, you must define the unique_key parameter within the model's configuration block. Composite primary keys are also supported; to implement this, use the array syntax: unique_key = '[parameter1, parameter2]'.
Implementation of this strategy requires the underlying cache system to possess merge data capabilities. Please consult the following link to review the specific data sources that support merge operations.
{{
config(
materialized='incremental',
incremental_strategy='merge',
unique_key='id',
incremental_condition = "updated_at >= '@LAST_REFRESH_DATE'"
)
}}
SELECT * from ...
Materialized View
In the Denodo adapter, dbt materialized views map to Materialized tables in VDP. The materialized table in Denodo is a special type of base view whose data is stored in the database where the data is cached, instead of in an external data source.
{{
config(
materialized='materialized_view',
database='admin'
)
}}
SELECT * from ...
Options
Folder
Within the configuration block, you can designate the folder where the materialized view will be stored in Denodo. The specified folder path must begin with "/" and cannot end with "/". If the folder does not exist, it will be created. When utilizing
incremental materialization, modifying the target folder for an existing base view will not automatically trigger a relocation of that object. To successfully move the base view to the newly specified directory, you must execute the command with the --full-refresh flag, which recreates the view within the desired folder structure.
{{
config(
materialized='materialized_view',
database='admin',
folder='/materialized_views'
)
}}
SELECT * from …
Remote Option Configuration
A boolean remote parameter may be included within the configuration block of a dbt model. When set to true, the materialization is configured as a remote table in the Denodo Virtual DataPort (VDP).
By utilizing this option, you can select the specific database where the data of the model will be physically loaded. For this mechanism to function, the target database must be properly configured as a data source within VDP. Consequently, a will be created in VDP, and through this table, you can access the physical data residing in the underlying data source.
This option is only compatible with and works in the following materialization strategies: table and incremental. In the case of incremental are supported merge and append option.
You need configure more parameters to uses the remote options:
- datasource_database_name: database of the JDBC data source in which the new table will be created. If null, it looks for datasource_name on the current database.
- datasource_name: Specify the VDP data source where the physical table is to be established.
- datasource_catalog: catalog of the database where the table will be created. It is mandatory but if the database does not support catalogs, set this to null or ''.
- datasource_schema: schema of the database where the table will be created. It is mandatory but if the database does not support schemas, set this to null or ''.
- remote_table_name: Defines the identifier for the new table to be generated within the designated data source database.It must be a valid identifier within the target database and cannot be a reserved word. The naming conventions require that the first character be a letter from A to Z (either uppercase or lowercase). The subsequent characters can consist of letters (a-z, A-Z), numbers, or underscores.
- replace_remote_table_if_exist: if true and a table with the same name already exists in the database (in the same schema/catalog),the table will be dropped and create again. If the value is false and the table already exists, the procedure will fail.
Configuration Example:
{{
config(
materialized='table',
folder='/table',
database='dbtmaterialized2',
remote=true,
datasource_database_name = 'admin',
datasource_name = 'sakila',
datasource_catalog = '',
datasource_schema = 'SAKILA',
remote_table_name = 'tableRemote',
replace_remote_table_if_exist = true )
}}
SELECT * FROM ...
Dbt-utils
dbt-utils is an open-source package of dbt ecosystem. When using the dbt-utils package within a dbt-denodo project, certain cross-database macros require Denodo-specific implementations to handle metadata queries correctly. Without these overrides, schema inspection operations can fail. The following dbt-utils macros are not natively compatible with Denodo: get_tables_by_pattern_sql and get_relations_by_pattern.
Configuration
To ensure full compatibility, you must copy the custom macro definitions provided in the DBT Denodo Adapter distribution into your local project.
Copy resources/macros/relations.sql directly into your dbt project’s root macros/ folder.
Once placed in your macros/ directory, dbt will automatically detect and prioritize these Denodo macros whenever dbt-utils relation inspection functions are invoked.
Limitations
The dbt-denodo adapter has several known limitations and incompatible features when compared to standard dbt implementations:
- Jinja Conditionals: The standard {% if is_incremental() %} Jinja conditional is incompatible with the dbt-denodo adapter. Instead, the incremental condition must be configured within the config block using the incremental_condition parameter to isolate the incremental part and define it directly within the Full Cache configuration of the Denodo platform.
- Incremental Strategies: Certain built-in strategies, specifically insert_overwrite, delete_insert and microbatch, are not supported by dbt-denodo adapter.
- Ephemeral Materialization: This materialization strategy does not translate to any physical materialization from dbt and therefore does not apply to any activity within the adapter.
- Transactions: The table and incremental materialization strategies are not transactional within the VDP. Consequently, a failure during the data loading process may result in an empty or incomplete table remaining in the Denodo platform.
Troubleshooting
An error was encountered while trying to flush usage events
During the execution of dbt commands, you may encounter logs indicating a failure to flush usage events. This typically occurs when the dbt environment attempts to transmit anonymous telemetry data to dbt Labs but is restricted by network configurations, firewall rules, or a lack of internet connectivity.
To eliminate these log entries, you can deactivate the transmission of anonymous usage statistics. This is achieved by adding a flag to your dbt_project.yml file:
flags:
send_anonymous_usage_stats: false