The port handed over a dict, not a DataFrame — SQL Course

O que você vai aprender

to look at the source's response as a dict with rules of its own, not as a ready-made table
to declare a row contract: what must be there, what has to be cast to a type, and what may be ignored in silence
to reject a broken record AT THE BOUNDARY rather than discover it in the mart three shelves later
to tell "the source handed us rubbish" from "we dropped the load"

Ticket #02. "Falls over every third night"

11:40. Done with the mart, you open the second ticket — it has been hanging since last week and was written by your predecessor himself:

"The orders loader falls over roughly one night in three. The log always says the same thing: KeyError: 'status'. A re-run usually helps. No time to dig in."

"Usually helps" here does not mean "fixes". On a repeat the source hands over a different set of rows, and the broken record may simply not fall into it. Tonight you were lucky — the load went through. Tomorrow the same problem comes back.

QUERY: "A re-run usually helps" is not a diagnosis, it is a bet. Your predecessor placed it one night in three and won more often than not. You do not need a bet, you need a promise the source is obliged to keep.

Start with the error itself. KeyError means the code reached for a field that was not in the response. The loader assumed: if the source handed over a record, then the record has everything we need. The source promised no such thing. It simply handed over a dict.

So the problem is not one missing field. The loader has no explicit rules at all for turning somebody else's dict into our row.

Today you build the first section of your belt — the intake port. Its job is not just "download the data" but to turn somebody else's dict into our row, or to refuse honestly.

First, let's look at what actually arrives at the boundary.

The intake desk: identical cards spill from a tray; some have a field burned through, others show a quantity below zero, and others have a scrawl instead of a date. Two trays and a steel template-sieve stand beside them. — Until a card has lain on the template it is not a record but a scrap of paper: the contract is checked at the intake, not in the mart.

One page of the source's response. The first block shows what a record really looks like: which keys it has, which types, and how money is represented. The second counts four kinds of deviation. You do not need to unpack the list-comprehension mechanics here: focus on the result and decide what to reject, cast to a type, or ignore.

from arena_source import fake_api

REQUIRED = ('order_id', 'customer_id', 'city', 'status', 'amount', 'qty', 'created_at')

page = fake_api.get('/orders', {'limit': 120})
rows = page['rows']
print('ответ порта : %s · одна строка: %s · строк: %d · есть ещё: %s'
      % (type(page).__name__, type(rows[0]).__name__, len(rows), page['has_more']))
print()
print('Первая строка, поле за полем:')
for name in sorted(rows[0]):
    print('    %-12s %-4s %r' % (name, type(rows[0][name]).__name__, rows[0][name]))
print()

missing = [r for r in rows if [f for f in REQUIRED if f not in r]]
neg_qty = [r for r in rows if isinstance(r.get('qty'), int) and r['qty'] < 0]
bad_date = [r for r in rows if not str(r.get('created_at', '')).startswith('2184-')]
unknown = [r for r in rows if set(r) - set(REQUIRED) - {'updated_at'}]

print('Из %d строк одной страницы:' % len(rows))
print('    нет обязательного поля : %2d, например %d (нет %s)'
      % (len(missing), missing[0]['order_id'],
         ', '.join(f for f in REQUIRED if f not in missing[0])))
print('    qty отрицательный      : %2d, например %d (qty=%d)'
      % (len(neg_qty), neg_qty[0]['order_id'], neg_qty[0]['qty']))
print('    дата не разбирается    : %2d, например %d (created_at=%r)'
      % (len(bad_date), bad_date[0]['order_id'], bad_date[0]['created_at']))
print('    незнакомое поле        : %2d, например %d (%s)'
      % (len(unknown), unknown[0]['order_id'],
         ', '.join(sorted(set(unknown[0]) - set(REQUIRED) - {'updated_at'}))))

from arena_source import fake_api

REQUIRED = ('order_id', 'customer_id', 'city', 'status', 'amount', 'qty', 'created_at')

page = fake_api.get('/orders', {'limit': 120})
rows = page['rows']
print('ответ порта : %s · одна строка: %s · строк: %d · есть ещё: %s'
      % (type(page).__name__, type(rows[0]).__name__, len(rows), page['has_more']))
