Enrich Data with Expressions and Lookups
Learning Objectives
After completing this unit, you'll be able to:
- Configure an Expression transformation to calculate, clean, and derive new field values using string and conditional functions.
- Describe and configure a Connected Lookup transformation to securely fetch data from an external reference system.
Going Beyond Pass-Through
Cassie's integration pipelines successfully extract, filter, combine, and organize data streams across AW Computing’s environments. However, real-world analytic requirements frequently demand data configurations that do not exist natively in the raw source files.
Her reporting team has a new request. The target database now needs a single FullName column, concatenated from separate source fields, and a new CustomerTier metric. Additionally, each customer row must be enriched with an official regional mailing address pulled from an independent reference file.
To fulfill these requirements mid-flight without relying on database post-processing, Cloud Data Integration (CDI) provides two foundational data-enrichment components: the Expression and Lookup transformations.
The Expression Transformation
The Expression transformation is a passive, row-level structural block that evaluates custom functions against your data stream to modify existing values or derive entirely new fields. It supports an extensive functional library spanning string manipulation, mathematical equations, date handling, and logical evaluations.
Cassie adds an Expression transformation onto her canvas following her union transformation. To generate her new variables, she utilizes the properties console.
- Cassie selects the Expression transformation on her mapping canvas.
- In the Properties Panel below, she navigates to the Expression tab.
- Cassie selects the + button to create a new output field. She sets the Field Type to Output Field, names it FullName, and configures its data type as String.
- She selects Configure next to her new field to launch the expression builder. Cassie enters the concatenation logic: FirstName || ' '|| Surname
- Cassie selects + again to create a second Output Field named CustomerTier (String). She pastes the following nested conditional statement into the logic block:
IIF(Order_Total > 100000, 'Platinum',
IIF(Order_Total > 50000, 'Gold',
IIF(Order_Total > 10000, 'Silver',
IIF(Order_Total > 1000, 'Bronze',
'Standard'
)
)
)
)
Both new fields flow downstream to the Target object alongside the original source fields.
Expression Functions Reference
The Expression transformation supports a library of functions organized by category. The ones Cassie uses most often are:
Category |
Example Function |
What They Do |
|---|---|---|
String |
LTRIM(), RTRIM(), UPPER(), LOWER() |
Clean and standardize text values |
Concatenation |
|| operator |
Join two string fields together |
Conditional |
IIF(condition, true_value, false_value) |
Evaluate a condition and return one of two values |
Numeric |
TO_INTEGER(), TO_DECIMAL() |
Cast field values to a different data type |
The Lookup Transformation
The Lookup transformation reads a reference dataset—a database table, flat file, or cached object—and returns matching values to the primary data stream based on a lookup condition.
Lookup Modes: Connected
CDI supports two distinct architectural lookup execution paths: Connected and Unconnected. Cassie uses Connected Lookups for all her current pipelines.
- Connected Lookup: The Lookup transformation is connected straight into the data flow lines on the canvas workspace. It receives inputs from an upstream object, queries the reference source, and passes its return fields seamlessly downstream. This is the standard operational mode.
Cassie needs to include the Customer Address details for each customer record. This data is maintained in an independent flat file. She adds a Lookup transformation to her canvas and defines the configuration settings:
- Lookup source: Customer_Addresses.csv
Cassie configures the lookup condition where the Customer IDs are the same in the source tables and the lookup file.
- Lookup condition: CustomerID = Cust_ID
Cassie returns the address fields she needs on the Return Fields tab and previews the output data to ensure it is configured correctly.
- Return fields: Address1, City, State, Zip
At runtime, for each incoming customer row, the Lookup queries the reference file and includes the related address attributes directly into the stream.
Lookup Cache
By default, a Lookup transformation queries the physical reference source once for every single row passing through the pipeline. For large datasets, this creates massive disk indexing bottlenecks and slows performance.
To protect performance, Cassie opens her properties panel and enables the Lookup Cache. When checked, the Secure Agent loads the entire reference dataset into RAM memory once at the start of the job. All subsequent rows evaluate against high-speed local memory instead of physical storage arrays.
This is the recommended setting for most Lookup transformations.
Wrap It Up
Cassie's enriched pipeline now looks like this:
- Source: Reads all incoming customer rows from the flat file.
- Expression: Derives FullName and calculates CustomerTier scales mid-flight.
- Lookup: Injects specific address variables from her reference file.
- Target: Writes the clean, enriched data straight into the SQL Server reporting table.
The destination target table receives fully standardized, enriched records, prepared for immediate analytical consumption without a single layer of database post-processing.
Cassie started this badge with a single flat-file pipeline and a long list of business requirements. Unit by unit, she added the tools to meet them: trimming her dataset with a Filter, ordering it with a Sorter, blending sources with a Joiner and Union, and enriching records with Expressions and Lookups. What began as a simple data move is now a clean, structured, and fully enriched pipeline ready for analytical consumption. As her integration work grows in complexity, the next step is learning how to package that logic so it can be shared and reused across mappings with mapplets.