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