Data independence is presented as an unqualified good, yet real systems deliberately let physical concerns leak into logical design and application code. When is breaking data independence the right call, and how do you contain the damage?
answer
- correctness independent, cost never was
- partition key becomes a required predicate
- stickiness ladder: stats < index < clustering < denormalisation < hint/shard key
- measured cost + logical layer exhausted first
- document, test, own, set a review trigger
basics
~20 sBreak it when a physical fact drives a decision the logical layer cannot express and the cost is measured, not assumed — partition keys, clustering-aware access patterns, denormalisation for read cost, sharding keys. Contain it by writing the dependency down, testing it, and giving it an owner and a review trigger.
solid answer
~60 sData independence is an abstraction, and abstractions cost. Legitimate breaks: - **Partitioning leaks into the schema.** Once a table is partitioned, the partition key becomes a de facto required predicate; queries and even the logical model bend around it. - **Clustering-aware modelling.** Choosing a key order so related rows sit together is a physical decision encoded logically. - **Denormalisation** — a maintained counter or a duplicated attribute — changes the logical schema purely to avoid physical cost. - **Sharding keys** become part of the application contract: every access path must carry them. - **Optimizer hints and forced plans** hard-code an access path in the application, the most direct violation of physical independence. The discipline is not to forbid these but to make them **explicit and reversible where possible**: record the physical assumption next to the artefact, cover it with a test that fails when the assumption dies, give it an owner and a re-evaluation trigger, and prefer the least sticky option — an index or statistics change over a hint, a hint over a schema shape only one storage layout supports.
go deeper
Know that physical layout affects performance even though it does not affect results, and that indexes come before schema changes.
Give examples of physical concerns leaking into logical design — partition keys, clustering, denormalisation — and the write-cost trade each carries.
Order the options by stickiness, insist on measurement first, and describe containment: documentation, tests, reconciliation, a narrow data-access layer.
Frame independence as a budget spent deliberately, and set the governance — owners, exit conditions, review triggers — so the system's physical assumptions are known rather than discovered during an incident.
## Why the ideal is not free The ANSI/SPARC model promises that storage decisions do not reach application code and that logical change does not reach applications either. Correctness lives up to that; **cost does not**. A query's meaning is independent of storage; its latency, lock footprint and resource use are entirely dependent on it. Any system with hard performance requirements eventually makes decisions that only make sense given the physical layer, and those decisions surface in places the model says they should not. ## The legitimate breaks **Partition key as a logical obligation.** Partitioning is an internal-level decision, but the moment a large table is partitioned by, say, date, a query without a date predicate scans everything. In effect a physical structure has added a rule to the logical contract: 'always filter by date'. This is a real, common, and usually correct trade. **Clustering and key order.** Choosing the primary key order so that rows read together are stored together — tenant first, then time — is a physical optimisation expressed as a logical design choice. It can be the difference between one page read and thousands. **Denormalisation for read cost.** Storing a maintained count, or duplicating an attribute onto a child row to avoid a join, changes the conceptual schema for purely physical reasons. It buys read cost and pays in write cost and a new invariant to keep true. **Sharding keys in the application contract.** Once data is distributed, routing keys are visible to callers; cross-shard operations are restricted or expensive. Distribution independence is essentially abandoned, deliberately, in exchange for scale. **Hints and forced plans.** Pinning an access path from application code is the most direct break of physical independence. It is sometimes the only tool available when a plan flips under production data and the deadline is now. **Physical-order assumptions.** Relying on incidental row order or on an index existing to make a query tolerable are the accidental versions of the same thing, and the ones that break silently. ## The criteria for deciding A break is justified when all of these hold: 1. **The cost is measured, not assumed.** A profile or plan showing a real problem at real volume, not an intuition. 2. **The logical layer genuinely cannot express it.** Rewriting the query, fixing statistics, or adding an index is always the first move; those preserve independence entirely. 3. **The break is proportionate.** Prefer the least sticky instrument: statistics and indexes are invisible to code; clustering choices are schema-local; denormalisation adds an invariant; hints and shard keys are the stickiest because they are embedded in application code. 4. **It is reversible or has an exit condition.** A hint added for a specific plan regression should have a trigger for removal — an engine upgrade, a data-volume change, a statistics fix. ## Containment The damage from a broken abstraction is not the break, it is the **undocumented** break — a future engineer removes the index, changes the key order, or drops the partition scheme, and something distant fails. - **Write the assumption down where the artefact lives**: a comment on the denormalised column, a note in the migration, a line in the schema documentation naming what depends on it. - **Encode it as a test.** An invariant check that the maintained counter matches the source of truth; a plan assertion for the query that must not scan; a constraint where one is expressible. A dependency covered by a failing test is a managed dependency. - **Give it an owner and a review trigger.** Every hint, every denormalised field, every 'must filter by partition key' rule needs someone accountable and a condition under which it is revisited. - **Keep the blast radius small.** Concentrate physical-aware access in a data-access layer rather than spreading it through business code, so a future change touches one place. - **Prefer database-side expression.** A denormalised value maintained by a trigger or a materialised structure is enforced for every writer; the same value maintained by one application service is bypassed by the next writer that appears. ## The framing that lands Data independence is a budget, not a law. You spend it deliberately for measured wins and account for what you spent. The failure mode senior engineers actually see is not too little independence — it is dependencies that were never written down, so nobody knows which physical facts the system is quietly standing on.
- You add an optimizer hint to fix a plan regression under deadline pressure. What must accompany it?A written reason and an exit condition: which query, which plan it was choosing, what data or statistics condition caused it, and what would make the hint removable — an engine upgrade, corrected statistics, a better index. Plus an owner and a test or plan assertion, otherwise the hint outlives its cause and pins the optimiser to a plan that is wrong for the next data distribution.
- How do you keep a denormalised value from drifting away from its source of truth?Enforce it in the database rather than in one application path: a trigger or maintained structure so every writer, including ad-hoc sessions and jobs, updates it. Then add a periodic reconciliation check that compares the denormalised value against a recomputation and alerts on divergence, because any single-service enforcement is bypassed by the next writer that appears.
It is technical debt in abstraction form: borrowing against the storage layer is fine when the return is measured and the loan is recorded; unrecorded borrowing is what wrecks the system later.
saying these in an interview costs you the question
- Treating data independence as absolute and refusing any physical-aware design regardless of measured cost.
- Adding hints or denormalisation on intuition, before exhausting query, index and statistics fixes.
- Leaving a hint or denormalised field with no owner, no documented reason and no removal trigger.
- Maintaining a denormalised value in one application service and assuming no other writer exists.
- Assuming sharding can be hidden from application code.