Salesforce (ODBC) Data Source Template

Overview

The Salesforce (ODBC) data source template connects Validatar to a Salesforce org through a CData ODBC driver, which presents Salesforce as a SQL-92 database and translates queries to SOQL.

Platform: Salesforce (all editions with API access)
Connection Category: Database
Connection Type: ODBC
Driver: CData ODBC Driver for Salesforce, 64-bit
Query language: SQL-92

This provides three capabilities SOQL does not offer: genuine joins across objects, unrestricted aggregates, and transparent paging. A referential integrity check that requires three round trips through the REST API is a single statement:

select c.Id, c.External_Account_Id__c
from Validatar_Demo__c c
left join Account p on p.Id = c.External_Account_Id__c
where c.External_Account_Id__c is not null and p.Id is null

The trade-off is a driver installation, a license, and a Data Agent to host them. The Salesforce (REST API) Data Source Template requires none of those. Salesforce: REST API or ODBC compares the two.

Requirements

Note: This template requires a Data Agent. A third-party ODBC driver is a native binary installed on a host, and the Validatar Cloud runtime does not host customer binaries. Salesforce connectivity without an agent is provided by the REST API template.

Setting Up Salesforce Access

The Salesforce configuration is identical to the REST API template's, except that this template uses the JWT bearer flow exclusively. Steps 1, 2, and 3b of Setting Up Salesforce Access in the Salesforce (REST API) Data Source Template article apply.

That configuration produces three values used by this template: a consumer key, a pre-authorized username, and a salesforce.pem private key.

Obtaining the Driver

The 64-bit Windows driver is available as a 30-day trial with no payment details required, and is free for development use.

• Download the CData ODBC Driver for Salesforce
• Licensing and pricing
• CData driver documentation

License tier depends on deployment. In this configuration the driver is queried by a Windows service running as LocalSystem on the Data Agent host, which is the detail that determines whether a Desktop or Server license applies. Establishing this with CData before purchase avoids a reclassification at renewal.

Installing the Driver

The driver must be installed as administrator on the Data Agent host so that it registers machine-wide.

LocalSystem considerations

The Validatar Data Agent runs as the LocalSystem service account. Three consequences follow, each of which produces failures that present as authentication errors:

1. A DSN-less connection string or a System DSN is required. A User DSN created under an interactive login is not visible to the service. The connection string below is DSN-less and avoids the question entirely.
2. The certificate must be readable by LocalSystem, not stored in a user profile.
3. The OAuth token cache must be writable by LocalSystem. The CData default location is per-user, and the driver cannot persist its token there when running as a service.

New-Item -ItemType Directory -Force C:\ProgramData\CData
Copy-Item .\salesforce.pem C:\ProgramData\CData\salesforce.pem

icacls C:\ProgramData\CData\salesforce.pem /grant "NT AUTHORITY\SYSTEM:(R)"
icacls C:\ProgramData\CData /grant "NT AUTHORITY\SYSTEM:(OI)(CI)(M)"

Connection string

DRIVER={CData ODBC Driver for Salesforce};
AuthScheme=OAuthJWT;
InitiateOAuth=GETANDREFRESH;
OAuthJWTIssuer={consumer key};
OAuthJWTSubject={pre-authorized username};
OAuthJWTCertType=PEMKEY_FILE;
OAuthJWTCert=C:\ProgramData\CData\salesforce.pem;
OAuthSettingsLocation=C:\ProgramData\CData\salesforce-oauth.txt;
UseSandbox=False

Note: CData documentation describes creating a Connected App. Salesforce no longer permits new Connected Apps as of the Spring '26 release. An External Client App is required, and the driver has been verified to authenticate against one.

Catalog Structure

The driver reports a single schema, Salesforce, which the template uses unmodified.

This differs from the REST API template, which derives standard, custom, and per-namespace schemas. The REST template is free to define its own schema names; here the schema must be one the driver recognizes, or schema-qualified references do not resolve. The standard-versus-custom distinction is preserved as the Custom Object catalog field.

What's Included

Catalog enrichment

The driver exposes Salesforce-aware metadata beyond a generic ODBC catalog:

Column data types are read from SalesforceType, which reports Salesforce's own type names — id, picklist, reference, currency — rather than the driver's ANSI approximation. This is the same vocabulary the REST describe API returns, so a given column resolves to the same Validatar data type through either template.

Ingestion is filtered to queryable objects and excludes platform objects (*Share, *History, *Feed, *ChangeEvent, *Tag, *Permission*, Apex*, Auth*, Flow*, Setup*). On a reference Developer Edition org this reduces 1,333 objects to 799, and 23,412 columns to 14,845.

Macros

Standard set: object_data, row_count, row_count_filtered, list_of_objects, list_of_objects_in_a_schema, field_metadata_for_an_object, distinct_values_in_a_column, row_count_grouped_by_column, standard_profile_of_a_column, key_value_list.

SQL-specific:

Data Profiles

Profiles are typed. Minimum and maximum are three distinct profiles by result type, so that numeric values sort numerically and dates render as dates.

Enabled by default: record count; null count and null percent; numeric minimum, maximum, mean, standard deviation, zero count, zero percent, negative count, and negative percent; string minimum, maximum, and lengths, with blank count and blank percent; date minimum and maximum.

Percent profiles are calculations derived from their prerequisite counts and the record count, rather than separate queries.

Distinct count ships disabled. The driver computes it client-side by retrieving rows, which on a large object can transfer the entire table across the REST API and consume the org's daily API allowance in a single profiling pass. It can be enabled for objects of known size.

Blank count is always zero. Salesforce normalizes empty text to null, so no blank value is distinct from a null.

Note: The template uses len() for string length rather than length(). On the driver build tested, length() does not return an error — it terminates the host process with a stack overflow, which on a Data Agent host stops the agent service.

Known Limitations

• Geolocation fields are not exposed. Measured on the reference org, the REST describe API reports 29 fields on a test object and the driver reports 28; the difference is a Geolocation compound field, which the driver does not expose at all, including its latitude and longitude components. Geolocation data requires the REST API template.
• Record types. The record_count_by_recordtype macro joins on o.RecordTypeId, a column that exists only on objects where record types are configured. On other objects the statement fails to parse with Invalid column name 'RecordTypeId'. The REST API template inspects the describe API first and returns an empty result instead; SQL provides no equivalent guard.
• API consumption is determined by the driver. The driver decides what to push down to SOQL and what to compute client-side by retrieving rows. COUNT(DISTINCT …) and unfiltered joins can be expensive.
• Ingestion duration. An unfiltered column scan takes approximately four minutes on a Developer Edition org; the filtered scan is proportionally faster.
• Data Agent only, as described under Requirements.
• Field-level security applies. The catalog reflects the visibility of the integration user.

Verification

Verified against a live Salesforce Developer Edition org with the CData driver on a Windows Data Agent: 20 elements passed, 1 skipped, 0 failed.

The skipped element is record_count_by_recordtype. No object in the verification org has record types configured, so no case exists to assert against, and the SQL form provides no guard for that condition.

Where both Salesforce templates ship the same macro against the same fixture, both return identical results — 200 records, 5 category groups, 4 distinct categories, 5 duplicate email groups, 15 orphaned references, and 200 recently modified records. Agreement across two independent access paths is the primary evidence of correctness for both.