Relationships tests assert FK integrity against a half-rebuilt star schema #2571

Expected Behavior

A dimensional relationships test passes, or it reports a genuine referential defect.

Current Behavior

It also fails when the run rebuilt one end of the foreign key and not the other. Production dbt build, 2026-08-18 14:52 UTC:

854 of 1058 FAIL 189 relationships_bridge_user_courserun_role_user_fk__user_pk__ref_dim_user_  [FAIL 189 in 1.05s]

Got 189 results, configured to fail if >10

The 189 orphans are not a data defect. dim_user was rebuilt in that run; bridge_user_courserun_role was not in the selection, so it still held the previous run's user_pk values. The whole 1058-node build failed.

Two things combine:

• dbt's default eager indirect selection pulls a relationships test into a run when either parent is selected. Selecting dim_user alone is enough to assert FK integrity against a table the run will not touch.
• The automation condition guarantees the lag. has_upstream_changes in upstream_or_code_changes is edge-triggered on a dependency's data version having already moved, so a downstream FK holder can only be requested on the next tick — and dbt_automation_sensor runs at minimum_interval_seconds=14400.

So every dim_user rebuild leaves up to 4 hours in which user_fk holders outside that run are stale, against a project-wide +error_if: ">10".

The contrast in the same run confirms the mechanism: tfact_enrollment, tfact_grade, tfact_certificate and tfact_order carry the identical user_fk -> dim_user test at error severity, were co-built with dim_user, and all passed.

Steps to Reproduce

1. Let the automation sensor launch a run whose selection contains dim_user but not bridge_user_courserun_role (this is the normal case — the bridge's own upstream int__combined__user_course_roles is unchanged).
2. Have more than 10 users' user_pk change since the bridge was last built. user_pk is recomputed from an identity ranking on every build, so this happens continuously — see Design durable user surrogate key (replace email-based user_pk) #2383.
3. relationships_bridge_user_courserun_role_user_fk__user_pk__ref_dim_user_ fails and takes the Dagster run with it.
4. The next sensor tick rebuilds the bridge and the orphans disappear without any change to the data.

Possible Solution

• Interim: severity: warn on the user_fk relationships tests of bridge_user_courserun_role and bridge_user_organization, matching the annotations fix(dim_user): derive user_pk from durable ids instead of email #2497 added to tfact_payment, tfact_problem_events and tfact_studentmodule_problems. This stops the run failures but hides a real 4-hour inconsistency from consumers such as course_staff_report.
• Real fix: materialize dim_user and its user_fk holders in one dbt invocation so the star is never asserted mid-update — widen the automation selection to include downstream FK holders. Note --indirect-selection=cautious would drop this coverage entirely rather than fix it, since the two ends are never selected together.
• A durable user_pk (Design durable user surrogate key (replace email-based user_pk) #2383) removes the orphans at the source; this issue is about the window itself and is worth fixing independently.

Additional Details

bridge_user_organization has identical exposure and is currently passing only because its orphan count sits under the threshold of 10.

Neither bridge can hold corrupt data: both are materialized='table' and inner-join dim_user on email, so a rebuild cannot produce orphans. The failure is purely an ordering window.

Separately observed in the same run, not covered by this issue: six tests on int__mitxonline__ecommerce_discount failed TABLE_NOT_FOUND inside a 2-second window while other tests on the same relation passed — a transient relation-visibility blip on Starburst Galaxy (the Trino query IDs carry four distinct coordinator suffixes, and profiles.yml sets threads: 4). There is no RetryPolicy anywhere in dg_projects/, so a 2-second blip kills a 1058-node build and the next attempt is 4 hours away.