How to Remove Test Data and Junk Rows from a CSV

Someone hands you an export from the production database. You open it, check the row count: 48,000 rows. Great. Then you start scrolling and see this:

• Order ID: TEST-001, customer: Test User
• Email: test@test.com, revenue: $0.00
• Order ID: DEMO-2024-05, customer: Demo Account
• Email: admin@example.com, revenue: $99,999.00

Test orders. Demo accounts. QA entries. Placeholder transactions with $0 revenue. Internal accounts with inflated dollar amounts. They're all mixed in with real customer data, and they're wrecking your numbers.

Your average order value is wrong because of the $0 test transactions. Your revenue total is inflated by the $99,999 demo entries. Your customer count includes fake accounts. You need to get rid of all of it before you can trust any calculation.

Here's how to clean it up in ExploreMyData.

Identify the patterns

Test data usually follows predictable patterns. Before you start filtering, spend a minute figuring out what yours looks like. Sort by the order ID column - test IDs often cluster together alphabetically because they start with "TEST" or "DEMO". Sort by email to spot the @test.com and @example.com addresses. Sort by revenue to find the $0.00 entries at the bottom.

In most exports, test data falls into a few categories:

• Prefixed IDs: order IDs starting with "TEST", "DEMO", "QA", or "SANDBOX"
• Fake emails: addresses containing "test.com", "example.com", or "@yourcompany.com"
• Zero-value transactions: $0.00 revenue, 0 quantity
• Placeholder names: "Test User", "Jane Doe", "Foo Bar"
• Internal accounts: employee email domains, specific account IDs

Build your filter with the Condition Builder

Click the green + in the Pipeline panel and select Filter from the Filter & Sort group. Two things at the top of that panel matter here. One is the Condition Builder, where you combine conditions with AND/OR and nest groups. The other is the toggle above it: Keep matching rows or Remove matching rows.

Use Remove. Describing junk is much easier than describing its opposite: you list what test data looks like, join it with OR, and let the filter invert the whole thing for you. Switch to Remove matching rows, set the group conjunction to OR, and add:

1. order_id starts with TEST
2. order_id starts with DEMO
3. email contains @test.com
4. email contains @example.com

Click Apply. The generated SQL is a single WHERE clause with the whole OR group negated:

WHERE NOT (order_id LIKE 'TEST%' OR order_id LIKE 'DEMO%' OR email LIKE '%@test.com%' OR email LIKE '%@example.com%')

Why not just write the conditions in the negative and keep? Because the operator you'd reach for doesn't exist. The text operators are is, is not, starts with, ends with, does NOT start with, does NOT end with, contains, is Empty and is NOT Empty. There is no "does NOT contain". You can still build the keep version, you just have to phrase the email rules as suffixes:

WHERE order_id NOT LIKE 'TEST%' AND order_id NOT LIKE 'DEMO%' AND email NOT LIKE '%@test.com' AND email NOT LIKE '%@example.com'

That's "does NOT start with" twice and "does NOT end with" twice, joined with AND. Note the @ in the domain. Without it, "does NOT end with test.com" would also throw away anyone at protest.com.

The revenue rule needs a Convert Type first

You'll notice the revenue condition is missing from the list above. That's deliberate. In this export revenue reads $0.00 and $99,999.00, which makes it a text column, and the condition builder offers text operators for text columns. There is no != to pick.

Add Convert Type on revenue to numeric before the filter. It strips the dollar sign and the thousands separator on the way through, so "$0.00" becomes 0 and "$99,999.00" becomes 99999 in one step. The badge in the column header flips from T to #, the operator list becomes = != > < >= <= plus is Empty and is NOT Empty, and now you can add a second Filter step with revenue != 0.

Think twice before you keep that rule permanently, though. A zero-revenue row isn't always a test row: full refunds, comped orders and free-tier signups are all real. If your file has those, filter on the test patterns and leave the zeros alone.

Clean up duplicates while you're at it

Test data has a way of creating duplicates too. A QA engineer runs the same test scenario five times, generating five rows with slightly different timestamps but otherwise identical data. Even after filtering out the obvious test rows, you might have duplicates in the real data.

After your filter step, add Remove Duplicates from the Filter & Sort group. Choose the columns that define a unique record - maybe order_id alone, or a combination of email + order_date + product if order IDs aren't reliable.

This generates SELECT DISTINCT ON (order_id) * and keeps the first occurrence of each.

Mixing AND and OR

Patterns often overlap in ways one flat list can't express. Say a row is junk if the email contains "@test.com", OR the customer name is exactly "Test User", OR the order ID starts with "QA", but you also want to spare anything from before 2024 because that's when the sandbox was still shared with real traffic.

The Condition Builder nests. Build an AND group at the top with two children: an inner OR group holding the three junk patterns, and a date condition. Keep the filter on Remove matching rows and the negation applies to the whole tree:

WHERE NOT ((email LIKE '%@test.com%' OR customer_name = 'Test User' OR order_id LIKE 'QA%') AND order_date >= '2024-01-01')

The panel shows a plain-English summary of whatever you've built underneath the builder, which is worth reading before you apply. Nested logic is easy to get backwards.

Check your work

After filtering, do a quick sanity check. Sort by revenue descending - are there still suspiciously large values that look like test data? Sort by email - any remaining @yourcompany.com addresses that should be excluded? Check the row count: does the number make sense for the time period?

The pipeline makes this easy to iterate on. If you spot more junk, add another filter condition. If you removed too much, click into the filter step and adjust the conditions. Every change rebuilds the results instantly.

Make it a habit

If you get regular exports from the same system, the test data patterns will be the same every time. The order of operations is always: filter out junk first, then analyze. Fifteen minutes of cleaning saves hours of second-guessing numbers that look off because a demo account with $99,999 in revenue is hiding in the data.

Filter the junk out of your export →