---
# === IDENTITY ===
id: business/erp-integration/data-virtualization-vs-replication/2026
canonical_question: "When should you use data virtualization vs replication vs API calls for ERP integration?"
aliases:
  - "data virtualization vs ETL replication for ERP systems"
  - "Denodo vs CDC vs direct API calls ERP integration decision"
  - "should I replicate ERP data or virtualize it"
  - "data virtualization vs data warehouse for ERP reporting"
entity_type: erp_integration
domain: business > erp-integration > data-virtualization-vs-replication
region: global
jurisdiction: global
temporal_scope: 2024-2026

# === SYSTEM PROFILE ===
systems:
  - name: "Data Virtualization Platforms"
    vendor: "Denodo, TIBCO/Spotfire, Microsoft (D365 Virtual Entities), CData"
    version: "Denodo 9.x, TIBCO/SDV 2025, D365 Virtual Tables 2025"
    edition: "Enterprise (Denodo/TIBCO), included (D365 Virtual Entities)"
    deployment: "cloud | on-premise | hybrid"
    api_surface: "JDBC/ODBC, OData v4, REST, SQL"
  - name: "Data Replication / CDC Platforms"
    vendor: "Fivetran, Debezium, Oracle GoldenGate, Qlik Replicate, Striim, Airbyte"
    version: "2025-2026 releases"
    edition: "Open source (Debezium/Airbyte) to Enterprise (GoldenGate, Qlik)"
    deployment: "cloud | on-premise | hybrid"
    api_surface: "Kafka Connect, CDC log readers, REST, Bulk API connectors"
  - name: "Direct API Integration"
    vendor: "ERP-native (Salesforce REST, SAP OData, Oracle REST, Dynamics Web API)"
    version: "Current ERP API versions (2025-2026)"
    edition: "All editions"
    deployment: "cloud"
    api_surface: "REST, OData v4, SOAP, GraphQL"

# === VERIFICATION ===
last_verified: 2026-03-07
confidence: 0.84
version: 1.0
first_published: 2026-03-07

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: evolving
  last_breaking_change: "TIBCO Data Virtualization rebranded to Spotfire Data Virtualization (SDV) mid-2025"
  next_review: 2026-09-03
  change_sensitivity: moderate

# === CONSTRAINTS ===
constraints:
  - "Data virtualization adds query latency — every read hits the source system (or cache), typically 200ms-5s per query vs <10ms for local replicated data"
  - "Cloud SaaS ERPs (Salesforce, Workday, NetSuite) do not expose database-level access — virtualization requires going through API layers, not direct SQL"
  - "Denodo Enterprise license starts at ~$100K/year — cost-prohibitive for small/mid-market ERP projects"
  - "CDC-based replication (Debezium, GoldenGate) requires transaction log access — impossible on cloud SaaS ERPs without vendor-native CDC streams"
  - "D365 Virtual Entities are read-only, do not support joins across virtual and native tables, and lack audit/change tracking"
  - "Replication introduces storage cost and data staleness — CDC latency is typically 1-10s, batch ETL is 15min-24h"
  - "Direct API calls are subject to ERP rate limits (e.g., Salesforce 100K/24h, Dynamics 6K/5min per user) — high-frequency reads will exhaust quotas"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Need CDC implementation details specifically (Debezium, GoldenGate, Salesforce CDC)"
    use_instead: "business/erp-integration/change-data-capture-erp/2026"
  - condition: "Need batch vs real-time integration pattern decision (not about data access layer)"
    use_instead: "business/erp-integration/batch-vs-realtime-integration/2026"
  - condition: "Need D365 Virtual Entities specifically"
    use_instead: "business/erp-integration/dynamics-365-virtual-entities/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: access_pattern
    question: "What is your primary data access pattern?"
    type: choice
    options:
      - "ad-hoc queries and reporting (BI dashboards, analyst queries)"
      - "operational reads (application reads live data from ERP)"
      - "analytics and data warehouse (historical analysis, ML training)"
      - "cross-system joins (query across multiple ERPs/systems simultaneously)"
  - key: data_volume
    question: "What's your query volume against ERP data?"
    type: choice
    options:
      - "< 100 queries/day (low frequency, ad-hoc)"
      - "100-10,000 queries/day (operational reporting)"
      - "> 10,000 queries/day (high-frequency application reads)"
  - key: latency_requirement
    question: "What data freshness do you need?"
    type: choice
    options:
      - "real-time (< 1 second stale)"
      - "near-real-time (1-60 seconds stale)"
      - "periodic (minutes to hours stale is acceptable)"
      - "historical (daily/weekly snapshots are fine)"

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/business/erp-integration/data-virtualization-vs-replication/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-03-07)"

# === RELATED UNITS ===
related_kos:
  depends_on: []
  related_to:
    - id: "business/erp-integration/change-data-capture-erp/2026"
      label: "Change Data Capture for ERP Integration"
    - id: "business/erp-integration/batch-vs-realtime-integration/2026"
      label: "Batch vs Real-Time vs Event-Driven Integration Patterns"
    - id: "business/erp-integration/dynamics-365-virtual-entities/2026"
      label: "Dynamics 365 Virtual Entities"
  solves:
    - id: "business/erp-integration/master-data-management-erp/2026"
      label: "Master Data Management for ERP Integration"
  alternative_to:
    - id: "business/erp-integration/erp-bulk-import-comparison/2026"
      label: "ERP Bulk Import Comparison"
  often_confused_with:
    - id: "business/erp-integration/dynamics-365-dual-write-capabilities/2026"
      label: "Dynamics 365 Dual Write Capabilities"

