Study guide

7.2 Audit Data: Relational Databases, Measurement Scales, Extraction & Reliability

AICPA CPA Exam — Auditing & Attestation AUD

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Orientation & the Auditing Regulatory Environment

1.1 CPA AUD Exam Structure, Scoring & Blueprint Strategy 1.2 Regulatory Authorities & Auditing Standards Hierarchy 1.3 Preconditions for an Audit & Engagement Letters (AU-C 210)

2 Chapter 2: Professional Ethics & the AICPA Code of Conduct

2.1 AICPA Code of Professional Conduct & Conceptual Framework 2.2 Independence Rules: AICPA vs. SEC and PCAOB Standards 2.3 Independence Under Government (GAGAS) & Employee Benefit (DOL) Standards 2.4 Professional Skepticism, Professional Judgment & Client Confidentiality

3 Chapter 3: Quality Management at Firm & Engagement Levels

3.1 Firm System of Quality Management: SQMS No. 1 3.2 Engagement Quality Reviews: SQMS No. 2 3.3 Engagement-Level Quality Management: SAS 146 & AU-C 220

4 Chapter 4: Audit Planning, Strategy & Materiality

4.1 Audit Planning & Overall Audit Strategy (AU-C 300) 4.2 Materiality, Performance Materiality & Tolerable Misstatement (AU-C 320 & 450) 4.3 The Audit Risk Model & Risk Assessment Mechanics

5 Chapter 5: Entity Understanding, Inherent Risk & Internal Control

5.1 Understanding the Entity, Environment & Inherent Risk Factors (SAS 145 / AU-C 315) 5.2 Economic Conditions, Industry Factors & SOX Corporate Governance 5.3 COSO 2013 Internal Control Framework: 5 Components & 17 Principles 5.4 IT General Controls (ITGCs) & Application Controls 5.5 Evaluating Design & Implementation of Controls

6 Chapter 6: Fraud Risk, Laws & Specialized Resources

6.1 Fraud Risk Assessment & Auditor Responses (AU-C 240) 6.2 Consideration of Laws, Regulations & Non-Compliance (AU-C 250 & NOCLAR) 6.3 Group Audits: AU-C 600 and the SAS No. 149 Transition 6.4 Using the Work of Internal Auditors (AU-C 610) & Specialists (AU-C 620)

7 Chapter 7: Audit Evidence Fundamentals & General Procedures

7.1 Audit Evidence: Sufficiency, Appropriateness & Management Assertions (AU-C 500) 7.2 Audit Data: Relational Databases, Measurement Scales, Extraction & Reliability 7.3 Types of Audit Procedures & Audit Data Analytics (ADA) 7.4 Substantive Analytical Procedures (AU-C 520)

8 Chapter 8: Audit Sampling Concepts & Attribute Testing

8.1 Audit Sampling Principles & Sampling Risk (AU-C 530) 8.2 Attribute Sampling for Tests of Controls 8.3 Non-Statistical Sampling & Dual-Purpose Testing

9 Chapter 9: Variables Sampling & Monetary Unit Sampling (MUS)

9.1 Classical Variables Sampling Methods (MPU, Ratio, Difference) 9.2 Monetary Unit Sampling (MUS / PPS) Mechanics & Sizing 9.3 Evaluating Variables Sampling Results & Upper Misstatement Limits

10 Chapter 10: Substantive Testing: Revenue, Receivables & Confirmations

10.1 Auditing the Revenue and Receivables Cycle & Cutoff 10.2 External Confirmations: Positive, Negative & Non-Responses (AU-C 505) 10.3 Allowance for Credit Losses (CECL) & Valuation of Receivables

11 Chapter 11: Substantive Testing: Purchasing, Inventory, Cash & Long-Term Assets

11.1 Purchasing, Accounts Payable & Search for Unrecorded Liabilities 11.2 Inventory Observation & Valuation (AU-C 501) 11.3 Auditing Cash, Bank Reconciliations & Interbank Transfers (Kiting) 11.4 Auditing Property, Plant & Equipment, Debt & Equity Cycles

12 Chapter 12: Complex Estimates, Related Parties & Concluding Evidence

12.1 Auditing Accounting Estimates & Fair Value (AU-C 540) 12.2 Investments in Securities, Derivatives & Equity-Method Investees 12.3 Related Parties & Significant Unusual Transactions (AU-C 550) 12.4 Litigation, Claims & Assessments and Legal Inquiry (AU-C 501 / ASC 450) 12.5 Subsequent Events & Subsequently Discovered Facts (AU-C 560)

13 Chapter 13: Final Evaluation, Written Representations & Documentation

13.1 Evaluating Audit Findings & Misstatement Accumulation (AU-C 450) 13.2 Going Concern Evaluation (AU-C 570) 13.3 Written Management Representations (AU-C 580) 13.4 Audit Documentation, Assembly & Retention (AU-C 230)

14 Chapter 14: The Standard Audit Report & Opinion Modifications

14.1 The Standard Unmodified Audit Report (AU-C 700) 14.2 Audit Report Modifications: Qualified, Adverse & Disclaimer (AU-C 705) 14.3 Emphasis-of-Matter & Other-Matter Paragraphs (AU-C 706) 14.4 Key Audit Matters (AU-C 701) & Critical Audit Matters (AS 3101)

15 Chapter 15: Comparative Statements, Other Information & PCAOB Reporting

15.1 Comparative Financial Statements & Predecessor Auditor Reporting 15.2 Other Information, Supplementary Information & Special Purpose Frameworks 15.3 Reviews of Interim Financial Information (AU-C 930 & PCAOB AS 4105) 15.4 PCAOB Reporting & Integrated Audits of ICFR (AS 3101 & AS 2201)

16 Chapter 16: SSARS Engagements: Preparation, Compilation & Review

16.1 The SSARS Framework & Hierarchy of Services (AR-C 60-90) 16.2 Preparation of Financial Statements (AR-C 70) 16.3 Compilation Engagements (AR-C 80) 16.4 Review of Financial Statements (AR-C 90)

17 Chapter 17: SSAE Attestation, SOC Reports & Government Audits

17.1 The SSAE Attestation Framework: Assertion-Based and Direct Examinations and Reviews 17.2 Agreed-Upon Procedures Engagements (AT-C 215, SSAE No. 19) 17.3 Service Organization Controls: SOC 1 & SOC 2 Engagements (AT-C 320) 17.4 Reporting on Compliance: AU-C 806, AT-C 315 & AU-C 935 17.5 Government Auditing Standards (Yellow Book / GAGAS) & Uniform Guidance Single Audits

7.2 Audit Data: Relational Databases, Measurement Scales, Extraction & Reliability

Key Takeaways Explain This Topic AI

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.

Last updated: September 2026

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.