UKG's Combined Pay Codes Land in Your Warehouse Beside the Hours They Sum

UKG defines a combined pay code as the total of several pay codes, and its own example is Grand Overtime, the sum of overtime and double time. Inside UKG nobody adds that total to its own parts. The timecard hides it unless an administrator ticks a box, the standard report puts it in a section of its own, and UKG's own warehouse refuses to map it at all. Three separate rules, and not one of them is a column. The extract copied the rows.

A workforce analyst wants overtime hours by employee for last month.

Query your UKG data with AI against a replicated warehouse and the shape looks friendly. employee_time_card_actual_total carries an employee_id, a pay_code_name, an apply_date, an hours_amount and a wages. Filter the month, filter the pay code name for overtime, group by employee, sum the hours.

The figure comes back too high. Nothing errors, nothing is null, and no employee is missing. The overtime an agent just reported includes Grand Overtime, which is the overtime, added to itself.

Before you start

• UKG Pro Workforce Management replicated through the Fivetran connector. It's a Lite connector, which Fivetran defines as one whose schema design "Starts with the available API." The landed tables are shaped like UKG API responses, so UKG's API reference is the authority for what a column means.
• Only active employees reach the timecard tables. Fivetran states the limitation plainly: "We sync data only for active employees into the following tables and their child tables: BALANCE EMPLOYEE_TIME_CARD EMPLOYEE_TIME_OFF_REQUEST MANAGER PERFECT_ATTENDANCE_TRANSACTION TIME_CARD_AS_MANAGER TIME_OFF_REQUEST_MANAGER." A leaver's hours aren't there.
• Deletes are captured on five tables, and none of them is a timecard table. Those five are DEVICE, DEVICE_GROUP, DEVICE_STATUS, EMPLOYEE and SCHEDULE_PATTERN. The timecard tables sync incrementally instead.
• No free instance to practise on. UKG grants developer access inside a customer tenant, so this runs against an extract your employer already has. The API reference is readable without a login, which means you can study every schema below before you have access to anything.
• Casing varies by route. The API is camelCase (hoursAmount), Fivetran lands snake_case (hours_amount), and Snowflake destinations upper-case it. The SQL below uses Fivetran's lower-case names.

The question

"How many overtime hours did each employee log last month?"

Every employer of hourly staff asks it, and the answers that hang off it are expensive ones. Overtime budget, agency cover, the next shift pattern, and in a hospital or a plant the staffing ratio a regulator reads.

It comes out of the warehouse rather than out of UKG because the question rarely stops at hours. It wants the hours beside the roster, the ledger, and last year.

What breaks

Here's the query almost anyone writes first.

SELECT employee_id,
       SUM(hours_amount) AS overtime_hours
FROM   employee_time_card_actual_total
WHERE  pay_code_name ILIKE '%overtime%'
  AND  apply_date >= DATE '2026-08-01'
  AND  apply_date <  DATE '2026-09-01'
GROUP  BY employee_id;

Nothing about it is careless. pay_code_name really does hold the pay code's name. hours_amount really does hold hours. The date filter reads like a month, and it is one.

The WHERE clause is the problem, and so is the SUM. Both of them assume that every row in this table is a different quantity of work.

They aren't. One of those rows can be the sum of two of the others, sitting beside them under a name of its own.

Widen the question to "how many hours did each employee log" and the trap gets worse, because dropping the pay code filter pulls in every combined pay code the tenant defined.

The result is plausible at a glance, which is the worst thing about it. No employee's figure is absurd. Overtime is simply high, in a period where everyone expected it to be high.

Why it breaks

Three facts are true at once, and the first query reads none of them.

A combined pay code is a total that UKG stores as a pay code

UKG says what one is, on its own pay code setup page, in a sentence:

Combined pay codes are totals of several pay codes. For example, a combined pay code named Grand Overtime is the sum of all overtime pay codes, such as overtime and double time.

The same page: "Combined pay codes can include money and time pay codes, but the combined pay code must have a type of time or money." The list of codes an administrator can put inside one is "Currently defined pay code definitions and combined pay codes," so a combined code can hold another combined code.

