Technical interview rounds: practise reasoning and validation

Choose preparation from the job description

Confirm the round format and relevant tools with the recruiter. A dashboard role, an analytics-engineering role and a pipeline role need different depth. The topics below are practice areas, not a claim that every employer tests the same questions.

SQL and Python: explain a transformation

State the input and output grain. Identify join keys, missing values, time boundaries and tie rules. Run a small example and reconcile a count or total. Use the SQL question guide and Python worked analysis.

BI and modelling: define the metric before the chart

Describe who uses the report and which decision it supports. Choose a fact-table grain and relationships that avoid duplicate measures. Explain how filters affect the denominator. Show a refresh timestamp and the data’s limitations.

Practise with the star-schema exercise. A particular schema does not automatically guarantee faster queries; measure performance on the relevant workload.

Pipeline recovery: make retries safe

A watermark-only INSERT is not a complete incremental strategy. Changed rows can duplicate earlier records, equal timestamps can be missed, and late arrivals can fall outside the window.

• Define stable keys and a repeatable source interval.
• Stage and validate the batch before applying it.
• Use a deduplicated merge/upsert or replace the affected partition where appropriate.
• Advance progress only after a successful commit.
• Test retrying the same batch and handling a late update.

The safe-retry guide develops this exercise. Airflow’s best-practices guide discusses repeatable task design.

Business reasoning: investigate a metric change

Confirm the definition and data coverage, quantify the change, then compare relevant segments. A segment difference is an observation, not a proven cause. Use the business-case guide to practise making a recommendation with limits.

Self-review after a practice round

• What assumption changed the answer?
• Which check would catch a plausible but wrong result?
• What tradeoff did you explain?
• Did you distinguish a known fact from a hypothesis?
• What single gap will you practise next?