> ## Documentation Index
> Fetch the complete documentation index at: https://docs.parable.work/llms.txt
> Use this file to discover all available pages before exploring further.

# Provider Pool

> Query and join the current data supplied by Provider Plugins.

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

```text theme={null}
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`.

<Note>
  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](/protocols/sql/data-catalog/discover-tables-and-columns)
  instead of assuming that an example table exists.
</Note>

## Query one Provider

Active Google Workspace directory users:

```sql theme={null}
SELECT
  id,
  primaryemail,
  orgunitpath,
  isadmin,
  lastlogintime
FROM providers.google.users
WHERE suspended = false
LIMIT 10
```

Open Linear issues, ordered by their latest update:

```sql theme={null}
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:

```sql theme={null}
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.

The Provider Plugin owns this projection. See the
[Provider Plugins primer](/plugins/provider-plugins) 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:

| 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:

```sql theme={null}
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`.

```sql theme={null}
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:

```sql theme={null}
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.

| 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, and match confidence. Identity resolution is best-effort; service
accounts and shared mailboxes can receive identities too.

```sql theme={null}
SELECT
  identity.id AS identity_id,
  identity.name,
  association.account_id,
  association.account_id_type,
  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:

```sql theme={null}
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](/protocols/sql/sql-behavior/keys-and-relationships)
before relying on a logical key, and use
[Provider quality](/protocols/sql/data-catalog/provider-quality) to inspect the
health of Provider tables.