Square Puts Tips on Their Own Line and Replication Folds Them Back In

Before you start

Query your Square data with AI against a replicated schema and the first number you ask for, last month's sales, has fifteen candidate columns and no definition attached to any of them. Square's own report distinguishes net sales vs total sales, and the warehouse distinguishes nothing. This post assumes you already have Square landing in a warehouse through Fivetran's managed Square connector or Airbyte's source-square. You'll need read access to payments, orders and refunds.

One gotcha worth knowing before you start. Every money field in Square is a nested {amount, currency} object, so your extract tool decides how it flattens into a column. Fivetran's docs render table names in upper case and Airbyte declares its streams in lower case. The first query below discovers your real column names, and nothing after it will run until you've substituted them.

The question

A finance lead asks what sales were last month, and expects the answer to match the sales summary in Square Dashboard. That's a reasonable expectation and a specific one. The Dashboard report is the number the business already trusts, so reproducing it is the whole job.

What breaks

Point an agent at the replicated schema and ask it. It finds a table called payments, a column whose name contains the word total, and writes this:

-- The query the column name invites.
select date_trunc('month', created_at) as month,
       sum(total_money_amount)         as sales
from payments
group by 1
order by 1;

That query runs. It returns a number. The number is wrong, and it's wrong in a way that no error message will tell you about, because Square documents exactly what's in that column:

total_money: "The total amount for the payment, including amount_money and tip_money."

And the column beside it:

amount_money: "The amount processed for this payment, not including tip_money."

So the column named total is the one with the gratuity in it. Sum it and you report the business inflated by every card tip it collected. The error is a percentage of the answer rather than a multiple of it, which is why it survives review: a number 14% too high looks like a good month.

A restaurant or a salon carries the largest version of this, because gratuity is a bigger share of the ticket there than in retail.

Why it breaks

Square already decided what your revenue is

This is the part that makes the schema confusing rather than merely sparse. Square didn't leave the definition open. It published one, and sellers read it every day in the sales summary. From Square's own glossary:

Net sales: "Gross sales minus returns minus discounts and comps."

Total sales: "Net sales plus taxes, tips, gift card sales minus additional amount refunds."

Total collected: "Payment amount - Refund amount"

Net total: "Total collected minus fees minus other withholdings."

Tips: "The total of voluntary gratuity added to the transaction by your customers. This excludes cash tips because they're not recorded in the point of sale app."

Read that list as a waterfall, because that's what it is. Each line is defined in terms of the one above it. And tips sit on their own line, separate from net sales, because Square had already worked out that gratuity isn't revenue.

The warehouse gets the arithmetic and none of the definitions

Now look at what replication actually lands. Not one of those five names appears anywhere in the schema. What arrives instead is a set of overlapping money columns on two tables.

On payments: amount_money, total_money, tip_money, refunded_money, approved_money, and processing_fee, which is an array rather than a scalar.

On orders: total_money, total_tax_money, total_discount_money, total_tip_money, total_service_charge_money, net_amount_due_money, plus net_amounts, return_amounts and rounding_adjustment, all three marked Beta in Square's own reference.

Left, the definitions Square publishes and a seller reads. Right, what a connector copies. The arithmetic replicates and the waterfall doesn't.

An agent choosing among those columns has their names to go on and nothing else. total_money wins on name similarity to the question, and loses on meaning.

Tips aren't the business's money

This is why the wrong column is wrong in kind and not just in size. Gratuity is collected on behalf of staff and owed to them. A sales figure that includes it isn't a slightly optimistic revenue number; it's a revenue number with somebody else's money in it. That propagates into margin, into per-location comparisons, and into anything built on top.

Square's glossary already draws that line by giving tips a line of their own. The warehouse doesn't draw it anywhere.

Square's glossary contradicts itself, and this post won't fix it

One caution, because it affects how far you can push the vendor's own definitions. Two lines on that same glossary page are mutually circular:

Net sales: "Gross sales minus returns minus discounts and comps."

Gross sales: "Net sales plus taxes."

Each defines itself in terms of the other. Both can't hold. We're flagging it rather than picking a reading, because choosing one would assert a definition Square doesn't. Build on Total collected, Net total, Total sales and Tips, which are stable and internally consistent, and treat the gross-versus-net boundary as something to settle with your finance team rather than with a quote.

Two more things the schema won't tell you

Payment status. payments carries a status column whose values include APPROVED, PENDING, COMPLETED and CANCELED, and they all coexist in the table. An unfiltered sum counts money that was never collected.

The amounts are integers in the currency's base unit, and the divisor varies by row. Square states the rule plainly: in the Money object, "the amount field indicates the amount in the base unit of the currency". For US dollars the base unit is cents, so the divisor is 100. For Japanese yen it's 1. The currency sits in a sibling column on the same row, and Location.currency is a property of the location. So no single scale factor is correct for the whole column, and sum(amount)/100.0 understates a Japanese location by a factor of 100 while being exactly right for the US rows next to it.

The scale factor is a property of the row, so one divide is wrong for any estate with more than one currency.

