Ten Date Columns on a Toast Order, and Only One Knows When Friday Ended

Ask a Toast warehouse for restaurant sales by day and you get a business date vs calendar date problem in sales reporting that no error message will ever mention. The bar closed at 2am. Nine columns filed those covers on Saturday. The manager's report says Friday, and it is the report that is right.

An operations lead asks what Friday did.

The agent finds orders, finds openedDate, truncates it to a day, joins down to the line items and sums the price. The query compiles. The joins are correct. One row per day, one number per row.

Then the manager opens Toast Web, reads Friday, and gets a different number. Nobody can say which is wrong, because the warehouse contains no column that explains the difference.

Before you start

• Toast already landed in a warehouse. Postgres, Snowflake, BigQuery and Redshift all work. This post is written for the team that already has the data.
• Read access. A read-only role is the right one.

No Toast instance? You cannot get one this afternoon, and that is worth saying plainly rather than sending you off to find out. Toast has a sandbox with simulated payments, and its credentials are issued by Toast's integrations team through the integration partnership process: vetting, review by Toast's compliance, privacy, security and legal teams, and a signed partner agreement. That is a business relationship, not a signup form.

Nothing in a warehouse yet? There is no managed Toast connector. Fivetran ships a Connector SDK template that you deploy and maintain yourself, starting from fivetran init --template toast. Everything below is read from that published template, which is real, versioned code and is what most Toast warehouses run close to unmodified. It is not a canonical Toast schema, because there is no such thing. A team that edited the template landed different names, and column casing varies by extract tool and destination in any case.

The question

What did we sell on Friday?

It is the most-asked question in the building and the one every other number hangs off. Labour percentage, cost of goods, covers per server hour: all of them are a money figure divided by something, and all of them inherit whatever the money figure got wrong about which day it was.

What breaks

Here is the query an analyst writes first, against the template's landed schema:

select date_trunc('day', o.openedDate) as day,
       sum(s.price)                    as sales
from   orders_check_selection s
join   orders_check c on c.id = s.orders_check_id
join   orders       o on o.id = c.orders_id
group  by 1
order  by 1;

Nothing about it is wrong in the way SQL can be wrong. The grain is right: line items, not orders, so no fan-out. The join keys are the ones the template creates. It runs, it returns one row per day, and every row is off.

A restaurant that serves until 1am has a Friday that ends on Saturday. Every cover between midnight and close is filed one day late. Friday looks light, Saturday looks heavy, and the shift that actually worked those hours is credited to the wrong day. Change openedDate to closedDate or paidDate and the numbers move without improving, because the problem is not which timestamp, it is that a timestamp is the wrong kind of thing to ask.

The agent had nine of them to choose from, and it chose well. That is the part worth sitting with.

Why it breaks

Toast does not consider a restaurant's day to end at midnight. It ends at a closeout hour configured per location, and Toast applies it before anything is displayed. Toast's own integration guide says so:

"The closeoutHour value in the General object returned by the restaurants API contains the restaurant's closeout hour. The default closeout hour is 4:00 a.m. local time unless a Toast employee changes this setting. The businessDate value on API data changes after the closeoutHour. Consider daylight savings time when interacting with the closeout hour."

So each order carries businessDate, the day the restaurant considers it to belong to, already resolved against that location's local time zone and its cutoff. That value lands in the warehouse. The cutoff that produced it does not.

Count what the connector template actually declares on orders. Its schema() function specifies thirteen column types: nine UTC_DATETIME and four BOOLEAN.

businessDate is not on that list. It lands anyway, because the template upserts the whole order payload and schema() declares only, in its own words, "any datatypes that we want to specify". So the destination infers its type from a JSON integer of the form 20260904.

That inversion is the whole trap. The nine columns that are wrong for this question are the nine the connector bothered to type. The one that is right arrives untyped, looking like a number rather than a date, sitting beside nine things that look exactly like dates. An agent choosing among them by name and type picks a timestamp every time, and it is not being careless.

The connector template's own schema() block, and the column it does not mention.

Then there is the harder half. The warehouse cannot recompute the right answer either. closeoutHour lives on the General object of the restaurants API, and the template's endpoint list contains /partners/v1/restaurants and no call to it. The restaurant table lands without the cutoff. So the rule that defines a trading day for that location is not in the warehouse in any form: not as a column, not as a config table, not as something a careful analyst could derive with enough SQL. The warehouse holds a correct answer it cannot justify.

What Toast did for you

Every report a manager has ever read in Toast Web was already aligned to their own restaurant's cutoff. Nobody in the building has had to think about it, because the application applied a per-location configuration value on every single render. Toast tells integrators to preserve that, in as many words:

"When using the /ordersBulk endpoint, Toast support recommends using the businessDate parameter to align with what is shown in Toast Web reports. This ensures orders are correctly mapped to the location's business day, as determined by the closeoutHour configured in the restaurants API."

The connector template follows that advice. It fetches orders one business date at a time, building its request as endpoint + "?businessDate=" + d. The concept is load-bearing in the pipeline itself, and it still never lands as a rule anything downstream can check.

