Quick Start

Whitespace

Trailing and leading whitespace is the most common reason keys fail to match. A column exported from Excel might contain "London " alongside "London", and R treats those as distinct values.

sales <- data.frame(
  city = c("London", "Paris ", " Berlin", "Tokyo"),
  revenue = c(500, 300, 450, 600),
  stringsAsFactors = FALSE
)

cities <- data.frame(
  city = c("London", "Paris", "Berlin", "Tokyo", "Madrid"),
  country = c("UK", "France", "Germany", "Japan", "Spain"),
  stringsAsFactors = FALSE
)

join_spy(sales, cities, by = "city")
#> 
#> ── Join Diagnostic Report ──────────────────────────────────────────────────────
#> Join columns: city
#> 
#> ── Table Summary ──
#> 
#> Left table: Rows: 4 Unique keys: 4 Duplicate keys: 0 NA keys: 0
#> Right table: Rows: 5 Unique keys: 5 Duplicate keys: 0 NA keys: 0
#> 
#> ── Match Analysis ──
#> 
#> Keys in both: 2
#> Keys only in left: 2
#> Keys only in right: 3
#> Match rate (left): "50%"
#> 
#> ── Issues Detected ──
#> 
#> ! Left column 'city' has 2 value(s) with leading/trailing whitespace
#> ℹ 2 near-match(es) found (e.g., 'Paris ' ~ 'Paris', ' Berlin' ~ 'Berlin') - possible typos?
#> 
#> ── Expected Row Counts ──
#> 
#> inner_join: 2
#> left_join: 4
#> right_join: 5
#> full_join: 7

The report flags the whitespace problems and shows which values are affected. "Paris " and " Berlin" both fail to match, dropping the match rate from 100% to 50%. The match rate is computed on the left table’s unique keys: of the four distinct cities in sales, two find a counterpart in cities. The damaged keys also surface a second way, as near matches: "Paris " sits one edit away from "Paris", so the report lists the pair as a possible typo even before we know the cause is whitespace.

Whitespace problems compound with multi-column keys:

sales2 <- data.frame(
  city = c("London", "Paris ", "Berlin"),
  district = c("West ", "Central", " Mitte"),
  revenue = c(500, 300, 450),
  stringsAsFactors = FALSE
)

districts <- data.frame(
  city = c("London", "Paris", "Berlin"),
  district = c("West", "Central", "Mitte"),
  pop = c(200000, 350000, 180000),
  stringsAsFactors = FALSE
)

join_spy(sales2, districts, by = c("city", "district"))
#> 
#> ── Join Diagnostic Report ──────────────────────────────────────────────────────
#> Join columns: city, district
#> 
#> ── Table Summary ──
#> 
#> Left table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> Right table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> 
#> ── Match Analysis ──
#> 
#> Keys in both: 0
#> Keys only in left: 3
#> Keys only in right: 3
#> Match rate (left): "0%"
#> 
#> ── Issues Detected ──
#> 
#> ! Left column 'city' has 1 value(s) with leading/trailing whitespace
#> ℹ 1 near-match(es) found (e.g., 'Paris ' ~ 'Paris') - possible typos?
#> ! Left column 'district' has 2 value(s) with leading/trailing whitespace
#> ℹ 2 near-match(es) found (e.g., 'West ' ~ 'West', ' Mitte' ~ 'Mitte') - possible typos?
#> 
#> ── Per-Column Breakdown ──
#> 
#> city: "66.7%" match rate (2/3)
#> district: "33.3%" match rate (1/3)
#> ℹ Lowest match rate: district
#> 
#> ── Expected Row Counts ──
#> 
#> inner_join: 0
#> left_join: 3
#> right_join: 3
#> full_join: 6

London fails on the district column, Paris on the city column. A row needs all key columns to match, so a single trailing space in any column breaks the join. For composite keys the report adds a per-column breakdown: each key column gets its own match rate, and the column with the lowest rate is called out as the likely culprit. Here the district column matches worse than the city column, which tells us where to look first. That breakdown lives in the report as multicolumn_analysis, with the weakest column named in problem_column.

A Quick Gate: key_check()

For a pass/fail check without the full report, we can use key_check():

key_check(sales, cities, by = "city")
#> ! Key check found 1 issue(s):
#> ✖ Left table column 'city' has whitespace issues (2 values)

