In Your Certinia Warehouse, the Table Named Timecard Is Not the Timecard

Ask a Certinia data warehouse for billable hours by project last month and two tables offer to answer. One is called pse_timecard_header_c and the other is called pse_timecard_c. The second one is a piece of the first. Inside Certinia PSA nobody meets this, because the app computes the week's total on every screen and carves the week at month end before anyone reads a figure. The computation lives in the application. The extract copied the rows.

A services operations lead wants approved billable hours by project for last month.

Point an AI agent at the replicated schema and it finds plenty to work with. Beside account and opportunity sit about a hundred tables in the pse_ namespace, and two of them carry the word timecard. One has seven columns named after the days of the week. The other has a stored pse_total_hours_c and every billing flag a finance team could want.

The agent picks one, filters on a date, groups by project, and returns a number.

The number is wrong, and nothing in the result says so. September's hours are missing the last four days of a week that started in August, and carrying four days of October that nobody has invoiced yet.

Before you start

• Certinia PSA replicated through the Salesforce connector. Certinia is a managed package in the pse namespace, so its objects are custom objects in your Salesforce org. Fivetran replicates them beside the standard objects with no separate setup, and Airbyte reads custom objects the same way.
• The licence that decides whether the tables exist at all. Fivetran's FAQ is blunt about it: "An installed package needs a license to view the associated custom objects. Assign your account a license." Without a Certinia licence on the connecting user, the pse_ tables are simply absent, and nothing in the sync log explains why.
• Formula fields don't arrive. Fivetran's connector page says it "automatically detects formula fields and excludes them from the tables synced to your destination." On the timecard that's the total. Fivetran's route back is its formula-field transformation, which stores the definitions in a FIVETRAN_FORMULA table and builds view models from them.
• Table names come from the API name rather than the label. pse__Timecard__c lands as pse_timecard_c under Fivetran's published rule, whose own example is My_Name____c becoming my_name_c. The label a person sees in the app doesn't travel.
• No sandbox to practise on. Certinia PSA is a licensed package with no trial org and no test drive, so route one here is a Salesforce sandbox your firm already owns. Casing varies by extract tool: lower-case on Postgres and BigQuery, upper-case on Snowflake, and the full pse__Timecard__c under source naming or Airbyte. The SQL below uses Fivetran's lower-case names.

The question

"How many approved billable hours did each project consume last month?"

Every services firm asks it, and four more numbers hang off it. Utilisation, realisation, project margin and revenue per consultant all start from that figure, so an error in it reaches a partner review before anyone re-derives the first number.

It comes out of the warehouse rather than out of Certinia because those ratios need the firm's other systems beside it: the ledger, the compensation model, the pipeline.

What breaks

Here's the query almost anyone writes first.

SELECT p.name AS project,
       SUM(h.pse_sunday_hours_c + h.pse_monday_hours_c + h.pse_tuesday_hours_c
         + h.pse_wednesday_hours_c + h.pse_thursday_hours_c
         + h.pse_friday_hours_c + h.pse_saturday_hours_c) AS billable_hours
FROM   pse_timecard_header_c h
JOIN   pse_proj_c p ON p.id = h.pse_project_c
WHERE  h.pse_billable_c
  AND  h.pse_status_c = 'Approved'
  AND  h.pse_start_date_c >= DATE '2026-09-01'
  AND  h.pse_start_date_c <  DATE '2026-10-01'
GROUP  BY 1
ORDER  BY 2 DESC;

Nothing about it is careless. The seven-column sum is there because no total column exists to use. pse_status_c really is a picklist whose values are Approved, Rejected, Saved and Submitted. The date filter reads like a month.

It isn't one. Every row in that table is a week, and the filter keeps or drops the whole week based on which month its first day fell in.

The result is plausible at a glance, which is the worst thing about it. No total is absurd, no project is missing, and nobody can see that four days of work moved between two months.

Why it breaks

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

The day is a column, and the week's total is a formula

