What it is
Sluice is a system that extracts patient and clinical records from an electronic health record database each night and loads them into a data warehouse, so that reports and dashboards reflect current information every morning. It replaces an older process built in SSIS, a general-purpose Microsoft tool that was never designed for the particular demands of this job.
Why it mattered
The electronic health record system runs on a database technology called InterSystems IRIS. At some point, that database was migrated to run in the cloud rather than on premises, which meant every query now had to travel across a VPN connection to reach it. IRIS also has a specific limitation: it processes a single query efficiently, but it does not scale well under concurrent query load, unlike a modern SQL Server instance, which is built to handle many simultaneous connections without significant degradation.
The old job extracted each table through a single sequential connection, one full table scan after another, all across that same VPN link. As a result, total runtime was bound by the sum of every table’s individual extraction time, with no practical way to reduce it short of increasing available bandwidth. The process was also fragile in a way that made failures expensive. When a job failed partway through, the standard remedy was a full restart from the beginning rather than a resume from the point of failure, and diagnosing the actual root cause on a bad night often consumed more time than a full rerun would have.
The approach behind the fix
Certain database systems, IRIS included, throughput far better under concurrent, partitioned reads than under a single sequential scan. Sluice takes advantage of this by partitioning each table, typically by a key range, and extracting those partitions through multiple concurrent ODBC connections instead of pulling the whole table through one connection. A table that once required a single uninterrupted sequential scan can now be split into, in some cases, hundreds of partitions extracted concurrently, each one crossing the VPN on its own connection rather than queuing behind the others.
The requirements guiding the project, established before development began, were straightforward and grounded in practical necessity. A team member without a development background needed to be able to register a new table for the nightly job through the application’s interface, not by writing code. Each table required its own configuration for partition count and concurrent connection limit, since some tables are enormous and others are small. If any single partition’s extraction crashed, the failure needed to be isolated rather than allowed to abort the entire run, with the system detecting the fault, cleaning up the partial write, and retrying that partition automatically. Following a run, anyone reviewing it needed to see exactly which tables succeeded, which failed, and the specific error behind each failure, without having to comb through raw log files.
How it works
The system runs as two separate processes rather than one combined application. An Orchestrator process determines what needs to run and maintains a persistent record of every attempt in SQL Server. A Worker process performs the actual extraction and bulk load, with one Worker spawned per table being downloaded. This process isolation exists for a concrete reason: the ODBC driver that communicates with IRIS has, on occasion, crashed the entire host process it was running inside, not merely the single query it was executing. By keeping Workers and the Orchestrator as independent processes, a driver crash inside one Worker only terminates that one table’s extraction. The Orchestrator detects the missed heartbeat within seconds, cleans up the partial state, and respawns a Worker for that table automatically, with no one needing to intervene manually in the middle of the night.
The result
A concentrated hardening effort brought the system to a genuinely production-ready state. Real validation runs completed in 45 to 46 minutes, a substantial improvement over the roughly two hours the legacy sequential process required, and did so with zero partition failures across the tested runs. Much of the engineering effort beyond that initial success went toward resilience rather than new features: watchdog processes that detect frozen ODBC connections through stalled row counts, automatic crash recovery through heartbeat-based lease expiration, and a connection scheduler that enforces both a hard cap on concurrent connections and a bandwidth ceiling at the same time, since the source database does not always behave predictably under sustained concurrent load.
Where it stands
Sluice has been built, tested, and hardened, and is in its final pre-launch phase. A written cutover checklist outlines what remains, and once deployed, it will fully replace the legacy job it was built to retire.