Custom ETL services
The pipeline somebody wrote in 2019 and then left
No DAG, no lineage graph, no vendor. A service that consumes a queue, batches, parses, maps and bulk-inserts — with the interesting behaviour spread across code, config files and a scheduler that nobody has looked at in two years.
Why this category has no tooling
Every data observability product assumes a platform underneath it. Bespoke services have none, and that is not an oversight — it is the definition of the category.
Monte Carlo, Bigeye, Metaplane and the rest attach to a warehouse and watch tables. Airflow's UI knows about tasks because Airflow scheduled them. dbt can test a model because dbt built it. In every case the tool has a privileged position: it either ran the thing or it owns the store the thing wrote to.
A hand-written loader offers no such foothold. There is no scheduler API to query, no manifest describing the graph, no lineage metadata emitted anywhere. The service is a process. It reads from somewhere, writes to somewhere, and the only durable trace it leaves is whatever the author happened to log — which is usually enough to reconstruct what happened, and almost never arranged so that anyone can.
This matters more than it sounds, because these pipelines are not a legacy rump. They are where the awkward integrations live: the partner who sends fixed-width files, the till system that speaks a proprietary protocol, the reconciliation nobody could express in SQL. They tend to be the oldest code in the estate, the least documented, and disproportionately close to revenue.
The person who wrote it has left
This is the actual operating condition, and it changes what a diagnosis has to do. It is not enough to be correct — it has to be demonstrable to someone who has never read this code before and does not know which of its behaviours are deliberate. A claim like "the mapping is stale" is useless without the file, its modified time, and the rows it failed on. That constraint is why every conclusion Decim reaches carries its evidence with it.
The anatomy of a bespoke loader
Six shapes account for most of what these services do, and each has a characteristic way of losing rows quietly.
The queue consumer
A listener pulls messages and accumulates them until one of two conditions trips: a batch size or an elapsed time. That pair is the single most important thing to know about a continuously running pipeline, because it defines what a "run" even is. There is no daily job to point at — the batch id is the run, and its counts are the reconciliation. Anyone asking "did last night's load work?" is asking a question the system cannot answer in those terms.
Spool files and handoffs
Memory-mapped files, staging directories, a table called something like
Staging_Incoming. These exist because someone needed to decouple two stages, and
they are invisible to every diagram because they were never a design decision — they were a
fix. When rows disappear between two stages that both report success, a handoff buffer is the
first place to look.
The mapper
Lookup from an external code to an internal identifier. This is where the quiet failures concentrate, because the sensible-looking code is also the dangerous code: an unknown key is skipped and logged rather than thrown, so the batch completes and the run is green.
The bulk insert
SqlBulkCopy or its equivalent, with a batch size of its own that is unrelated to
the intake batch size. The pattern to watch for is the recovery path: the batch fails, the
code catches it and retries row-by-row so that one bad row cannot block the other 4,999, and
each individual failure is logged and dropped. It is a reasonable design. It is also a silent
data-loss machine the moment nobody reads the log.
Retry and dead letters
A retry worker, an error table, and a path back into the parser. Rarely drawn, frequently the cause. The failure mode is circular: rows that will never succeed cycle indefinitely, and because each attempt looks like normal activity, throughput metrics stay flat while real progress is zero.
Several workloads in one process
Covered in its own section below, because it is the one that most reliably produces a misdiagnosis.
Where the pipeline is actually defined
The instinct is to read the repository. The repository holds perhaps half of what decides how rows move.
This is the same problem that makes SQL Server and SSIS
hard to reason about, arriving from a different direction. There, the definition sits in
msdb and SSISDB. Here it is scattered across a config file, a
control table, a scheduler entry and a compiled binary.
| What | Where it lives | In git? | Note |
|---|---|---|---|
| Batch size, parallelism, timeouts | appsettings.json per environment | Sometimes | Release artefact or config server, not always the repo |
| Which table a row lands in | Mapping file or a control table in the database | Rarely | Edited in production more often than anyone admits |
| Transformation logic | Compiled C#/Java/Python — plus stored procedures | Partly | The proc has no commit history |
| Schedule | Cron expression, Quartz, Hangfire, or systemd timer | Sometimes | Often changed on the host during an incident |
| Which queues and tables are live | Runtime only | No | Observed from logs and database metadata |
| Retry and dead-letter routing | Code, plus whatever the operator did manually | Partly | Requeues frequently leave no trace at all |
Config is the pipeline
When a JSON file decides which table a row lands in, that file is not configuration. It is the routing layer, deployed without review.
A representative appsettings.json for a queue-driven loader. Almost every number
here changes the shape of a run, and none of them are visible to a monitor watching for
errors.
{
"Intake": {
"QueueName": "pos-transactions",
"BatchSize": 500,
"MaxBatchWaitMs": 2000,
"PrefetchCount": 1000,
"MaxDegreeOfParallelism": 8
},
"Load": {
"ConnectionString": "Server=sql-prod-02;Database=Sales;Max Pool Size=100;Connect Timeout=15",
"BulkCopyBatchSize": 5000,
"BulkCopyTimeoutSeconds": 120,
"RetryRowByRowOnFailure": true
},
"Sync": {
"Enabled": true,
"CronExpression": "0 2 * * *",
"FullReload": true
},
"Mapping": {
"Source": "File",
"Path": "config/tender-map.json"
}
}
Read it as a set of coupled decisions rather than a list of settings.
BatchSize: 500 with MaxBatchWaitMs: 2000 means a quiet period
produces small batches and a busy one produces large ones — so batch counts are not
comparable across the day, and an average is meaningless.
MaxDegreeOfParallelism: 8 against Max Pool Size=100 is fine in
isolation and stops being fine the moment a second workload wants connections from the same
pool.
RetryRowByRowOnFailure: true is the setting that converts a loud failure into a
quiet one. And Mapping.Source: "File" is the detail that decides your whole
investigation: if the mapping is a file, the deployed copy can differ from the one in the
repository and from a control table in the database that somebody added later. All three can
disagree, and only one of them is actually being used.
The mapper that fails politely
Here is the code that turns a missing mapping into a successful run. It is not badly written — it is the obvious thing to write, and something close to it exists in most of these services.
foreach (var tx in batch)
{
if (!_tenderMap.TryGetValue(tx.PaymentType, out var tenderId))
{
_rejects.Add(new RejectedTransaction(tx, "unmapped PaymentType"));
_log.Warning("unmapped PaymentType {PaymentType} batch {BatchId}",
tx.PaymentType, batchId);
continue; // the batch still completes normally
}
mapped.Add(tx.WithTender(tenderId));
}
await _bulk.WriteAsync(mapped, ct); // writes only what survived the loop
_log.Information("batch {BatchId} loaded {Loaded} of {Received}",
batchId, mapped.Count, batch.Count);
Note what is and is not there. The rows are not lost — they are in
RejectedTransactions, with a reason. The counts are not missing — the final log
line carries both Loaded and Received. Every fact needed to detect
this is already being recorded.
What is missing is anyone comparing the two numbers. The service does not, because it has no opinion about what the ratio should be. The monitor does not, because it is watching for exceptions and there were none. That gap — evidence present, comparison absent — is the characteristic failure of this whole category, and it is why detection tools and investigation are different problems.
One process, several workloads
A service that looks like one pipeline is usually four, running on different clocks and competing for the same resources.
A typical bespoke service accumulates responsibilities: continuous intake from a queue, a periodic aggregation, a nightly full sync with an upstream system, and some housekeeping. Each arrived in a different quarter, for a different reason, and they share a process, a connection pool and a thread pool.
They do not share a clock. The intake runs constantly; the sync runs at 02:00. And a full reload holding connections for eleven minutes will starve the loader that needs them continuously.
01:59:58 INF batch 88412 loaded 500 of 500 in 412ms
02:00:01 INF sync started full=true
02:00:04 WRN intake queue depth 1,240 (threshold 1,000)
02:03:52 WRN intake queue depth 9,880 (threshold 1,000)
02:07:19 ERR Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. Max Pool Size=100
02:07:19 INF batch 88413 loaded 0 of 500 in 15,004ms
02:11:36 INF sync completed rows=2,410,882 duration=00:11:35
02:11:37 INF batch 88414 loaded 500 of 500 in 398msRead from the top, this is unambiguous. The sync starts at 02:00, queue depth climbs, the loader can no longer get a connection from the pool, and batch 88413 loads 0 of 500. When the sync finishes at 02:11 the next batch succeeds normally and every metric returns to baseline.
Nothing here alerts. The service never crashed, the pool timeout was caught and logged, and by the time anyone looks at a dashboard the graph is flat again. The only durable evidence is 500 rows that were received and never loaded — and the log line that says so, four minutes deep in a file nobody opens.
Why this misdiagnoses so reliably
An investigation that does not know about the second workload has to explain a stall with only the loader's own behaviour available, so it reaches for the plausible: a slow upstream, a bad batch, network flap. Each of those is consistent with the evidence in front of it. All of them are wrong, and each sends someone to instrument the wrong component.
The tell is periodicity. A cause that recurs at exactly the same minute is a schedule, and schedules belong to something else. This is why Decim treats a service as a set of workloads rather than a single unit, and why the topology records what was observed running as well as what was declared.
Diagnosing a partial load by hand
The sequence Decim automates. Worth knowing regardless — if these queries return nothing useful on your system, that itself tells you which evidence you are missing.
- 1
Establish what arrived
Before anything else, find the number of rows the service believes it received. If there is an intake or batch table, that is your denominator. If there is not — and often there is not — the only witness is a log line, which is why the log's retention window silently becomes your investigation window.
Serilog / NLog — the line that carries both numbers batch 88413 loaded 0 of 500 in 15,004ms - 2
Reconcile received against loaded and rejected
The arithmetic is the whole diagnosis:
loaded + rejected = received, or rows went somewhere nobody has written down. Run this across a day of batches rather than the one that was reported — a slow leak looks like nothing in a single run.Reconciliation by batch -- Reconcile intake against load and rejects, one batch at a time. -- A batch is only complete when loaded + rejected = received. Anything else -- is a row that entered the service and left no record of where it went. SELECT b.BatchId, b.ReceivedAtUtc, b.ReceivedCount, COUNT(DISTINCT t.TransactionId) AS LoadedCount, COUNT(DISTINCT r.RejectedTransactionId) AS RejectedCount, b.ReceivedCount - COUNT(DISTINCT t.TransactionId) - COUNT(DISTINCT r.RejectedTransactionId) AS Unaccounted FROM dbo.IntakeBatch AS b LEFT JOIN dbo.[Transaction] AS t ON t.BatchId = b.BatchId LEFT JOIN dbo.RejectedTransactions AS r ON r.BatchId = b.BatchId WHERE b.ReceivedAtUtc >= DATEADD(day, -1, SYSUTCDATETIME()) GROUP BY b.BatchId, b.ReceivedAtUtc, b.ReceivedCount HAVING b.ReceivedCount - COUNT(DISTINCT t.TransactionId) - COUNT(DISTINCT r.RejectedTransactionId) <> 0 ORDER BY b.ReceivedAtUtc DESC; - 3
Group the rejects by reason and hour
A reject table with a steady trickle is normal. A reason that was rare last week and dominates today is a mapping or upstream-format change, and the first hour it appears is the interval to correlate against deployments.
Reject profile over two weeks -- If there is no IntakeBatch table, the reject table is the next best -- witness. Group by reason and hour: a silent mapping failure shows up as a -- reason that was rare last week and dominant today. SELECT CONVERT(date, RejectedAtUtc) AS [day], DATEPART(hour, RejectedAtUtc) AS [hour], Reason, COUNT(*) AS Rows, MIN(RejectedAtUtc) AS FirstSeen, MAX(RejectedAtUtc) AS LastSeen FROM dbo.RejectedTransactions WHERE RejectedAtUtc >= DATEADD(day, -14, SYSUTCDATETIME()) GROUP BY CONVERT(date, RejectedAtUtc), DATEPART(hour, RejectedAtUtc), Reason ORDER BY [day] DESC, Rows DESC; - 4
Check what else was running
A bespoke service almost never does one thing. If the stall has a clock — the same minutes every night — the cause is a neighbour, not the loader. Look for pool timeouts and blocking during the window.
Live waits and blockers -- What the loader is waiting on right now. Run it during the stall, -- not after: sys.dm_exec_requests only shows requests currently executing. SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time / 1000.0 AS wait_seconds, DB_NAME(r.database_id) AS [database], SUBSTRING(t.text, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS running_statement FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE r.session_id <> @@SPID AND (r.blocking_session_id <> 0 OR r.wait_type IS NOT NULL) ORDER BY r.wait_time DESC; - 5
Correlate with the deployment record
Only once you have the first bad batch and its timestamp is it worth looking at commits. Working forwards from a release list produces plausible stories; working backwards from a timestamp produces a cause. This ordering is the difference between a diagnosis and a guess.
Bound the search to the interval you proved git log --since="2026-08-05 01:50" --until="2026-08-05 02:15" \ --name-only --pretty=format:"%h %an %ad %s"
| Failure mode | What it looks like | What proves it |
|---|---|---|
| Unmapped lookup key | Rows diverted to a reject table, run reports success | Reject-table counts grouped by reason and hour |
| Bulk insert partial failure | Batch rolls back, retries row-by-row, loses the failures to a log file | Loaded vs received counts per batch id |
| Connection pool exhaustion | Loader stalls while another workload holds every connection | Pool timeout errors correlated with the other workload's window |
| Config drift | Deployed mapping file disagrees with the database control table | File hash and modified time against the table's rows |
| Silent schema change | A new column or widened type upstream, quietly truncated on write | Column metadata history against the first bad batch |
| Requeue loop | The same rows cycle through retry forever without alerting | Repeat counts by correlation id across runs |
What Decim collects
Read-only, from inside your network, and every item is inspectable in the finished diagnosis.
The agent runs on your infrastructure and makes outbound HTTPS connections only. For a bespoke service it gathers application logs and their correlation ids; row counts and timestamps from intake, target and reject tables; column metadata and its change history; the deployed configuration with secrets redacted before transmission; scheduler entries, whether cron, Quartz, Hangfire or a systemd timer; and commit history where a repository is available.
It also records what it could not find, which is often the more useful half. A
service with no intake table cannot be reconciled at the batch level, and saying so plainly is
better than producing a confident number derived from a proxy. Where OpenTelemetry spans exist,
attributes such as parser.batch_id, raw_incoming_count and
success_count make the reconciliation exact rather than inferred.
From there the investigation proposes causes, tests each against what was collected, and records the ones it ruled out along with the proof — see how investigations work. Where a fix is mechanical, it arrives as a pull request you review like any other.
Common questions
Do you need access to our source code?
Our service is .NET Framework 4.7 on a Windows VM. Is that a problem?
We have no structured logging — just text files. Does that work?
How is this different from adding data quality tests?
Can it tell us the batch size and parallelism to use?
What does it do about connection strings and secrets in our config?
Related: SQL Server & SSIS for the same problem where
the definitions live in msdb;
Airflow & dbt for why a green DAG and a wrong row count
are not mutually exclusive; and all solutions by stack, role and
failure mode.
Get started
Bring us a pipeline that broke last week
The fastest way to evaluate this is a real incident you already know the answer to. If Decim gets it wrong, that is a far more useful demo than one where it doesn't.