Infovision Data Engineer Interview Questions and Answers

Window functions in SQL are used to perform calculations across a set of table rows related to the current row.

• Window functions are used to calculate values based on a set of rows related to the current row.

• They allow for ranking, aggregation, and other calculations within a specific window of rows.

• Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), and SUM() OVER().

To perform a full load in SSIS, you can use the Data Flow Task with a source and destination component.

• Create a Data Flow Task in the Control Flow tab of the SSIS package.

• Add a source component to extract data from the source system.

• Add a destination component to load data into the destination system.

• Map the columns from the source to the destination.

• Run the package to execute the full load.

Incremental load in ADF and SSIS involves identifying new or updated data and loading only those changes.

• In ADF, use watermark columns to track the last loaded value and filter data based on this value

• In SSIS, use CDC (Change Data Capture) components or custom scripts to identify new or updated data

• Both ADF and SSIS support incremental loading by comparing source and target data to determine changes

Linked Service connects to external data sources, while Dataset represents the data within the data store.

• Linked Service is used to connect to external data sources like databases, APIs, and file systems.

• Dataset represents the data within the data store and can be used for data processing and analysis.

• Linked Service defines the connection information and credentials needed to access external data sources.

• Dataset define...read more

Integration Runtime (IR) is a compute infrastructure that provides data integration capabilities across different network environments.

• IR is used in Azure Data Factory to provide data integration capabilities

• There are three types of IR: Azure, Self-hosted, and Azure-SSIS

• Azure IR is fully managed by Microsoft and is used for data movement in the cloud

• Self-hosted IR allows data movement between on-premises and cloud data...read more

Use Azure Data Factory to transfer files >10MB from FTP to ADLS efficiently.

• 1. Create a linked service for FTP in ADF to connect to the FTP server.

• 2. Create a linked service for ADLS to connect to your Azure Data Lake Storage.

• 3. Use a Copy Data activity in a pipeline to transfer files.

• 4. Set up a filter in the Copy Data activity to only select files larger than 10MB.

• 5. Schedule the pipeline to run at desired interva...read more

Use a single activity with dynamic parameterization and loops to copy data.

• Use a loop to iterate through the data source and destination locations.

• Parameterize the source and destination locations to dynamically copy data.

• Utilize a scripting language like Python or PowerShell to implement the logic.

• Example: Use a Python script with a loop to copy files from one folder to another.

• Example: Use PowerShell script with dyna...read more

Fact tables store quantitative data for analysis, while dimension tables store descriptive attributes related to facts.

• Fact tables contain measurable, quantitative data (e.g., sales revenue, order quantity).

• Dimension tables contain descriptive attributes (e.g., product name, customer details).

• Fact tables often have foreign keys linking to dimension tables.

• Example: A sales fact table might include total sales, while a p...read more

Snowflake is a cloud-based data warehousing platform that allows for easy and scalable data storage and analysis.

• Snowflake is a fully managed service that works on a pay-as-you-go model.

• It separates storage and compute resources, allowing for better scalability and cost-effectiveness.

• Snowflake supports SQL queries and has built-in support for semi-structured data like JSON and XML.

• It provides features like automatic sc...read more

Use SQL MERGE to efficiently insert or update records based on incoming data.

• MERGE statement combines INSERT and UPDATE operations in one command.

• Example: MERGE INTO target_table USING source_table ON target_table.id = source_table.id.

• When a match is found, use UPDATE to modify existing records.

• When no match is found, use INSERT to add new records.

• Example syntax: WHEN MATCHED THEN UPDATE SET target_table.col1 = source_...read more

Delete removes rows one by one and can be rolled back, while Truncate removes all rows at once and cannot be rolled back.

• Delete is a DML command, while Truncate is a DDL command.

• Delete can be rolled back using a transaction, while Truncate cannot be rolled back.

• Delete fires triggers on each row deletion, while Truncate does not fire triggers.

• Delete is slower as it removes rows one by one, while Truncate is faster as it...read more

To insert non-duplicate data into a target table, you can use methods like using a unique constraint, using a merge statement, or using a temporary table.

• Use a unique constraint on the target table to prevent duplicate entries.

• Use a merge statement to insert data into the target table only if it does not already exist.

• Use a temporary table to store the new data, then insert only the non-duplicate records into the targe...read more

Identifying long running queries in SQL

• Monitor query execution times using tools like SQL Server Profiler or Performance Monitor

• Check system views like sys.dm_exec_requests or sys.dm_exec_query_stats for query durations

• Use query hints like OPTION (RECOMPILE) to force recompilation of queries for better performance