You are using Python to transform data in an ETL job, and the transformation is failing intermittently due to missing values. What strategies do you implement to handle this?
What a strong answer covers
Uses try-except blocks to catch errors; applies data validation checks before processing; implements default values or imputation for missing data; logs missing value occurrences for further analysis.
The follow-up you would get
What would you do if you can't predict where the missing values will occur?
Could you have answered that?
scenarioPerformance Tuning2 of 6
You notice that a crucial report using a SQL query takes significantly longer to run than it did previously. What is your immediate response?
What a strong answer covers
Examines the query execution plan for slow operations; checks for recent changes in underlying data or structure; considers adding indexes or optimising joins; runs the query with EXPLAIN to understand performance bottlenecks.
The follow-up you would get
The underlying data has doubled in size. How do you manage performance as the dataset continues to grow?
Could you have answered that?
scenarioData Quality3 of 6
You receive a report that several records in your data warehouse are inaccurate, and the issue needs to be resolved urgently. What do you do in the first half hour?
What a strong answer covers
Identifies the scope of the inaccuracies; reviews data lineage to trace sources; checks for recent changes in ETL processes; validates against source data; communicates with data owners for context.
The follow-up you would get
You discover the inaccuracy originated from a change in a source system. How do you prevent this from recurring?
Could you have answered that?
ownershipDebugging existing code4 of 6
Tell me about a time when you encountered a bug in your code that you couldn't immediately fix. How did you approach the problem?
What a strong answer covers
Describes the context of the bug and its symptoms; outlines the systematic approach taken to debug, such as using logging or stepping through code; mentions seeking help through forums or colleagues; concludes with the eventual resolution and any changes made to prevent recurrence.
The follow-up you would get
What tools or methods would you use next time?
Could you have answered that?
technicalData Warehousing5 of 6
A data model in your warehouse has a table that is experiencing significant skew, with one value representing 80% of the rows. What strategies can you employ to handle this issue?
What a strong answer covers
Considers partitioning the table by a different column; evaluates whether to use a surrogate key to reduce skew; checks for appropriate indexing on the heavily skewed column; discusses the impact of denormalisation on performance.
The follow-up you would get
If partitioning does not resolve the issue, what is another approach?
Could you have answered that?
technicalWindow Functions6 of 6
You are tasked with calculating a moving average over a dataset, but the performance is slow. What steps do you take to optimise the query?
What a strong answer covers
Examines the execution plan for inefficiencies; checks indexing on the columns used in the window function; considers reducing the data size before applying the window function; evaluates whether to materialise intermediate results.
The follow-up you would get
If the execution plan shows a full scan, what can you do next?
Could you have answered that?
That is this set.
Load six more — you will get questions you have not seen first.