# Designing Sync Queries

> Source: https://docs.trailspark.ai/docs/designing-sync-queries

## Overview

Each sync is one SQL query pointed at one feed type — **Product events**, **Users**, or **Accounts**. This guide covers the rules every query needs to follow, the exact columns each feed expects, and a set of worked patterns for the situations that come up most often.

## Rules that apply to every feed

- Your query must reference the placeholder `@watermark` (and optionally `@watermark_end`) so each sync only reads new rows since its last run — the sync editor won't let you save a query that doesn't.
- Give every row a `message_id` that's unique to that row. If a sync is retried, restarted, or reads a slightly overlapping period (which is normal), the same `message_id` is simply recognized as already synced and skipped — so a sync never double-counts anything.
- Column names are matched case-insensitively, so `EVENT_NAME` and `event_name` are treated the same.
- Numeric IDs are fine — a numeric column mapped to a text field (like `user_id` or `message_id`) is coerced to text automatically.
- Events dated more than about 2 years in the past, or more than an hour in the future, are skipped. Run history shows this as **"Event time too old or in the future."**
- Columns that don't match a feed's column requirements are simply ignored — the preview warns you so you can catch a typo before saving.

## Column requirements by feed

### Product events

| Column | Required? | Notes |
|---|---|---|
| `event_name` | Required | What happened, e.g. `signed_in`, `project_created` |
| `occurred_at` | Required | When it happened |
| `message_id` | Required | A unique ID for this row |
| `user_id`, `email`, `anonymous_id` | At least one required | So TrailSpark knows who did it |
| `product_org_id` | Optional | Which account this event belongs to |
| `prop_*` | Optional | Any column starting with `prop_` becomes an event property — `prop_plan_tier` becomes the event's `plan_tier` property |

> [!NOTE]
> A row with only an `anonymous_id` (no `user_id` or `email`) still syncs — so you don't lose the activity — but it won't create or update a user record on its own, since TrailSpark doesn't yet know who that person is.

### Users

| Column | Required? | Notes |
|---|---|---|
| `user_id` | Required | Your internal ID for the user |
| `email` | Required | Their email address |
| `updated_at` | Required | When this row was last updated |
| `message_id` | Required | A unique ID for this row — yes, this feed needs one too |
| `product_org_id` | Optional | Which account this user belongs to — links the user to the account immediately |
| `trait_*` | Optional | Any column starting with `trait_` becomes a user attribute — `trait_job_title` becomes the user's `job_title` attribute |

**How identity works for warehouse data.** If you've used a CDP like Segment, you may expect a separate "identify" step that links anonymous visitors to known users. Warehouse data usually doesn't need it: because your SQL can join users to events, every row you send already carries `user_id` and `email` together, and Trailspark links them on the person's record directly. The Users feed is your user roster and their attributes — not a prerequisite for identifying events. Include `product_org_id` on a Users row and Trailspark links that user to their account right away, so the account shows up with its people even before any events arrive. The one case where anonymous linking matters is if your warehouse holds CDP-style anonymous IDs (for example, exported web-analytics device IDs): include `anonymous_id` on your Product events and Users rows, and Trailspark will connect that anonymous activity to the person the same way it does for CDP sources.

### Accounts

| Column | Required? | Notes |
|---|---|---|
| `product_org_id` | Required | Your internal ID for the account |
| `updated_at` | Required | When this row was last updated |
| `name`, `plan_id`, `plan_name`, `trial_status` | Optional | Reserved account fields |
| `mrr_cents` | Optional | Monthly recurring revenue, **in cents** — `9900` means $99.00 |
| `user_count`, `last_active_at` | Optional | Reserved account fields |
| `attr_*` | Optional | Any column starting with `attr_` becomes a custom account attribute |

## Why `message_id` matters

TrailSpark uses `message_id` to make sure a sync never double-counts anything. If a sync gets retried, restarted, or reads slightly overlapping periods (which is normal — see [Schedules and Run History](/docs/schedules-and-run-history)), the same `message_id` is simply recognized as "already synced" and skipped. You never need to worry about whether a run happened twice.

## Worked SQL patterns

These patterns are field-proven — they come from real syncs that were designed, previewed, and running in production. The table and column names below are placeholders; swap in your own schema.

