Not Every Team Can See Every Row

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

hard

Seniority

L7

Problem

A security reviewer found that an engineer had copied confidential HR files straight out of our S3 bucket, a read the query layer never saw. Design the warehouse we build on that S3 data, about 2 TB a day read by 300 analysts, so confidential data reaches only the groups allowed to see it.

Everyone can see the bucket. Not everyone should.

Business requirements

• Application-layer filtering has been bypassed by direct API calls; the storage itself has to enforce who can read which files.
• Reclassifying data after it lands is too late; sensitivity has to be assigned at write time, with routing following the classification.
• Some tables hold both public columns like name and department alongside confidential columns like salary; analysts need the public columns and HR needs all of them.
• Compliance requires a record of every query and file access for years, queryable; today there's no audit trail.

Worked solution and explanation

The trap

This is defense in depth dressed up as a warehouse build. The real skill is holding two access boundaries at the same time: file isolation in storage and column visibility in the engine. Anyone can draw object storage feeding a warehouse. What separates candidates is refusing to let the query engine be the only lock. If all governance lives in warehouse views, one direct GetObject call against the bucket returns the confidential file and the engine never hears about it.

Walk the requirements

Step 1: Classify at write time

The classification step tags each record from a rule, a column match or an upstream tag, and routes it on that tag. If you reclassify after landing, confidential rows sit in the wrong prefix long enough for someone to download them.

Step 2: Enforce at the storage layer

The public, restricted and confidential zones are separate prefixes or buckets. The restricted and confidential zones each carry their own bucket policy that grants read only to their groups' roles. A direct API call from the wrong role fails at storage, whichever query path it came from. This is why the design needs a cold-storage tier at all.

Step 3: Mask columns on mixed tables

An employee table holds name and department next to salary. The column masking policy in front of the governed warehouse shows analysts only the public columns and shows HR all of them, from one table. Hiding columns in the BI tool fails the moment someone queries the base table, so the masking policy sits between the warehouse and every reader group.

Step 4: Keep an audit trail you can query

Query history and storage access logs from every zone land in the audit archive, held in an open table format and retained for the regulatory window. Then 'who read this last March' is a SQL query, not a forensic project.

The shape that fits

Engine-only governance

One prefix with secure views. Access holds only while every request goes through the warehouse, and a direct bucket read skips the whole policy.

Storage plus engine

Bucket policies per zone block direct reads, and column masking in front of the warehouse handles mixed tables. Each layer covers the other's blind spot.

Common follow-up questions

• A new department needs access to a confidential dataset. What changes? (Only policy changes: a grant on the confidential zone's bucket policy and a column masking rule. No data moves, and the grant itself is audited.)
• The classifier misses a confidential row. How do you find who saw it? (Move the row to the correct zone, then query the audit archive for every read of the wrong zone during the exposure window.)