pse__Timecard_Header__c is what Certinia's dictionary describes as the record that "Records the hours and days worked by a resource in a given week." It carries Sunday_Hours__c through Saturday_Hours__c, each a Number(4,2), plus a required Start_Date__c and an End_Date__c.

It also carries a total, and that total is computed on read. The dictionary prints the definition:

Total_Hours__c · Total Hours · Formula: Sunday_Hours__c + Monday_Hours__c + Tuesday_Hours__c + Wednesday_Hours__c + Thursday_Hours__c + Friday_Hours__c + Saturday_Hours__c · Number(18,2)

Total_Billable_Amount__c is a formula too, branching on whether the firm bills a daily rate, and Total_Cost__c is a text formula that glues a currency code onto a product. Fivetran excludes all three. The week lands with seven hour columns and nothing that adds them up.

That's the same failure Business Central's dimension columns hit, with Fivetran naming the policy out loud. On its own it costs you a SUM of seven columns instead of one. The next fact is what turns it into a wrong answer.

The week doesn't respect the month

Certinia warns about this in its own voice, on its own time-periods page:

Week time periods can span months, quarters and years. When setting up a time period, check the start and end date for weeks with boundaries that span other time periods. Actuals for weeks that cross these boundaries might not be included in larger time period reporting.

A timecard is a week. Take a Sunday-start week beginning 27 September 2026: it ends on Saturday 3 October, so four of its days are September and three are October. That's calendar arithmetic rather than a claim about anyone's data, and up to twelve weeks a year contain a month boundary.

Filter headers by pse_start_date_c in October and that entire week disappears, including the three October days inside it. Filter by September and the whole week counts, including the October days. The week that starts Sunday 30 August behaves the same way one month earlier.

The error isn't a scale factor you could correct for. It's a shuffle of partial weeks between adjacent months, and it lands on whichever projects happened to be busy at month end. Which weekday starts the week is per resource: Certinia respects each resource's work calendar and falls back to Sunday.

The app already fixed it, in a child table with the parent's name

Certinia solved this years ago, and the solution is in the reader's warehouse already. It's in the table whose name says it's the question.

pse__Timecard__c is the split. The dictionary's own description, verbatim:

Represents a whole timecard or part of a timecard. PSA creates timecard splits when a timecard spans the boundary of a time period, allowing you to bill for part of a timecard. Submission, approval, and inclusion in financials occur on the timecard, but subsequent billing and invoicing are associated with child timecard splits.

Then, on the same page, the sentence this whole post turns on:

Note the object name, Timecard, and API name, pse__Timecard__c. Timecards (pse__Timecard_Header__c) have a master-detail relationship with timecard splits.

So the object the API calls Timecard is labelled Timecard Split in the app, and the object labelled Timecard is named Timecard_Header. The label is the only thing that tells them apart for a person, and Fivetran names tables from the API name. The label never reaches the warehouse.

The label is what tells the two apart in the app, and it's the one thing the extract leaves behind. Illustrative row counts; the object names, labels, field types and formulas are Certinia's.

The split is where the answer lives. Its Total_Hours__c is a plain Number(5,2) with no formula, so it lands. Every billing column lives there and only there: Billed__c, Invoiced__c, Billing_Event_Item__c, Eligible_for_Billing__c, Bill_Date__c, Invoice_Number__c.

And PSA writes them at exactly the boundaries that broke the first query. From the split overview:

One or more timecard splits are always automatically generated for each timecard that is submitted for approval. Multiple timecard split records are created when a timecard spans more than one time period.

The vendor's own example: "You enter a timecard for the last week in quarter three and the last day of that quarter falls on a Wednesday. Two timecard splits are created: A timecard split contains the hours for quarter three. A timecard split contains the hours for quarter four."

Which is where the second wrong number comes from. An agent that wants billing flags has to join the header to the split, and if it keeps summing the header's day columns after that join, every split week counts once per split. The Wednesday week in Certinia's example counts twice. That's the Salesforce fan trap in a new costume, and it's reachable from the correct instinct to go and find the billing flags.

