Overview
order_id is the crate label. order_total is the weight. Run the same expectation on both before finance trusts GMV.
On this page7 sections
The table we keep using
A blank ID is a lost crate. A blank total is a crate you cannot bill.
A null on a key and a null on a measure are different incidents that share one check. order_id is the crate label: without it you cannot join, refund, or dispute. order_total is the weight: the crate exists, the invoice amount does not.
The same function from the last lesson covers both. You pass the column name. failed_rows is int(df[col].isna().sum()). success is whether that number is zero.
Great Expectations names this expect_column_values_to_not_be_null. You already wrote that count. Tonight run it twice on df_orders, then still project order_id and order_total into result.
What we need from it
An e-commerce finance mart that ships with null order_total will understate GMV. SUM skips nulls in pandas and in SQL. The missing orders never appear as a hole in the chart. They vanish from the total. A not-null gate on the measure fails the publish instead of quietly shrinking revenue.
A bank ledger that ships with null transfer_id cannot reverse a debit. Support cannot find the row. Completeness on the key is what makes every later join possible. Completeness on the amount is what makes the books additive.
Keys and measures both need the check because they fail differently. One lost label. One lost weight. Both are not-null. The column name is which story you are telling in the incident channel.
Trace it step by step
df_orders.order_id and df_orders.order_total are clean in this warehouse. df_telemetry.reading is not. Sample shows the sensor lane failing so you can see a False next to two Trues.
Quarantine (keep failing rows aside) is a cousin of dropna. schema-contracts already dropped missing totals. Here you report.
| Column | Meaning of a null | Usual action |
|---|---|---|
| order_id | Cannot join or dispute | Fail the batch |
| order_total | Cannot add GMV | Fail, or quarantine that row |
| reading | Sensor gap | Alert; do not fill with zero |
Print both calls. result is the two-column projection you would actually stare at during an incident: order_id, order_total. Pandas schema-contracts used assert plus dropna to publish that frame. Keep the instinct. Report first, then project.
- Reuse expect_column_not_null(df, col).
- Print the dict for "order_id".
- Print the dict for "order_total".
- Assign result = df_orders.loc[:, ["order_id", "order_total"]].
Empty string is not NaN. A numeric CSV blank becomes NaN and isna() catches it. A status of "" survives not-null and fails a set check later. Do not treat those as the same defect.
- One helper, two columns, two printed dicts.
- failed_rows is the count of isna() True.
- Never fill a missing total with 0 to make the gate pass.
Call the helper three times in Sample so the failing sensor lane is visible. The exercise grades the two orders columns.
def expect_column_not_null(df, col):
failed_rows = int(df[col].isna().sum())
return {"success": failed_rows == 0, "failed_rows": failed_rows}
print(expect_column_not_null(df_orders, "order_id"))
print(expect_column_not_null(df_orders, "order_total"))
result = df_orders.loc[:, ["order_id", "order_total"]]The sensor column is the contrast. A False here is expected. Filling reading with 0 would make the gate lie about completeness.
def expect_column_not_null(df, col):
failed_rows = int(df[col].isna().sum())
return {"success": failed_rows == 0, "failed_rows": failed_rows}
print("order_id", expect_column_not_null(df_orders, "order_id"))
print("order_total", expect_column_not_null(df_orders, "order_total"))
print("reading", expect_column_not_null(df_telemetry, "reading"))
result = df_orders.loc[:, ["order_id", "order_total"]]Empty string is not NaN
isna() does not catch "". A blank status is a validity problem for the accepted-values lesson, not a not-null miss. Count nulls and empty strings as separate defects.
SQL COUNT of IS NULL
SELECT COUNT(*) FROM orders WHERE order_total IS NULL is this gate in SQL. dbt's not_null test compiles to that shape. pandas uses isna().sum(). The grain question is identical.
Same table, next cut
Run the same helper twice on df_orders: once on the key (order_id) and once on the measure (order_total). Both pass here. The sensor column is the contrast.
Input: header rows you would invoice from.
| order_id | order_total |
|---|---|
| ORD-0001 | 84.20 |
| ORD-0002 | 31.00 |
| ORD-0003 | 12.50 |
def expect_column_not_null(df, col):
failed_rows = int(df[col].isna().sum())
return {"success": failed_rows == 0, "failed_rows": failed_rows}
print("order_id", expect_column_not_null(df_orders, "order_id"))
print("order_total", expect_column_not_null(df_orders, "order_total"))
result = df_orders.loc[:, ["order_id", "order_total"]]Both orders columns return success True. A null order_total would fail the publish because SUM skips nulls and GMV would shrink silently.
Output: two printed dicts, two-column projection in result.
| column | role | result |
|---|---|---|
| order_id | grain / join key | success True |
| order_total | measure / GMV | success True |
Copy-paste without reading the output
Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.
Common beginner questions
Why check order_total if SUM can skip nulls?
Because skipping is the bug. A missing amount is not a zero-dollar order. Failing the gate forces a human to decide: quarantine the row, or stop the publish.
Should I dropna instead?
dropna is a transform. A gate is a report. schema-contracts already practiced dropna. Here you print success and a count, then still project the columns you would publish if the gate passed.
What about nulls in optional columns?
Skip them. Coupon codes and gift messages can be blank. Put not-null on keys, on measures you will invoice from, and on foreign keys you will join on.
What comes next
The next lesson checks uniqueness on the grain. duplicated() marks extras. A unique check on user_id in orders will fail because customers reorder. That is the wrong grain, not a broken pipeline.
Practice
Run Sample and read the three printed dicts. Then complete Exercise: reuse expect_column_not_null. Print the dict for df_orders "order_id", then again for "order_total". result is df_orders.loc[:, ["order_id", "order_total"]].
stdout should include True and a dict with success True. Same isna().sum() helper as the last lesson.
Practicals · load into the editor
After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.
Practice this
Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.