### a. Basic event sync

A single events table, filtered on `@watermark`, with a deterministic `message_id` built from a natural key:

```sql
SELECT
  'signed_in' AS event_name,
  e.event_time AS occurred_at,
  CONCAT(e.user_id, '|', CAST(e.event_time AS STRING)) AS message_id,
  e.user_id AS user_id,
  e.email AS email,
  e.account_id AS product_org_id
FROM `your-project.analytics.events` e
WHERE e.event_time >= @watermark
  AND e.event_time < @watermark_end
```

### b. Several event types in one sync

Use a CTE per event type and `UNION ALL` them together. Every branch must return the **same column set** — pad any `prop_` column a branch doesn't use with `CAST(NULL AS STRING)` (nulls pass through harmlessly), and prefix each branch's `message_id` (`'signup|'`, `'invite|'`) so IDs can never collide across branches:

```sql
WITH signups AS (
  SELECT
    'signup' AS event_name,
    s.created_at AS occurred_at,
    CONCAT('signup|', s.user_id, '|', CAST(s.created_at AS STRING)) AS message_id,
    s.user_id AS user_id,
    s.email AS email,
    s.account_id AS product_org_id,
    s.plan_selected AS prop_plan_selected,
    CAST(NULL AS STRING) AS prop_invited_by
  FROM `your-project.app_db.signups` s
  WHERE s.created_at >= @watermark AND s.created_at < @watermark_end
),
invites AS (
  SELECT
    'invite_sent' AS event_name,
    i.sent_at AS occurred_at,
    CONCAT('invite|', i.invite_id) AS message_id,
    i.inviter_user_id AS user_id,
    i.inviter_email AS email,
    i.account_id AS product_org_id,
    CAST(NULL AS STRING) AS prop_plan_selected,
    i.invitee_email AS prop_invited_by
  FROM `your-project.app_db.invites` i
  WHERE i.sent_at >= @watermark AND i.sent_at < @watermark_end
)
SELECT * FROM signups
UNION ALL
SELECT * FROM invites
```

### c. Reducing grain

A raw `visits` table that logs every page load can be turned into one `signed_in` event per user per day: `SELECT DISTINCT` on the user and the day, round `occurred_at` down to the day with `TIMESTAMP(DATE(...))`, and put the date in the `message_id` so each day only produces one row:

```sql
SELECT DISTINCT
  'signed_in' AS event_name,
  TIMESTAMP(DATE(v.visited_at)) AS occurred_at,
  CONCAT(v.user_id, '|', CAST(DATE(v.visited_at) AS STRING)) AS message_id,
  v.user_id AS user_id,
  v.email AS email,
  v.account_id AS product_org_id
FROM `your-project.analytics.visits` v
WHERE v.visited_at >= @watermark
  AND v.visited_at < @watermark_end
```

### d. Attributing account-level events to a person

Some events happen at the account level and don't have a natural user attached — a plan upgrade, say. Join a membership table and fall back to the account's owner, then require a resolved email so the row has a real identity:

```sql
WITH account_owner AS (
  SELECT
    m.account_id,
    ARRAY_AGG(m.user_id ORDER BY IF(m.role = 'owner', 0, 1) LIMIT 1)[OFFSET(0)] AS owner_user_id
  FROM `your-project.app_db.memberships` m
  GROUP BY m.account_id
)
SELECT
  'plan_upgraded' AS event_name,
  p.upgraded_at AS occurred_at,
  CONCAT('plan_upgraded|', p.account_id, '|', CAST(p.upgraded_at AS STRING)) AS message_id,
  ao.owner_user_id AS user_id,
  u.email AS email,
  p.account_id AS product_org_id
FROM `your-project.app_db.plan_changes` p
JOIN account_owner ao ON ao.account_id = p.account_id
JOIN `your-project.app_db.users` u ON u.user_id = ao.owner_user_id
WHERE p.upgraded_at >= @watermark
  AND p.upgraded_at < @watermark_end
  AND u.email IS NOT NULL
```

The `ARRAY_AGG(... ORDER BY IF(role = 'owner', 0, 1) LIMIT 1)` picks the owner if one exists, otherwise any other member of the account. The final `AND u.email IS NOT NULL` drops rows where no email could be resolved at all, rather than sending a row with no identity.

