Configuring External Objects
Ensure that package installation and initial Snowflake configuration has been performed.
Enable External Objects
On the "Create External Objects" tab, click the "Enable External Objects". This will create a Salesforce Connect External Data Source and check the Snowflake account for available tables and views.
To access a view from Salesforce, it must have a varchar column named
ExternalId (case insensitive, e.g. can also be
EXTERNALID). This reflects a Salesforce requirement, and each value in this column must be unique. The column must have a maximum length of 255.
To access a table, it must either have an "ExternalId" column (as described above) or alternatively have a primary key column defined.
Salesforce External Objects Creation
Expand the "Creating External Objects" section and follow the instructions to perform the initial sync process.
Create a Tab to view your data
The simplest way to immediately view data from your new External Objects is to add a Tab for the object:
Navigate through the rest of the wizard, leaving the defaults or changing as required.
Link to other objects
You can link your External Objects to other existing Objects in a few different ways which are described in detail here.
Indirect (most common)
To use Indirect, you must first have a Salesforce Object (standard or Custom) with a custom field that contains identifiers matching those in your Snowflake table. For example, if you had a Snowflake table named FINANCIAL_TRANSACTIONS:
|TRANSACTION_ID (text, PK)||MERCHANT_ID (number(10,0))||TRANSACTION_DATETIME (datetime)||TRANSACTION_AMOUNT (number(10,2))|
Note: The custom field should also be marked as case sensitive, to ensure its values match with those in Snowflake.
To do this, under Setup, click on the name of the External Object, and Edit the field you wish to link with: Change the field type: to Indirect Lookup Relationship: select the object: and the field: then follow the rest of the wizard, accepting defaults or changing as desired.
Now that the objects are linked in this way, the external objects should automatically appear on the 'Related' tab of the parent object ('Account' in this example), provided there are matching records.
If your Snowflake data contains 18-character Salesforce identifiers (these look like '0010o00002BmgIdAAJ'), you can use a "Lookup" relationship using a similar process to Indirect.
This is similar to Indirect Lookup, except that the External object is the parent in the relationship (i.e. for each record in the Snowflake table, you have zero to many Salesforce objects).
Use in a Report
You can use External Objects in reports. If the external object is not visible under "Other Reports", raise a support ticket with Salesforce, advising that the external object is not visible for report selection.
If you have created lookup relationships and wish to include both parent and child in the report, you must create a new Report Type under Setup which describes the parent-child relationship.
Row Level Filtering
Using the "Row Level Filtering" setting in the Snowflake connection parameters, it is possible to filter out rows based on matching fields from the Salesforce user object, which apply to all queries regardless of any filtering performed within Salesforce.
This setting is not currently available in the Omnata setup app, instead it can be added by:
- Under Salesforce Setup, navigate to Custom Metadata Types
- Next to "Snowflake Connection", click "Manage Records"
- Edit the "Snowflake" connection
- Add Row Level Filtering configuration:
The field can be configured with a number of filters, each seperated by a new line.
Each line is of the form: (snowflake table name).(column name)=$CurrentUser.(field name)
For example, the line:
means that any row from FINANCIAL_TRANSACTIONS where the CUSTOMER_EMAIL column does not match the Salesforce user's email address will not be included in any query results.