Overview
The client needed to extract data from an on-premises Oracle database and move it into Azure Data Lake Gen2 for centralized reporting and analytics. The Oracle environment was inside the client's private network, so the integration required secure hybrid connectivity.
Challenge
- ADF could not reach the on-prem Oracle database directly from the cloud.
- Network security model required outbound-only connectivity.
- Needed reliable pattern for full initial loads and ongoing incremental extraction.
Solution
MSPowerhouse implemented the integration using Azure Data Factory and a Self-hosted Integration Runtime installed on a server with network access to the Oracle database. A read-only database account was used to ensure least-privilege access.
The data pipeline connected to Oracle through the self-hosted runtime and copied data into Azure Data Lake Gen2. For larger tables, the architecture supported partitioning, parallel copy, and watermark-based incremental logic using date fields such as last updated date where available.
Technical Execution
- Azure Data Factory Oracle connector configuration.
- Self-hosted Integration Runtime for private network access.
- TCP connectivity to the Oracle listener.
- Read-only Oracle service account.
- Copy Activity into ADLS Gen2.
- Table-level extraction.
- Optional query-based extraction through views or SQL.
- Watermark-based incremental load design.
- Partitioning and parallel copy planning for large datasets.
- Raw and staged data lake folder structure.
- Validation of row counts and sample records.
Outcome
The client gained a secure and repeatable method to move Oracle data from an on-premises environment into Azure Data Lake Gen2.
Impact
This project helped unlock data from a legacy/private database environment and move it into a modern Azure data platform without compromising network security.
