In this video, we walk through 5 ISC practice questions on types of data storage and schemas. These questions are from ISC content area 1 on the AICPA CPA exam blueprints: Information Systems and Data Management.
The best way to use this video is to pause each time we get to a new question in the video, and then make your own attempt at the question before watching us go through it.
Types of Data Storage and Schemas
How a business stores its data shapes what it can do with that data. Some storage is built for consistent, reliable reporting, some for keeping everything cheaply until it’s needed, and some for giving one team quick access to just the data it uses. Within those systems, database schemas determine how tables are arranged and how they relate to one another.
Data Warehouse
A data warehouse is a large, centralized repository that brings data from many source systems together in one structured environment for reporting and analysis. Its purpose is to give the business a single, consistent source of information.
Data is cleaned, transformed, and integrated before it is stored. For example, a retailer pulling data from its point-of-sale system, inventory system, and online store would standardize product codes and customer IDs so records from all three systems match.
A warehouse also keeps data over many periods, which allows comparisons and trend analysis across years. And it is non-volatile, meaning data generally isn’t changed once it’s loaded. That keeps reports built from the warehouse stable and reliable.
Data Lake
A data lake stores large volumes of raw data in its original format until someone needs it. That data can be structured, like sensor readings in rows and columns; semi-structured, like server log files; or unstructured, like photos and documents.
The key difference from a warehouse is timing. A warehouse organizes data before storing it, while a lake applies structure only when the data is read for a specific analysis. That makes a lake flexible and inexpensive, especially when a company wants to keep data now and decide how to use it later. A manufacturer holding machine sensor data, technician photos, and system logs with no set plan for analysis is a good fit for a data lake.
The tradeoff is that without proper governance, a lake can become difficult to manage and query.
Data Mart
A data mart is a focused subset of data built for one department or business function, such as marketing, sales, or finance. It contains only the data relevant to that group.
Because a mart holds far less data, queries run faster and the system is easier to manage. A marketing team that struggles with slow reports against the company’s full dataset could be given a mart containing only campaign, customer, and website traffic data. A data mart can be built from a larger central repository or populated directly from source systems.
Star Schema
A star schema arranges tables around a central fact table connected directly to several dimension tables.
The fact table holds measurable, quantitative data, such as sales dollars and units sold. The dimension tables hold descriptive context for those numbers: who bought, what was bought, where, and when. A typical example is a Sales fact table linked to Customer, Product, Store, and Date tables.
In a star schema, all the descriptive information for a dimension sits in one table. The Product table would hold product name, category, and subcategory together. Since each dimension connects to the fact table through a single join, star schemas are simple and efficient to query, which makes them well suited for business intelligence reporting.
Snowflake Schema
A snowflake schema is a variation of the star schema in which dimension tables are normalized, meaning their descriptive data is broken into additional related tables. Rather than one Product table, the design might use a Product table linked to a Product Subcategory table, which links to a Product Category table.
Normalizing reduces redundant data. A category name is stored once instead of repeated on every product row. The cost is complexity, since retrieving the same information requires more joins. In practice, star schemas favor simplicity and query performance, while snowflake schemas favor reduced redundancy.
