Sluice

What it is

We built Sluice to extract patient and clinical records from an electronic health record database each night and load 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, and at some point that database moved to run in the cloud rather than on premises. That change meant every single query now had to cross a VPN connection to reach it. IRIS also handles one query at a time reasonably well, but does not speed up much when several queries run at once the way a modern SQL Server database does.

The old job pulled each table down as a single query, one after another, over that same VPN link. The whole nightly load took as long as the slowest table allowed, with no real way to make it faster short of buying more bandwidth. That same shift made failures more expensive too, since resuming from a partial run had never been necessary back when the whole extraction ran quickly on premises. When it failed partway through, the usual fix was to rerun the entire job from the start, not just the piece that failed, and diagnosing what actually went wrong on a bad night often took longer than rerunning the job would have.

The idea behind the fix

Some database systems respond well to being asked several smaller questions in parallel rather than one large question at a time. IRIS is one of them. We designed Sluice to take each table, slice it into many smaller pieces, and ask for all the pieces at once instead of one after another. A table that used to download as one long sequential pull now downloads as, in some cases, hundreds of small pulls running side by side, each one crossing the VPN independently rather than waiting in line.

I wrote the requirements down before the first line of code, and kept them plain and practical:

• A team member who is not a developer should be able to add a new table to the nightly job using only a screen in the application, not by writing code.
• Each table needs its own dial for how many pieces to split into and how many run at once, since some tables are enormous and some are tiny.
• If one piece of the extraction crashes, the whole night’s job should not be lost. The system should notice, clean up, and try that piece again on its own.
• After a run, I should be able to see exactly which tables succeeded, which failed, and why, without digging through log files.
• The whole nightly job should finish in no more time than the old SSIS job took, and ideally quite a bit less.

Built like a set of locks and channels, on purpose

We built the codebase around a consistent naming theme drawn from civil engineering, the kind of vocabulary used to describe canals, locks, and water control structures. A single partition of a table download is called a Flume. The program that reads from the source database is the Intake. The program that writes into SQL Server is the Penstock, the term for a controlled pipe feeding a turbine. My idea was that anyone joining the project later could learn one clear vocabulary and understand both the software and the physical metaphor behind it at the same time. Some pieces of that original vocabulary were planned but never actually built, and we said so plainly in our own documentation rather than pretending the metaphor is more complete than it is.

More practically, I split the system into two separate running programs rather than one large one. The Orchestrator decides what needs to run and keeps a permanent record of every attempt. The Worker does the actual work of connecting to the source and pulling data, with one Worker process per table being downloaded. This split matters because the software driver used to talk to IRIS has, on occasion, crashed the entire program it runs inside, not just the one operation it was doing. With Workers and the Orchestrator as separate programs, a driver crash inside one Worker only takes down that one table’s download. The Orchestrator notices within a few seconds, cleans up, and restarts that table on its own. Nobody has to wake up and rerun anything by hand.

How the project actually grew, phase by phase

The first working version, which we built in early May, could do one thing: pull a table from IRIS and land it in the warehouse’s Bronze layer. Everything else came afterward, in a sequence that reflects what actually turned out to matter once real use began.

We added a web interface next, along with a way to monitor Worker processes while they ran. Then came Schema Drift, a feature for comparing what a table’s structure is supposed to be against what the live source database currently reports, since IRIS schemas can drift out of sync with what the warehouse expects without anyone noticing until a load fails. We redesigned Schema Drift more than once as the project matured, eventually scoping it narrowly to just that comparison rather than also trying to manage the warehouse table structure at the same time, since combining the two concerns had been creating confusing failures.

We followed that with a connection types and environment switching system, letting a table reference a logical role, such as one connection type for one part of the clinical system and a different one for another, rather than being pinned to one specific physical connection. I deliberately designed switching between environments, such as a live database and a sandbox copy, as a two-step confirmation with a full audit trail, rather than a single click, because column names and structure can differ between those environments and an accidental switch could make the system report false problems that were not real.

We added an index management screen with a built-in code editor so that performance-tuning scripts for the warehouse tables could be written and stored in one place, rather than run by hand outside the application. We built a priority-based scheduler to decide which tables should launch first when many are queued at once. And we added a full Performance Insights feature, then rebuilt it entirely, moving away from fixed rules of thumb about how many pieces to split a table into, toward a model based on actually measured run times from real history.

