Ernst & Young Data Engineer Interview Questions and Answers

SQL order of execution determines the sequence in which different clauses are processed in a query.

• SQL query is parsed and validated first

• Next, the query optimizer creates an execution plan

• Execution plan includes steps like table scans, index scans, joins, etc.

• Finally, the query is executed and results are returned

Find the highest salary for each employee in their respective departments using SQL queries.

• Use the 'employees' table to get employee details including salary.

• Join the 'employees' table with the 'departments' table on department ID.

• Use the SQL 'GROUP BY' clause to group results by department.

• Utilize the 'MAX()' function to find the highest salary within each department.

• Example SQL query: SELECT department_id, MAX(salar...read more

Use SQL query to find the maximum salary for each department

• Use GROUP BY clause to group the data by department

• Use MAX() function to find the maximum salary within each group

• Join the tables if necessary to get department information

Amazon Redshift is a data warehouse service, while DynamoDB is a NoSQL database service for key-value and document data.

• Redshift is optimized for complex queries and analytics, while DynamoDB is designed for high-speed transactions.

• Redshift uses a columnar storage format, making it efficient for analytical queries; DynamoDB uses key-value pairs for quick lookups.

• Redshift is suitable for large-scale data warehousing, wh...read more

Query to find the second-highest average salary in each department using SQL.

• Use the AVG() function to calculate average salaries per department.

• Utilize a subquery to rank the average salaries within each department.

• Use the ROW_NUMBER() or RANK() window function to assign ranks to average salaries.

• Filter the results to get only the second-highest average salary.

AWS S3 offers various storage classes to optimize cost and performance based on data access patterns.

• S3 Standard: General-purpose storage for frequently accessed data.

• S3 Intelligent-Tiering: Automatically moves data between two access tiers when access patterns change.

• S3 Standard-IA (Infrequent Access): Lower-cost storage for infrequently accessed data, with retrieval fees.

• S3 One Zone-IA: Lower-cost option for infreque...read more

Star schema has a single fact table connected to multiple dimension tables, while Snowflake schema has normalized dimension tables.

• Star schema denormalizes data for faster query performance

• Snowflake schema normalizes data to reduce redundancy

• Star schema is easier to understand and query

• Snowflake schema is more flexible and scalable

• Example: A star schema for a sales database would have a fact table for sales transaction...read more

Transient tables are temporary tables, fact tables contain quantitative data, and dimension tables contain descriptive data.

• Transient tables are temporary and used for intermediate processing or storing temporary data.

• Fact tables contain quantitative data such as sales, revenue, or quantities.

• Dimension tables contain descriptive data like customer names, product categories, or dates.