polars join key dtype mismatch duplicate rows

TL;DR

Cast both join keys to the same dtype before the join, and check key uniqueness on the side you expect to be unique. polars joins are strict about dtypes: an Int64 column and a String column never match, and joining on non-unique keys multiplies rows instead of erroring.

polars join key dtype mismatch duplicate rows

Use this when

• polars raises a dtype or SchemaError when you call .join()
• The joined frame has far more rows than either input
• Keys look the same when printed but produce zero matches

Not for this skill when

• You are joining in pandas, use the pandas merge skill instead
• The join is correct but slow, thats a performance problem
• You need nearest-timestamp matching, thats an asof join

Steps

1. Inspect both key columns' dtypes. This catches most failures immediately:

print(df_left.schema)
print(df_right.schema)
print(df_left["user_id"].dtype, df_right["user_id"].dtype)

Expected output: the two dtypes side by side, e.g. Int64 vs String. Different dtypes never join.

1. Cast both keys to one common dtype before joining:

df_left = df_left.with_columns(pl.col("user_id").cast(pl.String))
df_right = df_right.with_columns(pl.col("user_id").cast(pl.String))
joined = df_left.join(df_right, on="user_id", how="inner")
print(joined.height)

Expected output: a nonzero row count and no error. If the string side contains non-numeric junk and you want numbers, use strict=False in the cast and then check for nulls.

1. Normalize string keys the same way on both sides:

df_left = df_left.with_columns(
    pl.col("user_id").str.strip_chars().str.to_lowercase()
)

Expected output: keys like "ABC " and "abc" become identical. Repeat on the right frame before joining.

1. If the row count exploded, check key uniqueness per side:

print(df_left["user_id"].n_unique(), df_left.height)
print(df_right["user_id"].n_unique(), df_right.height)

Expected output: for a many-to-one join, one side's unique count equals its height. If neither side is unique, you have a many-to-many join and the fanout is expected, dedupe first.

1. Diagnose which rows failed to match with an anti join:

unmatched = df_left.join(df_right, on="user_id", how="anti")
print(unmatched.height)
print(unmatched.head())

Expected output: the rows from the left frame that had no match, so you can see whether the problem is dirty keys on one side or both.

Variant phrasings

polars join returns empty dataframe but keys match

Almost always a dtype mismatch (step 1) or hidden whitespace (step 3). Check dtypes before anything else.

polars SchemaError when joining on datetime keys

Timezone-aware and naive datetimes are different dtypes in polars. Convert both with .dt.replace_time_zone() or strip timezones consistently.

polars join duplicate rows after fixing dtypes

The dtype error was hiding a fanout problem. Run step 4; the keys are probably not unique on the side you assumed.

Why it happens

polars does no implicit casting on join keys, unlike some SQL engines. Int64 vs String, or Datetime with different timezones, simply do not match, and the join either errors or returns zero rows. Separately, polars joins are relational: joining on non-unique keys produces the cartesian product of the duplicates, which looks like mysterious row multiplication.

Edge cases

• Categorical keys must share the same categories on both sides, cast to String to sidestep.
• Joining on multiple keys: every key must match in dtype, check all of them.
• null keys never match in an inner join, count them with .null_count() first.
• Works the same on LazyFrames, but cast with .with_columns() before .collect() so the schema is fixed early.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_kFp4GX6IWl1DnPN5Au7xHw