A Traceable Path from Ingestion to Insight

START WITH THE MECHANISM

Interactive companion and laboratory update: 12 November 2026. The editorial date places the topic on the timeline; this current explanation and its measured evidence retain their actual preparation and execution dates.

THE FULL INVESTIGATION

A Traceable Path from Ingestion to Insight

Dates and technology context

This is a retrospective editorial timeline. Topic context: 24 September 2026. First publicly available: 12 November 2026. Current text, visuals and laboratory revisions were prepared on 12 November 2026.

The local SQLite lineage companion and million-record run were performed on 12 November2026 using .NET10.0.15. Preserve the September2026 Azure design as a proposal and its distinct historical package baseline.

Execution update, 12 November 2026: the original cloud design below is preserved as a proposal. The dated appendix reports a separately implemented and tested local SQLite companion, including an actual million-record run. It does not claim execution of the original Azure stack.

A dashboard shows a number. Someone asks where it came from. The team can identify the database, the job and perhaps a collection of logs. Reconstructing the specific input records, transformation version and recovery attempts behind that number takes much longer. The system has a path for producing an answer, but only an improvised path for explaining it.

Traceability begins by treating that explanation as a product requirement. Every accepted transformation should leave enough durable information to connect its output to an identified input and a declared rule. Operational traces can help explain how the work ran. They should not be the only place where the business result remembers its origin.

This is a reference architecture and laboratory protocol. It is not a client case study, a record of an implemented platform or a claim about measured scale. The proposed stack uses .NET 9, ASP.NET Core 9, Azure Blob Storage, Azure Service Bus and SQL Server 2022. The contextual date is September 2026; the manuscript was written in November 2026. Any future implementation and measurements must carry their actual execution dates.

The proposed reproduction pins SDK 9.0.101, runtime 9.0.0, Azure.Storage.Blobs 12.23.0, Azure.Messaging.ServiceBus 7.18.2, Microsoft.Data.SqlClient 5.2.2 and OpenTelemetry 1.11.2, with ASP.NET Core instrumentation 1.11.1. The database reference is SQL Server 2022 RTM, build 16.0.1000.6. NuGet's Service Bus record and the metadata pack establish historical availability. These pins have not been restored or executed together and are not current production patch recommendations. The transaction protocol and JSON schema are original explanatory designs.

Give the insight a question it must answer

Use a synthetic workload that is understandable without domain expertise. Each source record represents an adjustment to a fictional account. It contains an event identifier, account identifier, integer amount in a declared unit and effective timestamp. The derived result is a total per account and reporting period.

The simplicity is intentional. If the experiment cannot explain an integer aggregate, adding a more sophisticated analytics layer will not repair its evidence model. Begin with a small generated corpus and increase its size only when a specific scaling question requires it.

Create an independent oracle from the canonical source records. It should compute the expected accepted event set and account totals with a separate, straightforward implementation. Store its output beside the generation seed and source hashes. Avoid using the production transformation code inside the oracle, because agreement would then be less informative.

The central question is concrete: for any reported total, can the system identify the accepted source records, the transformation version and the run that produced it, while distinguishing repeated delivery from repeated business effect? A fast answer to that question is useful. A precise answer is necessary.

Use immutable artifacts as the starting point

01 / See the idea

Preserve bytes and interpretation

Enlarge diagram

Upload each generated input file under a unique artifact identity. Its manifest records the content hash, record count, schema version and generation parameters. A path such as latest.csv is convenient for a person, but it is an inadequate identity for a result that must remain explainable after the next upload.

The application should verify the artifact before accepting its records. If the object at the referenced location no longer matches the expected identity, stop that work item and record the conflict. A retry must not silently change the input question.

Blob Storage's concurrency controls include conditional operations using ETags. Microsoft's concurrency guidance describes that mechanism. The reference design uses immutable names and a recorded hash as its primary artifact identity; a version condition adds protection during retrieval. Neither mechanism establishes that the data is meaningful. It establishes which bytes the application agreed to interpret.

Retain a distinction between the original bytes and parsed records. A parser fix can change the interpretation of the same artifact. Therefore the parsing and transformation version belongs in the lineage record. A later correction should create a new derived version, not rewrite the old explanation until nobody can tell what happened.

