​

Warehouse Sync

​

1. Create a read-only user

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out sequenzy_key.p8 -nocrypt
openssl rsa -in sequenzy_key.p8 -pubout -out sequenzy_key.pub

CREATE ROLE SEQUENZY_READ;
GRANT USAGE ON WAREHOUSE ANALYTICS_WH TO ROLE SEQUENZY_READ;
GRANT USAGE ON DATABASE PROD TO ROLE SEQUENZY_READ;
GRANT USAGE ON SCHEMA PROD.PUBLIC TO ROLE SEQUENZY_READ;
GRANT SELECT ON ALL TABLES IN SCHEMA PROD.PUBLIC TO ROLE SEQUENZY_READ;
GRANT SELECT ON ALL VIEWS IN SCHEMA PROD.PUBLIC TO ROLE SEQUENZY_READ;

CREATE USER SEQUENZY_READER TYPE = SERVICE DEFAULT_ROLE = SEQUENZY_READ
  RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';  -- contents of sequenzy_key.pub without the header lines
GRANT ROLE SEQUENZY_READ TO USER SEQUENZY_READER;

CREATE USER sequenzy_reader PASSWORD '...';
GRANT USAGE ON SCHEMA analytics TO sequenzy_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO sequenzy_reader;

​

2. Write the query

select
  u.email,
  u.id as user_id,
  u.first_name,
  a.plan,
  a.mrr,
  u.updated_at
from analytics.users u
join analytics.accounts a on a.id = u.account_id
where u.email is not null

​

3. Map columns

​

4. Choose how it runs

• Cursor column. Pick a column that grows whenever a row changes, such as updated_at. Each run then reads only rows at or after the last value it saw, which keeps large tables cheap. Rows with an empty cursor are not read. Without a cursor, every run reads the whole result (up to 2,000,000 rows) and skips unchanged rows. Your warehouse bills each run as it bills any query; on BigQuery, a cursor on a partitioned or clustered column keeps the bytes scanned small.
• Schedule. Every 15 minutes, hourly, daily, weekly, or manual only. The first run starts when you create the sync.
• Start automations. Off by default. When on, new contacts can enter sequences that start on contact creation, and events from the last hour can start or advance sequences.
• Double opt-in. Off by default, for warehouses that only hold contacts who already agreed to receive email. When on, new contacts get a confirmation email first.

​

How runs work

• Only changes are sent. Sequenzy keeps a fingerprint of every row it sent. Unchanged rows are counted as unchanged and skipped. A row is only recorded as sent after the import or event write accepted it. Rows that failed inside Sequenzy are read again on the next run; a row that keeps failing where the last run stopped is skipped with a note so the sync can move on. If an import later loses part of its rows, the next run sends every row again, up to three times a day.
• Long backfills continue on their own. With a cursor, a run stops after 30 minutes and the next one starts right away from where it stopped. Without a cursor, the rest waits for the next scheduled run. More than 2,000,000 rows with the same cursor value stop the sync with an error; use a more precise cursor column.
• Edits apply right away. Pausing, deleting or changing a sync stops a run in progress at its next page of rows; a changed sync then runs again with the new settings.
• Progress. The dashboard, sequenzy warehouse syncs runs and the API show rows read, synced, unchanged, skipped and failed, sample problems with their row numbers, and the progress of the contact imports each run queued.
• Failures retry. When a run fails, for example because of an expired key or a SQL error, the sync shows the reason and retries with backoff. Fix the problem and choose Run now to retry immediately.
• Full resync. Forgets what was sent and the cursor position, then sends every row again. Use it after changing data without touching the cursor column.

​

Permissions