7.2 Audit Data: Relational Databases, Measurement Scales, Extraction & Reliability
Key Takeaways
- A primary key uniquely identifies each record in a table, while a foreign key stores another table's primary key to create the relationship between tables.
- Nominal data are unordered categories, ordinal data are ranked categories, interval data lack a true zero, and ratio data support all arithmetic including ratios.
- A data extraction request should specify the population, source tables, field formats, file layout, extraction parameters, and control totals to reconcile to the general ledger.
- Data cleaning steps should be documented, with record counts and totals reconciled after each major transformation so no records are dropped or duplicated.
- Under AU-C 500, information produced by the entity, including AI-prepared schedules, must be tested for accuracy and completeness before it is used as audit evidence.
7.2 Audit Data: Relational Databases, Measurement Scales, Extraction & Reliability
Blueprint Link: Area III.A.1 and III.A.2 expect you to explain relational database components and measurement scales, specify a data extraction request, choose methods to clean and transform data, and validate the reliability of data from internal and external sources, including schedules prepared with technology such as artificial intelligence. Analytics are only as good as the data behind them.
1. Relational Database Building Blocks
Most accounting systems store data in a relational database: a set of related tables.
| Term | Meaning | Example in a Sales System |
|---|---|---|
| Table | A collection of data about one type of thing | Customers, Invoices, Invoice Lines |
| Record (row) | One occurrence in a table | One customer, one invoice |
| Field (attribute, column) | One characteristic of each record | Credit limit, invoice date, amount |
| Primary key | Field (or fields) that uniquely identifies each record; no blanks, no duplicates | CustomerID in Customers; InvoiceID in Invoices |
| Foreign key | Field in one table that refers to the primary key of another table, creating the relationship | CustomerID stored in Invoices |
| Composite key | Primary key made of two or more fields | InvoiceID + LineNumber in Invoice Lines |
Normalization organizes tables to reduce redundancy and update errors. In first normal form each field holds a single value and there are no repeating groups; second normal form removes fields that depend on only part of a composite key; third normal form removes fields that depend on another non-key field (for example, storing a customer's city in every invoice record).
Audit use:
- Referential integrity tests: Invoices whose CustomerID does not exist in the Customers table ("orphan" records) point to data errors or possibly fictitious sales.
- Uniqueness tests: Duplicate primary keys or duplicate invoice numbers suggest double billing or broken controls.
- Joins: Linking Invoices to Customers lets the auditor find sales above credit limits or shipments to customers created days before year-end.
2. Measurement Scales
The scale of a field determines which analyses make sense.
| Scale | What It Measures | Audit Example | Meaningful Analyses |
|---|---|---|---|
| Nominal | Categories with no order | Account type, region, vendor ID | Counts, frequencies, mode |
| Ordinal | Ordered categories with unequal gaps | Risk rating (low, moderate, high), aging bucket labels | Ranking, median |
| Interval | Equal gaps but no true zero | Calendar dates, temperature in degrees Fahrenheit | Differences (for example, days between dates) |
| Ratio | Equal gaps and a true zero | Dollar amounts, units, days outstanding | All arithmetic, including ratios and averages |
Data can also be discrete (countable values, such as the number of invoices) or continuous (any value within a range, such as elapsed time).
Common traps: averaging vendor ID numbers (nominal data), treating a "3" risk rating as three times as risky as a "1" (ordinal data), or saying one date is "twice" another (interval data).
3. Planning a Data Extraction Request
A precise request prevents incomplete or unusable files. A good request specifies:
- Purpose and population: For example, all revenue journal lines for January 1 through December 31, 2026, including manual and system entries.
- Source system and tables: Which ERP module and tables the data come from.
- Fields and their structure: Field names, data types (text, number, date), lengths, formats (for example, dates as YYYY-MM-DD), and how credits and debits are signed.
- File format: Delimited text or CSV for large files, with a data dictionary describing each field.
- Control totals: Record counts and total amounts that the auditor will reconcile to the general ledger and trial balance.
- Extraction method: The query or report used, its parameters (date ranges, company codes, filters), who runs it, and whether the auditor observes or reruns it.
4. Preparing, Cleaning, and Transforming Data
Raw extracts rarely arrive ready for analysis. Transformation (often called extract, transform, load) should be planned and documented.
| Common Issue | Typical Fix | Risk If Ignored |
|---|---|---|
| Duplicate records | Identify duplicates on key fields and investigate before removing | Overstated totals or hidden double payments |
| Inconsistent formats | Standardize dates, vendor names, and number formats | Failed joins and missed matches |
| Blanks or nulls | Decide whether blanks mean zero, unknown, or an error | Misleading averages or excluded records |
| Numbers stored as text | Convert data types | Sums and filters that silently skip rows |
| Sign conventions | Apply consistent debit and credit signs | Offsetting errors that mask misstatements |
Keep an untouched copy of the original file, record every transformation step, and reconcile control totals after each major step so that cleaning does not drop or duplicate data.
5. Validating the Reliability of Data and Information
When the auditor uses information produced by the entity, AU-C 500 requires evaluating whether it is sufficiently reliable, including obtaining evidence about its accuracy and completeness and whether it is precise and detailed enough for the auditor's purpose. SAS No. 142 adds attention to authenticity and susceptibility to management bias.
- Completeness: Reconcile record counts and totals to the general ledger; trace items from source documents into the report.
- Accuracy: Agree a sample of report items to source documents; recompute calculated fields.
- Report and query logic: Inspect or rerun the query, check its parameters (dates, entity codes, filters), and consider general IT controls over the system that generated it.
- External data: Consider the source's reputation, independence, and the method used to compile the data (for example, industry statistics or market prices).
- Spreadsheets: Check formulas, links, hard-coded overrides, and version control.
- Schedules prepared with artificial intelligence: Understand what inputs and instructions produced the schedule, agree its amounts to source records, look for unsupported or invented items, and consider controls over how the tool is used. AI output is information produced by the entity, not evidence on its own.
Exam Trap: A client representation that "the report is complete" does not validate a report. The auditor needs procedures that test the data's completeness and accuracy.
In a client's sales database, each record in the Invoices table includes a CustomerID field whose values must match the CustomerID primary key in the Customers table. How is the CustomerID field in the Invoices table best described?
A client assigns each customer a credit risk rating of 1 (low), 2 (moderate), or 3 (high). Which statement about analyzing this field is appropriate?
An auditor receives a client-prepared file of all 2026 journal entries to use in journal entry testing. Which step best addresses whether the file is complete?
Client staff used a generative AI tool to prepare the year-end schedule of accrued liabilities and gave it to the auditor. How should the auditor treat the schedule?