Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Plan an incremental load that loses nothing · 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

Plan an incremental load that loses nothing

The nightly load reads rows whose updated_at is greater than the last watermark, then records the new maximum. It has been quietly losing rows for months: the source writes in batches, so dozens of rows share the same updated_at to the second, and any of them written after the read but with the same timestamp are never picked up. Write plan_increment(rows, watermark_field, last_watermark, seen_at_watermark, id_field).

Function to write

plan_increment(rows: list[dict], watermark_field: str, last_watermark: str | None, seen_at_watermark: list, id_field: str) -> dict

A dict with the rows to process, the new watermark, and the ids seen at that watermark for the next run.

How to approach it

Read with greater-or-equal, then subtract the ids you already know you processed.

Sample cases

+ 2 held back until you submit

the first run

With no watermark everything usable is processed, and the boundary ids are recorded for next time.

Input

Argument 1

id	updated_at	amount
r1	2026-03-02T09:00:00Z	1
r2	2026-03-02T10:00:00Z	2
r3	2026-03-02T10:00:00Z	3
r4	2026-03-02T10:00:00Z	4
r5	2026-03-02T11:00:00Z	5
r6		6

Argument 2

'updated_at'

Argument 3

None

Argument 4

[] (empty list)

Argument 5

'id'

Returns

{
  'rows': [
    {
      'id': 'r1',
      'updated_at': '2026-03-02T09:00:00Z',
      'amount': 1
    },
    {
      'id': 'r2',
      'updated_at': '2026-03-02T10:00:00Z',
      'amount': 2
    },
    {
      'id': 'r3',
      'updated_at': '2026-03-02T10:00:00Z',
      'amount': 3
    },
    {
      'id': 'r4',
      'updated_at': '2026-03-02T10:00:00Z',
      'amount': 4
    },
    {
      'id': 'r5',
      'updated_at': '2026-03-02T11:00:00Z',
      'amount': 5
    }
  ],
  'watermark': '2026-03-02T11:00:00Z',
  'seen_at_watermark': [
    'r5'
  ]
}

resuming at a shared timestamp

Two rows share the recorded watermark and were never processed; the third was.

Input

Argument 1

id	updated_at	amount
r1	2026-03-02T09:00:00Z	1
r2	2026-03-02T10:00:00Z	2
r3	2026-03-02T10:00:00Z	3
r4	2026-03-02T10:00:00Z	4
r5	2026-03-02T11:00:00Z	5
r6		6

Argument 2

'updated_at'

Argument 3

'2026-03-02T10:00:00Z'

Argument 4

[
  'r2'
]

Argument 5

'id'

Returns

{
  'rows': [
    {
      'id': 'r3',
      'updated_at': '2026-03-02T10:00:00Z',
      'amount': 3
    },
    {
      'id': 'r4',
      'updated_at': '2026-03-02T10:00:00Z',
      'amount': 4
    },
    {
      'id': 'r5',
      'updated_at': '2026-03-02T11:00:00Z',
      'amount': 5
    }
  ],
  'watermark': '2026-03-02T11:00:00Z',
  'seen_at_watermark': [
    'r5'
  ]
}

nothing new

Everything is already processed, so the watermark and the boundary ids survive untouched.

Input

Argument 1

id	updated_at	amount
r1	2026-03-02T09:00:00Z	1
r2	2026-03-02T10:00:00Z	2
r3	2026-03-02T10:00:00Z	3
r4	2026-03-02T10:00:00Z	4
r5	2026-03-02T11:00:00Z	5
r6		6

Argument 2

'updated_at'

Argument 3

'2026-03-02T11:00:00Z'

Argument 4

[
  'r5'
]

Argument 5

'id'

Returns

{
  'rows': [],
  'watermark': '2026-03-02T11:00:00Z',
  'seen_at_watermark': [
    'r5'
  ]
}

an empty source

No rows at all must not reset the watermark or forget which ids were seen.

Input

Argument 1

[] (empty list)

Argument 2

'updated_at'

Argument 3

'2026-03-02T10:00:00Z'

Argument 4

[
  'r2'
]

Argument 5

'id'

Returns

{
  'rows': [],
  'watermark': '2026-03-02T10:00:00Z',
  'seen_at_watermark': [
    'r2'
  ]
}

Constraints

1Process every row whose watermark value is greater than or equal to the last watermark. Strictly greater loses rows that share the boundary value.
2Exclude a row at exactly the last watermark whose id is in seen_at_watermark — that one was processed by the previous run.
3With a last_watermark of None this is the first run: process everything usable.
4Return the selected rows ordered by watermark value then id.
5The new watermark is the highest value among the selected rows.
6seen_at_watermark for the next run is the ids of every selected row at that new watermark. When the watermark did not move, the previous run's ids are still at the boundary and must be carried forward too.
7When nothing is selected, return the watermark and the seen ids unchanged. An empty source must never reset the state.
8A row with no usable watermark value or no id cannot be tracked, and is not processed.

Worked example

Three rows — r2, r3 and r4 — all carry 2026-03-02T10:00:00Z. Suppose the last run read up to and including r2 and recorded the watermark as that timestamp. A strictly-greater comparison skips r3 and r4 forever: their timestamp is not greater than the watermark, and it never will be. They are simply gone, with no error and no gap in any count anybody checks. Reading with greater-or-equal picks them up, and would also re-process r2 — which is why the watermark alone is not enough state. Carrying the ids seen at exactly the boundary makes the pair (watermark, ids) a complete description of progress: everything below the watermark is done, and at the watermark, these specific rows are done. The empty-source case is the one that turns a bug into an incident. Returning a watermark of None when nothing was selected resets the load, and the next run reprocesses the entire table.

What this tests

That a watermark on a non-unique column is not a complete checkpoint. The fix — carry the ids at the boundary — is small and the reasoning is the hard part: either comparison is wrong on its own, and only the pair is correct.

PYTHON

plan_increment(rows: list[dict], watermark_field: str, last_watermark: str | None, seen_at_watermark: list, id_field: str) -> 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