Unit 11.02: Checking dtypes and row counts after loading

Unit ID: SQL-M11-U03 - Estimated active time: 13-16 minutes Objective: verify a DataFrame matches the query that produced it, before analysing it.

The boundary is where types change silently

df = con.execute("SELECT status, COUNT(*) AS orders, SUM(order_total) AS revenue "
                 "FROM orders GROUP BY status").df()
print(df.dtypes)
# status         str
# orders       int64
# revenue    float64

order_total is DECIMAL(12,2) in the database and arrives as float64 in pandas. Decimal is exact; float is not. For these values the totals agree:

sql_total = con.execute("SELECT SUM(order_total) FROM orders").fetchone()[0]
df_total  = con.execute("SELECT order_total FROM orders").df()["order_total"].sum()
print(sql_total, df_total, float(sql_total) == df_total)
# 2701463.00 2701463.0 True

They match here. On larger money datasets, repeated float addition can drift by small amounts - which is why financial totals belong in SQL, where the decimal type is preserved, rather than being re-summed in pandas.

The two checks to run on every load

n_sql = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
full  = con.execute("SELECT order_id, order_total FROM orders").df()

print(n_sql, len(full), n_sql == len(full))
# 1000 1000 True

Row count matching confirms nothing was lost in transit. Printing dtypes confirms nothing was silently converted. Together they take two lines and catch a whole category of confusing downstream errors.

NULL becomes something else

A SQL NULL arrives as None, NaN, or NaT depending on the column type - and NaN is a float, so a NULL in an integer column silently converts the whole column to float:

df = con.execute("SELECT test_id, score FROM tests").df()
print(df["score"].isna().sum())   # 2

The two NULL scores from Module 3 are still missing, now as NaN. df["score"].mean() skips them, just as AVG did - same denominator problem, different language.

Practice

Load feedback into a DataFrame and confirm the missing ratings survived the transfer.

fb = con.execute("SELECT id, rating FROM feedback").df()
print(len(fb), fb["rating"].isna().sum(), fb["rating"].mean())
# 500 60 3.045454545454545

Takeaway

Print row count and dtypes immediately after every load. Decimal becomes float and NULL becomes NaN, and neither conversion announces itself.

---

Mark complete