Pro WFM's own API carries the object under the same name. From the Timekeeping Setup schema, TimekeepingSetupPayCode.type is documented as "The pay code type. Valid values are: Regular, Combined, Duration, and Cascade," beside a combined boolean, "A Boolean indicator of whether or not the pay code is a combined pay code."

So a combined pay code isn't a report and it isn't a view. It's a pay code, with a name, an effective date and a list of member pay codes, and totals get written against it exactly the way they get written against Overtime.

The API hands those totals to every extract, by default

This is the line that decides whether a reader has the problem. From the timecard reference:

totals_include_combined_paycodes · boolean · Defaults to true

The schema behind the response says the same thing from the other side. TotalsData.combined is "A Boolean indicator of whether or not the total is combined," and TimeCardDataWhere.payCodeType is "The type of payCode. This property is only applicable for the Timecard Metrics operation. Valid values include COMBINED or REGULAR."

A caller who wants regular totals only has to ask. A caller who asks for nothing gets both.

Fivetran doesn't publish whether its requests set either option, so whether your connection holds combined rows is a question your own extract answers. The discovery query below is how you ask it.

The flag is on one of the two timecard shapes and not the other

UKG's connector lands timecard data twice, from two different API responses, and only one of them marks a combined total.

TotalsData is the shape behind employee_time_card_actual_total, and it carries 35 properties. One of them is combined.

Timecard_Aggregated_Total is the shape behind time_card_as_manager_aggregated_total, and it carries 34 properties. None of them is combined. It has payCode, amount, amountType, wages and rate, everything you'd need to build an hours figure, and nothing that says whether a row is a total of the rows around it.

The employee-side totals carry the flag and the manager-side totals do not. Illustrative identifiers, pay code names and hours; the table names, column names and the flag's presence and absence are UKG's and Fivetran's.

And no pay code table lands at all. Pay codes reach the warehouse only as a pay_code_id and a pay_code_name on the rows that used them, so two things stay behind in UKG: which codes are combined, and which regular codes each one adds up. UKG's setup API calls that second list selectedPayCodes, "The collection of selected paycodes of the GDAP-filtered combined paycode for the logged-in user." Nothing in a timecard extract carries it.

That's what makes pay_code_name ILIKE '%overtime%' such a reliable way to be wrong. The filter matches Overtime and Grand Overtime because both are overtime, and adding them counts the same hours twice.

One more column decides whether a row is even in hours. amount_type says which of hours_amount, days_amount or wages carries the number. Its literal values are documented as HOUR, DAY and MONEY on the manager-side schema. On the employee-side schema the same property is described only as "The type of the amount," with no list, so read your own values before you filter on one.

What the application did for you

Inside UKG, nobody meets any of this, because UKG holds the line in three separate places.

On the timecard. A combined pay code shows in the Totals tab only when an administrator ticks "Visible on Totals Tab in Timecard," and in reports only with "Visible in Report Options and Report Totals." Whether the total appears beside its parts is a setting on the pay code.

In the standard report. The Condensed Employee Time Detail report summarises "totaling time and money by business structure and pay code (excluding combined pay codes) and then by pay code only (separately listing combined pay codes)." It has a "Combined Pay Code Summary" section of its own, its report totals are "Pay code summaries (combined pay codes and regular pay codes)," and when you run it the Hours Summaries option makes you choose: "By Job, By Labor, By Combined Pay Codes, By Normal Pay Codes."

In UKG's own warehouse. Data Hub builds its metrics from pay code mappings, and its mapping page rules combined codes out in one sentence: "Only regular paycodes, those which contribute to relevant Data Hub metrics that leverage the mappings, are available in the Configuration Portal. Other types of paycodes, such as combined and cascading, are not relevant." Its worked example is this post's question: "the Data Hub summary metric 'Regular Hours' would aggregate hours for paycodes assigned to the Regular pay category."

A visibility setting, a report layout, and a pipeline rule. All three are UKG's own words, and none of the three is a column a connector could copy.

That's three rules, and the API underneath them returns combined totals by default.

