Daily reporting often begins with queries against several databases and a join across their results. The real difficulty lies in defining the day, cutoff, late transactions, schema differences, historical corrections, and whether numbers from separate sources represent the same moment.
Define “daily” and “complete”
A business day may follow headquarters, branch, or transaction-system time. A 00:05 transaction might belong to the prior day under a settlement cutoff. Define business_date, IANA timezone, cutoff, and daylight-saving behavior where applicable. Keep event time and ingestion time because they reveal different delays.
Reports also need a completeness rule. Must every branch arrive? How long will you wait for an offline source? Can a report publish provisionally? Status such as complete, partial, and revised exposes limitations instead of presenting partial figures as final.
Align meaning before aligning columns
| Concept | Common differences | Contract decision |
|---|---|---|
| Revenue | Order, invoice, payment, recognition | Event and status counted |
| Customer | Contact, account, legal entity | Entity and identity mapping |
| Amount | Gross, net, tax-inclusive | Formula and rounding |
| Time | Local time, UTC, posting date | Timezone and business date |
| Cancellation | Delete, status, reversing entry | Restatement rule |
Create a metric catalog with business owner, formula, grain, source, filters, and examples. Do not merely align columns named total. Use precision decimals for money and an explicit currency-conversion decision with a defined rate source and time.
Full snapshot or incremental extraction?
A full snapshot is simpler and rebuildable, but its cost grows with data and may burden an operational database. Incremental extraction is more efficient but needs a reliable watermark, update and delete handling, and replay. An updated_at timestamp is safe only if every change updates it and its resolution is sufficient.
- Use stable keys and one watermark per source rather than one global watermark.
- Advance the high-water mark only after the source batch is persisted successfully.
- Use an overlap window and deduplication where timestamps can arrive late.
- Define how deletes, voids, and historical corrections are detected.
- Use tested indexes and pagination so reporting does not disrupt transactions.
Multiple databases do not provide an automatic global snapshot
Read transactions on databases A and B can each be locally consistent while occurring at different moments. Data in the first source can change while the second query runs. Distributed transactions are often unavailable or inappropriate for reporting. The design must therefore state its consistency model.
Options include event-time cutoff, source-provided batch IDs, snapshot markers, or staging that waits for every source for a business date. Reconcile critical figures with source control totals. When one source is late, retain partial status and issue a revision instead of silently changing figures.
Make data quality part of operations
- Extract to staging.
Retain source, batch ID, extraction time, schema version, and checksum or control count.
- Validate.
Check types, nulls, duplicates, references, ranges, and volume changes needing review.
- Normalize.
Map identity, units, timezone, currency, and status under the contract.
- Aggregate.
Build metrics at the defined grain rather than from unowned ad hoc joins.
- Publish and reconcile.
Expose freshness and completeness, compare control totals, and manage revisions.
Monitor duration per source, watermark age, record count, schema errors, duplicates, and control-total variance. Retain raw or staged data according to policy so results remain explainable while restricting PII and access. A report that cannot be reproduced is difficult to trust when figures differ.
Building a reporting pipeline in NitroNet
A broken connection, query timeout, or full storage needs retry or operational response. Out-of-range values, unmatched identities, and control-total differences need data-quality review. Retrying the second category unchanged only grows the backlog. Keep a traceable quarantine, assign each rule an owner, and decide whether publication waits, remains partial, or is revised after correction.
A NitroNet Schedule workflow can trigger parameterized Read DB through agents for PostgreSQL, MySQL, SQL Server, MongoDB, or SAP HANA when the connection and schema are verified. Mapping, Convert Type, Clean, Merge, Aggregate, and Write or Upsert DB can build staging and outputs. Do not treat cross-source reads as atomic; persist batch state and completeness explicitly.
Start with one metric and two sources, validate against the process owner's report, then test a late source, schema change, duplicate batch, and rerun of an old date. Read about CRM–billing source of truth and self-hosted operating responsibility. NitroNet helps orchestrate the pipeline; metric definitions and reporting truth remain business-owned.