# === SOURCES ===
sources:
  - id: src1
    title: "Data Virtualization vs ETL: Which Approach is Right for Your Business?"
    author: SNIC Solutions
    url: https://snicsolutions.com/blog/data-virtualization-vs-etl
    type: technical_blog
    published: 2025-09-15
    reliability: moderate_high
  - id: src2
    title: "Comparing caching in Denodo with other forms of replication like ETL"
    author: Denodo
    url: https://community.denodo.com/kb/en/view/document/Comparing%20caching%20in%20Denodo%20with%20other%20forms%20of%20replication%20like%20ETL
    type: official_docs
    published: 2025-01-01
    reliability: high
  - id: src3
    title: "When to use data virtualization and when replication?"
    author: Adastra
    url: https://adastracorp.com/insights/when-use-data-virtualization-and-when-replication-three-key-criteria-based-on-out-best-practise-to-consider-when-making-a-decision/
    type: technical_blog
    published: 2025-04-01
    reliability: high
  - id: src4
    title: "Virtual Entities: Are they a fit for your production environment?"
    author: Mind Over Machines
    url: https://www.mindovermachines.com/virtual-entities-are-they-a-fit-for-your-production-environment/
    type: technical_blog
    published: 2025-02-10
    reliability: moderate_high
  - id: src5
    title: "What Is Change Data Capture (CDC)? Methods & Use Cases"
    author: Striim
    url: https://www.striim.com/blog/change-data-capture-cdc-what-it-is-and-how-it-works/
    type: technical_blog
    published: 2025-06-01
    reliability: high
  - id: src6
    title: "Data Virtualization: Benefits, Use Cases, and Trends"
    author: Data Ladder
    url: https://dataladder.com/what-is-data-virtualization-benefits-use-cases-trends/
    type: technical_blog
    published: 2025-08-01
    reliability: moderate_high
  - id: src7
    title: "The future of TIBCO Data Virtualization: Big changes ahead"
    author: Spotfire/TIBCO
    url: https://www.spotfire.com/blog/2025/06/25/the-future-of-tibco-data-virtualization-big-changes-ahead/
    type: vendor_release_notes
    published: 2025-06-25
    reliability: high
---

# Data Virtualization vs Replication vs Direct API Calls for ERP Integration

## TL;DR

- **Bottom line**: Use data virtualization for cross-system ad-hoc queries and BI dashboards where real-time freshness matters but query volume is low; use replication (ETL/ELT/CDC) for high-volume analytics, data warehousing, and any workload exceeding 10,000 queries/day; use direct API calls for transactional operations and low-volume operational reads. [src1, src3]
- **Key limit**: Data virtualization adds 200ms-5s latency per query because every read traverses the network to the source system (or its cache) — replicated data responds in <10ms from local storage. [src2]
- **Watch out for**: Virtualizing high-volume transactional queries against ERP systems — you will exhaust API rate limits and degrade ERP performance for all users. [src3]
- **Best for**: Architecture decisions when designing the data access layer for ERP integration projects — choosing between virtualization, replication, and direct API calls per data flow.
- **Authentication**: Approach-independent — all three patterns use the same ERP auth flows (OAuth 2.0, JWT, TBA, certificates). Virtualization platforms add a credential management layer on top.

## System Profile

This card is a cross-platform architecture pattern comparison covering three fundamental approaches to accessing ERP data from external systems: data virtualization (query in place without moving data), data replication (copy data to a local store via ETL/ELT/CDC), and direct API calls (point-to-point reads against ERP APIs). The decision framework applies universally; the implementation specifics vary by ERP system and data platform.

Data virtualization platforms (Denodo, TIBCO/Spotfire SDV, D365 Virtual Entities, CData) provide a semantic layer that federates queries across multiple ERP systems and databases without physical data movement. Replication platforms (Fivetran, Debezium, Airbyte, Oracle GoldenGate, Qlik Replicate, Striim) physically copy ERP data to a target data store — either via batch ETL/ELT or near-real-time CDC. Direct API calls use ERP-native REST, OData, or SOAP endpoints for point-to-point data retrieval.

| Property | Value |
|---|---|
| **Scope** | Cross-platform architecture pattern comparison |
| **Approaches** | Data Virtualization, Data Replication (ETL/ELT/CDC), Direct API Calls |
| **Virtualization Tools** | Denodo 9.x, TIBCO/SDV, D365 Virtual Entities, CData Connect |
| **Replication Tools** | Fivetran, Debezium 3.x, Airbyte, GoldenGate 23ai, Qlik Replicate, Striim |
| **ERP Systems** | Salesforce, SAP S/4HANA, Oracle ERP Cloud, Dynamics 365, NetSuite, Workday |
| **Deployment** | Cloud, on-premise, hybrid (approach-dependent) |
| **Status** | GA — all three patterns are production-proven |

## API Surfaces & Capabilities

Each approach uses different technical surfaces to access ERP data. This table maps the data access mechanism per approach. [src1, src2]

| Approach | Access Mechanism | Protocol | Query Language | Write Support | Real-Time? | Caching |
|---|---|---|---|---|---|---|
| Data Virtualization (Denodo) | JDBC/ODBC virtual views | SQL over HTTPS | SQL, VQL | Read-only (most configs) | Yes (live queries) | Configurable (TTL, partial, full) |
| Data Virtualization (D365) | Dataverse Virtual Tables | OData v4 | FetchXML, OData queries | Read-only | Yes | No native caching |
| Data Virtualization (TIBCO/SDV) | JDBC/ODBC, REST | SQL, REST | SQL | Read-only (most configs) | Yes (live queries) | Configurable |
| Replication (CDC) | Kafka Connect, log readers | CDC log parsing, REST | N/A (push-based) | N/A (replication) | Near-real-time (1-10s) | N/A (data is local) |
| Replication (ETL/ELT) | Batch connectors | REST, JDBC, file-based | N/A (scheduled extraction) | N/A (replication) | No (batch scheduled) | N/A (data is local) |
| Direct API Calls | ERP REST/OData endpoints | HTTPS/JSON, OData | SOQL, OData $filter, SuiteQL | Full CRUD | Yes | Client-side only |

## Rate Limits & Quotas

The critical difference: data virtualization and direct API calls consume ERP API quotas; replication consumes them once during extraction and then queries run locally. [src3]

### Per-Approach Impact on ERP Rate Limits

