refresh_log_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.refresh_log_v2Type: Logging | Rows: 36 | Size: < 0.1 MB

V2 refresh script execution log. Tracks all staging-to-final adlevel operations including row counts, date ranges, duplicate detection, and error status. Essential for monitoring data pipeline health and debugging refresh failures.

Schema

Column	Type	Nullable	Description
run_id	STRING	Yes	Unique identifier for the refresh run (shared across all platforms in a single execution)
run_timestamp	TIMESTAMP	Yes	Timestamp when the refresh operation was executed
platform	STRING	Yes	Platform name (google, microsoft, linkedin, reddit, 6sense, stackadapt)
min_date	DATE	Yes	Earliest date in the staging data for this platform
max_date	DATE	Yes	Latest date in the staging data for this platform
staging_rows	INTEGER	Yes	Number of rows read from the staging table
rows_deleted	INTEGER	Yes	Number of rows deleted from the final table (matching date range + IDs)
rows_inserted	INTEGER	Yes	Number of rows inserted into the final table
duplicate_count	INTEGER	Yes	Number of duplicate rows detected in staging (by Date + ID keys)
skipped_null_ids	INTEGER	Yes	Number of rows skipped due to NULL ID columns
status	STRING	Yes	Execution status: SUCCESS or ERROR
details	STRING	Yes	Additional details; contains error message (@@error.message) on failure

Partitioning & Clustering

• Partitioned by: None
• Clustering: None

Usage

Check Recent Refresh Status

SELECT *
FROM `paidteam-data-warehouse.customer_ads_suse.refresh_log_v2`
WHERE run_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
ORDER BY run_timestamp DESC;

Identify Failed Refreshes

SELECT
  run_timestamp,
  platform,
  status,
  details
FROM `paidteam-data-warehouse.customer_ads_suse.refresh_log_v2`
WHERE status = 'ERROR'
ORDER BY run_timestamp DESC
LIMIT 20;

Verify Row Counts Over Time

SELECT
  platform,
  run_timestamp,
  staging_rows,
  rows_deleted,
  rows_inserted,
  duplicate_count,
  skipped_null_ids
FROM `paidteam-data-warehouse.customer_ads_suse.refresh_log_v2`
WHERE status = 'SUCCESS'
ORDER BY run_timestamp DESC
LIMIT 50;

Notes

• Each execution of the scheduled refresh script generates one row per platform. A full run produces 6 rows (one for each platform).
• The run_id column links all platform entries from the same script execution, making it easy to see the complete result of a single run.
• duplicate_count tracks rows with identical Date + ID key combinations in staging. Duplicates are deduplicated during the INSERT (only one row per key is written).
• skipped_null_ids tracks rows where one or more ID columns are NULL. These rows are excluded from the final table to maintain data integrity.
• The details column contains the @@error.message from BigQuery when status = 'ERROR'. For successful runs, it may be NULL or contain informational notes.
• The refresh script uses BEGIN...EXCEPTION...END blocks per platform, so one platform's failure does not prevent other platforms from refreshing.