Fetch it all, not the first page — SQL Course

O que você vai aprender

to fetch the source's window whole: page after page, up to the end marker
to tell cursor from offset, and to understand what happens when the data changes during an export
to hand rows back as a generator, so the window never has to be held in memory whole
to count calls to the source and avoid spending quota on an unnecessary request

Ticket #03. "Fewer orders in the report than in the snapshot"

13:05. The Academy opened this ticket three days ago, but nobody has taken it:

"The daily export has 50 orders. The for the same day has 143. The discrepancy is stable: exactly 50 every day, however many rows the snapshot holds."

The most telling thing here is the number "exactly 50 every day". In the snapshots the order counts move about, and in the export the same constant comes back time after time.

So the problem is not in the data. We have run into a LIMIT: fifty is the source's page size.

Your predecessor's loader makes one request, gets the first page and considers the job done. The source honestly says that there is more data — and the loader ignores that signal.

Let's reproduce the error first.

In the circle of light on the belt stands one crate with a single notch, and past the edge of the light another dozen just like it can be made out; on the archivist's tablet there is one mark and an empty socket for the total. — The first page is not the source's answer but its beginning: until you reach the end you do not even know how many there are.

A single call of the port for the window "everything that changed after 12 March 12:00". Look not at the rows themselves but at two fields of the response: has_more and next_cursor. The full-window calculation at the bottom is only there for comparison; you do not need to unpack it here. The source says in plain words that it has not handed over everything yet.

from arena_source import fake_api, make_orders

SINCE = '2184-03-12 12:00:00'

fake_api.reset()
page = fake_api.get('/orders', {'since': SINCE, 'limit': 50})

print('строк в ответе      :', len(page['rows']))
print('has_more            :', page['has_more'])
print('next_cursor         :', page['next_cursor'])
print('вызовов к источнику :', fake_api.calls)
print()

window = [r for r in make_orders(240) if r['updated_at'] >= SINCE]
print('всего строк в окне  :', len(window))
print('загрузчик увидел    :', len(page['rows']))
print('НЕ доехало до ленты :', len(window) - len(page['rows']), 'строк')

python · источник

Why the source hands over in pieces, and how to fetch it all

The page limit is not there to make the client's life harder. If the source tried to return too many rows in one response, it would have to hold memory and a connection tied up for longer. And it is a live system that is answering other requests in that same second.

So an API normally hands data over in pages and, with every page, tells you two things: whether there is more, and where to read on from.

There are two ways of reading on. The difference between them becomes critical when the data changes during the export itself.

Offset. "Give me 50 rows starting from the 100th."

While the source does not change, the scheme is simple. But our intake port reads orders in updated_at order, and on the other side some orders are edited after the fact. An order from last week has its updated_at changed and jumps to the back of the queue.

Imagine you have already read the second page and are moving on to the third. At that moment one row from the second page drives off to the tail. Everything that stood after it shifts one position towards the front. The next offset stays the same — and one row slips past the export for good.

The symptom is unmistakable: a few orders missing from the mart, the problem hard to reproduce, and on a repeat export everything suddenly "adds up".

A keyset cursor. "Give me 50 rows AFTER this mark."

The cursor is opaque to the client: you do not compute it yourself, you hand it back to the source as it is. Such a cursor encodes not a row number but the sort-field values of the last row handed over.

That is why neighbouring rows shifting does not make the next row fall through a page boundary. But this still does not turn the result into a as of the start of the export: a changed row may appear again. The opacity matters here: a client that does not know how the cursor is built does not try to recompute it and cannot get out of step with the source.

Pagination is settled. Now the second question: where to put the pages you have already read.

You could collect them into one list and return the lot. With 143 rows nobody notices, but the window of a daily export may be much larger. There is no reason to keep them all in the loader's memory.

So the port hands back a generator. A page arrives — the rows flow off down the belt — the page can be forgotten. The generator matters here not as a stylistic device but as a way of not accumulating the whole window in memory.

Lazy reading has one more consequence: the whole work of the next step of the pipeline can happen between "handed over the first row" and going for the next page.

And a third rule: calls have to be counted.

A source normally limits how often you may ask. So an empty request after the last page is not a harmless "just in case" check, it is quota spent for nothing and one more chance to receive a 429 "too many requests".

If the API has already said has_more: false, the window is over. There is no point going for an empty page on top.

QUERY: The source said "there is more" three days running. Nobody listened. Listening to the source is cheaper than explaining the shortfall to the Academy afterwards.

A row is edited during the export: the neighbours shift left, the offset stays the same, and one row slips past for ever. A cursor bookmark stores the last key, so the next row is not lost when the boundary shifts.

