Shared Credit Card Data Solution

View the dbt documentation for model lineage and detailed technical information.

The point of this data solution

“If money makes the world go ’round, why does it also often wreak havoc with our personal worlds, spinning them up and down, topsy-turvy, even totally out of orbit?”

— The New Yorker Book of Money Cartoons: The Influence, Power, and Occasional Insanity of Money in All of Our Lives, edited by Robert Mankoff ¹

A data solution neither solves nor prevents financial rifts in a relationship, but it can alleviate quite a bit of the tension.

My partner and I wanted a way to know how much each of us needed to pay our shared credit card each statement. He wanted something that was incredibly simple to use with minimal opportunity for human error, and I wanted the ability to verify our assumed responsibility of the statement totals, given that we wanted to split the bill for some transactions, but not all. I built this data solution with those core desires in mind.

I first tried doing the process “by hand” (through an Excel spreadsheet) but the process was both time consuming and prone to human error. Now, the only manual steps are ones that we want to remain manual: dropping the transaction download in a GCP bucket, and identifying which transactions should be split and by what percentage. The transformation process that takes care of splitting the bill is automated, and the Claude connection to BigQuery provides us with conversational analytics, so we can easily ask questions about our statements.

Summarized data pipeline

Governance Guidelines

• Data privacy: All infrastructure stays within my personal GCP project; there are no third-party financial aggregators.
• Spend limits: BigQuery has a daily query quota set to prevent unexpected costs, and queries are capped at 1 GB billed. At this data volume, the whole pipeline stays within BigQuery’s free tier.
• Access: a dedicated service account with least privilege: roles/bigquery.dataEditor and roles/bigquery.jobUser
• Decision rights: The Google Sheet is the only spot for User input, guarded by dbt data tests. All computed metrics come from dbt and are evaluated with data tests.
• Raw data is unchanged: the raw layer is append-only. All transformation logic is in version-controlled dbt models.
• Conversational Analytics: An agent (Claude) can only read the mart layer. Metric definitions and grain details are written to the database tables themselves.

About the pipeline

Grain changes through the pipeline

At the start of the ingestion process, each row represents a transaction as defined by the bank, whether that be a purchase, credit card payment, or credit from a vendor (when an item is returned). The staging layer creates a transaction_id based on the following:

• transaction date
• card number of the user who made the purchase
• description, which serves as a proxy for merchant
• amount in dollars of the transaction
• occurrence: the number of times an identical charge (same card, merchant, and amount) appears on a given day

In order for us to have actionable data, we needed the grain to aggregate transactions to the statement grain per card user. I created a statement_id that is a hash of the statement’s starting date and ending date. The statement_id and card_owner are used to generate the final grain’s primary key.

bank transaction → split charge → statement → statement × owner

Statement start & end dates

Statement start and end dates are empirically computed for several reasons:

• Statement dates are specific per account holder; there is not a set definition for when a statement opens/closes.
• The logic for how statement dates are chosen by the bank is obscured.

Each uploaded file is bounded by one statement. By taking the minimum date of the file, we are able to empirically identify the start date of the statement. The maximum posted date thus represents the ending date of the statement. A hash of these two dates is used to create the statement_id in the model. This id is recomputed each time the pipeline runs, so I am not concerned about drift between computed statement dates and the actual statement dates: if a file is uploaded with a date outside of the previously computed range, that range will recompute with a new pipeline run.

The statement ids, start and end dates, and the statement’s previous id are all modeled in an intermediate “date spine” layer so that statements can be joined to with ease.

What happens when you upload a file?

After the .csv files are dropped into the bucket, the external table (raw.transactions) is able to read the new records. The only part of the pipeline that reads the external table is stg_transactions.

Thus, in order for a new .csv upload to be recognized in the Google Sheet, the stg_transactions model must be run first so that the transaction_id can be created. That id is used to deduplicate records in the case that the same files or transactions are uploaded more than once. However, this only works in the case that the uploaded files have the same file name. Later work will resolve the issue of duplicate transactions between different files.

Once the staging model runs, the Apps Script is able to query against stg_transactions to determine what records are missing from the expense-review spreadsheet; if a transaction_id exists in the staging table but not in the spreadsheet, the Apps Script will add said record to the spreadsheet.

The transaction review process

We wanted to manually review transactions to determine if they should be split between the two of us. The easiest way for the manual review of this process was for us to identify transactions in a Google Sheet; we are both very familiar with the interface. To keep that simplicity, the pipeline first populates the expense-review Google Sheet with the transactions dropped into the bucket. Then, we are able to go through those transactions and define the split percentage in the sheet.

The dbt pipeline is able to run even if the transactions are not yet classified as split/not split; in this case, each user will pay for whatever they charged on their card. Once the transactions are classified, then each user’s dues will represent the adjusted split. In both cases, the sum of both users’ credit card transactions will equal the statement.

How transactions are split

For example, if a grocery bill of \(\$200\) needs to be split 50/50, then the transaction grain at the intermediate level will have one row per user who is paying something toward that transaction.

User	Transaction ID	Amount
User1	111	\(\$100\)
User2	111	\(\$100\)

The sum of the dollar amounts will equal the total dollar charge of the original transaction.

Thus, the grain becomes one transaction per user who is accountable for a part of the transaction. The intermediate table responsible for the transformation has its own primary key for this grain, and there are foreign key references transaction_id and statement_id.

The split percentage determines how much each user should pay for a given transaction.

User	is_shared	split_percentage	Interpretation
User1	TRUE	0.2	User1 should pay 20% of the transaction; User2 pays 80% of the transaction
User1	TRUE	1	User1 pays the entire transaction (same impact as is_shared == FALSE)
User1	TRUE	0	User1 pays none of the transaction; User2 pays 100% of the transaction

Setting is_shared to TRUE and setting the split_percentage to 1 has the same effect as setting is_shared to FALSE.

Determining how much was paid per statement

There is a delay between the statement transactions and their corresponding payments. Payments appear in the next statement, so determining how much each individual paid toward a statement bill requires attributing a payment toward the previous statement.

That said, the more important total for us is the rolled up net total of charges, payments, and credits. This total will line up with our current statement.

Data Trust

Reconciling to the Statement pdf

In order to reconcile the data solution’s logic with the generated statement from the bank, I set up a quick way to compare the .pdf statement to the statement total as suggested by the data solution. First, I set up a BigQuery Claude connection and connected it to the mart layer of the project. Then, I give Claude the statement .pdf and ask Claude to compare the statement total on the .pdf to the modeled statement total. If there are any discrepancies, Claude is able to identify where the data mismatch is.

And for the sake of saying it, of course we manually verify that the data solution’s statement total equals the actual statement total, and that our payments combined pay off the bill in full.

Validating with data tests

There are several data tests that help maintain our assertions:

• The ratio for the percentage split for a transaction is bounded by 0 and 1 inclusive
• The sum of split transactions will always equal their original transactions
• not null and unique keys on the primary keys

Sources:

Footnotes

1. Mankoff, R. (Ed.). (1999). The New Yorker book of money cartoons: The influence, power, and occasional insanity of money in all of our lives. Nicholas Brealey.