Support Custom Column Positioning for Derived/Extra Columns in File-Based Targets

itle

Enhancement Request: Configurable Column Placement for Derived and Extra Columns in File-Based Targets

Business Requirement

Currently, HVR appends all derived or extra columns to the end of the target file structure. While this behavior is acceptable in many scenarios, it creates challenges for file-based targets that require a specific column order to satisfy downstream application, integration, reporting, or data interchange requirements.

We would like HVR to provide the ability to control the position of derived or extra columns within the target file layout.

Current Behavior

Existing source columns are written in their original order.
Derived columns and additional mapped columns are appended after all existing columns.
Users have no mechanism to place a derived column at a specific location within the target file structure.

Requested Enhancement

Introduce a configurable column positioning feature that allows derived or extra columns to be placed:

Before an existing column
After an existing column
At a specific column index/position
At the beginning of the record
At the end of the record (current default behavior)

Source File Layout:

EMP_ID, EMP_NAME, DEPARTMENT, SALARY

Derived Column:

LOAD_TIMESTAMP

Current HVR Output:

EMP_ID, EMP_NAME, DEPARTMENT, SALARY, LOAD_TIMESTAMP

Desired Output Options:

LOAD_TIMESTAMP,EMP_ID, EMP_NAME, DEPARTMENT, SALARY

Place before SALARY:

EMP_ID, EMP_NAME, DEPARTMENT, LOAD_TIMESTAMP, SALARY

Place after EMP_ID:

EMP_ID, LOAD_TIMESTAMP, EMP_NAME, DEPARTMENT, SALARY

Place at position 1:

LOAD_TIMESTAMP, EMP_ID, EMP_NAME, DEPARTMENT, SALARY

Benefits

Eliminates post-processing steps required to reorder columns.
Simplifies integration with legacy systems consuming fixed column layouts.
Improves support for flat-file exports and data exchange formats.
Reduces custom transformation logic outside HVR.
Provides greater flexibility and control for file-based targets.
Enhances usability when implementing audit, metadata, and derived columns.

Use Cases

CSV/Json file targets requiring a contractual column order.
Fixed-format file integrations.
Regulatory reporting feeds.
Data warehouse staging files with predefined layouts.
Audit columns (load timestamp, batch ID, source system) that must appear in specific positions.

Priority

Medium to High

This enhancement would significantly improve flexibility for customers using file-based targets and reduce the need for downstream file restructuring processes.

Expected Outcome

Users should be able to explicitly control the position of derived and extra columns within target file layouts rather than having all additional columns automatically appended to the end of the record structure.

Community