| Approach | ERP API Calls Per Query | Daily Impact (1K queries/day) | Daily Impact (100K queries/day) | Rate Limit Risk |
|---|---|---|---|---|
| Data Virtualization (uncached) | 1-N per user query (fan-out to sources) | 1,000-5,000 API calls | 100K-500K API calls (likely exceeds limits) | HIGH — every user query hits ERP |
| Data Virtualization (cached, 5min TTL) | 0-1 per unique query per TTL window | 50-500 API calls | 5K-50K API calls | MODERATE — depends on cache hit rate |
| Replication (CDC) | 0 per read query (continuous stream) | 0 read API calls (CDC is push-based) | 0 read API calls | LOW — CDC uses log streaming, not API calls |
| Replication (ETL batch) | 0 per read query (scheduled bulk pulls) | Bulk extraction only (e.g., 1 Bulk API job/hour) | Same | LOW — API calls during extraction windows only |
| Direct API Calls | 1 per query | 1,000 API calls | 100,000 API calls (hits Salesforce Enterprise limit exactly) | HIGH — linear scaling with query volume |

### ERP-Specific Virtualization Constraints

| ERP System | Virtualization-Friendly? | Key Limitation | Recommended Approach |
|---|---|---|---|
| Salesforce | Moderate — REST API accessible but rate-limited | 100K API calls/24h (Enterprise), no direct DB access | Replicate via Bulk API; virtualize only for low-volume ad-hoc |
| SAP S/4HANA | Good — OData and CDS views are virtualization-ready | Performance depends on HANA sizing, RFC deprecated in cloud | Virtualize via CDS views; replicate via ODP/SLT for analytics |
| Oracle ERP Cloud | Moderate — REST API and BI Publisher accessible | No published hard rate limit but throttled per tenant | Replicate via BICC; virtualize for cross-system dashboards |
| Dynamics 365 | Good — Virtual Entities are native | Read-only, no joins across virtual/native, no audit trail | Use Virtual Entities for lookups; replicate for reporting [src4] |
| NetSuite | Poor — heavy governance unit consumption | 10 concurrent requests, governance units per script execution | Replicate via Saved Searches or SuiteQL; avoid virtualization |
| Workday | Poor — undisclosed throttle limits, RaaS is batch-only | No real-time query API suitable for virtualization | Replicate via RaaS/EIB; use REST API for single-record lookups |

## Authentication

Authentication is approach-independent. All three patterns authenticate against the same ERP endpoints using the same credential types. The difference is where credentials are managed.

| Approach | Credential Management | Token Refresh | Multi-System Auth |
|---|---|---|---|
| Data Virtualization | Centralized in virtualization platform — one place for all ERP credentials | Automatic (platform-managed) | Built-in — single credential store for all sources |
| Replication | In replication tool (Fivetran, Debezium) or iPaaS | Automatic (tool-managed) | Per-connector configuration |
| Direct API Calls | In calling application code | Application must handle token refresh and expiry | Each integration manages its own credentials |

### Authentication Gotchas

- Data virtualization platforms store ERP credentials in their own credential vaults — ensure the vault meets your security/compliance requirements before centralizing secrets there [src2]
- CDC-based replication (Debezium) against on-premise databases requires database-level credentials with replication privileges — this is a different permission set than API OAuth tokens [src5]
- Direct API calls from multiple applications can each exhaust rate limits independently — a virtualization layer can centralize and throttle to prevent this [src3]

## Constraints
<!-- Agents: read this section before recommending any integration approach from this unit.
     These are hard constraints that must not be violated. -->

- **Cloud SaaS ERPs cannot be virtualized at the database level** — Salesforce, Workday, NetSuite, and Oracle Cloud do not expose database connections. Virtualization must go through their REST/OData APIs, which are rate-limited [src6]
- **Data virtualization is read-only for most ERP use cases** — writing back through a virtualization layer is technically possible (Denodo supports it) but is rarely used for ERP data due to transaction integrity concerns [src2]
- **CDC requires transaction log access** — impossible on cloud SaaS ERPs without vendor-native CDC (Salesforce CDC, SAP ODP). On-premise ERP databases (Oracle EBS, SAP ECC on HANA) support Debezium/GoldenGate [src5]
- **Virtualization latency is additive** — a query that joins data from 3 ERP systems incurs the latency of the slowest source plus federation overhead. A 3-way join across Salesforce + SAP + D365 can take 5-15 seconds [src3]
- **Denodo/TIBCO licensing is enterprise-only** — Denodo starts at ~$100K/year, TIBCO/SDV is similar. D365 Virtual Entities are included but limited. CData Connect Cloud is the only mid-market option (~$500/month) [src7]
- **Replication requires storage infrastructure** — data warehouse, data lake, or dedicated database to store replicated data, plus ongoing storage costs that scale with data volume

## Integration Pattern Decision Tree

```
START — Need to access ERP data from external system
├── What's the primary use case?
│   ├── Ad-hoc BI queries / dashboards (analyst-driven, <1K queries/day)
│   │   ├── Need cross-system joins (multiple ERPs/sources)?
│   │   │   ├── YES → DATA VIRTUALIZATION (Denodo/TIBCO/SDV)
│   │   │   │   └── Budget for $100K+/year licensing?
│   │   │   │       ├── YES → Denodo/TIBCO with caching enabled
│   │   │   │       └── NO → CData Connect Cloud or replicate to data warehouse
│   │   │   └── NO → Direct API calls with client-side caching
│   │   └── ERP has good virtualization support? (SAP CDS views, D365 Virtual Entities)
│   │       ├── YES → ERP-native virtualization features
│   │       └── NO → Replicate to data warehouse, query locally
│   ├── Operational application reads (>1K queries/day, low latency)
│   │   ├── Data freshness requirement?
│   │   │   ├── Real-time (<1s stale) → Direct API calls (watch rate limits)
│   │   │   ├── Near-real-time (1-60s stale) → REPLICATION via CDC
│   │   │   └── Periodic (minutes-hours stale) → REPLICATION via ETL/ELT batch
│   │   └── Query volume >10K/day?
│   │       ├── YES → REPLICATION (CDC or batch) — virtualization will exhaust API limits
│   │       └── NO → Direct API calls with application-level caching
│   ├── Analytics / data warehouse / ML training
│   │   └── Always → REPLICATION (ETL/ELT or CDC to data warehouse)
│   │       ├── Need historical snapshots → ETL/ELT with SCD (slowly changing dimensions)
│   │       └── Need latest data for dashboards → CDC for continuous replication
│   └── Data migration / initial load
│       └── Always → REPLICATION (bulk extraction via Bulk API / FBDI / DMF)
├── What's the data volume?
│   ├── < 10K records total → Any approach works
│   ├── 10K-1M records → Virtualization with caching OR replication
│   └── > 1M records → REPLICATION required — virtualization cannot handle scan queries efficiently
└── What's the budget?
    ├── Enterprise ($100K+/year for tooling) → Denodo + CDC (best of both worlds)
    ├── Mid-market ($10K-50K/year) → CData Connect or Fivetran/Airbyte replication
    └── Minimal → Open-source CDC (Debezium) + direct API calls
```