That's the shape this series keeps finding, and here it takes a particular form. Salesforce keeps its roll-up on the parent, so you need a join to opportunity_line_item before the double count starts. Maximo keeps its parts and its parent in one table and tells them apart with a column. UKG is Maximo's shape with a weaker signal: the total sits in the same table as its parts, so a single-table SUM with no WHERE clause is already wrong, and on one of the two timecard tables no column tells you which row is which.

The nearer cousin for the filter, rather than the sum, is JD Edwards, where a code on the row decides what the amount means.

The fix

Patching this query fixes one afternoon. The overtime budget asks the same question next week, and the schema will still look the way it looked this morning.

The definitions belong in the semantic model, written once with UKG's own documentation as the citation, so every hours question inherits them.

Start with the joins. Fivetran creates no foreign keys in the destination, so each one is a declaration with its evidence attached.

# subject_areas/ukg_timekeeping/relationships.yaml
relationships:
  - from_table: employee_time_card_actual_total
    from_column: employee_id
    to_table: employee_time_card
    to_column: employee_id
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      Many total lines per timecard. The API response has one employeeId and
      an array of actualTotals. The child's primary key is _fivetran_id, a
      Fivetran surrogate rather than a UKG key.
    source: https://developer.ukg.com/wfm/reference/retrieve-timecard-data-for-multiple-employees

  - from_table: employee_time_card
    from_column: employee_id
    to_table: employee
    to_column: id
    relationship: one_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      At most one timecard row per employee. Join on employee.id and never on
      employee.number: the API is explicit that employee_id "is not a person
      number", and the manager table is keyed on person_number instead.
    source: https://fivetran.com/_/api/service-erd/ukg_pro_workforce_management/preview-image/public

  - from_table: time_card_as_manager_aggregated_total
    from_column: time_card_as_manager_total_index
    to_table: time_card_as_manager_total
    to_column: index
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      Composite in the landed data. The match must also include
      employee_id on both sides, or every employee's total lines attach to
      every employee's total context that shares an index. The parent's
      total_context_type says whether the lines are actual, scheduled or
      projected.
    source: https://developer.ukg.com/wfm/reference/retrieve-timecard-as-manager

Then the part that carries the weight. No column type can say that one row is the sum of two others, so the semantic model has to.

entities:
  - name: ukg_regular_actual_total
    description: >
      One line of actual timecard totals for a regular pay code, as distinct
      from a combined pay code's line in the same table. UKG defines a
      combined pay code as "totals of several pay codes", Grand Overtime
      being the sum of overtime and double time, and it arrives as its own
      row beside them with combined = TRUE.
    resolves_to:
      table: employee_time_card_actual_total
      key: [_fivetran_id]
      selector: "employee_time_card_actual_total.combined = FALSE"
      forbidden_selectors:
        - >
          Summing hours_amount or wages across pay codes without this
          selector. Every hour inside a combined pay code counts twice.
        - >
          Matching pay_code_name on a pattern such as '%overtime%'. The
          pattern catches Overtime and Grand Overtime together, which is the
          same double count wearing a filter.
    caveats:
      - >
        A single combined pay code may be the figure the business quotes.
        Read that one row, or read its regular parts, never both.
      - >
        Which regular pay codes mean hours worked is a decision a person
        records here. Vacation and sick are regular pay codes too.
      - >
        Whether this connection holds combined rows depends on request
        options Fivetran does not publish. Run ukg_combined_pay_code_check
        before assuming either way.
    source: https://developer.ukg.com/wfm/reference/retrieve-timecard-data-for-multiple-employees

  - name: ukg_combined_pay_code
    description: >
      The pay codes this connection has been seen to flag as combined. No pay
      code table lands, so this is the only way to classify pay codes on
      time_card_as_manager_aggregated_total, whose API shape carries no
      combined property at all. It covers only pay codes that appear in the
      synced employee timecard window.
    resolves_to:
      table: employee_time_card_actual_total
      key: [pay_code_id]
      selector: "employee_time_card_actual_total.combined = TRUE"
    caveats:
      - >
        Membership, meaning which regular pay codes a combined one adds up,
        is not in the warehouse. It lives in UKG setup as selectedPayCodes
        and a person records it here if a metric needs it.
    source: https://developer.ukg.com/wfm/reference/retrieve-combined-paycode-by-id

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

