Denodo Platform 9 Certified Developer Professional cheat sheet

A data source stores only connection metadata (driver, URL, credentials, pooling); a base view is the wrapper that exposes one or more tables/operations of that source as a VDP view with its own schema and metadata.

JDBC sources: Denodo ships drivers for many databases but the recommended pattern is to place the vendor driver JAR in the <DENODO_HOME>/lib/extensions/jdbc-drivers folder and restart, so the driver is shared by all databases in the server.

Connection pooling is configured per data source (initial/min/max pool size, validation query, timeout). Pool size must be sized against the source database's own connection limit; a pool larger than the database allows causes intermittent connection failures under load.

Delegation capabilities are per data source and per database: the wrapper advertises which functions and operators it can push down. Disabling a capability forces Denodo to evaluate it, which changes both correctness of results (collation, case sensitivity) and cost.

Credentials can be stored in the data source, delegated (pass-through of the end-user's credentials), or obtained from a credential store / Kerberos. Pass-through requires the query to run under the user's own database identity and is incompatible with shared pooled connections unless configured per-user.

Base view schema changes are handled by refreshing the view; Denodo detects added/removed/renamed columns. Renamed source columns can be mapped to preserve downstream derived view definitions instead of breaking them.

For non-relational sources (REST/JSON, XML, delimited files, LDAP, SaaS connectors) the base view is built from a structured schema inferred or hand-declared; the tuple/row structure and the flattening of nested arrays determines how many rows a single document produces.

A data source can be marked read-only, and the 'Database URI' plus 'Driver class' pair is validated by a test connection that must succeed before the base view can be created.

Derived view types and their semantics: selection/projection (filter and column pruning), join, union, intersection, minus, flatten, aggregation, rollup, pivot/unpivot, interface, and OLAP-style views; each has a distinct pushdown rule set.

Join execution strategy is decided by the optimizer: 'hash' builds an in-memory hash on the smaller side, 'nested' (indexed/nested loop) suits small outer with indexed inner, 'merge' suits pre-sorted inputs, and 'parallel' splits the join across worker threads. The chosen strategy is visible in the execution trace.

A join between two views of the same data source may be delegated entirely to the database; a join across sources can never be delegated and always executes in the Denodo server, so cross-source joins are the main driver of server memory and CPU use.

The optimizer rewrites the query tree and pushes down selections and projections before joins. Predicates that cannot be delegated (unsupported function, different collation, implicit cast) become post-filters in Denodo and can turn a delegable join into a non-delegable one.

UNION requires compatible column types across branches; Denodo inserts implicit casts when types differ, and that cast blocks delegation. Align types explicitly in each branch to keep the union pushed down.

The execution trace (right-click the view > Execution Trace, or the 'Trace' tab) shows the generated SQL per data source, the number of rows and time per node, and whether each node was delegated ('pushed down') or executed locally. It is the primary tuning instrument.

Cost-based optimization uses table statistics gathered from the source; stale or missing statistics cause bad join-order and strategy choices. Refresh statistics after large source-side data changes.

Query timeout, 'Max results' (top N), and 'Max memory' per query are set at the database/view level; a join exceeding the memory limit spills or fails depending on configuration, so set them deliberately for known large joins.

Associations are logical relationships between view fields (typically a primary-key-like field on one side and a foreign-key-like field on the other). They do not change execution; they document joins and enable the graphical model and some automatic join suggestions.

Association types: one-to-one, one-to-many, many-to-one, many-to-many. Cardinality is metadata only - enforcing it is the responsibility of the underlying sources or of the queries written against the model.

Tags are name/value metadata attached to views, columns, databases, or data sources. They are the mechanism for classification (PII, sensitivity level, domain, owner) and can be used by policies and by the catalog search.

A tag can be defined at the server level with a fixed set of allowed values; using a controlled vocabulary keeps classification consistent across the model and lets policies match on tag values rather than on free text.

Column descriptions, view descriptions, and the 'Documentation' fields are exported with the model and surface in the Denodo catalog/Data Catalog; they are the primary human-readable governance artifact.

The Denodo catalog exposes views, associations, tags, and lineage. Lineage is derived from the view definitions, so a view created by arbitrary SQL (not by the graphical builder) may show reduced column-level lineage.

Model export/import (VQL or the Solution Manager) carries associations, tags, and descriptions; a deployment that omits them leaves the target environment without the governance metadata even though the views work.

Authentication can be local (VDP users), LDAP/Active Directory, Kerberos, SAML/OAuth for web clients, or pass-through to the source. Authentication answers 'who'; authorization answers 'what'.

Authorization is role-based: users are granted roles, roles are granted privileges on elements (Connect, Metadata, Execute, Write, Admin) at database, view, or column level. Privileges are additive; there is no explicit deny that overrides a grant.

Column-level privileges restrict which columns a role can see. A query that references a forbidden column fails or the column is excluded depending on the privilege configured; row-level restrictions are implemented with a 'restriction' on the view (a filter injected into every query).

Row restrictions are written as a condition using the special variable for the current user (e.g. user_name() / getuser()) so the same view returns different rows per user without duplicating the view.

Data masking is applied through a mask type on a column (e.g. full, partial, hash, random, custom expression). Masks are evaluated in the Denodo server, so the masked value never reaches the client, but the unmasked value still travels from the source to Denodo.

Masking policies can be attached to views/columns and to roles; a role with the 'unmask' privilege sees the original value. Test with a non-privileged user, because the administrator account typically bypasses masks.

Encryption: credentials stored in data sources can be encrypted with the server's secret; HTTPS/TLS is configured on the VDP server and on the web container, and client connections should be forced to TLS in production.

A view can be published as a REST web service (GET/POST/XML/JSON), a SOAP web service (WSDL generated), an OData entity set, or a GraphQL type. The publication is a separate artifact from the view definition.

REST publication options include the HTTP method, the input parameters (mapped to view parameters or to a WHERE clause), the output format (JSON, XML, CSV), and paging (rows per page, max rows). Without paging a large result set is returned in one response.

SOAP publication generates a WSDL from the view's input/output schema; complex types are derived from the view's column types, and changes to the view require republishing the service to update the WSDL.

OData exposes the view as an entity set supporting $filter, $select, $orderby, $top, $skip, and $expand for associations. Only the operations the underlying view supports are translated; unsupported ones return an error rather than silently ignoring the clause.

GraphQL publication exposes views as types and lets clients request exactly the fields they need; nested selections follow associations. Field-level authorization still applies, so a field the caller cannot see is rejected even if requested.

Service authentication reuses the VDP authentication (HTTP Basic, OAuth, or the configured auth method); the service runs as the authenticated user, so row restrictions and masks apply to API calls exactly as to SQL clients.

Each published service has its own URL path and can be scoped to a database; the service definition includes the 'swagger'/'openapi' description for REST and the metadata document for OData, which clients use for discovery.

The Denodo server cache stores the result of a view's query keyed by the query and its parameters; cache modes include off, partial (only fully delegated results), and full. A cached result is reused only when the same query and parameters recur.

Cache invalidation: entries expire after a TTL, can be invalidated manually, or can be invalidated when the underlying source changes (database cache invalidation). Without a TTL, stale data persists until explicit invalidation.

The MPP acceleration layer (Massive Parallel Processing) can pre-compute and store view data in an external engine (e.g. Presto/Trino, Spark, or an in-memory store) so repeated analytical queries avoid the source. It is configured per view with a refresh schedule.

Summary/recommendation: Denodo can recommend which views to cache or accelerate based on query history; the recommendation is based on observed frequency and cost, not on a static rule.

Query acceleration techniques in order of impact: push down as much as possible (delegation), reduce data early (filters and column pruning before joins), choose the right join strategy, then cache or MPP-precompute for repeated heavy queries.

Statistics: gather source table statistics and Denodo view statistics; the optimizer's join order and strategy depend on row-count estimates. Missing statistics on a large join is a common cause of memory errors.

Server tuning parameters include the number of worker threads, query memory limits, and the results cache size. These are set in the VDP server configuration and require a restart or a specific reload to take effect.

Monitor with the VDP administration tools: active queries, query history, and the execution trace. A query that is 'delegated' but slow usually means the source lacks an index; a query that is 'local' but slow usually means a large cross-source join.

Scheduler concepts: a job contains one or more tasks; a task runs a VDP query or a data movement (export/import) step; a schedule (cron-like) or a dependency triggers the job.

Data movement tasks can extract from a view and load into a target (another database, a file, Hadoop/HDFS, or a Denodo cache). The target is defined by a data source; the movement is configured with the source query, the target table, and the load mode.

Load modes: append (insert), overwrite/truncate-and-load (full refresh), and incremental (insert new rows and optionally update changed rows based on a key and a timestamp/high-water mark).

Incremental load requires a monotonically increasing watermark column (e.g. last_modified) and a stored high-water mark; the next run selects rows greater than the stored mark and updates the mark only after a successful load.

A job can define error handling: stop on error, continue, or retry N times with a delay. Without explicit handling, a failed task leaves the target partially loaded and the watermark unchanged, which is the safe default for incremental loads.

Dependencies let a job wait for another job's completion; this is how a chain (extract, transform, load, then refresh cache) is orchestrated without external schedulers.

Scheduler stores its own metadata (jobs, schedules, execution history) in a repository database; the history shows per-task rows read/written, duration, and error messages, which is the audit trail for data movement.

Design Studio is the Eclipse-based/ web development environment for building and editing VDP artifacts (data sources, base views, derived views, associations, tags) and for browsing the model.

Solution Manager manages the lifecycle: a 'solution' is a versioned set of elements exported from a source environment and deployed to a target environment (e.g. dev to test to prod). It tracks revisions and deployments.

Deployment in Solution Manager compares the source solution revision with the target environment and produces a deployment plan: elements to create, update, or delete. The plan is reviewed before execution.

VQL (Virtual DataPort Query Language) is the export format for model elements. A VQL export/import is the low-level alternative to Solution Manager and is useful for scripted or partial deployments, but it does not manage revisions.

Environment-specific values (hostnames, credentials, connection URLs) must be parameterized or overridden at deployment; hard-coded dev values deployed to prod are a common failure. Solution Manager supports property substitution for this.

Rollback: because Solution Manager keeps revisions, a previous revision can be redeployed to restore the prior state; a plain VQL import does not provide this unless the prior export was kept.

The development lifecycle is: build in Design Studio, export as a solution revision, deploy to the next environment via Solution Manager, validate with the target's own connections and credentials, then promote the same revision onward.

Core components: the Virtual DataPort server (the query engine and metadata store), the web container hosting the Denodo web tools (Design Studio, Scheduler web UI, catalog, data services), the Scheduler server, and the Solution Manager server. They can be installed together or distributed.

VDP stores its metadata in an internal repository (an embedded database or an external RDBMS). The external repository is recommended for production so that backup, HA, and growth are manageable.

Installation modes: standard (interactive), silent (response file), and distributed. The installer sets the license, the repository, the ports, and the admin credentials; the license file must match the edition and the host.

Administration: start/stop scripts per component, the VDP admin tool (or web admin) for users, roles, privileges, data sources, and server configuration, and log files under <DENODO_HOME>/logs for diagnosis.

Backup consists of the metadata repository plus the configuration and any custom extensions (drivers, jars). A view definition is recoverable only if the repository or a VQL export was saved.

Clustering/HA: multiple VDP servers can share an external repository and be fronted by a load balancer; cache and session behavior must be considered, since the cache is per-server unless a distributed cache is configured.

Upgrades follow a documented path: back up the repository and configuration, install the new version, migrate the repository, and validate the model and the custom extensions before switching traffic.