Oracle Data Engineer Interview: Rounds, Questions and Preparation

What the job involves

Data Engineers build and maintain the pipelines that move and transform data from source systems into warehouses and lakes, so analysts and ML models can use it. In global tech firms, they work with cloud services, big data tools, and SQL to ensure data is reliable, timely, and well-modelled. A fresher typically starts by writing ETL jobs, fixing pipeline issues, and optimising queries under senior guidance.

Role and Technical Knowledge

1. 1

   What is the difference between ETL and ELT, and when would you choose one over the other?

   Explain that ETL transforms before loading while ELT loads raw data first and transforms in the target. Mention that cloud warehouses with strong compute favour ELT for flexibility and scalability.

2. 2

   How would you design a star schema for a retail sales dataset?

   Describe a fact table with sales measures and dimension tables for product, customer, time, and store. Highlight benefits like simpler queries and faster aggregations for analytics.

3. 3

   Explain how you would handle slowly changing dimensions (SCD) Type 2 in a data warehouse.

   Cover adding new rows with effective dates and current flags instead of overwriting. Mention the need for surrogate keys and how it preserves history for reporting.

4. 4

   What are the key differences between a data lake and a data warehouse, and when would you use each?

   Contrast schema-on-read vs schema-on-write, storage cost, and typical use cases. A strong answer notes that many modern setups combine both in a lakehouse pattern.

Practical Scenarios and Problem Solving

1. 5

   A daily ETL job that loads sales data has been failing for three days. How would you debug and fix it?

   Start by checking logs and error messages, then validate source data availability, schema changes, and dependencies. Suggest a step-by-step isolation approach and a rollback or backfill plan.

2. 6

   You need to process a 500 GB log file with Python on a single machine. What approach would you take?

   Mention chunked reading, using generators, or switching to a distributed tool like Spark. Discuss memory limits and why loading everything into pandas is not feasible.

3. 7

   How would you ensure data quality in a pipeline that ingests data from multiple sources?

   Talk about validation checks (nulls, duplicates, range checks), monitoring alerts, and idempotent writes. Mention data contracts or schema registry as preventive measures.

4. 8

   A stakeholder reports that a dashboard shows incorrect totals. How do you trace the issue?

   Walk through checking the dashboard query, then the underlying tables, then the ETL logic and source data. Emphasise reproducing the issue and documenting findings before fixing.

SQL, Python, and Tools

1. 9

   Write a SQL query to find the second highest salary from an employee table.

   Use a subquery with MAX and a condition, or a window function like DENSE_RANK. Explain why the window function approach handles ties more cleanly.

2. 10

   How would you use Python to read a CSV file, filter rows, and write the result to a new file?

   Mention pandas read_csv, boolean indexing, and to_csv. Also note handling of missing values and choosing appropriate data types to save memory.

3. 11

   What is the difference between a left join and an inner join, and how can joins affect pipeline performance?

   Explain that inner join returns only matching rows while left join keeps all left rows. Discuss how large joins can cause shuffles and how partitioning or broadcast joins help.

4. 12

   How do you version control your data pipeline code, and why is it important?

   Describe using Git with feature branches, pull requests, and meaningful commits. Highlight collaboration, rollback ability, and auditability in team environments.

Behavioural and HR

1. 13

   Tell me about a time you had to learn a new technology quickly for a project.

   Use the STAR method: describe the situation, the technology, your learning approach, and the outcome. Show curiosity and ability to apply new skills under deadlines.

2. 14

   Describe a situation where you found a mistake in your work or a teammate's work. What did you do?

   Emphasise ownership, transparency, and fixing the issue without blaming. Mention how you communicated and what you changed to prevent recurrence.

3. 15

   Why do you want to work as a Data Engineer in a global tech company?

   Connect your interest in data pipelines and cloud technologies to the scale and impact of global systems. Show awareness of the role's challenges and your willingness to grow.

4. 16

   How do you prioritise when you have multiple data requests from different teams?

   Talk about assessing business impact, deadlines, and dependencies, then communicating clearly with stakeholders. Mention using a simple framework like urgency vs importance.