Creating lookup tables using transformation queries

AD
DM

Administrators and Data Managers can create and update lookup tables by using transformation queries in source and target subscriptions.

Instead of manually creating a lookup table, you can run a subscription to save the query output into a lookup table. You can then use the lookup table to populate data model field values using the LOOKUP function.

Lookup table support

Maximum size: 1 GB or 5 million rows.

If the transformation query creates a lookup table that exceeds the maximum size, the source subscription job will fail.

Lookup tables have a maximum size limit because they are stored in memory to support the NEX lookup functions.

Column name support

When processing incoming source files and creating lookup tables from a transformation query, most column names are supported. For example, column names can begin with a number (example, 2_name) or uppercase characters (example, SE_designation) and periods (.) (example, SE_Specialty__r.SE_External_id__c).

The NEX LOOKUP function, however, will only accept these column names if they are enclosed by simple quotes (").

To ensure that column names are supported for the LOOKUP function, you can simplify the incoming column names in the transformation query by using the "AS" clause.

Example query with simplified column names

SELECT
        "SE_designation__c" AS designation,
        "2_name" AS product_name,
        "SE_Specialty__r.SE_External_id__c" AS external_id
    FROM
        input_file__csv

Example scenario

In this example, we'll use a source subscription to load a product specialty matrix file from Veeva CRM.

Example source file

The transformation query will save the output as a lookup table. We can then use the lookup table to populate data model field values using the LOOKUP NEX function.

Process

Transformation query - Create a transformation query that will save the file output as a lookup table.
Source subscription - Configure the source subscription to load the file and create the lookup table.
Use the lookup table - Use the lookup table in a NEX rule to populate field values

Create a transformation query

To create the transformation query to use in a source subscription:

In the Admin console, click System Interfaces > Transformation Queries.
Click Add Query.
Details section - Type a Name and Description and choose the Inbound query type.
Query Output section - Select Lookup Table.
Type a Table Name. It is automatically appended with the __t suffix.

Table name support:
- Supported characters: lowercase letters (a-z), numbers (0-9) and underscore (_) characters.
- Maximum length: 100 characters.
- The name cannot begin with a number.
Note: Transformation queries replace lookup tables that have the same name. If you want to append or upsert data to an existing lookup table, you can define that logic in the transformation query.
In the Transformation Query section, define the query.

Example query
```
SELECT
        "SE_designation__c" AS designation,
        "SE_Product__r.Name" AS product_name,
        "SE_Specialty__r.SE_External_id__c" AS external_id
    FROM
        input_file__csv
```
Note that this query uses the file alias, input_file__csv, that you can define in the source subscription.

Tip: In this example, the column names from the specialty matrix file from Veeva CRM are renamed to a simplified name so it is easier to reference the column names in the LOOKUP function to populate the field values.

Upsert or append data to existing lookup tables

When query output is saved as a lookup table, the lookup table is replaced each time the subscription runs.

You can include logic in the transformation query to add and/or update data to the data in the existing lookup table.

Upsert - Using a delta file (new records and updates), join the data from the existing lookup table and the source file. The source file can add new data and update existing data in the lookup table.
Append - Using a delta file (only new records), union the data from the existing lookup table and the new data from the source file.

When the subscription runs, the lookup table will be replaced with the output of the query.

Upsert delta data to existing lookup table

You can add new data and update changed data in the existing lookup table by loading a delta file. To do this, create a transformation query that will union the incoming data from the delta file with the existing data in the lookup table.

Use a LEFT JOIN and a WHERE clause to remove any existing records in the lookup table that match on the same ID in the incoming delta file.

When data is upserted to an existing lookup table, the following behavior occurs:

Existing data that is not in the delta file is replaced in the lookup table.
Existing data that is in the delta file is replaced in the lookup table.
New records in the delta file are created in the lookup table.

Append delta data to existing lookup table

To add new data to the existing lookup table, create a transformation query that will union the incoming data from the delta file with the existing data in the lookup table.

Important: Ensure that the delta file includes only new records and no changes to existing records; otherwise, the lookup table will have duplicated records.

When data is upserted to an existing lookup table, the following behavior occurs:

All data in the existing lookup table is replaced in the lookup table.
All data in the delta file is added in the lookup table.

For similar examples using custom tables, see Upserting or appending delta data to a custom table.

Create a source subscription

Configure the source subscription to load the file. Link the transformation query to the subscription so the query output is saved as a lookup table.

To create the source subscription:

In the Admin console, click System Interfaces > Source Subscriptions.
Click Add Subscription > Use Classic Mode.
Job Run Outcome - Accept the default option, Run Job in Test Mode.

Transformation queries are the first thing that run in a source subscription, so even if the job is run in test mode (default) or if the job fails in a later stage, the lookup table is created.
Details section - Configure the following settings:
- Type a Name and Description for the subscription.
- System - Choose the source system.
- Status - The subscription is enabled by default.
Settings section - Accept the other default settings in this section.
Source Files section - Define the following settings:
- Network FTP Path - Define the location of the file.
- File Definitions - Define the information for the file that you will load.
Example

No configuration is required for the Modelling & Normalization, Network Expression Rules, and Match Configuration sections in this scenario.

In the Transformation Queries section, add the query that will read the file and transform the output to a lookup table.

If the transformation query has more than one output type (for example, a lookup table and a custom table), each output type displays in the Query Output column.
Save your changes and click Start Job to run the subscription to save the data to a lookup table.

Lookup table

On the Lookup Tables page (Data Model > Lookup Tables), the new table displays in the list.

The following details display for the lookup table:

name and description
created date
modified date (if the table has been re-uploaded by a subscription)

Each time the subscription runs, the lookup table is replaced. This is the same as re-uploading a lookup table.
source subscription name and job ID that created the table
transformation query name that was used in the subscription

Now, you can report on the lookup table, or you can use it in a NEX rule; for example, to populate data model field values.

For more information and examples, see Lookup tables.

Exporting configurations

If you export the transformation query to a target environment, all output options (including lookup table options) are exported.