# Transfer-ownership integration tests Integration tests that verify analytics endpoints scope streaming data **correctly in time** when a product has changed hands between accounts. When the `insights_transfer_product_ownership` feature flag is ON, the SQL layer routes every query through the "v2" permission macros and the `*_PRODUCT_TRANSFER` rollup tables. This suite is the end-to-end check that those paths return the right rows. --- ## Goal - **Scope** — End-to-end check that analytics endpoints correctly **time-slice** a product's streams when ownership has transferred between accounts. - **Trigger** — Only meaningful with `insights_transfer_product_ownership` ON, which routes queries through v2 permission macros + `*_PRODUCT_TRANSFER` rollup tables (gated on `PRODUCT_OWNERSHIP_ACCESS` instead of current-only `dim_release`). - **Fixture** — One real, once-transferred product (`5244974`, *Travessia*) with two sequential owners across windows W1 (→2026-04-30) and W2 (current). The transfer is **preliminary** — `PRODUCT_OWNERSHIP_ACCESS` in QA has not yet been rebuilt to reflect it; the planned boundary day comes from `FACTS.QA.DIM_RELEASE_HISTORY`. ### Four laws being asserted 1. **No forward leak** — a former owner never sees streams dated *after* their `access_until_date`. 2. **No fanout** — multi-row `PRODUCT_OWNERSHIP_ACCESS` joins must not multiply a viewer's totals by the number of past owners. 3. **Cumulative history preserved** — each owner sees `[beginning-of-time, their cutoff]`; windows are **nested**, not disjoint. Current owner + employee see everything. 4. **Grain consistency** — DAILY-backed endpoints (`/timeseries`, `/summary`) and ROLLUP-backed endpoints (`/aggregate-streams`, `/products`, `/top-accounts-metrics`) agree on who sees what. ### Two probe styles - **Precise** — endpoints with a per-product/ISRC selector (e.g. `ids=5244974`, `isrc=…`) directly probe product 5244974 against a visibility matrix (`early`/`mid`/`late`/`full` × 4 profiles). - **FF-pair differential** — for catalogue-aggregate endpoints (can't isolate one product), each request fires twice with paired profiles (FF-ON vs FF-OFF twin). The delta = exactly the transfer's contribution. Asserts **monotonicity** (ON ≥ OFF), **no-op** (where v2 collapses to current-ownership), and **grant** (former owner gains the transferred-away product's row). ### Extras - `test_boundary.py` — guards that `access_until_date` is **inclusive** (`<=`), so a former owner keeps their last-day streams (real traffic on the boundary day). - `test_invariants.py` — cross-endpoint numeric invariants (ISRC-sum ≈ product, DAILY ≈ ROLLUP for current owner, nested containment). --- ## 1. What this suite verifies For a product that was transferred between owners, every owner must see a **time-sliced** view of that product's streams: 1. **No forward leak** — a former owner never sees activity dated after their ownership ended (`access_until_date`). 2. **No fanout** — a product's streams are counted *once* per viewer. `PRODUCT_OWNERSHIP_ACCESS` and the `*_PRODUCT_TRANSFER` rollups hold one row per owner of a product; a buggy join or filter could multiply a viewer's totals by the number of owners. Totals must not inflate. 3. **Cumulative history is preserved** — each owner sees the product's streams from the beginning of time up to their cutoff. There is no lower bound: the current owner sees the entire pre-transfer history; a former owner sees everything up to the date they handed the product off (including eras owned by even-earlier owners). 4. **Consistency across grains** — the DAILY-backed endpoints (`/timeseries`, `/summary`) and the ROLLUP-backed endpoints (`/aggregate-streams`, `/products`, `/top-accounts-metrics`) agree on who-sees-what. --- ## 2. The feature flag `insights_transfer_product_ownership` (`analytics/constants/access.py` → `FEATURE_INSIGHTS_TRANSFER_PRODUCT_OWNERSHIP`). - **OFF** (production default): legacy permission filter — current-ownership only, via `dim_release`. A transferred product simply moves; the former owner loses all visibility, the new owner gains all of it. - **ON**: the v2 path. Permission macros gate on `PRODUCT_OWNERSHIP_ACCESS`; fact tables are swapped to their `*_PRODUCT_TRANSFER` variants. The four profiles in §4 have the flag assigned in QA Permissions. Without the flag the suite is meaningless — the v2 code path never runs. --- ## 3. The transfer scenario All tests target a single, real, once-transferred product. | Field | Value | |-------|-------| | Product id | `5244974` | | Product name | *Travessia* | | UPC / `display_upc` | `198000137366` | | `dim_release.artistid` | `3125999` | | Global participant id | `611a71d6-b9a9-4598-a83f-6aed1bf73904` | | ISRCs (13) | `BX69Y2100012` … `BX69Y2100024` | | Canonical single ISRC | `BX69Y2100018` (~1,377,810 lifetime streams, on product 5244974 only) | ### Ownership timeline — `FACTS.QA.DIM_RELEASE_HISTORY` ``` product_id | labelid | subaccountid | start_date_inclusive | end_date_inclusive ------------+---------+--------------+----------------------+-------------------- 5244974 | 81790 | NULL | NULL | 2026-04-30 5244974 | 797716 | NULL | 2026-05-01 | NULL ``` Two sequential owners (one row per owning account; `end_date_inclusive` is the **inclusive** last day of that owner's tenure — see §7). The transfer is **preliminary**: `FACTS.QA.PRODUCT_OWNERSHIP_ACCESS` has not been rebuilt yet, so as of 2026-05-27 it still lists vendor 81790 as the sole, current owner. The constants in `conftest.py` are written for the post-rebuild state implied by `DIM_RELEASE_HISTORY`, so tests pass once POA catches up. ``` ... 2026-04-30 │ 2026-05-01 ... now ─────────────────────────────────────────► │ W1 │ W2 │ Ala Comunicação │ Sued Nunes Produções LTDA (CURRENT) │ vendor 81790 │ vendor 797716 ``` | Window | Owner | Account | `access_until_date` | Daily streams (approx) | Dates with data | |--------|-------|---------|---------------------|------------------------|-----------------| | W1 | Ala Comunicação | vendor `81790` | `2026-04-30` | ~4–6k | 2024-10-24 … 2026-04-30 | | W2 | Sued Nunes Produções LTDA | vendor `797716` | `NULL` (current) | ~3–4k | 2026-05-01 … present | The transfer-boundary day carries real traffic — **2026-04-30 ≈ 4,007 streams** — so the inclusive/exclusive boundary behaviour (§7) is observable, not theoretical. --- ## 4. Profiles under test Each profile is a **pair** of `InsightsProfile` ids with *identical* account access — one with `insights_transfer_product_ownership` assigned in QA Permissions (the v2 path), one without it (the legacy, production path): | Key | FF-ON id | FF-OFF id | Account scope | Role in scenario | |-----|----------|-----------|---------------|------------------| | `employee` | `474215` | `1100` | full access | sees everything | | `ala_vendor` | `9900038` | `9900039` | vendor `81790` (Ala Comunicação) | **originating** former owner (W1) | | `sued_vendor` | `9900042` | `9900043` | vendor `797716` (Sued Nunes Produções LTDA) | **current** owner (W1+W2) | The FF-OFF ids mirror `tests/integration/endpoint_diff/retarget_manifest.py`. Pairing each FF-ON profile with its FF-OFF twin makes the flag a *differential oracle* (§6). `conftest.TRANSFER_PROFILES` is the FF-ON-only axis used by the precise probes; `conftest.TRANSFER_PROFILE_PAIRS` is the paired axis used by the differential tests, and `PROFILE_PAIRS` exposes each `ProfilePair`. All three profile keys carry real QA InsightsProfile ids: `employee` is carried over from the prior scenario, while `ala_vendor` and `sued_vendor` are newly provisioned for this scenario's vendor accounts. --- ## 5. How permission-scoping works Two macro families gate on `PRODUCT_OWNERSHIP_ACCESS` (`poa`). Both are `EXISTS` semi-joins, so they filter rather than fan out. ### DAILY grain — `permissions_filter.sql` / `account_scope_filter.sql` Used by `/timeseries`, `/summary`, etc. against the `_DAILY` fact tables. A former owner is gated to dates through and including their cutoff: ```sql poa.access_until_date IS NULL -- current owner: every date OR s.download_activity_date <= poa.access_until_date -- former owner ``` ### ROLLUP grain — `_PRODUCT_TRANSFER` tables + `permissions_filter` (`rollup_grain=true`) Used by `/aggregate-streams`, `/products`, `/top-accounts-metrics`, etc. The `*_PRODUCT_TRANSFER` rollups are pre-built by dbt: they join the DAILY facts to `poa` and emit **one row-set per owner**, with metrics time-sliced by the dbt `transfer_time_slice` macro: ```sql poa.access_until_date IS NULL OR s.download_activity_date <= poa.access_until_date ``` - A **scoped** viewer (label/subaccount profile) reads their *own* owner row-set — for a former owner that is their historical slice. - A **full-access** viewer (`employee`) reads only `is_current = TRUE` rows — the product appears solely under its *current* owner. So a former owner's rollup totals are frozen at their cutoff; the current owner's (and `employee`'s) rollup totals equal the product's full lifetime. --- ## 6. Visibility model & matrix Each owner sees `[beginning-of-time, their cutoff]`. Windows are **nested**, not disjoint — a later owner sees everything earlier owners saw, plus their own era. The current owner sees all of history. Date probes deliberately avoid the boundary day (which lives in `test_boundary.py`). `early` and `mid` both land in W1 (Ala's era); `late` is the only probe in W2 (Sued's era). Two W1 probes are kept so that the suite's 3-probe parametrize axis is preserved. | Probe | Date range | Lands in | |-------|-----------|----------| | `early` | 2025-01-01 … 2025-12-31 | W1 | | `mid` | 2026-01-01 … 2026-04-25 | W1 | | `late` | 2026-05-10 … 2026-05-25 | W2 | | `full` | 2024-10-24 … 2026-05-25 | W1+W2 | **Expected visibility** (`data` = non-empty & streams > 0; `empty` = no rows or streams sum to 0), for product / sound-recording DAILY endpoints: | Profile | `early` (W1) | `mid` (W1) | `late` (W2) | `full` | |---------|:---:|:---:|:---:|:---:| | `employee` | data | data | data | data | | `sued_vendor` (current) | data | data | data | data | | `ala_vendor` (former) | data | data | **empty** | data | The remaining endpoints — most `/account//*`, `/participant//*`, the `/top-*` leaderboards and the listing endpoints (`/product-metrics`, `/participant-metrics`, `/ugc-video-metrics`, `/market-ranks`) — **aggregate an account's or participant's whole catalogue**, so a single request cannot isolate product 5244974's transfer. They are tested with the **FF-pair differential** instead: each request is fired twice, once with the FF-ON profile and once with its FF-OFF twin (§4). The flag only ever changes scoping for transferred products, so the FF-ON-minus-FF-OFF delta is, by construction, exactly the transfer's contribution. Three laws are asserted (`conftest.assert_ff_*` / `assert_isolated_transfer`): - **monotonicity** — `FF-ON ≥ FF-OFF` always: v2 only ever *grants* a former owner back its historical slice, it never removes data; - **no-op** — `FF-ON == FF-OFF` where v2 resolves to current-ownership for the viewer (the employee on viewer-scoped endpoints; every profile on the non-ownership-scoped `/market-ranks`, and on the table-swap-only endpoints); - **grant** — for product 5244974's *own* row in a per-product/-ISRC/-track listing: a former owner has no row under FF-OFF (legacy hides the transferred-away product) and a time-sliced row under FF-ON. Where a listing exposes product 5244974's own row — `/account//products`, `/product-metrics`, `/top-metrics?upc=`, the UGC- and TikTok-ISRC endpoints — the differential is a **precise** per-product probe. Where only a catalogue-wide aggregate is available (`/top-accounts-metrics`, `/participant-metrics`, the participant timeseries endpoints) it is a monotonicity + no-op regression guard — still far stronger than the shape-only smoke tests it replaces. Four endpoints expose a **per-product selector** and so *can* isolate the transferred product — these are full time-slice probes, not smoke tests: | Endpoint | Per-product selector | |----------|----------------------| | `/account//timeseries` | `type=ACCOUNT_STREAMS_BY_PRODUCT&ids=5244974` | | `/account//summary` | `type=PRODUCT&ids=5244974` | | `/participant//timeseries` | `type=TRACK_STREAMS_BY_PRODUCT&ids=5244974` | | `/participant//summary` | `type=PRODUCT` + client-side row select | The first three apply the `ids` filter server-side. The fourth has no `ids` param, so `type=PRODUCT` is requested with a high `limit` (the participant's whole catalogue) and product 5244974's row is picked out of the per-product list client-side. All four thread the transfer flag into the v2 macros. The account endpoints time-slice by the **account's** ownership window (not the viewer's), so their visibility is keyed by account — see `conftest.ACCOUNT_WINDOW_PROFILE`. The participant endpoints are scoped by the viewer's permission window, so they reuse `_VISIBILITY` directly. Precise time-slice verification lives in `test_product.py`, `test_sound_recording.py`, `test_demographics.py` and `test_invariants.py`, each of which pins a single product or ISRC. ### Participation axis — the originating & destination artists A move also moves the artist's *participation*: it re-points the `PARTICIPATED_IN` edge onto a destination-vendor `LabelParticipant` and hard-deletes the originating LP's edge. The two `permission_label_participant_ids` branches of `_permissions_filter_v2` are therefore exercised by **two distinct artist profiles**, both reaching global participant `611a71d6…` (which represents both LPs): | Key | FF-ON | FF-OFF | LabelParticipant | Role | |-----|-------|--------|------------------|------| | `destination_artist` | `9900037` | `9900041` | `1567012701053` (vendor 797716) | current participant — current `PARTICIPATED_IN` arm | | `originating_artist` | `9900036` | `9900040` | `1702386309417` (vendor 81790) | former participant — `USED_TO_PARTICIPATE_IN` historical arm | - **destination_artist** sees the product's full lifetime in every window (its current participation row is live); the flag is a **no-op** (legacy reads the same row, untime-sliced). Same visibility shape as `sued_vendor`. - **originating_artist** had its `PARTICIPATED_IN` row deleted by the move, so the current arm and the legacy path see nothing; only the `USED_TO_PARTICIPATE_IN` shadow re-grants access, frozen to the former-owner window. The flag **grants** the moved-away product back (FF-OFF == 0, FF-ON == W1 slice). Same visibility shape as `ala_vendor`, via the participation axis. The originating grant relies on a subaccount normalization in `permissions_filter.sql`: the `USED_TO` row carries `subaccount_id = 0` for a vendor-level participant while `PRODUCT_OWNERSHIP_ACCESS` stores `NULL`, so the former-owner `poa` join compares `COALESCE(poa.subaccount_id, 0) = COALESCE(lp.subaccount_id, 0)`. Both FF-ON profiles must carry `insights_transfer_product_ownership` in SplitIO (the FF-OFF twins must not). #### Endpoint coverage of the participation axis Every endpoint that is gated by the **viewer's** `permissions_filter` runs the `permission_label_participant_ids` branch for an artist viewer, so the two artists exercise the *same* product-5244974 transfer scoping the ownership profiles do — reached through participation instead of label ownership. They are therefore mirrored across the whole viewer-scoped suite, not just the participant endpoints: each domain file carries a `*Artist` test class (e.g. `TestProductSummaryArtist`, `TestSoundRecordingTimeseriesArtist`, `TestTopMetricsArtist`) parametrized on the `ARTIST_PROFILES` / `ARTIST_PROFILE_ PAIRS` axes, reusing the same visibility matrix and FF-pair helpers. The `originating_artist` resolves to the `ala_vendor` role (former owner — `FORMER_ OWNERS`) and `destination_artist` to the `sued_vendor` role (current view — `CURRENT_VIEW` / `SEES_RECENT_WINDOW`), so `assert_visibility` / `assert_isolated_transfer` work unchanged. `test_used_to_participate.py` holds the `/participant//*` and `/participant-metrics` coverage plus the inclusive-boundary checks. Three endpoint groups are **not** mirrored on the participation axis: - the account-scoped `/account//*` endpoints and `/top-accounts-metrics` (`account_scope_filter` / direct `label_ids` filtering — gated by the account, not the viewer's participation, so an artist cannot exercise them); - the video / channel surfaces (`/account//video/*` and channel endpoints), which early-return empty for an artist caller before any SQL runs; - `/ugc-video-metrics` — a video surface whose participation-branch rollup join does not return within the suite's request budget for an artist viewer (it is kept on the ownership axis only). `/market-ranks` *is* mirrored: it is viewer-agnostic, so the table swap must stay neutral for the artists too. --- ## 7. Boundary handling — `access_until_date` is inclusive `PRODUCT_OWNERSHIP_ACCESS.access_until_date` is derived from `dim_release_history.end_date_inclusive` — the **last day** of a former owner's tenure, inclusive. Both layers honour that contract with `<=`: | Path | Predicate | |------|-----------| | dbt `transfer_time_slice` (ROLLUP) | `download_activity_date <= access_until_date` | | ows-analytics v2 macros (DAILY) | `download_activity_date <= access_until_date` | So a former owner sees streams through and including their transfer-boundary day (~4,007 streams on 2026-04-30). `test_boundary.py` pins that day and guards against a regression to a strict `<`, which would silently drop a former owner's last day of streams. --- ## 8. Test files | File | Covers | |------|--------| | `conftest.py` | Profiles + FF-OFF twins, `ProfilePair`/`PROFILE_PAIRS`, product/ISRC constants, owner timeline, probes, visibility matrix, `SEES_RECENT_WINDOW`, ownership roles, the `assert_visibility` / `assert_ff_*` / `assert_isolated_transfer` helpers | | `test_product.py` | `/product/` detail, `/product//` timeseries, summary, aggregated-streams, metrics-by-track — **precise** probes; `/product//aggregate-streams`, `/product-metrics`, `/product/growth-periods-bulk` — **FF-pair** precise per-product probes. `*Artist` classes mirror all of these on the participation axis | | `test_sound_recording.py` | `/sound-recording//` timeseries, summary, streams-breakdown, streams-all, streams-by-store, aggregated-streams, top-markets. `*Artist` classes mirror all of these on the participation axis | | `test_demographics.py` | `/sound-recording//demographics` and `/demographics?isrc=` — directional no-forward-leak checks (ownership axis + `*Artist` participation axis) | | `test_bulk_streams.py` | `POST /sound-recording/streams`, `POST /sound-recording/aggregate-streams` — per-ISRC structural sweep + **FF-pair** isolated-transfer probe on the canonical ISRC (ownership axis + `*Artist` participation axis) | | `test_account.py` | `/account//timeseries` + `summary` — **precise** time-slice probes (`type=*_BY_PRODUCT&ids=5244974`, keyed by account window); `products` — **FF-pair** per-product row probe; `top-content` — **FF-pair** no-leak check; `video/summary` + `video/timeseries` — **FF-pair** table-swap neutrality. Account-scoped, so **not** mirrored on the participation axis | | `test_account_metrics.py` | `/top-metrics?upc=` — **FF-pair** precise per-product probe; `/top-accounts-metrics`, `/top-sound-recordings` — **FF-pair** monotonicity + employee no-op. `*Artist` classes mirror `/top-metrics?upc=` and `/top-sound-recordings` (viewer-scoped); `/top-accounts-metrics` is account-scoped and stays ownership-only | | `test_misc.py` | `/market-ranks`, `/ugc-video-metrics` — **FF-pair** check that the `_PRODUCT_TRANSFER` table swap is metric-neutral for every viewer. `TestMarketRanksArtist` mirrors `/market-ranks` on the participation axis; `/ugc-video-metrics` (a video surface) is not mirrored | | `test_participant.py` | `/participant//timeseries` + `summary` — **precise** time-slice probes (`type=*_BY_PRODUCT&ids=5244974`; summary selects product 5244974's row client-side); aggregated-streams, track-streams-all, track-streams-by-store, demographics, `/participant-metrics` — **FF-pair** monotonicity + employee no-op. The participation axis lives in `test_used_to_participate.py` | | `test_used_to_participate.py` | The **originating** and **destination** artists (the two `permission_label_participant_ids` arms) across `/participant//` timeseries + summary (**precise** visibility), aggregated-streams, track-streams-all, track-streams-by-store, demographics, `/participant-metrics` (**FF-pair** monotonicity), the per-product **FF-pair** grant (originating) / no-op (destination), and the inclusive-boundary checks | | `test_tiktok.py` | `/sound-recording//tiktok/` timeseries, summary, aggregated-summary, aggregated-timeseries — **FF-pair** ISRC-isolated probes (no legacy leak + no forward leak; data-coverage-independent, since QA TikTok coverage is unverified). `*Artist` classes mirror all four on the participation axis | | `test_invariants.py` | Cross-endpoint numeric invariants: no-leak, no-fanout, nested containment, ISRC-sum ≈ product, DAILY ≈ ROLLUP for the current owner. `TestArtist*` classes assert the same identities for the originating/destination artists | | `test_boundary.py` | The transfer-boundary day is visible to the former owner — regression guard that `access_until_date` is inclusive (§7) | --- ## 9. Running the suite Prerequisites: - Both ids of each profile pair in §4 must exist in QA Permissions — the FF-ON id with the `insights_transfer_product_ownership` flag assigned, the FF-OFF id without it (the differential tests need both). All three profile keys are real QA profiles: `employee` is carried over from the prior scenario, and `ala_vendor` / `sued_vendor` are provisioned for the Ala 81790 and Sued 797716 vendor accounts. - `FACTS.QA.PRODUCT_OWNERSHIP_ACCESS` must hold the two rows for product 5244974 — one for vendor 81790 with `access_until_date = 2026-04-30`, one for vendor 797716 with `access_until_date = NULL` and `is_current = TRUE`. Until POA is rebuilt to match `DIM_RELEASE_HISTORY` the suite will fail. - The `*_PRODUCT_TRANSFER` rollups in `FACTS.QA` must include product 5244974's per-owner row-sets. - The transfer boundary day (2026-04-30) should be **>28 days** in the past before the recent-window tests (28-day rollup endpoints) can pass cleanly — otherwise a former owner's last days fall inside the window and the "no forward leak" assertion sees non-zero streams. - Kill dev server if running. Run the suite with pytest-xdist (`-n 10`) — it is I/O-bound on QA round-trips and a serial run takes several minutes; concurrency 10 is the project default (matches `make test_integration_endpoints`). ```bash # against QA INTEGRATION_TESTS_BASE_URL=https://qa-ows-analytics.theorchard.io \ uv run pytest tests/integration/transfer_ownership/ -n 10 -v # against a local dev server (queries FACTS.QA) make dev # separate terminal uv run pytest tests/integration/transfer_ownership/ -n 10 -v ``` Skips are expected where a scoped profile cannot reach another account (`_skip_if_unreachable` / `_skip_pair_if_unreachable`). The FF-pair differential tests do not skip on absent data — a missing signal is reported as a failure, except on TikTok, where QA coverage is unverified and only the flag-relative laws (no legacy leak, no forward leak) are asserted. --- ## 10. Diagnosing recent-window failures (`streams_28_days` "leaks") When a `*AggregatedStreams::test_dimension`, `ProductMetricsByTrack`, or `ProductBulkGrowthPeriods` test reports a non-zero `streams_28_days` for a former owner (e.g. `ala_vendor leaked 13307 post-transfer streams ...`), it is often a **false positive**. Those windows are pre-aggregated against the **streaming watermark**, not `today`, and any watermark less than `access_until_date + 28` days ahead will still legitimately cover the former owner's last days inside the rolling window. ### Checklist 1. **Confirm the rollup window.** The 28-day window's anchor is `get_max_available_streaming_stores_date()` (in `dbt-analytics`: `utils/macros/dates/get_max_available_streaming_stores_date.sql`): ```sql SELECT max_available_date FROM FACTS.QA.DATA_AVAILABILITY_GET_MAX_AVAILABLE_STREAMING_STORES_DATE; ``` The window is `[max - 27, max]`. If `max - 27 <= access_until_date`, the window still spans W1 → a former owner *legitimately* sees streams. The suite only passes cleanly once `max >= access_until_date + 28`. 2. **Verify POA still reflects the planned cutoff:** ```sql SELECT label_id, access_until_date, is_current FROM FACTS.QA.PRODUCT_OWNERSHIP_ACCESS WHERE product_id = 5244974; ``` 3. **Check the per-owner slice in the rollup directly:** ```sql -- ISRC-level (TestSoundRecordingAggregatedStreams) SELECT label_id, SUM(streams_28_days), COUNT(*) AS row_count FROM FACTS.QA.METRICS_BY_ISRC_COUNTRY_FEED_DISTRIBUTOR_ROLLUP_PRODUCT_TRANSFER WHERE isrc = 'BX69Y2100018' AND store_id != 1202 AND label_id IN (81790, 797716) GROUP BY 1; -- Product-level (TestProductAggregatedStreams, TestProductMetricsByTrack) SELECT label_id, SUM(streams_28_days), COUNT(*) AS row_count FROM FACTS.QA.METRICS_BY_PRODUCT_COUNTRY_FEED_DISTRIBUTOR_ROLLUP_PRODUCT_TRANSFER WHERE product_id = 5244974 AND store_id != 1202 AND label_id IN (81790, 797716) GROUP BY 1; -- Product-bulk (TestProductBulkGrowthPeriods) SELECT label_id, SUM(streams_28_days) FROM FACTS.QA.METRICS_BY_PRODUCT_FEED_DISTRIBUTOR_ROLLUP_PRODUCT_TRANSFER WHERE product_id = 5244974 AND label_id IN (81790, 797716) GROUP BY 1; ``` The former-owner row should match the failure's reported `leaked` value. ### Decision - **False positive** — `max - 27 <= access_until_date`. Either wait for the watermark to advance past `access_until_date + 28`, or skip/weaken the assertion conditional on the watermark. - **Real regression** — `max - 27 > access_until_date` *and* the rollup row is still non-zero. The `_PRODUCT_TRANSFER` overlay or the v2 permission macro is failing to truncate at `access_until_date`.