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.

Engine Record

An engine record is defined as:

Validation Record

A validation record is defined as:

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

Event Telemetry

Sync will often write "events" to telemetry. These are made available via the sync_events_v1 table.

Table Description

Event Usage

Commands sent between clients

Sometimes Sync will send a "command" from one device to another. The most obvious example of this is "send tab". Event telemetry is recorded both by the sending device and the receiving device.

In all cases, the sending device will create a unique "flowID" which will appear in event_flow_id, and it will send this value as part of the command. The device receiving the command will record this flow ID in the event it creates - so both the sending and receiving device will record the same flowID.

In all cases, the sending device will record in the event the ID of the device that is to receive the command. However, the receiving device does not record the device that sent the command.

The sending device will record: event_object="sendcommand", event_method will be the specific command being sent (eg, "displayURI" for sendTab).

The receiving device will record: event_object="processcommand", event_method will be the command being sent.

Commands sent by the repair process

Bookmark repair is fairly chatty when it comes to events - both the device initiating the repair and any other devices participating in the repair process record events, and they all use the same flowID - so it should be possible to reconstruct the repair process by looking for all events with the same flow ID.

The general repair process is:

1. One device finds problems and starts a repair. It emits an event event_object="repair", event_method="started".
2. It then attempts to find other devices able to help with the repair - if it finds any, it will emit event_object="repair", event_method="request", event_string_value="upload", and includes the device ID of the device it wants to respond - and waits.
3. Hopefully the other device will see the repair request. If it finds any objects it can upload, it will emit an event event_object="repairResponse", event_method="uploading".
4. Once the upload is complete, it will write an event event_object="repairResponse", event_method="finished".
5. If the repair responder fails for some reason, it will emit event_object="repairResponse", event_method="failed" or "aborted"
6. If initial device that sent the repair request doesn't see a response from the other device it will emit event_object="repair", event_method="silent". If the other device disappears completely, it will write event_object="repair", event_method="missing".
7. The initial device will then check to see if it is still missing items, and if so, attempt to find a new client. If it can, it jumps back to (2) above.
8. The initial device will eventually run out of clients, or will find all of its IDs. At that time it will emit event_object="repair", event_method="finished" - or if it gives up due to some error it will emit event_object="repair", event_method="failed"

whew! So in summary:

• every repair should emit event_object="repair", event_method="started" as it starts, and either event_object="repair", event_method="finished/failed" as the last event.
• There may be any number of repairResponse events written (including zero events if there are no suitable devices). Each of these should result in a event_object="repairResponse", event_method="finished/aborted"

Bookmark Validation Data Roll-ups

telemetry.sync_bmk_total_per_day

This table is a roll-up of all bookmark validations (success and failures).

This table is useful to calculate rates in conjunction with the telemetry.sync_bmk_validation_problems table.

telemetry.sync_bmk_validation_problems

This table was rolled-up using the following workbook: https://gist.github.com/kitcambridge/364f56182f3e96fb3131bf38ff648609

This table has a row for every validation problem detected during validation.

Example: If a user has 4 different validation problems, we will log each problem in their own row.

Logic used for format: This choice was made to avoid having endless amounts of columns representing each validation problem checked (which would mostly all be null) and to avoid having to add columns as we detect new types of validation problems.

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