Two things about splits are per org, and both change what the split table can answer. Which period types split a week is a configuration option, Timecard_Split_Time_Period_Types. And splits are generated on submission, so a header still sitting in Saved has none at all.

What the application did for you

Inside Certinia PSA, nobody meets any of this.

The Timecard Entry page is a weekly grid: a Week Ending Date, seven daily-hours columns, a sum per line and a total per week. The week totals itself through the formula. Billing runs on the splits. Revenue forecasting runs on the splits. And Project Actuals keeps rolled-up hours per project per time period as rows of their own, joined to Time_Period__c, rebuilt when timecards are approved.

Certinia built four different structures so that nobody has to read a week as a month. Two of the four are formulas that don't replicate, and two are configuration-dependent child tables whose names don't say what they are.

That shape is familiar by now. Toast has the same boundary problem and answers it with a column, a business date stamped on every order. Clio hides a unit inside a column name. Certinia's answer is the opposite shape from Toast's: not a column but a child row, written only when the week is submitted, and only at the boundaries an administrator configured.

The fix

Patching this query fixes one afternoon. Utilisation asks the same question next week and makes the same reasonable mistake, because the schema will still look the same.

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

Start with the joins. None of them is a foreign key in the warehouse, because Salesforce has no such thing and the connector lands plain ID columns. Every one is a Lookup or a MasterDetail in the dictionary, so the direction and the many-side are documented.

# subject_areas/certinia_services_delivery/relationships.yaml
relationships:
  - from_table: pse_timecard_c
    from_column: pse_timecard_header_c
    to_table: pse_timecard_header_c
    to_column: id
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      A timecard split belongs to exactly one timecard. PSA writes one split
      per time period the week touches, so a week crossing a boundary has two
      rows here and one in the parent. Note the shape of the names: the child
      table is called timecard, and its foreign key is called timecard_header.
    source: https://help.certinia.com/TechnicalReference/2025.2/ProfessionalServicesAutomation/Schema/Timecard__c.htm

  - from_table: pse_time_date_c
    from_column: pse_timecard_c
    to_table: pse_timecard_header_c
    to_column: id
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      One row per date inside the parent timecard's week, seven per week,
      present only when create-time-date-records is on. The master-detail
      field is named Timecard__c and looks up to Timecard_Header__c rather
      than to the split object that shares its name.
    source: https://help.certinia.com/TechnicalReference/2025.2/ProfessionalServicesAutomation/Schema/Time_Date__c.htm

  - from_table: pse_timecard_header_c
    from_column: pse_project_c
    to_table: pse_proj_c
    to_column: id
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      The project the week was logged against. Timecard_Header__c.Project__c
      is a Lookup to Proj__c.
    source: https://help.certinia.com/TechnicalReference/2025.2/ProfessionalServicesAutomation/Schema/Timecard_Header__c.htm

Then the part that carries the weight. Column types can't say which of two similarly named tables is the week, so the model has to.

entities:
  - name: certinia_timecard_week
    description: >
      One resource's week on one project, which is what Certinia labels a
      Timecard and what the warehouse calls pse_timecard_header_c. The day is
      a column, Sunday_Hours through Saturday_Hours, and Start_Date and
      End_Date bound the week. Total_Hours is a formula on this object and
      Fivetran does not replicate it. pse_timecard_c, whose API name is
      Timecard and whose label is Timecard Split, is this row's child.
    resolves_to:
      table: pse_timecard_header_c
      key: [id]
      selector: "pse_timecard_header_c.pse_status_c = 'Approved'"
      forbidden_selectors:
        - >
          Filtering pse_start_date_c to mean a month, quarter or year. The row
          is a week and the filter keeps or drops all seven days by the first
          one. Unpivot to dates first.
        - >
          Summing this table's hour columns after joining pse_timecard_c.
          Every week PSA split has more than one child and would count once
          per child.
    caveats:
      - >
        Which weekday starts the week is per resource, from the work calendar,
        defaulting to Sunday. The unpivot below reads the weekday columns
        against Start_Date, so it is correct either way.
      - >
        Approved is one of several ways this app says a week counts. Status,
        the Approved checkbox and Include In Financials can disagree, and
        which one this firm means is recorded here once.
    source: https://help.certinia.com/TechnicalReference/2025.2/ProfessionalServicesAutomation/Schema/Timecard_Header__c.htm

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

