Quorum Data Transfer Health Monitor

Critical Findings

Issues discovered during audit of Snowflake data transfer infrastructure (Feb 13, 2026)

CRITICAL MNTN Publisher Attribution: 100% Null

Every MNTN impression (PT=22) in XANDR_IMPRESSION_LOG has PUBLISHER_ID = null or 0. This was previously documented as ~20% but is actually 100%. Publisher-level attribution and reporting is completely unavailable for MNTN. By comparison, PT=8 has only 0.1% null, PT=9 has 0%, and PT=13 has 0.1% — confirming this is specific to how MNTN sends data, not a platform-wide issue.

Impact: Publisher optimization tab in Optimizer will show no data for MNTN campaigns. Clients cannot see which publishers are driving conversions.

CRITICAL No Snowpipes or Automated Ingestion

Zero Snowpipes exist in QUORUMDB.SEGMENT_DATA. All S3-to-Snowflake data loads (LotLinx, Paramount, MNTN) rely on external orchestration — likely cron jobs, Lambda functions, or manual COPY INTO commands via the COPY_FILES_TO_TABLE() stored procedure. There is no event-driven, automatic ingestion when new files land in S3 buckets.

Impact: If the external orchestrator fails (server down, cron misconfigured, credentials expire), data stops flowing with no Snowflake-side alerting. Note: The apparent LotLinx volume decline was investigated and found to be a normal multi-load backfill pattern, not a pipeline failure — but a real failure would look identical at first glance, reinforcing the need for Snowpipe-based alerting.

WARNING Only 2 Snowflake Tasks — 1 Suspended with Errors

Only two Snowflake tasks exist: TASK_ATTAIN_SEGMENT_MAID_UPSERT (active, Mon/Wed/Fri at 13:00 UTC) and DAILY_QRM_V4_REFRESH (suspended — SUSPENDED_DUE_TO_ERRORS). No scheduled tasks exist for LotLinx, Paramount, Causal IQ, or MNTN data loads.

Impact: DAILY_QRM_V4_REFRESH (which calls REFRESH_QRM_V4()) is not running. Additionally, client data loads have no Snowflake-native scheduling or error handling.

WARNING Attain Feed: Irregular Load Schedule

The Attain MERGE task is scheduled for Mon/Wed/Fri, but load history shows: Feb 7 (Fri) was skipped entirely, then Feb 9 (Mon) loaded 356M rows (2.5x normal, catching up). Feb 12 (Thu — not a scheduled day) had a tiny 5M row load, possibly manual. Normal loads are 133-144M rows.

Impact: Attain receives data in inconsistent batches rather than predictable cadence. Skipped loads cause large catch-up batches that consume more warehouse resources.

INFO LotLinx Backfill Pattern Can Mimic Volume Decline

RESOLVED: The apparent decline from 400-580K to 133K (Feb 10) and 56K (Feb 11) is NOT a real volume drop. The SEGMENT_DEVICES_CAPTURED_LOTLINX_BACKUP table backfills each date across ~3 load cycles spaced ~2 days apart. Recent dates have only completed 1-2 of their expected loads. Cross-validation against LOTLINX_QUORUMIMP_DATA confirms stable volumes at 478-507K/day. Future monitoring should account for this multi-load backfill pattern to avoid false alarms.

Note: When monitoring this table, always compare recent-date volumes against LOTLINX_QUORUMIMP_DATA as a cross-check before raising an alert. Only flag a true decline if BOTH tables show reduced volumes.

INFO Paramount Cross-Table Consistency

For Feb 10-12: PARAMOUNT_MAPPED_IMPRESSIONS has 17.1M rows vs PARAMOUNT_IMPRESSIONS_REPORT_90_DAYS at 16.5M rows. Mapped impressions slightly exceed total impressions, which could indicate different time-window boundaries or enrichment logic adding rows. Not necessarily a problem but worth understanding. Site visit to lead conversion rate is ~7.7% (2.5M leads from 33.2M site visits over 3 days), which appears reasonable.

Impact: Low — likely a boundary/enrichment difference, but worth documenting expected relationships between tables.

What This Dashboard Cannot Monitor

This dashboard queries Snowflake directly. The following areas require separate monitoring tools.

Investigation Guide

Reference for investigating warnings, updating this dashboard, and avoiding known pitfalls. Created Feb 13, 2026.

Client Data Paths

1. Attain — Snowflake Share

Outbound share SHARE_TO_ATTAIN → account UK76987. Primary table: QUORUM_CROSS_CLOUD.ATTAIN_FEED.SEGMENT_MAID_WITH_IP.