A scheduling design I proposed, argued against, and rejected

Not every idea I seriously considered made it into the final build, and one debate is worth describing because it shows the kind of judgment involved. The system needs to cap how many simultaneous connections to IRIS are allowed at once, shared across many Worker processes running independently. My original design used a simple shared counter to track this.

At one point, I worked out a more elaborate replacement in real detail: instead of a counter, track every live connection as its own row in a database table, each with an expiration time, so that if a Worker crashed without cleanly releasing its connection, the row would simply expire on its own and free up the slot automatically, healing itself without anyone noticing.

I rejected it, for reasons I wrote down plainly rather than glossing over. The proposed design would have added a database transaction before every single connection attempt, potentially hundreds of times a minute, which would have made a fast, simple operation into a much slower one. Worse, the expiration timer itself was a guess: set it too short, and a connection that was still legitimately in use could get treated as abandoned and have its slot handed to someone else, which is exactly the kind of bug the system exists to prevent. Set it long enough to avoid that risk, and the whole benefit of automatic healing disappears, since a genuinely crashed connection would then sit unclaimed for just as long as before. My existing simple counter, paired with the crash-detection system already built elsewhere, already recovered from a crash within ten to fifteen seconds in practice. The more complicated design was solving a problem I had already solved a simpler way, so I set it aside in favor of building a priority queue on top of the existing counter instead, which shipped and is the version running today.

Getting it fast enough to matter

A single concentrated stretch of work stands out as the point where the system became genuinely production-ready. Over two days, we ran a full audit of the system that turned up five defects severe enough to block launch, eight of lesser severity, and sixteen more minor issues, and we fixed and verified every one of them before that stretch of work ended. In the same push, we added a mode so the system could run against either the new warehouse or the older legacy database, depending on which one a given deployment needed, and we rewrote the connection scheduler so that both the number of simultaneous connections and the overall bandwidth budget were enforced together rather than as two separate, sometimes conflicting rules.

The result, measured against real validation runs, was a load that completed in 45 to 46 minutes with zero failures, down from roughly two hours under the old process. That is close to the two to three times speed-up I had hoped for in my original success criteria, and it came with something the old job never had: a session that fails partway now reports exactly which table failed and why, rather than leaving anyone to guess.

Handling a source database that behaves unpredictably

A meaningful share of our engineering effort went into resilience, not new features. IRIS connections can freeze without producing an error, appearing to be working while no data actually moves. We built a watchdog that runs inside each Worker and starts a stall timer only once the first row of data has actually arrived, so a query that is simply slow to start is not mistaken for a frozen one. If no progress happens for a set grace period, the watchdog kills that connection and lets the Orchestrator restart it clean.

There is also a subtler piece of resilience worth mentioning: when a database write and its confirmation get separated by a network hiccup, the system can end up unsure whether a write actually succeeded. Rather than risk writing the same data twice, or risk a fragile attempt to detect and delete a possible duplicate after the fact, we record a small marker as part of the very same transaction as the actual data write. On any retry, the code checks for that marker first. If it exists, the write already happened, and the retry reports success without repeating anything.

A real day inside the project

One entry from my own daily record gives a fair sense of what a serious day of this kind of work looks like. A long-planned review of the codebase came due, and it turned up dozens of individual findings across security, internal design, and a class of bug where two processes could interfere with each other’s work without either one crashing outright.

Two findings mattered most. The first was how the system should handle someone clicking a stop button in the middle of an active extraction, since simply telling the database to cancel was not safe and could leave a table in a partially written state. The second was that a small internal service, used by one part of the system to check the source database’s structure, turned out to be reachable over the network with no password of any kind, once we split the application and database servers onto separate machines. Neither was a small fix, and each required a real design decision.

For the stop button, I weighed a few approaches before settling on a database-driven flag that a running Worker checks on its own and shuts down cleanly when it sees it, rather than asking an outside process to guess what state a partial write was in. For the exposed internal service, we added a shared secret that both programs must present to each other before either will respond, on top of restricting which machines are even allowed to reach it at all. By the end of that one day, every item from the review had been closed, either fixed and confirmed working or found not to actually apply.