This is the same shape as Salesforce's "Opportunities with Products" report type holding the line-item grain, and the same shape as ServiceNow's table inheritance: the thing that made the application's number correct was never a column, so replication had nothing to copy.

There is one more wrinkle that the same replication step hides, and it compounds this one. Toast records voids at four levels and void state does not cascade. Voiding a check leaves every line under it with voided set to false. Modifiers carry a voidDate and no boolean at all. And the template calls op.delete() on deleted and never on voided, so the standard _fivetran_deleted filter is silent about every void in the warehouse. A day-grain fix that does not also handle voids gets the right day and the wrong money.

The fix

Both facts are declarations, not queries. They belong in the semantic model, where they are enforced for every question anyone asks rather than remembered by whoever writes the next one.

The first says what a day is:

entities:
  - name: toast_business_day
    description: >
      The day a restaurant considers a transaction to belong to. Toast computes it
      upstream from the order's open or promised time in the restaurant's LOCAL time
      zone and that location's configured closeoutHour, then lands the computed
      answer on orders.businessDate as an integer of the form YYYYMMDD. Neither
      input lands. closeoutHour is on the restaurants API General object, which the
      connector template never calls, and the nine timestamp columns on orders are
      all declared UTC_DATETIME. So businessDate is the only correct day grain in
      the warehouse AND it cannot be recomputed, validated or explained from
      anything else there.
    resolves_to:
      table: orders
      column: businessDate
      cast: "to_date(orders.businessDate::text, 'YYYYMMDD')"
      forbidden_for_day_grain:
        [openedDate, closedDate, paidDate, createdDate, modifiedDate,
         promisedDate, voidDate, deletedDate, estimatedFulfillmentDate]
    caveats:
      - >
        Every date question defaults to businessDate. A question explicitly about
        clock time, such as kitchen throughput by hour or labour overlap,
        legitimately uses the timestamps, and that is the only case that should.
      - >
        closeoutHour is not in the warehouse. If a metric needs to tell a reader
        their day ends at 4am, a person supplies that once per restaurant. Do not
        infer it from the data; an inferred cutoff that is an hour out is worse
        than no cutoff, because it looks authoritative.
      - >
        Locations in one group can have DIFFERENT closeoutHour values, so a
        group-level day grain is coherent only because Toast already applied each
        location's own cutoff before stamping businessDate. That is an argument for
        trusting the column, not for recomputing it.
      - >
        The integer type is load-bearing. Joining businessDate to a date dimension
        without the cast either fails or silently matches nothing.
    source: https://doc.toasttab.com/doc/cookbook/apiHowToReporting.html

The forbidden_for_day_grain list is the part that earns its place. Naming the right column helps once. Naming the nine wrong ones stops the next question, and the one after that, from quietly reintroducing the same error under a different name.

The second says what actually happened:

entities:
  - name: toast_void_state
    description: >
      Whether a Toast row represents something that happened. Toast records voids at
      four levels, the signal has a different shape at each, and it does not cascade:
      voiding a check leaves every selection under it with voided = false. Any sales
      figure must evaluate void state at its own level AND at every level above it.
    resolves_to:
      orders:                          {boolean: voided, date: voidDate, also: [deleted, createdInTestMode]}
      orders_check:                    {boolean: voided, date: voidDate, also: [deleted]}
      orders_check_selection:          {boolean: voided, date: voidDate}
      orders_check_selection_modifier: {boolean: null,   date: voidDate}
    caveats:
      - >
        Modifiers have NO voided boolean. Void state at that level is
        voidDate IS NOT NULL. A model that assumes a uniform voided column treats
        every voided modifier as valid.
      - >
        createdInTestMode marks orders rung up during staff training. They are
        ordinary rows. Exclude them from sales by default and say so, rather than
        letting a manager find training day in the revenue line.
      - >
        This describes the schema the published Connector SDK template produces.
        A team that edited the template landed different column names and this
        binding must be re-derived against their warehouse.
    source: https://github.com/fivetran/community_connectors/blob/main/toast/connector.py

With both declared, the metric is the thing an agent reaches for, and it cannot be assembled wrongly:

name: toast_net_sales
calculation: >
  Sum of line-item price across selections that actually happened, grouped on the
  restaurant's own business day rather than the calendar day: not voided at line,
  check or order level, not on a deleted order, not rung up in training mode.
bindings:
  PostgreSQL: >
    SELECT to_date(o.businessDate::text, 'YYYYMMDD') AS business_day,
           SUM(s.price)                              AS sales
    FROM   orders_check_selection s
    JOIN   orders_check c ON c.id = s.orders_check_id
    JOIN   orders       o ON o.id = c.orders_id
    WHERE  NOT s.voided
      AND  NOT c.voided
      AND  NOT o.voided
      AND  NOT o.deleted
      AND  NOT o.createdInTestMode
    GROUP  BY 1