## Quick Reference

| Dimension | Data Virtualization | Replication (CDC) | Replication (ETL/ELT) | Direct API Calls |
|---|---|---|---|---|
| **Data Freshness** | Real-time (live query) | Near-real-time (1-10s lag) | Stale (15min-24h lag) | Real-time (per request) |
| **Query Latency** | 200ms-5s (source + network) | <10ms (local data) | <10ms (local data) | 200ms-2s (API round-trip) |
| **Data Volume Limit** | Low-moderate (<1M records scanned) | Unlimited (incremental) | Unlimited (batch) | Per-request limits (2K-10K records) |
| **ERP API Impact** | High (every uncached query) | Low (CDC stream or scheduled pulls) | Low (scheduled bulk pulls) | High (every request) |
| **Storage Cost** | None (no data copied) | Moderate-high (full replica) | Moderate-high (full replica) | None (no data stored) |
| **Infrastructure** | Virtualization platform | Kafka + CDC tool + target DB | ETL tool + target DB/warehouse | Application code only |
| **Setup Complexity** | Moderate (connectors + semantic layer) | High (CDC infrastructure + schema mapping) | Moderate (ETL pipelines) | Low (API client code) |
| **Write Support** | Read-only (typically) | N/A (one-directional) | N/A (one-directional) | Full CRUD |
| **Cross-System Joins** | Native (federation) | Requires shared target DB | Requires shared target DB | Manual in application code |
| **Annual Cost** | $100K+ (Denodo), $6K+ (CData Cloud) | Free (Debezium) to $200K+ (GoldenGate) | $10K-100K (Fivetran, Airbyte) | Free (API calls only) |
| **Best For** | Ad-hoc cross-system BI, <1K queries/day | High-volume operational reads, event sync | Analytics, data warehouse, historical reporting | Transactional CRUD, low-volume lookups |

[src1, src2, src3, src5]

## Step-by-Step Integration Guide

### 1. Assess your data access requirements

Map each data flow in your ERP integration to one of the three patterns by answering: (a) query volume, (b) freshness requirement, (c) data volume, (d) write needs. Most projects use a hybrid of 2-3 approaches. [src3]

```text
# Assessment template — fill for each data flow
Data Flow:          [e.g., "Sales order dashboard"]
Source ERP:         [e.g., "Salesforce"]
Query Volume:       [e.g., "500 queries/day from 20 analysts"]
Freshness Need:     [e.g., "< 1 hour stale is fine"]
Data Volume:        [e.g., "200K orders, growing 5K/month"]
Write Required:     [e.g., "No — read-only reporting"]
→ Recommended:      [e.g., "Replication via ETL to data warehouse"]
```

**Verify**: Each data flow should map clearly to one approach. If you're mixing virtualization with >10K queries/day, reconsider.

### 2. Set up data virtualization (Denodo example)

Create a virtual view that federates data from SAP and Salesforce without replication. [src2]

```sql
-- Denodo VQL: Create a virtual view joining SAP and Salesforce data
-- Input:  SAP S/4HANA OData connection + Salesforce REST connection
-- Output: Unified customer view without data movement

-- Step 1: Create data source connections
CREATE DATASOURCE CUSTOM sap_s4hana
    CLASSNAME = 'com.denodo.connect.odata.ODataWrapper'
    JNDINAME = 'sap_s4hana_odata'
    PROPERTIES (
        'SERVICE_URL' = 'https://your-sap.example.com/sap/opu/odata/sap/API_BUSINESS_PARTNER',
        'AUTH_TYPE' = 'BASIC',
        'USER' = '${sap_user}',
        'PASSWORD' = '${sap_password}' ENCRYPTED
    );

CREATE DATASOURCE CUSTOM salesforce_rest
    CLASSNAME = 'com.denodo.connect.salesforce.SalesforceWrapper'
    PROPERTIES (
        'LOGIN_URL' = 'https://login.salesforce.com',
        'AUTH_TYPE' = 'OAUTH_JWT',
        'CONSUMER_KEY' = '${sf_consumer_key}',
        'PRIVATE_KEY_FILE' = '${sf_private_key_path}'
    );

-- Step 2: Create virtual view federating both sources
CREATE VIEW unified_customer_360 AS
    SELECT
        sf.Id AS salesforce_id,
        sf.Name AS customer_name,
        sf.AnnualRevenue AS sf_revenue,
        sap.BusinessPartner AS sap_bp_number,
        sap.OrganizationBPName1 AS sap_company_name,
        sap.CreditStandingStatus AS sap_credit_status
    FROM salesforce_accounts sf
    INNER JOIN sap_business_partners sap
        ON sf.SAPBusinessPartnerID__c = sap.BusinessPartner
    CONTEXT (
        'cache_status' = 'partial',
        'cache_expiration' = '300'  -- 5-minute cache TTL
    );
```

**Verify**: `SELECT COUNT(*) FROM unified_customer_360` → returns record count without error. Check Denodo monitor for query execution time <5s.

### 3. Set up CDC replication (Debezium + Kafka example)

Configure CDC-based replication from an on-premise Oracle ERP database to a PostgreSQL analytics store. [src5]