metrics:
  - name: ukg_actual_regular_pay_code_hours
    calculation: >
      Actual hours on regular pay codes, by employee or by pay period.
      Applies the ukg_regular_actual_total selector so combined pay code
      rows are excluded, and reads only rows whose amount type is hours.
    requires_entity: ukg_regular_actual_total
    source_tables: [employee_time_card_actual_total]
    primary_table: employee_time_card_actual_total
    required_filters: [combined, amount_type]
    other_names: [timecard hours, total hours, actual hours, hours by pay code]
    binding: >
      SUM(employee_time_card_actual_total.hours_amount)
        FILTER (WHERE employee_time_card_actual_total.combined = FALSE
                  AND employee_time_card_actual_total.amount_type = 'HOUR')
    citation: >
      UKG, Configure pay codes (the definition of a combined pay code);
      UKG Timekeeping Timecards OpenAPI (TotalsData.combined, and
      totals_include_combined_paycodes defaulting to true).

requires_entity: ukg_regular_actual_total is the load-bearing line. An hours figure reached by summing the raw table is a path the semantic model has already declared invalid, so overtime, regular hours and the staffing ratio all start from the same rows.

Six decisions in here can't be read from the schema, and a person makes each of them once.

Which regular pay codes count as worked. UKG's help says pay codes "can be tracked as productive time or as non-productive time, which does not appear as totaled hours," and Data Hub asks an administrator to map every pay code to a pay category before its metrics return anything. That mapping is the decision the warehouse can't make.

Which combined pay code the business quotes. If payroll's overtime number is a combined code, then that row alone is the right answer, and its parts are the wrong one.

Whether combined rows are present at all, and in which tables. The discovery query answers it and a person writes the answer into the entity's description.

What amount_type literally holds in this extract, since the employee-side schema documents no list.

Which window the connector synced. If the business asks about a period outside it, the fix is the extract rather than the declaration.

Who the connector logs in as. UKG filters by the connecting user's data access and gates wages behind a separate access control point, so a missing row and a null wage can both be permissions rather than facts.

Reproduce it yourself

No UKG trial exists to spin up, so this runs against the extract your employer already has.

1. Run the discovery query. It tells you whether your connection holds combined rows, which pay codes they are, and what your amount_type literally contains.
2. Run the manager-table check if anyone reads the time_card_as_manager tables.
3. Declare the entities and the metric, then ask "how many overtime hours did each employee log last month" in plain English, in the assistant your team already opens, and check the answer against the corrected SQL.

The discovery query. This is your own version of the number this page can't give you.

SELECT combined,
       amount_type,
       pay_code_name,
       COUNT(*)                    AS total_rows,
       COUNT(DISTINCT employee_id) AS employees,
       SUM(hours_amount)           AS sum_hours_amount,
       SUM(wages)                  AS sum_wages,
       MIN(apply_date)             AS first_apply_date,
       MAX(apply_date)             AS last_apply_date
FROM   employee_time_card_actual_total
GROUP  BY combined, amount_type, pay_code_name
ORDER  BY combined DESC, sum_hours_amount DESC;

Read it like this.

• Any row with combined true is a total of other rows in the same result. sum_hours_amount on those rows is the size of your double count, in hours, and it's the headline number for your employer.
• pay_code_name on those rows is the list a person needs, because the membership behind each name stayed in UKG.
• amount_type shows you the literal values this extract uses, which the employee-side schema never documents.
• first_apply_date and last_apply_date bound what the connector actually synced. Questions outside that window have no answer here whatever the SQL says.

The manager-table check. The time_card_as_manager tables have no flag, so this borrows one from the table that does.

SELECT t.total_context_type,
       a.pay_code_name,
       COUNT(*)      AS total_rows,
       SUM(a.amount) AS sum_amount
FROM   time_card_as_manager_aggregated_total a
JOIN   time_card_as_manager_total t
  ON   t.employee_id = a.employee_id
 AND   t.index       = a.time_card_as_manager_total_index
WHERE  a.pay_code_id IN (SELECT DISTINCT pay_code_id
                         FROM   employee_time_card_actual_total
                         WHERE  combined = TRUE)
