Denodo Associate cheat sheet

Data virtualization exposes a logical (virtual) data layer: queries are translated and pushed to sources at run time, and no copy of the data is persisted in the virtualization layer for base views.

A base view wraps one source object (table, file, API result, stored procedure); a derived view is defined over other views and can combine, join, aggregate, or transform them without touching the sources.

Virtual vs materialized: virtual views always read current source data; materialized/cached views trade freshness for performance and are refreshed on a schedule or on demand.

Logical data warehouse (LDW) pattern: keep the warehouse and marts, add a virtualization layer above them plus other sources, and expose a single logical schema to consumers.

Abstraction rule: consumers query logical view names and columns only; source schemas, connection details, and credentials are hidden behind the view, so source changes are absorbed by editing the view.

Federation: a single query can join data from heterogeneous sources (relational, NoSQL, files, SaaS APIs, web services) and the optimizer decides what is pushed down versus processed by the Denodo engine.

Data movement is not eliminated, only deferred: results still travel from source to engine to client, so network cost and source query cost remain part of the performance model.

Read-only by default: virtualization is primarily a query layer; write-back requires explicit configuration of insert/update/delete operations on the view and underlying source support.

Governance value: because every consumer goes through views, security, masking, lineage, and semantics are defined once at the logical layer rather than re-implemented per consumer.

Core split: the Design Studio (Eclipse-based, used by developers/administrators to create and manage views) is separate from the web container that serves client applications and the Data Catalog.

Virtual DataPort (VDP) is the query engine and server component; it stores metadata (views, data sources, permissions) in its own metadata database and executes client queries.

The Solution Manager is the centralized tool for deploying and promoting artifacts (views, data sources, policies) across environments such as development, test, and production.

Scheduler (Denodo Scheduler) runs jobs: cache refresh, exports, snapshots, and stored-procedure-style flows, with calendars, dependencies, and alerting.

Data Catalog is the metadata and self-service portal for discovering, profiling, and requesting access to published views; it is distinct from the administrative Design Studio.

Diagnostic and Monitoring tool provides server health, query statistics, and log inspection; query monitoring exposes running, waiting, and finished queries with their execution traces.

Cluster architecture: a load balancer in front of multiple VDP servers sharing a metadata repository, with a separate cluster of data catalog/web servers for the presentation tier.

Caching and MPP acceleration are optional server-side components; embedded Massively Parallel Processing (MPP) can hold cached data for high-concurrency analytical workloads.

Licensing is by edition (e.g., Developer/Standard/Enterprise tiers) and determines available features such as clustering, MPP, and advanced security modules.

Data source creation order: define the data source (JDBC URL, driver, credentials) → introspect to import schema → create base views → build derived views on top.

JDBC sources need the matching driver jar installed on the VDP server; connection pooling settings (min/max pool size, validation query, timeout) control reuse and failure detection.

Delegation (pushdown) capability differs per source adapter: the adapter declares which functions and operators it can execute natively; the rest is evaluated by Denodo.

Base view options include which columns are exposed, primary key declaration (needed for cache and some optimizations), and whether the view is searchable or cacheable.

Derived view building blocks: join, union, selection (filter), projection, aggregation, flatten (for arrays/XML), intersect/minus, and interface views for exposing a stable API.

Interface views define a published contract independent of implementation; swap the underlying implementation without breaking consumers that query the interface.

Data types are normalized to Denodo types on import; explicit casts may be needed when joining columns whose source types differ (e.g., varchar vs numeric IDs).

Query wrapping: the adapter can wrap a source query in an outer SELECT (adding filters, limits) or delegate the whole query; wrapper mode affects pushdown and performance.

Credentials can be stored per data source or passed through from the client (pass-through authentication) when the source must see the end user's identity.

Cost-based optimizer: Denodo estimates row counts and costs per branch and chooses join order, join method, and what to delegate; stale statistics lead to bad plans, so gather stats on views.

Pushdown rule of thumb: the more work delegated to the source, the less data crosses the network; operations Denodo cannot delegate (cross-source joins, some functions) run in the engine.

Join strategies: when both sides live in the same source, the join may be delegated as one source query; cross-source joins require fetching both sides and joining in the engine or via a temporary table.

