Skip to main content
The providers pool holds the data from every Provider that your Workspace connected. Each Provider is a schema. Each data stream becomes one or more tables. You query them with ordinary SQL over the same Flight SQL connection, and you can join any of these tables to each other. Google Workspace directory users are providers.google.users. GitHub pull requests are providers.github.pull_requests. Linear issues are providers.linear.issues. You can join a person across these Providers on email, or through providers.identity.* when the emails do not match. A data stream lands as one or more tables in its Provider’s schema. Column names are the source field names, lowercased (primaryEmail becomes primaryemail). Flattened object paths use __ between segments (assignee.email becomes assignee__email). Arrays can become child tables. Tap projection explains these rules.
The catalog lists a table when its Provider is connected and its stream is enabled. It can list the table before the first sync writes rows. The examples on this page use common Providers. Use the tables and columns that the catalog lists for your Workspace.

Address a table

{connector} is the Provider’s connector slug, such as google, github, linear, slack, okta, workday, or salesforce. A - in a slug becomes _. {table} is the stream’s table name. A child table is {table}__{path}. GitHub issue labels are providers.github.issues__labels. References are fully qualified. There is no USE statement or search_path. Catalog discovery reports each table’s logical key and the semantic type of each column. Read them there instead of guessing from column names.

Tables of Providers you did not connect

A query can name a table of a Provider that your Workspace did not connect, or of a stream that it did not enable. That table reads as an empty table with the columns the Provider declares. SQL written for several Providers therefore runs in every Workspace. If the tap declares captureUnknownFields: true, the empty table also has the raw_unknown_fields Map column. SQL can name a captured field such as raw_unknown_fields['Division'] before the Provider is connected. An aggregate over that empty table returns zero for count(*) and NULL for max(raw_unknown_fields['Division']). The result schema says what was missing, so your application can show “not connected” instead of zero:
  • parable.warning.not_connected_providers names each Provider that is not connected.
  • parable.warning.not_enabled_streams names each stream that is not enabled, such as slack.replies.
A misspelled table still fails as an unknown table.

See what you have

List tables, columns, and row counts in the catalog before you assume that a table has rows. Your connected Providers and their streams are also live in the workspace pool. This query lists the active Providers:
This query lists the streams of each Provider and whether each is enabled:
Query the pools has the Flight SQL clients for each language.

Query one Provider

Active Google Workspace users, with org unit and last login:
Open Linear issues, most recently updated first:
Open GitHub pull requests in one repository. Parable copies full_name from the repository onto each pull request, so you do not have to join repositories:

Read current rows

Every Provider table has state columns beside the Provider’s own fields: The catalog marks these as system columns (system: true), and the app hides them unless you turn on Show system columns. You can always select them. On an event table, filter on _parable_event_date to read whole days. Parable then reads only the files of those days:
Parable writes a row only when a load changes it. WHERE dt = '<day>' therefore returns the rows that changed that day, not the whole table. To read the current records, read the whole table and filter on the state columns:

Join Providers on email

Columns with the Contact.Email semantic type hold compatible email values. You can join them to each other and to workspace.users.email. Linear email and Google primaryemail are both Contact.Email columns:
The same join against Workspace members:
People who left in Workday but still have a Google account:
Open Salesforce opportunities whose owner is suspended in Google:

Join Providers when emails differ

