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.

Project & conventions

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

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 (below). 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`;

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

Consumer action: UPSERT each delta into your destination using the primary keys defined in the schema.

Handling deletions

Deletions are published to a ledger table. If an instance_id appears there, remove the related rows in your destination.

1. Fetch yesterday's deletions

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;

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

Job template

A daily job comes together 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.

Validation & reconciliation

Validate that you received everything for a daily run:

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.

Performance & reliability

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

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.

Further support

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

Related