SQL for Accountants in Tech Companies: Working with Large, Messy Data

“Maybe Friday,” said your data engineer when you asked for that Stripe report.

It’s now the second Friday.

“I’ve got marketing up my ass, Jim. I can’t get to the accounting stuff right now.”

You’re Jim.

You work at a tech company with about 50 people. There’s one person who knows how to get things out of the database, and it isn’t you.

The audit is around the corner. You’ve got three million rows of transactions and deferred revenue data to work through. Your usual Excel workflow isn’t going to cut it.

You could split the data across a bunch of files and stitch the results together. You’ve tried that before.

A row gets deleted. A formula doesn’t get dragged down far enough. An array formula breaks for reasons known only to Excel.

And then someone asks: “How do we know this includes everything?”

Great question.

You’ve got 20 CSV files Frankensteined together, and proving completeness has become a whole separate project.

I’ve been Jim. The broken formulas, the missing rows, the hours spent trying to make exports behave. I’ve been there.

And I get it. The data guy is busy. Marketing needs its reports. Everybody’s request is urgent.

But I still have an accounting job to finish.

Something had to change.

So I started learning enough SQL to get the data I needed, work with it, and check that my report actually made sense. I didn’t become a data engineer and quit my day job, but my day job got a little bit easier.

This series is about that: working with large, messy data when you’re the accountant who has to figure it out.

1. Database basics¶

Think about an Excel workbook with separate sheets for customers, invoices, and payments.

A database can hold those same things in separate tables. Each table has columns and rows, so when you first look at one, it probably won’t feel all that unfamiliar.

But when you download a report from your accounting or billing software, someone has usually already stitched the data together for you.

Take an invoice line-item report. You might get the item description and amount, the invoice number and date, and the customer’s name, all in one table.

Convenient. Everything you need in one place.

But some information repeats. An invoice with five lines might have the same customer name, invoice date, and invoice total repeated five times.

The database behind the software often keeps those things separate:

• Customers: one row per customer, with their ID, name, and other customer details.
• Invoices: one row per invoice, linked to the customer through their ID.
• Invoice lines: one row per line, linked to its invoice.

Why?

Imagine you’ve copied a customer’s name into 10,000 rows. Now you need to correct it. You update some rows, miss a few, and suddenly the same customer has three different names in your report.

Annoying I know. Been there, done that in Excel.

With this structure, you can correct the name in the customer record. The invoices still point to the same customer ID, and a report that pulls the current name from that record gets the correction too.

You store fewer repeated details, have fewer places to fix mistakes, and can connect new invoices or payments to an existing customer.

Exceptions exist, including tables built specifically for reporting. But this explains why the database you open might look very different from the export you’re used to.

What Stripe’s tables look like. There are a lot.

2. How tables connect¶

Each row represents a record, such as a customer or an invoice. The columns hold details about that record.

So how does the database know that a particular invoice belongs to a particular customer?

The invoice row stores the customer’s ID. The application records that connection when it creates the invoice, because it also needs to know who owes the money.

Say Acme has customer ID cus_123. Acme’s invoices each carry cus_123 in their customer ID column. That gives us something to match against the customers table.

Why use an ID instead of the customer’s name? Names can change. Two customers can have the same name. An ID gives the application a consistent way to identify the right record.

There should be one customer record for cus_123, but there could be hundreds of invoices referencing it. Each invoice has its own invoice ID too.

And how does an invoice line know which invoice it belongs to? It carries the invoice ID.

Which product was sold on that line? Another ID connects it to the product, sometimes through a separate price table.

Want to find every invoice line featuring that product? Follow those connections.

Customer IDs, invoice IDs, product IDs. And so on and so on.

That’s how the application keeps track of what belongs where. We can use those same connections to put our report together.

In SQL, combining tables using matching values is called a join.

3. Where SQL fits¶

And here comes SQL. Structured Query Language. Yes, I just looked that up.

SQL is a relatively straightforward computer language to get started with. It can also get very complicated, but we don’t need to start there.

You write instructions in a particular format, which we’ll go over, to tell the database what you want it to do.

Show me these rows. Add a new record. Update something. Delete something.

For our purposes, we’ll mostly start with SELECT, which lets us… select data as the name suggests. We can choose columns, filter rows, connect tables, and calculate totals.

For example:

SELECT id, status
FROM invoices
WHERE status = 'open'

• SELECT id, status: show only the id and status columns.
• FROM invoices: look in the invoices table.
• WHERE status = 'open': keep only invoices with an open status.

