skip to content

You must load 200 million rows into a table that already has several secondary indexes, foreign keys and CHECK constraints. How would you sequence the load so it finishes in hours rather than days, and what has to be true at the end before the data can be trusted?

level: seniorimportance: must knowfreq 45%

answer

  1. Per-row enforcement → set-based validation
  2. Load empty/detached, swap in at the end
  3. Sort-based index build beats N random inserts
  4. FK validation = one anti-join, not N probes
  5. Enabled ≠ validated: untrusted constraints hide bad rows and are ignored by the planner

basics

~20 s

Load into an empty table or a staging partition with indexes and foreign keys removed, use the bulk-load API in large batches, then rebuild indexes with sort-based builds and re-add constraints so they are validated by one set-based scan each. Afterwards verify every constraint is in a validated, not merely enabled, state.

solid answer

~60 s

**Sequence.** Load into a fresh table or a detached partition rather than the live one. Drop secondary indexes and foreign keys, disable triggers, keep NOT NULL. Load with the engine's bulk path — not single-row INSERTs — in large batches, sorted by the clustering key, with reduced logging where the engine offers it. Then rebuild indexes (a sort-based build is one pass over sorted data producing dense pages, versus 200 million random index maintenance operations), re-add the constraints, refresh statistics, and swap or attach the table. **Why it wins.** Per-row constraint enforcement becomes set-based validation: an FK check becomes one anti-join instead of 200 million point lookups; a CHECK becomes one scan. **What must be true afterwards.** Every constraint must be in a *validated/trusted* state, not "enabled but not validated" — engines let you add constraints without checking existing rows, and the optimizer will then refuse to reason with them while bad rows sit there undetected. Verify in the catalog, and independently re-run the anti-join and count checks. Also: statistics refreshed, index build actually completed, and the unconstrained window never exposed to live traffic.

code

sql · 17 lines
sql
-- 1. load into an empty, unconstrained clone / detached partition
DROP INDEX ix_fact_customer;
ALTER TABLE fact_stage DROP CONSTRAINT fk_fact_customer;

-- 2. bulk load (engine bulk path, large batches, sorted input)

-- 3. rebuild by sort, then re-add and validate
CREATE INDEX ix_fact_customer ON fact_stage (customer_id);
ALTER TABLE fact_stage
  ADD CONSTRAINT fk_fact_customer FOREIGN KEY (customer_id) REFERENCES customer(id);

-- 4. verify independently -- must return zero rows
SELECT f.customer_id
FROM   fact_stage f
LEFT   JOIN customer c ON c.id = f.customer_id
WHERE  f.customer_id IS NOT NULL AND c.id IS NULL
LIMIT  10;

go deeper

for a junior

Say the essentials: drop indexes and foreign keys, load, then rebuild and re-add them, because per-row checks are what make it slow.

for a middle

Explain why the set-based version is cheaper — sort-based index build, one anti-join per foreign key — and remember to analyze afterwards.

for a senior

Own the operational risks: load into an invisible table or partition, verify constraints ended up validated rather than merely enabled, plan the lock and replication impact, and make the runbook re-runnable after a crash.

for a principal

Frame it as a data-contract question: where the trust boundary sits, whether validation is proven independently of the loader, and how the load window interacts with replication lag, backups and downstream consumers.