Snowflake Task TASK_ATTAIN_SEGMENT_MAID_UPSERT runs Mon/Wed/Fri at 13:00 UTC on X_LARGE_WH. It MERGEs from SEGMENT_DEVICES_CAPTURED (last 30 days) into the Attain feed.

Other shared tables: MAID_CENTROID_ASSOCIATION (544M rows), HOUSE_HOLD_MAPPING (9B rows, stale since Nov 2025, manual refresh), SEGMENT_META_DATA (740K rows).

2. LotLinx — S3 + Snowflake

S3 bucket lotlinx-webpixeldata-filedrop → QUORUMDB.SEGMENT_DATA.SEGMENT_DEVICES_CAPTURED_LOTLINX_BACKUP.

No Snowflake Task or Snowpipe. Load process is external. Typical weekday: 400-580K rows. Weekend: 250-330K rows.

3. Paramount — S3 + Snowflake

S3 buckets paramount-retargeting-files and quorum-paramount-data → 5 Snowflake tables.

No Snowflake Task or Snowpipe. Agency ID 1480 in Optimizer (Class W — web visits). Revenue: $43-55K/mo (highest).

4. Causal IQ — Weekly Stats

CAMPAIGN_PERFORMANCE_REPORT_WEEKLY_STATS filtered by AGENCY_ID = 1813. Weekly cadence — LOG_DATE = Sunday (week-end date). 18 active advertisers. Revenue: $18-30K/mo.

5. MNTN — XANDR + Weekly Stats + S3

Three sources: XANDR_IMPRESSION_LOG (filter PT = 22), CAMPAIGN_PERFORMANCE_REPORT_WEEKLY_STATS (filter AGENCY_ID = 2514), and S3 bucket mntn-data-drop.

Critical: 100% of PT=22 rows have PUBLISHER_ID = null/0. This is MNTN-specific (other PTs are <1% null).

XANDR queries must always include AND CAST(TIMESTAMP AS DATE) <= CURRENT_DATE() to filter out garbage future dates.

Column Name Gotchas

These caused query failures in previous sessions. Always verify columns via INFORMATION_SCHEMA.COLUMNS before querying an unfamiliar table.

Table	Wrong Column	Correct Column
`SEGMENT_MAID_WITH_IP`	CREATED_DATE	CREATED_AT
`LOTLINX_BACKUP`	DRIVEBYDATE	DRIVE_BY_DATE
`PARAMOUNT_SITEVISITS`	EVENT_DATE	SITE_VISIT_TIMESTAMP
`PARAMOUNT_LEADS`	SITE_VISIT_TIMESTAMP	LEAD_TIMESTAMP
`PARAMOUNT_CUSTOM_AUDIENCE`	DELIVERY_DATE	LAST_IMPRESSION_PIXEL_FIRED
`WEEKLY_STATS`	REPORT_END_DATE	LOG_DATE
`XANDR_IMPRESSION_LOG`	MAID	DEVICE_UNIQUE_ID
`SEGMENT_DELIVERY_DETAILS`	AGENCY_NAME	AGENCY_ID

SQL Gotchas

1. XANDR future dates: MAX(CAST(TIMESTAMP AS DATE)) on XANDR returns "58082-12-23" without a bound. Always add: AND CAST(TIMESTAMP AS DATE) <= CURRENT_DATE()

2. Reserved word "rows": COUNT(*) as rows will fail. Use row_count instead.

3. No AGENCY_NAME: SEGMENT_DELIVERY_DETAILS only has AGENCY_ID. Name lookup requires a separate table.

Snowflake Infrastructure (as of Feb 13, 2026)

Snowflake Tasks (only 2 exist)

Task	Status	Schedule	Warehouse
`TASK_ATTAIN_SEGMENT_MAID_UPSERT`	started	Mon/Wed/Fri 13:00 UTC	X_LARGE_WH
`DAILY_QRM_V4_REFRESH`	SUSPENDED_DUE_TO_ERRORS	Daily 06:00 America/Denver	COMPUTE_WH

Snowpipes: Zero. None exist. All S3 ingestion is external.

Key Stored Procedure: COPY_FILES_TO_TABLE() — likely the main mechanism for S3 → Snowflake loads.

Open Warnings — Investigation Steps

RESOLVED LotLinx Volume Decline — False Alarm (Backfill Pattern)

Finding: The apparent decline from ~500K to 56K was caused by the table's multi-batch backfill loading pattern, not a pipeline failure. Each DRIVE_BY_DATE receives rows across ~3 load cycles spaced ~2 days apart. Recent dates (Feb 10-11) had only completed 1 of ~3 expected loads.

