An internal auditor reviews a financial reporting query and discovers it excludes transactions posted after the 25th of each month. The CFO has been making decisions based on incomplete data for two years. Data management failures rarely announce themselves. They surface when someone finally asks the right question.
AICPA Representative Tasks (verbatim). 1. Application, Identify data extraction methods and techniques. 2. Application, Define the various types of data storage (e.g., data warehouse, data lake, data mart) and database schemas (e.g., star, snowflake). 3. Application, Summarize the data life cycle (i.e., the span of the use of information, from creation, through active use, storage and final disposition). 4. Analysis, Examine a relational database's structure to determine whether it applies data integrity rules, uses a data dictionary, and normalizes the data. 5.
HIGH-FREQUENCY: The exam tests distinctions between data warehouses, data lakes, and data marts. Each serves different analytical needs.
Data Warehouse. A centralized repository of integrated data from multiple sources, transformed and organized for query and analysis. Data is cleaned, validated, and loaded through ETL (Extract, Transform, Load) processes.
Common mistakes
- Confusing data warehouses and data lakes. Warehouses store structured, cleansed data with predefined schemas. Data lakes store raw data in any format. The exam will describe a scenario: "raw sensor data stored for future analysis" is a data lake use case; "monthly sales aggregated for executive dashboards" is a warehouse use case.
- Ignoring schema type in performance analysis. Star schemas are optimized for query speed with fewer joins. Snowflake schemas save storage but require more joins. If a scenario describes slow query performance on a dimensional model with many normalized sub-tables, recognize the snowflake trade-off.
- Assuming normalization is always better. Normalization reduces redundancy but adds complexity and joins. Analytical databases often intentionally denormalize for performance. The correct design depends on the use case: OLTP systems favor normalization; OLAP systems tolerate denormalization.
Bottom line
- Data warehouses store structured historical data optimized for analysis; data lakes store raw data in any format; data marts are subject-specific subsets of warehouses.
- Star schemas use one fact table linked to denormalized dimensions; snowflake schemas normalize dimensions into sub-tables (star is faster for queries, snowflake saves storage).
- Data life cycle spans creation, active use, retention, archival, and disposition; each phase requires controls for integrity, security, and compliance.
- Normalization (1NF to 3NF) eliminates redundancy and update anomalies; a data dictionary documents structure, types, and business rules.
Exam shortcut
"Star or snowflake?" If dimension tables connect only to the fact table, it's star. If dimension tables link to other dimension tables (sub-dimensions), it's snowflake. SQL completeness check: Trace the WHERE clause first. A missing or overly restrictive filter is the most common cause of incomplete results. Then verify JOINs include all necessary tables and use the correct join type.
The full lesson (about 5,715 words, 38 min read) adds 10 worked examples, all 6 common mistakes, a self-check, free in the app.
Learning objectives
- I.B1
Browse all free CPA ISC lessons or jump into free CPA ISC practice questions.