### e. Turning state into events

A wide table with one `first_<feature>_at` timestamp column per feature can be turned into one `feature_adopted` event per account per feature with `UNPIVOT`, using the account and feature name to build `message_id`:

```sql
SELECT
  'feature_adopted' AS event_name,
  f.adopted_at AS occurred_at,
  CONCAT(f.account_id, '|', f.feature_name) AS message_id,
  f.account_id AS anonymous_id,
  f.feature_name AS prop_feature_name,
  f.account_id AS product_org_id
FROM (
  SELECT account_id, feature_name, adopted_at
  FROM `your-project.app_db.account_features`
  UNPIVOT(adopted_at FOR feature_name IN (
    first_reports_at AS 'reports',
    first_api_key_at AS 'api_keys',
    first_sso_at AS 'sso'
  ))
) f
WHERE f.adopted_at >= @watermark
  AND f.adopted_at < @watermark_end
```

This event belongs to an account rather than a specific person, so the query uses `anonymous_id` (set to the account ID) to satisfy the identifier requirement — it still attaches to the account through `product_org_id`, it just won't create a user record on its own. If you can resolve an actual person instead, prefer pattern (d) above.

### f. Full first load for Users or Accounts

The first sync only looks back as far as the **History window (days)** you set (up to 730). That's a problem for dormant users or accounts whose real `updated_at` is older than that — they'd never load, since rows more than about 2 years old are skipped outright. The fix: set the history window to its maximum (730 days), then clamp both the filter and the emitted `updated_at` to no older than 700 days ago with `GREATEST`:

```sql
SELECT
  u.user_id AS user_id,
  u.email AS email,
  GREATEST(u.updated_at, TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 700 DAY)) AS updated_at,
  CONCAT(u.user_id, '|', CAST(u.updated_at AS STRING)) AS message_id,
  u.job_title AS trait_job_title
FROM `your-project.app_db.users` u
WHERE GREATEST(u.updated_at, TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 700 DAY)) >= @watermark
```

Every row — even one that's been dormant for years — loads once on the first sync, because the clamp keeps its effective `updated_at` inside the 2-year window. From then on, a row whose real `updated_at` genuinely changes will flow again on its own; a row that never changes again simply won't re-appear.

### g. Keeping account activity fresh

Account rows update in place rather than creating new events each time, so it's safe to re-send an account row on any day it was active — even if nothing else about it changed. Add an `OR` clause on the day of last activity to keep **Accounts** rows current without waiting for some other field to change first:

```sql
SELECT
  a.account_id AS product_org_id,
  a.updated_at AS updated_at,
  a.name AS name,
  a.plan_id AS plan_id,
  a.mrr_cents AS mrr_cents,
  a.last_active_at AS last_active_at
FROM `your-project.app_db.accounts` a
WHERE a.updated_at >= @watermark
  OR DATE(a.last_active_at) >= DATE(@watermark)
```

## Common mistakes

> [!WARNING]
> - **Forgetting `message_id` on the Users feed.** It's easy to assume `message_id` is only for events — it's required on Users too.
> - **Putting user attributes in bare columns instead of `trait_*`.** A column named `job_title` is ignored; it needs to be `trait_job_title`. The preview warns you when a column doesn't match anything.
> - **Sending `mrr` in dollars instead of cents.** `mrr_cents` must be in cents — `9900` means $99.00, not $9,900.00 or $0.99.
> - **Using a message ID that isn't deterministic**, like a randomly generated UUID. A random ID changes every time the same underlying row is read again, which defeats duplicate protection entirely — build it from a stable natural key instead.

## Always preview before saving

Before you save a sync, click **Run preview**. It checks your columns against the feed's requirements, shows you sample rows, a per-row outcome for each one (an event, a user update, an account update, or a rejection with the reason), and the estimated amount of data the query will scan per run — all without waiting for a scheduled run.

## Next Steps

- [Schedules and Run History](/docs/schedules-and-run-history) — set a schedule, read run results, and stop or fix a sync
- [BigQuery Connector Overview](/docs/bigquery-overview) — how this connector fits with everything else