PolicyBazaar Senior Database Developer Interview Questions, Process, and Tips

Explanation of database terms: Bookmarking, Row id, Key Index, covering index.

• Bookmarking is a feature that allows users to save the location of a record in a database for future reference.

• Row id is a unique identifier assigned to each row in a table.

• Key Index is an index created on a column or set of columns that uniquely identifies each row in a table.

• Covering index is an index that contains all the columns required ...read more

Data types are classifications of data items that determine the nature of operations that can be performed on them.

• Numeric data types: int, float, double, decimal

• Character and string data types: char, varchar, text

• Date and time data types: date, time, datetime

• Boolean data type: bool

• Binary data types: binary, varbinary, image

Normalization is the process of organizing data in a database to reduce redundancy and dependency.

• It involves breaking down a table into smaller tables and defining relationships between them.

• Normalization helps to eliminate data inconsistencies and anomalies.

• There are different levels of normalization, with each level having specific rules to follow.

• For example, first normal form (1NF) requires that each column in a t...read more

Containers in SSIS are logical groupings of tasks and containers that define the workflow of an SSIS package.

• Containers can be used to group related tasks together and provide a clear visual representation of the package workflow.

• There are several types of containers in SSIS, including Sequence Containers, For Loop Containers, and Foreach Loop Containers.

• Sequence Containers are used to group tasks together and define t...read more

Transformations in SSIS are used to modify data during ETL process.

• Data Conversion Transformation

• Conditional Split Transformation

• Aggregate Transformation

• Sort Transformation

• Merge Transformation

• Lookup Transformation

• Derived Column Transformation

• Multicast Transformation

• Union All Transformation

• Pivot Transformation

• Unpivot Transformation

Event and error handling are crucial in SSIS to ensure smooth execution and proper handling of exceptions.

• Event handling involves capturing and responding to events that occur during package execution.

• Error handling involves identifying and handling errors that occur during package execution.

• SSIS provides various built-in event handlers and error handlers, such as OnError, OnTaskFailed, OnPostExecute, etc.

• Custom event ...read more

Snow Flake Schema has better performance than Star Flake Schema.

• Snow Flake Schema has normalized tables which reduces data redundancy and improves query performance.

• Star Flake Schema has denormalized tables which can lead to data redundancy and slower query performance.

• Snow Flake Schema is better suited for complex queries with multiple joins.

• Star Flake Schema is better suited for simple queries with fewer joins.

• Perfor...read more

Temp variables, tables, and CTE are used in SQL to store temporary data during query execution.

• Temp variables are used to store temporary data within a query.

• Temp tables are used to store temporary data that can be accessed by multiple queries.

• CTE (Common Table Expression) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

• Temp variables and tables are created using th...read more

Merge combines two tables based on a specified condition, while union combines two tables with the same structure.

• Merge is used to update or insert data into a target table based on a join condition with a source table.

• Union is used to combine the results of two SELECT statements into a single result set.

• Merge requires a join condition and can perform insert, update, and delete operations.

• Union requires that both table...read more