Cross-validation: LOTLINX_QUORUMIMP_DATA (separate pipeline) showed stable daily volumes of 478-507K for the same period, confirming no actual decline.

89-day trend analysis: Monthly averages are actually trending upward: Nov 438K → Dec 457K → Jan 467K → Feb 502K. Recent week z-score of +0.66 vs baseline — well within normal range.

-- MONITORING QUERY: Detect actual LotLinx issues vs backfill lag -- Compare the two tables. If QUORUMIMP_DATA is healthy but BACKUP looks low, -- it's just backfill lag. If BOTH are low, investigate for real. SELECT 'BACKUP' AS source, DATE(DRIVE_BY_DATE) AS dt, COUNT(*) AS vol FROM QUORUMDB.SEGMENT_DATA.SEGMENT_DEVICES_CAPTURED_LOTLINX_BACKUP WHERE DRIVE_BY_DATE >= DATEADD('day', -7, CURRENT_DATE()) GROUP BY dt UNION ALL SELECT 'QUORUMIMP' AS source, DATE(IMPRESSION_TIMESTAMP) AS dt, COUNT(*) AS vol FROM QUORUMDB.LOTLINX.LOTLINX_QUORUMIMP_DATA WHERE IMPRESSION_TIMESTAMP >= DATEADD('day', -7, CURRENT_DATE()) GROUP BY dt ORDER BY dt, source; -- Check backfill completion: how many load cycles per recent date? SELECT DRIVE_BY_DATE, CREATED_AT, COUNT(*) AS rows_loaded FROM QUORUMDB.SEGMENT_DATA.SEGMENT_DEVICES_CAPTURED_LOTLINX_BACKUP WHERE DRIVE_BY_DATE >= DATEADD('day', -7, CURRENT_DATE()) GROUP BY DRIVE_BY_DATE, CREATED_AT ORDER BY DRIVE_BY_DATE, CREATED_AT;

MEDIUM PRIORITY Attain HOUSE_HOLD_MAPPING Stale

9B-row table hasn't been refreshed since Nov 2025. Manual-only process. Household-level attribution depends on this mapping. MAID_CENTROID_ASSOCIATION match rate degraded 28% → 16% in the same period, which may be related.

Actions: Identify who last refreshed this, document the procedure, and determine if the match rate degradation is caused by stale household data.

MEDIUM PRIORITY MNTN Publisher Data 100% Null

Every PT=22 row has PUBLISHER_ID = null/0. Publisher optimization is impossible for MNTN campaigns.

-- Verify current state SELECT CAST(TIMESTAMP AS DATE) as dt, COUNT(*) as total, SUM(CASE WHEN PUBLISHER_ID IS NULL OR PUBLISHER_ID = 0 THEN 1 ELSE 0 END) as null_pub FROM QUORUMDB.SEGMENT_DATA.XANDR_IMPRESSION_LOG WHERE PT = 22 AND CAST(TIMESTAMP AS DATE) BETWEEN DATEADD('day', -3, CURRENT_DATE()) AND CURRENT_DATE() GROUP BY dt ORDER BY dt; -- Compare with other PTs to confirm MNTN-specific SELECT PT, COUNT(*) as total, ROUND(SUM(CASE WHEN PUBLISHER_ID IS NULL OR PUBLISHER_ID = 0 THEN 1 ELSE 0 END)*100.0/COUNT(*),1) as null_pct FROM QUORUMDB.SEGMENT_DATA.XANDR_IMPRESSION_LOG WHERE CAST(TIMESTAMP AS DATE) = CURRENT_DATE() AND CAST(TIMESTAMP AS DATE) <= CURRENT_DATE() GROUP BY PT ORDER BY total DESC;

Root cause: Likely MNTN doesn't pass publisher data in their XANDR feed. Needs escalation to MNTN's technical team.

LOW-MEDIUM Suspended Snowflake Task: DAILY_QRM_V4_REFRESH

Task calls REFRESH_QRM_V4() but is suspended due to errors.

-- Check task error history (may need ACCOUNTADMIN) SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY( TASK_NAME => 'DAILY_QRM_V4_REFRESH', SCHEDULED_TIME_RANGE_START => DATEADD('day', -30, CURRENT_TIMESTAMP()) )) ORDER BY SCHEDULED_TIME DESC LIMIT 10; -- Check what the stored procedure does SELECT GET_DDL('PROCEDURE', 'QUORUMDB.SEGMENT_DATA.REFRESH_QRM_V4()');

LOW Attain Irregular Load Schedule