Make admission recoverable

Writing an object and sending a notification are separate effects. If the process stops between them, an object may exist without a message. If a send is retried after an uncertain acknowledgement, more than one notification may refer to the same artifact. The design should expect both conditions.

A proposed admission service first stores the immutable artifact, then records an ingestion job and its notification intent in a single SQL transaction. A dispatcher sends the pending notification to Service Bus and records the dispatch outcome. If the dispatcher repeats a send, the work identity remains stable. Orphaned artifacts can be identified by comparing storage manifests with registered jobs under an explicit retention policy.

This is more machinery than a direct upload-and-send sequence. The alternative may be acceptable for a disposable experiment if a reconciliation scan can recover missed notifications. Choose the mechanism according to the recovery contract. What is unacceptable is assuming that two independent writes become atomic because they appear next to each other in application code.

Microsoft's Service Bus settlement documentation distinguishes transfer and processing acknowledgements. The application's durable job record serves a different purpose: it allows admission to be reconciled even when the caller does not know whether a particular transport attempt succeeded.

Design identities for the questions they answer

One identifier rarely serves every boundary well. The artifact identifier names the input bytes. The event identifier names a business occurrence. The work-item identifier names a processing unit. The attempt identifier names one execution. The trace identifier connects diagnostic observations across that execution.

Do not merge these identities merely to simplify a dashboard. A redelivered work item should have another attempt while preserving the same business identities. A new transformation version may process the same source event and produce a different versioned result. A trace can be missing or sampled out without erasing the durable relationship between input and output.

Keep identifiers free of unnecessary personal or secret information. A correlation value should help locate a record, not embed the record's sensitive contents in every downstream log. Access to the lineage store remains an application permission question; knowing an identifier should not grant access to its data.

The following is an illustrative record shape, not a tested SDK contract:

{
  "artifactId": "synthetic-artifact-0001",
  "artifactHash": "hash-recorded-by-generator",
  "schemaVersion": "adjustment-v1",
  "workItemId": "artifact-0001-segment-0000",
  "transformationVersion": "aggregation-v1",
  "attemptId": "generated-per-processing-attempt"
}

These values are placeholders. Their usefulness lies in the relationships they express, not in their appearance as realistic production identifiers.

Commit the effect and its explanation together

02 / A concrete example

A receipt belongs to an output version

Enlarge diagram

The worker reads a bounded segment, validates its records and attempts to accept each business event. The acceptance record, derived effect and lineage relationship should share a database transaction. If the transaction fails, none of those three should claim completion independently.

Enforce two different identities at the database boundary. A source-event registry binds eventId to its payload hash. A processing receipt is unique for the pair (eventId, outputVersion), where the output version identifies the transformation being materialized. A prior lookup followed by a separate insert leaves a race between workers; database constraints must arbitrate both identities. A repeated delivery to the same output version must not create another effect, while a deliberate replay into a new output version must remain possible.

This pseudocode describes the intended transaction semantics:

register or confirm SourceEvent(eventId, payloadHash)
    enforce unique eventId and reject a conflicting payload hash

begin transaction
    insert ProcessingReceipt(eventId, outputVersion)
        enforcing unique (eventId, outputVersion)
    apply account adjustment within outputVersion
    insert Lineage(eventId, outputVersion, transformationVersion,
                   artifactId, recordPosition)
commit transaction
complete work notification after all required records are terminal

if ProcessingReceipt(eventId, outputVersion) already exists:
    confirm the source payload identity
    record another attempt without another effect in that version
if source identity has a conflicting payload hash:
    quarantine the conflict in every output version

A source registration alone is not a claim that processing completed. Test the distinction explicitly: two deliveries of one event into output v1 should create one effect in v1; replay into v2 should create one separate effect in v2 while preserving v1. A changed payload under the same source identity must be rejected in both versions. These are acceptance scenarios for the design, not recorded execution results.

The transaction boundary can be a record or a bounded batch. A larger batch can reduce repeated overhead, but it increases the work repeated after a failure and may hold resources longer. The laboratory should compare a small number of declared batch sizes only after the correctness contract is working.

