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 declarescaptureUnknownFields: 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_providersnames each Provider that is not connected.parable.warning.not_enabled_streamsnames each stream that is not enabled, such asslack.replies.
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:Query one Provider
Active Google Workspace users, with org unit and last login: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:
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 theContact.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:
Join Providers when emails differ
A GitHub organization member has alogin 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:
Join work items, not only people
Linear issues carrybranchname. GitHub pull requests carry the head branch
in the flattened head object. This join needs no email and no identity:
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:
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_parentjoins each child row to its parent, such asproviders.github.issues__labels_with_parent. It returns one row for each child row whose parent is stored.{table}_nestedputs each child table back into its parent row, as an array.
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, andBLOCKall 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
REPEATABLEorSEED, 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 thenrows with the lowest hash.BUCKET k OUT OF nkeeps the rows in thek-th ofnequal ranges. Thenbuckets 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 theproviders.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: theproviders_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.