SpecialtyCare

Introduction

SpecialtyCare is a nationwide provider of outsourced clinical services to hospitals, covering perfusion, intraoperative neuromonitoring, and surgical support across thousands of procedures a year. Two problems sat between their clinical systems and the reporting that depends on them: replicating a cloud database into an on-premise warehouse while reshaping its schema, and turning the deeply nested JSON their medical devices produce into tables anyone can query. Etlworks forward deployed engineers built both.

The Challenge

Replicating a database and reshaping it at the same time: Data had to move from a cloud SQL Server instance to an on-premise SQL Server, but the destination is not a copy of the source. The target schema organizes everything under a single root record, the surgical case, so replication had to transform the schema in flight, clean the data on the way through, and resolve a large number of lookups across both source and destination tables. Close to real time, at millions of records.

A job change data capture cannot do: CDC moves changes as they are. This work required the shape of the data to change between source and destination, which meant traditional ETL running close to real time rather than a replication stream.

Deeply nested JSON from medical devices: Devices emit heavily nested JSON documents that had to be flattened and loaded into multiple destination tables. The backlog stood at 100,000 files, with 4,000 more arriving every day and individual files ranging from 20MB to 1GB.

Files that cannot be trusted: A device will occasionally produce a document that cannot be parsed at all. A pipeline running unattended could not stop when it met one.

Why Etlworks

Transformation during replication: Schema changes, cleansing, and cross-table lookups happen as part of the load rather than in a separate downstream step.

Nested data handling: Read, flatten, and route a single deeply nested document into several destination tables without writing a parser.

Throughput at file scale: Gigabyte documents processed without holding them in memory.

Error isolation: A bad record or an unreadable file fails on its own terms rather than taking the run down with it.

Forward deployed engineering: Etlworks engineers built both pipelines with the customer's team rather than handing over documentation.

The Solution

Schema-transforming replication: Flows read from cloud SQL Server, apply the cleansing and lookup logic, resolve every record to its root surgical case, and write into the on-premise schema close to real time.

Nested JSON flattened into multiple tables: Each device document is parsed, flattened, and split across the destination tables it belongs in, at file sizes up to 1GB.

Automation with self-recovery: The file pipeline runs unattended, retries what fails, and picks itself back up after an interruption without anyone restarting it.

Quarantine for unparsable files: Documents a device produced badly are marked as unparsable and set aside, so one malformed file never blocks the queue behind it.

Results

About one file per second, full ETL: Enough to clear the 100,000-file backlog and stay ahead of the 4,000 arriving daily, which the pipeline absorbs in a little over an hour.

Millions of records replicated close to real time, with the schema transformed, the data cleaned, and every lookup resolved on the way.

A fully automated process with retries and self-recovery, running without an operator.

Malformed device files handled as data, not as outages, marked and skipped while the rest of the queue keeps moving.