Query Workday Data Lake

After you set up Data Lake, you can query data in the Data Cloud Catalog using these sample queries. For more information about the catalog and column types, see Data Cloud Catalog.

Running the Queries

These sample queries access Workday data. Execute them through the external client. All queries in this section use

workday.workday_core

as the catalog and namespace. Your environment and setup may differ. Always verify your catalog and schema before running data queries:

SHOW CATALOGS
SHOW SCHEMAS IN {Catalog/Integration Name}
SHOW TABLES IN workday.workday_core

Replace

workday.workday_core

in the sample queries with your actual catalog/schema, if different.

Query Patterns

Join core tables to reference tables

The most common pattern is joining a core table to a reference table on a foreign key, which turns a stored ID into a readable value. A single foreign key (

) joins on one column. Without a join, a column like

marital_status

returns the raw ID it stores. When you join it to the

marital_status

reference table, you get a real value such as Married
.

Joiner tables

Array foreign keys (

FK[]

) can't be expressed in every external system. Workday Data Lake provides joiner tables under the

join_workday_core

schema to account for this. Each one flattens an array relationship into a two-column bridge with one row per (source, target) pair, so you can rebuild the relationship without

UNNEST

Joiner tables follow a fixed naming pattern:

join_<source_table>_to_<target_table>_via_<source_fk_column>

The Data Cloud Catalog contains a list of all the joiner tables.

Work with array columns

When you query the array column

worker.languages

directly, you get a list of numeric IDs rather than readable values. SQL has two ways to make the array usable:

cardinality(arr)

returns how many elements the array holds.

UNNEST(arr)

expands the array into one row per element, so you can join each element to its reference table.

Aggregate and filter

Standard SQL filters and aggregates apply to catalog columns. Use catalog column names and data types—for example, filter active workers with

currently_active = true

, or compute tenure from

hire_date

using

date_diff

and

GROUP BY

Sample Queries

Join worker to marital_status

SELECT
    w.display_id AS worker_name,
    m.display_id AS worker_marital_status
FROM workday.workday_core.worker AS w
JOIN 
    workday.workday_core.marital_status m ON w.marital_status = m.id
LIMIT 10;

Count languages per worker

SELECT display_id AS worker, cardinality(languages) AS language_count
FROM workday.workday_core.worker
WHERE cardinality(languages) > 0
LIMIT 10;

Expand workers languages with UNNEST

SELECT
    w.display_id    AS worker,
    lang.display_id AS language
FROM workday.workday_core.worker AS w
CROSS JOIN UNNEST(w.languages) AS t (language_id)
JOIN workday.workday_core.language AS lang ON lang.id = t.language_id
LIMIT 10;

Resolve an array relationship with a joiner table

Where

UNNEST

isn't available, the joiner table gets you the same result as ordinary joins.

SELECT
    sp.display_id  AS succession_plan,
    spc.display_id AS candidate,
    spc.top_candidate
FROM workday.workday_core.succession_plan           AS sp
JOIN workday.join_workday_core.join_succession_plan_to_succession_plan_candidate_via_candidates AS j ON j.source_id = sp.id
JOIN workday.workday_core.succession_plan_candidate AS spc ON j.target_id = spc.id;

The joiner exposes the source and target primary keys as columns. Confirm exact column names against the catalog's Joiner Tables sheet.

Worker tenure by years of service

SELECT
    date_diff('year', hire_date, current_date) AS tenure_years,
    count(*) AS worker_count
FROM workday.workday_core.worker
WHERE currently_active = true
GROUP BY 1
ORDER BY tenure_years DESC;