```json
// Debezium Oracle CDC connector configuration
// Input:  Oracle EBS/ERP database with supplemental logging enabled
// Output: Real-time change events streamed to Kafka topics
{
  "name": "erp-oracle-cdc",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "tasks.max": "1",
    "database.hostname": "erp-oracle.internal",
    "database.port": "1521",
    "database.user": "cdc_replication_user",
    "database.password": "${ORACLE_CDC_PASSWORD}",
    "database.dbname": "ERPDB",
    "database.pdb.name": "ERPPDB",
    "topic.prefix": "erp",
    "schema.include.list": "AP,AR,GL",
    "table.include.list": "AP.AP_INVOICES_ALL,AR.RA_CUSTOMER_TRX_ALL,GL.GL_JE_LINES",
    "database.history.kafka.bootstrap.servers": "kafka-broker:9092",
    "database.history.kafka.topic": "erp.schema-changes",
    "snapshot.mode": "initial",
    "log.mining.strategy": "online_catalog",
    "log.mining.continuous.mine": "true",
    "heartbeat.interval.ms": "10000",
    "transforms": "route",
    "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
    "transforms.route.regex": "erp\\.(.+)\\.(.+)",
    "transforms.route.replacement": "erp-cdc.$1.$2"
  }
}
```

**Verify**: `curl localhost:8083/connectors/erp-oracle-cdc/status` → `"state": "RUNNING"`. Check Kafka topic `erp-cdc.AP.AP_INVOICES_ALL` for incoming change events.

### 4. Set up direct API polling with caching (Python example)

For low-volume operational reads where replication is overkill. [src6]

```python
# Input:  Salesforce REST API credentials, SOQL query
# Output: Cached query results with configurable TTL

import requests
import time
from functools import lru_cache
from typing import Optional

class ERPApiCache:
    """Simple TTL cache for ERP API reads.
    Use for <1,000 queries/day. Beyond that, switch to replication."""

    def __init__(self, base_url: str, access_token: str, cache_ttl_seconds: int = 300):
        self.base_url = base_url
        self.access_token = access_token
        self.cache_ttl = cache_ttl_seconds
        self._cache = {}  # key -> (timestamp, data)

    def query(self, soql: str) -> dict:
        cache_key = soql.strip().lower()
        now = time.time()

        # Return cached result if within TTL
        if cache_key in self._cache:
            cached_time, cached_data = self._cache[cache_key]
            if now - cached_time < self.cache_ttl:
                return cached_data

        # Fetch from ERP API
        response = requests.get(
            f"{self.base_url}/services/data/v62.0/query",
            params={"q": soql},
            headers={
                "Authorization": f"Bearer {self.access_token}",
                "Content-Type": "application/json"
            },
            timeout=30
        )
        response.raise_for_status()
        data = response.json()

        # Cache the result
        self._cache[cache_key] = (now, data)
        return data

# Usage
client = ERPApiCache(
    base_url="https://your-instance.salesforce.com",
    access_token="your_oauth_token",
    cache_ttl_seconds=300  # 5-minute cache
)
result = client.query("SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue > 1000000")
```

**Verify**: Run the same query twice within 5 minutes — second call should return instantly (cache hit). Check Salesforce API usage: Setup → System Overview → API Usage.

## Code Examples

### Python: Choosing approach at runtime based on query characteristics

```python
# Input:  Query metadata (volume, freshness, data size)
# Output: Recommended approach string

def recommend_data_access_approach(
    queries_per_day: int,
    max_staleness_seconds: int,
    total_records: int,
    needs_cross_system_joins: bool,
    needs_write_access: bool,
    budget_tier: str  # "enterprise", "mid-market", "minimal"
) -> str:
    """Decision engine for data virtualization vs replication vs API.

    Returns one of: 'virtualization', 'cdc_replication',
    'etl_replication', 'direct_api'
    """
    # Write access eliminates virtualization and replication
    if needs_write_access:
        return "direct_api"

    # High-volume reads must use replication
    if queries_per_day > 10_000:
        if max_staleness_seconds < 60:
            return "cdc_replication"
        return "etl_replication"

    # Large data scans must use replication
    if total_records > 1_000_000:
        if max_staleness_seconds < 60:
            return "cdc_replication"
        return "etl_replication"

    # Cross-system joins favor virtualization (if budget allows)
    if needs_cross_system_joins:
        if budget_tier == "enterprise":
            return "virtualization"
        return "etl_replication"  # Replicate to shared warehouse

    # Low-volume, real-time reads
    if queries_per_day < 100 and max_staleness_seconds < 1:
        return "direct_api"

    # Low-volume with some staleness tolerance
    if queries_per_day < 1_000:
        if budget_tier == "enterprise" and max_staleness_seconds < 300:
            return "virtualization"
        return "direct_api"  # With application-level caching

    # Default for moderate volume
    if max_staleness_seconds < 60:
        return "cdc_replication"
    return "etl_replication"
```

### SQL: Denodo virtual view with intelligent caching

```sql
-- Input:  Multiple ERP data sources connected to Denodo
-- Output: Unified virtual view with per-source caching policies

-- Fast-changing data: short cache (Salesforce opportunities)
ALTER VIEW salesforce_opportunities
    CACHE PARTIAL
    EXPIRATION 60;  -- 60-second TTL for active deal data

-- Slow-changing data: long cache (SAP material master)
ALTER VIEW sap_material_master
    CACHE FULL
    EXPIRATION 86400  -- 24-hour TTL for reference data
    PRELOAD ON STARTUP;

-- Cross-system join uses cached data from both sources
CREATE VIEW cross_erp_order_view AS
    SELECT
        sf.OpportunityId,
        sf.Amount,
        sap.MaterialNumber,
        sap.MaterialDescription,
        d365.SalesOrderNumber
    FROM salesforce_opportunities sf
    JOIN sap_material_master sap ON sf.ProductCode__c = sap.MaterialNumber
    JOIN d365_sales_orders d365 ON sf.D365OrderId__c = d365.SalesOrderNumber;
-- This query hits cache for SAP (24h), cache for SF (60s), live for D365
```

### cURL: Test D365 Virtual Entity performance