key_check() runs the duplicate, NA, whitespace, and case checks (a subset of what join_spy() examines) and condenses the answer to a single logical, returned invisibly. With warn = TRUE (the default) it also prints which issues it found, one line per issue. Setting warn = FALSE silences the printing entirely, which is the mode we want inside scripts:

ok <- key_check(sales, cities, by = "city", warn = FALSE)
ok
#> [1] FALSE

Because the return value is a plain logical, it slots into stopifnot() or conditional logic. A line like stopifnot(key_check(x, y, by = "id", warn = FALSE)) turns key quality into a hard precondition: the script halts at the join site instead of producing a silently wrong result.

Keys with Different Names

Often the key columns carry different names in the two tables: the orders table calls it id, the CRM calls it customer_id. Renaming a column just to run a diagnostic is busywork, so every joinspy function that takes by also accepts the named-vector form familiar from dplyr:

sales_reps <- data.frame(
  id = c("C1", "C2", "C3"),
  total = c(100, 250, 175),
  stringsAsFactors = FALSE
)

crm <- data.frame(
  customer_id = c("C1", "C2", "C4"),
  owner = c("Dana", "Eli", "Fay"),
  stringsAsFactors = FALSE
)

join_spy(sales_reps, crm, by = c("id" = "customer_id"))
#> 
#> ── Join Diagnostic Report ──────────────────────────────────────────────────────
#> Join columns: id = customer_id
#> 
#> ── Table Summary ──
#> 
#> Left table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> Right table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> 
#> ── Match Analysis ──
#> 
#> Keys in both: 2
#> Keys only in left: 1
#> Keys only in right: 1
#> Match rate (left): "66.7%"
#> 
#> ── Expected Row Counts ──
#> 
#> inner_join: 2
#> left_join: 3
#> right_join: 3
#> full_join: 4

In the report header, the mapping shows as id = customer_id, and every check runs on id in the left table against customer_id in the right. The same spelling works in key_check(), join_repair(), join_strict(), and the wrappers, so a pipeline that joins on mismatched names never needs an intermediate rename step just to get diagnostics.

Case Mismatches

A CRM might store "ACME" while the billing system stores "Acme", and R treats these as distinct strings.

invoices <- data.frame(
  company = c("ACME", "globex", "Initech", "UMBRELLA"),
  amount = c(1200, 800, 950, 1500),
  stringsAsFactors = FALSE
)

vendors <- data.frame(
  company = c("Acme", "Globex", "Initech", "Umbrella"),
  sector = c("Manufacturing", "Logistics", "Software", "Biotech"),
  stringsAsFactors = FALSE
)

join_spy(invoices, vendors, by = "company")
#> 
#> ── Join Diagnostic Report ──────────────────────────────────────────────────────
#> Join columns: company
#> 
#> ── Table Summary ──
#> 
#> Left table: Rows: 4 Unique keys: 4 Duplicate keys: 0 NA keys: 0
#> Right table: Rows: 4 Unique keys: 4 Duplicate keys: 0 NA keys: 0
#> 
#> ── Match Analysis ──
#> 
#> Keys in both: 1
#> Keys only in left: 3
#> Keys only in right: 3
#> Match rate (left): "25%"
#> 
#> ── Issues Detected ──
#> 
#> ! 3 key(s) would match if case-insensitive (e.g., 'ACME' vs 'Acme')
#> ℹ 1 near-match(es) found (e.g., 'globex' ~ 'Globex') - possible typos?
#> 
#> ── Expected Row Counts ──
#> 
#> inner_join: 1
#> left_join: 4
#> right_join: 4
#> full_join: 7

Only "Initech" matches exactly; the other three would be lost in a standard join. The case-mismatch check compares the two key sets after lowercasing and reports every pair that would match if case were ignored, with an example pair quoted in the message. Fixing this requires choosing a direction (lowercase everything, or uppercase everything), which is exactly what the standardize_case argument of join_repair() does in the next section.

Encoding and Invisible Characters

Two strings can render identically in the console but differ at the byte level: a non-breaking space (\u00A0) versus a regular space, a zero-width space from PDF copy-paste.

# Simulate invisible character contamination
raw_ids <- data.frame(
  product_id = c("SKU-001", "SKU-002", paste0("SKU-003", "\u200B")),
  batch = c("A", "B", "C"),
  stringsAsFactors = FALSE
)

clean_ids <- data.frame(
  product_id = c("SKU-001", "SKU-002", "SKU-003"),
  warehouse = c("East", "West", "North"),
  stringsAsFactors = FALSE
)

