Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Location or Environment actions improvement

Answered
Naresh Kumar Grandi User
We request the addition of the following metadata variables in Fivetran HVR to achieve feature parity with Qlik Replicate and support enterprise CDC migration use cases

Requested New HVR Metadata Variables

  • Source Database User

    • Qlik: $AR_H_USER
    • Proposed HVR: {hvr_src_user}
    • Purpose:
      • Data lineage
      • Compliance auditing
      • Application-level troubleshooting
      • User activity tracking
    • Currently unavailable in HVR. We have something like hvr_cap_user but it is getting the details from HVR Location  but not the original transaction user from Database logs 

  • Source Transaction ID

    • Qlik: $AR_H_TRANSACTION_ID
    • Proposed HVR: {hvr_tx_id}
    • Purpose:
      • End-to-end transaction tracing
      • CDC validation
      • Data reconciliation
      • Multi-hop replication troubleshooting
    • Currently unavailable in HVR.  hvr_tx_id  ??
  •  

  • Source Log Position / LSN / SCN / Stream Position

    • Qlik: $AR_H_STREAM_POSITION
    • Proposed HVR: {hvr_stream_position}
    • Purpose:
      • Restartability
      • CDC auditing
      • Exact source log sequencing
      • Downstream reconciliation
    • Existing {hvr_tx_scn} for oracle/{hvr_tx_sequence_number} for Db2 Iseris  are available but not supporting for DB2 zO/S
  • Oracle SCN/SQL Server LSN/DB2 RBA/Postgres WAL Position/MySQL Binlog Position

Business Justification

  • Preserve existing Qlik Replicate functionality during migration
  • Enable audit columns in S3, Kafka, and database targets.

Request

Please expose the following metadata as native HVR expression

1
{hvr_src_user}
2
{hvr_tx_id}
3
{hvr_stream_position}
Show more lines

and make them available for:

  • IntegrateExpression
  • CaptureExpression
  • FileIntegrate
  • Kafka targets
  • S3 targets
  • API/metadata access

This enhancement would significantly simplify enterprise migrations from other replication  tools to  Fivetran HVR and improve CDC auditing, lineage, and operational visibility

Please sign in to leave a comment.

Comments

5 comments

Date Votes
  • Naresh Kumar Grandi User

    Sample data: 

    PX_ACTION  PX_USER ($AR_H_USER) PX_TIMESTAMP DB2_ROW_CRT_TS  PX_UOWID( $AR_H_TRANSACTION_ID) PX_RESTART( $AR_H_STREAM_POSITION)            
    UPDATE BF19DX   03:18.1 03:13.0 0000000000028B1F546647D8  10575;639204481990437180;20240209171642195714|000000028B1F5466B7480001
    UPDATE BF19DX   03:18.1 03:13.0 0000000000028B1F546647D8  10575;639204481990437350;20240209171642195714|000000028B1F5466C67C0001
    UPDATE BF19DX   03:18.1 03:13.0 0000000000028B1F546647D8  10575;639204481990437520;20240209171642195714|000000028B1F5466D5EE0001
    UPDATE BF19DX   03:18.1 03:13.0 0000000000028B1F546647D8  10575;639204481990437680;20240209171642195714|000000028B1F5466E5EB0001
    UPDATE BF19DX   03:18.1 03:13.0 0000000000028B1F546647D8  10575;639204481990437860;20240209171642195714|000000028B1F5466F5070001
  • Edwin Fivetranner

    Hi Naresh,

    Thank you for the detailed request. We reviewed the requirements against the metadata already available in HVR 6. Much of the requested functionality appears to be supported today, although HVR uses different variable names and formats.

    • Source database user: {hvr_cap_user} is intended to refer to the database user who performed the original change—not the user configured at the HVR location. This is supported for Db2 for z/OS and can be used in both CaptureExpression and IntegrateExpression. If you are seeing the HVR connection user instead, we would like to investigate the specific configuration and HVR version.
    • Transaction identification: {hvr_tx_seq} identifies the source transaction sequence. All changes belonging to the same transaction share this value. Combined with {hvr_tx_countdown}, it also uniquely identifies and orders the individual changes within the transaction.
    • Stream position: {hvr_integ_seq} provides a unique, continuously increasing value for a specific source location. It preserves the order of the source changes and is derived from the database logging sequence.

    These values can be added as extra columns using ColumnProperties and IntegrateExpression, including for database, Kafka, S3, and other file-based targets. Please test these variables and if they would work in your use cases.

    Best regards,

    Edwin

  • Naresh Kumar Grandi User
    Qlik header HVR variable Status
    $AR_H_DB_COMMIT_TIMESTAMP {hvr_cap_tstamp} Confirmed
    $AR_H_OPERATION {hvr_op_str} Confirmed
    $AR_H_USER {hvr_cap_user} Disputed — will revalidate
    $AR_H_TRANSACTION_ID {hvr_tx_seq} + {hvr_tx_countdown} need to test either of them 
    $AR_H_STREAM_POSITION {hvr_integ_seq} confirmed
    $AR_H_CHANGE_SEQ {hvr_integ_seq} Collision with above — decide
    $AR_H_TIMESTAMP {hvr_integ_tstamp} Approximate
  • Naresh Kumar Grandi User

    also currently we are using below function in other tool  to map the Source PST/PDT time to local CST/CDT time:

    DB2_ROW_CRT_TS: substr(strftime('%Y-%m-%d %H:%M:%f', $AR_H_TIMESTAMP, '-2 hours'), 0, 22) || substr($AR_H_TIMESTAMP, 22, 25)

    can we try similar function using hvr_cap_tstamp??
  • Edwin Fivetranner

    HVR_CAP_TSTAMP represents the time at which HVR captured the change. For log-based capture, this is normally the source transaction’s commit time. HVR stores this as an absolute timestamp in UTC.

    HVR can convert this timestamp when populating an extra destination column. You can use a ColumnProperties action with an IntegrateExpression containing the %[localtime] timestamp specifier.

    For example:

    {hvr_cap_tstamp %[localtime]%Y-%m-%d %H:%M:%S}

    %[localtime] converts the UTC timestamp using the timezone of the machine on which the Integrate process runs. To make this predictable, you can configure the following Environment action for the target location or target location group:

    Name  = TZ
Value = America/Chicago

    Using America/Chicago is preferable to subtracting a fixed number of hours. It automatically applies CST or CDT depending on the date and therefore handles daylight-saving time correctly.

    For example, the same UTC timestamp would be converted as follows:

    2026-01-15 18:00:00 UTC → 2026-01-15 12:00:00 CST
2026-07-15 18:00:00 UTC → 2026-07-15 13:00:00 CDT

    You can also force UTC output explicitly:

    {hvr_cap_tstamp %[utc]%Y-%m-%d %H:%M:%S}