Oracle E Business Suite Incremental Extract

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 | Microsoft Learn

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