Data warehouse interview questions and answers
Last updated on Tue 17 Mar 2020
The interviews questions and answers on data warehouse are presented in this article. Students need not refer many books before facing an interview because these questions are collected from different sources and kept at one place to make convenient for the students. The candidate who has a strong knowledge technically clears the interview easily. Students can make use of this article and revise all the concepts thoroughly.
- Define Data warehouse?
The father of Data warehousing is Bill Inmon. A Data warehouse is a subject oriented, time, and non-volatile collection of data in support of decision making process of management.
- Give different types of data warehouses?
- Data Mart
- Operational Data Store
- Enterprise Data Warehouse
- Define start schema?
The simplest form of schema of a data warehouse that contain fact tables and one or more dimensional is known as a star schema.
- Define Data mart?
The subset of data warehouse that is designed for specific line of business like marketing, sales or finance is known as Data mart. Data is derived from an enterprise wide data warehouse in a data mart which is dependent. Data is directly collected from sources in an independent data mart.
- Define Snowflake schema?
Snowflake schema is a Fact table that is connected to various dimension tables, the star and snowflake schema are the methods used for storing data which are multidimensional in nature.
- Define Data Modeling?
Data Modeling is represented in the real world set of entities or data structures and their relationship in their data models that are essential for a database. There are various types of data types, they are:
- Logical data modeling
- Conceptual data modeling
- Dimensional data modeling
- Enterprise data modeling
- Physical data modeling
- Relation data modeling
- Define ETL Tools?
ETL tools are stands for transformation, extraction and loading data into data warehouse for making decision. ETL refers to methods involved in gaining access and operating source data and loading to target database.
- Define dimensional table?
Attributes that depict fact records in fact table are present in Dimension tables.
- Define Surrogate key?
The substitution for primary key is Surrogate key. It is a unique identifier or number of every row that is used for primary key of table.
- Define Data Mining?
The process in which data is analyzed from various perspectives and abbreviating into useful information is known as Data Mining.
- Give different types of dimensions in Informatica?
There are three different types of dimensions available in informatica, they are:
- Degenerative Dimension
- Junk Dimension and
- Conformed Dimension
- Define operational data store?
It is the second layer in the architecture of data warehouse. Both the characteristics of DSS and OLTP systems are included in it.
- Differentiate ER Modeling and Dimensional Modeling?
To normalize OLTP database design, ER Modeling is used whereas de-normalizing MOLAP or ROLAP design uses Dimensional modeling.
- Define Maplet?
The set of transformations that are built in Maplet designer and can be used in different mappings is known as a Maplet.
- Define Session and Batches?
The set of commands which are used to describe server to transfer data to the target is known as a Session.
A set of tasks that includes one or more number of tasks is known as a Batch. Email, wait, command, sessions are the examples.
- Describe two different modes of data movement in the Informatica Server?
There are two different modes of data movements, they are:
The mode in which each record is individual DML statement is prepared and executed is known as a Normal mode.
The mode in which multiple records of DML statement is prepared and executed thereby increasing performance.
- Give four output files that informs server created during session running?
- Workflow Log
- Session Log
- Errors Log
- What is the usage of store procedure transformation?
The essential tool for populating and maintaining databases is known as stored procedure transformation.
- Differentiate static and dynamic cache?
Dynamic cache reduces performance when it is compared to static cache. Such things are not seen by static cache and inserts data as many times as it comes.
- Give the command that is used to run Batch?
Pmcmd is the command used to start batch.
- Define parameter file?
Text editor is used to create parameter file such as notepad or word pad. In the parameter file, the following values can be defined:
- Mapping variables
- Mapping parameters and
- Session parameters
- Give different types of lookup caches?
- Persistent cache
- Re cache
- Shared cache
- Static cache
- Dynamic cache
- Define fact table?
In the start schema, the centralized table is known as fact table. There are three different types of fact tables, they are:
- Semi additive
- Additive and
- What are the types of dimensions available in informatica?
- Snowflake schema
- Star schema
- Galaxy schema
- Define Aggregate Awareness?
The feature of designer is aggregate awareness that use aggregate tables in database. Pre-calculated data is present in these tables. The main purpose of these tables is to improve performance of SQL transactions. Thus, it used to speed up queries execution.
- Differentiate OLTP and OLAP?
The full form of OLTP is online transaction processing which includes normalized tables whereas the full form of OLAP is online analytical programming which includes history of OLTP data and it is non-volatile and acts as Decision Support System.
- Mainly, it concentrates on day to day transaction
- Data stability
- Frequency of gaining access is high
- Mainly, it concentrates on the coming decisions and predictions.
- It is a medium to low
- Replicated and demoralized data
- It is static till it gets refreshed
- To enhance the performance from a session, what parameters are tweaked?
- Index cache memory
- DTM shared memory
- Increasing commit interval
- By indexing
- Data cache memory
- Using persistent cache
- Give benefits of DWH?
- Delivery of information is immediate
- Future vision of historical trends
- Customer service is improved
- Tools to look data in new ways
- Define Dimension table?
The data which is used to the data stored in fact table is present in the Dimensional table.
- Updatable data
- Primarily character data
- Fewer rows and
- One primary key
- Give different types of dimensions?
- Structural Dimension
- Informational Dimension
- Partitioning Dimension
- Categorical Dimension