print()
print('Первая строка, поле за полем:')
for name in sorted(rows[0]):
    print('    %-12s %-4s %r' % (name, type(rows[0][name]).__name__, rows[0][name]))
print()

missing = [r for r in rows if [f for f in REQUIRED if f not in r]]
neg_qty = [r for r in rows if isinstance(r.get('qty'), int) and r['qty'] < 0]
bad_date = [r for r in rows if not str(r.get('created_at', '')).startswith('2184-')]
unknown = [r for r in rows if set(r) - set(REQUIRED) - {'updated_at'}]

print('Из %d строк одной страницы:' % len(rows))
print('    нет обязательного поля : %2d, например %d (нет %s)'
      % (len(missing), missing[0]['order_id'],
         ', '.join(f for f in REQUIRED if f not in missing[0])))
print('    qty отрицательный      : %2d, например %d (qty=%d)'
      % (len(neg_qty), neg_qty[0]['order_id'], neg_qty[0]['qty']))
print('    дата не разбирается    : %2d, например %d (created_at=%r)'
      % (len(bad_date), bad_date[0]['order_id'], bad_date[0]['created_at']))
print('    незнакомое поле        : %2d, например %d (%s)'
      % (len(unknown), unknown[0]['order_id'],
         ', '.join(sorted(set(unknown[0]) - set(REQUIRED) - {'updated_at'}))))

python · источник

The row contract: three decisions per field

The very first row of the page arrives without status. That is the row your predecessor's loader fell over on. And it is still a convenient defect: you see it straight away. The other cases do not stop the code: a negative quantity and an unparseable date may travel on, while an unknown field should simply be dropped.

Take the amount: it arrived AS A STRING — '336.68'. APIs often hand money over this way: the source should not decide for us how to store and round values. But our belt still needs a definite type.

While the amount stays a string, amount * qty gives you not revenue but repeated text. So at the boundary the external format has to be turned into the internal one explicitly.

For money the contract uses Decimal, not float. float stores numbers in binary: 0.1 + 0.2 in it does not equal 0.3, so arithmetic with such values can accumulate rounding error. In stg_orders the amount sits as numeric(12,2), so the port's contract speaks the same language: Decimal('336.68') keeps exactly what the source sent.

The date is the same story. It arrives as a string too, and in three records out of a hundred and twenty the word "yesterday" stands where the date should be. The quantity is negative on four records: that is a reversal, arriving as a row of its own. And the loyalty_tier field was added by the source last week without asking anybody — and more will follow.

React to each such case only as it brings the load down, and the night's load depends on the accidents of the source. So the decision for every field is taken IN ADVANCE.

Decision	What we do	Example
must be there	no field — the row goes no further	`order_id`, `status`, `amount`
cast the type	does not cast — the row goes no further	`'336.68'` → `Decimal('336.68')`, `'2184-03-12 01:02:03'` → a date
ignore	the field is not in the contract — drop it in silence	`loyalty_tier`

The third decision is especially important. A new field at the source must NOT bring parsing down: nobody reads another team's release notes at night. We simply do not include it in our row.

A missing mandatory field, on the other hand, is already a breach of contract. A record like that cannot go further: the data the calculations need is not there anyway.

The contract itself is better pinned down in code than left as a comment somebody forgets to update. Python has a dataclass for that: the fields listed set the shape of the row the port promises the next shelf. Anything that is not in the dataclass will not reach stg_orders — and that is not a loss here, it is a deliberate rule of the boundary.

What is left is to decide what happens to a record that failed the contract. In this lesson the parsing function returns either a finished row or None.

None here means not "drop the load" but "this record goes to quarantine, and the belt keeps running". Broken rows on a page of a hundred and twenty must not stop the whole night.

The quarantine shelf itself you will set up in the very next chapter, and the weight of a rejection and the counters that go with it we take apart in chapter de8. What matters now is the principle: the decision about quality is taken at the boundary, while it is still clear what exactly the source sent — not three shelves later in a finished report.

Three decisions of the contract and three different outcomes for one record: a required field is missing — the row goes to quarantine, a type is coerced — it travels on, an unknown field is silently dropped and the parse does not fall over.

