Provider Pool - Parable

The Provider Pool contains current data from every Provider connected to a Workspace. Each Provider Plugin contributes one or more queryable tables. You can query those tables with ordinary SQL and join tables from different Providers on the same connection.

Address a Provider table

providers.{connector}.{table}

{connector} is the technical connector slug exposed by the Provider Plugin, such as google, github, or linear. {table} is the projected stream name. Examples include:

providers.google.users
providers.github.pull_requests
providers.linear.issues

References are fully qualified. There is no USE statement or search_path.

A table appears only when its Provider is configured for the Workspace and its stream has registered. It can be present before the first sync produces rows. Use catalog discovery instead of assuming that an example table exists.

A query may name a table of a Provider the Workspace has not connected. It reads as an empty table with the columns the Provider Plugin declares, so SQL written for several Providers runs in every Workspace. The Provider’s convenience views (below) read the same way, over those empty tables. The statement’s result schema names each such Provider, as comma-separated connector slugs, in its parable.warning.not_connected_providers metadata, so a reader can show “not connected” rather than zero. Each such table also carries parable.provider.connection = not_connected and the health state not_connected (see Types and metadata). Catalog discovery still lists only connected Providers. A Provider counts as connected once the Workspace has added it, even before any of its streams serves a table. A connected Provider serves only its own tables: a misspelled table, a stream it has not enabled, or any name of a Provider whose streams are all disabled, still fails as an unknown table. Only providers resolves a Provider the Workspace has not connected. In providers_quarantine and providers_quality, a table of such a Provider fails as an unknown table. Those pools hold the records a connected Provider’s syncs set aside and the quality history of its tables, so SQL meant to run in every Workspace reads them only for Providers that catalog discovery lists. A sync that produces no new records is still a valid result, and the table stays queryable. An empty first run returns zero rows. An empty incremental run keeps rows from earlier runs. Rows are removed only when the stream reports a deletion or an exhaustive result proves that the rows are absent. An empty result does not mean that the sync failed.

Query one Provider

Active Google Workspace directory users:

SELECT
  id,
  primaryemail,
  orgunitpath,
  isadmin,
  lastlogintime
FROM providers.google.users
WHERE suspended = false
LIMIT 10

Open Linear issues, ordered by their latest update:

SELECT
  identifier,
  title,
  priority,
  team__key,
  assignee__email,
  state__name,
  updatedat
FROM providers.linear.issues
WHERE completedat IS NULL
  AND canceledat IS NULL
ORDER BY updatedat DESC
LIMIT 20

Open GitHub pull requests in one repository:

SELECT
  number,
  title,
  state,
  user__login,
  draft,
  created_at,
  html_url
FROM providers.github.pull_requests
WHERE full_name = 'your-org/your-repo'
  AND state = 'open'
ORDER BY created_at DESC

Understand projected names

A Provider stream can project one or more tables:

Field names are lowercased: primaryEmail becomes primaryemail.
Flattened object paths use __: assignee.email becomes assignee__email.
Arrays can become child tables named {table}__{path}.

For example, GitHub issue labels can appear at providers.github.issues__labels. The parent ID repeats across child rows, and the child table has its own logical key. Do not use SELECT DISTINCT on the parent ID to remove expected child records. A child row’s parent_pk column holds its parent’s key as text. A string key is copied as it is. A number or boolean key is the text CAST(key AS VARCHAR) returns. A date key is its day count since 1970-01-01, a time key its microseconds since midnight, and a timestamp key its microseconds since the Unix epoch, which CAST(CAST(key AS BIGINT) AS VARCHAR) returns. The convenience views below compare keys this way. A join you write yourself must do the same:

SELECT c.*
FROM providers.claude.usage_report AS p
JOIN providers.claude.usage_report__results AS c
  ON CAST(CAST(p.starting_at AS BIGINT) AS VARCHAR) = c.parent_pk

A floating-point key of magnitude 1e16 or more does not round-trip, so its children do not join by either path; see Keys and relationships. Some streams also expose convenience views that join child rows to their parent, such as providers.github.issues__labels_with_parent, and a {table}_nested view that re-nests each child table into its parent row as an array. A _with_parent view returns one row for each child row whose parent is stored. Most tables keep one row per record, and these views join on the parent ID. Some event streams store an event again each time a load reads it, for example when two loads’ time windows overlap. Such a stream keeps one parent row and one set of child rows per load. Its views pair each child row with the parent row written by the same load, matched on dt and _parable_seal_id as well as the parent ID, so an event read by three loads returns three pairs, not nine. To read one row per event from such a table, keep one load’s rows per parent ID:

SELECT *
FROM providers.microsoft.messages__torecipients_with_parent
QUALIFY dense_rank() OVER (
  PARTITION BY parent_pk ORDER BY dt DESC, _parable_seal_id DESC
) = 1

A view can fail to initialize, for example while storage is temporarily unavailable. A query that names the view then fails, but never as an unknown relation (QL-PL-002): a view whose own definition cannot be planned returns QL-TB-003 with a message naming the view, and other causes keep their own codes. The next query retries without a catalog refresh. GetTables, the semantic catalog, and information_schema leave out a view whose latest initialization failed and keep listing every other table. GetTables and the semantic catalog try every view on each request; information_schema lists the view again after one of those, or a query that names it, succeeds. After a catalog update or service restart, the first information_schema.columns or information_schema.views query can fail on a view that nothing has tried yet. Run it again. The Provider Plugin owns this projection. See the Provider Plugins primer for how a custom Plugin defines streams, schemas, and projection behavior.

