Dayforce Non Primary Key Tables

The Ceridian Dayforce ("Dayforce") integration from DLH.io is the best data synchronization solution available on the market today for Dayforce customers. Customers often ask about a misconception regarding the dynamic integration with Dayforce to synchronize data from it to any downstream application being a mirror of the Dayforce Application itself. One such difference is the use of unique records and primary keys which signify uniqueness of a records in a dataset.

This is particularly interesting because most of the Dayforce data that can be synchronized is provided as transactional data. Meaning that the data feed for those entities/tables provided by Dayforce have no unique identifiers that enable direct reference to previous records without requiring some transformation or comparision process to align the data in order for it to be updated with any changes (or reference to deleted records in the Dayforce application itself). This means that records that are changed in the dayforce application appear in the dayforce feed as a point-in-time modification. In data architecture principles this flow of information is deemed as transactional when a system having any new, updated, or deleted information is provided as a unique row, as if pulling or synchronizing the data for the first time.

Why is this important?

This is important because it impacts how downstream reporting is assessed. Knowing that data for many tables/entities is transactional means that any attempt to manipulate that row of data in transit may cause data integrity issues. The best approach is to land that data transactionally as it synchronizes from dayforce to your target destination, such as Snowflake, Azure Synapse, Databricks, etc., and then perform some transformation or cleansing on that raw ("bronze") data layer.

This post processing transformation is important because it allows each organization's unique business requirements to be handled on the raw data, not from some other process' interpretation of the raw data. Another example of why this is important is that some organizations deem that tracking the changes of updates for analytics or historical trend analysis is important for reasons of data science, analytics, data warehousing, etc. If the raw data was not brought in transactionally to the target tables when it is expressed by dayforce to be as such, then customer's who desire this change or trend analysis would be unable to achieve their perspective or reporting goals.

Transactional Raw Data

DLH now ensures the integrity of transactional data landing into customer's targets systems without any cleansing or updates. This structural integrity was based on analysis of Dayforce data feeds and feedback from customers using the Ceridian Dayforce source connector. All customer DLH accounts now have access to create one or more Pre or Post-SQL Transformations to adjust any incoming or existing data based on their business needs. This includes things like marking metadata columns such as __DLH_IS_ACTIVE and __DLH_IS_DELETED to TRUE or FALSE depending on the business logic requirements. You can find examples of these transformations on the main Ceridian Dayforce page. The DLH.io support team can assist with this exercise for your implementation or it can be accomplished with some mid-level SQL skill by following the general advise in the docs. Other transformation recommendations are using DLH dbt or dbtCloud or Coalesce for more robust transformation and cleansing requirements as most data warehouse or lakehouse architectures should have any way.

Dayforce PK Tables

Below are the DLH data synchronization tables for Dayforce that have Primary Key concepts. During an April 2025 update and after this timeframe for any new Dayforce connectors, any other tables than these listed below will be without a structural or constraint based primary key.

For clarity, when any tables other than these listed or synchronized, if they contain pen-duplicate (where there are rows with similar data, and perhaps associated, but one or more of the columns is different) records no merging of that newly synchronized record will occur. An example of this would be in the case of the EMPLOYEE_WORK_ASSIGNMENT table. It does not (or no longer) has a primary key structure constraint and there for if on Day 1 a sync bridge pulls data resulting in the following:

And then on Day 2, that same employee has changed the previous work assignment, and the sync bridge retrieves this record,

Then the main target table in your destination schema will have two records. They will not be merged because the business rules for each customer cannot be determined by the Dayforce feed, no PK exists, and therfore this is a transactional table. The result would look as follows:

In this case you can read this use case documentation, for theEmployee Work Assignment solution, which discusses how to use a SQL Transformations to update the previous NULL effective_end date and/or __DLH_IS_ACTIVE fields with the latest effective_end date and false values respectively.

What to do if you find any issue?

If you encounter any problems based on the integrity of the transactional raw data synchronized, such as absolute duplicates (duplicates where there are rows with each column's data exactly matching, compared to a pen-duplicate status where there are rows with similar data but one or more of the columns is different), data not loading or synchronizing when compared to the source application, etc. please contact DLH Customer Support