Timeline and effort

I worked on this across roughly 12 weeks. My own record, reconstructed from git commit history rather than logged in real time as I worked, shows 28 distinct working sessions in that period.

Technical reference

This section goes deeper than the narrative above, into the actual shape of the system, for anyone who wants to understand or rebuild something like it rather than just read about it.

Architecture

flowchart LR
    subgraph Source["Source system, across a VPN"]
        IRIS[("Clinical database")]
    end

    subgraph AppBox["Application server"]
        Web["Management UI<br/>Blazor Server"]
    end

    subgraph SqlBox["Database server"]
        Orch["Orchestrator<br/>Windows service"]
        W1["Worker<br/>one process per table"]
        CTL[("Control database")]
        TGT[("Target warehouse")]
    end

    IRIS ==>|"rows read, partition by partition"| W1
    W1 ==>|"bulk insert"| TGT
    W1 ==>|"attempt history written"| CTL
    CTL ==>|"pending work read each tick"| Orch
    Web ==>|"commands written as rows"| CTL

    Orch -.->|"spawns, monitors heartbeat, restarts"| W1

Reading the diagram. Thick arrows are data movement and point the way the data actually travels, so a read from the source points into the process doing the reading rather than out toward the thing being read. Dotted arrows are control, meaning one process starting or stopping another.

The one relationship worth noticing is that the management interface and the Orchestrator never talk to each other directly. Every command, a session launch, a kill request, a promotion, is written into the control database as a row or a flag, and the Orchestrator picks it up on its next tick, typically within a few seconds. That indirection is deliberate: either process can restart independently without losing track of what the other was doing.

How partitioning actually works

Each table’s JobTables row carries a PartitionMode of either Range or Modulo, a PartitionExpression, and a PartitionCount.

Range mode requires an indexed integer column. It divides that column’s value space into contiguous chunks, one per partition, and each Flume reads its assigned chunk. We prefer this mode when a suitable column exists, because it requires only one full pass over the data on the source side. It performs poorly on tables where the identifier column is skewed or has large gaps, since some partitions would end up covering far more rows than others.

Modulo mode is the fallback, and the default when no indexed integer column is viable. Every partition query wraps the base query with a generated clause of the form WHERE COALESCE((expression), 0) # count = partition, where # is IRIS’s own modulo operator and expression is typically a numeric cast of the table’s identifier column, for example cast(PATID as decimal(20,0)). Each partition number from zero up to PartitionCount - 1 gets its own query, and IRIS evaluates the modulo condition per row. This mode tolerates skewed or gapped identifiers at the cost of requiring IRIS to scan the full table once per partition rather than once overall.

FlumeBuilder.WrapInPartition is the pure function we wrote to generate this clause, and we kept it intentionally free of any side effects or dependencies, which makes it trivial to unit test in isolation from the rest of the pipeline.

What the Orchestrator actually does, tick by tick

The Orchestrator runs on a fixed tick, roughly every five seconds, and each tick performs several independent checks:

• Spawn gate — decides which Pending tables are allowed to launch a Worker this tick, based on how many connections are already granted in that table’s connection group versus the group’s configured cap. Tables launch in priority order using a Grade field, a longest-processing-time heuristic where larger or historically slower tables start first, so that the last table to finish is not one that could have started earlier.
• Stale-lease detection — reclaims any table whose Worker has not sent a heartbeat in the last three minutes, treating it as crashed, and either restarts it or marks it permanently failed once MaxWorkerRestarts is exceeded.
• Kill handling — watches for a KillRequestedAt flag set by the web application, cancels pending work immediately, and waits for all running Workers to acknowledge the kill on their own next heartbeat before finalizing cleanup.
• Promotion — once every table in a session has succeeded, promotes the landed data from its raw staging schema into the schema that downstream reporting reads from.

None of this relies on in-memory state that would be lost if the Orchestrator itself restarted. Every decision is derived fresh from what is currently in SluiceDB, which is what lets the Orchestrator crash and restart without losing track of an in-progress session.

The naming lexicon

A partial reference, the terms we actually implemented and that matter for understanding the codebase:

Some planned terms from the same theme, SluiceGate, Raceway, Drawdown, Crest, were designs I sketched out but never implemented, and we keep them listed separately in our own documentation as a record of what was considered rather than pretending they exist in the running code.