```bash
# Input:  Dynamics 365 environment URL, bearer token
# Output: Virtual entity query response with timing

# Query a Virtual Entity (Finance & Operations data exposed to Dataverse)
time curl -s -w "\nHTTP Status: %{http_code}\nTotal Time: %{time_total}s\n" \
  -H "Authorization: Bearer $D365_TOKEN" \
  -H "OData-MaxVersion: 4.0" \
  -H "OData-Version: 4.0" \
  -H "Accept: application/json" \
  "https://your-org.crm.dynamics.com/api/data/v9.2/mserp_vendors?\$select=mserp_vendoraccountnumber,mserp_name&\$top=10"

# Expected: Total Time should be 1-5s for Virtual Entities
# If >5s consistently, consider replicating this data instead
```

## Data Mapping

### Data Representation Differences by Approach

| Aspect | Virtualization | Replication | Direct API |
|---|---|---|---|
| **Schema** | Virtual views match source schema (or transformed) | Target schema — may differ from source | API response schema (JSON/XML) |
| **Data types** | Translated by virtualization engine | Mapped during ETL/CDC pipeline | Raw API types (string dates, etc.) |
| **Null handling** | Source-dependent (passed through) | Can be transformed during load | API-dependent (null vs absent field) |
| **Currency** | Source format (may differ per ERP) | Standardized during transformation | Source format |
| **Datetime** | Source timezone (UTC in cloud ERPs) | Converted during transformation | UTC or source timezone |
| **IDs/Keys** | Source IDs (no surrogate keys) | Surrogate keys possible in target | Source IDs only |

### Data Type Gotchas

- Denodo automatically maps source types to JDBC types, but precision loss can occur with SAP BCD (Binary Coded Decimal) fields — validate financial amounts after federation [src2]
- CDC replication preserves source data types in Kafka (Avro/JSON schema) but the sink connector must handle type mapping to the target — PostgreSQL NUMERIC vs Oracle NUMBER vs Snowflake NUMBER are not equivalent [src5]
- D365 Virtual Entities return all datetime fields in UTC regardless of user timezone settings — the calling application must handle timezone conversion [src4]
- Cross-system joins in virtualization can produce unexpected results when joining on string representations of IDs — SAP Business Partner "0000001234" vs Salesforce "001234" require trim/pad transformations in the virtual view [src3]

## Error Handling & Failure Points

### Common Error Scenarios by Approach

| Approach | Error | Cause | Resolution |
|---|---|---|---|
| Virtualization | Query timeout (30-120s) | Source ERP slow or unavailable | Configure timeout fallback to cached data; add circuit breaker |
| Virtualization | Source connection pool exhausted | Too many concurrent queries | Increase pool size or enable full caching for hot views |
| Virtualization | Rate limit exceeded on source ERP | Uncached queries exceeding ERP API quota | Enable aggressive caching; switch to replication for high-volume views |
| CDC Replication | Replication slot lag growing | Consumer too slow or backpressure | Scale Kafka consumers; check for slow sink connectors |
| CDC Replication | Schema change breaks pipeline | ALTER TABLE on source without notification | Use Debezium schema registry with compatibility mode |
| CDC Replication | Supplemental logging disabled | DBA disabled it during maintenance | Monitor with heartbeat checks; alert on log gap |
| ETL Replication | Bulk API timeout on large extract | Extracting too many records in single job | Chunk into smaller date-range batches |
| ETL Replication | Stale data due to failed pipeline run | ETL job failed silently overnight | Implement pipeline observability with freshness SLAs |
| Direct API | 429 Too Many Requests | Rate limit exceeded | Exponential backoff (2^n seconds, max 5 retries, jitter) |
| Direct API | 504 Gateway Timeout | ERP under load, complex query | Simplify query, add selective filters, reduce payload |

[src2, src5, src6]

### Failure Points in Production

- **Virtualization cascade failure**: When one of three federated ERP sources goes down, the entire cross-system virtual view fails — even if the failing source contributes only 5% of the data. Fix: `Configure per-source timeouts and graceful degradation in Denodo (CONTEXT clause with TIMEOUT and DEFAULT_VALUE)`. [src2]
- **CDC replication gap after ERP maintenance**: ERP database restarts can invalidate replication slots (PostgreSQL) or log positions (Oracle). Fix: `Use Debezium snapshot.mode=when_needed to automatically re-snapshot when log position is lost`. [src5]
- **ETL pipeline silent failure**: A Fivetran sync fails at 2 AM but dashboards show yesterday's data without warning. Fix: `Implement data freshness checks — compare max(last_modified) in target vs source; alert when delta exceeds SLA`. [src1]
- **Rate limit exhaustion from virtualization**: Denodo users running ad-hoc queries against Salesforce exhaust the 100K/24h API limit by noon. Fix: `Enable Denodo query governance — set per-user and per-view query quotas; mandate caching for frequently-accessed views`. [src3]
- **D365 Virtual Entity join failure**: Attempting to join virtual table columns with native Dataverse columns returns "LinkEntity with ColumnSet not supported." Fix: `Cannot be fixed — this is a platform limitation. Replicate the data to Dataverse instead`. [src4]

## Anti-Patterns

### Wrong: Virtualizing high-volume transactional queries

```python
# BAD — Using data virtualization for an application making 50K reads/day
# This will exhaust ERP API limits and add latency to every read

def get_product_price(product_id: str) -> float:
    # Every call hits the ERP API through the virtualization layer
    result = denodo_client.query(
        f"SELECT price FROM erp_product_master WHERE id = '{product_id}'"
    )
    return result[0]["price"]

# Called 50,000 times/day from e-commerce checkout flow
# Result: ERP rate limit exceeded by noon, checkout failures
```

### Correct: Replicate reference data, query locally

```python
# GOOD — Replicate product master to local cache/DB via CDC
# ERP API is called 0 times for reads — CDC stream pushes changes

def get_product_price(product_id: str) -> float:
    # Query local PostgreSQL replica — <5ms latency
    result = local_db.execute(
        "SELECT price FROM product_master_replica WHERE id = %s",
        (product_id,)
    )
    return result[0]["price"]

# CDC keeps local replica 1-10s behind source
# 50,000 reads/day cost 0 ERP API calls
```

