Syncing data

This guide shows how to keep your own database in sync with the e-satisfaction BigQuery warehouse. It covers three jobs: a one-off full refresh to load everything, a daily incremental sync that pulls only what changed, and how to handle deletions so removed records don't linger downstream.

Who this is for

This is a developer-facing guide for the team building your sync. You'll need Data Warehouse access and the service-account credentials the e-satisfaction team provides.

Why sync

Querying the warehouse directly is the quickest way to explore your data. Syncing it into your own database is the better fit when you want e-satisfaction feedback to sit next to your own data — joined with orders, CRM records or other sources in the tools your teams already use. A good sync stays small and predictable: load everything once, then each day pull only what changed and remove what was deleted.

Before you start

Source dataset — your data lives in a dataset of the form client-data-429110.client_<client_name>. Substitute your own dataset name throughout.
Time logic — there's no timezone handling; compare against DATE(updated_time) and CURRENT_DATE().
Keys & UPSERT — primary keys are marked in the schema documentation. The rule is simple: same key ⇒ update; otherwise insert.
Tables — every table referenced here is listed in that schema documentation, which is your map for the full set.

Set up your sync

Run a full refresh (one-off / rebaseline)

On the first run, load the full current state of each table you need. Every run after that can be an incremental sync. Optionally add AND organization_id = @organization_id to scope the data when your dataset spans multiple organizations.

-- Normalized responses (examples)
SELECT * FROM `client-data-429110.client_<client_name>.n_responses`;
SELECT * FROM `client-data-429110.client_<client_name>.d_responses_csat`;
SELECT * FROM `client-data-429110.client_<client_name>.d_responses_nps`;
SELECT * FROM `client-data-429110.client_<client_name>.d_responses_rating`;

-- Flat
SELECT * FROM `client-data-429110.client_<client_name>.f_instances`;
SELECT * FROM `client-data-429110.client_<client_name>.f_responses`;

-- Instances, configuration, queue & tags
SELECT * FROM `client-data-429110.client_<client_name>.n_questionnaire_instances`;
SELECT * FROM `client-data-429110.client_<client_name>.n_questionnaires`;
SELECT * FROM `client-data-429110.client_<client_name>.n_queue_items`;
SELECT * FROM `client-data-429110.client_<client_name>.n_tags`;
SELECT * FROM `client-data-429110.client_<client_name>.n_topics`;

Pull the daily incremental

Every day, pull just the records that changed yesterday, so you sync only what's missing. You can widen the timeframe if a run failed or was skipped. All tables are partitioned on updated_time, so filtering on it keeps queries fast.

Declare yesterday once and reuse it:

DECLARE yesterday DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

SELECT *
FROM `client-data-429110.client_<client_name>.n_responses`
WHERE DATE(updated_time) = yesterday;
-- ...repeat for the other tables in your scope

Then UPSERT each delta into your destination using the primary keys defined in the schema.

Fetch yesterday's deletions

Deletions are published to a ledger table. If an instance_id appears there, the related rows need to be removed from your destination.

DECLARE yesterday DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

SELECT instance_id
FROM `client-data-429110.client_<client_name>.n_deleted_questionnaire_instances`
WHERE DATE(deleted_time) = yesterday;

Propagate the deletes

When an instance is deleted, the deletion must be propagated to its "child" tables. Delete rows matching each instance_id across:

n_responses and all d_responses_*
f_instances, f_responses, all f_responses_*, and f_sentiment
n_questionnaire_instance_metadata

Before deleting downstream, you can preview how many rows each table would lose by joining your destination tables against the day's deleted instance_ids — a quick sanity check that the deletion is the size you expect.

Validate the run

Check that you received everything for the day:

DECLARE yesterday DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

SELECT COUNT(*) AS changed_instances
FROM `client-data-429110.client_<client_name>.n_questionnaire_instances`
WHERE DATE(updated_time) = yesterday;

SELECT COUNT(*) AS deleted_instances
FROM `client-data-429110.client_<client_name>.n_deleted_questionnaire_instances`
WHERE DATE(deleted_time) = yesterday;

Spot checks — sample a few instance_ids from yesterday's pull and verify destination parity after UPSERT and DELETE.
Periodic parity — reconcile monthly counts by organization_id, questionnaire_id and question_id.

Putting it together: a daily job

A daily job combines steps 2 to 5 like this:

RUN_DATE   = CURRENT_DATE()
YESTERDAY  = RUN_DATE - 1 day

# 1) Pull deltas WHERE DATE(updated_time) = YESTERDAY for each table in scope
# 2) UPSERT into destination using natural keys (see Keys & UPSERT)
# 3) Deletions:
deleted_ids = SELECT instance_id
              FROM n_deleted_questionnaire_instances
              WHERE DATE(deleted_time) = YESTERDAY
FOR table IN [instances, responses*, flats, sentiment, metadata]:
    DELETE FROM <dest.table> WHERE instance_id IN (deleted_ids)
# 4) Emit audit metrics (rows_upserted, rows_deleted, run_date, run_id)

The UPSERT step looks slightly different per destination — for example a MERGE in BigQuery or Snowflake, or an INSERT ... ON CONFLICT ... DO UPDATE in PostgreSQL — but the logic is the same: match on the natural keys, update on match, insert otherwise.

FAQ

Use d_responses_* or f_responses_*? Prefer the normalized d_responses_* / n_responses tables for analytics; the flat f_* tables are handy for simple ingestion.

Why "yesterday" and not "last N hours"? It guarantees stable, partition-pruned daily slices with simple scheduling.

How are deletions surfaced? Exclusively via n_deleted_questionnaire_instances; consumers must delete downstream by instance_id.

Keep it fast and safe to re-run

Always include WHERE DATE(updated_time) = @yesterday for partition pruning.
Design idempotently: re-runs converge via UPSERT.
For late arrivals, use a small lookback window — for example, the last three days rather than only yesterday.
Keep destination columns nullable to absorb schema additions.
Scope by tenant where applicable with AND organization_id = @organization_id.

Further support

For help, contact [email protected] or open a ticket through the Support Portal.

Data Warehouse

Back to the warehouse overview.

Sharing access

Grant a BigQuery service account access to your dataset.

Metrics & pillars

How the metrics behind the schema keep reporting consistent.

Data pipelines (out)

Request warehouse access and connect.