A3.4 Alternative databases and data warehouses (HL only)

The city council wants long-term trend analysis and policy support — this is a classic data warehouse use case, not an operational (OLTP) task. The data must be preserved over time and queryable historically.

Non-volatile means data, once loaded, is never altered or deleted — guaranteeing the reliability of historical records. Time-variant means every record carries a timestamp, enabling analysts to query any historical period and compare trends across years.

For the city to analyse how traffic patterns or water usage changed over five years, the historical records must remain intact (non-volatile) and be queryable by date or period (time-variant). These two properties together enable the described analytical use case.

The option stating non-volatile and time-variant is correct because these are the Inmon properties that directly enable reliable long-term historical analysis — the stated goal.

The question asks which data warehouse properties best justify designing a unified analytical system for long-term trend analysis from diverse sensor sources.

Volatile is the opposite of the non-volatile property — data warehouses do not update or delete records. Real-time updates describe OLTP systems, not data warehouses. This option contradicts the warehouse model.

While integrated is a valid warehouse property (consolidating multiple sources), pairing it with volatile (records regularly corrected) again contradicts the fundamental non-volatile nature of a data warehouse. The option is internally inconsistent with warehouse design.

Schema-less is a characteristic of NoSQL systems, not data warehouses. While append-only behaviour is a consequence of non-volatility, it is not itself a formal defining property, and describing the solution as schema-less misidentifies the architecture.

Non-volatile and time-variant is the only option that correctly identifies formal Inmon properties and directly supports the requirement for reliable, historically queryable trend analysis.

The problem describes format inconsistencies — different currencies and date formats across source systems. These inconsistencies must be resolved before data enters the warehouse, so the relevant stage is Transform.

The Transform stage applies format standardisation (e.g., converting all dates to YYYY-MM-DD and all prices to a single currency), cleansing (fixing errors), filtering (removing duplicates), and validation (checking integrity constraints).

The Load stage appends already-cleaned data to the warehouse. It does not perform format conversion — that is the exclusive responsibility of Transform. Attempting to resolve conflicts at load time would compromise data integrity.

Transform is correct because format standardisation and validation are sub-steps performed in this middle stage of the ETL pipeline.

The question asks which ETL stage handles data format standardisation (currency and date harmonisation) before loading into the warehouse.

Extract simply pulls raw data from source systems. It makes no changes to formats, currencies, or encodings. The inconsistencies described are carried forward unchanged through the Extract stage.

Load appends pre-processed data to the warehouse. The warehouse schema does not automatically convert currencies or date formats — data must already be in the correct format before this stage. Placing format conversion at Load is a misconception.

Index is not one of the three ETL stages (Extract, Transform, Load). Indexing and partitioning are warehouse optimisation techniques applied to improve query performance, not to standardise source data formats.

Transform is the correct stage — it is specifically designed to standardise formats, cleanse errors, and validate data before it is loaded into the warehouse.