What is real here and what is sandbox. fake_api reproduces the behaviour of a paginated REST source: a window by since, a limit, an opaque next_cursor, the has_more flag, and the call counter fake_api.calls. The traversal pattern carries over to requests; the network call and response parsing must be adapted.

But the sandbox is deliberately simpler than a live system. There is no network here, no parallel requests and no real load on the source.

The rate limit does not trigger by itself either: a 429 response is set in advance, so that the lesson gives every reader the same result. That makes the call counter a teaching metric in the sandbox. In the same role is played by the source's quotas and by your own limit on concurrent loads.

There is one more important boundary — how the cursor is built. The string c2c91dfe9 is opaque to you, but inside the shim it is an encoded offset, not a position in the ordering.

That is, arena_source honestly reproduces the PROTOCOL of cursor , but not its robustness to rows shifting. A real keyset cursor stores the sort-field values of the last row and does not skip the next row merely because its ordinal position changed.

And finally, there are no after-the-fact edits in this source at all: updated_at is set once, shortly after created_at, and never changes again.

So the gaps offset produces are DESCRIBED in this lesson, not reproduced. The offset example remains a thought experiment here: the sandbox demonstrates only the pagination protocol.

Practice: write the code

Rewrite fetch_all(since, page_size) so that the port takes the whole window instead of stopping after the first page. Requirements:

the function is a generator: it yields rows one at a time (yield) instead of returning a list;
it walks the pages, passing next_cursor back, until the source says has_more: False;
since goes in EVERY request: the cursor points at a place inside the window, and the window itself is set by since;
no surplus calls. The window holds exactly three pages — so exactly three requests to the source.

The starter holds the very loader from ticket #03: it takes one page and puts it into a list. The check deliberately collects the result into a list so it can compare the rows; that is test machinery, not a pattern for the loader. Your job is to turn the starter into a lazy walk over the whole window.

from typing import Any, Dict, Iterator, Optional
from arena_source import fake_api

PAGE_SIZE = 50


def fetch_all(since: str, page_size: int = PAGE_SIZE) -> Iterator[Dict[str, Any]]:
    """Пока забирает только первую страницу и отдаёт её списком."""
    page = fake_api.get('/orders', {'since': since, 'limit': page_size})
    return page['rows']

python · источник

Interview question

How this is asked at interview. "You are exporting a table through an API page by page. While the export runs, rows in the source change. What will you get at the end, and how do you live with it?"

What is being checked here is not that you know the word "" but that you understand what happens to page boundaries while the source is changing.

With offset pagination over a changeable ordering you get gaps and duplicates. When a row drives off past a boundary already read, the rest of the records shift, and the next offset stays the same.

A cursor will not skip a row merely because its ordinal position shifted, but another problem remains: the result still does not become a as of the start of the export. While you read the pages, some rows manage to change and arrive in a newer revision.

Hence the practical conclusion they actually want to hear: a page-by-page export does not by itself give a consistent slice. In our API, since sets only the lower bound and next_cursor only the place to continue; neither field freezes the data as of the start.

A frequent follow-up: "and what if the source only offers offset?" Then you choose the most stable ordering available and account for the API's limitation: offset alone cannot guarantee a consistent slice.

Check yourself

An export runs by offset, ordered by updated_at. Between the requests for the second and third pages, an order from the SECOND page has its updated_at edited after the fact and moves to the end. What happens at the page boundary?

Check yourself

The author rewrote fetch_all like this: collect all the pages into a list rows and return rows at the end. The lesson's check fails on the very first assert. Why is that not nitpicking?

Principais pontos

The steady "exactly 50 orders a day" from ticket #03 turned out to be the page size, not the number of sales. The source itself says whether there is more (has_more) and where to continue (next_cursor).
Offset breaks when the ordering is built on a changeable field: the rows shift while the page boundaries stay where they were, which gives gaps that are hard to reproduce.
A cursor is opaque to the client. A keyset cursor stores the last key rather than a row number, so the next row is not skipped merely because the boundary shifted. In this course's sandbox an offset hides behind the cursor: the protocol is real, the robustness is not.
since goes in every request: the cursor points at a place INSIDE the window, and the window is set by since.
The port hands back a generator and does not accumulate all rows itself, so its memory does not grow with the size of the window.
Calls are counted. Three pages, three requests; a fourth "to make sure" only spends quota for nothing and may end in a 429.

Further down the belt: the port can already fetch a whole window and parse a row. What is left is to check what happens if you run it twice for the same date.