What is real here and what is sandbox. The Python in this course runs straight in the browser, and there is no network access there. So arena_source.fake_api makes no real HTTP call.

The shape of the interaction with the source is preserved. In this cell, focus on the api.get(path, params) call and the response containing a list of rows. The has_more and next_cursor markers, error codes and Retry-After will matter in later lessons — you do not need to unpack them now. Swap fake_api for requests and the main row-parsing steps stay the same.

The shim leaves out what would make the lesson hard to reproduce: real timeouts and delays. Deviations in the data are defined in advance rather than appearing at random.

That matters for debugging: you can compare the function's result with a known and see which contract rule fired.

Practice: write the code

Write the intake port: a function parse(row) that turns a source dictionary into an OrderRow or returns None. The job of the function is to walk one record through the contract and let nothing that fails it travel on. The contract it has to hold:

required fields — all seven from REQUIRED. Even one missing → None;
amount arrives as a string, in the contract it is a Decimal (money is not kept in float). Does not convert → None;
qty — an integer, strictly greater than zero. Zero and negative → None (refunds travel their own road);
created_at arrives as a string of the form 2184-03-12 01:02:03, in the contract it is a datetime. Does not parse → None;
unknown fields (loyalty_tier, updated_at) do not break the parse and do not reach OrderRow.

The starter holds the naive parse — the very one that fell over with KeyError. Replace it so that one bad record turns into None instead of stopping the whole port.

from dataclasses import dataclass
from datetime import datetime
from decimal import Decimal, InvalidOperation
from typing import Any, Dict, Optional

REQUIRED = ('order_id', 'customer_id', 'city', 'status', 'amount', 'qty', 'created_at')


@dataclass
class OrderRow:
    """Контракт строки: то, что порт обещает следующей полке."""
    order_id: int
    customer_id: int
    city: str
    status: str
    amount: Decimal   # деньги — Decimal: в stg_orders это numeric(12,2)
    qty: int
    created_at: datetime


def parse(row: Dict[str, Any]) -> Optional[OrderRow]:
    """Наивный разбор: берёт что дали и ничего не проверяет."""
    return OrderRow(
        order_id=row['order_id'],
        customer_id=row['customer_id'],
        city=row['city'],
        status=row['status'],
        amount=row['amount'],
        qty=row['qty'],
        created_at=row['created_at'],
    )

python · источник

Interview question

How this is asked at interview. "The source added a new field to its response and renamed an old one. What should happen to your load?"

The important thing here is to separate two events.

If a new field appeared, the load carries on and the field is ignored. You cannot bring the whole belt down because the source widened its response.

If a mandatory field disappeared or was renamed, the row no longer matches the contract. Rows like that are rejected and go to quarantine. The chapter on quality control will explain when widespread bad data should stop the whole task.

The second question of the same series: "where do you reject bad records — at the input or in the mart?" The answer is at the input.

The reason is practical: the further a broken row travels down the belt, the more layers it touches and the more expensive the investigation. For this lesson, the rule is enough: check each row at the input.

Check yourself

The source kept every field in the contract and added a new delivery_slot field to each order. How is the receiving port obliged to behave tonight?

Check yourself

The parsing code says amount = row['amount'] with no type cast. The value 336.68 remained a string. What can happen when the code first uses it as a number?

Practice: solve the tasks

Solved 0 of 3 · any 2 is enough to pass

Principais pontos

The source hands over dicts, not a ready-made table: the types may be strings, and the set of keys is not guaranteed.
A dataclass sets the row's shape, and the parsing function checks the admission rules. Every field gets one of three decisions in advance: must be there, cast the type, or ignore.
An unknown field does not bring parsing down. A missing mandatory field brings THE ROW down, not the whole night.
parse(row) -> OrderRow | None: None means "to quarantine". The decision is taken at the boundary, while it is still clear what exactly the source sent.
The KeyError: 'status' from ticket #02 no longer reproduces: the field is checked before it is reached.

The intake port can now tell a good row from a bad one. But so far it brings back only the first page of the source's response.

Further down the belt is : how to fetch the rest of the pages without losing data between calls.