Gyan Asthali Residential School Data Engineer 1 Interview Questions and Answers

Q1. Difference between Delete and Truncate
Ans. 
Delete removes specific rows from a table, while Truncate removes all rows from a table.
• Delete is a DML operation, while Truncate is a DDL operation.

• Delete can be rolled back, while Truncate cannot be rolled back.

• Delete operation is slower compared to Truncate operation.

• Delete operation maintains the integrity constraints, triggers, and indexes, while Truncate does not.
Answered by AI
Add your answer

MCQ based technical questions they have asked. Covering most of the basic abinitio components

Q1. Technical questions like scd types ,rollup normalise join scenarios.
Add your answer

Aptitude Questions related to Basic Quanitative aptitue, psuedo code snippets,Computer Fundamental Questions, Related to Operating System

There are 3 coding question
1.Easy (Related to Arrays)
2.Medium(String related questions)
3.Medium(Stack related questions)

Q1. 1.Introduction 2.Discussion about my projects 3.Questions on some terms which was mentioned in my project 4.One easy level coding questions 5. Two sql questions related to Join Logic 6.Some questions relat...read more
Add your answer

Q1. Mainly for Document Verification
Add your answer

Q1. Are you ready to relocate
Add your answer
Q2. What's your salary expectation
Add your answer

Q1. Explain ETL pipeline ecosystem in Azure Databricks?
Add your answer
Q2. Star vs Snowflake schema, when to use?
Add your answer
Q3. Find Salary higher than Average department salary
Add your answer
Q4. Implementation of SCD2 table
Add your answer
Q5. How incremental loading is done
Add your answer

Q1. How would you create a pipeline for ADLS to SQL data movement?
Add your answer
Q2. How would you create a pipeline from REST API to ADLS? What is there are 8 million rows of records?
Add your answer
Q3. IF data needs filtering, joining and aggregation, how would you do it with ADF?
Add your answer
Q4. Explain medallion architecture.
Add your answer
Q5. Explain medallion with databricks
Add your answer

Q1. Basic questions and salary expectation.
Add your answer

Q1. Sql constrainsts, star schema, dml dcl commands
Add your answer
Q2. About cureent project and responsibilities
Add your answer

Q1. Current projects and resposibilities
Add your answer
Q2. Where vs having, reason for job change
Add your answer

Q1. How do you optimize SQL queries?
Ans. 
Optimizing SQL queries involves using indexes, avoiding unnecessary joins, and optimizing the query structure.
• Use indexes on columns frequently used in WHERE clauses

• Avoid using SELECT * and only retrieve necessary columns

• Optimize joins by using INNER JOIN instead of OUTER JOIN when possible

• Use EXPLAIN to analyze query performance and make necessary adjustments
Answered by AI
Add your answer
Q2. How do you do performance optimization in Spark. Tell how you did it in you project.
Ans. 
Performance optimization in Spark involves tuning configurations, optimizing code, and utilizing caching.
• Tune Spark configurations such as executor memory, number of executors, and shuffle partitions.

• Optimize code by reducing unnecessary shuffles, using efficient transformations, and avoiding unnecessary data movements.

• Utilize caching to store intermediate results in memory and avoid recomputation.

• Example: In my projec...read more
Answered by AI
Add your answer
Q3. What is SparkContext and SparkSession?
Ans. 
SparkContext is the main entry point for Spark functionality, while SparkSession is the entry point for Spark SQL.
• SparkContext is the entry point for low-level API functionality in Spark.

• SparkSession is the entry point for Spark SQL functionality.

• SparkContext is used to create RDDs (Resilient Distributed Datasets) in Spark.

• SparkSession provides a unified entry point for reading data from various sources and performing
Answered by AI
Add your answer
Q4. When a spark job is submitted, what happens at backend. Explain the flow.
Ans. 
When a spark job is submitted, various steps are executed at the backend to process the job.
• The job is submitted to the Spark driver program.

• The driver program communicates with the cluster manager to request resources.

• The cluster manager allocates resources (CPU, memory) to the job.

• The driver program creates DAG (Directed Acyclic Graph) of the job stages and tasks.

• Tasks are then scheduled and executed on worker nodes ...read more
Answered by AI
View 1 more answer
Q5. Calculate second highest salary using SQL as well as pyspark.
Ans. 
Calculate second highest salary using SQL and pyspark
• Use SQL query with ORDER BY and LIMIT to get the second highest salary

• In pyspark, use orderBy() and take() functions to achieve the same result
Answered by AI
Add your answer
Q6. 2 types of modes for Spark architecture ?
Ans. 
The two types of modes for Spark architecture are standalone mode and cluster mode.
• Standalone mode: Spark runs on a single machine with a single JVM and is suitable for development and testing.

• Cluster mode: Spark runs on a cluster of machines managed by a cluster manager like YARN or Mesos for production workloads.
Answered by AI
Add your answer
Q7. If you want very less latency - which is better standalone or client mode?
Ans. 
Client mode is better for very less latency due to direct communication with the cluster.
• Client mode allows direct communication with the cluster, reducing latency.

• Standalone mode requires an additional layer of communication, increasing latency.

• Client mode is preferred for real-time applications where low latency is crucial.
Answered by AI
Add your answer

Q1. Scenario based. Write SQL and pyspark code for a dataset.
Add your answer
Q2. If you have to find latest record based on latest timestamp in a table for a particular customer(table is having history) , how will you do it. Self join and nested query will be expensive. Optimized query...read more
Add your answer

Q1. What are different type of joins available in Databricks?
Ans. 
Different types of joins available in Databricks include inner join, outer join, left join, right join, and cross join.
• Inner join: Returns only the rows that have matching values in both tables.

• Outer join: Returns all rows when there is a match in either table.

• Left join: Returns all rows from the left table and the matched rows from the right table.

• Right join: Returns all rows from the right table and the matched rows ...read more
Answered by AI
Add your answer
Q2. How do you make your data pipeline fault tolerant?
Ans. 
Implementing fault tolerance in a data pipeline involves redundancy, monitoring, and error handling.
• Use redundant components to ensure continuous data flow

• Implement monitoring tools to detect failures and bottlenecks

• Set up automated alerts for immediate response to issues

• Design error handling mechanisms to gracefully handle failures

• Use checkpoints and retries to ensure data integrity
Answered by AI
Add your answer
Q3. What is AutoLoader?
Ans. 
AutoLoader is a feature in data engineering that automatically loads data from various sources into a data warehouse or database.
• Automates the process of loading data from different sources

• Reduces manual effort and human error

• Can be scheduled to run at specific intervals

• Examples: Apache Nifi, AWS Glue
Answered by AI
Add your answer

Q1. How do you connect to different services in Azure?
Ans. 
To connect to different services in Azure, you can use Azure SDKs, REST APIs, Azure Portal, Azure CLI, and Azure PowerShell.
• Use Azure SDKs for programming languages like Python, Java, C#, etc.

• Utilize REST APIs to interact with Azure services programmatically.

• Access and manage services through the Azure Portal.

• Leverage Azure CLI for command-line interface interactions.

• Automate tasks using Azure PowerShell scripts.
Answered by AI
Add your answer
Q2. What are linked Services?
Ans. 
Linked Services are connections to external data sources or destinations in Azure Data Factory.
• Linked Services define the connection information needed to connect to external data sources or destinations.

• They can be used in Data Factory pipelines to read from or write to external systems.

• Examples of Linked Services include Azure Blob Storage, Azure SQL Database, and Amazon S3.
Answered by AI
Add your answer