300+ Senior Data Engineer Interview Questions and Answers

Query to get the customer with the highest total order value for each year, month.

• Join the Order and Customer tables on the foreign key

• Group the results by year, month, and customer

• Calculate the total order value for each group

• Find the maximum total order value for each year, month

• If there are multiple customers with the same highest total order value, select the one with the lower Customer_ID

To fetch the modified information without ModifiedDate column from a table with 10 million records.

• Create a trigger to capture the modified information and insert it into a separate table with ModifiedDate column.

• Use a tool like Change Data Capture (CDC) to track changes in the table and extract the modified information.

• Use a query to compare the current table with a backup copy taken the previous day to identify the modified information.

There are multiple ways to obtain a total amount of 25 using coins of denominations 1, 2, 5, and 10. The question asks for unique distinct ways and non-unique repeated combinations or permutations.

• For unique distinct ways, you can use dynamic programming to calculate the number of ways to reach the target amount.

• For non-unique repeated combinations or permutations, you can use a recursive function to generate all possible combinations.

• Example for unique distinct ways: [1, 2, ...read more

Handle changing schema by using schema evolution techniques and version control.

• Use schema evolution techniques like adding new fields, renaming fields, and changing data types.

• Implement version control to track changes and ensure backward compatibility.

• Use tools like Apache Avro or Apache Parquet to store data in a self-describing format.

• Implement automated testing to ensure data quality and consistency.

• Collaborate with data producers to establish clear communication and doc...read more

Find indices of an element in a non-decreasing array

• Iterate through the array and keep track of the indices where the element X is found

• Return the list of indices or [-1, -1] if element X is not found

• Handle edge cases like empty array or X not present in the array

Normalisation is the process of organizing data in a database to reduce redundancy and improve data integrity. Denormalisation is the opposite process.

• Normalisation is used to eliminate data redundancy and improve data integrity.

• Denormalisation is used to improve query performance by reducing the number of joins required.

• A real-time example of normalisation is breaking down a customer's information into separate tables such as customer details, order details, and payment deta...read more

There are three types of schema in Data Warehousing: Star Schema, Snowflake Schema, and Fact Constellation Schema.

• Star Schema: central fact table connected to dimension tables in a star shape

• Snowflake Schema: extension of star schema with normalized dimension tables

• Fact Constellation Schema: multiple fact tables connected to dimension tables in a complex structure

Use Spark (Databricks) notebooks to migrate 1000s of tables efficiently.

• Utilize Spark's parallel processing capabilities to handle large volumes of data

• Leverage Databricks notebooks for interactive data exploration and transformation

• Automate the migration process using scripts or workflows

• Optimize performance by tuning Spark configurations and cluster settings

Use the len() function to check the length of the data frame.

• Use len() function to get the number of rows in the data frame.

• If the length is 0, then the data frame is empty.

• Example: if len(df) == 0: print('Data frame is empty')

Calculating resources based on cores and memory given with overhead and driver memory

• Calculate the total memory available by multiplying the number of cores with memory per core

• Deduct the overhead memory required for the operating system and other processes

• Allocate driver memory for each executor based on the workload

• Consider the memory requirements for other services like Hadoop, Spark, etc.

• Example: For 16 cores with 64 GB memory, assuming 1 GB overhead and 2 GB driver memor...read more

Transaction isolation is a concept in databases that ensures transactions are executed independently of each other.

• Transaction isolation levels determine the degree to which one transaction must be isolated from other transactions.

• Types of transaction isolation levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

• Each isolation level offers a different level of consistency and concurrency control.

• For example, in Read Uncommitted isolation level, ...read more

Join two DataFrames in PySpark and replace null values with corresponding values from another DataFrame.

• Use the 'join' method to combine two DataFrames based on a common key.

• Utilize 'fillna' or 'when' functions to replace null values after the join.

• Example: df1.join(df2, 'key').fillna(df2['value']) to replace nulls in df1 with values from df2.

• Ensure the DataFrames are properly aligned on the join key to avoid data loss.

Pass connection string as environment variable or use AWS Secrets Manager

• Store connection string as environment variable in Lambda function configuration

• Retrieve connection string from AWS Secrets Manager and use it in Lambda function

• Use IAM role to grant Lambda function access to database

• Encrypt connection string using AWS KMS for added security

Tasks and stages are components of the execution plan in Spark UI.

• Tasks are the smallest unit of work in Spark, representing a single operation on a partition of data.

• Stages are groups of tasks that are executed together as part of a larger computation.

• Tasks within a stage can be executed in parallel, while stages are executed sequentially.

• Tasks are created based on the transformations and actions in the Spark application.

• Stages are created based on the dependencies between R...read more

Query acceleration speeds up query processing by optimizing query execution and reducing the time taken to retrieve data.

• Query acceleration uses techniques like indexing, partitioning, and caching to optimize query execution.

• It reduces the time taken to retrieve data by minimizing disk I/O and utilizing in-memory processing.

• Examples include using columnar storage formats like Parquet or optimizing join operations.

Check if a list is sorted and sort the list without using Python methods.

• To check if a list is sorted, iterate through the list and compare each element with the next one. If any element is greater than the next one, the list is not sorted.

• To sort the list without using Python methods, implement a sorting algorithm like bubble sort, selection sort, or insertion sort.

• Example for checking if a list is sorted: ['a', 'b', 'c'] is sorted, ['c', 'b', 'a'] is not sorted.

• Example for ...read more

Repartition increases or decreases the number of partitions in a DataFrame, while Coalesce only decreases the number of partitions.

• Repartition can increase or decrease the number of partitions in a DataFrame, leading to a shuffle of data across the cluster.

• Coalesce only decreases the number of partitions in a DataFrame without performing a full shuffle, making it more efficient than repartition.

• Repartition is typically used when there is a need to increase the number of parti...read more

Given a sorted array of integers, return the sorted array of squared elements in least time complexity.

• Create a new array to store squared elements

• Use two pointers to iterate through the original array from both ends

• Compare the squared values and add them to the new array in descending order

Optimization techniques used in project

• Caching

• Parallel processing

• Compression

• Indexing

• Query optimization

Optimisation techniques used in the project include indexing, query optimization, caching, and parallel processing.

• Indexing: Creating indexes on frequently queried columns to improve search performance.

• Query optimization: Rewriting queries to make them more efficient and reduce execution time.

• Caching: Storing frequently accessed data in memory to reduce the need for repeated database queries.

• Parallel processing: Distributing tasks across multiple processors to speed up data p...read more

Reverse a string using SQL and Python codes.

• In SQL, use the REVERSE function to reverse a string.

• In Python, use slicing with a step of -1 to reverse a string.

AWS services used include S3, Redshift, Glue, EMR, and Lambda in a scalable and cost-effective architecture.

• AWS S3 for storing large amounts of data

• AWS Redshift for data warehousing and analytics

• AWS Glue for ETL processes

• AWS EMR for big data processing

• AWS Lambda for serverless computing

Understanding SQL joins is crucial for data retrieval and analysis in relational databases.

• INNER JOIN: Returns records with matching values in both tables. Example: SELECT * FROM A INNER JOIN B ON A.id = B.id.

• LEFT JOIN: Returns all records from the left table and matched records from the right table. Example: SELECT * FROM A LEFT JOIN B ON A.id = B.id.

• RIGHT JOIN: Returns all records from the right table and matched records from the left table. Example: SELECT * FROM A RIGHT J...read more

Broadcast variables are read-only variables that are cached on each worker node while accumulator variables are write-only variables that are used to accumulate values across multiple tasks.

• Broadcast variables are used to give every node a copy of a large input dataset or a small lookup table.

• Accumulator variables are used to keep a running total of values across multiple tasks.

• Broadcast variables are used for read-only operations while accumulator variables are used for writ...read more

The interview questions cover topics related to Azure Data Factory, Spark, and Python programming.

• Integration runtimes in ADF include Azure, Self-hosted, and SSIS IRs.

• To copy 100 files in ADF, use a Copy Data activity with a wildcard path in source and sink datasets.

• DAG in Spark represents a directed acyclic graph of computation, while lineage tracks the data flow.

• Narrow transformations in Spark operate on a single partition, wide transformations shuffle data across partition...read more

SQL query to group by employee ID and combine first name and last name with a space

• Use the GROUP BY clause to group by employee ID

• Use the CONCAT function to combine first name and last name with a space

• Select employee ID, CONCAT(first_name, ' ', last_name) AS full_name

I have experience with various Data Engineering and BI tools such as SQL, Python, Tableau, and Apache Spark.

• Proficient in SQL for data querying and manipulation

• Experience with Python for data processing and analysis

• Familiarity with Tableau for data visualization

• Worked with Apache Spark for big data processing