Cache modes: full cache (entire view result stored), partial cache (query results stored per query pattern), and no cache; invalidation via TTL, schedule, or explicit refresh.

Cache is only usable when the view is cacheable and the query matches what is cached; highly selective ad-hoc predicates benefit less from full caching than repeated aggregate queries.

Materialized views and summaries precompute expensive aggregations; the optimizer can rewrite a query to use a summary table when it detects a matching aggregation.

Execution trace / query plan view shows each step: delegated SQL sent to sources, rows returned, time per node - use it to find the bottleneck node rather than guessing.

Smart query acceleration features include query rewriting, aggregation pushdown, and (in MPP-enabled setups) moving cached data into the MPP engine for parallel scans.

Avoid SELECT * on wide views and unnecessary cross-source joins in hot paths; reducing projected columns and pushing filters into the source cuts transfer volume directly.

Authentication vs authorization: authentication establishes who the user is (database users, LDAP/Active Directory, Kerberos, SAML/OAuth for web access); authorization decides what they may query.

Privilege model: grant SELECT/EXECUTE etc. on databases, views, and columns to users and roles; roles simplify administration and should be the primary grant target.

Row-level security via policies or view WHERE clauses filtered by session variables (e.g., current user, department); column-level security via column privileges or masking.

Data masking: replace or obfuscate sensitive column values at query time (e.g., partial masking of card numbers) so consumers see masked data without a copy.

Denodo supports policy-based security where a central policy (masking, row filter, encryption) is applied to many views at once instead of editing each view.

Data lineage and impact analysis come from the metadata graph: lineage shows where a view's data originates; impact analysis shows what breaks if a source or view changes.

Data sovereignty: keep data in its jurisdiction by leaving it at the source and virtualizing across borders; only query results cross the boundary, and masking can be applied before results leave.

Audit and monitoring: query logs and audit trails record who queried what and when; essential for compliance evidence and for detecting anomalous access patterns.

Encryption in transit (TLS between client, VDP, and sources) and at rest (metadata DB, cache stores) is configured per connection and per store, not globally by default.

Delivery modes: on-demand query (JDBC/ODBC, REST/JSON, OData), scheduled export to files, cached/snapshot delivery, and publish/subscribe style pushes to targets.

Denodo exposes data through multiple interfaces: JDBC, ODBC, REST web services (XML/JSON), OData, GraphQL, and the JDBC-based port used by BI tools - the same view can serve all of them.

Export jobs via Scheduler write results to files (CSV, Excel, etc.) or load them into target databases; jobs can be scheduled, chained, and alerted on failure.

Denodo Embedded MPP can serve as a delivery target for high-concurrency BI workloads by holding cached data and answering repeated queries without hitting sources.

Data movement targets include cloud storage, data warehouses, and lakes; the platform can generate the load (e.g., via connectors) rather than requiring external ETL for every path.

Caching for delivery: when consumers need low latency and can tolerate staleness, serve from cache; when they need current data, serve on demand and push filters to sources.

Result pagination and row limits protect consumers and sources: set max rows, timeouts, and fetch sizes on views and connections to prevent runaway queries.

Delivery to BI tools: publish views in the catalog, provide connection details and sample queries; BI tools connect via JDBC/ODBC and see only the published interface views.

Data marketplace concept: a curated catalog where data owners publish datasets (views) with descriptions, tags, sample data, and access terms; consumers search, evaluate, and request access.

Denodo Data Catalog provides search, metadata browsing, profiling (row counts, value distributions), lineage, and a request/approval workflow for access.

Self-service flow: discover dataset → inspect metadata and samples → request access → approval by owner → consumer connects via the published interface (JDBC/REST) or export.

Publishing a view to the marketplace requires meaningful business metadata (name, description, owner, tags, classification) - technical view names alone are not sufficient for discovery.

Access requests are governed by policies; approvals can be role-based or owner-based, and the grant is recorded so it can be audited and revoked.

Community/rating features (comments, ratings, usage stats) help consumers judge fitness; usage statistics also reveal which datasets are actually consumed and which are stale.

Self-service is bounded by security: consumers see only published interface views and masked columns; they cannot browse raw source schemas or connection credentials.

Marketplace governance: classify datasets (e.g., public/internal/confidential), attach terms of use, and keep a data owner accountable for each published asset.