SQL Server 2022 is the chosen historical database baseline, supported by its official release record. No feature introduced in SQL Server 2025 is needed for this design. The hard part is preserving application semantics across boundaries, not selecting the newest engine name.

Keep the operational trace beside the durable lineage

A trace should help explain where time was spent, which dependency failed and how an attempt moved through the system. The lineage store should help establish which inputs contributed to a result. Those responsibilities overlap enough to benefit from shared identifiers, but not enough to collapse into one storage policy.

Sampling may be appropriate for operational traces. Retention may be short. An exporter can be unavailable. If any of those conditions destroys the only explanation of an accepted result, the architecture has made a business requirement depend on a diagnostic convenience.

Store the durable relationship in the database transaction and attach correlation identifiers to the relevant spans. The trace can point to an ingestion job or output version without carrying the entire source payload. An investigator can then move from a symptom to an authorised lineage query, rather than search logs for a value that happens to look familiar.

The OpenTelemetry .NET 1.0 announcement establishes that the instrumentation foundation predates the historical baseline. Exact package pins and instrumented operations must still be verified in the future build. The diagram is a proposed observability boundary, not evidence that traces have already been captured.

Ask the result to explain itself

The reference query should expose more than an account total. It should identify the output version, transformation version, source artifact set and accepted event count. A separate authorised query can retrieve the contributing record identities when an investigation requires them.

For a large aggregate, materialising every input-output pair may be expensive. That is a design trade to examine, not a reason to abandon provenance. Some transformations can be explained through partition manifests and deterministic rules. Others require record-level relationships. The required granularity depends on which questions the system must answer and which corrections it must support.

Begin with record-level lineage in the small fixture because it makes the invariant easy to inspect. Then evaluate a compressed representation against the same queries. If the smaller representation cannot identify which records were affected by a parser defect, it may be inadequate for the intended recovery workflow even if it explains the grand total.

The interface should distinguish “not processed,” “accepted,” “rejected by a declared rule” and “unresolved conflict.” A missing value should not become zero merely because zero is easier to display. An apparently complete dashboard can hide an incomplete pipeline when those states are collapsed.

Use faults to test the explanation

03 / Watch the mechanism

Recovery must preserve the explanation

Enlarge diagram

Static view. Play to see how the mechanism unfolds.

Inject a duplicate notification and verify that the business effect remains singular while the attempt history grows. Stop a worker after commit but before acknowledgement and verify that the next attempt discovers the existing effect. Change an artifact identity and verify that the system refuses to continue as though it were the original input.

Then disable the telemetry destination temporarily. The pipeline's durable lineage should remain intact, even if diagnostic detail is lost. This test separates the business evidence requirement from the observability availability assumption.

Finally, introduce a deliberate transformation defect in a disposable version. The purpose is not to observe how convincingly a dashboard can explain a wrong number. It is to test whether the system can identify the affected outputs, retain the original version and rebuild a corrected version from the same canonical input.

Each scenario needs an independent expected outcome. Reconcile event sets, account totals and output-version relationships. Compare individual accounts as well as the overall sum. A missing positive adjustment and a missing negative adjustment can cancel numerically while leaving two accounts wrong.

Compare the simpler alternatives

A queue and several stores are not prerequisites for every ingestion problem. A scheduled process reading a small set of files into a database can be easier to operate and may satisfy the same business requirement. The reference architecture uses Service Bus to study decoupling and recovery, not to imply that every pipeline needs a broker.

Similarly, a database-native import may be preferable when the transformation is simple and the source already fits the database boundary. The application worker becomes useful when validation, versioning or integration logic belongs outside that boundary. Make the trade explicit instead of treating an additional service as evidence of architectural maturity.

The strongest counterargument to detailed lineage is its cost. Storing relationships, preserving artifacts and maintaining versioned outputs requires resources and operational attention. A low-consequence dashboard may not justify record-level provenance forever. The right response is to define the required questions and retention, then measure the cost of answering them.

The laboratory should be able to reject its own design. If a simpler pipeline answers every required provenance and recovery question with less operational burden, that is a useful result. If detailed lineage materially improves correction and diagnosis, the evidence should show which questions became answerable.