GROUP  BY t.total_context_type, a.pay_code_name
ORDER  BY sum_amount DESC;

Every row it returns is a combined total sitting unmarked in the manager table. It only catches pay codes that also appear in the synced employee timecard window, so zero rows is weaker evidence than it looks. Note the two-column join: match on employee_id as well as the index, or each employee's total lines attach to every employee's total context. index may need quoting on some destinations.

Corrected. One flag, one unit, and a named list instead of a pattern.

SELECT employee_id,
       SUM(hours_amount) AS overtime_hours
FROM   employee_time_card_actual_total
WHERE  combined = FALSE
  AND  amount_type = 'HOUR'
  AND  pay_code_name IN ('Overtime', 'Double Time')
  AND  apply_date >= DATE '2026-08-01'
  AND  apply_date <  DATE '2026-09-01'
GROUP  BY employee_id;

Two things changed and both matter. combined = FALSE drops the totals of totals. The IN list replaces the pattern, so a pay code named Grand Overtime can't rejoin the sum through its name. Substitute your own regular overtime codes, which the discovery query listed.

If the business quotes a combined figure instead, read that row on its own:

SELECT employee_id,
       SUM(hours_amount) AS grand_overtime_hours
FROM   employee_time_card_actual_total
WHERE  combined = TRUE
  AND  amount_type = 'HOUR'
  AND  pay_code_name = 'Grand Overtime'
  AND  apply_date >= DATE '2026-08-01'
  AND  apply_date <  DATE '2026-09-01'
GROUP  BY employee_id;

One of these two is your overtime number. Adding them together is the original bug in a longer query.

FILTER and ILIKE are PostgreSQL spellings. On Snowflake use COUNT_IF and ILIKE; on BigQuery use COUNTIF and LOWER(...) LIKE. Only the casing and those functions change by route.

If payroll still disagrees with your figure, check three things before the SQL. Whether any rows carry amount_type for days rather than hours, because a day-based code's number lives in days_amount. Whether historical corrections belong in the answer, since corrections land in tables of their own and unioning them double counts a second way. And whether the employee left, because the connector syncs timecards for active employees only.

What this looks like in Agami

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

A combined pay code is declared, not inferred. Introspection reads the columns and gets their types right. What it can't read is that combined = TRUE marks a row as the sum of other rows in the same table. ukg_regular_actual_total records that once, with UKG's own sentence as the citation, so the next hours question doesn't rediscover it.

A forbidden selector is a path the semantic model won't take. Summing hours_amount across pay codes without the flag is declared invalid on the entity, and so is matching pay_code_name on a pattern. An overtime question routes through the named list instead.

Metrics carry their required filters. ukg_actual_regular_pay_code_hours binds to the entity rather than the table and names combined and amount_type as filters it can't be computed without. A query that drops one isn't a different answer to the same question, and the metric says so.

An unsigned metric answers with a warning until a person approves it. Every declaration above ships confidence: proposed and review_state: unreviewed, because none of it has been read by an analyst yet. The review queue is where which pay codes count as worked stops being a guess.

Hours ship once they match payroll's own number. Reconciliation takes a screenshot of the UKG report your payroll team trusts, a CSV, or numbers pasted into chat, and compares them at a one percent tolerance by default. A difference opens the SQL. On these tables it points at a combined code, a unit or an inactive employee, and finding which one is the useful output.

And the number we don't have. We haven't run this against a real UKG warehouse, so this page carries no figure for how much a naive sum overstates hours. It depends entirely on which combined pay codes your tenant defined, and that configuration isn't in the warehouse to measure. The discovery query returns yours. The semantic model also can't decide which regular pay codes mean worked time, or which of the two overtime answers your payroll team quotes. It records the answer once a person gives it.

Frequently asked questions

What is a combined pay code in UKG?

A total of several pay codes, stored as a pay code. UKG's own definition: "Combined pay codes are totals of several pay codes. For example, a combined pay code named Grand Overtime is the sum of all overtime pay codes, such as overtime and double time." Its API lists Combined beside Regular, Duration and Cascade as a pay code type, so a combined code has a name, an effective date and a list of member codes just like any other.

