A file in the wrong folder gives wrong answers, silently

Break it on purposeNeeds Python with DuckDB

Problem

Hive-style partitioning stores a column's value in the folder name (d=2025-03-01/) instead of in the file. The engine trusts the folder: a file copied into the wrong folder is read as belonging to that folder's date, and nothing checks it, because the file itself does not say otherwise. Storing the value inside the file as well gives a second channel to compare against.

With DuckDB, write a Hive-partitioned Parquet table, copy the Parquet file for d=2025-03-01 by hand into d=2025-03-02/, and read the table grouped by d. Then write the data with the date also stored inside the file (d_in_file) and count rows where the folder's date and the file's date differ.

The question. What does the table report after the copy, and does storing the date inside the file catch it?

Predict

A day's file is copied into the wrong date folder. What does the table report?

An error: the dates disagreeThe rows under the wrong date, with no errorThe rows under their own date

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

a-file-in-the-wrong-folder.py47 linesDownload

View the code

a-file-in-the-wrong-folder.py

# /// script
# requires-python = ">=3.10"
# dependencies = ["duckdb"]
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""A file in the wrong folder.

Writes a Hive-partitioned Parquet table with DuckDB, copies one day's file by
hand into the next day's folder, and reads the table back: the copied row is
reported under the folder's date, with no error. Then writes the same data
with the date also stored inside the file and counts the mismatches between
the folder's date and the file's own.
"""
import argparse, glob, json, os, shutil, tempfile
import duckdb

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--days", type=int, default=3, help="Days of orders.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

def build(root, with_inner):
    c = duckdb.connect()
    inner = ", d AS d_in_file" if with_inner else ""
    c.execute(f"""COPY (SELECT i AS id, DATE '2025-03-01' + (i % {args.days})::INT AS d, 10 AS amount{inner} FROM range({args.days * 10}) t(i))
                  TO '{root}' (FORMAT parquet, PARTITION_BY (d))""")
    src = glob.glob(os.path.join(root, "d=2025-03-01", "*.parquet"))[0]
    shutil.copy(src, os.path.join(root, "d=2025-03-02", "copied-by-hand.parquet"))       # the mistake
    return c

work = tempfile.mkdtemp()
a = os.path.join(work, "orders"); c = build(a, False)
totals = [{"d": str(d), "rows": n, "amount": s} for d, n, s in c.execute(
    f"SELECT d, count(*), sum(amount) FROM read_parquet('{a}/*/*.parquet', hive_partitioning=true) GROUP BY d ORDER BY d").fetchall()]
b = os.path.join(work, "orders_checked"); c = build(b, True)
mismatch = c.execute(f"""SELECT count(*) FROM read_parquet('{b}/*/*.parquet', hive_partitioning=true) WHERE d <> d_in_file""").fetchone()[0]
files_bad = c.execute(f"""SELECT count(DISTINCT filename) FROM read_parquet('{b}/*/*.parquet', hive_partitioning=true, filename=true) WHERE d <> d_in_file""").fetchone()[0]
summary = {"mismatched_rows": mismatch, "mismatched_files": files_bad, "expected_rows_per_day": 10, "duckdb": duckdb.__version__}
for r in totals: print(f"d = {r['d']}: {r['rows']} rows, amount {r['amount']}  (true: 10 rows, 100)")
print(f"with the date also inside the file: {mismatch} rows in {files_bad} file(s) whose folder date differs from their own")
shutil.rmtree(work)

if args.json:
    with open(args.json, "w") as f:
        json.dump({"summary": summary, "tables": {"totals": totals}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.
Terminal
```
uv run a-file-in-the-wrong-folder.py
uv run a-file-in-the-wrong-folder.py --help     # every knob
```

Knobs
Knob · default	What it changes
--days3	Days of orders.

Our run

The table after the copy (each day truly has 10 rows, 100)

The table after the copy (each day truly has 10 rows, 100)
d	Rows	Amount
2025-03-01	10	100
2025-03-02	20	200
2025-03-03	10	100

Reference runApple M2 · 8 cores · 8 GB

Output · uv run a-file-in-the-wrong-folder.py

d = 2025-03-01: 10 rows, amount 100  (true: 10 rows, 100)
d = 2025-03-02: 20 rows, amount 200  (true: 10 rows, 100)
d = 2025-03-03: 10 rows, amount 100  (true: 10 rows, 100)
with the date also inside the file: 10 rows in 1 file(s) whose folder date differs from their own

macOS 26.6.2 · Python 3.13.3 · duckdb 1.5.6 · run 2026-10-20

What we found

The copied file's rows were counted under 2 March: that day showed 20 rows and an amount of 200, against 10 and 100, with no error. With the date stored inside the file, a comparison found 10 rows in 1 file whose folder date differed from their own.

Read it

Folder-only partitioning has no verification channel: the path is the only record of the value, so any operation that moves files moves data between partitions. Keeping the partition value inside the file costs a few bytes per row group and turns a silent error into a query that finds it.