What the laboratory must publish before it becomes a case study

The next implementation should publish the generator, canonical source manifest, independent oracle, schema, transaction rules, fault schedule and reconciliation queries. Its evidence pack should include actual execution dates, package pins and raw observations. No empty result table should be filled with plausible-looking values for presentation.

The mandated .NET 9 stack is a historical reference baseline, not a recommendation for a new production deployment today. Its support status at the contextual date differs from its status when this manuscript was written. Reproduction and current deployment need separate decisions, even when they use similar code.

A successful run would support a bounded conclusion: for this generated workload and these failure scenarios, the implementation preserved the declared relationships from input artifacts to derived outputs. It would not establish an unmeasured customer scale or a universal reliability claim.

That is enough to make the work valuable. The reference project gives readers a way to ask a harder question than “did the pipeline finish?” It asks whether the system can explain what it finished, what it did again, what it refused to accept and how its answer could be corrected without erasing the evidence of the earlier one.

Execution appendix, 12 November 2026: receipts for versioned outputs

This appendix adds a real SQLite implementation of the acceptance and lineage boundary described earlier. It was built and executed on 12 November 2026. The original Azure reference architecture and its historical package choices remain a design record; the new local companion does not claim that those cloud services were deployed or benchmarked.

The existing TraceablePipeline artifact supplied alongside the earlier page is preserved. This companion gives readers another, explicitly durable implementation to inspect. It focuses on source identity, versioned receipts and atomic effects rather than replacing every part of the original proposed service.

The selected execution environment is SDK 10.0.400 and runtime 10.0.15, with Microsoft.Data.Sqlite 10.0.0 and the native bundle fixed by the package lockfile. These later execution inputs belong to the dated appendix and do not move the original article's context date. Microsoft.Data.Sqlite 10.0.0, SQLite native bundle 3.0.2.

The shared small suite passed 32 checks. It verifies source conflicts, batch rollback, duplicate handling, versioned replay, lineage counts, exact receipt identity sets and account totals. A separate child-process scenario commits, exits deliberately and is resumed by the parent from the same database file.

One source identity, more than one legitimate derived version

01 / See the idea

The receipt key includes the output version

Enlarge diagram

The central schema distinction is between SourceEvent(id, hash) and Receipt(eventId, outputVersion). The source identity names an occurrence and binds it to a payload. The receipt names one application of that source into a particular derived version.

This resolves a subtle conflict between idempotency and correction. A receipt keyed only by event identity would prevent a deliberate replay into a corrected output version. A receipt keyed only by a new run identifier could permit repeated delivery within the same output version to add another effect. The pair preserves both requirements.

In the companion, v1 applies the source amount unchanged, while v2 doubles it. This is a deliberately simple, visible difference, not a realistic accounting migration. The source event remains the same. The transformation and derived aggregate differ.

The writer first establishes source identity, then attempts the versioned receipt. A matching existing receipt skips the effect. A new receipt permits one effect and one lineage row in the same transaction. A changed payload under the original event identity is rejected in both output versions, even if the new version has no receipt yet.

That last rule prevents a replay from quietly rewriting history. A corrected interpretation can be a new transformation of the same canonical input. A different input masquerading under the same source identity is an integrity conflict, not a new interpretation.

Store enough lineage to answer a specific question

The fixture's lineage row contains event identity, output version, transformation identifier, artifact identifier and record position. It references the processing receipt through a composite foreign key. The source payload hash is available through the source-event relation.

CREATE TABLE Lineage(
    eventId INTEGER NOT NULL,
    outputVersion TEXT NOT NULL,
    transformation TEXT NOT NULL,
    artifact TEXT NOT NULL,
    recordPosition INTEGER NOT NULL,
    PRIMARY KEY(eventId, outputVersion),
    FOREIGN KEY(eventId, outputVersion)
        REFERENCES Receipt(eventId, outputVersion)
);

The artifact identifier is synthetic-generator-v1, and the record position is the generated event identity. This is a fixture convention that makes the source reproducible from the declared generator. It is not a claim that an arbitrary production file can always be sought efficiently by record number.