join_spy(raw_ids, clean_ids, by = "product_id")
#> 
#> ── Join Diagnostic Report ──────────────────────────────────────────────────────
#> Join columns: product_id
#> 
#> ── Table Summary ──
#> 
#> Left table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> Right table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> 
#> ── Match Analysis ──
#> 
#> Keys in both: 2
#> Keys only in left: 1
#> Keys only in right: 1
#> Match rate (left): "66.7%"
#> 
#> ── Issues Detected ──
#> 
#> ! Left column 'product_id' has encoding issues (invisible chars or mixed encoding)
#> ℹ 3 near-match(es) found (e.g., 'SKU-003​' ~ 'SKU-003', 'SKU-003​' ~ 'SKU-001', 'SKU-003​' ~ 'SKU-002') - possible typos?
#> 
#> ── Expected Row Counts ──
#> 
#> inner_join: 2
#> left_join: 3
#> right_join: 3
#> full_join: 4

The zero-width space appended to "SKU-003" is invisible when printed but prevents the match. joinspy scans for the usual suspects: zero-width space (U+200B), zero-width non-joiner (U+200C), zero-width joiner (U+200D), byte order mark (U+FEFF), and non-breaking space (U+00A0). Columns that mix declared encodings are flagged as well. These characters typically arrive via copy-paste from PDFs or web pages, or from files saved with a byte order mark, so an encoding flag is often a hint about an upstream pipeline step worth checking.

Near Matches

When an unmatched key sits within an edit distance of two from a key on the other side, the report lists the pair as a possible typo. Keys shorter than three characters are skipped to avoid false positives.

batches_x <- data.frame(
  batch = c("LOT-A100", "LOT-B200", "LOT-C300"),
  yield = c(0.92, 0.88, 0.95),
  stringsAsFactors = FALSE
)

batches_y <- data.frame(
  batch = c("LOT-A100", "LOT-B200", "LOT-C30O"),  # final character is a letter O
  supplier = c("North", "East", "West"),
  stringsAsFactors = FALSE
)

join_spy(batches_x, batches_y, by = "batch")
#> 
#> ── Join Diagnostic Report ──────────────────────────────────────────────────────
#> Join columns: batch
#> 
#> ── Table Summary ──
#> 
#> Left table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> Right table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> 
#> ── Match Analysis ──
#> 
#> Keys in both: 2
#> Keys only in left: 1
#> Keys only in right: 1
#> Match rate (left): "66.7%"
#> 
#> ── Issues Detected ──
#> 
#> ℹ 3 near-match(es) found (e.g., 'LOT-C300' ~ 'LOT-C30O', 'LOT-C300' ~ 'LOT-A100', 'LOT-C300' ~ 'LOT-B200') - possible typos?
#> 
#> ── Expected Row Counts ──
#> 
#> inner_join: 2
#> left_join: 3
#> right_join: 3
#> full_join: 4

"LOT-C300" and "LOT-C30O" differ by a single character, a digit zero against a letter O, which is the kind of error manual data entry produces. Near matches carry severity "info" rather than "warning" because the package cannot know whether two similar keys are a typo or two genuinely distinct values. They are surfaced for human review; join_repair() deliberately leaves them alone.

Type Mismatches

Key columns can also disagree about type. An identifier stored as numeric on one side and character on the other is common when one table comes from a database and the other from a CSV reader.

shipments <- data.frame(zip = c(1010, 1020, 1030), n_parcels = c(5, 7, 2))

zones <- data.frame(
  zip = c("1010", "1020", "1030"),
  zone = c("Inner", "Mid", "Outer"),
  stringsAsFactors = FALSE
)

join_spy(shipments, zones, by = "zip")
#> 
#> ── Join Diagnostic Report ──────────────────────────────────────────────────────
#> Join columns: zip
#> 
#> ── Table Summary ──
#> 
#> Left table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> Right table: Rows: 3 Unique keys: 3 Duplicate keys: 0 NA keys: 0
#> 
#> ── Match Analysis ──
#> 
#> Keys in both: 3
#> Keys only in left: 0
#> Keys only in right: 0
#> Match rate (left): "100%"
#> 
#> ── Issues Detected ──
#> 
#> ! Type mismatch: 'zip' is numeric, 'zip' is character - may cause unexpected results
#> 
#> ── Expected Row Counts ──
#> 
#> inner_join: 3
#> left_join: 3
#> right_join: 3
#> full_join: 3

