Question 1

Definition of derived data: Assess the statement. “Derived data are detailed, current data intended to be the single, authoritative source for all decision support applications.”

Accepted Answer

Introduction / Context: Derived data are created by transforming base (atomic) data: aggregations, calculations, rollups, and other summaries that support analytics. This statement incorrectly equates derived data with the “single authoritative source” of detailed, current data—roles more typically associated with an operational data store (ODS) or mastered operational layer. Given Data / Assumptions: Base/atomic data: detailed, low-level, often near real-time. Derived data: calculated or aggregated from base data. Authoritative sources are governed systems of record or curated ODS layers. Concept / Approach: Decision support relies on both detailed and derived layers. The authoritative source of truth for operational attributes is usually mastered atomic data; derived layers accelerate analytics by precomputing metrics like daily revenue, 30-day rolling averages, or cohort retention. Conflating these layers leads to confusion and may degrade traceability. Step-by-Step Solution: Identify atomic facts and dimensions as the authoritative, detailed layer.Define derived artifacts (summary tables, materialized views, semantic models) with lineage back to atomic data.Use governance to certify which derived data sets are “official” for reporting.Maintain refresh schedules so derived values stay timely, but recognize they are not the atomic source. Verification / Alternative check: Data catalogs typically distinguish “golden” atomic datasets from “certified” derived datasets, with lineage diagrams confirming dependency. Why Other Options Are Wrong: Calling the statement “Correct” misrepresents standard definitions. “ODS” and “CDC” are about integration/latency, not the meaning of “derived.” Lineage helps trust but does not change definitions. Common Pitfalls: Treating derived metrics as interchangeable with base facts; losing lineage; double-deriving metrics leading to reconciliation issues. Final Answer: Incorrect

Question 2

Warehouse refresh: Determine the correctness of this statement. “Data in the data warehouse are loaded and refreshed from operational systems.”

Accepted Answer

Introduction / Context: A data warehouse integrates data from multiple operational systems (OLTP and line-of-business applications) to support analytics. The loading and refreshing of warehouse data from those sources—batch, micro-batch, or streaming—is a core premise of DW/BI architecture. The statement asserts this common design. Given Data / Assumptions: Operational systems serve as systems of record for business events. Pipelines ingest, transform, and load data into warehouse layers. Refresh cadence may vary (daily, hourly, near real-time). Concept / Approach: The warehouse centralizes and standardizes cross-functional data to enable conformed metrics. Whether using ETL, ELT, CDC, or streaming, the flow originates from operational sources (plus external data). The warehouse is not the upstream producer; it is the curated consumer and integrator. Step-by-Step Solution: Identify source systems and required entities (orders, customers, products).Design ingestion and transformation (staging, quality checks, conformance).Load atomic and derived layers; publish semantic models.Schedule periodic or event-driven refreshes to keep analytics current. Verification / Alternative check: Architecture diagrams for Kimball/Inmon/lakehouse all depict sources feeding the warehouse via ingestion and transformation processes. Why Other Options Are Wrong: Restricting to batch or CDC is unnecessary—both are valid mechanisms among several. Streaming tools influence latency, not the correctness of the fundamental data flow. Common Pitfalls: Treating the warehouse as a write-back operational system; insufficient refresh governance leading to stale dashboards; missing conformance across sources. Final Answer: Correct

Question 3

Fact–dimension keys: Evaluate the best practice. “Every key used to join the fact table with a dimension table should be a surrogate key.”

Accepted Answer

