> ## Documentation Index
> Fetch the complete documentation index at: https://support.detrics.io/llms.txt
> Use this file to discover all available pages before exploring further.

# System Columns

> Understand the columns Detrics adds to every BigQuery table

<Info>**Last updated:** March 18, 2026</Info>

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

| Column                | Type      | Position      | Description                                   |
| --------------------- | --------- | ------------- | --------------------------------------------- |
| `_detrics_account_id` | STRING    | First column  | The platform account ID that owns the row     |
| `_detrics_row_id`     | STRING    | Second column | SHA-256 content hash of the row's data        |
| `_detrics_sync_id`    | STRING    | Third column  | UUID of the transfer run that created the row |
| `_detrics_synced_at`  | TIMESTAMP | Last column   | When 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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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)

```sql theme={null}
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

| Column          | Type      | Description                                                 |
| --------------- | --------- | ----------------------------------------------------------- |
| `sync_id`       | STRING    | Transfer run ID (matches `_detrics_sync_id` in data tables) |
| `table_name`    | STRING    | Name of the BigQuery table                                  |
| `sync_mode`     | STRING    | Sync mode used (incremental, full\_refresh, full\_append)   |
| `status`        | STRING    | Result: success, partial\_success, or failed                |
| `row_count`     | INT64     | Number of rows loaded                                       |
| `duration_ms`   | INT64     | How long the sync took in milliseconds                      |
| `started_at`    | TIMESTAMP | When the sync started                                       |
| `completed_at`  | TIMESTAMP | When the sync finished                                      |
| `error_message` | STRING    | Error details if the sync failed                            |
| `metadata`      | JSON      | Additional context (chunk details, entity level, etc.)      |

### Example: Recent Sync History

```sql theme={null}
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

```sql theme={null}
SELECT
  table_name,
  error_message,
  started_at
FROM `project.dataset._detrics_sync_log`
WHERE status = 'failed'
ORDER BY started_at DESC
```

***

## Table Ownership Detection

The system columns also serve as a **table ownership fingerprint**. Before writing to any table, Detrics checks that all four `_detrics_*` columns exist. If a table with the target name already exists in the dataset but lacks these columns, Detrics assumes it was created by you (or another tool) and **refuses to write to it**, protecting your data from accidental overwrites.

This means you can safely share a dataset between Detrics-managed tables and your own tables, views, or materialized queries. Detrics will never modify a table it didn't create.

If you encounter this protection, see the [FAQ](/bigquery/faq#what-if-i-already-have-a-table-with-the-same-name-as-a-detrics-transfer-target) for resolution steps.

***

## 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.