metrics:
  - name: certinia_billable_hours
    calculation: >
      Approved billable hours by project for a date range, computed at the day
      grain by unpivoting the seven weekday columns to dates, so a week that
      crosses the range boundary contributes only the days inside it. Reads
      the header alone, so nothing fans out.
    requires_entity: certinia_timecard_week
    source_tables: [pse_timecard_header_c]
    primary_table: pse_timecard_header_c
    other_names: [billable hours, approved hours, hours by project, timecard hours]
    binding: >
      SUM(CASE d.dow
            WHEN 0 THEN h.pse_sunday_hours_c    WHEN 1 THEN h.pse_monday_hours_c
            WHEN 2 THEN h.pse_tuesday_hours_c   WHEN 3 THEN h.pse_wednesday_hours_c
            WHEN 4 THEN h.pse_thursday_hours_c  WHEN 5 THEN h.pse_friday_hours_c
            WHEN 6 THEN h.pse_saturday_hours_c END)
    citation: >
      Certinia PSA data dictionary: Timecard_Header__c (the seven weekday
      columns and the Total_Hours formula), Timecard__c (the split and its
      stored total); About Time Periods, Work Calendars, and Holidays.

requires_entity: certinia_timecard_week is the load-bearing line. A billable-hours figure reached by filtering pse_start_date_c is a path the model has already declared invalid, so utilisation and realisation start from the same hours.

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

Which time-period types split a week. Timecard_Split_Time_Period_Types is per org. If it includes Month, the split table already answers the month question directly. If it only holds Quarter, the splits are right for quarters and no help for months.

Whether Time Date records exist. pse__Time_Date__c holds one row per date, seven per week, and only when create-time-date-records is on. Where it's on, it's the simplest correct source. Where it was switched on part-way through, earlier weeks have no rows and the unpivot is the only route.

What "counts". Status__c, Approved__c, Include_In_Financials__c and Exclude_From_Utilization__c are four columns and one business decision. Utilisation reads one, actuals read another, and a project manager's headline reads a third.

Hours or days. Since Spring 2023 a timecard can be logged in days, with seven *_Days__c columns beside the seven *_Hours__c and an hours-to-days rule on the project. A firm that bills day rates has two units in one row.

Whether to recover the formula columns. Fivetran's formula-field transformation can rebuild Total_Hours__c in the destination. Recomputing it from the seven columns in the model is the other option, and it's the one that keeps the definition where a reader can see it.

Reproduce it yourself

There's no Certinia trial org to spin up, so this runs against your own replicated extract or a Salesforce sandbox your firm already has.

1. Run the discovery query. It tells you how many of your weeks cross a month, how many PSA split, and whether the splits agree with the headers.
2. Run the gap query. It puts the first query's answer beside the corrected one, per project.
3. Declare the entity and the metric, then ask "how many approved billable hours did each project consume 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.

WITH header AS (
  SELECT id,
         pse_status_c,
         pse_start_date_c,
         pse_end_date_c,
         pse_sunday_hours_c + pse_monday_hours_c + pse_tuesday_hours_c
           + pse_wednesday_hours_c + pse_thursday_hours_c
           + pse_friday_hours_c + pse_saturday_hours_c AS day_hours
  FROM   pse_timecard_header_c
),
split AS (
  SELECT pse_timecard_header_c  AS header_id,
         COUNT(*)               AS splits,
         SUM(pse_total_hours_c) AS split_hours
  FROM   pse_timecard_c
  GROUP  BY 1
)
SELECT COUNT(*)                                            AS weeks,
       COUNT(*) FILTER (WHERE EXTRACT(MONTH FROM h.pse_start_date_c)
                          <> EXTRACT(MONTH FROM h.pse_end_date_c)) AS weeks_crossing_a_month,
       COUNT(*) FILTER (WHERE s.splits > 1)                AS weeks_split_across_periods,
       COUNT(*) FILTER (WHERE s.splits IS NULL
                          AND h.pse_status_c <> 'Saved')   AS submitted_weeks_with_no_split,
       SUM(s.split_hours) - SUM(h.day_hours)               AS split_minus_header_hours
