Salesforce (REST API) Data Source Template

Overview

The Salesforce (REST API) data source template connects Validatar to a Salesforce org through the Salesforce REST API. It requires no driver installation and no third-party license, and runs on any runtime that can reach *.my.salesforce.com over HTTPS — including Validatar Cloud.

Platform: Salesforce (all editions with API access)
Connection Category: Script
Connection Type: Python Script
Query language: SOQL

The template catalogs objects and fields from the Salesforce describe API and enriches the Validatar catalog with Salesforce-specific metadata: formula definitions, picklist values, external IDs, encryption flags, and lookup targets. Its macros target the data quality failures characteristic of CRM data — incomplete fields, duplicate accounts and contacts, references to deleted records, and picklist values that persist in data after being deactivated in configuration.

SQL access to the same org, including cross-object joins, is provided by the Salesforce (ODBC) Data Source Template. Salesforce: REST API or ODBC compares the two.

Requirements

Note: Ingestion columns that do not match an existing custom field reference key are discarded silently. Ingesting before the catalog fields are applied results in missing enrichment with no accompanying error.

Authentication Requirements

Three Salesforce platform changes govern how this template authenticates:

• Connected Apps can no longer be created as of the Spring '26 release. External Client Apps are the replacement. Existing Connected Apps continue to function.
• The OAuth username-password flow is blocked by default on orgs created in Summer '23 or later.
• The OAuth username-password flow is retired entirely in Winter '27.

The template therefore supports only the client credentials and JWT bearer flows, both configured on an External Client App. No username-password fallback is offered.

Connection Parameters

Authentication modes

Client credentials requires a consumer key, a consumer secret, and a designated run-as user on the External Client App. It carries no additional runtime dependency.

JWT bearer replaces the shared secret with an RSA private key that signs a short-lived assertion, and is generally preferred where a long-lived secret is not acceptable. It requires the cryptography package on the runtime, a certificate uploaded to the External Client App, and pre-authorization of the run-as user.

Both modes are verified and fully supported. Neither offers greater capability or throughput than the other.

Setting Up Salesforce Access

Configuration requires System Administrator access in Salesforce and takes approximately 20 minutes.

Note: Most third-party integration guides describe creating a Connected App. Salesforce no longer permits new Connected Apps as of Spring '26; an External Client App is required.

1. Identify the My Domain URL

The My Domain URL appears in the browser address bar after login, in the form https://acme.my.salesforce.com. This value is the Instance URL parameter. login.salesforce.com is not a valid value.

Salesforce reference: My Domain

2. Create the External Client App

Navigate to Setup → App Manager → New External Client App and configure:

• Name: any meaningful identifier, such as Validatar Data Quality
• Distribution State: Local
• API (Enable OAuth Settings): enabled
• Callback URL: http://localhost:1717/OauthRedirect — required by the form, though neither supported flow performs a redirect
• OAuth Scopes: Manage user data via APIs (api) and Perform requests at any time (refresh_token, offline_access)

Salesforce requires 2–10 minutes to activate a newly created app. Authentication attempts made before activation completes fail with invalid_client_id.

Salesforce reference: Create an External Client App

3a. Configure the client credentials flow

In the External Client App, under Policies → Edit → OAuth Policies:

• Enable Client Credentials Flow
• Set Run As to the integration user the queries execute as. That user's permissions determine catalog and query visibility.

The consumer key and secret are then available under Settings → OAuth Settings → Consumer Key and Secret → Reveal.

Salesforce references: OAuth 2.0 Client Credentials Flow · Configure an External Client App for the Client Credentials Flow

3b. Configure the JWT bearer flow

Generate a certificate and private key:

openssl req -x509 -sha256 -nodes -days 3650 -newkey rsa:2048 \
  -keyout salesforce.key -out salesforce.crt -subj "/CN=validatar-integration"
openssl pkcs8 -topk8 -nocrypt -in salesforce.key -out salesforce.pem

Then, in the External Client App:

1. Under Settings → OAuth Settings → Edit, enable Use Digital Signatures and upload salesforce.crt. The certificate must be under 4 KB.
2. Under Policies → Edit → OAuth Policies, set Permitted Users to Admin approved users are pre-authorized, then use Manage Profiles to add the profile of the run-as user.

Omitting step 2 produces the error user hasn't approved this consumer, which is the most common failure in this configuration.

The connection then requires the consumer key, the username, and the contents of salesforce.pem.

Salesforce reference: OAuth 2.0 JWT Bearer Flow

4. Confirm the credentials

