PBI Analytics Azure Data Engineer Interview Questions and Answers

Q1. Lets say you have table 1 with values 1,2,3,5,null,null,0 and table 2 has null,2,4,7,3,5 What would be the output after inner join?
Ans. 
The output after inner join of table 1 and table 2 will be 2,3,5.
• Inner join only includes rows that have matching values in both tables.

• Values 2, 3, and 5 are present in both tables, so they will be included in the output.

• Null values are not considered as matching values in inner join.
Answered by AI
Add your answer
Q2. Lets say you have customers table with customerID and customer name, Orders table with OrderId and CustomerID. write a query to find the customer name who placed the maximum orders. if more than one person...read more
Add your answer
Q3. Spark Architecture, Optimisation techniques
Add your answer
Q4. Some personal questions.
Add your answer

Q1. Explain the entire architecture of a recent project you are working on in your organisation.
Ans. 
The project involves building a data pipeline to ingest, process, and analyze large volumes of data from various sources in Azure.
• Utilizing Azure Data Factory for data ingestion and orchestration

• Implementing Azure Databricks for data processing and transformation

• Storing processed data in Azure Data Lake Storage

• Using Azure Synapse Analytics for data warehousing and analytics

• Leveraging Azure DevOps for CI/CD pipeline aut
Answered by AI
Add your answer
Q2. How do you design an effective ADF pipeline and what all metrics and considerations you should keep in mind while designing?
Ans. 
Designing an effective ADF pipeline involves considering various metrics and factors.
• Understand the data sources and destinations

• Identify the dependencies between activities

• Optimize data movement and processing for performance

• Monitor and track pipeline execution for troubleshooting

• Consider security and compliance requirements

• Use parameterization and dynamic content for flexibility

• Implement error handling and retries fo
Answered by AI
Add your answer
Q3. Lets say you have a very huge data volume and in terms of performance how would you slice and dice the data in such a way that you can boost the performance?
Add your answer
Q4. Lets say you have to reconstruct a table and we have to preserve the historical data ? ( i couldnt answer that but please refer to SCD)
Add your answer
Q5. We have adf and databricks both, i can achieve transformation , fetching the data and loading the dimension layer using adf also but why do we use databricks if both have the similar functionality for few ...read more
Add your answer

Q1. Basic HR questions
Add your answer

Q1. SCD type 1 and SCD type 2 in databircks
Add your answer
Q2. How to pass parameters form ADF to ADB
Add your answer

Sql queries and knowledge of different syntaxes

Q1. Find the student with marks greater than 80 in all subjects
Ans. 
Filter students with marks greater than 80 in all subjects
• Iterate through each student's marks in all subjects

• Check if all marks are greater than 80 for a student

• Return the student if all marks are greater than 80
Answered by AI
Add your answer
Q2. Write the syntax to define the schema of a file for loading.
Ans. 
Syntax to define schema of a file for loading
• Use CREATE EXTERNAL TABLE statement in SQL

• Specify column names and data types in the schema definition

• Example: CREATE EXTERNAL TABLE MyTable (col1 INT, col2 STRING) USING CSV
Answered by AI
Add your answer

Q1. Activities used in ADF
Ans. 
Activities in Azure Data Factory (ADF) are the building blocks of a pipeline and perform various tasks like data movement, data transformation, and data orchestration.
• Activities can be used to copy data from one location to another (Copy Activity)

• Activities can be used to transform data using mapping data flows (Data Flow Activity)

• Activities can be used to run custom code or scripts (Custom Activity)

• Activities can be u...read more
Answered by AI
Add your answer
Q2. Dataframes in pyspark
Ans. 
Dataframes in pyspark are distributed collections of data organized into named columns.
• Dataframes are similar to tables in a relational database, with rows and columns.

• They can be created from various data sources like CSV, JSON, Parquet, etc.

• Dataframes support SQL queries and transformations using PySpark functions.

• Example: df = spark.read.csv('file.csv')
Answered by AI
Add your answer

Q1. Managerial Questions
Add your answer
Q2. About project roles and resposibilities
Add your answer

Q1. Based on my previous company projects
Add your answer
Q2. SQL based questions are asked
Add your answer
Q3. ADF based questions are asked
Add your answer
Q4. Azure related questions are asked
Add your answer

Q1. Reg salary discussion
Add your answer

Q1. How are you connecting your onPerm from Azure?
Ans. 
I connect onPrem to Azure using Azure ExpressRoute or VPN Gateway.
• Use Azure ExpressRoute for private connection through a dedicated connection.

• Set up a VPN Gateway for secure connection over the internet.

• Ensure proper network configurations and security settings.

• Use Azure Virtual Network Gateway to establish the connection.

• Consider using Azure Site-to-Site VPN for connecting onPremises network to Azure Virtual Network.
Answered by AI
Add your answer
Q2. What is Autoloader in Databricks?
Ans. 
Autoloader in Databricks is a feature that automatically loads new data files as they arrive in a specified directory.
• Autoloader monitors a specified directory for new data files and loads them into a Databricks table.

• It supports various file formats such as CSV, JSON, Parquet, Avro, and ORC.

• Autoloader simplifies the process of ingesting streaming data into Databricks without the need for manual intervention.

• It can be ...read more
Answered by AI
Add your answer
Q3. How do you normalize your Json data
Ans. 
Json data normalization involves structuring data to eliminate redundancy and improve efficiency.
• Identify repeating groups of data

• Create separate tables for each group

• Establish relationships between tables using foreign keys

• Eliminate redundant data by referencing shared values
Answered by AI
Add your answer
Q4. How do you read from Kafka?
Add your answer

Q1. Basics of adf adb
Add your answer
Q2. What is IR in adf pipe line
Ans. 
IR in ADF pipeline stands for Integration Runtime, which is a compute infrastructure used by Azure Data Factory to provide data integration capabilities across different network environments.
• IR in ADF pipeline is responsible for executing activities within the pipeline.

• It can be configured to run in different modes such as Azure, Self-hosted, and SSIS.

• Integration Runtime allows data movement between on-premises and clo...read more
Answered by AI
Add your answer

Q1. Brief about your projects Be much prompt with your roles and responsibilities. Good to know SQL better
Add your answer

Q1. Salary negotiation and about relocation
Add your answer