An ETL job copied new Parquet files into a lakehouse table's data directory — why don't queries return them?

## What actually happened The job did its work correctly at the file layer and skipped the table layer entirely. Valid Parquet files now sit in the table's storage path, but no commit was made, so no version of the table lists them. Since a table-format reader builds its scan from the metadata file list rather than from a directory listing, the query result is not a bug — it is the guarantee working. If a listing could add files to a table, every failure mode that table formats were built to eliminate would come straight back. These unreferenced files have a name: **orphan files**. They also arise naturally from failed or killed jobs that wrote data files before their commit, which is why every format ships some form of orphan cleanup. ## Why this is worse than "the rows are missing" Three consequences follow, in increasing severity. First, the rows are absent from query results while consuming storage you pay for. Second, the table's own bookkeeping — row counts, per-file statistics, snapshot sizes — is unaffected and therefore *correct about the table but wrong about the folder*, which makes the discrepancy hard to spot from the table side. Third, and the real hazard: cleanup of unreferenced files, run on a retention threshold, will delete them. The team then loses data that was never in the table, often weeks later, with no obvious link back to the ETL job that wrote it. Orphan cleanup is also the reason these jobs use a conservative age threshold: a file written moments ago by a legitimate in-flight writer is indistinguishable from an orphan, so removing recent files can destroy a commit that was about to succeed. ## The mirror-image failure The symmetric mistake is deleting files directly — a tidy-up script, a lifecycle rule expiring objects by age, a manual `rm` on a partition prefix. Metadata still references those paths, so queries fail loudly with file-not-found rather than returning fewer rows. That is arguably the better failure because it is visible, but it is harder to repair: you must roll the table back to a version whose files still exist, or rewrite the affected metadata. Storage lifecycle rules are a particularly nasty version of this, because they act on their own schedule and delete files that older versions still need for time travel. ## How to fix it properly The correct write path is always through the table format: the engine's insert or merge, or the format's write API, so that data files and the metadata commit that registers them are produced together. When the files genuinely already exist and re-writing them is wasteful — a bulk import, a hand-off from an external producer — every major format provides a supported way to adopt existing data files into a table. That path is not a copy: it reads each file's footer to collect schema and statistics, checks compatibility, and then writes a real commit referencing the existing paths. That is the crucial difference from `cp`: a commit happens and statistics are recorded, so planning can prune the new files like any others. ## Preventing recurrence - **Lock the prefix.** Grant write permission on the table's storage path only to the identities that write through the table format. Direct writers should get a permission error, not a silent no-op. - **Never point a lifecycle rule at a managed table's data prefix.** Retention is the table format's job, driven by version expiry, not by object age. - **Give external producers a landing zone.** Let them write wherever they like outside the table, then ingest with a real commit. - **Detect drift.** Run the orphan-file scan in a report-only mode periodically; a sudden pile of unreferenced files in a table prefix is the signature of a bypassing writer, and finding it before the deletion window closes is what saves the data. ## The one-sentence answer "The table is its metadata, not its directory — the files were never committed, so they are orphans: invisible now and deletable later." Everything else follows from that.