FROM   header h
LEFT   JOIN split s ON s.header_id = h.id;

Read it like this.

• weeks_crossing_a_month is how many weekly rows the first query misfiles. That's the headline number for your firm.
• weeks_split_across_periods is how many rows a header-to-split join would double.
• submitted_weeks_with_no_split should be zero. Anything else says the split table isn't the whole story.
• split_minus_header_hours should also be zero. A non-zero value means headers were edited after their splits were generated, or splits are missing.

One more line tells you whether the daily table is available to use instead:

SELECT COUNT(*) AS time_date_rows FROM pse_time_date_c;

Zero rows, or no such table, means create-time-date-records is off and the unpivot below is your route.

The gap query. Same month, same filters, both answers side by side.

WITH days AS (
  SELECT h.id,
         h.pse_project_c  AS project_id,
         h.pse_billable_c AS billable,
         h.pse_status_c   AS status,
         h.pse_start_date_c
           + ((d.dow - EXTRACT(DOW FROM h.pse_start_date_c)::int + 7) % 7) AS work_date,
         CASE d.dow
           WHEN 0 THEN h.pse_sunday_hours_c
           WHEN 1 THEN h.pse_monday_hours_c
           WHEN 2 THEN h.pse_tuesday_hours_c
           WHEN 3 THEN h.pse_wednesday_hours_c
           WHEN 4 THEN h.pse_thursday_hours_c
           WHEN 5 THEN h.pse_friday_hours_c
           WHEN 6 THEN h.pse_saturday_hours_c
         END AS hours
  FROM   pse_timecard_header_c h
  CROSS  JOIN (VALUES (0), (1), (2), (3), (4), (5), (6)) AS d(dow)
),
by_day AS (
  SELECT project_id, SUM(hours) AS real_hours
  FROM   days
  WHERE  billable AND status = 'Approved'
    AND  work_date >= DATE '2026-09-01'
    AND  work_date <  DATE '2026-10-01'
  GROUP  BY 1
),
by_week_start AS (
  SELECT h.pse_project_c AS project_id,
         SUM(h.pse_sunday_hours_c + h.pse_monday_hours_c + h.pse_tuesday_hours_c
           + h.pse_wednesday_hours_c + h.pse_thursday_hours_c
           + h.pse_friday_hours_c + h.pse_saturday_hours_c) AS first_query
  FROM   pse_timecard_header_c h
  WHERE  h.pse_billable_c AND h.pse_status_c = 'Approved'
    AND  h.pse_start_date_c >= DATE '2026-09-01'
    AND  h.pse_start_date_c <  DATE '2026-10-01'
  GROUP  BY 1
)
SELECT p.name AS project,
       w.first_query,
       d.real_hours,
       w.first_query - d.real_hours AS drift
FROM   pse_proj_c p
LEFT   JOIN by_week_start w ON w.project_id = p.id
LEFT   JOIN by_day        d ON d.project_id = p.id
WHERE  COALESCE(w.first_query, d.real_hours) IS NOT NULL
ORDER  BY ABS(COALESCE(w.first_query, 0) - COALESCE(d.real_hours, 0)) DESC;

drift is what the week-shaped filter costs, per project, in both directions. A project whose work stopped mid-month shows a drift of zero, which is why the first query survives review.

Corrected. One grain, and the filter applied to the day rather than to the week.

