Best approach to update a target field referencing a source field

Hi @aepuser16 ,

Include crmId (type: string) as an identity field.

Add customer_status (type: string) as a profile attribute.

Add a date-typed field status_updated_date if you want to record when the status was set.

Use crmId as your primary identity namespace in both source and target schemas. This ensures incoming records automatically merge to the correct profile based on CRMID.

Answer to your question: Yes - map CRMID as the primary identifier in both schemas. It gives you one consistent key for merges.

Instead of modifying the source CSVs themselves, leverage AEP’s Data Prep within your batch ingestion Flow Service:

Set up your batch ingestion

map your incoming CRMID column to the XDM crmId field.

create a new column customer_status that’s a constant formula: IF(crmId IS NOT NULL, "Moved", null)

set status_updated_date = NOW() so you know when the update occurred.

Reference: https://experienceleague.adobe.com/en/docs/experience-platform/data-prep/home

In your ingestion mapping, bind the Data Prep outputs (crmId, customer_status, status_updated_date) to the corresponding schema fields.

Publish the Flow. Every time your API ingests new batches, these transformations run automatically - updating the profile store with customer_status = "Moved"

AEP lets you apply row-level TTL only on event datasets, not on individual profile attributes. To achieve a 90-day roll-off of customer_status, you can do below:

Capture status_updated_date in ingestion

Create a scheduled Data Flow (via the Batch or Streaming API) that runs daily and queries all profiles where customer_status = "Moved" AND status_updated_date <= (currentDate – 90 days)

Updates customer_status back to "Active" (or null) for those records.

CRMID is present in both the source and target schemas. Should this be the primary identifier in both schemas?

Yes. Use CRMID as the primary identity namespace in your XDM schema and in the ingestion mapping so that records merge correctly in RT CDP.

Do we need to add customer_status field in the source? Is it necessary?

No. Use a Data Prep recipe to derive and inject that field at ingestion time - you can set it to "Moved" whenever a CRMID is present.

If the customer_status field currently has the value "Active" in the target, when updated to “Moved “ based on the source input, the "Moved" value should be retained only for 90 days.

Best practice is to capture a status_updated_date on ingestion and run a scheduled Data Flow (or Query + Prep recipe) that resets the field after 90 days. TTL at dataset level is less precise.

Note: This response is inspired from Generative AI.

Thanks

Ankit

@aepuser16 Just checking in — were you able to resolve your issue?
We’d love to hear how things worked out. If the suggestions above helped, marking a response as correct can guide others with similar questions. And if you found another solution, feel free to share it — your insights could really benefit the community. Thanks again for being part of the conversation!