That’s it. We’re asking for a list, without changing the original data.

The basics come down to a fairly small set of building blocks. But you can combine those blocks to answer increasingly complicated questions.

“Show me this customer’s invoices” is one question.

“Show me monthly sales by product for customers who joined this year, excluding voided invoices. But only include customers who paid for three consecutive months, made no payment in their fourth month, and paid again in their fifth.”

That takes a few more steps. Same language.

Lots of applications use SQL behind the scenes. When you click a button to load a report, the software might be sending a query to a database for you.

We’re learning how to ask for the information ourselves.

4. When SQL helps¶

You don’t need millions of rows before SQL becomes useful. Sometimes you just need to stop doing the same annoying thing every month.

Here are a few situations where it can help.

Your spreadsheet starts struggling.¶

Maybe you’ve got 500,000 rows in Excel with formulas running across them. Maybe Google Sheets is super slow at 100,000 rows. Those aren’t official limits. How much your spreadsheet can handle depends on the number of rows, formulas, conditions, and other factors.

But when every calculation hangs up your file for 10, 30, or even 60 seconds, it’s a problem.

You’re stitching together a ridiculous number of files.¶

You have twelve monthly exports, except each month came in four parts. You need to combine them, clean them up, and make one report.

You could copy and paste everything together. You could also accidentally miss a file, paste one twice, or leave out the last thousand rows. SQL can help you combine those files and check what made it into the result.

And you don’t necessarily have to load everything into a database first. Some tools let you run SQL directly against CSV files. We’ll get to that in the examples.

The information you need is scattered across different reports.¶

Customer details are in one export. Invoices are in another. Payments are somewhere else.

You need one report that connects all three. Those IDs we just talked about become useful here. SQL lets you match the records and pull the information together.

You need lots of calculations across lots of rows.¶

Monthly totals by customer. Sales by product. Outstanding invoices grouped by how overdue they are.

Instead of dragging formulas down hundreds of thousands of rows or messing around with an array formula, you describe the calculation and how you want the results grouped.

You keep doing the same cleanup every month.¶

Fix the dates. Exclude test customers. Group the products. Calculate the totals.

Then next month, do it all again.

With SQL, you can save those instructions and run them on the next batch.

You need to find what’s wrong.¶

Duplicate transaction IDs. An invoice with no matching customer. A payment you can’t connect to an invoice.

SQL is useful for asking, “Show me the records that don’t make sense,” especially when there are too many to inspect manually.

You want to keep the source data intact and explain how you got your number.¶

For reporting, you can use queries that read the source data without changing it. You can filter out records from your result without deleting them from the original dataset.

That doesn’t automatically make your report complete or correct. You can still write a query that leaves out half the transactions or counts the same invoice five times.

But you can save the steps, review them, and run the same checks every time. It’s deterministic and easier to follow than trying to remember which rows you deleted last time.

5. When Excel is easier¶

Excel is still by far the best finance app ever created. And it should be your first go-to when possible.

If you’ve got a small dataset, a one-off question, or a model that needs lots of manual inputs and adjustments, Excel may still be the easiest place to work.

You don’t need to turn a 200-row expense analysis into a database project just because you learned SELECT. (But it might be a useful learning exercise.)

• A small, one-off analysis: Excel
• A model with manual inputs and scenarios: Excel
• Combining and cleaning recurring exports: SQL
• Filtering and summarizing a large dataset: SQL

You can also use both. Let SQL handle the heavy preparation, then bring the smaller result into Excel to review it, build a model, or present it.

6. Where AI helps¶

Yes. AI can make a lot of this easier. It can write SQL, work with Excel files, and produce incredibly complicated formulas without you ever reading them.

You can get useful results without understanding every step. You’re still obviously responsible for the output.

So why learn any of this?

Because you might not know what’s possible. Or what to ask for.

Learning a little, just a little, about databases and SQL gives you a better idea of how to bring data in, connect it, transform it, and get what you need. You can help decide how the work should be done instead of accepting whatever approach AI happens to take.

And once you have a process that works, you can save the queries and run them again. AI can help you build that process too. You don’t need it to reinvent the report every month.

You don’t have to write every line yourself. But you should know just enough to make sure you’re taking the right approach.

7. Choosing your database¶

There are lots of names floating around. MySQL, PostgreSQL, Microsoft SQL Server, BigQuery, Snowflake, Supabase, DuckDB. It can get confusing pretty quickly.

