Audit Manual Entry Integrity Metrics

Business Context

You’re on the data engineering team at a fintech payments processor that settles card and bank-transfer transactions for thousands of merchants. Operations agents can create manual_entries to correct partner file issues (e.g., missing references, late-arriving settlement files). Because these entries can impact reconciliation and regulatory reporting, the risk team runs a daily audit to detect data integrity problems.

In the last week, the audit dashboard showed a spike in duplicate partner references and broken links to the canonical transactions table. Your task is to generate a daily integrity report for the last 7 days of data, anchored to the most recent manual_entries.created_at date in the dataset (not CURRENT_DATE, to keep results deterministic for backfills).

Task

Write a SQL query that produces daily integrity metrics for manual entries in the last 7 days.

Requirements

1. Define the reporting window as dates >= (max(created_at)::date - 6 days) where max(created_at) is taken from manual_entries.
2. For each entry_date (UTC date of manual_entries.created_at) return:
   • total_entries: number of manual entries created that day
   • duplicate_partner_refs: number of entries whose (partner_id, partner_ref) appears more than once within the 7-day window
   • conflicting_amount_or_currency: number of entries that are duplicates and where the duplicate group has more than one distinct amount or more than one distinct currency
   • missing_linked_txn: number of entries where linked_transaction_id is not null but does not exist in transactions
3. Output must be ordered by entry_date ascending.

Table Definitions

manual_entries

transactions