100+ Free TDWI CBIP Practice Questions

Explanation: The grain declares exactly what a single fact table row represents, such as 'one row per order line item' or 'one row per customer per day.' Declaring the grain is the critical second step of Kimball's four-step dimensional design process. All facts in the table must be true to that declared grain.

Explanation: In a star schema each dimension is a single denormalized table joined directly to the central fact table, producing the characteristic star shape. A snowflake schema normalizes dimensions into hierarchies of related sub-tables. Star schemas are generally preferred in BI for simpler queries and better performance.

Explanation: SCD Type 2 inserts a new row for the changed attribute, typically using a new surrogate key plus effective/expiration dates or a current-row flag, preserving complete history. Type 1 overwrites the old value, and Type 3 adds a column for the prior value (limited history). Type 2 is the most common technique when full historical accuracy is required.

Explanation: Inmon's top-down approach builds a centralized, subject-oriented enterprise data warehouse in third normal form (3NF) to reduce redundancy and ensure integrity, then feeds dimensional data marts for reporting. This contrasts with Kimball's bottom-up dimensional (star schema) approach. The normalized core is the defining characteristic of the Inmon method.

Explanation: The transform stage applies business rules, cleansing, deduplication, type conversion, derivations, and conformance so the data fits the target schema and quality standards. Extract pulls data from sources and load writes it into the target. Transformation is where most of the data-integration logic and effort resides.

Explanation: In ELT, data is extracted and loaded into the target (often a cloud data warehouse or lake) in raw form, and transformations run inside the powerful target engine. This leverages scalable warehouse compute and is popular with platforms like Snowflake and BigQuery. Traditional ETL transforms data in a separate engine before loading.

Explanation: CDC detects inserts, updates, and deletes at the source and moves only those changed rows downstream, enabling efficient incremental and near-real-time integration. It avoids costly full reloads by capturing deltas, often via database transaction logs. This is essential for low-latency warehouse refreshes.

Explanation: Roll-up (also called drill-up) aggregates measures by moving up a dimension hierarchy, such as month to quarter to year, reducing detail. Drill-down does the reverse, exposing finer detail. Slice and pivot select subsets and reorient axes rather than change aggregation level.

Explanation: Predictive analytics uses statistical models and machine learning to forecast future outcomes and probabilities. Descriptive analytics explains what happened, diagnostic explains why, and prescriptive recommends what action to take. Predictive sits between describing the past and recommending action.

Explanation: An INNER JOIN returns only the rows where the join predicate matches in both tables, discarding non-matching rows from either side. A LEFT OUTER JOIN keeps all left rows, and a FULL OUTER JOIN keeps unmatched rows from both. INNER JOIN is the most restrictive of the common joins.