The match analysis still predicts full overlap because R coerces numeric to character when comparing the key sets, and base merge() does the same when joining. dplyr’s join verbs refuse to join a double column to a character column, so the same two tables join under one backend and error under another. The type-mismatch warning catches the disagreement before the backend choice decides the outcome. The report also warns about character-versus-factor pairs, mismatched factor levels, and numeric keys that risk floating-point or large-integer precision loss.

Combining Multiple Issues

Real data rarely has just one problem. join_spy() checks for whitespace, case, and encoding issues in one call and reports them together, grouped by type.

employees <- data.frame(
  dept = c("Sales ", "ENGINEERING", "marketing", paste0("HR", "\u00A0")),
  name = c("Alice", "Bob", "Carol", "David"),
  stringsAsFactors = FALSE
)

departments <- data.frame(
  dept = c("Sales", "Engineering", "Marketing", "HR"),
  budget = c(50000, 80000, 45000, 30000),
  stringsAsFactors = FALSE
)

join_spy(employees, departments, by = "dept")
#> 
#> ── Join Diagnostic Report ──────────────────────────────────────────────────────
#> Join columns: dept
#> 
#> ── Table Summary ──
#> 
#> Left table: Rows: 4 Unique keys: 4 Duplicate keys: 0 NA keys: 0
#> Right table: Rows: 4 Unique keys: 4 Duplicate keys: 0 NA keys: 0
#> 
#> ── Match Analysis ──
#> 
#> Keys in both: 0
#> Keys only in left: 4
#> Keys only in right: 4
#> Match rate (left): "0%"
#> 
#> ── Issues Detected ──
#> 
#> ! Left column 'dept' has 1 value(s) with leading/trailing whitespace
#> ! Left column 'dept' has encoding issues (invisible chars or mixed encoding)
#> ! 2 key(s) would match if case-insensitive (e.g., 'ENGINEERING' vs 'Engineering')
#> ℹ 3 near-match(es) found (e.g., 'Sales ' ~ 'Sales', 'marketing' ~ 'Marketing', 'HR ' ~ 'HR') - possible typos?
#> 
#> ── Expected Row Counts ──
#> 
#> inner_join: 0
#> left_join: 4
#> right_join: 4
#> full_join: 8

Zero matches out of four rows, with the problems grouped by type. The fix differs per category: whitespace is safe to trim automatically, case standardization requires choosing a direction, and encoding issues may indicate deeper pipeline problems. The severity tags help with triage. A "warning" (duplicates, whitespace, NA keys, numeric/character type clashes) changes join results in ways that usually matter. Entries tagged "info", like empty strings, factor level differences, and near matches, deserve a look but are sometimes intentional.

Duplicate Keys

Duplicates don’t prevent matches, but they multiply rows. If "London" appears three times in the left table and twice in the right, we get six rows for London after an inner join.

key_duplicates() returns the offending rows along with a count:

transactions <- data.frame(
  store_id = c("S1", "S1", "S2", "S3", "S3", "S3"),
  day = c("Mon", "Tue", "Mon", "Mon", "Tue", "Wed"),
  amount = c(100, 200, 150, 300, 250, 400),
  stringsAsFactors = FALSE
)

key_duplicates(transactions, by = "store_id")
#>   store_id day amount .n_duplicates
#> 1       S1 Mon    100             2
#> 2       S1 Tue    200             2
#> 4       S3 Mon    300             3
#> 5       S3 Tue    250             3
#> 6       S3 Wed    400             3

The .n_duplicates column maps directly to the row multiplication factor in a join: a key with .n_duplicates of 3 contributes three rows for every matching row on the other side. The keep argument accepts "first", "last", or the default "all". With "first" or "last", only one representative row per duplicated key is returned, which is handy when we want one row per key to inspect rather than the full set:

key_duplicates(transactions, by = "store_id", keep = "first")
#>   store_id day amount .n_duplicates
#> 1       S1 Mon    100             2
#> 4       S3 Mon    300             3

key_duplicates() also takes composite keys (by = c("store_id", "day")), in which case a row counts as a duplicate only when all key columns repeat together. A row whose key contains an NA in any component is treated as having an NA key, matching how joins treat missing key components.

What to do about duplicates depends on what they mean. When the duplicates are legitimate detail rows (multiple transactions per store), the usual move is to aggregate to the key level before joining, or to keep the detail and declare the relationship with join_strict(..., expect = "n:1") so the expansion is explicit. When the duplicates are accidental (a double-loaded file, a botched append), deduplicate first; keep = "first" gives a quick way to pick a representative row, though deciding which row to trust is a data question, and the answer belongs in the script rather than in a silent default.