Why are my UKG overtime hours too high in the warehouse?

Almost certainly because a combined pay code is in the sum. In employee_time_card_actual_total a combined total is a row beside the rows it sums, and pay_code_name ILIKE '%overtime%' matches Overtime and Grand Overtime together. Add combined = FALSE and replace the pattern with a named list of your regular overtime codes.

Does Fivetran land combined pay code rows?

It can, and Fivetran doesn't publish whether it does. The UKG timecard API's totals_include_combined_paycodes parameter defaults to true, and TimeCardDataWhere.payCodeType accepts COMBINED or REGULAR with no documented default. Run the discovery query above against your own extract rather than assuming either way.

Which UKG table tells me a row is a combined total?

Only the employee-side one. TotalsData, the API shape behind employee_time_card_actual_total, carries a combined boolean described as "A Boolean indicator of whether or not the total is combined." Timecard_Aggregated_Total, the shape behind time_card_as_manager_aggregated_total, carries no such property. Classify the second table by borrowing the first table's flag on pay_code_id.

Where do I find which pay codes a combined code adds up?

Not in the warehouse. No pay code table lands from this connector, so pay codes reach your destination only as a pay_code_id and a pay_code_name on the rows that used them. The membership list is selectedPayCodes on the combined pay code in UKG setup, and a person has to bring it across.

Doesn't UKG Data Hub already handle this?

Inside Data Hub, yes, and explicitly. Its mapping page says "Only regular paycodes, those which contribute to relevant Data Hub metrics that leverage the mappings, are available in the Configuration Portal. Other types of paycodes, such as combined and cascading, are not relevant." Data Hub is UKG's own BigQuery pipeline, provisioned with UKG rather than self-serve. A team that moved timecards out with a connector to report on them beside the ledger has moved past that rule.

References

1. Configure pay codes, UKG online help. The definition of a combined pay code quoted above, the time-or-money type rule, the "Visible on Totals Tab in Timecard" and "Visible in Report Options and Report Totals" settings, and the Available and Selected Pay Codes lists.
2. Retrieve Timecard Data for Multiple Employees, UKG Developer Hub. The Timekeeping Timecards schema document, including TotalsData and its combined, hoursAmount, daysAmount, wages, applyDate and amountType properties.
3. Retrieve Timecard as Manager, UKG Developer Hub. The totals_include_combined_paycodes parameter and its default, TimeCardDataWhere.payCodeType, TotalContext.totalType, and Timecard_Aggregated_Total with its 34 properties and no combined flag.
4. Retrieve Combined Paycode by ID, UKG Developer Hub and Combined Paycodes for Timekeeping. The Timekeeping Setup schema: TimekeepingSetupPayCode.type, its combined boolean, and selectedPayCodes.
5. Condensed Employee Time Detail report, UKG Pro WFM help. The report that totals by pay code "excluding combined pay codes", its Combined Pay Code Summary section, and the Hours Summaries option.
6. Configure Data Hub Mappings, UKG Pro WFM help and Data Hub. The regular-paycodes-only mapping rule and the "Regular Hours" example metric, and what Data Hub is.
7. UKG Pro Workforce Management connector, Fivetran. The Lite badge, the active-employees limitation, the capture-deletes list, and the incremental-sync list.
8. Lite connectors, Fivetran. The definition whose schema design "Starts with the available API", which is why the API reference is the authority for these columns.
9. UKG Pro Workforce Management setup guide and API configuration, Fivetran. What the connection logs in as, which decides what lands.
10. UKG Pro Workforce Management schema ERD, Fivetran. The column-level picture of what lands, including the combined column and the primary keys quoted above.
11. UKG and Google Cloud partner to transform work with agentic AI, Google Cloud Press Corner. The vendor's own natural-language answer, inside UKG, with integrations "expected to be available in 2026".
12. agami-core on GitHub

Make your UKG data answerable

Agami is the trust layer between your AI assistant and your warehouse. It declares which rows are totals of other rows, so an overtime question returns hours that match payroll's own number or says why it cannot.

Start a free trial or talk to us →