Senior Software Developer

ETL is a process in Data Warehousing and it stands for Extract, Transform and Load. It is a process in which an ETL tool extracts the data from various data source systems, transforms it in the staging area, and then finally, loads it into the Data Warehouse system. 

The different steps in ETL process are : 

1) Extraction: 
The first step of the ETL process is extraction. In this step, data from various source systems is extracted which can be in various formats like relational databases, No SQL, XML, and flat files into the staging area. It is important to extract the data from various source systems and store it into the staging area first and not directly into the data warehouse because the extracted data is in various formats and can be corrupted also. Hence loading it directly into the data warehouse may damage it and rollback will be much more difficult. Therefore, this is one of the most important steps of ETL process.

2) Transformation: 
The second step of the ETL process is transformation. In this step, a set of rules or functions are applied on the extracted data to convert it into a single standard format. It may involve following processes/tasks: 
i) Filtering – loading only certain attributes into the data warehouse.
ii) Cleaning – filling up the NULL values with some default values, mapping U.S.A, United States, and America into USA, etc.
iii) Joining – joining multiple attributes into one.
iv) Splitting – splitting a single attribute into multiple attributes.
v) Sorting – sorting tuples on the basis of some attribute (generally key-attribute).

3) Loading: 
The third and final step of the ETL process is loading. In this step, the transformed data is finally loaded into the data warehouse. Sometimes the data is updated by loading into the data warehouse very frequently and sometimes it is done after longer but regular intervals. The rate and period of loading solely depends on the requirements and varies from system to system.

What is a Slowly Changing Dimension?

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records.

There are three types of SCDs and you can use Warehouse Builder to define, deploy, and load all three types of SCDs.

Type 1 SCDs - Overwriting : 
In a Type 1 SCD the new data overwrites the existing data. Thus the existing data is lost as it is not stored anywhere else. This is the default type of dimension you create. You do not need to specify any additional information to create a Type 1 SCD.

Type 2 SCDs - Creating another dimension record : 
A Type 2 SCD retains the full history of values. When the value of a chosen attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective time and expiration time to identify the time period between which the record was active.

Type 3 SCDs - Creating a current value field : 
A Type 3 SCD stores two versions of values for certain selected level attributes. Each record stores the previous value and the current value of the selected attribute. When the value of any of the selected attributes changes, the current value is stored as the old value and the new value becomes the current value.

A high cardinality attribute column in the fact table which does not have any other content except its natural key and is required as a dimension for analysis or drill-down purpose, is called a degenerate dimension. As this degenerate dimension is constructed from a fact table item and is placed in the fact table, it is also known as fact dimension. It helps to reduce duplicate data by placing high cardinality dimension key in the fact table.

1) Fact table contains the measuring on the attributes of a dimension table.
Dimension table contains the attributes on that truth table calculates the metric.

2) In fact table, There is less attributes than dimension table.
While in dimension table, There is more attributes than fact table.

3) In fact table, There is more records than dimension table.
While in dimension table, There is less records than fact table.

4) Fact table forms a vertical table.
While dimension table forms a horizontal table.

5) The attribute format of fact table is in numerical format and text format.
While the attribute format of dimension table is in text format.

6) The number of fact table is less than dimension table in a schema.
While the number of dimension is more than fact table in a schema.

OLAP stands for Online analytical processing and OLTP stands for Online transaction processing. The major differences b/w OLTP and OLAP are : 

1) OLAP : Consists of historical data from various Databases.
OLTP : Consists only operational current data.

2) OLAP : It is subject oriented. Used for Data Mining, Analytics, Decision making,etc.
OLTP : It is application oriented. Used for business tasks.

3) OLAP : It reveals a snapshot of present business tasks.
OLTP : It provides a multi-dimensional view of different business tasks.

4) OLAP : Relatively slow as the amount of data involved is large. Queries may take hours.
OLTP : Very Fast as the queries operate on 5% of the data.

5) OLAP : It only need backup from time to time as compared to OLTP.
OLTP : Backup and recovery process is maintained religiously

6) OLAP : Large amount of data is stored typically in TB, PB
OLTP : The size of the data is relatively small as the historical data is archived. For ex MB, GB

To delete the duplicate rows from the table in SQL Server, we should follow these steps:

1) Find duplicate rows using GROUP BY clause or ROW_NUMBER() function.
2) Use DELETE statement to remove the duplicate rows.

1) INNER JOIN : It returns the combined tuple between two or more tables.
OUTER JOIN : It returns the combined tuple from a specified table even join condition will fail.

2) INNER JOIN : Used clause INNER JOIN and JOIN.
OUTER JOIN : Used clause LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, etc.

3) INNER JOIN : When any attributes are not common then it will return nothing.
OUTER JOIN : It does not depend upon the common attributes. If the attribute is blank then here already placed NULL.

4) INNER JOIN : If tuples are more. Then INNER JOIN works faster than OUTER JOIN.
OUTER JOIN : Generally, The OUTER JOIN is slower than INNER JOIN. But except for some special cases.

5) INNER JOIN : It is used when we want detailed information about any specific attribute.
OUTER JOIN : It is used when we want to complete information.

6) INNER JOIN : JOIN and INNER JOIN both clauses work the same.
OUTER JOIN : FULL OUTER JOIN and FULL JOIN both clauses work the same.

Tip 1 : Prepare the points that you will speak in your introduction prior to the interview.
Tip 2 : Tell about your current cgpa, achievements and authenticated certification
Tip 3 : I told about my role in current internship and what all I do

Tip : For an experienced professional seeking a change, this is a common question. The easiest method to respond to this question is to state that you are leaving your current work in order to advance your career. Make sure you don't criticize or speak poorly about the company where you now work.