Harel Insurance
Harel Insurance streams changes from IBM i (AS400), Oracle and SQL Server into Databricks in real time, on-premise and inside its own security perimeter.
Introduction
Harel Insurance is one of Israel’s largest insurance and financial services groups. Its core policy and pension systems run on IBM i (AS400) alongside Oracle and Microsoft SQL Server, and Databricks is its analytics platform. Harel uses Etlworks to capture changes from all three source platforms and deliver them into Databricks in real time, on-premise, inside a security perimeter that keeps the data path off the internet.
Harel chose Etlworks over Qlik. The platform is delivered and supported locally by Aqurate, the Etlworks partner in Israel, with Etlworks engineering behind them.
The Challenge
Harel’s existing Microsoft Fabric pipelines could bulk-load SQL Server, but they could not capture changes from IBM i or Oracle at all. The new platform had to meet several demanding requirements at once:
- Three source platforms: IBM i, Oracle and SQL Server, including a platform most CDC tools do not read at all.
- History, then a change stream: an initial load of very large tables followed by continuous capture into Databricks Delta tables, the landing zone of a medallion architecture.
- Real-world source data: tables with no primary key and no index, schemas that change over time, and several IBM i tables recorded in the same journal.
- One source, several destinations: the same changes had to reach Databricks and Azure Event Hubs.
- A strict security review: nothing installed on the IBM i, installation through a DMZ, secrets kept in the corporate vault, single sign-on through the corporate identity provider, and the platform running on Harel’s own infrastructure.
- Enterprise operations: resilience to network failures, unavailable targets and load peaks, plus governance, lineage and incident handling that fit how Harel already runs its systems.
Why Etlworks
- CDC from all three sources in one platform: changes are read from the transaction log of IBM i, Oracle and SQL Server, not by polling tables.
- Nothing on the AS400: the connector reads committed changes from the DB2 journal remotely over the IBM i JDBC driver. No agent is installed, and no SQL jobs or stored procedures run inside the database. The CDC engine runs outside and resumes from the last saved offset.
- No Oracle GoldenGate licence: Oracle CDC uses LogMiner’s public API.
- On-premise by design: the full platform runs on the customer’s own servers, with single sign-on, alerts through the customer’s own mail server, and webhook events into the monitoring tools already in place.
- Built-in change control: version history, diffs, rollback and a recycle bin for every flow and connection.
- An engineering team that responds: Etlworks engineers work directly with the customer and the partner, and fixes ship quickly.
The Solution
Etlworks runs on Harel’s own virtualized infrastructure, with separate production and test environments.
- CDC into Databricks: flows stream changes from IBM i, Oracle and SQL Server into Databricks Delta tables. Large tables start with an initial snapshot, then switch to continuous capture.
- Built for messy sources: schema changes are applied automatically, and tables with no primary key or index are captured like any other table.
- Several AS400 tables, one journal: Harel records several IBM i tables in a shared journal. Etlworks engineering added support for capturing them all from that one journal, released as part of the regular product.
- Fan-out: where a source also feeds Azure Event Hubs, the same change stream serves both destinations.
- Secure by default: installed through the DMZ, nothing deployed on the IBM i, and every change to flows and connections tracked with full history.
- Room to grow: the deployment starts on a single node and can add runtime nodes to a cluster without downtime as the workload grows.
- Local support: Aqurate provides first- and second-line support in Israel, with Etlworks engineering behind them.
Results
- Real-time CDC from three platforms: changes from IBM i, Oracle and SQL Server reach Databricks continuously, from one platform.
- Throughput: from thousands to tens of thousands of records per second, depending on the source and the shape of the table, with large tables loaded first and then kept current by CDC.
- Stability under load: pipelines run steadily through load peaks and recover on their own from network failures and unavailable targets, resuming from the last saved position.
- Every requirement met: Harel confirmed that the platform met all of its requirements, and singled out how quickly the Etlworks and Aqurate teams responded.
- Inside the perimeter: the platform runs within Harel’s own security perimeter, with nothing installed on the source systems.
Key Takeaways
- Three sources, one platform: CDC from IBM i, Oracle and SQL Server into Databricks, read from the transaction log.
- Nothing inside the sources: no agent on the AS400 and no jobs or procedures in any source database.
- Fully on-premise: the platform runs inside the customer’s own security perimeter.
- One stream, several destinations: the same changes feed Databricks and Azure Event Hubs.
- Challenges solved together: where Harel’s environment needed something new, Etlworks engineering built it and shipped it.
- Chosen over Qlik: Harel selected Etlworks for CDC across its core systems.
Running CDC from IBM i, Oracle or SQL Server? Start your free trial today or talk to us about your environment.