A reader can therefore ask which source event contributed to output v1 or v2, which transformation was applied and which payload identity the system accepted. The aggregate alone could not answer those questions. The lineage relation supplies the explanation without depending on a retained diagnostic trace.

The representation is intentionally modest. It does not capture every parser option, data-quality rule or business approval. A real transformation should include the identities necessary to reproduce its meaning. Adding many fields without identifying the questions they answer would increase storage while leaving the explanation unclear.

Commit the explanation with the effect

Source acceptance, processing receipt, aggregate update, lineage insertion and checkpoint update occur inside one batch transaction. If the writer throws before commit, none of those changes should describe an accepted batch.

The small suite verifies that contract by injecting failure after all batch work has been issued but before commit. It checks not only that the operation failed, but that source count, receipt count, lineage count, aggregate rows and checkpoint all remain empty for the first batch. Retrying then accepts the same records.

A second test introduces a source conflict after a new record has already been processed within the transaction. The conflict rolls back the preceding new record as well. This demonstrates the declared all-or-nothing batch boundary. A per-record transaction design might choose another behavior, but it would need another explicit recovery and reporting contract.

SQLite groups statements into an atomic transaction under its storage assumptions. That local guarantee is exactly the mechanism used here. It does not imply atomicity between a database and an unrelated cloud object store or message broker. SQLite transaction behavior through Microsoft.Data.Sqlite.

The source inserts and receipt inserts use uniqueness-aware no-op behavior for duplicates. The application then distinguishes a matching source hash from a conflicting payload; SQL's no-op alone would not establish that distinction. SQLite UPSERT semantics.

Repeated delivery preserves one effect in each version

02 / Watch the mechanism

A corrected output keeps its earlier explanation

Enlarge diagram

Static view. Play to see how the mechanism unfolds.

After committing the first ten records into v1, the suite repeats them and observes ten duplicates with no new effect. It then writes those same source records into v2 and observes ten newly accepted versioned receipts. A second v2 replay again produces only duplicates.

The verifier checks v1 and v2 independently. Receipt counts and lineage counts match the expected number for each version. Exact event identities are read in order and compared with the canonical range. Individual account totals are compared with an independently calculated oracle, using factor one for v1 and factor two for v2.

The global source count does not double when v2 is created. That is an important diagnostic signal. The system retains one canonical source identity while allowing multiple documented interpretations. A report that combines global source count with per-version receipt count without labelling them could make correct behavior look like missing or duplicated data.

The large run uses the same distinction at a larger actual volume. It writes one million v1 inputs, repeats one thousand of them, and replays one thousand into v2. The v2 phase is intentionally smaller; the report must not imply that a million v2 outputs were created.

Restart from durable state, not a remembered loop counter

The child-process test writes ten events, commits and prints a marker, then calls Environment.Exit(23). The parent checks that specific exit and marker before reopening the file. It verifies the committed state, repeats the first ten events without another effect, then continues with events eleven through twenty.

The result demonstrates that the accepted state survives the process that produced it. It is not a power-failure test, and it does not measure recovery from corrupted storage. The host remains alive, and the child exits after a known successful commit.

The checkpoint is valid only under the fixture's ordered, contiguous generator. It stores the maximum accepted identifier transactionally, while verification checks that the complete lower range exists. It must not be copied into an arbitrary out-of-order stream as though a maximum identifier proved completeness.

This is a useful example of a traceability requirement constraining an optimization. A compact checkpoint can be sufficient when the source order and acceptance boundary support it. When they do not, the system needs a richer progress representation rather than a more confident label on the same number.

Keep the client's memory bound distinct from total memory

03 / See the idea

One source set, distinct versioned receipts

Enlarge diagram

The writer reuses an array of one thousand value-type records. It generates the next batch only when the current batch has been processed. Prepared commands are reused across records and transactions. This keeps the number of client event records bounded while permitting a million-event run.

Other memory still exists. Hash arrays, boxed parameter values, provider internals, database caches and the runtime contribute allocation and retention. The measured managed allocation is therefore reported separately from the fixed buffer capacity. Neither is a measurement of native SQLite cache memory or the process's retained heap.