The data model, in plain terms

The control database, SluiceDB, holds a small number of tables that between them describe everything about a run:

• Sessions — one row per nightly (or manual) run, with a final CompletionStatus of Succeeded, Failed, or Cancelled once the run ends.
• JobTables — one row per table the system knows how to download, with its own partition count, connection concurrency, and either a plain table reference or a hand-written query.
• ChannelRuns — one row per Worker process launched for one table during one session, tracking whether it drained successfully.
• PartitionRuns — one row per individual partition attempt, including which attempt number it was and whether it succeeded, the finest-grained record in the system.
• ConnectionSlotRequests — one row per pending or active connection slot, granted by a background scheduler roughly ten times a second in priority order, with a time-limited grant that expires and frees itself automatically if a Worker crashes without releasing it cleanly.
• GlobalParameters — a single settings row controlling retry limits, backoff timing, notification email configuration, and which target database mode is active.

A table’s status as shown anywhere in the application, the dashboard, the monitor page, the run history, is read from one view we built that combines the session, the tracker, and the channel run state into a single answer, so that every screen agrees with every other screen about what actually happened.

Deployment topology

Every environment, development, test, and production, uses the same two-server split. One box runs IIS and the web application. A separate box runs every database plus the Orchestrator, running as a Windows Service, plus every Worker process the Orchestrator launches. The two communicate entirely through the database, never directly.

The system runs against exactly one target at a time, either the newer MedallionHub warehouse or the older legacy ODS database, chosen per environment and never mixed. The two modes use different table promotion mechanics under the hood, a schema-level rename in the legacy mode versus a schema transfer in the newer one, but we drive both with the same application code, treating the target as a parameter rather than two separate code paths.

Legacy schema mapping

The older system used a naming prefix convention that MedallionHub replaces with named schemas. This mapping matters for anyone tracing data from the old system into the new one:

Bugs we found and fixed along the way

We kept a running log of every real incident during development. Here is all of it, condensed to the essentials, grouped by the part of the system each one touches.

IRIS and ODBC quirks

• A computed-property error only showed up when we read every row, since a TOP 1 query let IRIS quietly skip the bad computation. We test with TOP 1 removed now, every time.
• The same error looked permanent the first time we saw it, then turned out to be transient. We re-verify a “still broken” report against the live system before trusting it.
• Two similar-looking IRIS errors need opposite responses: one is a genuine IRIS-side failure we can’t fix ourselves, the other is our own CAST expression, which we can.
• A simplified WHERE 1=1 test took a different code path inside IRIS than the real query, so it never reproduced a failure we were chasing. Probes need the real partition expression to mean anything.
• IRIS table references need a namespace-qualified name for live queries, not the bare name used for schema lookups.
• IRIS’s LENGTH() fails outright on stream-type columns, which report as ordinary varchar. We exclude them by their reported length now, not just by type name.
• Column names can differ between a live environment and a sandbox copy, so batch lookups need a per-column fallback instead of failing whole on one missing column.
• IRIS can report varchar lengths beyond what SQL Server supports. We treat those uniformly as SQL Server’s MAX type everywhere we display or compare them.
• IRIS’s native type names never reach the screen unconverted. Everything we show a user goes through one canonical mapping into SQL Server’s own vocabulary.

Orchestrator, scheduling, and worker lifecycle

• Our Worker-launch throttle had to live in the Orchestrator, not inside each Worker, since Workers launching independently could each pass their own check while collectively blowing past the limit.
• Our launch gate had to count actual granted connections, not each table’s configured maximum, since summing configured budgets overcounted usage and blocked launches with real capacity free.
• The same gate later had to count running Worker processes, not just active connections, since Workers spend real setup time before their first connection opens.
• When two connection types shared one pool, one could starve the other. We split capacity proportionally to each type’s pending workload now.
• A Worker binary that predated a routing change failed with an error that looked exactly like a missing record. We redeploy every executable sharing logic we’ve changed now, not just the one we edited.
• Tables that failed cleanly, rather than crashing, were never actually retried, since only the crash-retry path was wired up. We added the missing path.
• A session’s completion email could report success even after a crash at setup, since the logic inferred status from child records a crash never creates. We read the session’s own recorded outcome directly now.
• A promotion step could fire while the last table’s record was still being written, leaving a session stuck looking falsely partial. We added a one-tick grace period before concluding anything.

