Back to Study Material GATE Exam

Data Warehousing for GATE DA

A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data designed to support management decision making. These...

Data Warehousing for GATE DA

Data Warehouse Architecture and OLAP Concepts

A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data designed to support management decision making. These four characteristics, articulated by W.H. Inmon, distinguish a warehouse from an operational database. GATE DA tests both the conceptual architecture and the practical modeling techniques used in data warehousing.

Subject orientation means the warehouse is organized around major subjects such as sales, customers, or products rather than around application processes. Integration means data from heterogeneous sources is cleaned, transformed, and stored in a consistent format. Time variance means the warehouse stores historical data, allowing analysis of trends over time; operational databases typically store only current data. Non-volatility means data is loaded in bulk and queried but not updated transactionally.

The three-tier architecture of a data warehouse consists of the bottom tier (data sources and ETL), the middle tier (the warehouse server), and the top tier (front-end tools). ETL stands for Extract, Transform, Load: data is extracted from source systems, transformed to match the warehouse schema, and loaded into the warehouse. A data mart is a smaller, subject-specific subset of a warehouse that serves a particular business unit.

Online Analytical Processing (OLAP) supports multidimensional analysis of warehouse data. OLAP operations on a data cube: roll-up aggregates data to a higher level of the hierarchy (e.g., daily sales to monthly sales); drill-down disaggregates to a lower level; slice selects one dimension at a fixed value, reducing dimensionality by one; dice selects a range of values on multiple dimensions; pivot (rotate) reorients the cube for a different dimensional perspective.

ROLAP (Relational OLAP) stores data in a relational database and translates multidimensional queries into SQL. MOLAP (Multidimensional OLAP) stores data in a proprietary multidimensional array structure, enabling fast access but requiring precomputation. HOLAP (Hybrid OLAP) combines both approaches: detailed data stays in relational form while aggregates are stored multidimensionally.

Dimensional Modeling: Star Schema, Snowflake Schema, and Fact Tables

Dimensional modeling is the dominant technique for structuring data warehouse schemas. Developed by Ralph Kimball, it organizes data into facts (measurements) and dimensions (context). The resulting schemas are denormalized compared to OLTP schemas, trading storage space for query performance and business user understandability.

A fact table contains quantitative measures of business processes. Each row records a measurement event, such as a sales transaction. Fact table rows typically have many foreign keys referencing dimension tables and a small number of numeric measure columns such as quantity, revenue, and cost. Fact tables are generally very large (millions to billions of rows) and long and thin (few columns). Three types of facts: additive facts can be summed across all dimensions (revenue); semi-additive facts can be summed across some dimensions but not all (bank balance can be summed across customers but not meaningfully over time); non-additive facts cannot be summed at all (ratios, percentages).

A dimension table contains descriptive attributes that provide context for facts. A customer dimension might contain customer ID, name, address, age group, and region. Dimension tables are typically small (thousands to millions of rows) and wide (many columns). The primary key of each dimension table is the foreign key in the fact table.

A star schema has a central fact table connected directly to multiple dimension tables, forming a star shape. Queries are simple two-table joins between the fact table and one or more dimension tables. A snowflake schema normalizes dimension tables by splitting hierarchical data into separate tables. For example, a customer dimension might be split into customer and city and state tables. Snowflake schemas reduce storage but require more joins, potentially reducing query performance.

A slowly changing dimension (SCD) tracks how dimension attributes change over time. Type 1 SCD overwrites the old value with no history. Type 2 SCD adds a new row for each change, preserving full history with effective dates. Type 3 SCD adds a new column for the current value and keeps the old value in the original column, preserving one level of history. GATE DA questions on SCDs typically ask which type is appropriate for a described business requirement.

ETL Processes, Data Quality, and GATE-Focused Exam Tips for Warehousing

The ETL pipeline is the operational backbone of a data warehouse. Extract gathers data from source systems, which may include relational databases, flat files, XML feeds, APIs, and streaming data. Transform applies business rules, cleans data, handles missing values, standardizes formats, resolves inconsistencies, derives calculated fields, and maps source data to warehouse schema definitions. Load inserts the transformed data into the warehouse, either by truncating and reloading (full load) or by identifying and processing only changed records (incremental or delta load).

Data quality is a critical concern in ETL. Common data quality issues include missing values, duplicate records, inconsistent formats (e.g., dates stored as MM/DD/YYYY in one source and YYYY-MM-DD in another), invalid values (age = -5), and referential integrity violations (a foreign key with no matching primary key). Data profiling analyzes source data to understand its structure, content, and quality before ETL design. Data cleansing resolves identified issues through rules, lookups, or standardization procedures.

Surrogate keys are system-generated integer primary keys used in dimension tables instead of natural keys from source systems. They serve multiple purposes: they insulate the warehouse from changes in source system keys, they support slowly changing dimensions by allowing multiple rows for the same natural key entity, and they enable faster joins due to integer comparison versus string comparison.

Change data capture (CDC) identifies records that have changed in source systems since the last ETL run. Techniques include timestamp-based CDC (querying for records with a modified timestamp after the last ETL timestamp), trigger-based CDC (database triggers write changed rows to a staging table), and log-based CDC (reading the database transaction log). Log-based CDC is the most reliable and has the least impact on source systems.

GATE tip for data warehousing questions: the exam tests definitions, the four characteristics of a data warehouse, the difference between star and snowflake schemas, SCD types, and OLAP operations. Focus your revision on being able to correctly apply each OLAP operation to a described cube, identify the appropriate SCD type for a given scenario, and distinguish ROLAP, MOLAP, and HOLAP by their storage mechanism. These areas generate the most questions per revision hour invested.