Ometria Exporter

Set up

---

To get set up with the Ometria Exporter connector you have to provide the below details on the Source page of the connector:

• API Key - Your Ometria API key, available from your Ometria account settings.

Features

---

Reports detail

---

The Push Contacts report sends one request per row in the source table. Each row's DATA value is sent as-is as the request body to:

POST https://api.ometria.com/v2/push

The Push Errors report sends one request per row in its source table to:

GET https://api.ometria.com/v2/push/_errors

and returns the 100 most recent push errors on your Ometria account.

Source Table Creation

---

• For the Push Contacts report, the source table needs a single DATA column. Each row holds a JSON array of up to 100 contact objects. Ometria requires @type and id on every record, plus at least one identifier (email, customer_id or phone_number). Additional fields are listed in the docs.

  SQL

  CREATE OR REPLACE TABLE PUSH_CONTACTS_DATA (
      DATA VARCHAR NOT NULL
  );
  
  INSERT INTO PUSH_CONTACTS_DATA (DATA)
  VALUES (
      '[
          {
              "@type": "contact",
              "@collection": "default",
              "id": "uuid_4567",
              "customer_id": "uuid_4567",
              "email": "[email protected]",
              "firstname": "Bob",
              "lastname": "Smith",
              "marketing_optin": "EXPLICITLY_OPTEDIN",
              "timestamp_acquired": "2026-04-19T09:00:00+00:00"
          },
          {
              "@type": "contact",
              "@collection": "default",
              "id": "uuid_4568",
              "customer_id": "uuid_4568",
              "email": "[email protected]",
              "marketing_optin": "NOT_SPECIFIED"
          }
      ]'
  );

  If your contacts are in a table with one row per contact, you can batch them into rows of 100 with the following SQL:

  SQL

  CREATE OR REPLACE TABLE PUSH_CONTACTS_DATA AS
  WITH contacts AS (
      SELECT
          ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID) AS n,
          OBJECT_CONSTRUCT(
              '@type',           'contact',
              '@collection',     'default',
              'id',              CUSTOMER_ID,
              'customer_id',     CUSTOMER_ID,
              'email',           LOWER(EMAIL),
              'firstname',       FIRST_NAME,
              'lastname',        LAST_NAME,
              'marketing_optin', MARKETING_OPTIN
          ) AS contact
      FROM MY_SCHEMA.CUSTOMERS
  )
  SELECT TO_JSON(ARRAY_AGG(contact) WITHIN GROUP (ORDER BY n)) AS DATA
  FROM contacts
  GROUP BY CEIL(n / 100);

  OBJECT_CONSTRUCT leaves out keys whose value is NULL, so empty columns won't clear existing values in Ometria.

• For the Push Errors report, the source table is a trigger table with exactly one row. Each row sends one request, so one row gives one fetch of the latest errors. The column value isn't used by the API; it's copied into the responses table as a label.

  SQL

  CREATE OR REPLACE TABLE PUSH_ERRORS_TRIGGER AS
  SELECT 'daily' AS RUN_LABEL;

  Don't name trigger columns url, method, content_type, query_params, response or error, as these would overwrite the matching columns in the responses table.

Reading the responses

• Push Contacts - each row's response holds a JSON object like {"status": "OK", "request_id": "…", "accepted": 100, "rejected": 0, "skipped": 0}. A successful response means the batch was queued, not yet applied.

• Push Errors -the error details are in the _responses table of the single-row, single-column trigger table (e.g. PUSH_ERRORS_TRIGGER_RESPONSES). Each row's response holds a JSON array of errors. Each error has request_id, record_type, record_id, timestamp, message (error_message, error_path) and record (a copy of the record that failed). You can flatten it with the following SQL:

  SQL

  SELECT
      e.value:request_id::STRING            AS request_id,
      e.value:record_type::STRING           AS record_type,
      e.value:record_id::STRING             AS record_id,
      e.value:timestamp::STRING             AS error_timestamp,
      e.value:message:error_message::STRING AS error_message,
      e.value:message:error_path::STRING    AS error_path,
      e.value:record                        AS record
  FROM MY_SCHEMA.PUSH_ERRORS_TRIGGER_RESPONSES r,
       LATERAL FLATTEN(input => PARSE_JSON(r.RESPONSE)) e
  QUALIFY ROW_NUMBER() OVER (
      PARTITION BY request_id, record_id, error_timestamp
      ORDER BY r._KLEENE_EXTRACT_DATE DESC
  ) = 1;

  The request_id matches the request_id in the Push Contacts responses table, so you can join the two to see which batch each error came from.

Limitations

---