Task is Mon/Wed/Fri but Feb 7 was skipped, Feb 12 (Thu) had a tiny 5M-row manual load. Inconsistent cadence.

-- Check load pattern over 30 days SELECT CREATED_AT::DATE as load_date, DAYNAME(CREATED_AT::DATE) as dow, COUNT(*) as row_count FROM QUORUM_CROSS_CLOUD.ATTAIN_FEED.SEGMENT_MAID_WITH_IP WHERE CREATED_AT >= DATEADD('day', -30, CURRENT_DATE()) GROUP BY load_date, dow ORDER BY load_date;

Quick Health Check Queries (Copy-Paste Ready)

Attain

SELECT MAX(CREATED_AT)::DATE as latest, COUNT(*) as today_rows FROM QUORUM_CROSS_CLOUD.ATTAIN_FEED.SEGMENT_MAID_WITH_IP WHERE CREATED_AT >= CURRENT_DATE();

LotLinx

SELECT DRIVE_BY_DATE::DATE as dt, COUNT(*) as row_count FROM QUORUMDB.SEGMENT_DATA.SEGMENT_DEVICES_CAPTURED_LOTLINX_BACKUP WHERE DRIVE_BY_DATE >= DATEADD('day', -7, CURRENT_DATE()) GROUP BY dt ORDER BY dt;

Paramount (all 5 tables)

SELECT 'IMPRESSIONS' as tbl, MAX(IMP_DATE)::DATE as latest FROM QUORUMDB.SEGMENT_DATA.PARAMOUNT_IMPRESSIONS_REPORT_90_DAYS UNION ALL SELECT 'MAPPED', MAX(IMP_DATE)::DATE FROM QUORUMDB.SEGMENT_DATA.PARAMOUNT_MAPPED_IMPRESSIONS UNION ALL SELECT 'SITEVISITS', MAX(SITE_VISIT_TIMESTAMP)::DATE FROM QUORUMDB.SEGMENT_DATA.PARAMOUNT_SITEVISITS UNION ALL SELECT 'LEADS', MAX(LEAD_TIMESTAMP)::DATE FROM QUORUMDB.SEGMENT_DATA.PARAMOUNT_LEADS UNION ALL SELECT 'AUDIENCE', MAX(LAST_IMPRESSION_PIXEL_FIRED)::DATE FROM QUORUMDB.SEGMENT_DATA.PARAMOUNT_CUSTOM_AUDIENCE_DELIVERY;

Causal IQ

SELECT MAX(LOG_DATE)::DATE as latest, COUNT(*) as row_count, COUNT(DISTINCT ADVERTISER_ID) as advertisers FROM QUORUMDB.SEGMENT_DATA.CAMPAIGN_PERFORMANCE_REPORT_WEEKLY_STATS WHERE AGENCY_ID = 1813;

MNTN — XANDR

SELECT MAX(CAST(TIMESTAMP AS DATE)) as latest FROM QUORUMDB.SEGMENT_DATA.XANDR_IMPRESSION_LOG WHERE PT = 22 AND CAST(TIMESTAMP AS DATE) <= CURRENT_DATE();

MNTN — Weekly Stats

SELECT MAX(LOG_DATE)::DATE as latest, COUNT(*) as row_count FROM QUORUMDB.SEGMENT_DATA.CAMPAIGN_PERFORMANCE_REPORT_WEEKLY_STATS WHERE AGENCY_ID = 2514;

How to Update This Dashboard

This dashboard uses a static data snapshot in the CLIENT_DATA JavaScript object. To update it with fresh data:

1. Run the health check queries above to get current values.

2. In the HTML source, find the CLIENT_DATA object and update: latestDate, totalRows, recentRows, and the dailyVolumes arrays.

3. Update the SNAPSHOT_TIME constant and the timestamp in the init() function.

4. Health status auto-calculates from staleness thresholds:

Cadence	Healthy	Warning	Critical
Daily	≤ 1 day	2–3 days	> 3 days
Weekly	≤ 8 days	9–14 days	> 14 days

Related Systems & Files

System	Details
Quorum Optimizer API	Flask on Railway, v5.13-pipeline-health. Existing `/api/v5/pipeline-health` monitors base tables but NOT client transfers.
GitHub	`ezrakd/quorum-optimizer`
Project Index	`/mnt/Claude/quorum-project/COWORK_PROJECT_INDEX.md`
S3 Architecture	`AWS_S3_AND_DELIVERY_ARCHITECTURE.md` in the Quorum Rewrite zip
Revenue Data	`/mnt/Claude/Weekly Revenue Forecast 2-12-26.xlsx`