### Wrong: Replicating everything "just in case"

```python
# BAD — Replicating all 500 Salesforce objects to Snowflake
# Most are never queried; storage and sync costs are wasted

replication_config = {
    "source": "salesforce",
    "target": "snowflake",
    "objects": "ALL",  # 500+ objects, 2TB of data
    "schedule": "every_15_minutes"
}
# Result: $5,000/month Snowflake storage, $2,000/month Fivetran
# Only 12 objects are actually queried
```

### Correct: Replicate what's needed, virtualize the rest

```python
# GOOD — Replicate high-volume objects; virtualize low-volume on-demand

replication_config = {
    "source": "salesforce",
    "target": "snowflake",
    "objects": [
        "Account", "Contact", "Opportunity", "OpportunityLineItem",
        "Lead", "Campaign", "CampaignMember", "Task",
        "Event", "Case", "Product2", "PricebookEntry"
    ],  # 12 high-volume objects actually used in dashboards
    "schedule": "every_15_minutes"
}

# Low-volume objects accessed via Denodo virtualization on-demand
# e.g., ContentDocument, Entitlement, BusinessHours — <10 queries/day
```

### Wrong: Using direct API calls for analytics queries

```python
# BAD — Running analytics queries via live API calls
# Each dashboard refresh makes dozens of API calls

def get_revenue_by_region_dashboard():
    # 6 API calls per dashboard load, 50 users, refreshed every 5 min
    # = 6 * 50 * 288 (per day) = 86,400 API calls/day just for one dashboard
    regions = sf_api.query("SELECT Region__c FROM Account GROUP BY Region__c")
    for region in regions["records"]:
        revenue = sf_api.query(
            f"SELECT SUM(Amount) FROM Opportunity "
            f"WHERE Account.Region__c = '{region['Region__c']}' "
            f"AND StageName = 'Closed Won'"
        )
    return results
```

### Correct: Replicate to analytics store, query locally

```python
# GOOD — Replicate opportunity data to warehouse, run analytics locally

def get_revenue_by_region_dashboard():
    # Single query against local Snowflake/BigQuery replica
    # 0 ERP API calls, <500ms response time
    result = warehouse.query("""
        SELECT a.region, SUM(o.amount) AS total_revenue
        FROM opportunity_replica o
        JOIN account_replica a ON o.account_id = a.id
        WHERE o.stage_name = 'Closed Won'
        AND o.close_date >= DATEADD(month, -12, CURRENT_DATE)
        GROUP BY a.region
        ORDER BY total_revenue DESC
    """)
    return result
```

## Common Pitfalls

- **Underestimating virtualization query cost**: Each uncached virtualization query consumes 1-N ERP API calls depending on query complexity. A simple `SELECT *` with a filter might cost 1 API call, but a join across 3 objects with aggregation can cost 5-10 API calls — and users do not see this cost. Fix: `Enable query cost tracking in Denodo; set per-user daily query budgets; mandate caching for all production views`. [src2]
- **Ignoring cache invalidation complexity**: Denodo cache with a 5-minute TTL means users might see data up to 5 minutes stale. For financial data (e.g., credit limits, available inventory), this can cause over-allocation. Fix: `Use Denodo cache invalidation listeners tied to ERP change events (Platform Events, webhooks) for critical data paths`. [src2]
- **Choosing CDC for cloud SaaS ERPs without checking feasibility**: Debezium and GoldenGate require database-level log access. Salesforce, Workday, and NetSuite do not provide this. Fix: `For cloud SaaS ERPs, use vendor-native CDC (Salesforce CDC, SAP ODP) or API-based incremental extraction with timestamp filters`. [src5]
- **Not accounting for virtualization platform outage**: If Denodo goes down, every application that reads ERP data through it fails. Unlike replication (where data is local and survives source outages), virtualization creates a single point of failure. Fix: `Deploy Denodo in HA cluster (active-active); consider maintaining a parallel replicated data path for critical applications`. [src3]
- **Mixing virtualization and replication for the same data without governance**: One team virtualizes Salesforce Account data while another replicates it to Snowflake. Reports from each path show different numbers due to caching and sync timing. Fix: `Designate one canonical path per data object — either virtualized or replicated, never both for the same consumer use case`. [src1]

## Diagnostic Commands

```bash
# === Data Virtualization (Denodo) ===

# Check cache status and hit rate for a virtual view
# Run in Denodo VQL Shell
# SELECT * FROM GET_CACHE_STATUS() WHERE viewname = 'unified_customer_360';

# Check active connections to ERP sources
# SELECT * FROM GET_CONNECTIONS() WHERE datasource_type = 'CUSTOM';

# Monitor query execution plan (push-down vs. post-processing)
# DESC QUERYPLAN SELECT * FROM unified_customer_360 WHERE region = 'US';

# === CDC Replication (Debezium) ===

# Check connector status and lag
curl -s localhost:8083/connectors/erp-oracle-cdc/status | python3 -m json.tool

# Check current replication offset (Kafka)
kafka-consumer-groups.sh --bootstrap-server kafka:9092 \
  --group erp-cdc-sink --describe

# Check replication slot lag (PostgreSQL source)
# SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
#   AS lag_bytes FROM pg_replication_slots WHERE slot_name = 'debezium';

# === Direct API ===

# Check Salesforce API usage remaining
curl -s -H "Authorization: Bearer $SF_TOKEN" \
  "https://your-instance.salesforce.com/services/data/v62.0/limits" \
  | python3 -c "import sys,json; d=json.load(sys.stdin); print(f'DailyApiRequests: {d[\"DailyApiRequests\"][\"Remaining\"]}/{d[\"DailyApiRequests\"][\"Max\"]}')"

# Check D365 rate limit headers from last response
curl -s -D - -H "Authorization: Bearer $D365_TOKEN" \
  "https://your-org.crm.dynamics.com/api/data/v9.2/accounts?\$top=1" \
  2>&1 | grep -i 'x-ms-ratelimit\|retry-after'
```

## Version History & Compatibility

