Late binding on foreign keys, how strict should staging be?

Start with the execution plan, numbers beat guesses.

Plain English: the system prefers to guess a good-enough plan than the perfect plan, because figuring out the perfect plan would take longer than just running the good-enough one.

Adding unknown-member rows and dropping the strict staging rejection. Should remove the load-order coupling causing our headaches.

Sometimes the real fix is a product change so you stop needing that join at all.

Prefer a staging table plus validation gate before promoting to prod tables.

Strict enforcement at staging tends to create exactly this brittleness. Most warehouse patterns favor loading facts with the FK as-is, even if temporarily 'orphaned,' and handling unresolved references at the dimension layer with a placeholder or unknown-member row rather than blocking the load.

What batch size or interval worked for you at similar scale?

We replaced custom sensors with data contracts and row count checks.

How do you handle backfill without duplicating rows?

Check whether AQE is disabled in your Spark conf, skew join handling helped us a lot here.

Yes, the partial index was the win for us too.

We saw the same issue, fixing the partition filter dropped runtime 60%.