Infosys Senior SQL Developer Interview Questions and Answers

ETL Developers are responsible for designing, developing, and maintaining ETL processes to extract, transform, and load data from various sources into a data warehouse.

• Designing ETL processes to extract data from multiple sources

• Transforming data to meet business requirements

• Loading data into a data warehouse or other target systems

• Developing and maintaining ETL jobs using tools like Informatica, Talend, or SSIS

SCD Type 1 in IICS involves overwriting existing data with new data without maintaining historical changes.

• In IICS, use the Mapping Designer to create a mapping that loads data from source to target.

• Use a Lookup transformation to check if the record already exists in the target table.

• If the record exists, update the existing record with new data using an Update Strategy transformation.

• If the record does not exist, inse...read more

Analytical functions are used to perform calculations across a set of rows related to the current row.

• Analytical functions operate on a group of rows and return a single result for each row

• They can be used to calculate running totals, moving averages, rank, percentiles, etc.

• Examples include ROW_NUMBER(), RANK(), SUM() OVER(), AVG() OVER()

The Slowly Changing Dimension Type 2 (SCD2) transformation is used for handling historical data changes in a data warehouse.

• SCD2 transformation is used to track historical changes in dimension tables.

• It maintains multiple versions of a record by adding new rows with updated information and end-dating the previous record.

• Commonly used in scenarios where historical data needs to be preserved and queried.

• Example: When a c...read more

To find the count of different types of joins between two tables A and B.

• Left join: Includes all records from table A and matching records from table B.

• Right join: Includes all records from table B and matching records from table A.

• Inner join: Includes only the matching records from both tables.

• Full outer join: Includes all records when there is a match in either table A or table B.

I have used HUDI and Iceberg in my previous project for managing large-scale data lakes efficiently.

• Implemented HUDI for incremental data ingestion and managing large datasets in real-time

• Utilized Iceberg for efficient table management and data versioning

• Integrated HUDI and Iceberg with Apache Spark for processing and querying data

Start up parameters are used to configure the behavior of SQL Server when it starts up.

• Start up parameters are specified in the SQL Server Configuration Manager.

• Common start up parameters include -d (database files location), -e (error log location), -m (single user mode), -T (trace flags), etc.

• Start up parameters can be modified to customize the behavior of SQL Server during startup.

• Example: -dC:\Program Files\Microso...read more