| Tool/Platform | Version | Release | Status | Key Changes |
|---|---|---|---|---|
| Denodo Platform | 9.1 | 2025-Q4 | Current | AI-enhanced query optimizer, improved cloud connectors |
| Denodo Platform | 9.0 | 2025-Q1 | Supported | Data catalog integration, GraphQL support |
| Denodo Platform | 8.0 | 2023-Q1 | Supported (extended) | Major architecture refresh |
| TIBCO Data Virtualization | SDV 2025 | 2025-Q3 | Current | Rebranded to Spotfire Data Virtualization |
| TIBCO Data Virtualization | TDV 8.8 | 2024-Q4 | Maintenance | Last release under TIBCO name |
| D365 Virtual Entities | 2025 Wave 2 | 2025-10 | Current | Improved OData performance, more F&O entities |
| Debezium | 3.4.x | 2025-12 | Current | Java 17+ required, Kafka 3.x baseline |
| Debezium | 2.7.x | 2024-06 | EOL | Last Java 11 compatible version |
| CData Connect Cloud | 2025.2 | 2025-Q4 | Current | Added SAP HANA and Workday connectors |

[src2, src5, src7]

### Deprecation Notes

- TIBCO Data Virtualization is transitioning to Spotfire Data Virtualization (SDV) — existing TDV licenses will be migrated. No feature parity gaps announced [src7]
- Debezium 2.x reached EOL in 2025 — upgrade to 3.x requires Java 17+ and Kafka 3.x [src5]
- D365 Virtual Entities roadmap is unclear regarding write support and join capabilities — Microsoft has not committed to feature timeline [src4]

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Cross-system ad-hoc BI queries, <1K queries/day, budget for enterprise tooling | High-volume application reads (>10K queries/day) | CDC replication (business/erp-integration/change-data-capture-erp/2026) |
| Need real-time data freshness for low-volume dashboards | Analytics workloads scanning millions of records | ETL/ELT replication to data warehouse |
| Quick POC without building ETL pipelines | Cloud SaaS ERPs with strict rate limits (Salesforce, NetSuite) | API-based incremental extraction with local caching |
| SAP S/4HANA CDS view consumption (virtualization-friendly architecture) | Need write access back to ERP | Direct API calls (business/erp-integration/{system}-rest-api-capabilities/2026) |
| Centralized data governance across multiple ERPs | Budget <$50K/year for data platform tooling | Open-source replication (Debezium + Airbyte) |
| D365 read-only lookups from Power Platform apps | Complex joins between virtual and native D365 data | Replicate F&O data to Dataverse (business/erp-integration/dynamics-365-dual-write-capabilities/2026) |

## Cross-System Comparison

| Capability | Data Virtualization | CDC Replication | ETL/ELT Replication | Direct API Calls |
|---|---|---|---|---|
| **Implementation Time** | 2-4 weeks (connectors + views) | 4-8 weeks (infrastructure + pipelines) | 2-6 weeks (pipeline development) | Days-weeks (per integration) |
| **Ongoing Maintenance** | Low (no pipelines to monitor) | Moderate (monitor lag, schema drift) | Moderate (pipeline failures, scheduling) | Low (per-integration) |
| **Data Freshness** | Real-time (millisecond stale with live query) | Near-real-time (1-10s) | Periodic (15min-24h) | Real-time (per request) |
| **Query Performance** | 200ms-5s (network bound) | <10ms (local storage) | <10ms (local storage) | 200ms-2s (API bound) |
| **Scalability Ceiling** | Low-moderate (source API limits) | High (Kafka scales horizontally) | High (warehouse scales) | Low (API rate limits) |
| **Failure Impact** | Source outage = query failure | Source outage = stale local data (still queryable) | Source outage = stale data | Source outage = total failure |
| **Cost at Scale** | High (licensing + ERP API cost) | Moderate (infrastructure + storage) | Moderate (ETL tool + storage) | Low (API calls only) |
| **Data Governance** | Centralized (single semantic layer) | Distributed (per-pipeline) | Distributed (per-pipeline) | None (per-application) |
| **Multi-Source Joins** | Native (federation engine) | Requires shared target DB | Requires shared target DB | Manual (application code) |
| **Historical Data** | No (only current state) | Partial (if configured with event sourcing) | Yes (SCD, snapshots) | No (current state only) |
| **Vendor Lock-in** | High (Denodo/TIBCO proprietary) | Low-moderate (Debezium is open source) | Low-moderate (standard SQL targets) | None (standard APIs) |

## Important Caveats

- Data virtualization query performance is network-bound — latency between virtualization platform and ERP sources is the dominant factor. Co-locate in the same cloud region for best results
- The "zero data movement" claim of virtualization is misleading for caching-heavy deployments — Denodo's full cache mode effectively replicates data, just within its own storage layer [src2]
- CDC replication is the gold standard for high-volume ERP reads, but cloud SaaS ERPs do not expose transaction logs — you must use vendor-native CDC mechanisms (Salesforce CDC, SAP ODP) which have their own limitations (3-day retention for Salesforce, SLT licensing for SAP) [src5]
- Most real-world ERP integrations use a hybrid approach — virtualizing reference/master data while replicating transactional data. Plan for both from the start [src3]
- Tool pricing changes frequently — Denodo, Fivetran, and Snowflake all revised pricing models in 2025. Verify current pricing before making architecture decisions
- D365 Virtual Entities should be considered a "convenience feature" for Power Platform, not a production-grade data virtualization solution — the limitations (read-only, no joins, no audit) are fundamental [src4]

## Related Units

- [Change Data Capture for ERP Integration](/business/erp-integration/change-data-capture-erp/2026)
- [Batch vs Real-Time vs Event-Driven Integration Patterns](/business/erp-integration/batch-vs-realtime-integration/2026)
- [Dynamics 365 Virtual Entities](/business/erp-integration/dynamics-365-virtual-entities/2026)
- [Master Data Management for ERP Integration](/business/erp-integration/master-data-management-erp/2026)
- [Dynamics 365 Dual Write Capabilities](/business/erp-integration/dynamics-365-dual-write-capabilities/2026)
- [ERP Bulk Import Comparison](/business/erp-integration/erp-bulk-import-comparison/2026)