Understanding large datasets for a compliance report

Understanding large datasets for a compliance report starts with three questions: what does each row represent, which records belong in the report, and what evidence supports the results? A large file is not necessarily complete or accurate just because it contains many rows.

Before calculating totals, establish the scope, check the meaning of the fields, and compare the data with the source system. That makes it easier to explain both your findings and any limits on them.

How a dataset becomes a compliance report

A dataset is a collection of records, often supplied as a CSV file. A CSV stores values as text in rows and columns. Each column has a field name, such as a customer ID, event date, status or region.

The important question is what one row represents. A row might be one customer, one transaction, one account status change or one daily snapshot. This is sometimes called the grain of the data. If a customer has several transactions, counting rows gives you the number of transactions, not the number of customers.

Ask the person who supplied the file for a description of the fields and how the export was created. In particular, find out:

• Scope. Which organizations, products, regions and reporting periods are included? Are any sources or records excluded?
• Row meaning. Does each row describe a person, an event, an account or something else? Can the same ID appear more than once for a valid reason?
• Field definitions. What do status codes mean, which fields are required, and are dates recorded in a specific time zone?
• Export details. When was the file created, and does it reflect current data or a snapshot taken earlier?

Write these answers down before you prepare the report. If the source cannot explain a field, mark its meaning as unconfirmed rather than guessing.

What the numbers mean

Rows and distinct IDs answer different questions

Suppose an export has 10,000 event rows for 8,000 distinct customer IDs. Both figures may be correct: some customers have more than one event. Neither number alone tells you how many customers had a particular outcome.

Repeated IDs are not automatically duplicates. Check whether the rows have different dates or event IDs before treating them as errors. If you need to investigate repeated records, see what to do with duplicate records in a compliance report.

Percentages need a clear denominator

If 500 of those 10,000 event rows have an unresolved status, that is 5% of event rows. It is not necessarily 5% of customers, because one customer may have several unresolved events. State what you counted and what you divided it by.

Also define which records are eligible for each measure. A percentage calculated from all events can differ from one calculated only from events that required review. Do not treat a blank status as resolved unless the field definition explicitly says to do so.

A date range needs boundaries

Agree on which date field determines inclusion. A record's creation date and completion date can put it in different reporting periods. A useful way to describe a January report is records dated on or after January 1 and before February 1, in the agreed time zone. This avoids confusion about events near midnight.

Check whether the export includes late corrections or records added after the reporting period. A file created in February may contain updates that were not present at the end of January.

Inspect your own CSV

1. Open the file in the CSV Editor. It can open CSV files with up to 10 million rows, although a very large file may still depend on the memory available on your device.
2. Read the column names and inspect several rows. Check that values appear under the expected headings and that IDs, dates and statuses look consistent with the field definitions.
3. Search for a known ID or event supplied by the source. Compare its fields with the source system or another trusted reference, if you have access.
4. Sort relevant columns to inspect groups of dates, IDs or statuses. Look for unexpected values, but check their meaning before changing or removing anything.
5. Record what you found and compare the file's coverage with the agreed scope. Keep the original export unchanged so you can return to the source evidence.

The editor works on the file in your browser without uploading it. Its search and sort features help you inspect records, but a few sample checks cannot prove that every row is correct.

Check coverage before drawing conclusions

Compare the export's row count and date coverage with the source system's export summary, if one is available. Investigate differences before reporting a total. A mismatch might be caused by a filter, an excluded region, a different reporting date or an incomplete export.

Check the fields needed for each measure. Missing dates can make it impossible to assign records to a period, while missing IDs can make a distinct customer count unreliable. The guide to finding empty values in a CSV explains how to investigate gaps.

For totals or grouped calculations across a very large file, use analysis software or a database that can handle the full dataset. Check the result against the source totals and make sure the entire file was included. A spreadsheet that opens only part of a file can produce a plausible but incomplete result.

Document the evidence and its limits

Keep a short record of the source, export date, reporting period, filters, field definitions and checks performed. For each reported number, note whether it counts rows, distinct IDs or another unit, along with the denominator used for any percentage.

Describe unresolved issues plainly. For example, say that a percentage excludes records with missing status values, or that one region was absent from the export. If a problem could change the conclusion, ask the data owner for a corrected export before finalizing the report.

Limit access to the original and working files to people who need them. Follow your organization's rules for handling personal information, retaining evidence and deleting temporary copies.