WITH days AS (
  SELECT h.pse_project_c  AS project_id,
         h.pse_billable_c AS billable,
         h.pse_status_c   AS status,
         h.pse_start_date_c
           + ((d.dow - EXTRACT(DOW FROM h.pse_start_date_c)::int + 7) % 7) AS work_date,
         CASE d.dow
           WHEN 0 THEN h.pse_sunday_hours_c
           WHEN 1 THEN h.pse_monday_hours_c
           WHEN 2 THEN h.pse_tuesday_hours_c
           WHEN 3 THEN h.pse_wednesday_hours_c
           WHEN 4 THEN h.pse_thursday_hours_c
           WHEN 5 THEN h.pse_friday_hours_c
           WHEN 6 THEN h.pse_saturday_hours_c
         END AS hours
  FROM   pse_timecard_header_c h
  CROSS  JOIN (VALUES (0), (1), (2), (3), (4), (5), (6)) AS d(dow)
)
SELECT p.name AS project,
       SUM(days.hours) AS billable_hours
FROM   days
JOIN   pse_proj_c p ON p.id = days.project_id
WHERE  days.billable
  AND  days.status = 'Approved'
  AND  days.work_date >= DATE '2026-09-01'
  AND  days.work_date <  DATE '2026-10-01'
GROUP  BY 1
ORDER  BY 2 DESC;

The weekday arithmetic is Certinia's own, reversed. Time_Date__c.Day_Of_Week__c is defined as CASE(MOD((Date__c-DATE(2000,1,2)),7), 0, Sunday, 1, Monday, ...), which is Sunday-anchored, and that's what PostgreSQL's EXTRACT(DOW) returns. Because the columns are named by weekday and the week is bounded by its own dates, Monday's date is the unique Monday inside the week whatever the work calendar says. The query needs no knowledge of the org's week start day.

EXTRACT(DOW) and FILTER are PostgreSQL spellings. On Snowflake use DAYOFWEEK and COUNT_IF; on BigQuery use EXTRACT(DAYOFWEEK) minus one and COUNTIF. Only the casing and those two functions change by route.

If a project manager still disagrees with PSA's own report, check three things before the SQL. Whether the report reads Project Actuals rather than timecards, since actuals are keyed to a time period and rebuilt on approval. Whether any week is still in Saved, because unsubmitted time exists in the header and nowhere else. And whether the firm logs any time in days rather than hours.

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.

Which table is the week is a declaration, not an inference. Introspection reads the master-detail and gets the direction right: pse_timecard_c is the child. What it can't read is that the child's name is the parent's noun. certinia_timecard_week records that once, in the entity description, so the next question doesn't re-derive it from column names that actively mislead.

A forbidden selector is a path the model won't take. Filtering pse_start_date_c to mean a month is declared invalid on the entity, so a month question routes through the unpivot rather than through the week's first day. Summing the header's day columns after joining the splits is declared invalid for the same reason.

The fan trap gets named before the SQL runs. Join cardinality is declared, so a header-to-split join under an aggregate is detected per aggregate and reported: which join inflated which number. It doesn't rewrite your SQL or refuse the query, because whether a doubled week is a bug depends on what you asked. Naming it is the part that was missing.

A formula that didn't replicate can still be written down. Certinia's dictionary prints Total_Hours__c's definition in full, and it's exactly the column Fivetran left behind. A model seeded from that page carries the sum the warehouse lost, as a metric with the vendor's page as its citation.

Hours ship once they match the firm's own report. Reconciliation takes a screenshot of the PSA report your partners trust, a CSV, or numbers pasted into chat, and compares them at a one percent tolerance by default. A mismatch opens the SQL. On these tables it points at a week boundary, a split or a status, and finding which one is the useful output.

And the number we don't have. We haven't run this against a real Certinia warehouse, so this page carries no figure for what share of a firm's hours sit in weeks that cross a month, or how far the first query drifts. The discovery query returns yours. The semantic model also can't decide which time-period types your org splits on, or whether flat-fee hours belong in utilisation. It records the answer once a person gives it.

Frequently asked questions

What's the difference between pse__Timecard__c and pse__Timecard_Header__c?