print(), summary(), and plot()

Three S3 methods cover the common ways of consuming a report. print() produces the full console report we have been reading all along. summary() flattens the headline numbers into a data frame with one metric per row, which is the form to keep when collecting diagnostics across many joins:

summary(report)
#>               metric value
#> 1          left_rows   4.0
#> 2         right_rows   5.0
#> 3   left_unique_keys   4.0
#> 4  right_unique_keys   5.0
#> 5       keys_matched   2.0
#> 6     keys_left_only   2.0
#> 7    keys_right_only   3.0
#> 8         match_rate   0.5
#> 9             issues   2.0
#> 10   inner_join_rows   2.0
#> 11    left_join_rows   4.0
#> 12   right_join_rows   5.0
#> 13    full_join_rows   7.0

summary() also takes a format argument: "text" prints aligned key-value pairs, and "markdown" prints a table ready to paste into a report or an issue tracker.

summary(report, format = "markdown")
#> | Metric | Value |
#> |--------|-------|
#> | left_rows | 4 |
#> | right_rows | 5 |
#> | left_unique_keys | 4 |
#> | right_unique_keys | 5 |
#> | keys_matched | 2 |
#> | keys_left_only | 2 |
#> | keys_right_only | 3 |
#> | match_rate | 0.5 |
#> | issues | 2 |
#> | inner_join_rows | 2 |
#> | left_join_rows | 4 |
#> | right_join_rows | 5 |
#> | full_join_rows | 7 |

plot() draws the key overlap as a Venn diagram, with the left-only, shared, and right-only key counts in the three regions and the match rate in the subtitle:

plot(report)

The picture makes the diagnosis immediate: two keys live only in sales (the damaged Paris and Berlin), two are shared, and three live only in cities. The figure earns its place when the audience is wider than the analyst: a Venn diagram in a data-quality writeup communicates “half our keys have no counterpart” faster than a table of counts does. The file argument saves the same figure to disk (PNG, SVG, or PDF, chosen by the file extension) and colors controls the two circle colours. The method invisibly returns the three counts, so the numbers behind the figure stay available. For checking report objects in defensive code, is_join_report() tests the class:

is_join_report(report)
#> [1] TRUE

This matters in pipelines that call last_report() (introduced below), which returns NULL when no join has run yet.

Dry Run

We can preview what join_repair() would change with dry_run = TRUE:

messy_left <- data.frame(
  id = c(" A-101", "B-202 ", "c-303", paste0("D-404", "\u200B")),
  score = c(88, 92, 76, 95),
  stringsAsFactors = FALSE
)

messy_right <- data.frame(
  id = c("A-101", "B-202", "C-303", "D-404"),
  label = c("Alpha", "Beta", "Gamma", "Delta"),
  stringsAsFactors = FALSE
)

join_repair(messy_left, messy_right, by = "id", dry_run = TRUE)
#> 
#> ── Repair Preview (Dry Run) ────────────────────────────────────────────────────
#> 
#> ── Left table (x) ──
#> 
#> ℹ id: trimmed whitespace (2), removed invisible chars (1)

The dry run shows how many values will change and what kind of change each column needs, without touching the original data. Running the preview first is worth the extra line whenever the keys carry meaning: case standardization in particular rewrites values, and a dry run confirms it will only rewrite the ones we expect.

Applying Repairs

Drop dry_run (or set it to FALSE) to get back the repaired data frames. When we pass both x and y, the return value is a list with both:

repaired <- join_repair(
  messy_left, messy_right,
  by = "id",
  trim_whitespace = TRUE,
  standardize_case = "upper",
  remove_invisible = TRUE
)
#> ✔ Repaired 4 value(s)

repaired$x$id
#> [1] "A-101" "B-202" "C-303" "D-404"
repaired$y$id
#> [1] "A-101" "B-202" "C-303" "D-404"

Both sides now have clean, uppercase, whitespace-free keys. We can confirm with key_check():

key_check(repaired$x, repaired$y, by = "id")
#> ✔ Key check passed: no issues detected

The diagnose-repair-verify loop is the core workflow: join_spy() names the problems, join_repair() fixes the mechanical ones, and key_check() confirms the keys are clean before the join runs. The non-key columns (score and label here) are never modified, so repairs cannot corrupt the data the join is meant to carry.