They aren’t all exactly the same kind of thing. MySQL, PostgreSQL, and SQL Server are database systems. Supabase is a platform that provides a hosted PostgreSQL database. BigQuery and Snowflake are built for large-scale data analysis.

You don’t need to become an expert in all the differences right now. At this stage, focus on what you need to do and what’s already available to you.

My first suggestion: find out what your company already uses. If the data you need is already in BigQuery or Snowflake, that’s probably where you should start.

PostgreSQL, usually called Postgres, is an established, widely used option. Supabase is a good place to consider if you want to work with Postgres.

DuckDB is another useful option, especially when you’re working with exported files. It runs on your own computer and lets you query CSV files directly with SQL, without setting up a cloud database.

Snowflake is useful for analytics, especially if your company already uses it. Costs depend on how you use it, so I wouldn’t call it automatically expensive, but it wouldn’t be my first suggestion just to learn a few queries.

If you’re doing this on your own and already work in Google Sheets, I’d suggest starting with BigQuery.

BigQuery is Google’s platform for analyzing large datasets. You can write and run SQL in your browser through BigQuery Studio, and for modest workloads, it can cost very little or nothing.

Another neat thing about BigQuery: you can import CSV files directly into it. So those exports you’ve been wrestling with in Excel can become tables you query with SQL. There may be some cleanup involved, especially with dates, column names, and mixed data types, but getting the files in is fairly straightforward.

FYI: Snowflake also makes importing CSV files pretty straightforward. If most of your work involves bringing in exports and querying them, it’s worth considering too. You might prefer its import interface to BigQuery’s.

One particularly useful feature for accountants is Connected Sheets. You can connect Google Sheets to BigQuery data, build pivot tables and charts, and refresh the results when you need them.

The larger dataset stays in BigQuery while you work with the results in a familiar spreadsheet.

We’ll cover setup and practical examples in later articles. For now, you just need to know what these tools are and why you might use them.

8. Getting data in¶

A few different ways.

The application puts it there.

When someone creates a customer, issues an invoice, or records a payment, the application saves that information. The engineering team built the instructions that tell it what to store and where.

So if you’re working with your company’s application data, it already lives somewhere. Your team may also copy it into a separate database for reporting.

You can import files yourself.

Those CSV exports can become database tables. This is useful when you have files to work with and don’t need an ongoing connection to the original application.

But an imported file is a snapshot. If something changes in the app tomorrow, your imported data won’t know about it.

You can use a tool to bring the data over regularly.

You’ll hear these called ETL or ELT tools. The letters stand for extract, transform, and load, with the order changing depending on whether you transform the data before or after loading it.

Some of these tools are loved. Some are hated. Some are both.

I’ve personally used Fivetran because I found it easier to figure out.

It has prebuilt connectors for applications like Stripe, NetSuite, QuickBooks, Xero, Ramp, and many other finance apps. A connector already knows how to talk to the application, retrieve the supported data, and organize it into tables in your reporting database. You don’t have to build that connection yourself.

For example, a Stripe connector can bring customer, invoice, and payment-related data into a platform like BigQuery or Snowflake, where you can query it with SQL.

You give it access, choose the destination, and configure the available table selections and sync schedule. It brings over the initial data, then regularly checks for updates. Exactly what it can retrieve and how it handles changes depends on the connector.

That saves you from downloading and importing the same exports over and over.

It doesn’t mean your accounting report is finished, though. The connector knows how to move the data. It doesn’t automatically know how you define revenue, which transactions you need to exclude, or what your reconciliation should look like.

Now the data is somewhere you can work with it. The next step is turning it into something useful.

9. Meet Clack¶

Imagine you work for Clack, an up-and-coming workplace chat company with the inspiring slogan:

“Exactly like Slack, but better.”

You’ve just joined as controller. No accounting team yet. No budget to hire one.

No big deal. You got good stock options.

The Canadian revenue report looks fantastic. Revenue has roughly tripled. Clack recently signed Canadian medical companies paying extra for specialized Canada-hosted service, so there’s a reasonable explanation.

Then you recognize a few customers in the report.

Aren’t those guys in San Francisco?

You have invoices, customer details, and a report that suddenly needs explaining. Finding out why starts with a few manageable questions: which invoices are included, what information puts them in Canada, and what did we actually charge?

In the next article, we’ll open the example files, run your first SQL query, and start investigating alongside Pixel Jim.