Read row state

Every Provider table carries state columns beside the Provider’s own fields. The catalog marks them as system columns and lists them under Show system columns:

Column	Meaning
`dt`	The load day that last wrote the row.
`_parable_observed`	`true` when the latest load contained the row.
`_parable_last_observed_at`	When a load last contained the row.
`_parable_deleted`	`true` when the Provider reported the row deleted, or a full load no longer contained it and the Provider deletes by absence.
`_parable_deleted_at`	When the row was first marked deleted.
`_parable_seal_id`	The load that last wrote the row.

A table that holds the current state of each record, such as providers.google.users, writes a row only when a load changes it: the load contains the record, the record goes missing for the first time, or it is first marked deleted. A record missing from later loads keeps the dt and _parable_seal_id of the day it went missing. WHERE dt = '<latest day>' therefore returns the rows that changed that day, not the whole table. Read current state from the whole table and filter on the state columns:

SELECT id, primaryemail
FROM providers.google.users
WHERE _parable_deleted = false
  AND _parable_observed = true

Join people by semantic email

Columns marked with the Contact.Email semantic scalar represent compatible email values. They can be joined across Providers and to workspace.users.email.

SELECT
  linear.email,
  linear.name AS linear_name,
  google.orgunitpath,
  google.isadmin,
  google.lastlogintime
FROM providers.linear.users AS linear
JOIN providers.google.users AS google
  ON google.primaryemail = linear.email

The same Provider data can join live Workspace membership:

SELECT
  member.email,
  member.name,
  google.orgunitpath,
  google.isadmin
FROM workspace.users AS member
JOIN providers.google.users AS google
  ON google.primaryemail = member.email

Do not join unrelated Provider-native id columns and expect a person match.

Resolve accounts when emails differ

Directory streams can contribute to the providers.identity schema. It appears after at least one identity directory stream has synced. In a Workspace that has added no Provider with an identity directory stream, a query that names one of these tables reads it as an empty table, and the result schema names identity in parable.warning.not_connected_providers. Once the Workspace adds such a Provider, the identity tables count as connected: while its directory stream is disabled, a query that names one fails as an unknown table, as it does for any stream that Provider has not enabled.

Table	Grain
`providers.identity.account`	One row per resolved person.
`providers.identity.account_associations`	One row per Provider account linked to a resolved person.
`providers.identity.account_merges`	One row per identity merge event.

Associations preserve the Provider-native account ID, its type, the match method, match confidence, and the account’s email, name, and photo_url. Identity resolution is best-effort; service accounts and shared mailboxes can receive identities too. email and name are the values the matcher compared, trimmed and lowercased. email is a Contact.Email column: it is null when the account has no email or its email is not valid, and an equality literal compares in canonical form. name is null when the account has no name. Read an identity’s emails and names here instead of joining back to each directory table.

`match_method`	How the account joined its identity
`canonical`	It comes from a canonical person source.
`email_exact`	Its `email` equals the email of an account already in the identity.
`email_local_part`	The part of its email before `@` equals that of an account already in the identity.
`email_fuzzy`	Its `email` is similar to one in the identity; `match_confidence` holds the score.
`name_fuzzy`	Its `name` is similar to one in the identity; `match_confidence` holds the score.
`minted`	No account matched it, so it founded the identity.

For a fuzzy match, the value it matched is on another association of the same identity. providers.identity.account.photo_url is a Network.Url column holding the person’s profile photo as the Provider reported it. It comes from a stream field that declares x-transformPersonPhoto (Slack profile.image_512, Google Workspace thumbnailPhotoUrl). An identity takes its photo the way it takes its name: from the canonical person source first, then from the earliest-connected stream that has one. It is null when no linked account has a photo. Provider photo URLs can expire, so copy the image rather than linking to it. providers.identity.account_associations.photo_url holds each linked account’s own photo, by the same rules. To rank sources another way, or to fall back when a photo cannot be fetched (a default avatar, a directory photo that needs sign-in), read every association’s photo_url instead of the identity’s.

SELECT
  identity.id AS identity_id,
  identity.name,
  association.account_id,
  association.account_id_type,
  association.email,
  association.name AS account_name,
  association.photo_url,
  association.match_method,
  association.match_confidence
FROM providers.identity.account AS identity
JOIN providers.identity.account_associations AS association
  ON association.identity_account_id = identity.id
ORDER BY identity.name, association.match_method

Join work, not only people

Use domain keys when they represent the same work item. A Linear issue branch can join a GitHub pull request head branch without using identity data:

SELECT
  issue.identifier,
  issue.title AS issue_title,
  issue.state__name AS issue_state,
  pull_request.full_name,
  pull_request.number AS pull_request_number,
  pull_request.state AS pull_request_state,
  pull_request.html_url
FROM providers.linear.issues AS issue
JOIN providers.github.pull_requests AS pull_request
  ON pull_request.head__ref = issue.branchname
WHERE issue.branchname IS NOT NULL

See keys and relationships before relying on a logical key, and use Provider quality to inspect the health of Provider tables.

Admin

​Address a Provider table

​Query one Provider

​Understand projected names

​Read row state

​Join people by semantic email

​Resolve accounts when emails differ

​Join work, not only people

Address a Provider table

Query one Provider

Understand projected names

Read row state

Join people by semantic email

Resolve accounts when emails differ

Join work, not only people