🏛️ Data Warehouse Vocabulary
A colleague explains: "The star schema has a central fact table and four dimension tables."
What type of data does a fact table contain?
Fact table vs. Dimension table — the core star schema distinction:
| Table type | Contains | Example |
|---|---|---|
| Fact table | Events/transactions with numeric metrics + FK references | order_fact: order_id, customer_id FK, amount, quantity |
| Dimension table | Descriptive context (names, categories, attributes) | customer_dim: customer name, segment, region |
Star schema: 1 fact + multiple dimensions, no joins between dimensions. Snowflake schema normalises dimensions further. Key vocabulary: granularity, fact grain, degenerate dimension, surrogate key.
A data engineer says: "The team implemented SCD Type 2 for the customer dimension."
What does SCD Type 2 mean?
Slowly Changing Dimensions (SCD) — managing historical changes in dimension data:
| SCD Type | Strategy | History |
|---|---|---|
| Type 1 | Overwrite existing row | No history preserved |
| Type 2 | New row per change + effective_date/expiry_date | Full history, enables time travel queries |
| Type 3 | Add a "previous_value" column | Limited history (one previous state only) |
SCD2 vocabulary: surrogate key, current_flag, effective_date, expiry_date, historical snapshot.
An architect says: "This is an OLAP query — it scans billions of rows across 3 years of data."
How does OLAP usage differ from OLTP?
OLAP vs. OLTP — fundamental database workload distinction:
| Workload | Typical query | Optimised for | Examples |
|---|---|---|---|
| OLAP | SUM(revenue) GROUP BY month | Columnar storage, full table scans | Redshift, BigQuery, Snowflake |
| OLTP | SELECT * FROM orders WHERE id = 123 | Row-based, index lookups, low latency | PostgreSQL, MySQL |
Key vocabulary: columnar vs. row storage, analytical vs. transactional workload, data warehouse vs. operational DB.
A data engineer explains: "The warehouse uses partitioning on the event_date column."
What performance benefit does partitioning provide?
Partition pruning — the query optimizer skips irrelevant partitions entirely:
| Without partitioning | With date partitioning |
|---|---|
| Full table scan (e.g., 3 years of data) | Scans only the matching date partition |
| High cost, slow query | Dramatically lower cost and latency |
Example: WHERE event_date = '2025-01-01' on a date-partitioned table scans 1 day's data, not years. Key vocabulary: partition pruning, partitioning key, clustering vs. partitioning (BigQuery: partitioning = coarse-grained; clustering = fine-grained sort order within partitions).
An analyst says: "The data model uses a conformed dimension shared across multiple fact tables."
What is the advantage of a conformed dimension?
Conformed dimension = same dimension used consistently across marts:
| Conformed Date dimension | Used by |
|---|---|
| Single, shared definition of date attributes | Sales mart, Marketing mart, Finance mart |
Benefit: enables cross-mart analysis ("drill-across") with consistent metric definitions. Contrast with siloed dimensions: each mart owns its own customer definition → inconsistency. Key vocabulary: data mart, enterprise data bus, conformed fact, drill-across query.
PR Description: "Implemented a new ETL process to load daily sales data into the data warehouse. Utilizing Slowly Changing Dimension (SCD) Type 1 for product updates and incremental loading based on date."
During code review, your team lead asks: 'Can you elaborate on why we used SCD Type 1 here? What are the potential downsides?'SCD Type 1 is the most appropriate choice in this scenario because it focuses on maintaining a current snapshot of dimension attributes without capturing historical changes. The key advantage is simplicity and efficiency for situations where historical accuracy isn't paramount – a common use case when loading new data daily. Options A, B, and C misrepresent the core functionality or potential drawbacks of SCD Type 1; it doesn't guarantee audit trails or handle concurrent updates natively.// Code Review Comment
Your team lead is reviewing a recent PR and asks: 'Can you elaborate on why we used SCD Type 1 here? What are the potential downsides?' Consider this snippet of code as context:
```sql
INSERT INTO sales_fact (sale_id, product_key, customer_key, date_key, quantity, price)
VALUES (12345, 9876, 5432, '2023-10-26', 2, 19.99);
```
Which of the following best describes a key limitation of using SCD Type 1 in this scenario?PR Description: "Implemented a new ETL process to load daily sales data into the data warehouse. Utilizing Slowly Changing Dimension (SCD) Type 1 for product updates and incremental loading based on date."
During code review, your team lead asks: 'Can you elaborate on why we used SCD Type 1 here? What are the potential downsides?'SCD Type 1 is the most appropriate choice in this scenario because it focuses on maintaining a current snapshot of dimension attributes without capturing historical changes. The key advantage is simplicity and efficiency for situations where historical accuracy isn't paramount – a common use case when loading new data daily. Options A, B, and C misrepresent the core functionality or potential drawbacks of SCD Type 1; it doesn't guarantee audit trails or handle concurrent updates natively.// Code Review Comment
Your team lead is reviewing a recent PR and asks: 'Can you elaborate on why we used SCD Type 1 here? What are the potential downsides?' Consider this snippet of code as context:
```sql
INSERT INTO sales_fact (sale_id, product_key, customer_key, date_key, quantity, price)
VALUES (12345, 9876, 5432, '2023-10-26', 2, 19.99);
```
Which of the following best describes a key limitation of using SCD Type 1 in this scenario?PR Description: "Implemented a new ETL process to load daily sales data into the data warehouse. Utilizing Slowly Changing Dimension (SCD) Type 1 for product updates and incremental loading based on date."
During code review, your team lead asks: 'Can you elaborate on why we used SCD Type 1 here? What are the potential downsides?'SCD Type 1 is the most appropriate choice in this scenario because it focuses on maintaining a current snapshot of dimension attributes without capturing historical changes. The key advantage is simplicity and efficiency for situations where historical accuracy isn't paramount – a common use case when loading new data daily. Options A, B, and C misrepresent the core functionality or potential drawbacks of SCD Type 1; it doesn't guarantee audit trails or handle concurrent updates natively.// Code Review Comment
Your team lead is reviewing a recent PR and asks: 'Can you elaborate on why we used SCD Type 1 here? What are the potential downsides?' Consider this snippet of code as context:
```sql
INSERT INTO sales_fact (sale_id, product_key, customer_key, date_key, quantity, price)
VALUES (12345, 9876, 5432, '2023-10-26', 2, 19.99);
```
Which of the following best describes a key limitation of using SCD Type 1 in this scenario?PR Description: "Implemented a new ETL process to load daily sales data into the data warehouse. Utilizing Slowly Changing Dimension (SCD) Type 1 for product updates and incremental loading based on date."
During code review, your team lead asks: 'Can you elaborate on why we used SCD Type 1 here? What are the potential downsides?'SCD Type 1 is the most appropriate choice in this scenario because it focuses on maintaining a current snapshot of dimension attributes without capturing historical changes. The key advantage is simplicity and efficiency for situations where historical accuracy isn't paramount – a common use case when loading new data daily. Options A, B, and C misrepresent the core functionality or potential drawbacks of SCD Type 1; it doesn't guarantee audit trails or handle concurrent updates natively.// Code Review Comment
Your team lead is reviewing a recent PR and asks: 'Can you elaborate on why we used SCD Type 1 here? What are the potential downsides?' Consider this snippet of code as context:
```sql
INSERT INTO sales_fact (sale_id, product_key, customer_key, date_key, quantity, price)
VALUES (12345, 9876, 5432, '2023-10-26', 2, 19.99);
```
Which of the following best describes a key limitation of using SCD Type 1 in this scenario?Sarah is explaining the concept of a data warehouse to a new team member. She says: 'A data warehouse is like a giant spreadsheet that stores information from different systems.' Which of the following best describes this statement's primary purpose?
Sarah's statement is a useful simplification. A data warehouse is indeed built to store and organize data from various sources, which is its primary function. However, it's crucial to remember that data warehouses are designed for analysis, not just raw storage like a spreadsheet. The key difference is the historical aspect - data warehouses hold snapshots of information over time, enabling trend analysis.
Mark, a senior data engineer, is discussing SCD Type 1 with the team. He states: 'SCD Type 1 allows us to track changes to attributes but doesn't capture the history of those changes.' What does this statement primarily highlight about SCD Type 1?
SCD Type 1 is designed for simplicity. It adds a 'valid from' and 'valid to' column to represent changes to attributes like customer address. However, it *doesn't* retain the historical values of those changed attributes; once the 'valid to' date passes, the old value is overwritten with the new one. This is a deliberate design choice for performance reasons.
Your team lead is reviewing a recent PR and asks: 'Can you explain the rationale behind using a star schema here? What are the advantages over a snowflake schema?' Consider this data model snippet:-- Star Schema for Sales Data
Fact_Sales(SaleID, ProductID, CustomerID, DateID)
Dim_Product(ProductID, ProductName, Category)
Dim_Customer(CustomerID, CustomerName, City)
Dim_Date(DateID, Date)The team lead is probing for the *why* behind using a star schema. Star schemas are generally preferred for data warehousing due to their simplicity and optimized query performance—the fact table's direct relationships with dimension tables simplify analytical queries. The key advantage over a snowflake schema (which has normalized, many-to-many relationships) is faster aggregation and reporting.
'Hey @john_doe, just noticed this query in the data warehouse is running incredibly slowly. It's joining multiple tables with complex aggregations. Any thoughts on how we might optimize it?'
This is a realistic Slack message highlighting a performance issue. The query's slowness likely indicates a need for indexing, materialized views, or a more efficient query design. The key here is to start with diagnostics – understanding *why* the query is slow before jumping to solutions.
Frequently Asked Questions
What does the "Data Warehouse Vocabulary" exercise practise?
Practice English vocabulary for data warehouses: fact tables, dimension tables, SCD Type 2, OLAP vs OLTP, partitioning, and conformed dimensions for data engineers and analysts.
How many questions are in this exercise?
This exercise has 22 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Data Engineering Language category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "Data Warehouse Vocabulary" part of a larger series?
Yes — it's one exercise in the Data Engineering Language category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Data Engineering Language category page for related exercises, or browse the main Exercises hub for other IT English topics.