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

Community

Transformations: Fix the integrate job logic to not to touch history at target table

Sailendra Koritala Koritala User

Hello,

We are maintaining history at target by keeping soft deletes. But when the source table keys matched with target, HVR integrate is re-activating soft deleted record at target instead of creating a new record.

I heard from support team that this is a integrate job limitation in HVR. But need a tailored solution to maintain history at all time.

You can reach out to me at srinivasa.koritala@sherwin.com for more details.

Regards,

Sailendra

Please sign in to leave a comment.

Comments

2 comments

Date Votes
  • Mark Van de Wiel User

    Hi Sailendra,

    Thank you for your request. There are other customers who have requested this. We have a project planned for delivery in the second half of this calendar year to address your concern.

    Please do note that by keeping soft-deleted rows around for which keys have been reused you can no longer rely on the primary key from the source to be a key on the target. That affects how you must structure any joins.

    Also, there is of course the possibility of repeated reuse of the key, in which case you would end up with multiple soft-deleted rows i.e. the combination of <primary key from source> + <soft delete column> should also not be a key.

    FYI the internal reference to our project is T-644448.

    Thanks,
    Mark.

  • Sailendra Koritala Koritala User

    Thank you Mark for the update.

    That's correct. Our primary keys will be reused at source.

    In our case  HVR_IS_DELETED=0 means live data at target, HVR_IS_DELETED=1 means soft deleted at target.

    So, we created different views on top of target tables. 1 view for only live data, other view only history data ( i.e. soft deleted rows )

    Based on the business use case we pick corresponding view from above.

    Live data view: <primary key from source> + HVR_IS_DELETED=0

    History data view: <primary key from source> + HVR_IS_DELETED=1

    also, Since we use Snowflake as target, it does not impose any primary keys by default.