A GitHub organization member has a login and an id, but no email. Do not join the id columns of two Providers and expect a person match. Directory streams feed the providers.identity schema. Examples are Google users, Linear users, GitHub members, Slack users, Okta users, and Workday workers. The schema appears after at least one directory stream has synced. Until the Workspace enables a directory stream, a query that names one of its tables reads an empty table. Each association keeps where the account came from. connector_slug names the Provider, and account_id is the Provider’s own ID for the account, such as Google users.id or GitHub members.id. account_id_type is id, uuid, or email. email and name are the account’s email and name. An account joins an identity only through an exact link. The link is the same Provider account ID, the same email, or an external user ID that names the account. For example, a Linear user’s githubuserid links that user to the GitHub member with that ID. A similar email or an equal name is not a link. One person whose accounts share no exact link is therefore counted as two identities. Service accounts and shared mailboxes get identities too. match_method records how the account joined its identity: Every method is exact, so match_confidence is always 1.0. Historical results can still show email_local_part, email_fuzzy, or name_fuzzy. Parable no longer writes these values. Parable recomputes identities each time identity resolution runs. When a run splits an identity, the part with most of its accounts keeps the id. A stored identity id can therefore name a different person later. For a key that never moves, keep the account’s connector_tap_id and account_id. Every account linked to a resolved person:
A Google directory row plus every other account of the same person, including GitHub members that have no email:
People linked to more than one account. Start here to see whether identity coverage is dense enough to join two specific Providers:
Provider Pool describes every identity column.

Join work items, not only people

Linear issues carry branchname. GitHub pull requests carry the head branch in the flattened head object. This join needs no email and no identity:
Google group owners who are also directory users, with org unit and admin flag:
providers.google.groups is the group list (id, name, email, directmemberscount). Each member row carries its group’s ID in group_id, so gm.group_id = g.id joins a member to its group.

Child tables

An array of objects can become a second table, {table}__{path}. Each child row repeats its parent’s key in parent_pk, as text. GitHub issue labels:
A child table has its own logical key. Do not use SELECT DISTINCT on the parent key to remove rows: the parent key is supposed to repeat. Some streams also have convenience views:
  • {table}__{path}_with_parent joins each child row to its parent, such as providers.github.issues__labels_with_parent. It returns one row for each child row whose parent is stored.
  • {table}_nested puts each child table back into its parent row, as an array.
Tap projection explains child tables and how to join them yourself.

Sample a large table

TABLESAMPLE reads a sample of a table or a subquery. Use it to try a query, or an AI function, on a large table before you run it on every row:
  • A size without a unit is a percentage, from 0 to 100. BERNOULLI, ROW, SYSTEM, and BLOCK all sample rows.
  • Parable keeps a row when a hash of its values and the seed is below the percentage. The same seed keeps the same rows of the same data, so two samples of one table in one query keep the same rows.
  • Without REPEATABLE or SEED, the seed is 0. A query that samples therefore returns the same rows each time. Postgres picks a new seed each time instead.
  • (n ROWS) keeps the n rows with the lowest hash.
  • BUCKET k OUT OF n keeps the rows in the k-th of n equal ranges. The n buckets together hold every row once.
  • A join of two sampled tables keeps about the product of their percentages: 10 percent of each keeps about 1 percent of the matches.
TABLESAMPLE ... OFFSET and BUCKET ... ON are refused with QL-PL-012. A query with a percentage outside 0 to 100, or with a row count, a seed, or a bucket that is not a whole number, fails before it runs.

Rows you can read

Provider Policies and Provider Permissions can narrow the rows of a Provider table for you. Rows you can read explains how. A Provider whose streams describe access, such as file sharing or channel membership, adds the providers.access schema. Parable computes its seven tables from those streams. They describe who can open each record, including records that the person who reads them cannot open: Only people with All data access, and Parable staff with permission to query your Workspace’s data, can read these tables, in SQL that names no Parable. Each read is recorded. For everyone else, Data Access members included, the tables are not in the catalog or in information_schema, and a query that names one fails as an unknown table. Nobody reads them in a Parable or under View as. A holder of All data access finds their columns in information_schema.columns over Flight SQL. Provider Permissions explains the declarations that produce these tables.

Quality and other pools

Quality checks, SPC, and shape statistics for Provider tables live in Quality: the providers_quality.summary.* tables, and history tables such as providers_quality.google.users__checks. Identity tables have no quality history. A Plot can read Provider tables, and its results appear in parables. Published files in artifacts join Provider tables the same way as any other table, often on an email column.