Skip to main content
Last updated: March 18, 2026
Detrics adds four system columns to every BigQuery table. These columns enable deduplication, multi-account filtering, and sync auditing. They are clearly prefixed with _detrics_ so they’re easy to identify and exclude from analysis when not needed.

​
Column Reference

ColumnTypePositionDescription
_detrics_account_idSTRINGFirst columnThe platform account ID that owns the row
_detrics_row_idSTRINGSecond columnSHA-256 content hash of the row’s data
_detrics_sync_idSTRINGThird columnUUID of the transfer run that created the row
_detrics_synced_atTIMESTAMPLast columnWhen the row was loaded into BigQuery

​
_detrics_account_id

Every row is tagged with the platform account ID it belongs to. This is critical for:
  • Multi-account transfers — When a transfer syncs multiple ad accounts, this column lets you filter or group by account
  • Account-scoped deduplication — Incremental sync deletes and re-inserts data scoped to specific accounts, so one account’s sync never affects another account’s data

​
Example: Filter by Account

SELECT campaign_name, SUM(spend) as total_spend
FROM `project.dataset.meta_ads_campaigns`
WHERE _detrics_account_id = '709801159698717'
  AND date >= '2026-03-01'
GROUP BY campaign_name

​
Example: Compare Across Accounts

SELECT
  _detrics_account_id,
  SUM(spend) as total_spend,
  SUM(impressions) as total_impressions
FROM `project.dataset.meta_ads_campaigns`
WHERE date >= '2026-03-01'
GROUP BY _detrics_account_id
ORDER BY total_spend DESC

​
_detrics_row_id

A SHA-256 hash computed from all non-system column values plus the account ID. Two rows with identical data produce identical hashes. This column is primarily used internally by Detrics for deduplication tracking. What’s included in the hash: All metric values, dimension values, and the account ID. What’s excluded from the hash: System columns (_detrics_row_id, _detrics_sync_id, _detrics_synced_at) and ephemeral URL fields (like ad_image_url) that change on every API call even when the actual content hasn’t changed.

​
_detrics_sync_id

A UUID that identifies the transfer run that created each row. All rows loaded in the same sync share the same _detrics_sync_id. This is useful for:
  • Auditing — Know exactly which sync produced each row
  • Debugging — If you notice bad data, trace it back to a specific run
  • Full Append analysis — Distinguish between snapshots taken at different times

​
Example: See All Rows from a Specific Sync

SELECT *
FROM `project.dataset.google_ads_campaigns`
WHERE _detrics_sync_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'

​
_detrics_synced_at

A UTC timestamp recording when each row was loaded into BigQuery. Always the last column in every table. Useful for:
  • Data freshness checks — Verify that recent syncs are running on schedule
  • Full Append filtering — Select only the latest snapshot or compare snapshots across dates

​
Example: Check Data Freshness

SELECT
  MAX(_detrics_synced_at) as last_sync,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(_detrics_synced_at), HOUR) as hours_since_sync
FROM `project.dataset.meta_ads_campaigns`

​
Example: Get Only the Latest Snapshot (Full Append Tables)

SELECT *
FROM `project.dataset.campaign_lifetime_metrics`
WHERE _detrics_synced_at = (
  SELECT MAX(_detrics_synced_at)
  FROM `project.dataset.campaign_lifetime_metrics`
)

​
Sync Log

In addition to the system columns on each table, Detrics creates a metadata table called _detrics_sync_log in your dataset. This table records one row per table per transfer run.

​
Sync Log Columns

ColumnTypeDescription
sync_idSTRINGTransfer run ID (matches _detrics_sync_id in data tables)
table_nameSTRINGName of the BigQuery table
sync_modeSTRINGSync mode used (incremental, full_refresh, full_append)
statusSTRINGResult: success, partial_success, or failed
row_countINT64Number of rows loaded
duration_msINT64How long the sync took in milliseconds
started_atTIMESTAMPWhen the sync started
completed_atTIMESTAMPWhen the sync finished
error_messageSTRINGError details if the sync failed
metadataJSONAdditional context (chunk details, entity level, etc.)

​
Example: Recent Sync History

SELECT
  table_name,
  sync_mode,
  status,
  row_count,
  ROUND(duration_ms / 1000, 1) as duration_seconds,
  started_at
FROM `project.dataset._detrics_sync_log`
ORDER BY started_at DESC
LIMIT 20

​
Example: Failed Syncs

SELECT
  table_name,
  error_message,
  started_at
FROM `project.dataset._detrics_sync_log`
WHERE status = 'failed'
ORDER BY started_at DESC

​
Column Order in BigQuery

Detrics organizes columns in a consistent order across all tables:
  1. _detrics_account_id (first)
  2. _detrics_row_id
  3. _detrics_sync_id
  4. Time columns (e.g., date, week, month)
  5. Dimension columns (e.g., campaign_name, ad_name)
  6. Metric columns (e.g., spend, impressions, clicks)
  7. _detrics_synced_at (last)
This order makes it easy to scan tables in the BigQuery console — system columns are clearly separated from your data.