source_tables: [orders_check_selection, orders_check, orders]
primary_table: orders_check_selection
default_day_grain: business_day
other_names: [sales, net sales, food sales, revenue]

default_day_grain is what makes "what did we sell on Friday" resolve to the restaurant's Friday without anyone asking for it, and other_names is what makes it resolve when someone says revenue instead of sales.

Governance does not live in the prompt, and that distinction is the whole reason to write any of this down. The model can be argued out of a rule it was merely told about, by nothing more sinister than a question phrased a new way. A rule in the semantic model is applied to every question, including the ones nobody anticipated, and it stays auditable afterwards: the person defending Friday's number can point at the declaration that produced it.

Reproduce it yourself

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

1. Confirm your orders table has businessDate. If your team edited the template it may be named differently or, if the extract was narrowed, be absent. That is the first thing to check and it takes one query.
2. Run the wrong query from the top of this post. Keep the output.
3. Run the gap query below. It tells you how much of your reporting the first query has been quietly moving.
4. Declare the entity and the metric, then ask the question in plain English in the assistant your team already opens every morning, and compare the answer against Toast Web for the same day.

The gap query is the one worth keeping:

select count(*)                                                    as orders,
       sum(case when to_date(o.businessDate::text,'YYYYMMDD')
                     <> date_trunc('day', o.openedDate)::date
                then 1 else 0 end)                                 as filed_on_a_different_day
from   orders o
where  o.businessDate is not null;

Every order in the second column is one your calendar-day report put on the wrong day. For a breakfast cafe that number is zero and you can stop reading. For a bar it is most of Friday and Saturday, and every daily report you have ever run has been wrong in a way that averages out over a week and never averages out over a shift.

Then split it by day of week. The error is not evenly distributed, and seeing it concentrate on the two days that matter most is more persuasive than any total.

And the corrected version of the original question:

select to_date(o.businessDate::text, 'YYYYMMDD') as business_day,
       sum(s.price)                              as sales
from   orders_check_selection s
join   orders_check c on c.id = s.orders_check_id
join   orders       o on o.id = c.orders_id
where  not s.voided and not c.voided and not o.voided
  and  not o.deleted and not o.createdInTestMode
group  by 1
order  by 1;

That one ties to Toast Web, because it is grouping on the same value Toast Web groups on.

Frequently asked questions

What is the difference between business date and calendar date in restaurant sales reporting?

The calendar date is midnight to midnight. The business date is the trading day as the restaurant defines it, running from one closeout hour to the next, so a late night's covers stay on the day the shift belongs to. Toast computes the business date per location and stamps it on every order. Any restaurant that serves past midnight will get different totals from the two, and the business date is the one its own reports use.

Why does my Toast sales query not match Toast Web?

Almost certainly because the query groups on a timestamp rather than on businessDate. Toast Web aligns to the location's configured closeout hour; a date_trunc on openedDate, closedDate or paidDate aligns to midnight UTC. The covers between midnight and close land on the following day in your warehouse and on the correct day in Toast Web.

Where is the Toast closeout hour in my warehouse?

It is not there. closeoutHour sits on the General object of the restaurants API, and Fivetran's published Connector SDK template does not call that endpoint, so the restaurant table lands without it. You can read it from the Toast admin or the restaurants API and supply it once per location. Do not try to infer it from the data.

Why is businessDate an integer instead of a date?

Because Toast returns it as an integer of the form 20260904, and the connector template's schema() function does not declare a type for it, so the destination infers one from the payload. Cast it with something like to_date(businessDate::text, 'YYYYMMDD') before joining it to a date dimension, or the join silently matches nothing.

Does filtering on voided remove voided sales?

Not on its own. Void state does not cascade in Toast, so voiding a check leaves every line item under it with voided set to false. A sales figure has to check void state at the line, the check and the order. Modifiers are different again: they carry a voidDate and no boolean.

References

1. Building a data warehouse integration, Toast. The closeout hour, its 4:00 a.m. default, the rule that businessDate changes after it, the daylight-savings warning, and the instruction to align to Toast Web using businessDate.
2. Toast Orders API reference. The entry point for the Orders API. Its object schemas render client-side, so the column-level facts in this post come from references 1 and 4 rather than from this page.
3. Toast connector, Fivetran. That this is a Connector SDK template you deploy and maintain, not a managed connector.
4. community_connectors/toast/connector.py, Fivetran. The 35 tables, the nine declared UTC_DATETIME columns and four booleans on orders, the endpoint list, the per-business-date fetch loop, and the op.delete() behaviour on deleted but not voided.
5. Void an order, Toast. What the API sets when something is voided.
6. Environments, Toast. The sandbox, and that its credentials come from Toast's integrations team.
7. Integration partnership process, Toast. The vetting, approvals and signed agreement behind that sandbox.
8. agami-core on GitHub

Make your Toast data answerable

Agami is the semantic layer between your AI assistant and your warehouse. It declares what each column means, which joins are safe, and which questions the data cannot answer, so an agent returns a governed number or says why it cannot.

Start a free trial or talk to us →