Introduction / Context: In dimensional modeling (Kimball methodology), dimension tables typically use surrogate keys—integer identifiers generated in the warehouse—to decouple analytics from volatile, reused, or composite source keys. Facts then reference these surrogate keys. The statement claims that every fact–dimension join should use surrogate keys; we evaluate this as a best practice. Given Data / Assumptions: Dimensions may be slowly changing (SCD), requiring type 2 history. Source natural keys can change, collide, or be non-integer, harming performance and referential stability. Warehouse controls key management for consistency across sources. Concept / Approach: Surrogate keys allow multiple historical versions of a dimension row (same natural business key, different effective dates) while keeping facts bound to the correct historical context. They also improve join performance and enable conformance across heterogeneous systems. Exceptions (degenerate dimensions in facts) do not contradict the rule, as degenerate attributes do not require a separate dimension join. Step-by-Step Solution: Assign each dimension row a surrogate key on load.Lookup/bridge source natural keys to the current or correct historical surrogate during fact load.Store surrogate keys in fact tables; enforce referential integrity.Maintain change-tracking to support SCD behavior. Verification / Alternative check: Review SCD Type 2 examples: surrogate joins are mandatory to ensure historical accuracy independent of natural key changes. Why Other Options Are Wrong: “Incorrect” ignores the modeling and history advantages. Limiting to SCD or snowflake misses broader benefits (performance, conformance). Depending on source keys undermines stability and cross-source integration. Common Pitfalls: Embedding natural keys in facts; losing history when natural keys change; improper surrogate lookup causing late-arriving facts to mismatch. Final Answer: Correct

Question 4

Status data and event data: Assess the statement. “Both status data (state at a point in time) and event data (time-stamped occurrences) can be stored in a database.”

Accepted Answer

Introduction / Context: Databases hold many forms of data. “Event data” records discrete occurrences (for example, a purchase) with timestamps. “Status data” captures the current state (for example, account balance) or a snapshot. The question asks whether both can be stored in a database; the answer hinges on common modeling patterns rather than any platform limitation. Given Data / Assumptions: Relational, document, and time-series systems are all considered databases. Schemas can represent events (facts) and status (snapshots or slowly changing dimensions). Retention and performance requirements vary by use case. Concept / Approach: Event tables typically store one row per occurrence with a timestamp (and possibly an event type). Status can be modeled as the latest row in a history table, a separate current-state table maintained by upserts, or periodic snapshots. Many analytic architectures keep both: events for causality and sequence, status for quick point-in-time queries. Step-by-Step Solution: Define entities and business events (orders placed, shipped, returned).Create event fact tables (grain = one event) with timestamps and foreign keys.Model status as current-state tables or periodic snapshots, depending on needs.Use views or semantics to align events with derived status when necessary. Verification / Alternative check: Warehousing textbooks show event-centric facts plus status snapshots (for example, monthly inventory snapshots) in the same database, confirming feasibility. Why Other Options Are Wrong: Confining this to time-series or append-only databases is unnecessary. Eventual consistency relates to distributed systems behavior, not representational capability. Common Pitfalls: Confusing current-state overwrites with loss of history; mismatched grains between events and status leading to inaccurate joins; missing effective-dating for status changes. Final Answer: Correct

Question 5

Database operations terminology: Evaluate the definition. “Joining is the process of partitioning data according to predefined criteria.”

Accepted Answer

Introduction / Context: Accurate terminology matters in SQL and data engineering. “Join” and “partition” are distinct concepts. A join combines rows from two tables based on a related key or predicate. Partitioning divides a table’s data into segments (by range, hash, list) to improve manageability and performance. This statement conflates the two. Given Data / Assumptions: Standard relational databases and modern cloud warehouses are considered. We focus on logical definitions, not internal execution details. Predefined criteria could apply to either operation but with different outcomes. Concept / Approach: A join operation produces a result set by matching rows across tables (inner, left, right, full, cross, semi). Partitioning changes physical or logical storage layout without inherently combining rows. While some join algorithms use in-memory partitions (for example, hash join partitions input streams), this is an execution strategy, not the definition of “joining.” Step-by-Step Solution: Define join: SELECT ... FROM A JOIN B ON A.key = B.key ⇒ combines related rows.Define partitioning: CREATE/ALTER TABLE ... PARTITION BY ... ⇒ splits storage into segments.Assess the claim: it describes partitioning, not joining.Conclude the statement is incorrect. Verification / Alternative check: Query plans show joins producing combined rows; partitioning affects how data is scanned/pruned, not the relational semantics of combining tables. Why Other Options Are Wrong: “Correct” misdefines joining. References to hash joins or sharding confuse implementation with definition. Optimizer hints change plans, not definitions. Common Pitfalls: Assuming partitioning equals sharding equals distribution; believing partitioned tables “auto-join” faster without considering keys and statistics. Final Answer: Incorrect

Question 6

In data warehousing and OLTP contexts, most operational (day-to-day) information systems are designed around transient data that is updated in place rather than kept as historical snapshots. Is this statement accurate?

