Attribute Consolidation

Configure how attributes extract values from source data when schema transformations have consolidated paths during discovery. Attribute consolidation bridges the gap between consolidated discovery pa

Overview

When Schema Transformations consolidate attribute paths during scanning (e.g., customerInfo → customer), the discovered path changes but source data retains the original path. Attribute Consolidation provides SQL expressions to extract values from the original paths during Dynamic Table code generation.

Supported: Only for Discovered and Schema attribute types. Virtual attributes do not support consolidation.

Required: Both SCHEMA_INSERT_REGULAR_EXPRESSION_SEARCH and SCHEMA_INSERT_SQL_EXPRESSION must be non-empty for consolidation to be applied.

---

Configuration Fields

Schema Consolidation Search

Field: SCHEMA_INSERT_REGULAR_EXPRESSION_SEARCH

Behavior: Uses Python find() (string search, not regex) to locate the first occurrence of the search value in the attribute path. Despite the field name, this is a literal string match, not a regular expression.

Requirements:

• Must be a literal string that appears in the consolidated attribute path

• Matches the portion that was replaced during schema consolidation

• Case-sensitive

Example: If schema transformation replaced customerInfo with customer, use Info to find where consolidation occurred.

Schema Consolidation SQL Expression

Field: SCHEMA_INSERT_SQL_EXPRESSION

Behavior: SQL expression inserted into GET_PATH with CONCAT at the location where the search value was found. Evaluates to a string representing the original path segment.

Requirements:

• Valid SQL expression that evaluates to a string

• Typically a literal string (e.g., 'customerInfo')

• Can use dynamic SQL expressions for conditional paths

Example: 'customerInfo' provides the original path segment that was consolidated.

---

How It Works

Code Generation Process

1. Search: Locates first occurrence of SCHEMA_INSERT_REGULAR_EXPRESSION_SEARCH in the attribute path using string search

2. Insert: Constructs GET_PATH with CONCAT, inserting SCHEMA_INSERT_SQL_EXPRESSION at the match location

3. Generate: Produces SQL that extracts from the original source path

Generated SQL Pattern

Without Consolidation:

json_data:"data":"customer"::VARCHAR

With Consolidation:

GET_PATH(json_data, CONCAT('"data"."', 'customerInfo', '."'))::VARCHAR

The SQL expression is inserted where the search value was found, reconstructing the original path for extraction.

Note: Works with both standard JSON paths and embedded JSON paths (paths containing embedded_json).

---

Relationship to Schema Transformations

Attribute Consolidation works with Schema Transformations configured at the data source level:

Schema Transformation (Data Source Level):

• Type: consolidate schema

• Search Expression: Regex pattern to find paths (e.g., customerInfo)

• Replace Expression: Replacement pattern (e.g., customer)

• Result: Paths like data.customerInfo become data.customer during discovery

Attribute Consolidation (Attribute Level):

• Search: String to locate in consolidated path (e.g., Info)

• SQL Expression: Original path segment (e.g., 'customerInfo')

• Result: Code generation extracts from data.customerInfo in source data

Coordination: The search value should match the portion that was replaced. The SQL expression should provide the original path segment.

Example Alignment:

• Schema Transformation: Search customerInfo, Replace customer → consolidates data.customerInfo → data.customer

• Attribute Consolidation: Search Info, SQL Expression 'customerInfo' → extracts from original data.customerInfo

---

Interaction with Other Settings

Order of Application:

1. Attribute Consolidation extracts value from original path

2. Transformation Expression (if configured) transforms the extracted value

3. Null Value Expression handles nulls (if transformation type is not "SQL Expression")

Notes:

• Null Value Expression and Transformation Expression reference the extracted value using {attribute_name}

• Foreign keys reference consolidated attribute paths, not original paths

• DateTime format conversion applies after extraction

• INCLUDE_IN_CODE_GEN must be enabled for consolidation to be applied

---

Examples

Example 1: Simple String Replacement

Schema Transformation:

• Search: customerInfo, Replace: customer

• Consolidates: data.customerInfo.email → data.customer.email

Attribute Consolidation:

• Search: Info

• SQL Expression: 'customerInfo'

Generated SQL:

GET_PATH(json_data, CONCAT('"data"."', 'customerInfo', '."email"'))::VARCHAR AS customer_email

Example 2: Dynamic Path Construction

Schema Transformation:

• Removes Info suffix from multiple attributes

Attribute Consolidation:

• Search: Info

• SQL Expression: CONCAT(SPLIT_PART('{attribute_name}', '.', 2), 'Info')

Result: Dynamically reconstructs original path based on consolidated path.

Example 3: Conditional Path Selection

Attribute Consolidation:

• Search: Info

• SQL Expression: COALESCE('customerInfo', 'customer')

Result: Tries original path first, falls back to consolidated path if not found.

---

Troubleshooting

Consolidation not applied:

• Verify both fields are non-empty

• Check search value exists in the consolidated path (case-sensitive)

• Ensure attribute type is Discovered or Schema (not Virtual)

NULL values in materialized tables:

• Verify original path exists in source data

• Test SQL expression independently

• Review schema transformation configuration

SQL syntax errors:

• Verify SQL expression syntax is valid

• Check proper quoting/escaping in SQL expression

• Review generated SQL in code output

---

Summary

Attribute Consolidation bridges the gap between consolidated discovery paths and original source data paths. Configure Schema Transformations first, then set attribute consolidation to match. The search uses string matching (not regex) to locate where consolidation occurred, and the SQL expression provides the original path segment for extraction.

For more information: Schema Transformations | Attributes: Metadata