Repairing a Single Table

When y is omitted, join_repair() returns the repaired x directly rather than a list. This is the form to use when cleaning a table once, before joining it against several others:

codes <- data.frame(
  code = c(" X1", "X2 ", ""),
  n = 1:3,
  stringsAsFactors = FALSE
)

join_repair(codes, by = "code", empty_to_na = TRUE)
#> ✔ Repaired 3 value(s)
#>   code n
#> 1   X1 1
#> 2   X2 2
#> 3 <NA> 3

The empty_to_na flag converts empty strings to NA. The distinction matters for joins: two empty strings match each other, so a stray "" on both sides quietly joins rows that have no business being paired, while NA keys never match anything. Converting makes the missingness explicit.

What Repair Does Not Fix

join_repair() fixes mechanical string problems: whitespace, case, invisible characters, empty strings. It does not fix typos, semantic mismatches, or different key formats ("USA" vs. "United States"). Typos surface in the report as near matches and need human judgment, since an automated fix could merge keys that are genuinely distinct. Format mismatches need a recoding table. vignette("common-issues") walks through both, alongside the rest of the problem catalogue.

Repair Suggestions from a Report

If we already have a JoinReport from join_spy(), suggest_repairs() generates ready-to-run code that fixes the detected issues:

report <- join_spy(messy_left, messy_right, by = "id")
suggest_repairs(report)
#> 
#> ── Suggested Repairs ───────────────────────────────────────────────────────────
#> x[["id"]] <- trimws(x[["id"]])
#> # Remove invisible characters:
#> x[["id"]] <- gsub("[\u200B\u200C\u200D\uFEFF\u00A0]", "", x[["id"]], perl = TRUE)
#> # Standardize case:
#> x[["id"]] <- tolower(x[["id"]])
#> y[["id"]] <- tolower(y[["id"]])

The generated code calls base R functions (trimws(), tolower(), gsub()) keyed to what the report detected, covering whitespace, case, empty-string, and encoding issues. The snippets are also returned invisibly as a character vector, so they can be written to a script file. This path suits the case where we want the fix recorded as explicit code in the analysis script rather than hidden inside a join_repair() call.

Sampling Large Tables

On tables with millions of rows, the string scans take noticeable time. The sample argument caps how many rows each table contributes to the analysis:

set.seed(7)
big_orders <- data.frame(
  product_id = sample(sprintf("P%04d", 1:5000), 20000, replace = TRUE),
  qty = sample(1:10, 20000, replace = TRUE)
)
catalog <- data.frame(
  product_id = sprintf("P%04d", 1:5000),
  price = round(runif(5000, 1, 100), 2)
)

report_big <- join_spy(big_orders, catalog, by = "product_id", sample = 2000)
report_big$sampling
#> $sampled
#> [1] TRUE
#> 
#> $original_x_rows
#> [1] 20000
#> 
#> $original_y_rows
#> [1] 5000
#> 
#> $sample_size
#> [1] 2000

When sampling kicks in, the report records the original row counts alongside the sample size, and the printed report carries a notice. The trade-off is exactness: match rates and row predictions then describe the sample, so treat them as estimates of the full-table values. Issue detection still works well under sampling, since a systematic problem like trailing whitespace shows up in any reasonably sized sample. A practical division of labour is to sample during interactive exploration, where we mostly want to know whether the keys are clean and roughly how they overlap, and to run the full analysis once before the join that matters, where the exact row prediction is the payoff.

Explaining Row Count Changes

join_explain() takes the result, the two input tables, and the join column, then explains why the row count changed:

tickets <- data.frame(
  event_id = c(1, 2, 2, 3),
  seat = c("A1", "B2", "B3", "C1"),
  stringsAsFactors = FALSE
)

events <- data.frame(
  event_id = c(1, 2, 4),
  name = c("Concert", "Play", "Opera"),
  stringsAsFactors = FALSE
)

result <- merge(tickets, events, by = "event_id")
expl <- join_explain(result, tickets, events, by = "event_id", type = "inner")
#> 
#> ── Join Explanation ────────────────────────────────────────────────────────────
#> 
#> ── Row Counts ──
#> 
#> Left table (x): 4 rows
#> Right table (y): 3 rows
#> Result: 3 rows
#> ℹ Result has 1 fewer rows than left table
#> 
#> ── Why the row count changed ──
#> 
#> ℹ Left table has 1 duplicate key(s) - each match creates multiple rows
#> ℹ Inner join dropped 1 unmatched left rows