curl -s -X POST "https://acme.my.salesforce.com/services/oauth2/token" \
  -d "grant_type=client_credentials" -d "client_id=CONSUMER_KEY" -d "client_secret=CONSUMER_SECRET"

A response containing access_token confirms the configuration is complete.

Catalog Structure

Salesforce has no schema concept, so the template derives three catalog schemas:

Objects in a namespace schema are owned by an installed package and are subject to change on package upgrade.

What's Included

Catalog enrichment

Object level: label, API name, custom flag, namespace prefix, key prefix, sharing model, queryable, createable, updateable, and record type count.

Field level: label, API name, custom flag, formula flag, formula expression, required, unique, external ID, encrypted, auto-number, picklist values, and lookup targets.

Standard field properties — help text, length, precision, scale, nullability, auto-number, and primary key — populate Validatar's built-in column properties rather than custom fields.

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.

Salesforce-specific:

Orphaned references

Salesforce enforces referential integrity on lookup and master-detail fields: deleting a parent record clears the child's value, cascades the delete, or is blocked. Genuine lookup fields therefore rarely dangle.

The orphaned references that occur in practice reside in text fields holding record IDs from another object or another system, where no constraint applies. The orphaned_lookups macro supports both cases: the Parent Object parameter is optional for a true lookup field, where the target is read from the field describe, and required for a text field.

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.

Table level: record count, column count.

All column types: null count, null percent, distinct count, distinct percent, sample size.

Numeric columns: minimum, maximum, mean, standard deviation, zero count, zero percent, negative count, negative percent.

Text columns: minimum, maximum, minimum length, maximum length, mean length, blank count, blank percent.

Date columns: minimum date, maximum date.

Exact and sampled values

Null counts, distinct counts, minimums, maximums, and means are exact, computed by SOQL aggregates batched across multiple fields per query. Zero and negative counts are also exact, each issued as a separate query, as SOQL provides no conditional aggregate.

Values SOQL cannot compute server-side are derived from a single sampled page, 2,000 records by default: string lengths, standard deviation, and any statistic on a field type SOQL does not aggregate. Every column carries a sample_size profile recording the basis.

SOQL does not aggregate textarea, multipicklist, boolean, base64, address, or location fields. Statistics for those field types are derived from the sample.

Blank count is always zero. Salesforce normalizes empty text to null, so no blank value is distinct from a null. The profile is emitted as an explicit zero rather than omitted.

API Consumption

Salesforce meters API calls. Developer Edition allows 15,000 per 24-hour period; paid editions allow more but remain finite. The allowance is shared across all integrations in the org.

The include_objects allowlist is recommended on orgs where the allowance is constrained. An unfiltered Developer Edition org exposes more than 1,600 objects. The built-in exclusion list removes platform objects (*Share, *History, *Feed, *ChangeEvent, *__e, *__mdt); an explicit allowlist is more precise.

The api_usage macro reports the remaining allowance and can be scheduled as a test.

Installation

1. Apply the catalog custom fields: Settings → Catalog Custom Fields → Import, using salesforce-crm-catalog-custom-fields.xml. This must precede the first ingestion.
2. Import the template: Settings → Data Source Templates → Import.
3. Create a data source from the template and supply the connection parameters.
4. Run technical metadata ingestion.

Known Limitations

• SOQL has no SELECT *. Every query enumerates its fields, which requires a describe call first. The object_data macro is bounded by a row limit for this reason.
• Compound fields cannot appear in a field list. Address and Geolocation fields are excluded automatically. Their components, such as BillingCity and Location__Latitude__s, are queryable normally.
• SOQL supports parent-child relationship queries only. Arbitrary joins are not available. The orphaned_lookups macro resolves its comparison in a second pass; work requiring genuine joins is better served by the ODBC template.
• Query limits are 2,000 records per page and 50,000 per query. The template pages transparently. Larger extracts require Bulk API 2.0.
• Encrypted fields cannot be filtered, grouped, or profiled meaningfully.
• Field-level security applies. The catalog reflects the visibility of the integration user. A field hidden from that user is absent from the describe response entirely and is indistinguishable from a field that does not exist.

Verification

Verified against a live Salesforce Developer Edition org: 26 of 26 elements passed — connection, pre-execution script, metadata ingestion, all 22 macros, and the profile set — on both the client credentials and JWT authentication modes.

The verification fixture contains 1,050 records across six objects with seven counted defects: 15 orphaned references, 5 duplicate email groups, 25 missing category values, 12 negative amounts, 9 zero quantities, 8 out-of-range close dates, and 3 case-variant account names. Every macro assertion is an exact row count against those values.