DWH Interview Question and Answers

5. What is the difference between OLTP database and Data warehouse database?
  • Designed for real-time business transactions and processes.
  • Optimized for a common and known set of transactions, usually intensive.
  • nature; addition, updations and deletion of rows at a time, per table.
  • Designed for validation of data during transactions, heavily influenced by business rules and database constraints and entity relationships.
  • Supports few concurrent users relative to the OLTP environment
  • Houses very minimal historical data.
Data warehouse:
  • Designed for analysis of business measures by subject area, category and attributes.
  • Optimized for bulk loads and large complex, unpredictable queries that access many rows per table.
  • Designed to be loaded with consistent,valid data: uses very minimal validation routines but employs business empirical formulas for analytical purpose.
  • Supports large user bases often distributed across geographies
  • Houses a mix of most current information as well as historical data often regulated by the data purging and data retention strategies of the organization
Your Name Your Email-ID
Your Answer
6. What is the difference between OLTP and OLAP?
  • OLTP systems are for doing clerical/operational processing of data whereas OLAP systems are for carrying out analytical processing of the data.
  • OLTP systems look at data in one dimension; whereas in OLAP systems, data can be viewed in different dimensions and hence interesting business intelligence can be extracted from the data.
  • Operational personnel of an organization use the OLTP systems whereas management uses OLAP systems, though operational personnel may also use portions of OLAP system.
  • OLTP systems contain the current data as well as the details of the transactions.
  • OLAP systems contain historical data, and also data in summarized form.
  • OLTP database size is smaller as compared to OLAP systems. If the OLTP database occupies Gigabytes (GB) of storage space, OLAP database occupies Terabytes (TB) of storage space.
Your Name Your Email-ID
Your Answer
7. What are the types of data marts?
  Depending on the functionality, there are different nomenclatures for data warehouses/data marts.
  • Stand alone Data Marts: Data marts that do not interact with other data marts are called stand-alone data marts. On the other hand, data marts can be integrated to create a data warehouse.
  • Multi source Data Mart: A data mart for which the input data is obtained from multiple sources is called a multi-source data mart.
  • Personal Data Mart: A data mart for use by individuals such as Chief Executive Officer (CEO), Chief Technology Officer (CTO) or Chief Financial Officer (CFO) is called Personal Data Mart.
  • Operational Data Store: ODS is a database system that obtains data from different sources, consolidates it and keeps it at a single location. However, it does not have the sophistication of a data mart or a data warehouse for analytical processing. ODS is generally developed as a pre-cursor for ERP systems.
Your Name Your Email-ID
Your Answer
8. What is mean by ETL?
  The overall data acquisition process, called ETL (extraction, transformation and loading), is generally grouped into three main components:
  • Extraction: Involves obtaining the required data from the various sources.
  • Transformation: Source data undergoes a number of operations that pre-pare it for import into the data warehouse (target database). To perform this task, integration and transformation programs are used which can reformat, recalculate, modify structure and data elements, and add lime elements. They can also perform calculations, summarization, de-normalization, etc.
  • Loading: Involves physically placing extracted and transformed data in the target database. The initial loading involves a massive data import into the data warehouse. Subsequently, an extraction procedure periodically loads fresh data based on business rules and a pre determined frequency.
Your Name Your Email-ID
Your Answer
12345678910 Page 2 of 10