The output has two parts. The row-count section states the before and after sizes and flags the direction of the change. The explanation section then decomposes the change into its causes: duplicate keys multiplying rows, unmatched keys being dropped or padded, and NA keys that never match. Usually when a table grows unexpectedly, the answer is a handful of keys with duplicates on one side. Passing type tells the function which join was performed so it attributes dropped rows correctly.

The explanation is also returned invisibly as a list, with the row counts, the difference, per-side duplicate counts, and the matched/left-only/right-only key counts:

expl$diff
#> [1] -1
expl$left_only
#> [1] 1

The result lost one row relative to tickets and one left key had no match: the inner join dropped event 3. Capturing the list lets a pipeline assert on the decomposition, for example failing the run whenever left_only is nonzero.

Before/After Comparison

join_diff() compares a data frame before and after a join to show which rows were added, dropped, or preserved:

before <- data.frame(
  id = 1:4,
  value = c("a", "b", "c", "d"),
  stringsAsFactors = FALSE
)

lookup <- data.frame(
  id = c(2, 3, 4, 5),
  extra = c("X", "Y", "Z", "W"),
  stringsAsFactors = FALSE
)

after <- merge(before, lookup, by = "id", all = TRUE)
join_diff(before, after, by = "id")
#> 
#> ── Join Diff ───────────────────────────────────────────────────────────────────
#> 
#> ── Dimensions ──
#> 
#> Before: 4 rows x 2 columns
#> After: 5 rows x 3 columns
#> Change: "+1" rows, "+1" columns
#> 
#> ── Column Changes ──
#> 
#> ✔ Added: extra
#> 
#> ── Key Analysis ──
#> 
#> Unique keys before: 4
#> Unique keys after: 5

With all = TRUE nothing is dropped: the row for id = 1 survives with NA in extra, and id = 5 enters from the lookup side. The diff reports one added row, one added column, and one more unique key after the join than before. The by argument is optional; without it the comparison covers dimensions and columns only, and with it join_diff() adds the key analysis.

Here is a second example that highlights row addition from duplicates rather than new keys:

before2 <- data.frame(
  id = c(1, 2, 3),
  value = c("a", "b", "c"),
  stringsAsFactors = FALSE
)

dup_lookup <- data.frame(
  id = c(1, 1, 2, 3),
  tag = c("X", "Y", "Z", "W"),
  stringsAsFactors = FALSE
)

after2 <- merge(before2, dup_lookup, by = "id")
d <- join_diff(before2, after2, by = "id")
#> 
#> ── Join Diff ───────────────────────────────────────────────────────────────────
#> 
#> ── Dimensions ──
#> 
#> Before: 3 rows x 2 columns
#> After: 4 rows x 3 columns
#> Change: "+1" rows, "+1" columns
#> 
#> ── Column Changes ──
#> 
#> ✔ Added: tag
#> 
#> ── Key Analysis ──
#> 
#> Unique keys before: 3
#> Unique keys after: 3
#> Duplicate rows: "+2"

The row count grew from three to four because id = 1 matched two rows in the lookup. No new keys were introduced; the growth came entirely from duplicate expansion, and the key analysis shows it as duplicate rows appearing where there were none. join_diff() distinguishes these two sources of row addition (new keys vs. duplicate expansion). Like join_explain(), it returns its findings invisibly, including the added and removed column names:

d$columns_added
#> [1] "tag"

Quiet Mode and Deferred Reports

In pipelines, printed output can be distracting. With .quiet = TRUE, the report is suppressed and can be retrieved afterward via last_report():

result <- inner_join_spy(patients, labs, by = "patient_id", .quiet = TRUE)

# Later, inspect what happened
rpt <- last_report()
rpt$match_analysis$match_rate
#> [1] 0.6666667

last_report() returns the report from the most recent *_join_spy() call and persists for the duration of the R session. Printing is governed by two arguments: the report prints when verbose = TRUE (the default) and .quiet = FALSE, and either verbose = FALSE or .quiet = TRUE silences it. The diagnostics themselves always run: quiet mode changes what is shown, and the report is still computed, stored for last_report(), attached as an attribute, and written to the log file when logging is enabled.

Cartesian Product Detection

When both tables have duplicates on the same key, the join produces the Cartesian product of those duplicates. Two keys with 100 rows each produce 10,000 rows for that key alone. check_cartesian() warns before this happens:

left <- data.frame(
  group = c("A", "A", "A", "B"),
  x = 1:4,
  stringsAsFactors = FALSE
)

right <- data.frame(
  group = c("A", "A", "B", "B"),
  y = 5:8,
  stringsAsFactors = FALSE
)

cart <- check_cartesian(left, right, by = "group")
#> ✔ No Cartesian product risk (expansion factor: 2x)

The function computes, per matched key, the product of its occurrence counts on the two sides, and sums these into the predicted inner-join size; the expansion factor is that total divided by the larger of the two input row counts. Here key A contributes 3 * 2 = 6 rows and key B contributes 1 * 2 = 2, so the four-row inputs produce eight rows, an expansion factor of 2:

cart$expansion_factor
#> [1] 2
cart$worst_keys
#>   key x_count y_count product
#> A   A       3       2       6
#> B   B       1       2       2

The threshold argument (default 10) sets how large the expansion factor must be before the function reports a risk. A clean lookup join keeps the factor at 1 or below, and pushing it past 10 essentially requires duplicates on both sides of the same key, which is why 10 makes a sensible default boundary between intended expansion and an accident. For a pipeline where every join should be near 1:1, a tighter threshold catches trouble earlier:

check_cartesian(left, right, by = "group", threshold = 1.5)
#> ✖ Cartesian product risk: result will be 2x larger than input
#> 
#> ── Worst offending keys ──
#> 
#> "A": 3 x 2 = 6 rows
#> "B": 1 x 2 = 2 rows

The warning ranks the worst offending keys by their row product, which is the right place to start: in real data, Cartesian blowups usually trace back to a few catch-all keys (an "UNKNOWN" code, an empty-string id) that are heavily duplicated on both sides.

Multi-Table Join Chains

analyze_join_chain() runs diagnostics across a sequence of joins. We provide a named list of tables and a list of join specifications:

orders <- data.frame(order_id = 1:4, customer_id = c(1, 2, 2, 3))
customers <- data.frame(customer_id = 1:3, region_id = c(1, 1, 2))
regions <- data.frame(region_id = 1:2, name = c("North", "South"),
                      stringsAsFactors = FALSE)

chain <- analyze_join_chain(
  tables = list(orders = orders, customers = customers, regions = regions),
  joins = list(
    list(left = "orders", right = "customers", by = "customer_id"),
    list(left = "result", right = "regions", by = "region_id")
  )
)
#> 
#> ── Join Chain Analysis ─────────────────────────────────────────────────────────
#> 
#> ── Step 1: orders + customers ──
#> 
#> Left: 4 rows
#> Right: 3 rows
#> Match rate: 100%
#> Expected result: 4 rows (left join)
#> ! 1 issue(s) detected
#> 
#> ── Step 2: result + regions ──
#> 
#> Left: 4 rows
#> Right: 2 rows
#> Match rate: 100%
#> Expected result: 4 rows (left join)
#> ! 1 issue(s) detected
#> 
#> ── Chain Summary ──
#> 
#> ! Total issues across chain: 2

Each step prints its input sizes, match rate, expected left-join row count, and issue count, followed by a chain-level summary. The special name "result" refers to the output of the previous join, so we can chain as many tables as needed. Because "result" resolves to the actual intermediate data (built with left joins), key diagnostics at step two account for any NAs or key distribution changes introduced by the first join. This matters in star-schema assembly, where a problem in the first join (a duplicate dimension key, say) only becomes visible as row growth two joins later.

The return value is a list with one element per step, each holding the step’s full JoinReport, so any step can be drilled into afterward:

chain[[2]]$report$match_analysis$match_rate
#> [1] 1

Backend Support

All join wrappers and join_strict() accept a backend argument. By default (backend = NULL), joinspy detects the backend from the input class: tibbles use dplyr, data.tables use data.table, plain data frames use base R.

# Force dplyr backend even for plain data frames
left_join_spy(x, y, by = "id", backend = "dplyr")

# Force base R for tibbles
left_join_spy(x, y, by = "id", backend = "base")

The diagnostic layer is backend-agnostic; all string analysis and key checking happens before the join engine is invoked, so reports are identical regardless of backend. The result keeps the class the backend produces (a tibble from dplyr, a data.table from data.table), and extra arguments in ... pass through to the underlying join function, so backend-specific options remain available. When a requested package is missing, joinspy falls back to base R with a warning rather than failing. vignette("backends") covers the dispatch rules, the differences between the engines, and mixed-class inputs.