Workday Human Capital Management (HCM)
Workday HCM is a cloud-based human capital management platform that brings human resources (HR), recruiting, staffing, benefits administration, absence management, time tracking, compensation management and related functions together in one system.
Fivetran's Workday HCM connector only supports the following modules:
- Absence Management
- Compensation
- Core HCM
- Payroll
- Performance Management
- Time Tracking
Features
| Feature Name | Supported | Notes |
|---|---|---|
| Capture deletes | ||
| History mode | WORKER_HISTORY table and its child tables | |
| Custom data | ||
| Data blocking | ||
| Column hashing | ||
| Re-sync | ||
| Row filtering | ||
| API configurable | API configuration | |
| Priority-first sync | ||
| Fivetran data models | ||
| Private networking | ||
| Authorization via API |
When you re-sync a child table, it automatically triggers a re-sync for the parent table and its associated child tables.
When you select a child table that was previously deselected, you need to re-sync its parent table to backfill the data.
Supported deployment models
We support the SaaS and Hybrid deployment models for the connector.
You must have an Enterprise or Business Critical plan to use the Hybrid Deployment model.
Setup guide
Follow our step-by-step Workday HCM setup guide to connect Workday HCM with your destination using Fivetran.
Sync overview
Initial sync
When you first connect a new account, Fivetran scans every table you selected and copies every row that we have permission to view. We import each table in full before moving onto the next table. Depending on the number and size of tables to be imported, the initial sync can take a while.
Updating data
Fivetran uses one of the following strategies to update the tables that you included in your sync:
Incremental
We update tables incrementally if they have a bsvc:Updated_From, bsvc:Updated_Through, bsvc:Effective_From , or bsvc:Effective_Through column. We query the data with the following parameters:
<bsvc:Transaction_Log_Criteria_Data>
<bsvc:Transaction_Date_Range_Data>
<bsvc:Updated_From>1970-01-01T16:33:18.795Z</bsvc:Updated_From>
<bsvc:Updated_Through>2023-08-10T13:15:14.366Z</bsvc:Updated_Through>
<bsvc:Effective_From>2023-08-08T13:15:14.366Z</bsvc:Effective_From>
<bsvc:Effective_Through>2023-08-10T13:15:14.366Z</bsvc:Effective_Through>
</bsvc:Transaction_Date_Range_Data>
</bsvc:Transaction_Log_Criteria_Data>
<bsvc:Transaction_Log_Criteria_Data>
<bsvc:Transaction_Date_Range_Data>
<bsvc:Updated_From>2023-08-08T13:15:14.366Z</bsvc:Updated_From>
<bsvc:Updated_Through>2023-08-10T13:15:14.366Z</bsvc:Updated_Through>
<bsvc:Effective_From>1970-01-01T16:33:18.795Z</bsvc:Effective_From>
<bsvc:Effective_Through>2023-08-10T13:15:14.366Z</bsvc:Effective_Through>
</bsvc:Transaction_Date_Range_Data>
</bsvc:Transaction_Log_Criteria_Data>
The first instance of the transaction log records any newly effective changes up to the Effective_Through date, regardless of when the change was made. This instance includes changes that were made prior to the historical sync that have since become effective.
The second instance captures any new changes that were entered since the last sync that are effective up to the Effective_Through date. This instance includes the backdated transactions that are effective prior to the historical sync.
The JOB_REQUISITION table is synced incrementally and may fail to capture certain updates. When a child table of the JOB_REQUISITION table is modified, the Transaction_Log_Criteria_Data filter may not capture updates made only to child tables if the parent record is not updated. As a result, these changes may be missed during incremental syncs. Workday API does not provide an alternative incremental filter to capture such updates. When a subsequent change updates the parent table, we retrieve any previously missed updates. To ensure that we capture all updates, perform a re-sync of the JOB_REQUISITION table.
Re-import
We re-import tables in full if they do not have a bsvc:Updated_From, bsvc:Updated_Through, bsvc:Effective_From , or bsvc:Effective_Through column. We re-import these tables once a day to avoid slowing down our syncs.
For CALCULATED_TIME_BLOCK, we re-import the tables based on the following criteria:
| Table Import Duration | Default Import Frequency |
|---|---|
| 10 minutes or less | Every sync |
| Between 10 minutes and 60 minutes | Twice a day |
| More than 60 minutes | Once a week |
We incrementally sync the following tables and their child tables:
CANDIDATEJOB_PROFILEJOB_REQUISITIONPAYROLLPOSITIONWORKERWORKER_EVENTWORKER_HISTORY
We re-import all other tables daily.
All daily re-import tables, except CALCULATED_TIME_BLOCK, may encounter errors when processing more than 1 million records due to a Workday API limitation.
If you encounter this error, contact Workday Support for assistance with large data requests involving tables that do not support time-range criteria.
Deleted rows
We track deleted rows differently for parent tables and child tables:
- For parent tables, we capture soft deletes during re-syncs only. We do not capture deletes for these tables during normal syncs.
- For child tables, we capture hard deletes during both syncs and re-syncs.
History Mode
We support history mode for the WORKER_HISTORY table and its child tables. We populate the _fivetran_start column based on the effective date of each change.
We use the effective dates in each worker's transaction log and sync the transactions to the TRANSACTION_LOG_ENTRY table. For each effective date, we add a row to every history table, even if the values in that table didn't change. We store the effective date in the as_of_effective_date column. During incremental syncs, we fetch only the changed dates. For each record, the rows do not overlap, and exactly one row has _fivetran_active value set to TRUE.
Connections that haven't moved to transaction log-based history mode yet use the effective dates from worker events instead. For these connections, a few changes to entities related to workers (for example, Job Profile) may impact a worker's compensation values without generating a new worker event. To capture such changes, we also capture the most recent version of the worker (as of the sync date) every time we sync the worker data.
The transaction types in the transaction log determine how many effective dates, and therefore how many rows, we sync. If you don't configure an Integration Transaction Log Service, the transaction log includes all transaction types, such as Time Off Entry, which significantly increases your data volume. To keep your history tables focused on real changes, configure an Integration Transaction Log Service that subscribes only to the transaction types that change worker records. Learn more in our setup guide's recommended transaction types.
Query history tables with repeated rows
History tables may contain consecutive rows of a record with identical values. Depending on the type of your query, determines whether you need to remove these repeated rows:
| Query type | Remove repeated rows? | How |
|---|---|---|
| Current state, for example a worker's current position | No | Filter on _fivetran_active = TRUE. This returns exactly one row per record. |
| Point in time, for example a worker's position on a given date | No | Filter on <as_of_timestamp> BETWEEN _fivetran_start AND _fivetran_end. This returns exactly one row per record. |
| Change history, for example a slowly changing dimension (SCD) Type 2 model with one row per change | Yes | Use the collapse query. |
The following example retrieves a worker's primary position on a given date without removing repeated rows:
SELECT *
FROM <schema>.worker_position_history
WHERE worker_id = '<worker_id>'
AND <as_of_timestamp> BETWEEN _fivetran_start AND _fivetran_end
AND is_primary_job = TRUE;
Removing repeated rows in your destination is a query-time transformation. It doesn't change the data we sync, therefore, it doesn't reduce your synced rows or your Monthly Active Rows (MAR).
Collapse repeated rows
Use the following query to combine consecutive rows with identical values into a single row for each period during which a record's values remain unchanged. The collapse query returns one row for every real change for each record. It identifies each record is identified using the record key for history tables:
| Table | Record key |
|---|---|
WORKER_HISTORY | id |
PERSONAL_INFORMATION_HISTORY | id, type |
WORKER_POSITION_HISTORY | worker_id, position_id |
WORKER_POSITION_COMPENSATION_HISTORY | worker_id, position_id, id |
WORKER_POSITION_JOB_CLASSIFICATION_HISTORY | worker_id, position_id, job_classification_id |
WORKER_POSITION_ORGANIZATION_HISTORY | worker_id, position_id, index |
WORKER_POSITION_PAYROLL_REPORTING_CODE_HISTORY | worker_id, position_id, payroll_reporting_id |
WORKER_PAY_GROUP_ASSIGNMENT_HISTORY | worker_id, index |
WORKER_DOCUMENT_FIELD_HISTORY | worker_id, integration_document_field |
The query works as follows:
- It encodes all business columns of each row into one hash. Business columns include all columns except the record keys,
_fivetran_start,_fivetran_end,_fivetran_active,_fivetran_synced, andas_of_effective_date. The query encodes each value asNforNULL, or as the value's length followed by the value. As a result, a change betweenNULLand an empty string counts as a change. A value that contains the|separator can't hide a change either. - It starts a new version when a row's hash differs from the previous row of the same record or when there is a gap between the two rows. A gap means when a record ends and reappears later, for example a position that was reassigned months later.
- It merges each run of identical rows into one row. The merged row uses the first
_fivetran_startasvalid_fromand the last_fivetran_endasvalid_to, and it's marked as current if the run contains the active row.
Configure the SQL query
- Run the following query:
WITH hashed AS (
SELECT
h.*,
-- Encode every business column as 'N' for NULL or '<length>:<value>', then hash the result
MD5(
CASE WHEN <business_column_1> IS NULL THEN 'N'
ELSE CAST(LENGTH(CAST(<business_column_1> AS VARCHAR)) AS VARCHAR) || ':'
|| CAST(<business_column_1> AS VARCHAR) END
|| '|' ||
CASE WHEN <business_column_2> IS NULL THEN 'N'
ELSE CAST(LENGTH(CAST(<business_column_2> AS VARCHAR)) AS VARCHAR) || ':'
|| CAST(<business_column_2> AS VARCHAR) END
-- || '|' || <repeat the CASE expression for every business column>
) AS row_hash
FROM <schema>.<history_table> h
),
flagged AS (
SELECT
hashed.*,
CASE
-- Same values as the previous row of the record and no time gap: same version
WHEN row_hash = LAG(row_hash) OVER (PARTITION BY <record_key> ORDER BY _fivetran_start)
AND _fivetran_start = LAG(_fivetran_end) OVER (PARTITION BY <record_key> ORDER BY _fivetran_start)
+ INTERVAL '1' MILLISECOND
THEN 0
ELSE 1
END AS is_new_version
FROM hashed
),
versioned AS (
SELECT
flagged.*,
SUM(is_new_version) OVER (PARTITION BY <record_key> ORDER BY _fivetran_start
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS version_number
FROM flagged
),
collapsed AS (
SELECT
versioned.*,
MAX(_fivetran_end) OVER (PARTITION BY <record_key>, version_number) AS version_end,
MAX(CASE WHEN _fivetran_active THEN 1 ELSE 0 END)
OVER (PARTITION BY <record_key>, version_number) AS version_active
FROM versioned
)
SELECT
<record_key>,
<business_column_1>,
<business_column_2>,
-- <every other business column>
_fivetran_start AS valid_from,
version_end AS valid_to,
CASE WHEN version_active = 1 THEN TRUE ELSE FALSE END AS is_current
FROM collapsed
WHERE is_new_version = 1;
Replace the following placeholders before running the query:
<schema>and<history_table>with your destination schema and the history table.<record_key>with the table's record key columns, separated by commas.<business_column_1>and<business_column_2>with the table's business columns. Repeat theCASEexpression and the column in the finalSELECTfor every other business column.
Adapt the query to your destination
Adjust the SQL syntax based on your destination:
- Text data types: Use the text type supported by your destination in
CAST. For example,STRINGin BigQuery and Databricks. - Timestamp calculations: Replace the
+ INTERVAL '1' MILLISECONDwith the corresponding function in your destination. For example,TIMESTAMP_ADD(..., INTERVAL 1 MILLISECOND)in BigQuery orDATEADD(millisecond, 1, ...)in Snowflake and Redshift. - JSON columns: Convert JSON columns to text with your destination's JSON-to-string function. For example
TO_JSON_STRINGin BigQuery. ThePERSONAL_INFORMATION_HISTORYtable contains the JSON columnsadditional_nationalityandcitizenship_status. - Boolean columns: If your destination doesn't support casting BOOLEAN values to text, such as Redshift, use
CASE WHEN <boolean_column> THEN 'true' ELSE 'false' ENDinstead ofCAST.
Guidelines for collapsing repeated rows
Follow these guidelines when you use the query:
- Partition records using the full record key for the table, not by
worker_idalone. - Do not remove rows based on the record key and
_fivetran_start. This combination is already unique, and the repeated rows have different dates. - Use
_fivetran_endforvalid_to. Do not calculatevalid_tofrom the next row's_fivetran_start, because records may have gaps. - Exclude columns that you added yourself, such as
load_timestamps, from the hash. - Rebuild the collapsed table from the source table on every run, or rebuild it from the earliest
_fivetran_startvalue that changed. Backdated corrections and deletions update existing rows, so appending only new rows may miss these changes.
If you use dbt, replace the body of your change history model with the collapse query. Do not change the model name and output columns, so your downstream models remain the same and continue to work. Models that read only current rows or a point-in-time do not require any changes.
Schema information
This schema applies to all Workday Human Capital Management connections.
See the Workday HCM ERD in table format | Workday HCM ERD as Markdown
Schema notes
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. You can use the type column to query the Workers and Pre-hire data from the PERSONAL_INFORMATION table.
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.
Configure custom and calculated fields
To configure custom and calculated fields, do the following:
Configure integration system
- Log in to your Workday application.
- In the application's search box, type Create Integration System and select it from the results..
- Enter a name for the Integration System.
- In the Template section, select New Using Template. We recommend that you use the Cloud Integration template.
- Click OK.
- Click ... next to the Integration System name.
- Under Actions, select Integration System > Configure Integration Services.
- Select the Enable All checkbox.
- Go to Custom Integration Services and click +.
- For the Integration service field, select Create > Create Integration Field Override Service.
- In the Create Integration Field Override service page, add your service name and select WORKER (or any Business Object you want to configure the custom or calculated fields on) as the Business Object.
- For the Fields section, add all the custom field names you want to add and click OK.
These fields are later overridden to have the calculated or custom data.
- Click OK to finalize configuring your Integration Service.
- Make a note of the Integration System ID for this Integration System. You will need it to configure Fivetran.
Map custom fields and use field overrides
- Click ... next to the Integration System name. If you are not on the integration system page, in the search box, search for the integration system name you created above, and you can select your integration system (should appear in More categories > Integrations).
- Under Actions, select Integration System > Configure Integration Field Overrides.
- Select the Integration Field Override Service you configured in the previous section.
- Search for the field you want to be synced under the Override External Field section.
- Click OK
Every field is related to a Business Object that specifies what endpoint will have this custom data field. Once you select the field values, these fields will start appearing in the SOAP responses for the specific endpoints under the *DOCUMENT_FIELD tables. For more information, see our Workday HCM ERD.
We can't sync Time_In_Position or Time_In_Job_Profile as standard fields. Workday calculates these values at runtime and doesn't expose them as standard elements in SOAP API Get_Workers responses or through the core REST API /workers endpoints.