Practice data warehouse vocabulary: star schema, fact tables, dimension tables, grain, slowly changing dimensions (SCD), and surrogate keys.
0 / 17 completed
1 / 17
What is a 'star schema' in data warehousing?
A star schema has one central fact table (containing measurable events like transactions) connected to multiple dimension tables (like Date, Customer, Product). It's denormalized for query performance — fewer JOINs needed for analytics.
2 / 17
What is a 'fact table' in a data warehouse?
A fact table stores the measurable events of a business process — each row is typically one transaction, event, or occurrence. It contains foreign keys linking to dimension tables and numeric measures. Example: an Orders fact table with columns for date_key, customer_key, product_key, quantity, revenue.
3 / 17
What does 'the grain of this table is one row per transaction' mean?
'Grain' is the most important concept when designing a fact table — it defines exactly what one row represents. Stating the grain explicitly (e.g., 'one row per order line item') prevents ambiguity and ensures consistent aggregation in queries.
4 / 17
What is a Slowly Changing Dimension (SCD)?
An SCD handles dimension attributes that change over time. SCD Type 1 overwrites (no history). SCD Type 2 adds a new row with a new effective date (full history preserved). Type 2 is most common — it lets you analyze 'what was the customer's address at the time of the order.'
5 / 17
Why do data warehouses use 'surrogate keys' instead of natural keys from source systems?
Surrogate keys (typically auto-incremented integers) provide stability — if a source system changes its natural key format, the warehouse is unaffected. They also enable SCD Type 2 (multiple rows per entity with different keys), and integer JOINs are faster than string JOINs.
6 / 17
Reviewer: 'The PR introduces a new column, `customer_lifetime_value`, directly into the `Orders` fact table. This seems like a significant change to the schema without any clear justification for how this data will be used or aggregated. It feels like we're introducing potentially complex calculations at the lowest level of granularity.
What is the most appropriate response you should give to address this concern during a code review?
The correct answer emphasizes proactive questioning and suggesting a more appropriate data modeling approach. The reviewer's concern is valid: adding calculated fields directly into fact tables can lead to performance issues and makes querying complex. Options A and C are insufficient because they don't address the underlying design problem; option D attempts to circumvent the issue without understanding it, while option B directly seeks clarification and proposes a better solution – moving the calculated field to a dimension table for improved data management.
7 / 17
Reviewer: 'The PR introduces a new column, `customer_lifetime_value`, directly into the `Orders` fact table. This seems like a significant change to the schema without any clear justification for how this data will be used or aggregated. It feels like we're introducing potentially complex calculations at the lowest level of granularity.
What is the most appropriate response you should give to address this concern during a code review?
The correct answer emphasizes proactive questioning and suggesting a more appropriate data modeling approach. The reviewer's concern is valid: adding calculated fields directly into fact tables can lead to performance issues and makes querying complex. Options A and C are insufficient because they don't address the underlying design problem; option D attempts to circumvent the issue without understanding it, while option B directly seeks clarification and proposes a better solution – moving the calculated field to a dimension table for improved data management.
8 / 17
Reviewer: 'The PR introduces a new column, `customer_lifetime_value`, directly into the `Orders` fact table. This seems like a significant change to the schema without any clear justification for how this data will be used or aggregated. It feels like we're introducing potentially complex calculations at the lowest level of granularity.
What is the most appropriate response you should give to address this concern during a code review?
The correct answer emphasizes proactive questioning and suggesting a more appropriate data modeling approach. The reviewer's concern is valid: adding calculated fields directly into fact tables can lead to performance issues and makes querying complex. Options A and C are insufficient because they don't address the underlying design problem; option D attempts to circumvent the issue without understanding it, while option B directly seeks clarification and proposes a better solution – moving the calculated field to a dimension table for improved data management.
9 / 17
Reviewer: 'The PR introduces a new column, `customer_lifetime_value`, directly into the `Orders` fact table. This seems like a significant change to the schema without any clear justification for how this data will be used or aggregated. It feels like we're introducing potentially complex calculations at the lowest level of granularity.
What is the most appropriate response you should give to address this concern during a code review?
The correct answer emphasizes proactive questioning and suggesting a more appropriate data modeling approach. The reviewer's concern is valid: adding calculated fields directly into fact tables can lead to performance issues and makes querying complex. Options A and C are insufficient because they don't address the underlying design problem; option D attempts to circumvent the issue without understanding it, while option B directly seeks clarification and proposes a better solution – moving the calculated field to a dimension table for improved data management.
10 / 17
Reviewer Comment: 'The PR adds a `discount_amount` column to the `Sales` fact table. While this seems reasonable, can you elaborate on how frequently this data is updated and whether it's appropriate for a high-cardinality dimension like `Product'? It might be better suited as an attribute within the `Products` dimension.
This question tests understanding of dimension modeling principles. The reviewer raises a key concern – high cardinality in fact tables can lead to performance issues and increased storage costs. Option 2 reflects a common (though often debated) optimization technique; however, the core point is that the reviewer's observation about cardinality requires further consideration. Options A and D are incorrect assumptions, while option B misinterprets the review's intention.
11 / 17
Alex (Data Engineer): 'Just noticed a new ETL process pushing daily customer churn data directly into our `Customers` dimension. Seems like we're storing all historical churn events there. Shouldn't we be using an SCD type 2 to track changes over time?'
This scenario assesses knowledge of Slowly Changing Dimensions (SCDs). Alex correctly identifies that SCD Type 2 is appropriate for tracking changes over time. Option A is too absolute; SCD Type 2 isn't *always* the best choice, but it's often ideal when historical data needs to be preserved and modified. Options C and D misrepresent the situation.
12 / 17
The API endpoint /datawarehouse/reports/{report_id} returns the following JSON: {
"status": "success",
"data": {
"report_name": "Monthly Sales Report",
"metrics": [
{"product_id": 123, "sales_amount": 150},
{"product_id": 456, "sales_amount": 80}
]
}
}
This question tests understanding of the 'grain' concept. The JSON response shows individual sales transactions for each product – a single row per product represents the grain of this table. Options A and D are incorrect interpretations, while option B describes a related but not precise connection.
13 / 17
"Sarah (Data Analyst): 'I'm currently working on a report that needs to pull daily sales data from the data warehouse. The current schema with its multiple fact tables and dimensions is making it difficult to get the required metrics efficiently.'"
This scenario focuses on the practical challenges of working with complex schemas. Sarah's statement underscores the need for a well-designed data warehouse to facilitate reporting efficiently. Option A is an extreme reaction; option C minimizes the root cause, and option D suggests a tradeoff that's rarely appropriate in data warehousing.
14 / 17
Mark (Dev): 'Hey team, I'm building a new reporting dashboard that pulls data from the sales fact table. I need to join it with dimensions like `Products` and `Customers`. I'm wondering about the best way to ensure consistent naming conventions across these tables – should we standardize all dimension names?' What is Mark *really* asking for in this message?
Mark's question isn't simply about technical details; he's seeking advice on how to design the reporting dashboard effectively. He's highlighting concerns about joining data from potentially inconsistent dimension names – this points towards a need for careful schema design and standardization.
15 / 17
PR Description: 'This PR adds a new column, customer_lifetime_value, directly into the Orders fact table. This allows us to calculate and report on CLV for each customer at a daily level. We expect this data to be updated every hour.' What is the *primary* reason for adding this column to the fact table?
The PR description explicitly states that it's adding `customer_lifetime_value` to enable daily reporting. While storing metrics directly can sometimes improve query performance, the core justification here is the need for frequent updates and reporting on this metric – a key characteristic of data warehousing.
16 / 17
David (Data Engineer): 'Just finished implementing an ETL process that pushes daily customer churn data directly into our Customers dimension. It's a bit noisy, but it provides a more granular view of churn over time.' What is David *primarily* communicating in this update?
David's statement focuses on the *consequences* of the new ETL process – namely, that it's introducing 'noise' into the dimension. This indicates a potential issue with data quality or granularity and is a typical update shared during a stand-up meeting.
17 / 17
Reviewer: 'The PR introduces a new column, `customer_lifetime_value`, directly into the `Orders` fact table. This seems like a significant change to the schema without any clear justification for how this data will be us… What's the primary reason why storing CLV directly in a fact table is generally discouraged in a data warehouse design? This approach can lead to performance bottlenecks and redundancy, as CLV calculations are typically derived from dimension tables.
The core issue isn't about the *calculation* of CLV, but about where it's stored. Fact tables are designed for high-velocity transactional data; storing a derived metric like CLV directly adds complexity and potential redundancy. CLV is almost always calculated from dimension attributes (customer lifetime data) – keeping it in a dimension table allows for efficient querying and avoids the need to recalculate it every time.
What will I practice in "Data Warehouse Vocabulary"?
This is a BI Analytics Language exercise set. It walks through 17 scenario-based multiple-choice questions built around real usage of BI Analytics Language terminology that IT professionals encounter on the job.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free to complete with no account, sign-up, or paywall.
How many questions are in this exercise?
This set contains 17 questions. Each one shows immediate feedback and a detailed explanation after you answer, so you learn the correct usage right away rather than waiting for a final score.
Do I need prior experience to complete this exercise?
No prior experience is required. Each question includes a full explanation covering the reasoning behind the correct answer, so the exercise itself teaches the BI Analytics Language vocabulary as you go.
Can I retry the exercise if I get questions wrong?
Yes — use the "Try again" button on the results screen to reset your answers and go through all the questions again. There is no limit on attempts.
Is my progress saved?
Your answers and score for the current session are tracked in the browser as you go. No account or login is needed, and there is nothing to install.
What if I don't understand a term used in a question?
Read the explanation shown after you answer each question — it breaks down the correct term in plain English with a real-world example. You can also check the site Glossary for quick definitions.
How is this different from reading a blog article on the topic?
Exercises like this one are interactive drills that test and reinforce specific vocabulary through multiple-choice questions, while blog articles explain concepts in prose. Practising here after reading builds active recall, not just passive recognition.
Where can I find more BI Analytics Language exercises?
See the BI Analytics Language exercises hub for the full set of related pages, or browse all exercise categories from the main Exercises index.
Can I use this exercise to prepare for a technical interview?
Yes — BI Analytics Language vocabulary comes up often in technical discussions and interviews. Pair this exercise with our dedicated Interview Preparation section for role-specific practice.