Data integrity and idempotency

• Promotion needs an explicit all-succeeded check right before it runs, everywhere it can trigger, since checking for “any terminal state” let one real failure ride along with 140 successes.
• A lost write acknowledgment could cause a duplicate write on retry. We write a small marker in the same transaction as the real write, and check for it before ever retrying.

Deployment and operations

• An unbounded retry loop with no memory of past failures grew a log table to 41 gigabytes over five weeks before we noticed. Every retry loop we write now backs off progressively instead of retrying forever at full speed.
• A missing certificate-validation setting worked by accident for a long time, until a library update changed its own default and broke it outright. We set that option explicitly on every connection string now.
• We once checked database size against the wrong physical server and got a misleadingly small number, since two servers shared a database name. We confirm which machine actually holds the file first now.
• An automated file copy with no exclusion list deleted a live production config file that was never in git. We check whether a config file is tracked before running any automated copy against a live server.
• A publish step run in one packaging mode left runtime files behind that broke a later publish in a different mode. We clear the output folder before switching packaging modes.
• A log meant to record every deployed script actually under-reported what had run. We stopped trusting it and check for the real database object instead.
• Deployment scripts with a smart quote or dash typed by a word processor silently corrupted the database objects they created. We keep every script in plain, unaccented characters now.
• A screenshot automation tool checked for an element before the setup step that made it appear, so it never found it. We chose a check element that’s always visible on load instead.

Data modeling and query correctness

• A database column type has to match its code type exactly, no automatic widening. We check how a column type is already handled elsewhere before writing new code against it.
• T-SQL’s LIKE treats square brackets as a character class, not literal text, so a search for bracket-containing text silently matched nothing. We use a literal string match instead whenever brackets might appear.
• A command-line tool’s stripped-down output produces exactly one line per row with no header. Code that assumed a fixed header count once misread real data as headers and deleted rows.
• Storing a timestamp with only whole-second precision let a row a fraction of a second past that mark compare as newer than its own baseline forever, stalling a polling query permanently. We preserve full precision in that comparison now.

Design-level lessons

• Turning the target database into a configurable parameter surfaced four traps at once: a completeness check reading the wrong location, logic assuming its own home database, a permission gap an old workaround had quietly covered for, and a cached setting going stale right after a change.
• A recommendation feature broke three different ways once tested against real data: a ratio blew up near a zero denominator, a prediction occasionally beat what was physically possible, and a fallback option kept getting considered before the better choices it was meant to only replace.
• Coordinating two background services that start at the same moment needed an explicit, cancellation-safe signal, since nothing coordinates that automatically on its own.

User interface bugs

• A background service needed a specific dual registration to be injectable elsewhere in the app; the standard registration alone left a page hanging forever.
• Scripts embedded directly in a partially updated page silently never ran, since the browser only executes scripts present in the very first full page load.
• A third-party code editor failed silently on a restricted network since it loaded from a public CDN. We bundled its files locally instead.
• A data table’s sort kept resetting on every render because it was bound to a value recomputed fresh each time. We stored the filtered results in a stable field instead.
• A loading spinner never appeared before a slow operation started, since setting the flag alone doesn’t force a screen update. We force one explicitly before the slow work begins now.
• A button’s click handler failed to compile because it nested a quoted string inside an already-quoted attribute. We moved that logic into a small named method instead.
• Adding endpoint-level access control intercepted requests before a page’s own friendlier “access denied” message could render. Often still the right trade, but a real, visible behavior change worth knowing about.

Where it stands now

As of this writing, we have built, tested, and hardened Sluice, but it has not yet had its first real production night. We have a written cutover checklist covering exactly what remains: which database scripts need to run, which server the system will live on, and one remaining manual test of the stop button described above, since it is the one piece of logic we’ve reviewed thoroughly but not yet clicked for real.

Once it is live, Sluice will fully replace the old SSIS job. The validation numbers from that hardening push suggest the parallel extraction approach delivers on its original goal, and the resilience work we built alongside it gives us a level of visibility into failures, and a level of protection against a source database that does not always behave predictably, that the old job never had.