Data warehouse (DW) Interview Questions and Answers

A Data warehouse is the repository of a data and it is used for Management decision support system. Data warehouse consists of wide variety of data that has high level of business conditions at a single point in time. The goal is to produce statistical results that may help in decision makings. Data warehouses are used as centralized data repositories for analytical and reporting purposes.

• Dimension
• Conceptual Modal
• Logical model
• Physical model
• OLTP
• OLAP
• ETL
• ELT
• Star Schema
• Snowflakes schema
• Kimball approach
• Immon approach
• Data mart
• Enterprise data warehouse

There are four stages of Data warehousing:

• Offline Operational Database
• Offline Data Warehouse
• Real Time Data warehouse
• Integrated Data warehouse

Data warehouse use cases focus on providing high-level reporting and analysis that lead to more informed business decisions. Use cases include:

• Carrying out data mining to gain new insights from the information held in many large databases
• Conducting market research by analyzing large volumes of data in-depth
• An online business analyzing user behavior to make business decisions

Business Intelligence is also known as DSS – Decision support system which refers to the technologies, application and practices for the collection, integration and analysis of the business related information or data. Even, it helps to see the data on the information itself.

Dimension table is a table which contain attributes of measurements stored in fact tables. This table consists of hierarchies, categories and logic that can be used to traverse in nodes.

Fact table contains the measurement of business processes, and it contains foreign keys for the dimension tables.

Example – If the business process is manufacturing of bricks

Average number of bricks produced by one person/machine – measure of the business process

Data Mining is set to be a process of analyzing the data in different dimensions or perspectives and summarizing into useful information. Can be queried and retrieved the data from database in their own format.

Data mart is a subset of organizational data. In other words, it is a collection of data specific to a particular group within an organization.

OLTP (On-Line Transaction Processing) is an application that modifies the data whenever it received and has large number of simultaneous users.OLTP databases emphasize fast query processing and only deal with current data. Such data systems are used for capturing information for business processes, providing source data for the data warehouse.

OLAP (Online Analytical Processing)  is set to be a system which collects, manages, processes multi-dimensional data for analysis and management purposes. OLAP systems help to analyze the data in the data warehouse.

C

An ODS (Operational Data Store) is a database designed to integrate data from multiple sources for additional operations on the data. Unlike a master data store, the data is not sent back to operational systems. It may be passed for further operations and to the data warehouse for reporting.

In ODS, data can be scrubbed, resolved for redundancy and checked for compliance with the corresponding business rules. This data store can be used for integrating disparate data from multiple sources so that business operations, analysis and reporting can be carried while business operations occur. This is the place where most of the data used in current operation is housed before it’s transferred to the data warehouse for longer term storage or archiving.

An active data warehouse is a data warehouse that enables decision makers within a company or organization to manage customer relationships effectively and efficiently.

Data warehouse is a place where the whole data is stored for analyzing, but OLAP is used for analyzing the data, managing aggregations, information partitioning into minor level information.

ER diagram is abbreviated as Entity-Relationship diagram which illustrates the interrelationships between the entities in the database. This diagram shows the structure of each table and the links between the tables.

Foreign keys of dimension tables are primary keys of entity tables. Foreign keys of fact tables are the primary keys of the dimension tables.

SCD is defined as slowly changing dimensions and it applies to the cases where record changes over time.

Hybrid SCDs are a combination of both SCD 1 and SCD 2.

It may happen that in a table, some columns are important and we need to track changes for them i.e., capture the historical data for them whereas in some columns even if the data changes, we do not have to bother.

There are three types of SCD and they are as follows:

• SCD 1 – The new record replaces the original record
• SCD 2 – A new record is added to the existing customer dimension table
• SCD 3 – A original data is modified to include new data

BUS schema consists of suite of confirmed dimension and standardized definition if there is a fact tables.

Star schema is a schema used in data warehousing where a single fact table references a number of dimension tables. In a star schema, “keys” from all the dimension tables flow into the fact table. This entity-relationship diagram resembles a star, hence it is named a Star schema.

Snowflake schema which has primary dimension table to which one or more dimensions can be joined. The primary dimension table is the only table that can be joined with the fact table.

Core dimension is nothing but a Dimension table which is used as dedicated for single fact table or data mart.

ETL is abbreviated as Extract, Transform and Load. ETL is a software which is used to reads the data from the specified data source and extracts a desired subset of data. Next, it transforms the data using rules and lookup tables and convert it to a desired state.

Extract, Load, Transform (ELT) is a different approach to loading data. ELT takes the data from disparate sources and loads it directly into the target system, such as the data warehouse. The system then transforms the loaded data on-demand to enable analysis.

VLDB is abbreviated as Very Large Database and its size is set to be more than one terabyte database. These are decision support systems which is used to server large number of users.

Real-time data warehousing captures the business data whenever it occurs. When there is business activity gets completed, that data will be available in the flow and become available for use instantly.

Aggregate tables are the tables which contain the existing warehouse data which has been grouped to certain level of dimensions. It is easy to retrieve data from the aggregated tables than the original table which has more number of records.

This table reduces the load in the database server and increases the performance of the query.

A fact less fact tables is the fact table which doesn’t contain numeric fact column in the fact table.

Time dimensions are usually loaded through all possible dates in a year and it can be done through a program. Here, 100 years can be represented with one row per day.

Data warehouses use a different design patterns from standard operational databases systems. The latter are optimized to maintain strict accuracy of data in the moment by rapidly updating real-time data. Data warehouses, by contrast, are designed to give a long-range view of data over time. They trade off transaction volume and instead specialize in data aggregation.

• Clusters
• Nodes
• Partitions/Slices
• Columnar Storage
• Compression
• Data load

Non-Addictive facts are said to be facts that cannot be summed up for any of the dimensions present in the fact table. If there are changes in the dimensions, same facts can be useful.

Conformed fact is a table which can be used across multiple data marts in combined with the multiple fact tables.

A Data mart is a specialized version of Data warehousing and it contains a snapshot of operational data that helps the business people to decide with the analysis of past trends and experiences. A data mart helps to emphasizes on easy access to relevant information.

Data warehouses are expensive to scale, and do not excel at handling raw, unstructured, or complex data. However, data warehouses are still an important tool in the big data technology.

Cloud-based data warehouses are a big step forward from traditional architectures. However, users still face several challenges when setting them up:

Loading data to cloud data warehouses is non-trivial, and for large-scale data pipelines, it requires setting up, testing, and maintaining an ETL process. This part of the process is typically done with third-party tools.

Updates, upserts, and deletions can be tricky and must be done carefully to prevent degradation in query performance.

Semi-structured data is difficult to deal with – needs to be normalized into a relational database format, which requires automation for large data streams.

Nested structures are typically not supported in cloud data warehouses. You will need to flatten nested tables into a format the data warehouse can understand.

Optimizing cluster: There are different options for setting up a Red shift cluster to run your workloads. Different workloads, data sets, or even different types of queries might require a different setup. To stay optimal you’ll need to continually revisit and tweak your setup.

Query optimization: user queries may not follow best practices, and consequently will take much longer to run. You may find yourselves working with users or automated client applications to optimize queries so that the data warehouse can perform as expected.

Backup and recovery: while the data warehouse vendors provide numerous options for backing up your data, they are not trivial to set up and require monitoring and close attention.(Source: Data warehouse guide-Panoply)