The header is the week. Certinia labels pse__Timecard_Header__c as Timecard in the app, and describes it as recording "the hours and days worked by a resource in a given week." The other object, pse__Timecard__c, is its child, and the app labels it Timecard Split. Certinia's dictionary says so outright: "Note the object name, Timecard, and API name, pse__Timecard__c." Because Fivetran names tables from the API name, neither label reaches your warehouse.

Why is Total_Hours missing from my Certinia timecard table?

Because it's a formula. Certinia defines Timecard_Header__c.Total_Hours__c as the sum of the seven weekday columns. Fivetran's connector page says it "automatically detects formula fields and excludes them from the tables synced to your destination." Sum pse_sunday_hours_c through pse_saturday_hours_c instead, or rebuild the column with Fivetran's formula-field transformation.

Why don't my Certinia hours match the month?

Because a timecard is a week, and a filter on its start date keeps or drops all seven days by the first one. Certinia's own documentation warns that "Week time periods can span months, quarters and years" and that "Actuals for weeks that cross these boundaries might not be included in larger time period reporting." Unpivot the seven weekday columns to dates and filter on the day.

Can I just join the timecard header to the timecard splits?

Only if you read the split's own stored Total_Hours__c rather than the header's day columns. PSA writes "one or more timecard splits" per timecard, and more than one whenever the week spans a time period, so summing the parent's columns across that join counts every split week once per split. The billing flags are on the split and only on the split, which is what makes the join tempting.

Why are the pse_ tables missing from my warehouse entirely?

Almost certainly a licence. Fivetran's Salesforce FAQ says "An installed package needs a license to view the associated custom objects. Assign your account a license." Certinia PSA is a managed package, so the connecting user needs a Certinia licence to see its objects at all, and the sync log won't say so.

Doesn't Certinia Veda already answer this?

Inside Certinia, it may. Veda runs on the live Salesforce org, where the formulas evaluate, the splits exist and Project Actuals are current. Certinia has extended it to reach Claude and Microsoft Copilot over MCP, still against the live org. A firm that only ever asks Veda never meets this trap. A firm that moved the data out to report on it beside the ledger has moved past what Veda can see.

References

1. Certinia PSA data dictionary, Timecard_Header__c. The schema of record for the week: the seven *_Hours__c columns at Number(4,2), the required Start_Date__c, the Status__c picklist, and the Total_Hours__c, Total_Billable_Amount__c and Total_Cost__c formulas quoted above.
2. Certinia PSA data dictionary, Timecard__c. The split: its object description, the "Note the object name" sentence, the master-detail relationship, the stored Total_Hours__c at Number(5,2), and the billing columns.
3. Certinia PSA data dictionary, Time_Date__c and Project_Actuals__c. The daily grain and its Sunday-anchored weekday formula, and the app's own hours per project per time period.
4. Timecard Split Overview, Certinia Help. Splits are "always automatically generated" on submission, more than one when the week "spans more than one time period", with the quarter-three example.
5. About Time Periods, Work Calendars, and Holidays, Certinia Help. The vendor's own warning that weeks span months, quarters and years.
6. Managing Timecard Settings, Certinia Help and Timecard Entry Fields. create-time-date-records, the split time-period types, the week start day, and the weekly entry grid.
7. Salesforce connector, Fivetran. Formula-field exclusion, the one-to-one object mapping, and the FIVETRAN_FORMULA system tables.
8. Salesforce connector FAQ, Fivetran. The package licence, and tables named from the object's API name rather than its label.
9. Naming conventions, Fivetran. The rule table whose own example is My_Name____c becoming my_name_c.
10. Salesforce source, Airbyte. Custom-object support and the formula-field caveat on the other route.
11. Certinia, the Veda expansion release. The vendor's own natural-language answer, inside the application and over MCP.
12. agami-core on GitHub

Make your Certinia data answerable

Agami is the trust layer between your AI assistant and your warehouse. It declares which of the two timecard tables is the week, so a month means the days inside it and an agent returns hours that match your firm's report or says why it cannot.

Start a free trial or talk to us →