Data reference

Queries on Pipeloom data read these tables. Each environment sees only its own rows, so you never filter by environment yourself. Write from syncs, not a schema name.

These names are stable. New tables and columns may be added, but existing ones won't be renamed or removed.

Times are timestamptz. Ids are uuid, except sync job ids, which are numbers.

syncs

One row per sync job.

Column	What it is
`job_id`	The sync job.
`connection_id`	The connection that ran.
`pipeline_id`	The pipeline the connection belongs to, if any.
`job_type`	`sync`, or a reset or refresh.
`status`	`succeeded`, `failed`, `cancelled`, `running` and so on.
`created_at`, `started_at`, `ended_at`	When the job was created, started and ended.
`duration_seconds`	How long it ran.
`attempts`	How many attempts it took.
`records_extracted`, `records_loaded`, `records_rejected`	Record counts.
`bytes_extracted`, `bytes_loaded`	Byte counts.
`failure_origin`, `failure_type`, `failure_message`	Why it failed, when it did.

stream_syncs

One row per stream per sync job: the counts in syncs, per stream.

Column	What it is
`job_id`, `connection_id`	The sync job and its connection.
`stream_namespace`, `stream_name`	The stream. The namespace is empty when there is none.
`created_at`, `ended_at`	When the job was created, and when this stream finished.
`records_extracted`, `records_loaded`, `records_rejected`	Record counts for the stream.
`bytes_extracted`, `bytes_loaded`	Byte counts for the stream.
`run_state`	How the stream ended, for example `complete` or `incomplete`.
`incomplete_cause`	Why it didn't complete, when it didn't.
`was_backfilled`, `was_resumed`	Whether this was a backfill, or resumed an earlier attempt.

failures

One row per failure in a sync's attempts.

Column	What it is
`job_id`, `attempt_number`, `seq`	The job, the attempt, and the failure's place in it.
`connection_id`	The connection.
`failed_at`	When it failed.
`origin`	Where it failed: the source, the destination, the platform…
`type`	The kind of failure, for example `config_error` or `system_error`.
`message`	The failure message.

schema_changes

One row per schema change found at a source.

Column	What it is
`event_id`	The change.
`connection_id`	The connection.
`changed_at`	When it was found.
`streams_added`, `streams_removed`	Streams that appeared or went away.
`fields_added`, `fields_removed`, `fields_changed`	Columns that appeared, went away or changed type.
`breaking`	Whether the change breaks the connection.

pipeline_runs

One row per pipeline run.

Column	What it is
`execution_id`	The run.
`pipeline_id`	The pipeline.
`status`	How the run ended, or that it is still running.
`state`	The run's state as the orchestrator reports it.
`started_by`	`landing` when a sync landing new data started it, `pipeline` when another pipeline ran it, otherwise `schedule`.
`started_at`, `ended_at`, `duration_seconds`	When it ran and for how long.
`steps`	How many steps it ran.

step_runs

One row per step in a pipeline run.

Column	What it is
`execution_id`, `step_id`	The run and the step.
`pipeline_id`	The pipeline.
`status`	How the step ended.
`attempts`	How many attempts it took.
`started_at`, `duration_seconds`	When it started and how long it took.
`busy_seconds`	Time spent working rather than waiting.
`job_id`	For a sync step, the sync job it ran.

connections and pipelines

Names for the ids in the other tables, so a query can show names.

Table	Columns
`connections`	`connection_id`, `name`, `status`, `schedule_type`, `source_name`, `source_type`, `destination_name`, `destination_type`, `updated_at`
`pipelines`	`pipeline_id`, `name`, `deployed`, `updated_at`

Views

View	What it is
`freshness`	Per stream: `last_loaded_at`, `usual_gap_seconds` (the median time between loads over the last 28 days), and `overdue` (true when it's been more than twice that).
`daily_volume`	Per connection per day: `syncs`, `succeeded`, `failed`, `records_loaded`, `records_rejected`, `bytes_loaded`.

Example

Failed syncs per connection in the last 7 days, by name:

select c.name, count(*) as failed
from syncs s
join connections c using (connection_id)
where s.status = 'failed'
  and s.created_at > now() - interval '7 days'
group by c.name
order by failed desc

On this page