Cash tips are absent entirely. Square's glossary says the Tips line "excludes cash tips because they're not recorded in the point of sale app". No column anywhere in the replica carries them, so any tip total you compute is card tips only.

Square answers this question inside Square

Worth saying plainly, because it bounds what this post is claiming. Square ships a conversational assistant over the seller's own data, inside the Dashboard, free. From Square's announcement in June 2025:

"Square AI starts small, with a new conversational AI assistant integrated directly into Square Dashboard. It is available now in public beta for all sellers* in the US. Now, when a seller visits their dashboard, they can simply ask questions about their business using natural language, and receive instant, direct answers."

Managerbot succeeded it in open beta in April 2026, described as "the next evolution of Square AI".

If Square is your only system, that's a better answer than anything here. It already knows the five definitions, because it lives beside the report that uses them.

The question it can't answer is the one that leaves Square. Takings joined to a payroll system. To an accounting ledger. To a second point of sale. Or across several Square accounts that a multi-location operator consolidates by hand. That's the question this post is about, and the warehouse is where it gets asked.

The fix

The declaration does two things. It names what each column contains, and it stops the word sales resolving to the column that merely sounds like it.

# semantic_model/square/metrics.yaml
# Column names follow Square's documented API field path. Confirm them
# against your own replica with query 0 below: the flattening of the
# nested Money object varies by extract tool.

metrics:
  - name: card_amount_excluding_tip
    description: >
      Amount processed, excluding gratuity, completed payments only.
      Square documents amount_money as "The amount processed for this
      payment, not including tip_money". Still in the currency's base unit.
    calculation: SUM(payments.amount_money_amount)
    bindings:
      - payments.amount_money_amount
      - payments.status
    source_tables: [payments]
    primary_table: payments
    default_filters:
      - payments.status = 'COMPLETED'
    reviewed: false

  - name: card_gratuity
    description: >
      Card tips only. Square's glossary excludes cash tips, which are
      "not recorded in the point of sale app", so this is not total
      gratuity. Owed to staff; never add this to a revenue metric.
    calculation: SUM(COALESCE(payments.tip_money_amount, 0))
    bindings:
      - payments.tip_money_amount
      - payments.status
    source_tables: [payments]
    primary_table: payments
    default_filters:
      - payments.status = 'COMPLETED'
    reviewed: false

excluded_columns:
  # total_money is a real column and a correct one. It is excluded from
  # the revenue subject area so that "sales" cannot resolve to it by name.
  - table: payments
    column: total_money_amount
    reason: >
      Includes gratuity. Named like revenue, is not revenue.

# semantic_model/square/relationships.yaml
relationships:
  - name: payments_to_locations
    from_table: payments
    from_column: location_id
    to_table: locations
    to_column: id
    cardinality: many_to_one
    # Location.currency is "The currency used for all transactions at
    # this location", which is what makes the base-unit divisor a
    # per-row property rather than a constant.
    reviewed: false

  - name: refunds_to_payments
    from_table: refunds
    from_column: payment_id
    to_table: payments
    to_column: id
    cardinality: many_to_one
    # No foreign key survives replication: the source is a REST API,
    # not a database, so there is nothing for an inference tool to read.
    reviewed: false

  - name: payments_to_orders
    from_table: payments
    from_column: order_id
    to_table: orders
    to_column: id
    cardinality: many_to_one
    # Payment.order_id is an optional string, so this join drops
    # payments taken outside the Orders API. Do not make it inner.
    reviewed: false

reviewed: false on every block is the honest state. These are derived from Square's published reference rather than signed off by anyone who has run them against your replica. A query using an unsigned metric still answers and carries a warning until a person approves it, which is the right behaviour for a declaration whose column names you still have to confirm.

Reproduce it yourself

Query 0 runs first, and everything else depends on it. The API field path is total_money.amount. How that becomes a column is your extract tool's choice, and Fivetran's schema ERD is a JavaScript shell with no table names in it, so this is discovery rather than ceremony:

-- What the money columns are actually called in your replica.
select table_name, column_name, data_type
from information_schema.columns
where lower(table_name) in ('payments', 'orders')
  and (lower(column_name) like '%money%' or lower(column_name) like '%amount%')
order by table_name, column_name;

Substitute the names it returns into everything below.

The query that measures your own gap. This is the one to run before you change anything, because it tells you the size of the problem on your data rather than on anyone else's:

-- How much of what you have been calling sales is gratuity.
select date_trunc('month', created_at) as month,
       sum(total_money_amount)         as if_you_used_total_money,
       sum(amount_money_amount)        as if_you_used_amount_money,
       sum(total_money_amount) - sum(amount_money_amount) as the_difference
from payments
where status = 'COMPLETED'
group by 1
order by 1;

the_difference is card gratuity, to the extent that tip_money is the only thing separating those two columns, which is what Square's Payment reference states and what you should confirm against your own rows. All three figures are in the base unit, so scale them by currency and not by a constant.

The query to use instead. It reports the two quantities separately rather than blending them, and it filters status:

-- Card sales and gratuity, kept apart, completed payments only.
select date_trunc('month', p.created_at)                             as month,
       sum(p.amount_money_amount)                                    as amount_ex_tip,
       sum(coalesce(p.tip_money_amount, 0))                          as tips,
       sum(p.amount_money_amount) - sum(coalesce(r.refund_total, 0))  as amount_net_of_refunds
from payments p
left join (select payment_id, sum(amount_money_amount) as refund_total
           from refunds
           group by payment_id) r
  on r.payment_id = p.id
where p.status = 'COMPLETED'
group by 1
order by 1;

If you don't have Square replicated yet, use the Square Sandbox. Square describes it as "a free, isolated server environment that allows developers to safely test Square APIs without affecting real data", with "an unlimited number of free Sandbox API calls". Seeding a handful of payments with and without tips is enough to reproduce the argument above.

Two limits to know first. Sandbox credentials "cannot be used with or accessed from the other" environment. And the Sandbox Dashboard "provides only a limited subset of the features available in the production Square Dashboard", so the Sandbox gives you the warehouse half of this argument and not the report half.

One connector limitation is worth carrying into any shift-level work. Fivetran states that "due to a limitation of the Cash Drawer Shifts API, Fivetran cannot capture the transition from OPEN to CLOSED for shifts that remain open for more than 48 hours".

What this looks like in Agami

Agami is a semantic model that sits between an agent and your warehouse, and on this problem it does four specific things.

• A money column is declared with what it includes. amount_money and total_money both land, both are correct, and only one of them answers a question about sales. The model records that distinction in Square's own words, so the agent picks on documented meaning rather than on name similarity.
• Gratuity is its own metric and can't be added to revenue. card_gratuity exists, is named, and is separate. Restoring Square's own waterfall boundary is the point, and it's a declaration rather than a convention somebody has to remember.
• A column can be excluded from a subject area without being deleted. total_money stays in the warehouse and leaves the revenue vocabulary, so the word sales can't resolve to it. The exclusion is a rule in the model rather than a line in a prompt, which is what makes it auditable: a persuasively worded question can't talk its way past it.
• An unsigned metric answers and says it's unsigned. Every block above carries reviewed: false, because nobody has run it against your replica. The answer arrives with that warning attached instead of arriving silently.

All of that lands in the assistant your team already opens, so the finance lead who asked the original question gets the governed answer in the same conversation rather than in a separate tool.

What we can't claim. We haven't measured a Square estate, so no figure in this post says what share of any seller's takings is gratuity, and the gap query above exists precisely because that number is yours and not ours. We also can't tell you whether your connector lands the Beta net_amounts and return_amounts blocks, because Square marks them Beta, no Fivetran dbt package exists for Square, and the connector's ERD isn't readable. And Square doesn't publish how orders.total_money is composed, so the argument here rests on payments, where the inclusion and exclusion of tip are each stated on the record.

Frequently asked questions

Is total_money a bug?

No. It's a correct column that answers a real question, which is how much was charged to the card in total. It's the wrong column for a question about sales, and the only problem is that its name is the closest match to the word a person asks with.

Can't I just subtract tip_money from total_money?

Usually, yes, and amount_money already is that subtraction according to Square's own description of the two fields. Reading the column Square documents as excluding tips is more robust than recomputing it, because it stays correct if the composition ever changes.

Why not use orders.total_money instead?

Because Square never publishes how it's composed. The Orders API documentation describes price adjustments as things the API "calculates and applies" without giving the arithmetic. The payments columns state their inclusions explicitly, which is why the argument rests there.

Does this apply to Square Online, Square for Restaurants and Square Invoices?

The Payment and Order objects are shared across Square's products, so the column semantics hold. What differs is which products a Sandbox can exercise, and Square lists exclusions for several of them.

Will the number then match my Square Dashboard exactly?

Not from these queries alone. The Dashboard's Total sales line adds taxes, gift card sales and additional amount refunds, and its Net total subtracts fees and withholdings. Getting to an exact match means declaring each of those lines, and the glossary's gross-versus-net circularity is a question for your finance team rather than for the schema.

References

• Square Payment object reference, for amount_money, total_money, tip_money and status
• Square Order object reference, for the order-level money columns and the Beta markers
• Square glossary and sales summary definitions
• Working with Monetary Amounts, for the base-unit rule
• Square Sandbox overview
• Fivetran Square connector documentation
• Square AI open beta announcement and Managerbot open beta
• How to Sum Shopify Revenue When Every Money Column Lands Twice, the opposite cause with the same destination: Shopify's money fields land twice, Square's land once and need scaling
• Ten Date Columns on a Toast Order, and Only One Knows When Friday Ended, the sibling question of when a restaurant sale belongs to a day
• agami-core on GitHub, the open-source semantic model and the metric and exclusion blocks shown above

Define revenue once, and let every question inherit it

Agami is the trust layer between your AI assistant and your warehouse. It records that amount_money excludes gratuity and total_money does not, keeps card_gratuity as its own metric that cannot be added to revenue, and carries the Dashboard's own definitions into every question about sales.

Start a free trial or talk to us →