SAP CDC transformation doesn't have ODQ columns as output

Question

Hello, we are trying to implement SAP CDC -> ADLS parquet mapping dataflow. But output from the SAP CDC transformation drops the ODQ metadata columns like ODQ_CHANGEMODE. They are not available in the sink file. I can see those columns only in staging files. We are using auto-mapping in our flow. Is there a way to retrieve those ODQ columns and push them into the sink parquet file?

Answer 1

@Dmytro Honcharuk Thanks for the additional information.

I wish I had SAP environment to test this scenario and share with some screenshots but as per my discussion with product team, in the source transformation, ADF process those SAP ODQ fields internally and mark the rows as insert, update, or delete. For multiple update rows, ADF sort/order them and pick the last after image. Essentially, it performs de-duplication of multiple updates in SAP.

The recommendation from product team is to create a new column named, say "row operation" using Derived column (use isUpdate, isInsert , isDelete functions) and set the field value to insert, update, or delete based on mapping dataflow row marker. This allows for sink to have regular data columns + row operation (set to I / U / D).

MS Doc for reference: Expression functions in mapping data flow

You could use the added column in Snowflake to perform final merge.

Hope this info helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

We have our DWH in Snowflake and want to read incremental deltas from SAP to data lake and after that merge data in Snowflake into target tables with SCD 2. Hence, we need to have ODQ metadata columns to be able to perform proper merge operation on Snowflake side - to identify change mode for the row. The problem that we cannot skip intermediate file storage operation due to the internal architecture concerns.

Answer 3

This is by design, today ADF has four SAP system columns:

ODQ_CHANGEMODE: SAP generated column and replaced by dataflow language “RowMarker” after dedupe operation.

ODQ_ENTITYCNTR: SAP generated column

__PACKAGEID, SEQUENCENUMBER: ADF generated columns to ensure the order of delta change we extract

After SAP source in dataflow, if customer still wants to know the operation type (CRUD) of each row, he can add derive transformation to get it with dataflow expression (RowMarker is internal column, invisible to customer). For example: