In Your Workday HCM Warehouse, PERSON_NAME Holds People Who Don't Work for You

Query Workday HCM data with AI against a warehouse fed by the standard connector and the first question anyone asks, how many people work here, comes back too big. Not because a join is wrong. The headcount table holds every pre-hire record beside every worker record, the name table holds four kinds of name in the same table, and a column called type is the only thing that separates them.

A CFO asks how many people work here.

Point an AI agent at the landed schema and it finds the answer in seconds. PERSONAL_INFORMATION is one row per person. Count the rows.

The query compiles. The number is a little larger than the one HR reported last month, which is what a growing company expects to see.

It includes everyone Workday holds as a pre-hire: people in the hiring process who haven't started, and some who never will.

Before you start

• Workday HCM already landed in a warehouse. This post is written for the team that has it through Fivetran's managed Workday HCM connector. Read the scope line on that page before anything else: "Fivetran's Workday HCM connector only supports the following modules: Absence Management, Compensation, Core HCM, Payroll, Performance Management, Time Tracking." Recruiting, Learning, Talent, and Benefits don't come through it.
• There is no free instance. Workday is licensed to enterprises under multi-year agreements. No trial, no developer edition, no public sandbox, and Workday Community needs customer or partner status. Everything below runs against your own replicated schema, and a reader who isn't at a Workday customer can't follow along.
• This is HCM, not Financial Management, and not RaaS. Workday Financial Management is a different connector with a different dbt package. Workday RaaS syncs the output of custom reports rather than the object model, so a RaaS-fed warehouse holds whatever a report author built and nothing here applies to it.
• Casing varies by extract tool. Fivetran's connector page writes the tables upper case (PERSONAL_INFORMATION, PERSON_NAME) and its dbt package writes them lower. This post writes SQL lower and documentation upper. Adjust to whatever your destination did.
• Custom fields need work inside Workday first. They arrive only after an Integration System with Field Override Services is configured, and someone with the rights to do that has to do it. Nothing below depends on one.

The question

"How many people work here?"

It is the denominator of every ratio people analytics produces. Attrition is leavers over headcount. Revenue per employee is revenue over headcount. Span of control, cost per hire, and time to fill all sit on top of it, and the board sees at least two of them every quarter. HR has been reporting it correctly out of Workday for years, and the finance team already has last month's number to compare against.

What breaks

Here is the query, and it is the first thing anyone writes:

select count(*) as headcount
from   personal_information;

Nothing about it is careless. The table is called personal information, the connector documents it as the table for people, and there is exactly one row per person record.

It counts workers plus pre-hires. A pre-hire is a person record Workday creates before the hire is complete. Some of them start next month. Some never start. All of them are in the count.

So you add a name to the output, which is where it gets worse:

select p.personal_info_system_id,
       n.first_name,
       n.last_name
from   personal_information p
join   person_name n
  on   n.personal_info_system_id = p.personal_info_system_id;

One row per person per name record. A worker with a legal name and a preferred name is two rows. A worker who has also entered an emergency contact is three, and the third row is a different human being: a relative, with no employment relationship to the company at all.

Then someone asks for employees per city, joins the address table, and gets a number that mixes where people live with where the offices are.

Every one of these queries compiles. Every join key resolves. The numbers come back larger and plausible, and no row looks wrong.

The three tables and what each value of type actually selects, from Fivetran's connector documentation. The table name describes some of the rows. The column describes all of them.

Why it breaks

Fivetran states the mechanism directly, in three consecutive sentences under the "Schema notes" heading of the connector documentation:

"We sync personal information from the Pre-hire and Workers endpoints into the PERSONAL_INFORMATION table. For Workers data, we set the value of the type column to worker, and for the Pre-hire data, we set the value of this column to pre_hire."

"We sync all types of person-name data into the PERSON_NAME table and set the value of the type column to LEGAL, PREFERRED, ADDITIONAL, or RELATIVE, depending on the type of name data stored."

"We sync all types of address data into ADDRESS table and set the value of the type column to PERSON_CONTACT_ADDRESS and BUSINESS_SITE_ADDRESS."

Read those as a warehouse and three things follow.

PERSONAL_INFORMATION is two populations in one table. Workday exposes workers and pre-hires through separate endpoints. The connector reads both and lands them together, with the endpoint recorded in a column. So the population choice, which Workday made structural, becomes a value.

PERSON_NAME is not one row per person. Its grain, from the dbt package's source definition, is (personal_info_system_id, type, index), where Fivetran documents index as "An index for a particular identifier." A person has as many rows as they have kinds of name, and more than one row per kind is representable. There is no single-column primary key. And RELATIVE is not a kind of the worker's name at all; it is the name of a related person, entered for emergency contact or dependent purposes.

ADDRESS holds people and buildings. A person contact address is where a worker lives, and it is sensitive personal data. A business site address is where an office is. An unfiltered join answers two different questions at once and labels the result with one of them.

Three tables, one column name

The column is called type in all three tables, the values are different in each, and the table name describes only some of the rows. PERSON_NAME is the sharpest case: Fivetran's own column description in the dbt package reads "The type or category of the person name (e.g., legal name, preferred name)." Read that and you'd never guess a fourth value puts someone else's name in the table.

An AI agent reading table names reasonably concludes that PERSON_NAME holds the person's name, that PERSONAL_INFORMATION holds employees, and that ADDRESS holds where they live. Each conclusion is true of most of the rows. None is true of all of them.

This is worth setting against the rest of the series, because the shape is unusual. When one HubSpot deal belongs to three companies, the trap is a genuine many-to-many fan through a junction table. When ServiceNow's incident and task are the same record, two tables are secretly one. Workday's is the reverse: one table is secretly several record sets, flattened on the way into the warehouse and never labelled as a union. The joins here are all correct and all ordinary. What is wrong is the row set on the other end of each of them.

What Workday did for you

Inside Workday you were never given the choice, because you were never handed a table.

A Workday report is built against a data source, and the data source is the thing that decides which population you get. Workday's own reporting course walks through them: Workers, Employees, Contingent Workers, Trended Workers, All Positions. Picking the population is the first step of building any report, and Workday makes you do it before you can pick a single field. The pre-hire endpoint and the worker endpoint are different things because Workday treats them as different things.

Replication inverts that. The connector reads several endpoints, lands them in one table each, and puts the population choice in a column. The decision Workday forced you to make first becomes a where clause you have to know to write, in a table whose name suggests the choice was already made.

An independent Workday modelling reference reaches the same conclusion from the other side: model the worker once and carry the type as an attribute, so a conversion reads as one person with a changed type rather than as two people. That is advice about how to rebuild the boundary Workday used to enforce.

This is the same shape as Salesforce's "Opportunities with Products" report type holding the line-item grain, and the same shape as Banner's registrar screen resolving the effective term: the thing that made the application's number correct was a boundary, not a column, and replication had nothing to copy.

The fix

None of this is a query problem. Adding where type = 'worker' fixes one query for one person on one afternoon, and the next person to ask about headcount starts from the same schema and reaches the same wrong number by the same correct route.

It belongs in the semantic model, where each row set is declared once and every question inherits it.

The joins go in, and the notable thing about them is how little there is to say. Every one is a straightforward many-to-one and the connector documents the key:

# subject_areas/people/person_name.yaml
relationships:
  - name: person_name_to_personal_information
    from: { table: person_name, columns: [personal_info_system_id] }
    to:   { table: personal_information, columns: [personal_info_system_id] }
    cardinality: many_to_one
    citation: >
      fivetran/dbt_workday src_workday.yml declares personal_info_system_id on
      person_name, described as "The system ID associated with the personal
      information of the individual." The join is correct and it is many-to-one
      by grain, not by a type filter: person_name holds one row per
      (personal_info_system_id, type, index). Confirm the id column name on
      personal_information against your own schema; the dbt package does not
      declare that table and the connector's ERD is published as an image.

Then the part that carries the weight. The schema can't say which rows a question means, so the model has to:

entities:
  - name: workday_worker_population
    description: >
      Which person records count as employed. Fivetran syncs the Pre-hire and
      Workers endpoints into ONE table, personal_information, and distinguishes
      them only by a column called type, with values worker and pre_hire. A
      pre-hire has not started and may never start. Any headcount, any
      per-employee ratio, and any denominator anywhere must filter this column,
      and nothing in the schema forces it.
    resolves_to:
      table: personal_information
      selector: "personal_information.type = 'worker'"
    caveats:
      - >
        Inside Workday you cannot avoid this decision: a report is built on a
        data source that fixes the population before you choose a field.
        Replication turns that forced choice into an optional where clause.
      - >
        Whether contingent workers count as headcount is company policy, not a
        schema fact, and it is a different question from the pre-hire filter.
        Decide it once, here, and write down which way.
    citation: https://fivetran.com/docs/connectors/applications/workday-hcm

  - name: workday_person_name
    description: >
      A worker's name. person_name is NOT one row per person. Fivetran lands
      every kind of name in it and sets type to LEGAL, PREFERRED, ADDITIONAL, or
      RELATIVE. Grain is (personal_info_system_id, type, index). Joining it
      without a type predicate multiplies every worker by their name records,
      and RELATIVE rows are a different human being entirely: an emergency
      contact or a dependent, not an employee.
    resolves_to:
      table: person_name
      selector: "person_name.type = 'LEGAL'"
    caveats:
      - >
        LEGAL versus PREFERRED is a policy choice with a human cost and it is
        not the same answer everywhere. Payroll, tax, and right-to-work outputs
        need LEGAL. Anything a colleague reads, such as a directory, an org
        chart, or a dashboard label, should use PREFERRED where one exists and
        fall back to LEGAL. Defaulting the whole model to LEGAL deadnames
        people. A person decides this, and the decision is recorded here.
      - >
        RELATIVE rows must never reach an employee-facing surface by accident.
        They are third-party personal data about someone with no employment
        relationship to the company.
    citation: https://fivetran.com/docs/connectors/applications/workday-hcm

  - name: workday_address_kind
    description: >
      address holds both people and buildings. Fivetran sets type to
      PERSON_CONTACT_ADDRESS or BUSINESS_SITE_ADDRESS. An unfiltered join mixes
      where a worker lives with where their office is, so "employees per city"
      silently answers two different questions at once.
    resolves_to:
      table: address
      selector_person: "address.type = 'PERSON_CONTACT_ADDRESS'"
      selector_site: "address.type = 'BUSINESS_SITE_ADDRESS'"
    caveats:
      - >
        Home addresses are sensitive personal data and are usually the wrong
        answer to a business question. Most location questions mean the work
        location. Default to the site and make the person address opt-in.
    citation: https://fivetran.com/docs/connectors/applications/workday-hcm

And the metric binds to the entity rather than to the table:

metrics:
  - name: headcount
    calculation: >
      Distinct people whose person record is a worker rather than a pre-hire.
      Every per-employee ratio in the model uses this as its denominator.
    requires_entity: workday_worker_population
    source_tables: [personal_information]
    primary_table: personal_information
    other_names: [headcount, employees, staff, how many people work here]
    citation: >
      Fivetran, Workday HCM connector, Schema notes. The entity supplies the row
      selection, which is the part the schema cannot express.

requires_entity is the load-bearing line. It says that reaching personal_information for a headcount without the population selector isn't a stylistic choice; it is a number the model has already declared invalid.

Four decisions in here aren't inferable from the schema, and a person makes them once.

Legal name or preferred name. This is the highest-stakes decision in the model and the only one that can hurt somebody. Payroll and statutory reporting need the legal name. Anything a colleague reads should show the preferred name where one exists. A model that defaults to LEGAL everywhere will deadname people in every dashboard that reads it, and no introspection will ever tell you that. Decide it per surface, write it down in the entity, and treat a request to change it as a policy change rather than a schema edit.

Whether contingent workers are headcount. Every company has already written this policy down somewhere, and never in the schema.

Which modules the connector is syncing. Scope is chosen per connection, and a question about a module that isn't synced has no honest answer from this warehouse. A person reads the connector configuration once.

Whether address may be queried at all. Employees' home addresses carry an access decision, and it belongs to a person rather than to a model. The default in the entity above is the site address; the person address is opt-in.

Reproduce it yourself

This runs against your own warehouse, which is the only route available for Workday.

1. Run the shape query. It says how exposed you are before you change anything.
2. Run the wrong query from the top of this post. Keep the output.
3. Run the gap query. It puts a number on what step 2 has been quietly adding.
4. Declare the relationship, the entities, and the metric, then ask the question in plain English in the assistant your team already opens, and compare against the number HR reports.

The shape query. Run this first. It uses only the type column, which the connector documents by name on all three tables:

select 'personal_information' as tbl, type, count(*) as rows
from   personal_information
group  by 1, 2
union all
select 'person_name', type, count(*)
from   person_name
group  by 1, 2
union all
select 'address', type, count(*)
from   address
group  by 1, 2
order  by 1, 3 desc;

Every row beyond the first in each block is rows an unfiltered query has been counting. The pre_hire line is your headcount error. The RELATIVE line is the number of people in your name table who don't work for you.

The gap query. This is the one worth keeping:

select count(*)                                          as naive_headcount,
       count(*) filter (where type = 'worker')           as actual_headcount,
       count(*) - count(*) filter (where type = 'worker') as phantom_headcount
from   personal_information;

phantom_headcount is your own number, computed on your own estate. Every one of them is a person being counted as an employee who hasn't started. At a company that hires steadily it is small and never zero. At one that is mid-expansion it is most of the gap between the warehouse and what HR reported.

If your destination doesn't support filter, replace it with sum(case when type = 'worker' then 1 else 0 end). To count distinct people rather than rows, use the person-id column on personal_information; the connector's ERD shows it for your estate and the dbt package doesn't declare this table, so confirm the name against your own schema rather than pasting one.

The corrected queries. The predicate is the entire fix:

select count(*) as headcount
from   personal_information
where  type = 'worker';

select p.personal_info_system_id,
       n.first_name,
       n.last_name
from   personal_information p
join   person_name n
  on   n.personal_info_system_id = p.personal_info_system_id
 and   n.type = 'LEGAL'
where  p.type = 'worker';

Read n.type = 'LEGAL' as a placeholder for the decision above, not as the answer. For a directory it should be PREFERRED with a fallback, and the entity is where that gets written once.

What this looks like in Agami

Everything above holds whoever builds the model. Here is what it is in our product, in the terms this post has used.

The joins are read, not guessed, and so is the grain. Introspection reads the tables, the columns, the keys, and how tables join from the warehouse itself, one-to-many included, into readable YAML in your repo. On this schema that finds every join in this post correctly, which is exactly the point: the joins were never the problem, and knowing the grain of person_name is what tells you a bare join will multiply.

The type predicate is drafted as an entity, and a person approves it. Descriptions, entities, and metric definitions are drafted from the schema, and then your team approves them, reversibly. The population selector, the name kind, and the address kind above are that step. Legal versus preferred is recorded in the entity where every question inherits it, rather than re-decided in each prompt.

Headcount ships once it matches the number HR already reports. Reconciliation takes a screenshot of the dashboard, a CSV export, or numbers pasted into chat, and compares at a one percent tolerance by default. A mismatch opens the SQL so you can see why. On this schema the mismatch is the pre-hire rows, and finding it is the useful output rather than the failure.

