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

Users

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

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), 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:

1SELECT
2  'signed_in' AS event_name,
3  e.event_time AS occurred_at,
4  CONCAT(e.user_id, '|', CAST(e.event_time AS STRING)) AS message_id,
5  e.user_id AS user_id,
6  e.email AS email,
7  e.account_id AS product_org_id
8FROM `your-project.analytics.events` e
9WHERE e.event_time >= @watermark
10  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:

1WITH signups AS (
2  SELECT
3    'signup' AS event_name,
4    s.created_at AS occurred_at,
5    CONCAT('signup|', s.user_id, '|', CAST(s.created_at AS STRING)) AS message_id,
6    s.user_id AS user_id,
7    s.email AS email,
8    s.account_id AS product_org_id,
9    s.plan_selected AS prop_plan_selected,
10    CAST(NULL AS STRING) AS prop_invited_by
11  FROM `your-project.app_db.signups` s
12  WHERE s.created_at >= @watermark AND s.created_at < @watermark_end
13),
14invites AS (
15  SELECT
16    'invite_sent' AS event_name,
17    i.sent_at AS occurred_at,
18    CONCAT('invite|', i.invite_id) AS message_id,
19    i.inviter_user_id AS user_id,
20    i.inviter_email AS email,
21    i.account_id AS product_org_id,
22    CAST(NULL AS STRING) AS prop_plan_selected,
23    i.invitee_email AS prop_invited_by
24  FROM `your-project.app_db.invites` i
25  WHERE i.sent_at >= @watermark AND i.sent_at < @watermark_end
26)
27SELECT * FROM signups
28UNION ALL
29SELECT * 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:

1SELECT DISTINCT
2  'signed_in' AS event_name,
3  TIMESTAMP(DATE(v.visited_at)) AS occurred_at,
4  CONCAT(v.user_id, '|', CAST(DATE(v.visited_at) AS STRING)) AS message_id,
5  v.user_id AS user_id,
6  v.email AS email,
7  v.account_id AS product_org_id
8FROM `your-project.analytics.visits` v
9WHERE v.visited_at >= @watermark
10  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:

1WITH account_owner AS (
2  SELECT
3    m.account_id,
4    ARRAY_AGG(m.user_id ORDER BY IF(m.role = 'owner', 0, 1) LIMIT 1)[OFFSET(0)] AS owner_user_id
5  FROM `your-project.app_db.memberships` m
6  GROUP BY m.account_id
7)
8SELECT
9  'plan_upgraded' AS event_name,
10  p.upgraded_at AS occurred_at,
11  CONCAT('plan_upgraded|', p.account_id, '|', CAST(p.upgraded_at AS STRING)) AS message_id,
12  ao.owner_user_id AS user_id,
13  u.email AS email,
14  p.account_id AS product_org_id
15FROM `your-project.app_db.plan_changes` p
16JOIN account_owner ao ON ao.account_id = p.account_id
17JOIN `your-project.app_db.users` u ON u.user_id = ao.owner_user_id
18WHERE p.upgraded_at >= @watermark
19  AND p.upgraded_at < @watermark_end
20  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:

1SELECT
2  'feature_adopted' AS event_name,
3  f.adopted_at AS occurred_at,
4  CONCAT(f.account_id, '|', f.feature_name) AS message_id,
5  f.account_id AS anonymous_id,
6  f.feature_name AS prop_feature_name,
7  f.account_id AS product_org_id
8FROM (
9  SELECT account_id, feature_name, adopted_at
10  FROM `your-project.app_db.account_features`
11  UNPIVOT(adopted_at FOR feature_name IN (
12    first_reports_at AS 'reports',
13    first_api_key_at AS 'api_keys',
14    first_sso_at AS 'sso'
15  ))
16) f
17WHERE f.adopted_at >= @watermark
18  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:

1SELECT
2  u.user_id AS user_id,
3  u.email AS email,
4  GREATEST(u.updated_at, TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 700 DAY)) AS updated_at,
5  CONCAT(u.user_id, '|', CAST(u.updated_at AS STRING)) AS message_id,
6  u.job_title AS trait_job_title
7FROM `your-project.app_db.users` u
8WHERE 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:

1SELECT
2  a.account_id AS product_org_id,
3  a.updated_at AS updated_at,
4  a.name AS name,
5  a.plan_id AS plan_id,
6  a.mrr_cents AS mrr_cents,
7  a.last_active_at AS last_active_at
8FROM `your-project.app_db.accounts` a
9WHERE a.updated_at >= @watermark
10  OR DATE(a.last_active_at) >= DATE(@watermark)

Common mistakes

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 — set a schedule, read run results, and stop or fix a sync
• BigQuery Connector Overview — how this connector fits with everything else