hupden/ projects
blogtools
← blog

The table format kept the IPs I'd scrubbed

#iceberg#privacy#data-engineering#spark

I keep a small local copy of this site's access logs in Apache Iceberg tables, to learn the format on real data. Real access logs mean real client addresses, so the warehouse directory is gitignored and the notebooks have their outputs stripped before every commit.

The stripping isn't about diff noise. It exists because of one notebook cell that lists Iceberg's metadata tables, and one of those tables prints visitor addresses. Nothing leaked; the strip was in place from the first commit, and I checked every committed version of the notebook for stored outputs. But it made me ask the obvious follow-up: if I truncated the addresses in the table itself, the way this site truncates them before archiving, would they actually be gone?

They would not, in three different ways, and the third one is invisible to the check most people would run. Everything below is reproduced on synthetic data, using the documentation address ranges, with Iceberg 1.11 on Spark 3.5.

Copy one: the column statistics

Every Iceberg data file is listed in a manifest along with per-column statistics: value counts, null counts, and a lower and upper bound. The bounds are what let a query planner skip files, and for a string column they are literal values from the data. The .files metadata table decodes them:

SELECT readable_metrics.remote_addr.lower_bound,
       readable_metrics.remote_addr.upper_bound
  FROM logs.files
'198.51.100.23' .. '203.0.113.7'

That's two real-shaped client addresses per file, per column, sitting in metadata. Run that in a notebook, save it, and the addresses are in git, which is the cell I mentioned.

The default metrics mode for strings is truncate(16): bounds are cut to sixteen characters. That sounds like a privacy feature and isn't one. The longest IPv4 address is fifteen characters, so IPv4 always survives whole. IPv6 gets cut, but sixteen characters of an address goes a long way:

'2001:db8:abcd:12' .. '2001:db8:abcd:13'

That's a /64, the size of a single network segment. This site's archive truncates IPv6 to a /48, so the default metadata keeps a whole extra group of that boundary address, past what the policy allows anywhere else. How much survives depends on how the address is written: a compressed address keeps more groups inside sixteen characters than a fully expanded one. (The upper bound is the truncated prefix incremented by one, so it's always a slightly different, equally specific value.) User agents come out as the first sixteen characters of the generic preamble every browser sends, which makes that the one column where sixteen characters really doesn't say much.

Copy two: the snapshots

Now the scrub. A row-level update, truncating every address in place:

UPDATE logs SET remote_addr = CASE
  WHEN remote_addr LIKE '%:%' THEN '2001:db8:abcd::/48'
  ELSE concat(substring_index(remote_addr, '.', 3), '.0/24') END

Query the table and it looks done. Every row reads 203.0.113.0/24 and so on, and .files, which shows the current snapshot, now has truncated bounds.

But an Iceberg write never edits a file. It writes new ones and commits a new snapshot, and the old snapshot keeps pointing at the old files. So the original data is one clause away:

SELECT remote_addr FROM logs VERSION AS OF <first snapshot id>
['203.0.113.7', '198.51.100.23', '2001:db8:abcd:12::1']

Most people who know Iceberg know this part. It's what time travel is, and the answer is equally well known: expire the old snapshots.

CALL system.expire_snapshots('db.logs', TIMESTAMP '<now>', 1)

It reported two data files deleted. .all_data_files, which lists files across every retained snapshot, now shows only the truncated one. And a grep -r for the original addresses across the whole table directory, data and metadata both, found nothing. That looks like a finished scrub.

Copy three: the manifest that recorded the deletion

It isn't finished. Iceberg's manifests are Avro files, and Iceberg writes Avro deflate-compressed by default. A grep over compressed bytes finds nothing whether the value is there or not, so a clean grep proves nothing.

Decoding the Avro blocks properly and searching again found both IPv4 addresses and the IPv6 prefix still on disk, in the manifests of the snapshot that expire_snapshots had just kept.

The reason is how a manifest records a change. When the update replaced the old data files, the new snapshot's manifest kept an entry for each removed file, marked DELETED, and an entry carries the file's full column statistics. Expiry deletes the old data, but this manifest belongs to the snapshot you kept, so it stays. Iceberg will show you, if you ask the metadata table that lists manifest entries as they are, rather than only live files:

SELECT status, readable_metrics.remote_addr.lower_bound,
               readable_metrics.remote_addr.upper_bound
  FROM logs.entries
1 ADDED    '198.51.100.0/24'  .. '203.0.113.0/24'
2 DELETED  '2001:db8:abcd:12' .. '2001:db8:abcd:13'
2 DELETED  '198.51.100.23'    .. '203.0.113.7'

The data files those rows describe no longer exist. Their addresses do.

What finally cleared them was rewriting the manifests, which writes fresh ones containing only live entries, and then expiring again, so the snapshot holding the old manifests can go too:

CALL system.rewrite_manifests('db.logs');
CALL system.expire_snapshots('db.logs', TIMESTAMP '<now>', 1);

After that, .entries showed one EXISTING row with truncated bounds, and the decoded manifests held no original address. The old vN.metadata.json files were still there, and I checked them too: they carry schemas and snapshot summaries, not column values.

Not collecting it in the first place

All of that is cleanup. The better fix is to never write the bounds. Iceberg lets you choose the metrics mode per column:

ALTER TABLE logs SET TBLPROPERTIES (
  'write.metadata.metrics.column.remote_addr' = 'counts',
  'write.metadata.metrics.column.user_agent'  = 'counts'
)

With counts, the bounds come back NULL and the value and null counts stay. The cost is real and worth stating: without bounds, a filter on that column can't skip files, so WHERE remote_addr = ... reads every file. On a table this small that costs nothing measurable. On a large one, it's a trade you make deliberately. Statistics are computed when a file is written, so the setting does nothing for manifests that already exist; those still need the cleanup above.

The general version

A column rewrite in a table format is a write, not an edit. Every value you "removed" is still referenced by history until you expire it, and summarized in metadata that may outlive the expiry. The lesson I keep relearning is the second half of that: the check that said "gone" was a grep, and the format had compressed the evidence out of its reach. A search that can't read the thing it's searching will always come back clean. Before believing a clean result, I check that the same search can find a value I know is there. I seeded these tables with addresses precisely so that the decoded search had something it was required to find, and it did.