CloudServices/Sync/ReDash

Table Described

The Sync team's telemetry goes through our data pipeline and lands in Presto to be explored in Re:Dash. Below is a description of the data available in the sync_summary table. Further documentation on this data is available in the telemetry-batch-view repo, and in the general sync ping documentation.

Field Names	Data Type	Description
app_build_id	varchar	Firefox build ID (e.g. 20160801085519)
app_version	varchar	Firefox version (e.g. 50.0a2) - corresponds to the MOZ_APP_VERSION configure value
app_display_version	varchar	The application version as shown in the about dialog. Almost always identical to app_version. Corresponds to the MOZ_APP_VERSION_DISPLAY configure value.
app_name	varchar	Will always be the string "Firefox" in desktop submitted pings.
app_channel	varchar	The update channel (e.g. "release", "nightly", etc) - corresponds to the MOZ_UPDATE_CHANNEL config variable.
uid	varchar	Hashed Sync/FxA ID
deviceid	varchar	Hashed FxA device ID.
when	bigint	Unix timestamp of when sync occurred. Make sure to put in "quotes" since when is a reserved SQL word. Note that because this is taken from the client's clock, the time may be wildly inaccurate.
took	bigint	Number of milli-seconds it took to Sync.
failurereason	row(name varchar, value varchar)	Sync failure reason, or null if no failure.
status	row(sync varchar, service varchar)	The status of sync after completion, or null is both statuses record success.
why	varchar	Currently always null, but eventually should be the reason the sync was performed (eg, timer, button press, score update, etc)
devices	array(row(id varchar, os varchar, version varchar))	Array of the other devices in this user's device constellation.
engines	array(engine_record)	A record of the engines that synced. Each element of the array is in the format of an engine record.
submission_date_s3	varchar	The date this ping was submitted to the telemetry servers. Because a ping will typically be sent for a previous session immediately after a browser restart, this submission date may be later than the date recorded in the ping. Note also that this is a timestamp supplied by the server so is likely to be more reliable than the dates recorded in the ping itself.

Engine Record

An engine record is defined as:

Field Name	Data Type	Description
name	varchar	The name of the engine.
took	bigint	How many milliseconds this engine took to sync.
status	varchar	The status of this engine after sync completed, or null is the status reflects success.
failureReason	row(name varchar, value varchar)	Details of any errors recorded during the sync of this engine, or null on success.
incoming	row(applied bigint, failed bigint, newFailed bigint, reconciled bigint)	Details of how many incoming records were applied, failed, failed for the first time in this sync, and reconciled.
outgoing	array(row(sent bigint, failed bigint)))	For each batch of records uploaded as part of a Sync, how many records were sent in that batch and how many failed.
validation	validation_record	Validation information for this engine. Null if validation cannot or did not run during this sync (common). If present, it's format is of a validation record

Validation Record

A validation record is defined as:

Field Name	Data Type	Description
version	bigint	Version of the validator used to get this data.
checked	bigint	Number of records the validator checked in this engine.
took	bigint	How long validation took for this engine.
problems	array(row(name varchar, count varchar))	The problems identified. Problems with a count of 0 are excluded. Null on failure or if no problems occurred.
failureReason	row(name varchar, value varchar)	Details of any errors recorded during the validation, or null if validation succeeded.

FAQ

Q1: In engines, we have status and failureReason, how are they different from the columns with the same names?

A1: These are the exceptions and status for the engine itself, whereas the columns at the top-level of the table are for the entire sync. The error handling should be cleaned up, but in general, failureReason will be reporting bugs, whereas "status" is reporting when we explicitly decided that we couldn't apply a record.

---

Q2: Do we only log the engines array when we see "service":"error.sync.failed_partial" in status?

A2: I don't think that's true - eg, "select * from sync_summary where engines is not null and status is null limit 10" shows records.

---

Q3: What values are valid in the validation record's name field?

A3: It's up to the engine and to the client. For desktop bookmarks, they're documented here (or here if that link breaks).

Query Examples

The example below demonstrates how to select data in JSON object.

WITH errors AS (
  SELECT 
  	failurereason.name AS name, 
  	failurereason.value AS value 
  FROM sync_summary 
  WHERE failurereason IS NOT NULL
)
SELECT 
	name, 
	COUNT(value) 
FROM errors 
GROUP BY name

The next example shows how to handle unix time stamps and how to use the Re:Dash date picker. It's important to either cast the date or to use the type constructor like below or you won't be able to use any operators on the date which is required for the date picker.

WITH syncs AS (
	SELECT
		/* convert date from unix timestamp */
		date_trunc('day', from_unixtime("when"/1000)) AS day,
  		status.service AS service
	FROM
		sync_summary
)
SELECT day, status, volume
FROM (
  SELECT
    day,
    'success' as status,
    SUM(
      CASE 
      WHEN service IS NULL THEN 1 
      ELSE 0 
      END
    ) AS volume
  FROM syncs
  GROUP BY day

  UNION ALL

  SELECT
    day,
    'failed' as status,
    SUM(
      CASE 
      WHEN service IS NOT NULL THEN 1 
      ELSE 0 
      END
    ) AS volume
  FROM syncs
  GROUP BY day
)
/* date picker */
WHERE day >= timestamp '{{start_date}}' AND day <= timestamp '{{end_date}}'

GROUP BY 1,2,3
ORDER BY 1,2,3

This example is how you would unpack the engines array into it's own table to then query:

WITH engine_errors AS (
  SELECT 
  	uid, 
  	date_trunc('day', from_unixtime("when"/1000)) AS date,
  	engine
  FROM sync_summary
/* The CROSS JOIN UNNEST will join the array to each row */
  CROSS JOIN UNNEST(engines) AS t (engine)
  --LIMIT 1000000
)

SELECT 
	engine.name AS engine_name,
	SUM(
      CASE
      WHEN engine.failureReason IS NOT NULL THEN 1
      ELSE 0
      END
    ) AS errors
FROM engine_errors
WHERE date >= timestamp '{{start_date}}' AND date <= timestamp '{{end_date}}'
GROUP BY engine.name