0

I would like to set up a pipeline to sync data from Data Warehouse to no sql CosmosDB. Copy tool works fine for one to one table relations but for one to many obviously, I will have duplicates of objects in my NoSQL DB. What is the best way to solve this issue and have an array of one to many items instead of duplicating rows? Thanks in advance

Viacheslav Shelestovskiy
  • 9
  • 2
  • Could you elaborate more about how do you want to do the data map? Could you give some data sample? – Fang Liu Oct 25 '18 at 07:25
  • For example we have 3 tables: **reports**: subject, model_id; **model**: model_id, model_desc; **attachments**: report_id, file_path; So in this case we have report-model one-to-one and report-attachments one-to-many relations. And after syncing data i would like to have a documentary object like: `report: { report_id: 1, model_dec: 'Description', attachments: [{file_path: 'path/img1.png'}, {file_path: 'path/img2.png'}] }` – Viacheslav Shelestovskiy Oct 25 '18 at 07:48

1 Answers1

1

In your case, I don’t think copy activity can achieve that. Copy activity just copy data from one table to another by appending new documents or do upsert based on cosmos dB ID. Maybe you could write your own code to do the merging and then use ADF custom activity to invoke your code. https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-dotnet-custom-activity

Fang Liu
  • 2,325
  • 2
  • 13
  • 18