The pattern of transaction batching and parameterized-command reuse is documented for Microsoft.Data.Sqlite, but the particular batch size remains a fixture setting rather than a universal recommendation. Bulk insert with Microsoft.Data.Sqlite.

The exclusive run completed on 12 November 2026 at 14:10 UTC, with status passed. It used .NET 10.0.15 and SQLite 3.50.4 on Windows build 20348, with DELETE journaling and FULL synchronous mode. This was one sequential run, not a repeated statistical comparison or a service-level latency study.

Phase	Submitted records	Newly accepted	Duplicates	Transactions	Timed seconds	Managed bytes allocated
new-v1	1,000,000	1,000,000	0	1,000	12.183669	2,134,861,536
duplicate-v1	1,000	0	1,000	1	0.005602	1,353,280
new-v2	1,000	1,000	0	1	0.014496	2,689,200

The first phase actually accepted one million unique events into v1. Its observed rate was approximately 82,077 submitted records per second within the declared generation, hashing and transaction boundary. That number is not projected from a smaller test. It also is not a forecast for a broker-backed service: there was no network ingestion, cloud object read or production query workload in this path.

The duplicate phase left v1 with one million receipts and one million lineage rows. The v2 phase created one thousand separate receipts and lineage rows while retaining the global source count of one million. The smaller follow-up phases have different work and volumes, so their elapsed times should not be ranked as competing architectures.

All three phases passed exact event-range and independent per-account reconciliation. For v1 the expected receipt-ID sum was 500,000,500,000; for the first thousand v2 records it was 500,500. The sum is an additional summary, not the sole completeness check: the verifier also walks every ordered receipt identity and checks every account total.

The first phase allocated about 2.13 billion managed bytes cumulatively while using a reusable event buffer of one thousand records. These statements are compatible. Allocation measures work over time, not simultaneously retained memory. Hash results, parameter boxing and provider internals still allocate. The experiment did not collect a retained-heap or native-cache profile and therefore does not claim a process-wide memory ceiling or a zero-allocation ingestion path.

Reconciliation runs outside the timed phase, and the reported database state is captured before cleanup. After the report was written, connections were disposed and the owned database files removed. A later optimization should retain the same correctness gates and make its changed measurement boundary explicit rather than comparing a new, narrower stopwatch with this result.

Use the local result to strengthen the larger design

The companion supplies an implemented invariant that the earlier architecture can preserve as it grows: a source identity has one payload hash, a source can have one receipt per output version, and each receipt's effect and lineage commit together.

Introducing a broker should not redefine those identities. Introducing cloud artifacts should preserve the canonical input relationship. Changing the database should retain the atomic acceptance boundary or explicitly redesign the guarantee. The local tests become a contract to port, not evidence that every replacement has already passed.

The original reference project asks whether a result can explain its input and transformation. The executed companion makes that question concrete for a bounded integer workload. It can show what was accepted again without being applied again, what became a new derived version, and what was rejected because its claimed source identity no longer matched its payload.

That is a stronger foundation for a later distributed implementation than a throughput number alone. The next optimization must preserve the explanation, not merely make the aggregate appear sooner.

Historical versions and verification boundaries

These pins existed before the context date. Availability and static API checks do not establish a successful build or a production-safe patch level. Execution claims are limited to explicitly identified companion checks.

Component	Version	Available by	Source
OpenTelemetry	1.11.2	2025-03-04	Primary record
OpenTelemetry.Extensions.Hosting	1.11.2	2025-03-04	Primary record
OpenTelemetry.Exporter.OpenTelemetryProtocol	1.11.2	2025-03-04	Primary record
OpenTelemetry.Instrumentation.AspNetCore	1.11.1	2025-03-05	Primary record
Azure.Storage.Blobs	12.23.0	2024-11-12	Primary record
Azure.Messaging.ServiceBus	7.18.2	2024-10-08	Primary record
Microsoft.Data.SqlClient	5.2.2	2024-08-27	Primary record
.NET SDK	9.0.101	2024-12-03	Primary record
.NET runtime / ASP.NET Core shared framework	9.0.0	2024-12-03	Primary record
SQL Server	2022 / RTM 16.0.1000.6	2022-11-16	Primary record

Download the complete laboratory and execution records.