Every answer returns with its SQL beside it. Whether type = 'worker' was applied is visible on the answer, not taken on faith, and a reviewer who knows the schema can see a RELATIVE row being counted before anyone else does.

A validated question becomes a golden test. Once headcount agrees with HR for a given month, that question and its answer are saved, and a change to the model that breaks it is not promoted.

And the number we don't have. We haven't run this on a Workday estate, so there is no count of pre-hire rows, no name rows per worker, no size of the error, and no accuracy figure of ours in this post. The shape query and the gap query return yours. And the rule that keeps RELATIVE rows and home addresses off an employee-facing surface is only as good as the access decision a person makes; the model records that decision, it doesn't make it.

Frequently asked questions

Why is my Workday headcount from the warehouse higher than the number HR reports?

Almost certainly because the query counts PERSONAL_INFORMATION without filtering type. Fivetran syncs the Pre-hire and Workers endpoints into that one table and sets type to worker or pre_hire. A pre-hire is a person record created before the hire is complete, so an unfiltered count includes people who aren't employees yet and some who never will be. Filter to type = 'worker', then decide separately whether contingent workers belong in your headcount.

What does the type column in PERSONAL_INFORMATION mean?

Which Workday endpoint the row came from. Fivetran documents two values: worker for rows from the Workers endpoint and pre_hire for rows from the Pre-hire endpoint. Inside Workday these are different populations chosen by the report's data source; in the warehouse they are one table and the choice is a column value.

Why does joining PERSON_NAME multiply my rows?

Because PERSON_NAME is not one row per person. Its grain is (personal_info_system_id, type, index), and type can be LEGAL, PREFERRED, ADDITIONAL, or RELATIVE. A worker with a legal and a preferred name is two rows. A RELATIVE row is a different person entirely, such as an emergency contact or a dependent, so a join without a type predicate both multiplies workers and pulls in people who don't work for the company.

Should the semantic model use the legal name or the preferred name?

Both, on different surfaces, and a person decides which surface gets which. Payroll, tax, and right-to-work outputs need LEGAL. Anything a colleague reads, such as a directory, an org chart, or a dashboard label, should use PREFERRED where one exists and fall back to LEGAL. Defaulting the whole model to LEGAL deadnames people. Record the decision in the entity so every question inherits it.

Does this apply to Workday RaaS or Workday Financial Management?

No. Workday RaaS syncs the output of custom Workday reports, so a RaaS-fed warehouse holds whatever the report author built and its population was chosen inside Workday. Workday Financial Management is a separate connector with a separate dbt package. This post is about Fivetran's Workday HCM connector, which covers six modules: Absence Management, Compensation, Core HCM, Payroll, Performance Management, and Time Tracking.

References

1. Workday HCM connector, Fivetran. The three Schema-notes sentences quoted above, the six supported modules, history mode for WORKER_HISTORY, and the note that deletes are captured differently for parent and child tables. Readable in full without a login.
2. fivetran/dbt_workday, src_workday.yml. The 23 source tables the package declares and the column list for person_name, including index, personal_info_system_id, type, first_name, and last_name.
3. fivetran/dbt_workday, docs.md. Fivetran's own column descriptions: index as "An index for a particular identifier" and person_name_type as "The type or category of the person name (e.g., legal name, preferred name)."
4. Advanced Reporting for HCM: Worker Trending, Workday. That a Workday report starts from a data source that fixes the population before a field is chosen.
5. The Workday Worker object, Irvine Analytics. Independent modelling guidance: model the worker once and carry the type as an attribute.
6. agami-core on GitHub

Make your Workday data answerable

Agami is the semantic layer between your AI assistant and your warehouse. It declares which rows each table is actually about, so an agent returns a governed headcount or says why it cannot.

Start a free trial or talk to us →