Accepted Answer

Introduction / Context: Operational systems (also called OLTP systems) support day-to-day business processes such as order entry, invoicing, and inventory adjustments. This question checks your understanding of the difference between transient data (updated in place) and periodic data (append-only history), and which one is typical for operational workloads. Given Data / Assumptions: Operational systems prioritize fast inserts/updates for current state. Historical trend analysis is not the primary goal; it is typically handled elsewhere (data warehouses or data lakes). Terminology: “transient data” = update-in-place; “periodic data” = never physically altered once stored. Concept / Approach: In OLTP design, rows commonly represent the current truth. When a customer’s address changes, the same record is updated (transient behavior). This keeps tables smaller and queries fast for operational users. In contrast, analytics needs history; data warehouses record changes as additional rows (periodic behavior) to enable time-series reporting. Understanding where each style fits improves schema design and ETL planning. Step-by-Step Solution: Identify the system type: operational vs. analytical.Map requirement: operational → current state queries and short transactions.Choose data behavior: update-in-place (transient) for operational consistency and performance.Route history needs to downstream warehouse/marts using periodic structures. Verification / Alternative check: Inspect audit trails: if the production tables show a single “current” record per business key and change history is kept in separate audit/warehouse tables, the system uses transient data in the core OLTP layer. Why Other Options Are Wrong: “Incorrect” conflicts with standard OLTP practice. “Applies only to data warehouses/analytical workloads” inverts the roles; warehouses favor periodic, not transient. “Depends exclusively on the file system” misunderstands that this is a modeling and workload choice, not a storage-format feature. Common Pitfalls: Expecting operational tables to hold long-term history, which bloats tables and slows transactions. Best practice is to offload history to analytic stores while keeping OLTP lean. Final Answer: Correct

Question 7

Data scrubbing (data cleansing) improves data quality in the short term; however, by itself it does not permanently solve systemic data quality problems. Do you agree with this statement?

Accepted Answer

Introduction / Context: Organizations often apply data scrubbing during ETL to standardize codes, remove duplicates, fix formats, and repair obvious errors. This question tests whether you understand the distinction between tactical cleansing and strategic data quality management at the source. Given Data / Assumptions: Cleansing is applied in staging/ETL before data lands in a warehouse or data mart. Root causes of poor data include process gaps, UI validation weaknesses, and unclear reference data. Goal: sustained, organization-wide data quality, not one-time fixes. Concept / Approach: Data scrubbing detects and corrects issues already present (for example, mapping “NY”, “N.Y.” to “NY”). While this raises quality for downstream analytics, it does not change the upstream processes that keep generating errors. Long-term solutions involve governing master/reference data, improving application validations, standardizing codes, and instituting stewardship and monitoring. Therefore, scrubbing is necessary but insufficient on its own. Step-by-Step Solution: Apply cleansing in ETL to remediate current defects.Profile data to identify recurring error patterns and sources.Address root causes in operational systems (UI rules, drop-downs, lookups).Implement governance: ownership, metrics, and continuous monitoring. Verification / Alternative check: Track defect recurrence after ETL cleansing. If the same anomalies reappear each load, cleansing alone has not solved the problem—source-level fixes are required. Why Other Options Are Wrong: “Incorrect because cleansing fixes source systems forever” is false—ETL does not reprogram upstream apps. Limiting correctness to specific tools or data types misunderstands that the principle is process-agnostic. Common Pitfalls: Relying on complex ETL mappings as a permanent crutch, which becomes costly and brittle. Always pair cleansing with upstream remediation and governance. Final Answer: Correct

Question 8

Independent data marts are frequently created when an organization pursues short-term, department-specific objectives instead of an enterprise data strategy. Is this characterization accurate?

Accepted Answer

