Overview
orders.order_id should be unique. ecommerce_events.event_id is duplicated on purpose for late ingest. The function must tell those stories apart.
On this page7 sections
The table we keep using
unique is a claim about grain, not a vibe about cleanliness.
Uniqueness is a claim about grain: one row per order_id in orders, one row per event_id if you said that is the grain. The test is the same hunt SQL already ran with GROUP BY order_id HAVING COUNT(*) > 1.
In pandas, duplicated() marks extra copies after the first. duplicate_count is how many extras you found. success is whether that number is zero. expect_column_unique(df, col) returns {"success": bool, "duplicate_count": int}.
This warehouse's orders.order_id is unique. df_events.event_id is not: the generator replays a slice of events with a later ingest_time, the late-arriving fact you already met in SQL. Print both so you can see a pass and a fail.
What we need from it
An e-commerce GMV mart that joins orders to a duplicated extract will fan out. One order becomes two rows. SUM(order_total) doubles. The chart looks like a record day. Uniqueness on the grain would have failed the job before finance saw the spike.
A bank that stores customer_id twice in a dimension used for statements will mail two copies, or worse, apply a fee twice. Duplicate keys at the grain you claimed are not a style issue. They are double counting.
The wrong unique check is also expensive. unique on user_id in orders fails for any repeat customer. That is expected. Page on that and you train the team to ignore gates. Test the grain you claim, not every id column.
Trace it step by step
Default duplicated() marks extras after the first copy. That is the duplicate_count this lesson wants. keep=False would count every row that participates in a clash, including the first. Pick one definition and stick to it in the suite.
A failing unique check is only an incident if you claimed that column as the grain.
| Column | Claimed grain? | What unique() should say |
|---|---|---|
| orders.order_id | Yes: one row per order | Pass (duplicate_count 0) |
| events.event_id | No: late replay allowed | Fail (extras exist) |
| orders.user_id | No: customers reorder | Fail, and the test is wrong |
SQL wrote this as GROUP BY order_id HAVING COUNT(*) > 1. dbt named it unique. Pandas schema-contracts asserted is_unique. You return a count so a suite can keep going past the first failure.
- duplicate_count = int(df[col].duplicated().sum()).
- success is duplicate_count == 0.
- Print expect_column_unique on df_orders.order_id.
- Project order_id into result.
Sample prints events first so you see a False, then orders so you see a True. The exercise grades the orders call.
- duplicated() without keep=False counts extras, not the whole clash group.
- int() the numpy count, same as isna().sum().
- Never unique-check a foreign key that is allowed to repeat.
Print the dict. Do not assign it to result.
def expect_column_unique(df, col):
duplicate_count = int(df[col].duplicated().sum())
return {"success": duplicate_count == 0, "duplicate_count": duplicate_count}
print(expect_column_unique(df_orders, "order_id"))
result = df_orders.loc[:, ["order_id"]]Events are the failing lane on purpose. Late ingest replays a slice. A unique gate on event_id would be the wrong contract unless you declared that grain and forbade replays.
def expect_column_unique(df, col):
duplicate_count = int(df[col].duplicated().sum())
return {"success": duplicate_count == 0, "duplicate_count": duplicate_count}
print("events.event_id", expect_column_unique(df_events, "event_id"))
print("orders.order_id", expect_column_unique(df_orders, "order_id"))
result = df_orders.loc[:, ["order_id"]]duplicated() vs keep=False
Default duplicated() marks extras after the first copy. keep=False marks every row in a clash, including the first. Mix them in one suite and the counts will not match the incident writeup.
GROUP BY HAVING COUNT(*) > 1
You wrote this hunt in SQL. dbt's unique test compiles to it. pandas duplicated().sum() is the extra-row count. The grain question is the same one the SQL track asked: one row per what?
Same table, next cut
Check uniqueness on the grain you claimed. orders.order_id should pass. events.event_id fails on purpose so you see a non-zero duplicate_count.
Input: one row per order_id. user_id repeats (customers reorder).
| order_id | user_id |
|---|---|
| ORD-0001 | U-9 |
| ORD-0002 | U-3 |
| ORD-0003 | U-9 |
def expect_column_unique(df, col):
duplicate_count = int(df[col].duplicated().sum())
return {"success": duplicate_count == 0, "duplicate_count": duplicate_count}
print("orders", expect_column_unique(df_orders, "order_id"))
print("events", expect_column_unique(df_events, "event_id"))
result = df_orders.loc[:, ["order_id"]]orders prints success True with duplicate_count 0. events prints success False because late ingest replays a slice of event_id values.
Output: a failing unique check is only an incident if you claimed that column as the grain.
| column | claimed grain? | duplicate_count |
|---|---|---|
| orders.order_id | yes | 0 |
| events.event_id | no (replay allowed) | > 0 |
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 not df[col].is_unique?
That is a boolean. This track wants a count you can log. is_unique is fine for an assert in schema-contracts. Gates report duplicate_count so you know how bad the clash is.
Are two rows with the same order_id and different statuses duplicates?
Yes for this gate. The column is the grain. If you meant a different grain (order_id plus status), say so and test a composite key, which this lesson does not grade.
Why do events duplicate?
Late-arriving facts. Streaming and SQL already showed ingest_time after event_time. Uniqueness on event_id is the wrong expectation unless you deduped to one row per event first.
What comes next
The next lesson bounds the row count so an empty gold table cannot be a green write. Uniqueness does not catch a silent truncate. Row count does.
Practice
Run Sample to see False on events and True on orders. Then complete Exercise: implement expect_column_unique(df, col) returning {"success": bool, "duplicate_count": int}. Call it on df_orders, "order_id" and print the dict. result = df_orders.loc[:, ["order_id"]].
duplicate_count = int(df[col].duplicated().sum()). stdout should include True.
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.