Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Reconcile two systems row by row · Python scenario · D8LooP

Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.

CODE WORKSPACE

Reconcile two systems row by row

Every night the pipeline copies orders from the transactional database into the warehouse, and every quarter finance asks whether the two agree. Comparing totals is not enough — two errors of opposite sign cancel — so the check has to be row by row, and it has to tolerate the small rounding differences that a currency conversion legitimately produces. Write reconcile(left, right, key, compare_fields, tolerance).

Function to write

reconcile(left: list[dict], right: list[dict], key: str, compare_fields: list[str], tolerance: float) -> dict

A dict with report — one classified entry per key — and duplicate_keys, the repeated keys on each side.

How to approach it

Index both sides, noticing repeats as you go; then walk the union of the keys.

Sample cases

+ 2 held back until you submit

two systems that nearly agree

A rounding difference inside tolerance, a real status difference, a key on each side only, and a duplicate.

Input

Argument 1

order_id	amount	status
o1	100	shipped
o2	50	pending
o3	25	shipped
o2	51	pending

Argument 2

order_id	amount	status
o1	100.004	shipped
o2	50	shipped
o4	9	shipped

Argument 3

'order_id'

Argument 4

[
  'amount',
  'status'
]

Argument 5

0.01

Returns

{
  'report': [
    {
      'key': 'o1',
      'status': 'match',
      'differences': []
    },
    {
      'key': 'o2',
      'status': 'differs',
      'differences': [
        {
          'field': 'status',
          'left': 'pending',
          'right': 'shipped'
        }
      ]
    },
    {
      'key': 'o3',
      'status': 'only_in_left',
      'differences': []
    },
    {
      'key': 'o4',
      'status': 'only_in_right',
      'differences': []
    }
  ],
  'duplicate_keys': {
    'left': [
      'o2'
    ],
    'right': []
  }
}

both systems empty

Nothing to reconcile is an empty report rather than a failure.

Input

Argument 1

[] (empty list)

Argument 2

[] (empty list)

Argument 3

'order_id'

Argument 4

[
  'amount'
]

Argument 5

0.01

Returns

{
  'report': [],
  'duplicate_keys': {
    'left': [],
    'right': []
  }
}

nothing compared

With no fields to compare, every shared key matches by definition.

Input

Argument 1

order_id	amount	status
o1	100	shipped
o2	50	pending
o3	25	shipped
o2	51	pending

Argument 2

order_id	amount	status
o1	100.004	shipped
o2	50	shipped
o4	9	shipped

Argument 3

'order_id'

Argument 4

[] (empty list)

Argument 5

0.01

Returns

{
  'report': [
    {
      'key': 'o1',
      'status': 'match',
      'differences': []
    },
    {
      'key': 'o2',
      'status': 'match',
      'differences': []
    },
    {
      'key': 'o3',
      'status': 'only_in_left',
      'differences': []
    },
    {
      'key': 'o4',
      'status': 'only_in_right',
      'differences': []
    }
  ],
  'duplicate_keys': {
    'left': [
      'o2'
    ],
    'right': []
  }
}

Constraints

1Report every key in either system, ordered by key as text.
2A key on one side only is only_in_left or only_in_right, with no differences listed.
3A key on both sides is match when every compared field agrees, and differs otherwise, listing each disagreeing field with both values.
4Two numbers agree when they are within tolerance of each other. Everything else agrees only when it is equal.
5A boolean never agrees with a number, even though Python says True == 1. A flag and a count are different facts.
6When a key appears more than once on a side, keep the first row for the comparison and report the key under duplicate_keys for that side.
7Compare only the fields in compare_fields, in that order. No fields means every shared key matches.
8Return report and duplicate_keys, the latter holding a sorted list per side.

Worked example

Order o1 is 100.0 in the source and 100.004 in the warehouse. With a tolerance of 0.01 that is a match — it is the currency conversion, not a lost payment. Order o2 has the same amount on both sides and a status of pending against shipped, which is a genuine disagreement and the one finance cares about. o2 also appears twice on the left. Silently joining through the duplicate gives two report lines for one order, or one line built from whichever copy the implementation happened to keep — and either way the report says something the data does not support. Comparing the first and naming the key as duplicated keeps the report honest about what it could and could not check.

What this tests

Comparison with a tolerance, which is a different operation from equality, and the habit of reporting the input's defects rather than joining through them. A reconciliation that cannot say what it failed to check is not a reconciliation.

PYTHONreconcile(left: list[dict], right: list[dict], key: str, compare_fields: list[str], tolerance: float) -> dict

AI evaluation

The reviewer reads your query.

Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules