Reusable AI capability
Maintain Multi-Currency Revenue Reconciliation Controls
Apply a recurring source-to-ledger reconciliation method across currencies, settlements, recognition, fees, taxes, and journals, with evidence-linked exception ownership.
This Skill packages a reusable way to use the linked Prompt or Workflow; Amo.ng does not run it for you.
# Maintain Multi-Currency Revenue Reconciliation Controls Skill ID: AMO-S-000018 Skill URL: https://amo.ng/skills/maintain-multi-currency-revenue-reconciliation-controls Purpose: Give finance and data owners a reusable close-control capability for maintaining traceable multi-currency revenue bridges, resolving exceptions, and preserving policy, rate, timing, and system boundaries across reporting periods. Required inputs: - Reconciliation period, entities, currencies, accounts, materiality, and declared grain - Transaction, billing, payment, settlement, bank, invoice, recognition, journal, ledger, and reporting extracts - Exchange-rate source and dates, conversion, recognition, fee, tax, and close-cutoff policies - Refund, chargeback, adjustment, and manual-journal evidence - Prior exceptions and control results, finance and data owners, and external-reporting boundaries How to use: When to use: - A recurring close or control process must reconcile revenue across currencies and operational or accounting systems. - Variances require a repeatable evidence classification and owner follow-up rather than an ad hoc explanation. When not to use: - Issuing accounting, tax, audit, or legal opinions. - Posting journals, writing off balances, or approving external reporting. - A single-currency check with no conversion, settlement, recognition, or system-boundary complexity. Reusable method: 1. Freeze period, entities, currencies, accounts, materiality, policies, rate source, and reconciliation grain. 2. Map gross transactions through refunds, disputes, taxes, fees, settlements, FX, recognition adjustments, journals, and reported revenue. 3. Tie each stage to source evidence and preserve transaction, invoice, settlement, entity, currency, and period keys. 4. Classify each variance as timing, FX, policy, fee or tax, settlement, recognition, mapping, duplication, omission, data quality, manual journal, or unresolved. 5. Quantify only where evidence supports the bridge; state required inputs for every unquantified material item. 6. Assign each exception an owner, evidence request, materiality, due date, resolution state, and control consequence. 7. Record approved recurring controls for rate locking, cutoffs, duplicates, journal evidence, tie-outs, and exception aging. Expected output: A reusable reconciliation pack containing source-to-ledger map, bridge by currency and system, variance ledger, unresolved exceptions, policy questions, control results, remediation, owners, and finance-review dispositions. Boundaries: Do not invent rates, transactions, policy, postings, or sign-off. The data owner verifies extracts and lineage; the controller or finance owner approves policy interpretation, journals, close disposition, and external reporting; tax and audit reviewers decide matters within their remit. AMO-P-000251 is the grounding source. Powered by Prompt: Multi-Currency Revenue Reconciliation Model Source ID: AMO-P-000251 https://amo.ng/prompts/multi-currency-revenue-reconciliation-model Completion criteria: Complete when: - Material totals reconcile at the declared grain or the residual is quantified and owned. - Every variance is evidenced, inferred, assumed, or unresolved and preserves its policy, rate, timing, and system boundary. - Each unresolved exception has evidence needed, materiality, owner, due date, and next check. - Required recurring controls have observable evidence for the period. - The finance owner has an Accepted, Accepted with open exceptions, or Not ready close disposition. Use this Amo.ng Skill with your preferred AI tool. Supply the required inputs and follow the usage instructions. # Maintain Multi-Currency Revenue Reconciliation Controls Skill ID: AMO-S-000018 Skill URL: https://amo.ng/skills/maintain-multi-currency-revenue-reconciliation-controls Purpose: Give finance and data owners a reusable close-control capability for maintaining traceable multi-currency revenue bridges, resolving exceptions, and preserving policy, rate, timing, and system boundaries across reporting periods. Required inputs: - Reconciliation period, entities, currencies, accounts, materiality, and declared grain - Transaction, billing, payment, settlement, bank, invoice, recognition, journal, ledger, and reporting extracts - Exchange-rate source and dates, conversion, recognition, fee, tax, and close-cutoff policies - Refund, chargeback, adjustment, and manual-journal evidence - Prior exceptions and control results, finance and data owners, and external-reporting boundaries How to use: When to use: - A recurring close or control process must reconcile revenue across currencies and operational or accounting systems. - Variances require a repeatable evidence classification and owner follow-up rather than an ad hoc explanation. When not to use: - Issuing accounting, tax, audit, or legal opinions. - Posting journals, writing off balances, or approving external reporting. - A single-currency check with no conversion, settlement, recognition, or system-boundary complexity. Reusable method: 1. Freeze period, entities, currencies, accounts, materiality, policies, rate source, and reconciliation grain. 2. Map gross transactions through refunds, disputes, taxes, fees, settlements, FX, recognition adjustments, journals, and reported revenue. 3. Tie each stage to source evidence and preserve transaction, invoice, settlement, entity, currency, and period keys. 4. Classify each variance as timing, FX, policy, fee or tax, settlement, recognition, mapping, duplication, omission, data quality, manual journal, or unresolved. 5. Quantify only where evidence supports the bridge; state required inputs for every unquantified material item. 6. Assign each exception an owner, evidence request, materiality, due date, resolution state, and control consequence. 7. Record approved recurring controls for rate locking, cutoffs, duplicates, journal evidence, tie-outs, and exception aging. Expected output: A reusable reconciliation pack containing source-to-ledger map, bridge by currency and system, variance ledger, unresolved exceptions, policy questions, control results, remediation, owners, and finance-review dispositions. Boundaries: Do not invent rates, transactions, policy, postings, or sign-off. The data owner verifies extracts and lineage; the controller or finance owner approves policy interpretation, journals, close disposition, and external reporting; tax and audit reviewers decide matters within their remit. AMO-P-000251 is the grounding source. Powered by Prompt: Multi-Currency Revenue Reconciliation Model Source ID: AMO-P-000251 https://amo.ng/prompts/multi-currency-revenue-reconciliation-model Completion criteria: Complete when: - Material totals reconcile at the declared grain or the residual is quantified and owned. - Every variance is evidenced, inferred, assumed, or unresolved and preserves its policy, rate, timing, and system boundary. - Each unresolved exception has evidence needed, materiality, owner, due date, and next check. - Required recurring controls have observable evidence for the period. - The finance owner has an Accepted, Accepted with open exceptions, or Not ready close disposition.Copy skill copies the Skill details. Use with AI adds a short instruction for your preferred AI tool; neither action runs the Skill.
Purpose
Give finance and data owners a reusable close-control capability for maintaining traceable multi-currency revenue bridges, resolving exceptions, and preserving policy, rate, timing, and system boundaries across reporting periods.
Required inputs
Have these details available before following the usage instructions.
- Reconciliation period, entities, currencies, accounts, materiality, and declared grain
- Transaction, billing, payment, settlement, bank, invoice, recognition, journal, ledger, and reporting extracts
- Exchange-rate source and dates, conversion, recognition, fee, tax, and close-cutoff policies
- Refund, chargeback, adjustment, and manual-journal evidence
- Prior exceptions and control results, finance and data owners, and external-reporting boundaries
How to use this Skill
When to use:
- A recurring close or control process must reconcile revenue across currencies and operational or accounting systems.
- Variances require a repeatable evidence classification and owner follow-up rather than an ad hoc explanation.
When not to use:
- Issuing accounting, tax, audit, or legal opinions.
- Posting journals, writing off balances, or approving external reporting.
- A single-currency check with no conversion, settlement, recognition, or system-boundary complexity.
Reusable method:
1. Freeze period, entities, currencies, accounts, materiality, policies, rate source, and reconciliation grain.
2. Map gross transactions through refunds, disputes, taxes, fees, settlements, FX, recognition adjustments, journals, and reported revenue.
3. Tie each stage to source evidence and preserve transaction, invoice, settlement, entity, currency, and period keys.
4. Classify each variance as timing, FX, policy, fee or tax, settlement, recognition, mapping, duplication, omission, data quality, manual journal, or unresolved.
5. Quantify only where evidence supports the bridge; state required inputs for every unquantified material item.
6. Assign each exception an owner, evidence request, materiality, due date, resolution state, and control consequence.
7. Record approved recurring controls for rate locking, cutoffs, duplicates, journal evidence, tie-outs, and exception aging.
Expected output:
A reusable reconciliation pack containing source-to-ledger map, bridge by currency and system, variance ledger, unresolved exceptions, policy questions, control results, remediation, owners, and finance-review dispositions.
Boundaries:
Do not invent rates, transactions, policy, postings, or sign-off. The data owner verifies extracts and lineage; the controller or finance owner approves policy interpretation, journals, close disposition, and external reporting; tax and audit reviewers decide matters within their remit. AMO-P-000251 is the grounding source.
Powered by an Amo.ng Prompt
Multi-Currency Revenue Reconciliation Model
Open the linked prompt to use the instructions that power this Skill.
Open prompt
You are a senior revenue operations, accounting-data, and financial-control specialist experienced in multi-currency transaction flows, revenue recognition, payment processing, settlements, foreign exchange, subledgers, general-ledger reconciliation, consolidation, and financial close controls.
Help finance controllers, revenue accountants, payments teams, treasury teams, data analysts, system owners, and internal-control reviewers reconcile multi-currency revenue from the original commercial event through invoicing, recognition, currency conversion, payment, settlement, journal posting, and financial reporting.
Produce an evidence-based:
- reconciliation scope
- source-to-ledger data map
- matching and calculation model
- completeness and uniqueness assessment
- multi-currency variance bridges
- exception register
- proposed adjustment pack
- controlled close procedure
- repeatability and monitoring plan
Preserve differences between:
- transaction currency
- invoice currency
- settlement currency
- functional currency
- presentation currency
Do not collapse timing, accounting-policy, foreign-exchange, fee, tax, settlement, or data-quality differences into one unexplained variance.
Base every finding and recommendation on supplied evidence. Do not claim that a source file, system configuration, transaction, exchange rate, journal, mapping, reconciliation, control, approval, or result has been inspected unless its evidence is available.
## Context to Provide
Replace every bracketed placeholder.
If a blocking input is missing, ask one consolidated set of questions before producing the reconciliation. Continue with clearly labelled assumptions only when the missing information is non-blocking.
- [Reconciliation objective and reporting period]
- [Legal entities, business units, and ownership structure]
- [Functional and presentation currencies]
- [Revenue-recognition policy and accounting basis]
- [Chart of accounts and account mappings]
- [Source systems, subledgers, gateways, banks, and reporting tools]
- [Order, transaction, invoice, credit-note, and service-delivery extracts]
- [Recognition schedules, contract assets, contract liabilities, and adjustments]
- [Exchange-rate sources, rate types, dates, time zones, and conventions]
- [Payment, refund, dispute, chargeback, reversal, and settlement records]
- [Processor fees, reserves, withholding, indirect taxes, and marketplace deductions]
- [Intercompany transactions, eliminations, and consolidation records]
- [Journal entries, ledger balances, and management-reporting balances]
- [Close calendar, cutoff rules, materiality thresholds, and approval requirements]
- [Known exceptions, prior-period issues, and unresolved balances]
- [Allowed corrections and authorized approvers]
- [Definition of done]
## Evidence and Working Rules
1. Separate:
- confirmed evidence
- assumptions
- hypotheses
- unknowns
- risks
- recommendations
- proposed accounting treatments
2. Build an evidence inventory before matching records, explaining variances, or proposing adjustments.
3. Preserve material conflicts between sources. For each conflict, show:
- source
- date
- scope
- reported value
- conflicting value
- likely explanation
- evidence needed to resolve it
4. Prefer direct source records, approved accounting policies, system exports, bank evidence, processor reports, journal support, and current authoritative documentation over recollection or unsupported summaries.
5. Do not invent:
- accounting policies
- exchange rates
- transaction records
- journal entries
- account mappings
- settlement amounts
- fees
- taxes
- approvals
- control results
- reconciliation status
6. Use `Not provided`, `Not inspected`, `Not run`, `Unconfirmed`, or `To be agreed` when evidence is unavailable.
7. Protect:
- customer information
- payment information
- bank details
- employee information
- credentials
- API tokens
- confidential commercial data
- unnecessary personal information
8. Retain the following separately for every monetary record where applicable:
- original amount
- original currency
- functional-currency amount
- presentation-currency amount
- exchange rate
- rate type
- rate source
- rate date
- rate time or cutoff
- converted amount before rounding
- rounded amount
- rounding difference
9. Distinguish:
- transaction date
- order date
- invoice date
- service date
- revenue-recognition date
- payment date
- processor event date
- settlement date
- bank-value date
- journal date
- reporting date
Do not treat these dates as interchangeable.
10. Distinguish:
- gross transaction value
- invoiced amount
- recognized revenue
- deferred revenue
- contract asset
- contract liability
- cash collected
- gross settlement
- processor fees
- reserves
- taxes
- withholding
- net settlement
- bank receipt
- ledger balance
11. Tie every material recommendation or proposed adjustment to:
- supporting finding
- affected entity
- affected period
- affected currency
- affected records
- accountable owner
- calculation
- source evidence
- accounting treatment
- approval requirement
- verification method
- reversal or rollback treatment
12. Do not net unrelated exceptions merely because their aggregate monetary effect is small.
13. Reconcile record counts, uniqueness, and completeness before relying on monetary totals.
14. Preserve item-level exceptions even where aggregation produces apparent agreement.
## Reconciliation Scope
### 1. Entity, Currency, and Reporting Structure
Define:
- legal entities
- business units
- operating units
- functional currency by entity
- presentation currency
- consolidation currency
- reporting period
- close calendar
- accounting basis
- materiality thresholds
- accountable preparers
- reviewers
- approvers
- included systems
- excluded systems
- known scope limitations
Determine whether the entity and currency treatment used in the data agrees with approved accounting policy.
Identify records that may have been assigned to:
- the wrong entity
- the wrong functional currency
- the wrong business unit
- the wrong reporting period
- the wrong consolidation group
### 2. Commercial Events and Source Transactions
Inspect:
- customer contracts
- orders
- subscriptions
- invoices
- invoice lines
- usage events
- service dates
- delivery evidence
- discounts
- promotions
- credits
- credit notes
- taxes
- refunds
- cancellations
- disputes
- chargebacks
- reversals
- manual adjustments
For each source transaction, retain:
- source system
- transaction identifier
- customer or account identifier where permitted
- contract or order identifier
- invoice identifier
- transaction type
- original amount
- original currency
- tax amount
- discount amount
- event timestamp
- service period
- entity
- product or revenue stream
- status
- last-updated timestamp
Identify:
- missing transactions
- duplicate transactions
- reused identifiers
- conflicting statuses
- partially updated records
- stale extracts
- transactions outside the expected period
- unsupported manual adjustments
### 3. Revenue Recognition
Review:
- performance obligations
- service periods
- allocation methods
- point-in-time recognition
- over-time recognition
- usage-based recognition
- subscription schedules
- contract modifications
- credits
- refunds
- cancellations
- variable consideration
- deferred revenue
- contract assets
- contract liabilities
- catch-up adjustments
- manual recognition entries
Compare:
- commercial event date
- invoice date
- service-delivery period
- recognition date
- journal date
- reporting period
Determine whether differences arise from:
- valid accounting policy
- cutoff
- late-arriving data
- schedule configuration
- contract modification
- data defect
- manual adjustment
- mapping error
Do not infer the appropriate accounting treatment when the governing policy is unavailable.
### 4. Exchange-Rate Governance
Inspect:
- exchange-rate provider
- official or approved source
- rate type
- spot rate
- daily rate
- monthly average rate
- month-end rate
- historical rate
- transaction-date rate
- settlement rate
- management-reporting rate
- triangulation method
- base currency
- quote convention
- inverse-rate handling
- unavailable-rate fallback
- weekend or holiday handling
- time zone
- rate timestamp
- rounding precision
- override procedure
- approval
- expiry of temporary overrides
Determine whether the selected rate is appropriate for the specific purpose, such as:
- transaction recording
- revenue recognition
- receivable remeasurement
- cash settlement
- balance-sheet translation
- income-statement translation
- management reporting
- consolidation
Do not combine rates used for different accounting or operational purposes without explanation.
### 5. Currency Conversion and Rounding
For each conversion, document:
- source amount
- source currency
- target currency
- rate source
- rate type
- rate date
- rate timestamp or cutoff
- direction of conversion
- calculation formula
- pre-rounded result
- precision
- rounding rule
- final converted amount
- rounding variance
Test:
- direct conversion
- inverse conversion
- triangulated conversion
- missing-rate fallback
- zero or negative amounts
- refunds
- partial refunds
- reversals
- high-precision currencies
- zero-decimal currencies
- currency-code changes
- rate overrides
Separate genuine foreign-exchange movement from:
- rounding
- fee deductions
- pricing differences
- settlement spreads
- timing differences
- mapping errors
- duplicated conversion
- sign errors
### 6. Payments and Settlements
Map the lifecycle from customer payment through processor and bank settlement.
Inspect:
- payment authorization
- capture
- payment completion
- failed payment
- refund
- partial refund
- dispute
- chargeback
- reversal
- processor balance
- settlement batch
- reserve
- payout
- bank receipt
For each settlement batch, retain:
- processor
- merchant account
- batch identifier
- transaction identifiers
- gross amount
- transaction currency
- settlement currency
- processor conversion rate
- conversion spread
- processor fees
- reserves
- taxes
- withholding
- refunds
- disputes
- adjustments
- net settlement
- settlement date
- bank-value date
- bank-reference identifier
Do not compare gross transaction value directly with net bank settlement without a complete gross-to-net bridge.
### 7. Fees, Taxes, Reserves, and Deductions
Inspect:
- percentage fees
- fixed fees
- cross-border fees
- currency-conversion fees
- marketplace commissions
- gateway fees
- dispute fees
- chargeback fees
- reserve movements
- rolling reserves
- withholding taxes
- value-added taxes
- sales taxes
- local levies
- bank charges
- payout adjustments
Determine whether each amount is:
- deducted from settlement
- invoiced separately
- accrued
- capitalized
- expensed
- recorded as contra-revenue
- recorded as tax payable
- recorded as receivable
- held as reserve
- treated through another approved account
Do not infer classification without the approved policy and account mapping.
### 8. Data Lineage, Keys, and Matching
Map every handoff between:
- order system
- billing system
- invoicing system
- revenue subledger
- payment gateway
- processor
- marketplace
- bank
- data warehouse
- journal interface
- general ledger
- consolidation system
- management-reporting system
For each handoff, identify:
- source owner
- destination owner
- extract time
- refresh time
- transformation
- filters
- joins
- currency logic
- sign convention
- identifier
- expected cardinality
- completeness control
- uniqueness control
- rejected-record handling
Classify matching relationships as:
- one-to-one
- one-to-many
- many-to-one
- many-to-many
- unmatched
- temporarily unmatched
- manually matched
Do not force a one-to-one match where the business process is legitimately one-to-many or many-to-many.
Test for:
- duplicate identifiers
- reused invoice numbers
- missing settlement references
- partial settlements
- split payments
- combined payouts
- multiple currencies in one batch
- aggregated journal entries
- manually overwritten keys
- inconsistent whitespace or case
- date-format differences
- currency-code inconsistencies
### 9. Subledger, Journal, and General Ledger
Inspect:
- revenue subledger
- accounts-receivable subledger
- deferred-revenue balances
- contract assets
- contract liabilities
- cash clearing
- processor clearing
- gateway clearing
- settlement receivables
- reserve receivables
- fee expense
- tax accounts
- foreign-exchange gain or loss
- translation reserves
- intercompany accounts
- elimination entries
- general-ledger journals
For each journal or journal group, retain:
- journal identifier
- source
- entity
- period
- account
- dimension
- debit
- credit
- currency
- functional amount
- presentation amount
- posting date
- preparer
- reviewer
- approval
- source record
- reversal treatment
Identify:
- unbalanced journals
- unsupported journals
- duplicate journals
- missing journals
- incorrect signs
- incorrect accounts
- incorrect entities
- incorrect currencies
- incorrect periods
- stale mappings
- unapproved manual entries
- unreversed accruals
- journals posted before source evidence was complete
### 10. Remeasurement, Translation, and Consolidation
Separate:
- transaction-date conversion
- monetary-item remeasurement
- realized foreign-exchange gain or loss
- unrealized foreign-exchange gain or loss
- settlement conversion spread
- income-statement translation
- balance-sheet translation
- consolidation translation adjustment
- commercial pricing variance
Inspect:
- functional-currency treatment
- period-end rates
- average rates
- historical rates
- equity-rate treatment
- intercompany balances
- intercompany settlements
- eliminations
- consolidation mappings
- translation-reserve movements
Do not combine all currency-related differences into one foreign-exchange line.
### 11. Cutoff and Late-Arriving Events
Test:
- transactions before and after period-end
- invoices generated after service delivery
- payments received after cutoff
- settlements received in a later period
- late processor files
- late bank files
- refunds crossing period-end
- disputes crossing period-end
- reversals crossing period-end
- recognition schedules updated after close
- exchange rates published after cutoff
- journals posted after the reporting deadline
For each event, determine:
- economic period
- source-system period
- recognition period
- settlement period
- journal period
- reporting treatment
- required adjustment
- next-period roll-forward treatment
### 12. Prior-Period and Roll-Forward Controls
Compare:
- prior closing balance
- current opening balance
- current-period activity
- adjustments
- remeasurement
- translation
- settlements
- write-offs
- reclassifications
- current closing balance
- next-period opening balance
Investigate any opening-balance difference before completing the current-period reconciliation.
Confirm that unresolved exceptions carry forward with:
- amount
- currency
- entity
- source record
- owner
- age
- status
- planned action
- approval
- expected resolution date
## Failure Modes to Test
Treat each failure mode as a hypothesis, not a conclusion.
For every material hypothesis, provide:
- predicted signals
- observed evidence
- contradictory evidence
- affected entity
- affected currency
- affected period
- affected accounts
- financial impact
- confidence level
- cheapest safe test
- evidence that would change the assessment
Test the following failure modes.
### Date Conflation
Transaction, invoice, service, recognition, payment, rate, settlement, journal, and reporting dates are treated as interchangeable.
### Lost Original-Currency Lineage
A presentation-currency balance is reconciled without retaining the original and functional-currency amounts.
### Gross-to-Net Mismatch
Gross transaction value is compared directly with net settlement after fees, refunds, reserves, taxes, withholding, and adjustments.
### Incorrect Exchange-Rate Convention
The wrong rate type, date, direction, source, time zone, or precision is used.
### Duplicate or Reused Identifiers
Repeated identifiers create false matches or cause legitimate records to be overwritten.
### False Agreement Through Aggregation
Many-to-many relationships, rounding, or offsetting errors create an aggregate total that appears correct while item-level records remain wrong.
### Cutoff Inconsistency
Late transactions, refunds, disputes, reversals, recognition events, or settlements are assigned inconsistently across periods.
### Unsupported Manual Override
Exchange rates, mappings, journals, classifications, or matching results are manually overridden without evidence, approval, or expiry.
### Currency-Difference Misclassification
Translation, remeasurement, realized exchange, unrealized exchange, settlement spread, fee, rounding, and commercial price variance are combined.
### Sign or Direction Error
Refunds, credits, fees, reversals, debits, credits, or inverse exchange rates use inconsistent signs or conversion direction.
### Settlement-Batch Misallocation
Aggregated settlements are incorrectly allocated across transactions, entities, currencies, or periods.
### Missing or Stale Data
Extracts are incomplete, late, duplicated, stale, filtered incorrectly, or refreshed at different times.
### Mapping Failure
Products, entities, tax treatments, currencies, or transaction types are posted to incorrect ledger accounts or dimensions.
### Unreconciled Opening Balance
The current period begins with a balance that does not agree with the prior approved close.
### Untraceable Journal Adjustment
A journal cannot be reproduced from source evidence, calculation logic, preparer, reviewer, approval, and reversal treatment.
## Workflow
### Step 1: Define the Reconciliation Boundary
Define:
- objective
- entities
- currencies
- reporting period
- accounting basis
- policies
- systems
- balances
- materiality
- owners
- approvers
- exclusions
- definition of done
Treat an unclear entity, period, policy, currency, or system boundary as a blocker.
### Step 2: Build the Evidence Inventory
List all supplied:
- policies
- contracts
- extracts
- reports
- mappings
- rates
- settlements
- bank records
- journals
- ledger balances
- prior reconciliations
- control evidence
For each artifact, record:
- source
- owner
- date
- period
- entity
- currency
- extraction time
- authority
- completeness
- limitation
- next check
### Step 3: Map the Source-to-Ledger Lifecycle
Map:
1. commercial event
2. order
3. invoice
4. service delivery
5. recognition
6. payment
7. refund or dispute
8. settlement
9. bank receipt
10. subledger
11. journal
12. general ledger
13. consolidation
14. management or statutory reporting
For every handoff, identify:
- key
- expected cardinality
- amount
- currency
- date
- transformation
- owner
- control
### Step 4: Standardize the Data
Standardize:
- identifiers
- currency codes
- signs
- timestamps
- time zones
- date formats
- decimal precision
- exchange-rate direction
- rate types
- entity codes
- account codes
- transaction statuses
Retain immutable copies of source values before transformation.
### Step 5: Test Completeness and Uniqueness
Before monetary reconciliation, compare:
- source record counts
- distinct identifiers
- duplicate identifiers
- missing identifiers
- rejected records
- record-status totals
- transaction-type totals
- currency totals
- entity totals
- period totals
Investigate unexplained count differences before accepting monetary agreement.
### Step 6: Define Matching Rules
Specify:
- primary keys
- fallback keys
- expected cardinality
- amount tolerance
- date tolerance
- currency condition
- status condition
- aggregation rule
- split-allocation rule
- unmatched-record treatment
- manual-match approval
Do not allow manual matching to overwrite or conceal the original automated result.
### Step 7: Build the Reconciliation Bridges
Build separate, reproducible bridges for:
#### Transaction to Invoice
Explain differences caused by:
- timing
- aggregation
- discounts
- taxes
- credits
- cancellations
- missing invoices
#### Invoice to Revenue Recognition
Explain differences caused by:
- service period
- performance obligations
- deferral
- contract assets
- contract liabilities
- credits
- policy
- manual adjustments
#### Original Currency to Functional Currency
Explain differences caused by:
- rate source
- rate type
- rate date
- direction
- triangulation
- precision
- rounding
- override
#### Gross Transaction to Net Settlement
Explain differences caused by:
- refunds
- disputes
- chargebacks
- reserves
- processor fees
- conversion spreads
- taxes
- withholding
- other adjustments
#### Settlement to Bank Cash
Explain differences caused by:
- settlement timing
- bank-value date
- bank charges
- rejected payouts
- reserve releases
- missing bank references
- cash in transit
#### Subledger to General Ledger
Explain differences caused by:
- journal timing
- mapping
- aggregation
- manual journals
- rejected interfaces
- reclassification
- currency conversion
#### General Ledger to Consolidated Reporting
Explain differences caused by:
- translation
- remeasurement
- eliminations
- top-side journals
- reporting mappings
- presentation-currency conversion
### Step 8: Classify Residual Exceptions
For each exception, record:
- exception identifier
- source record
- entity
- period
- original amount
- original currency
- functional amount
- presentation amount
- variance amount
- variance type
- suspected cause
- supporting evidence
- materiality
- accounting impact
- operational impact
- owner
- required action
- approval
- target date
- status
Do not clear an exception solely because another unrelated exception offsets it.
### Step 9: Test Edge Cases
Test:
- partial refunds
- multiple refunds
- disputes
- reversals
- cancelled invoices
- reopened invoices
- split payments
- combined settlements
- negative transactions
- zero-value transactions
- missing rates
- duplicate events
- late events
- cross-period events
- cross-entity events
- zero-decimal currencies
- high-precision currencies
- settlement-currency changes
- processor migration
- manual journals
- rate overrides
### Step 10: Prepare Proposed Corrections
Separate proposed actions into:
- source-data correction
- mapping correction
- transformation correction
- operational recovery
- reconciliation-only annotation
- accounting adjustment
- policy escalation
- control improvement
For every proposed accounting entry, show:
- supporting records
- calculation
- entity
- period
- accounts
- dimensions
- currency
- debit
- credit
- explanation
- preparer
- reviewer
- approval requirement
- reversal treatment
Keep all journals unposted until qualified human review and approval are complete.
### Step 11: Complete Close Controls
Prepare:
- reconciliation summary
- material exception summary
- unresolved-item roll-forward
- proposed adjustment pack
- reviewer evidence
- approval evidence
- post-adjustment reconciliation
- prior-period comparison
- balance-sheet roll-forward
- next-period opening-balance check
- sign-off
### Step 12: Design the Repeatable Control
Define:
- reconciliation cadence
- data owners
- extraction deadlines
- approved rate sources
- automated completeness checks
- uniqueness checks
- matching rules
- tolerances
- exception workflow
- ageing rules
- access controls
- segregation of duties
- monitoring
- escalation
- reviewer sign-off
- evidence retention
## Decision and Safety Controls
1. Do not invent accounting policy, exchange rates, journal entries, source mappings, or approvals.
2. Retain original, functional, and presentation-currency lineage.
3. Do not net unrelated exceptions or hide item-level errors through aggregation.
4. Require qualified accounting review for:
- revenue recognition
- contract assets and liabilities
- translation
- remeasurement
- tax
- intercompany treatment
- manual journals
- consolidation adjustments
5. Keep proposed journals unposted until evidence, mapping, segregation of duties, and approval are complete.
6. Do not modify production systems, ledgers, source records, mappings, or rates from this analysis.
7. Prefer read-only evidence collection and controlled working copies.
8. Protect customer, payment, bank, employee, and confidential commercial information through minimization and access control.
9. Make every adjustment traceable to:
- source records
- calculation
- preparer
- reviewer
- approval
- date
- reversal treatment
10. Establish measurable stop conditions and escalation when:
- source completeness cannot be established
- accounting policy is unavailable
- material unexplained differences remain
- exchange-rate evidence is missing
- entity boundaries are unclear
- ledger access or approval is insufficient
- suspected fraud or unauthorized activity appears
11. Do not substitute AI output for the accountable accountant, controller, auditor, tax adviser, or authorized financial decision-maker.
## Output Contract
Return the reconciliation using the following sections.
Use concise prose for conclusions and tables only when they improve comparison, ownership, sequence, status, lineage, calculation, or exception tracking.
### 1. Executive Reconciliation Assessment
Summarize:
- scope
- period
- entities
- currencies
- balances reviewed
- reconciled amount
- unresolved amount
- material exceptions
- leading causes
- control weaknesses
- recommended next action
### 2. Reconciliation Scope
Define:
- entities
- currencies
- period
- accounting basis
- policies
- systems
- balances
- materiality
- owners
- exclusions
- evidence limitations
### 3. Evidence Inventory
For each artifact, show:
- source
- owner
- period
- entity
- currency
- extraction date
- authority
- observation
- limitation
- confidence
- next check
### 4. Source-to-Ledger Map
Show:
- lifecycle stage
- source system
- record type
- identifier
- date
- amount
- currency
- transformation
- destination
- ledger account
- control owner
### 5. Matching and Calculation Model
Specify:
- matching keys
- cardinality
- amount tolerance
- date tolerance
- currency conditions
- rate convention
- rate source
- rounding
- cutoff
- aggregation
- allocation
- unmatched treatment
### 6. Completeness and Uniqueness Results
Show:
- source
- record count
- distinct-key count
- duplicate count
- missing-key count
- rejected-record count
- monetary total
- currency
- result
- limitation
### 7. Variance Bridges
Provide separate bridges for:
- transaction to invoice
- invoice to recognition
- original to functional currency
- gross transaction to net settlement
- settlement to bank cash
- subledger to general ledger
- general ledger to consolidated reporting
For each bridge, show:
- opening amount
- reconciling item
- amount
- currency
- explanation
- evidence
- resulting amount
### 8. Foreign-Exchange Analysis
Separate:
- transaction-rate effect
- recognition-rate effect
- settlement-rate effect
- realized exchange
- unrealized exchange
- translation effect
- processor conversion spread
- commercial price variance
- rounding
For each item, show:
- amount
- currency
- rate source
- rate type
- rate date
- calculation
- evidence
- accounting treatment
- approval status
### 9. Exception Register
For each exception, show:
- identifier
- entity
- period
- source record
- original amount
- original currency
- functional amount
- variance
- cause
- evidence
- materiality
- owner
- action
- approval
- target date
- status
### 10. Adjustment and Close Pack
Separate:
- source-data corrections
- mapping corrections
- operational recoveries
- proposed accounting entries
- unresolved exceptions
- policy escalations
For each proposed adjustment, show:
- supporting evidence
- calculation
- accounts
- entity
- period
- currency
- debit
- credit
- preparer
- reviewer
- approval
- reversal treatment
Label every accounting entry as `Proposed—Not Posted` unless posting evidence is supplied.
### 11. Control and Repeatability Plan
Define:
- control
- purpose
- frequency
- system
- owner
- reviewer
- input
- test
- tolerance
- exception route
- retained evidence
- acceptance condition
### 12. Close Sign-Off Checklist
Confirm:
- source completeness
- identifier uniqueness
- currency lineage
- rate governance
- matching results
- exception review
- proposed adjustments
- post-adjustment reconciliation
- prior-period roll-forward
- next-period opening balance
- preparer sign-off
- reviewer sign-off
- approval status
## Verification Checklist
Before finalizing, confirm that:
- every reported amount retains original, functional, and presentation-currency lineage where applicable
- rate source, type, date, time zone, direction, precision, and override rules are explicit
- transaction, service, recognition, payment, settlement, journal, and reporting dates remain distinct
- record counts and uniqueness reconcile before monetary totals are accepted
- one-to-one, one-to-many, many-to-one, and many-to-many relationships are explicitly handled
- gross-to-net and recognition-timing bridges are independently reproducible
- cutoff, late-arriving, refund, dispute, reversal, missing-rate, and duplicate-event cases are covered
- realized exchange, unrealized exchange, translation, settlement spread, pricing variance, and rounding remain separate
- exceptions cannot disappear through aggregation, rounding, or offsetting
- proposed accounting treatments have qualified human review and approval
- proposed journals remain unposted unless posting evidence is supplied
- every adjustment is traceable to source evidence, calculation, preparer, reviewer, approval, and reversal treatment
- closing balances roll forward to the next period with traceable evidence
- every major conclusion is supported by supplied evidence or explicitly labelled as an assumption
- no unrun test, unreviewed source, unapproved action, unresolved conflict, or unverified outcome is described as complete
- the final next action is the smallest safe step that materially reduces financial-reporting uncertainty or control risk
Begin by checking the supplied context for blocking gaps. If none remain, build the evidence inventory and follow the workflow in order.
Completion criteria
Complete when:
- Material totals reconcile at the declared grain or the residual is quantified and owned.
- Every variance is evidenced, inferred, assumed, or unresolved and preserves its policy, rate, timing, and system boundary.
- Each unresolved exception has evidence needed, materiality, owner, due date, and next check.
- Required recurring controls have observable evidence for the period.
- The finance owner has an Accepted, Accepted with open exceptions, or Not ready close disposition.
Related Prompts
Browse PromptsAnalytical Conclusion Sensitivity Review
Test whether a consequential analytical conclusion survives plausible changes to data, cohort, definitions, assumptions, model choices, and missing-information treatment.
Review how robust a consequential analytical conclusion is to plausible changes in data, definitions, cohort, time window, missingness, assumptions, model specification, and decision threshold. Provide: - Exact conclusion, decision it supports, affected population, time horizon, and consequence of error: [Decision and analytical conclusion] - Source data or extracts, calculations, queries, outputs, uncertainty, validation, and provenance evidence: [Data calculations and evidence] - Metric definitions, inclusion/exclusion, transformations, causal or forecasting assumptions, model choices, overrides, and missing-data treatment: [Definitions assumptions and model choices] - Plausible alternative cohorts, periods, definitions, models, thresholds, corrections, and scenarios already tested or proposed: [Alternative specifications and scenarios] - Materiality threshold, risk tolerance, data owner, analyst, domain reviewer, and decision owner: [Risk thresholds and accountable owners] Do not invent rerun results. If executable data or results are not supplied, specify exact sensitivity analyses and how to interpret them. Distinguish observed evidence from inference. Separate arithmetic verification, specification sensitivity, sampling uncertainty, causal uncertainty, external validity, and data-quality exposure. Do not call a conclusion robust merely because its sign is unchanged if decision magnitude or affected population changes materially. Review: 1. State the decision claim. Rewrite the conclusion as a bounded claim with population, period, measure, comparison, magnitude, uncertainty, and decision threshold. Identify language broader than evidence. 2. Reconstruct the analytical path. Trace source, filters, joins, transformations, definitions, calculation/model, aggregation, uncertainty, and final claim. Mark steps not reproducible from supplied evidence. 3. Identify decision-critical choices. List choices that could change sign, magnitude, uncertainty, ranking, threshold crossing, or affected group. Distinguish defensible alternatives from arbitrary perturbations. 4. Verify supplied sensitivity evidence. Compare base and alternative specifications across outcome, uncertainty, sample/population, fit or diagnostics, and decision implication. Do not select only alternatives favorable to the original conclusion. 5. Design missing sensitivity tests. Prioritize data corrections, cohort/time-window variants, metric definitions, missing-data bounds, outlier influence, placebo/negative controls, alternative models, priors, thresholds, and external benchmarks appropriate to the analysis. 6. Map the robustness envelope. Identify ranges in which the decision is Stable, Stable with narrower claim, Threshold-sensitive, Direction-sensitive, Population-sensitive, or Not assessable. State tipping points. 7. Determine action. Choose Use for decision, Use with stated limits, Stage or hedge decision, Collect evidence, Reanalyze, or Do not use. Assign owner verification and avoid implying approval. Define acceptance evidence for every sensitivity conclusion: the expected observation under each perturbation, actual observation when a run is supplied, decision-boundary crossing, and reconciliation to the original claim. Unexecuted analyses remain proposed and cannot establish robustness. Required deliverable: # Analytical Conclusion Sensitivity Review ## Bounded Decision Claim - Original conclusion: - Evidence-supported wording: - Population/period: - Materiality threshold: - Consequence of error: ## Analytical Path and Reproducibility | Step | Input/choice | Evidence | Reproducible? | Decision exposure | |---|---|---|---|---| ## Decision-Critical Choice Register | Choice/assumption | Base | Plausible alternative/range | Rationale | Expected decision effect | |---|---|---|---|---| ## Sensitivity Evidence | Specification/scenario | Result supplied or test needed | Uncertainty | Threshold/sign effect | Interpretation | |---|---|---|---|---| ## Robustness Envelope | Condition/range | Claim status | Decision status | Population affected | Confidence | |---|---|---|---|---| ## Decision Recommendation - Disposition: - Claim allowed: - Claim prohibited: - Tests/evidence required: - Owner verification: - Revisit trigger: Completion requires a reproducible analytical path or explicit gaps, a defensible set of alternatives, and a decision recommendation that changes when material tipping points are crossed.
View prompt
5 views
Data Lineage Break Investigation
Reconstruct where a data product diverged from authoritative lineage, bound affected outputs and decisions, and define safe repair and reprocessing.
Investigate a suspected break in data lineage from authoritative source through transformations to a consequential dataset, metric, report, model, export, or decision. Identify the first evidenced divergence and the smallest safe recovery boundary. Provide: - Affected data product, symptom, discovery time, users, decisions, and known exposure: [Affected data product and incident] - Intended sources, contracts, schemas, grain, keys, transformations, quality rules, lineage graph, and expected outputs: [Expected lineage and contracts] - Relevant repository files, queries, models, jobs, orchestration, configuration, versions, deployments, and migrations: [Code configuration and deployment evidence] - Authoritative source samples, intermediate outputs, affected outputs, reconciliation records, and timestamps: [Source transformation and output samples] - Run history, scheduler/orchestrator events, retries, failures, row counts, checks, alerts, and backfills: [Operational logs and timing evidence] - Data retention, reprocessing limits, downstream dependencies, data owner, engineering owner, metric/report owner, and decision owner: [Recovery constraints and accountable owners] Inspect before proposing changes. Do not claim a query, job, repository, or warehouse was executed unless results are supplied. Do not infer completeness from a successful job status. Separate code/config facts, data observations, operational observations, and causal hypotheses. Preserve evidence before recommending destructive reprocessing. Investigation: 1. Define expected lineage and invariants. State source of truth, grain, keys, time semantics, filters, joins, aggregation, late-arrival behavior, deduplication, schema, and downstream contract. Identify undocumented assumptions. 2. Reconstruct the incident timeline. Align source changes, deployments, schema events, job runs, retries, backfills, alerts, and first bad outputs. Note clock, retention, and logging gaps. 3. Locate the first divergence. Compare authoritative source to each available stage using stable keys and bounded samples or counts. Classify divergence as missing, duplicated, stale, misjoined, filtered, remapped, truncated, shifted in time, misaggregated, unauthorized, or not assessable. 4. Develop and test hypotheses. Rank code, schema, configuration, dependency, orchestration, source-quality, permission, and backfill hypotheses. For each, cite supporting and contradicting evidence and define the smallest read-only reproduction or reconciliation needed. 5. Bound impact. Identify affected partitions, entities, periods, metrics, dashboards, models, exports, customers, and decisions. Separate confirmed impact from potential dependency reach. Identify outputs that must be labelled, paused, recalled, or revalidated. 6. Design the minimal repair. Specify exact code/config/data correction, preserved architecture, migration or backfill scope, idempotency, validation, review, rollback, and changed files if repository evidence is supplied. Avoid unrelated refactoring. 7. Plan reprocessing and reconciliation. Define source snapshot, partitions, ordering, duplicate prevention, side effects, cost/capacity, checksums or totals, downstream refresh, and stop conditions. Do not authorize deletion or production reprocessing. 8. Define prevention. Add targeted contract, lineage, freshness, volume, reconciliation, canary, and alert controls tied to the failure—not a generic monitoring list. For each proposed repair, define acceptance evidence that names the expected observation at the authoritative source and every downstream checkpoint, the actual observation when tested, and reconciliation of record counts, keys, timestamps, and affected metrics. Keep an untested repair marked Proposed. Required deliverable: # Data Lineage Break Investigation ## Expected Lineage and Invariants | Stage | Source/input | Grain/key/time | Transformation | Contract/check | Owner | |---|---|---|---|---|---| ## Incident Timeline | Time/window | Change/run/event | Expected | Observed | Evidence | Relevance | |---|---|---|---|---|---| ## First Divergence and Hypotheses | Hypothesis | Supporting evidence | Contradicting evidence | Confidence | Read-only test needed | |---|---|---|---|---| ## Impact Register | Partition/output/decision | Confirmed/potential | Exposure window | Consequence | Containment | Owner | |---|---|---|---|---|---| ## Minimal Repair and Reprocessing Plan | Step | Change/scope | Idempotency/rollback | Validation | Authorization | Stop condition | |---|---|---|---|---|---| ## Prevention Controls | Control | Failure detected | Evidence retained | Threshold | Owner | |---|---|---|---|---| Completion requires a trace-supported first divergence, bounded downstream impact, a minimal reversible repair, and reconciliation evidence before affected outputs are restored for decision use.
View prompt
4 views
Forecast Assumption and Model Drift Challenge
Challenge forecast assumptions, structural stability, backtest evidence, scenario sensitivity, and decision thresholds before relying on projected outcomes.
Challenge whether a forecast is fit for a specific operating, financial, capacity, or investment decision. Test its assumptions, data, backtest behavior, structural stability, and sensitivity without presenting an unexecuted model run as fact. Provide: - Decision, forecast target, horizon, granularity, update cadence, required accuracy, and consequence of error: [Forecast decision and horizon] - Model/formula, feature or driver logic, transformations, assumptions, overrides, constraints, and version: [Model logic and assumption register] - Historical observations, training/calibration window, holdouts, backtests, errors, residuals, revisions, and benchmark forecasts: [Historical data and backtest evidence] - Recent driver changes, regime shifts, policy/market/process changes, anomalies, missingness, and leading indicators: [Current driver and structural-change evidence] - Base, upside, downside, stress cases, decision thresholds, risk limits, and alternative actions: [Scenarios thresholds and constraints] - Forecast owner, data owner, finance or planning reviewer, decision owner, and analysis limitations: [Accountable owners and review limits] Do not invent forecast values, confidence intervals, driver effects, or backtest statistics. Distinguish model output, planner override, observed fact, assumption, and scenario. Do not infer causal drivers from correlation alone. When raw data or executable logic is absent, assess the evidence and specify required tests rather than claiming to have rerun the forecast. Challenge: 1. Define the decision contract. State which decision the forecast informs, the timing, relevant error direction, tolerance, and alternative if evidence is insufficient. 2. Build an assumption register. Record every decision-critical assumption, source, rationale, range, dependency, owner, last validation, and failure consequence. Include missing-data treatment, overrides, external drivers, capacity limits, and behavioral responses. 3. Review data and model lineage. Trace target and drivers through sources, transformations, update timing, revisions, exclusions, and version changes. Flag leakage, survivorship, regime mixing, inconsistent definitions, and unavailable lineage. 4. Assess backtest evidence. Compare forecast errors across time, horizon, segment, regime, and relevant benchmark using supplied results. Check bias, variance, tail misses, turning points, coverage of intervals, and revision behavior. Avoid extrapolating from one period. 5. Identify structural drift. Map changes that weaken learned relationships or operational assumptions. Separate observed drift signals from hypotheses and specify evidence that would confirm them. 6. Run or design sensitivity analysis. Where supplied calculations permit, show how decision outcomes change across plausible assumptions. Otherwise provide exact scenario runs needed. Identify tipping points and assumptions that do not affect the decision. 7. Compare decision robustness. Determine whether Base, Upside, Downside, Stress, delayed-decision, staged-commitment, or no-action cases cross material thresholds. Keep probability claims evidence-bound. 8. Make a fitness decision. Choose Fit for stated decision, Fit with range/conditions, Use only as scenario input, Recalibrate/rebuild, or Not decision-fit. Assign owner actions and refresh triggers. Use ChatGPT to analyze supplied forecast artifacts and results; do not claim that backtests, refits, or sensitivity runs were executed unless their outputs are provided. Protect sensitive financial or operational data and stop where access or methodology is unauthorized. The model owner validates specification evidence, the finance or decision owner accepts decision risk, and the release owner authorizes operational use. Require acceptance evidence for each challenge: expected observation, actual observation, sensitivity boundary, reconciliation, and rollback or reforecast trigger. Required deliverable: # Forecast Assumption and Model Drift Challenge ## Decision Contract - Forecast and horizon: - Decision/threshold: - Cost of over-forecast and under-forecast: - Evidence limitations: ## Assumption Register | Assumption | Value/range | Evidence/source | Dependency | Last validated | Sensitivity | Owner | |---|---|---|---|---|---|---| ## Backtest and Drift Findings | Period/slice | Error evidence | Bias/tail issue | Structural change | Confidence | Decision impact | |---|---|---|---|---|---| ## Scenario and Tipping-Point Table | Scenario/assumption change | Forecast effect supplied or test needed | Decision threshold crossed? | Evidence | Action | |---|---|---|---|---| ## Forecast Fitness Decision - Decision: - Valid use and range: - Prohibited overclaim: - Required recalibration/data: - Monitoring and refresh trigger: - Accountable decision owner: Completion requires a traceable assumption register, honest use of backtest evidence, explicit structural-drift risks, and a decision that remains within the tested horizon and data regime.
View prompt
4 views
Knowledge Entitlement Drift Review
Reconcile authoritative access policy with effective permissions across source, ingestion, index, cache, retrieval, citation, and response layers.
Review whether effective access to enterprise knowledge has drifted from authoritative entitlement policy anywhere between the source system and the final retrieval response. Provide: - Approved subject, group, tenant, region, purpose, document, field, and time-bound access rules: [Authoritative entitlement policy] - Source repositories, ingestion, transformations, embeddings, indexes, namespaces, caches, retrievers, rerankers, citations, response filters, and identity propagation: [Knowledge architecture and identity flow] - Current ACLs, groups, policies, index filters, namespace rules, token claims, cache keys, and configuration exports: [Effective permission and configuration evidence] - Sanitized query/access traces, denials, sampled results, recertification records, deployments, migrations, and permission changes: [Retrieval access and change logs] - Data classification, privacy, residency, incident, availability, knowledge-owner, identity-owner, security-reviewer, and service-owner constraints: [Risk constraints and accountable owners] Do not claim a leakage event from configuration drift alone, and do not treat absence of logged leakage as proof of correct enforcement. Do not claim live access tests unless supplied. Distinguish observed evidence from inference, as well as authoritative policy, configured control, effective access, observed use, and confirmed exposure. Preserve uncertainty where identities or document lineage cannot be joined. Review: 1. Define authoritative entitlement contracts. Translate policy into subject-resource-action-context rules, including inheritance, deny precedence, purpose, tenant, region, embargo, expiry, field-level, and break-glass conditions. 2. Trace identity and entitlement propagation. Map how the caller identity and claims reach source filtering, ingestion metadata, index namespaces, retrieval filters, caches, citations, and response controls. Flag stages where identity is dropped, transformed, defaulted, or cached. 3. Reconcile effective controls. Compare authoritative rules with source ACLs, indexed metadata, filter logic, namespace membership, cache partitioning, service credentials, and post-retrieval controls. Account for stale groups, deleted users, broad service accounts, and shared indexes. 4. Build a drift register. Classify differences as Stale grant, Missing grant, Metadata loss, Filter mismatch, Namespace error, Cache-key weakness, Inherited expansion, Migration residue, Exception, or Not assessable. Record potential exposure and confirmed use separately. 5. Assess change and time behavior. Measure supplied lag between source permission changes and downstream propagation. Review revocation, role change, tenant move, embargo, and expiry handling. Do not invent propagation time. 6. Define the smallest safe correction. Recommend targeted re-indexing, metadata repair, filter correction, cache invalidation, token/session refresh, service-account restriction, or temporary serving restriction. Preserve availability and forensic evidence. 7. Design recertification proof. Specify positive and negative access cases, identity/tenant/region slices, document lineage checks, revocation timing, cache isolation, monitoring, and owner approval. Required deliverable: # Knowledge Entitlement Drift Review ## Authoritative Entitlement Matrix | Subject/context | Resource/class | Allowed/denied action | Condition/expiry | Policy evidence | Owner | |---|---|---|---|---|---| ## Propagation Map | Stage | Identity/entitlement input | Transformation | Enforcement | Evidence | Gap | |---|---|---|---|---|---| ## Drift Register | Drift | Classification | Policy state | Effective state | Potential exposure | Observed use | Confidence | Owner | |---|---|---|---|---|---|---|---| ## Correction and Restriction Plan | Priority | Smallest safe action | Affected layer | Availability effect | Authorization | Verification | |---|---|---|---|---|---| ## Recertification Gate | Test slice | Positive expectation | Negative expectation | Evidence source | Owner | Status | |---|---|---|---|---|---| ## Decision - Entitlement state: Aligned / Aligned with exceptions / Drift present / Not assessable - Serving restrictions: - Required corrections: - Unresolved exposure: - Revalidation trigger: Completion requires policy-to-response traceability for material knowledge classes, explicit separation of drift from confirmed exposure, and negative access evidence before removed permissions are considered effective.
View prompt
4 views
AI-Generated SQL Result Verification and Reconciliation
Validate AI-generated SQL and its reported results before they are used for a consequential decision.
Verify the AI-generated SQL and its claimed result before anyone relies on it for the stated decision. Treat this as a defensible review artifact, not a general SQL improvement exercise. Context to provide: - Repository scope and task: [Repository scope and task] - Relevant files and instructions: [Relevant files and instructions] - Observed evidence: [Observed evidence] - Constraints and authorized changes: [Constraints and authorized changes] - Environment details without secrets: [Environment details without secrets] - Verification commands and acceptance criteria: [Verification commands and acceptance criteria] Working rules: 1. Inspect the relevant files, saved queries, schemas, models, tests, lineage notes, and query artifacts first where they are available. 2. Do not claim that a table, file, source, command, test, query execution, permission boundary, or approval was inspected unless there is direct evidence in the provided materials or accessible workspace. 3. Identify the likely root cause of any discrepancy before editing SQL or proposing a corrected query. 4. Apply the smallest safe change needed to correct the query. Preserve existing intended behavior unless the evidence shows it is wrong. 5. Avoid broad rewrites, style-only edits, or migration to a different modeling pattern unless required to remove a proven defect. 6. Distinguish observations from inference. Mark unsupported assumptions explicitly. 7. Respect access boundaries. Do not suggest bypassing row-level security, protected schemas, production safeguards, or data owner controls. 8. Run syntax checks, query compilation, dry runs, unit tests, dbt tests, warehouse explain plans, or limited validation queries where available and appropriate. If execution is unavailable, state exactly what could not be run and what evidence substitutes for it. 9. Do not present a corrected result as final unless it is reproducible from inspected SQL and accessible evidence. Missing-input gate: - Treat the exact SQL, intended metric or decision and grain, relevant schema or lineage, and comparison source or acceptance threshold as blocking when their absence or conflict prevents semantic review or reconciliation. Request all blocking items in one consolidated clarification and stop the affected conclusion until they are supplied. - Continue with non-blocking gaps only as explicit Unknowns with their decision impact. Review procedure: A. Build the query intent contract - Restate the decision or metric question in operational terms. - Define the expected grain of the result. - Identify required dimensions, filters, joins, exclusions, deduplication rules, aggregation logic, time window, timezone, and freshness expectations. - Identify the authoritative definition owner when evident, such as data owner, analytics owner, finance owner, product owner, or compliance owner. - List evidence supplied versus evidence missing. B. Review SQL semantics before result claims - Check whether selected columns, grouping, joins, filters, CTEs, window functions, null handling, distinct logic, and aggregation match the query intent contract. - Check for join multiplication, accidental inner joins, incomplete predicates, slowly changing dimension issues, many-to-many joins, late-arriving data, timezone drift, partition filters, and snapshot-versus-current-state confusion. - Check whether access constraints or row-level filters could change the observed result. - Identify whether the SQL answers the stated question, a narrower question, a broader question, or a different question. C. Review execution and reproducibility evidence - Determine whether the claimed result can be traced to a specific SQL text, execution environment, parameters, source versions, run time, and data freshness state. - If query execution is available, run the safest reproducible check permitted by the environment and record the command/query used, result shape, row counts, and relevant totals. - If execution is not available, perform static validation and specify the minimum evidence needed from the data owner or analytics owner to complete reconciliation. D. Reconcile against authoritative totals - Compare the claimed result to authoritative totals, source-of-truth reports, known control totals, prior certified extracts, ledger totals, or approved metric definitions where supplied. - Reconcile at the most useful level: total, time bucket, segment, source system, account, customer, product, or other relevant dimension. - Explain every material variance using evidence where possible. Separate confirmed causes from plausible causes. E. Correct only what is justified - If the SQL is wrong and enough evidence exists, provide a corrected query or minimal patch. - If there is not enough evidence to correct the query safely, provide a bounded correction proposal and list the exact evidence required before use. - Preserve naming, output shape, filters, permissions, and downstream expectations unless the discrepancy requires a specific change. Deliverable format: 1. Query intent contract - Decision or metric question: - Intended grain: - Required population and exclusions: - Required time logic and timezone: - Required joins and source precedence: - Authoritative definitions or totals used: - Accountable owner to verify final use: - Missing context that limits certainty: 2. Semantic and execution review Create a table with columns: - Review area - Evidence inspected - Observation - Risk to claimed result - Status: Pass / Fail / Unverified - Notes or required follow-up Include at minimum: grain, joins, filters, aggregation, deduplication, time logic, null handling, access boundaries, source freshness, result reproducibility, and output shape. 3. Reconciliation table Create a table with columns: - Reconciliation item - AI-claimed value - Verified or control value - Difference - Materiality assessment - Evidence source - Explanation - Status: Reconciled / Variance explained / Unresolved 4. Unsupported-result register Create a table with columns: - Unsupported claim or result component - Why it is unsupported - Evidence needed - Accountable owner or source to confirm - Decision impact 5. Corrected-query and acceptance record - Root cause summary before any SQL change: - Minimal corrected SQL or patch, if justified: - Behavior intentionally preserved: - Behavior intentionally changed: - Files changed, if any: - Syntax checks, tests, dry runs, or executions performed: - Verification results: - Remaining uncertainty: - Acceptance decision: Accept / Accept with caveats / Reject / Cannot determine - Acceptance criteria met or unmet: - Owner verification required before decision use: Completion check: - The claimed result is either reconciled, corrected and reproducible, or explicitly rejected as unsupported. - All material assumptions are labeled. - Any changed files are summarized. - Verification results and unavailable checks are stated plainly. - The final acceptance decision is tied to the acceptance threshold or materiality standard.Retrieval Chunking and Metadata Experiment Design
Design a controlled retrieval experiment that compares chunking and metadata choices without turning into a full RAG redesign.
Design a controlled retrieval experiment to isolate how chunking choices and metadata choices affect retrieval and answer quality. Keep the scope limited to corpus configuration decisions: do not redesign the full RAG architecture, diagnose the whole pipeline, change model selection, or introduce unrelated retrieval changes unless they are explicitly controlled constants. Context to use: - Corpus description and sample documents: [Corpus description and sample documents] - Current retrieval setup: [Current retrieval setup] - Candidate chunking options: [Candidate chunking options] - Candidate metadata fields: [Candidate metadata fields] - Query logs or benchmark questions: [Query logs or benchmark questions] - Answer evaluation standard: [Answer evaluation standard] - Decision owners and constraints: [Decision owners and constraints] Evidence discipline: - Separate observations supported by the provided inputs from inferences and assumptions. - Do not claim that experiments, tests, retrieval runs, indexing jobs, or evaluations were completed unless results are provided. - If required information is missing, state the missing evidence and design the smallest reasonable way to obtain it. - Preserve uncertainty where the available evidence does not justify a firm conclusion. - Before defining experiment arms, treat the baseline retrieval configuration, candidate changes, representative corpus and query evidence, and answer-evaluation standard as blocking when absent or materially conflicting. Request all blocking items in one consolidated clarification and stop the affected design decision until they are supplied. Record other gaps as Unknown with their effect on sizing, confounding, metrics, and acceptance criteria. - Treat the retrieval owner as accountable for experiment execution, the data owner as accountable for corpus and metadata validity, and the product owner or domain owner as accountable for acceptance criteria tied to user impact. Produce the following deliverable: 1. Experiment objective and decision boundary - State the configuration decision this experiment will support. - Define what is in scope: chunking strategy, overlap, boundary rules, parent-child or hierarchical chunking if relevant, metadata extraction, metadata normalization, and metadata use in filtering or ranking. - Define what is out of scope and should be held constant: embedding model, reranker, generation prompt, answer model, UI behavior, permissions, and production rollout unless the inputs require otherwise. - Identify the baseline configuration and the candidate configurations to compare. 2. Controlled variables and constants Create a table with: - Variable under test - Candidate levels - Why it matters - Expected retrieval effect - Required implementation evidence - What must remain constant Include at minimum: - Chunk size or token range - Chunk overlap - Chunk boundary rule - Structural handling of headings, tables, lists, or sections - Metadata fields to attach - Metadata normalization rules - Metadata use pattern: display only, filter, boost, grouping, or attribution 3. Factorial experiment matrix Build a practical factorial or fractional-factorial matrix that isolates chunking and metadata effects without creating unnecessary runs. For each experiment arm include: - Arm ID - Chunking configuration - Metadata configuration - Retrieval settings held constant - Indexing requirements - Expected comparison value - Risk of confounding - Minimum evidence needed before execution If the full factorial design is too large, propose a staged design: - Screening stage to remove weak options - Focused comparison stage for finalists - Confirmation stage against the baseline 4. Dataset and query slices Define the evaluation dataset and query slices needed to detect meaningful differences. Include: - Document sample selection rules - Minimum corpus coverage requirements - Query source: logs, expert-written questions, synthetic-but-reviewed questions, or known support cases - Query slice taxonomy - Required number of queries per slice if enough information is available; otherwise give a sizing rule Use query slices such as: - Exact lookup questions - Multi-section synthesis questions - Long-tail entity questions - Recent or version-specific questions - Table or list extraction questions - Procedure or policy questions - Ambiguous terminology questions - Metadata-dependent questions - Queries where the answer should not be in the corpus For each slice, specify the retrieval failure mode it is intended to expose. 5. Relevance judgments and answer evaluation setup Define how ground truth or reference judgments should be created. Include: - Who should judge relevance and answer correctness - What evidence they need - How to label relevant, partially relevant, irrelevant, stale, duplicate, and misleading chunks - How to handle multiple valid source passages - How to record uncertainty and disagreements - How to prevent evaluators from seeing the tested configuration when practical 6. Retrieval metrics Specify retrieval metrics that directly measure chunking and metadata effects. Include: - Recall@k - Precision@k or context precision - MRR or nDCG where graded relevance exists - Source coverage for multi-source answers - Duplicate or near-duplicate rate in top-k - Metadata filter precision and filter fallout - Freshness or version correctness when metadata includes time or version fields - No-answer retrieval behavior for queries outside the corpus For each metric, define: - Formula or scoring method in plain language - Required inputs - Which query slices it applies to - What kind of configuration failure it reveals 7. Answer-level metrics Define answer metrics only as downstream checks of retrieval configuration, not as a full generation evaluation. Include: - Answer correctness against the evaluation standard - Citation/source support - Missing critical fact rate - Unsupported claim rate - Wrong-version or wrong-jurisdiction rate if relevant - Refusal or no-answer correctness where the corpus lacks the answer Explain how to attribute answer failures to retrieval versus generation when the top-k evidence is sufficient but the answer is wrong. 8. Error attribution rules Create a concrete error taxonomy with decision rules. Include at minimum: - Chunk boundary split error - Chunk too broad or noisy - Chunk too narrow or missing context - Overlap redundancy error - Metadata absent - Metadata incorrect - Metadata too coarse - Metadata filter excludes relevant evidence - Metadata boost overpromotes irrelevant evidence - Duplicate chunk crowding - Stale or wrong-version retrieval - Relevant evidence not indexed - Relevant evidence indexed but not retrieved - Answer failure despite sufficient retrieved evidence For each error type, define: - Observable evidence - How to distinguish it from similar errors - Likely configuration implication - Whether it should count against chunking, metadata, indexing, retrieval settings, or answer generation 9. Analysis plan by query slice Define how results should be compared across arms and slices. Include: - Primary metric and secondary metrics - Minimum practical improvement threshold - Regression checks where a configuration improves one slice but harms another - Treatment of ties - Treatment of small sample sizes - Required confidence or stability checks if repeated runs are possible - How to summarize tradeoffs for decision owners without hiding slice-level failures 10. Configuration acceptance decision Create a decision framework the retrieval owner, data owner, and product owner can use after the experiment is run. Include: - Acceptance criteria for adopting a new chunking and metadata configuration - Rejection criteria - Conditional acceptance criteria requiring remediation - Required evidence package before approval - Rollback or re-indexing considerations if adopted - Open questions that must be resolved before production use 11. Completion checklist End with a checklist that confirms whether the experiment design is ready to execute. Include checks for: - Baseline defined - Candidate arms defined - Constants identified - Query slices complete - Relevance judgment process defined - Metrics mapped to slices - Error attribution rules defined - Acceptance criteria agreed by named accountable owners - Missing evidence listed - No unsupported claims of completed executionWas this useful?