Introduction / Context: Data mart architectures can be independent (built directly from operational systems) or dependent (sourced from a conformed enterprise data warehouse). This question probes why independent marts often appear in organizations. Given Data / Assumptions: Different departments have immediate reporting needs and budgets. Enterprise modeling and conformance require time and coordination. Independent marts trade speed for long-term integration. Concept / Approach: When business priorities are short-term, teams frequently spin up stand-alone marts to meet local KPIs fast. Without conformed dimensions and shared definitions, these marts drift, creating “spreadmarts” and inconsistent metrics across the enterprise. While they can deliver quick wins, they complicate cross-functional analytics later. Step-by-Step Solution: Identify departmental pain points prompting rapid delivery.Build an independent mart using ETL directly from sources.Observe rapid satisfaction of local needs but lack of enterprise conformance.Plan eventual harmonization or migration to a dependent/EDW-sourced model. Verification / Alternative check: Compare “Customer” metrics across finance and sales marts. If definitions or totals differ, the organization likely has independent marts created for short-term goals rather than a coordinated enterprise model. Why Other Options Are Wrong: Tying the phenomenon to cloud or schema type misses the governance driver. Claiming independent marts require enterprise models contradicts the definition. Common Pitfalls: Proliferation of conflicting truth sources, duplicate ETL, and higher maintenance. Establish a roadmap for conformance to regain consistency while preserving agility. Final Answer: Correct

Question 9

Extraction strategies: Is a static extract (full snapshot) the method typically used for ongoing, routine data warehouse maintenance, or is it mainly for initial loads/occasional snapshots?

Accepted Answer

Introduction / Context: Data warehouses are maintained via periodic loads. Two broad extraction styles are common: static (full snapshot) and incremental (delta/change data capture). This question checks whether static extracts are the norm for ongoing maintenance. Given Data / Assumptions: Ongoing loads must be efficient and minimize source/ETL impact. Large tables make repeated full snapshots expensive. Change data capture (CDC) or timestamp-based deltas are widely used for maintenance. Concept / Approach: Static extracts export all rows regardless of change, making them suitable for initial population or rare rebuilds. For routine cycles, incremental extracts move only new or changed rows using last_update timestamps, log-based CDC, or merge patterns. This reduces load times, storage, and contention on source systems. Step-by-Step Solution: Classify load: initial vs. maintenance.For maintenance, design delta logic (timestamps, sequence numbers, or logs).Implement upserts/soft-deletes in the warehouse according to change type.Reserve static extracts for first loads or special reconciliations. Verification / Alternative check: Measure daily ETL runtime. If full snapshots are used for large fact tables, run times spike and windows lengthen. Switching to deltas typically shortens the window and reduces resource use. Why Other Options Are Wrong: Calling the statement “Correct” conflates initial load with steady-state maintenance. Bandwidth alone does not determine the method; source impact and SLAs matter. CDC reference is misapplied; CDC is itself an incremental technique. Common Pitfalls: Trusting soft deletes without audit trails, or forgetting late-arriving facts and out-of-order updates. Always design idempotent, restartable delta loads with reconciliation checks. Final Answer: Incorrect

Question 10

Periodicity of data: Are periodic data physically altered after insertion, or are they append-only and never updated once written?

Accepted Answer

Introduction / Context: Warehouse designers distinguish between transient (update-in-place) and periodic (append-only) data. Understanding this difference guides fact table strategy, auditing, and slowly changing dimensions. Given Data / Assumptions: Periodic data captures history by adding new rows for changes. Once stored, prior records are preserved for time-based analysis. Updates occur by inserting a new version (and possibly closing the old one with an end date), not by altering the old row’s facts. Concept / Approach: The essence of periodic data is immutability. For example, a daily inventory snapshot records stock levels each day; yesterday’s counts are not changed today. This supports accurate time-series analysis, trending, and auditing. Therefore, saying periodic data are “physically altered” contradicts the definition. Step-by-Step Solution: Define business requirement for history retention.Model facts/dimensions with effective dating (start_date, end_date).On change, insert a new row and optionally expire the prior one.Prevent in-place updates to historical records to preserve lineage. Verification / Alternative check: Inspect ETL for INSERT-only behavior on historical tables and for integrity checks ensuring that existing historical rows are not overwritten. Why Other Options Are Wrong: Marking the statement “Correct” misdefines periodic data. Limiting the concept to specific schemas or platforms is unnecessary; periodicity is a modeling choice, not a technology constraint. Common Pitfalls: Mixing transient updates into periodic stores, which corrupts history, or failing to manage end dates and overlapping effective periods, which breaks point-in-time reporting. Final Answer: Incorrect

Data Warehousing Questions