## Why per-row enforcement is the wrong shape for a bulk load Constraints are designed for OLTP: verify one row at a time, immediately, under concurrency. A bulk load inverts every one of those assumptions. It is one writer, no concurrency, and the same check repeated 200 million times. At roughly per-row cost: - Each secondary index: a random descent + leaf write + log record. Five indexes = a billion index operations, most of them random I/O once the indexes exceed memory. - Each FK: a probe into a parent index plus a lock entry. Three FKs = 600 million probes and a lock set that, if held in one transaction, may exhaust the lock table. - Each CHECK: cheap, but still 200 million evaluations. Set-based equivalents are dramatically cheaper because they use sequential I/O and sorting: - Building an index from a loaded table is a sort of 200 million keys followed by a bottom-up tree build — sequential reads, sequential writes, and it produces densely packed pages with no fragmentation. - Validating an FK is one anti-join: `child LEFT JOIN parent ... WHERE parent.pk IS NULL`, which the optimizer can execute as a hash join over two sequential scans. - Validating a CHECK is one scan with a predicate. ## The sequence **1. Load somewhere that is not the live table.** The safest shape is loading into a brand-new table (or a partition detached from the live one) that starts empty and unconstrained. This removes the two biggest risks — exposing partially-loaded or unconstrained data to readers, and being unable to abandon a failed load cheaply. **2. Strip the write-path structures.** Drop secondary indexes and foreign keys; disable triggers. Keep NOT NULL and cheap CHECKs — they cost almost nothing and catch garbage early rather than after hours of loading. Whether to keep the primary key depends on whether the load itself needs dedup; if the input is known clean, build it afterwards. **3. Load with the bulk path.** Use the engine's bulk loader / copy interface rather than row-at-a-time INSERTs; commit in large batches instead of per row so log flushes amortize; run multiple parallel streams over disjoint input if the target supports it; sort the input by the clustering/primary key so rows land in physical order and the later index builds start from near-sorted data. Use minimal/reduced logging modes where the engine offers them, understanding the recovery and replication implications. **4. Rebuild indexes.** After the data lands. Build in parallel where possible, sized so sort workspace does not spill unnecessarily. Expect the index build to be a significant fraction of total elapsed time — sometimes larger than the load itself — and to need disk space for both the sort and the finished index. **5. Re-add constraints and validate.** Adding an FK back triggers a validation scan; that is the set-based anti-join you want. If the engine supports adding a constraint as not-yet-validated and validating later with lighter locking, that can be used to control the lock window, but the validation must actually be run. **6. Refresh statistics.** A freshly loaded table with stale or absent statistics produces catastrophic plans on the first queries. Analyze before exposing it. **7. Publish.** Rename/swap the table, or attach the partition. This is the point where consumers see the data, and it should be the first moment they can. ## What must be true afterwards — the part candidates forget The dangerous end state is a constraint that exists but was never checked against the loaded rows. Every major engine has a way to add a constraint without validating existing data (variously spelled *not valid*, *novalidate*, *nocheck*, *with check option skipped*). It is a legitimate tool for controlling lock time, but a constraint in that state has two costs: 1. **Bad rows may exist and nothing will tell you.** The constraint only governs future writes. 2. **The optimizer stops trusting it.** Query planners use validated constraints as facts — to prune partitions, to eliminate impossible predicates, to prove a join does not duplicate rows. An untrusted constraint is ignored, and plans quietly get worse. So the completion checklist is: - Query the catalog for any constraint on the loaded table not marked validated/trusted, and fix it. - Independently re-run the integrity queries yourself: an anti-join per FK, a count of rows violating each CHECK, a duplicate count per unique key. Trust but verify — this catches the case where a constraint was never re-created at all after the load. - Confirm indexes exist and are valid; interrupted index builds can leave behind unusable or invalid index objects. - Compare row counts and a checksum/aggregate against the source. - Confirm statistics were refreshed. ## Risks to name unprompted - **The unconstrained window.** While constraints are off, nothing prevents bad data. Doing the load on a separate table or a detached partition confines that window to data nobody can see. - **Crash mid-load.** Recovery leaves you with a table missing its indexes and constraints; your runbook must be idempotent and re-runnable from a known state. - **Replication and log volume.** Bulk loads flood the replication stream; replicas can lag by hours. Minimal-logging modes may make a replica or a backup unusable until a fresh base backup is taken. - **Space.** Index rebuild needs sort space plus the old and new index simultaneously in some strategies. - **Validation is not free either.** Re-adding an FK scans the child table and probes the parent; on a huge table that scan is measured in minutes, and it takes locks. Plan for it rather than being surprised. ## The compressed answer "Turn per-row enforcement into set-based validation: load into an empty, unconstrained table, then build indexes by sort, re-add constraints so each one validates in a single scan, analyze, and swap it in — then prove every constraint is validated and re-run the integrity queries myself rather than trusting that the re-enable step worked."

  • Why is rebuilding an index after the load faster than maintaining it during the load?
    Maintaining it during the load means one random descent, leaf write and log record per row, with page splits leaving pages partly full. Rebuilding is a sort followed by a bottom-up bulk build: sequential reads and writes, one pass, and densely packed leaf pages with no fragmentation. The difference is random versus sequential I/O and per-row versus per-batch logging.
  • What is the danger of re-adding a foreign key in a not-validated state and never validating it?
    The constraint governs future writes only, so any bad rows loaded while it was absent remain and nothing reports them. Worse, the optimizer treats an unvalidated constraint as untrustworthy and refuses to use it for reasoning such as partition pruning or join elimination, so plans silently degrade. It should be a temporary state used to shorten a lock window, followed by an explicit validation.
  • How do you avoid exposing readers to the window in which constraints are disabled?
    Do the load somewhere invisible: a fresh table you rename in at the end, or a partition detached from the live table that you validate and then attach. That way the only object without constraints is one no query can see, and a failed load is abandoned by dropping it rather than by repairing live data.

saying these in an interview costs you the question

  • Disabling constraints on the live table and loading directly into it, exposing readers to unconstrained data
  • Believing the engine automatically re-validates existing rows when a constraint is re-enabled
  • Forgetting to refresh statistics, then blaming the engine for bad plans on the freshly loaded table
  • Assuming index rebuild time is negligible compared with the load
  • Ignoring the replication and backup impact of minimal-logging bulk load modes
  • Row-at-a-time INSERTs inside one giant transaction as the 'fast' path

context

resolving links…