Oracle E Business Suite Incremental Extract

[AJAJ]

Hi there,

Just need your thoughts on design approach. I would rate myself 7/10 on ADF. 

My source is Oracle EBS. It has 100's of tables and large amount in billions of transactional rows and every day possibly 100k-500k new rows.

I currently have a pipeline using watermark table with tablename, maxtime stamp, maxkey values of staging tables.

I use dynamic copy activity referring the watermark tables (dynamically select * from <table1> where modifieddate > maxdate from staging for that particular table)

Is there any design ADF approach for hands off way to auto detect source changes, synch and trickle the changes to destination staging tables or ADLS files? possibly every hour or may be once a day?

am i missing any other best strategy that you might be using. I see 3rd party tools like fivetran doing the synch automatically and hands off. Is there anything similar that could be designed in ADF.

Thanks

[AJAJ]

Thanks. My current set up already has section 1 implemented.

If my understanding is correct, there are no other design approached or options in ADF for source replication other than my existing implementation. I will wait to see if there are any other suggestions and then have this closed. Just want to make sure im not missing anything in terms of ADF features especially when source CDC is not an option.

[Anonymous]

Ok @AJAJ .!

[Anonymous]

Hi @AJAJ ,

Few helpful helps -
Pattern to incrementally amass data with Dataflow Gen2 - Microsoft Fabric | Microsoft Learn
Delta Lake Change Data Feed in Fabric Lakehouses (serverlesssql.com)

I just felt like sharing these links with you, I hope it might give you some insights.

[Anonymous]

Hello @AJAJ ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

[Anonymous]

Hi @AJAJ ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

[AJAJ]

Im aware of source CDC but if thats not allowed or available. how to go about new tables, new columns added.. all should make it to SQL destination.

[Anonymous]

Hi @AJAJ ,

Thanks for using Fabric Community.
Here are my thoughts on your design approach -

Current Approach:

• Watermark table with timestamps and keys is a good starting point for tracking changes.
• Dynamic copy activity based on watermark tables provides flexibility.
  
  

Improvements and Alternatives:

1. Incremental Load with Last Modified Date:
   • Instead of full table scans, use WHERE modifieddate > <max_timestamp> clause in your dynamic copy activity. This retrieves only new/modified rows since the last run.
   • Update the watermark table with the new max_timestamp after a successful run.
2. Partitioning:
   • If Oracle EBS supports table partitioning, consider partitioning your tables based on a date/time column.
   • You can then target specific partitions for loading new data, improving performance.
3. Polling vs. Scheduling:
   • Currently, your pipeline might be triggered manually. Consider using ADF triggers to schedule it hourly or daily.
4. ADF Version Control:
   • Utilize ADF version control to track changes made to your pipeline and facilitate rollbacks if needed.

Alternative Solutions without CDC:

1. Change Data Capture (GoldenGate):
   • While you mentioned CDC limitations, consider exploring Oracle GoldenGate if feasible. It captures changes at the database level for near real-time data integration.
2. Third-Party Tools:
   • Tools like Fivetran offer automated data integration with various sources, including Oracle EBS. They often leverage change data capture mechanisms for efficient data movement.
3. Custom Scripting:
   • As a last resort, explore writing custom scripts (e.g., Python with cx_Oracle library) to query Oracle EBS for changes and load them into ADF. This requires more development effort compared to ADF features.

Additional Considerations:

1. Monitoring and Logging:
   • Set up monitoring and logging for your pipeline to track its execution status and identify any issues.
2. New Tables and Columns:
   • Manually update your pipeline logic or metadata to handle new tables and columns. Consider using version control to track these changes.

Conclusion:

While CDC offers the most efficient way to capture changes, the approaches mentioned above provide viable alternatives within ADF for replicating data from Oracle EBS. Remember to prioritize a balance between automation, performance, and maintainability when designing your solution.

Note: Following a particular design is completely depends on your individual use case.

Docs to Refer -
Master Change Data Capture (CDC) with Azure Data Factory - IWConnect
Incrementally copy data by using change tracking in the Azure portal - Azure Data Factory | Microsof...

Hope this is helpful. Please let me know incase of further queries.