Crypto data normalization: what breaks on input
Raw exports from crypto sources almost never match in structure.
One source returns the amount as a number, another as a string, a third as a pair of "value and number of decimal places," while the timestamp arrives sometimes in seconds, sometimes in milliseconds, sometimes as a string with a timezone offset.
Different source response formats are not an error in themselves, but until they are brought to a common form, neither portfolio reporting nor limits are calculated correctly.
The second area of loss is duplicate transactions. The same trade comes from the venue’s trading interface, from public network data, and from a price aggregator, and in each case it has its own identifier that does not intersect with the neighboring one.
Reconciliation starts working only with a matching rule: transaction reference, output index, and wallet address with a time tolerance window.
The third area is discrepancies in trade accounting. For one source, the fee is included in the transaction amount; for another, it is listed as a separate line; a partially filled order turns into a dozen records instead of one.
The venue timestamp and network confirmation time diverge, and the calculation of cost basis using FIFO depends on transaction order. Crypto data normalization is needed precisely here: without it, identical trades produce different amounts when recalculated.
The result: data reconciles with itself only after a canonical schema, deduplication rules, and regular source reconciliation appear. Next, we break down how this is addressed.
What changes after implementation: effect in measurable terms
The effect of normalization is visible not in reports but in daily work: the pipeline stops depending on manual operations and specific people. Below are the values that change measurably, and why this happens.
Speed of connecting a new source
A new venue or aggregator such as CoinMarketCap is connected with a declarative description of field mapping: ticker, trade side, volume, timestamp. Format parsing for each source is written once—further connections take hours instead of weeks.
Share of automated reconciliations
Reconciliation of trades and balances between systems used to run on exports and manual comparison. Matching rules by trade and account keys cover the main part of the work: more than 90% of positions reconcile automatically, and only real discrepancies are handled manually.
Class of eliminated failures
Repeated message delivery, a connection drop in the middle of a packet, and different precision and time zones across sources produced duplicates and lost records in accounting.
Event-keyed recording and a processing journal eliminate this class of failures entirely, rather than individual incidents.
Unified accounting schema
One field dictionary, unified types, precision, and time representation instead of a set of tables for each source. Reports and analytics are assembled from a single representation, and a new slice is a query, not a separate export with manual reconciliation.
Source connection options: comparison of approaches
The source connection method is chosen based on stream volume and required data freshness.
The same asset comes from an exchange interface, from a network explorer, and from directories such as CoinGecko: if everything is pulled with one schema, gaps appear at peak times, while the pipeline sits idle during quiet hours.
| Option | When it fits | Limitations |
|---|---|---|
| Direct connection to the source | Trades, order books, and history in continuous mode | Request rate limits; a connection drop leaves a gap |
| Scheduled file exports | Historical archives, asset directories, retrospective reconciliation | One-interval delay; each source has its own format |
| Real-time notification ingestion | New blocks, transfers, balance changes | Requires an always-available receiver; missed events are backfilled manually |
| Manual on-demand reimport | Failure investigation, recovery of individual segments | Does not scale to a stream; requires an operator and duplicate control |
How to choose
While the market is calm, scheduled exports are enough—they provide full depth and do not hit limits. As soon as event-driven reaction is needed, notifications are added to them, while direct connection is left for directories and targeted queries.
Manual reimport works as insurance: it closes a missed segment after a failure but does not replace the stream.
The normalizer brings all four schemas to a unified record structure, so a source can be replaced or added without rewriting consumer logic.
How implementation is structured: stages of engineering work
We start not with code but with an audit of sources: a stable result is achieved only when we understand where the data comes from and how these sources differ from one another.
Exchanges, block explorers, and aggregators return different history depth, update frequency, and response structure, calling the same asset differently.
- Source audit. For each source, we record the response format, history depth, update frequency, request limits, and failure behavior—we get a coverage map before designing the schema.
- Discrepancy map. We consolidate differences into one document: how each source labels the instrument, in what scale it returns time and volumes, where duplicates, gaps, and breaks appear during block reorganizations.
- Field matching rules. We design the canonical schema and mapping directories: a unified asset identifier, one representation of time and precision, and explicit source priority when values conflict.
- Ingestion setup. We build the processing stream with retries, an idempotency key, and deduplication so that the same block or trade does not double the data during recalculation.
- Validation on historical slices. We run the rules on past periods: compare record counts and checksums, catch schema drift and cases where a source changed its response format.
- Handover and procedures. We deliver the schema, directories, discrepancy journal, and the procedure for connecting a new source so that expanding the source set does not require rebuilding the logic.
What we hand over: configurations, schemas, procedures
We consider the project delivered not at the moment of demonstration but when the on-call engineer can bring up the pipeline from scratch and handle an incident without contacting the authors. Therefore, a set of artifacts is handed over, each answering a specific support question.
- Ingestion configuration. Sources, polling intervals, request limits, exponential backoff retry rules—in files in your repository; edits go through review, not manual changes on the server.
- Field mapping schema. The table "source field → canonical field → transformation rule → behavior on missing value" covers all connected venues; a new source is added without changing code.
- Access policies. Roles and scopes, key lifetimes, rotation procedure, secret storage locations—the contractor's access is revoked with one operation.
- Failure procedure. A decision tree by failure class: source unavailable, data lag above threshold, price discrepancy between venues, duplicate trades. For each—what the on-call person does themselves and when they escalate.
- Support instructions. Step-by-step pipeline bring-up from scratch, updating the directory of venues and instruments, post-change validation, and troubleshooting typical errors and their causes.
- Monitoring and alerts. Data freshness metrics, share of unmatched records, trigger thresholds—the pipeline state is visible without manual checks.
Everything is in your repository and your infrastructure: we remain available for further development, but not as the only ones who can press the button.
Case study: discrepancies between an exchange and internal accounting
The client is an exchange operator with internal accounting on its own database. Discrepancies surfaced only during reconciliation: one asset came under three tickers, trade times were in different zones, and partial fills were merged manually.
By period close, hundreds of disputed transactions accumulated, and each was investigated by an analyst in a spreadsheet.
We started this normalization case with a directory of assets and trading pairs: alias matching, a unified format for time and amounts, and deduplication of redelivered trades.
We used CoinGecko for reference asset metadata—new listings stopped entering accounting with empty directories.
Metrics before and after
| Metric | Before | After |
|---|---|---|
| Period close time | 9 days | 1 business day |
| Share of automated reconciliations | 41% | 97% |
| Disputed transactions per month | 380 | 12 |
| Manual edits in the register | daily | exceptions only |
The remaining discrepancies are not pipeline errors but real gaps on the counterparty side: the system marks them and sends them to an investigation queue with full context.
The directory is versioned, so matching rules can change without fear—rolling back to the previous version and recalculating history takes minutes.
How does streaming normalization differ from batch normalization?
The difference shows at the moment data becomes suitable for a decision. Streaming processing normalizes the event on input: we bring the address, amount, and asset to a unified form immediately, while the transaction has not yet entered a block.
Batch processing accumulates raw records and recalculates them regularly—on a schedule or when volume accumulates.
Instant reaction is needed where the decision depends on the latest event: monitoring wallet limits and balances, triggering risk rules when a suspicious route appears, fee recalculation.
Here the aggregation window is measured in seconds, and the delay between event arrival and state update is the cost of an error.
A batch pipeline is justified where completeness matters, not speed: balance reconciliation, reporting, historical analysis for a period, preparing marts for analytics.
The aggregation window here is hours and days, but the calculation can be replayed entirely after changing token and contract matching rules.
Duplicate-free reprocessing relies on a deterministic record key—transaction hash plus output number—and idempotent insertion. Replaying a window then updates existing rows instead of doubling volumes and breaking reconciliation. Versioning the normalized record allows comparing the result before and after a rule change.
In practice, the pipelines do not compete but complement each other: streaming provides reaction, batch provides numeric consistency.
Both write through a single normalization layer and a shared deduplication layer, otherwise the same transaction gets a different appearance in two marts.
Technical concerns: sources, failures, limitations
Concerns about normalization are usually the same: some sources have no public interface description, response formats change without warning, and parsing certain transactions runs into processing constraints. Below are answers to inconvenient questions that most often arise during acceptance.
The source has no interface description—how do you build the parser?
We derive the contract by observation: we run polling in a test environment, record the actual fields, types, and update frequency, and then describe them in the source registry.
After that, canary polling works—if the field set diverges from the reference, an alert fires before the data reaches the mart.
The source changed its format—will old data break?
The schema is versioned: previous records remain under their old contract, new ones are written under the new one, and field matching is switched with a flag.
We run recalculation of past periods separately and compare checksums before and after—a format change does not become a history break.
The source is unavailable for several hours—will data be lost?
The polling cursor advances only on confirmed data, so after availability is restored we fetch the missed interval.
For sources that do not return history, we maintain two independent acquisition routes and daily reconciliation: a discrepancy above threshold goes to a manual review queue.
What about transactions that could not be parsed?
Silent loss is excluded: unrecognized records are not discarded but stored in a separate layer with a reason—unknown event type, address mismatch, duplicate by hash—and the coverage share is visible in the report.
Disputed accounting cases—transfers between own wallets, refunds, fees—are tagged by a rule and go as a separate line, rather than dissolving into the overall balance.
Let's discuss the task: what inputs are needed for an estimate
Estimating normalization of data from crypto sources starts not with numbers but with inputs: without them, scope and work composition are determined at random. Below is the minimum that removes uncertainty and allows labor costs to be calculated from facts, not assumptions.
- List of sources: exchanges, wallets, blockchain explorers, brokerage exports—for each, the access method and request rate limits are needed.
- Formats and volumes: in what form data arrives, how many records per day, and to what depth historical backfill is required.
- Target model: where we store it—own database, analytical warehouse, files; which entities and relationships should be produced on output.
- Matching rules: directories of tickers, exchanges, and trading pairs; how to interpret internal transfers, withdrawals, staking, and fees.
- Reporting requirements: which slices and exports are needed, at what frequency, and in what form—for accounting, audit, or management.
- Update mode and failures: batch on a schedule or streaming, acceptable delay, procedure for rolling back a partial load.
- Accesses and environments: test and production, keys, secret storage, who owns the data.
Based on these inputs, we return with an estimate and work plan: stages, source connection order, data quality control points, and the composition of documentation to be handed over. Describe the task—we will consolidate the inputs into one document and propose the next step.