Question 1

Can you share an experience where you resolved a conflict within your team?

Accepted Answer

**Situation:** Two senior engineers disagreed on Airflow vs Prefect for a new project; 1-week delay.

**Task:** As lead, I needed to resolve and align the team.

**Action:** I set up a bake-off. Each presented a PoC for our core use case. We defined criteria: community support, testing ease, CI/CD integration. I moderated, focusing on evidence over opinion. We scored each tool.

**Result:** We chose Airflow for better fit. Team aligned, project delivered on time....

Question 2

Create a SQL query to identify customers with purchases above a dynamic threshold.

Accepted Answer

**Architectural Logic**: Dynamic thresholds require subquery/CTE; efficiency depends on threshold computation. **Approach**: `WITH agg AS (SELECT customer_id, SUM(amount) AS total FROM purchases GROUP BY customer_id), threshold AS (SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY total) AS p90 FROM agg) SELECT a.* FROM agg a, threshold t WHERE a.total > t.p90`. **Why CTE**: Reusable threshold; single scan for percentile....

Question 3

How do you monitor consumer lag in Kafka, and how can you reduce it?

Accepted Answer

**Monitor**: Lag = log end offset - consumer offset. Tools: Kafka Consumer API, JMX, Burrow, Grafana. **Reduce lag**: (1) Scale consumers (same group)—more partitions. (2) Increase partitions (requires rebalance). (3) Optimize processing—async I/O, batch. (4) Scale downstream—don't block on slow DB. (5) Temporarily add consumer instances. **Alert**: Lag > 10K or threshold. **Root cause**: Slow processing vs....

Question 4

How do you optimize partitioning when dealing with large datasets?

Accepted Answer

**Principles**: (1) Partition by high-cardinality filter columns (date, region). (2) Target 128–200MB per partition. (3) Avoid over-partitioning—small-file problem. (4) Coalesce before write. (5) Z-ordering (Delta) for multi-column filters. (6) Dynamic partition pruning. **Align with queries**: Analyze query filters. **Repartition**: By join key before shuffle....

Question 5

How would you deal with a situation where you had to work with a difficult team member?

Accepted Answer

**Situation**: Teammate missed deadlines; resisted code review. **Task**: Deliver project; improve collaboration. **Action**: (1) 1:1 to understand perspective—overload? unclear expectations? (2) Aligned on goals: project success. (3) Set clear expectations, deadlines. (4) Data-backed feedback: "Last 3 PRs took 5 days to merge; can we target 2?" (5) Escalated to manager after repeated attempts. **Result**: Communication improved; deadlines met; member engaged with feedback....

Question 6

Optimize a query fetching customer data with a rolling 6-month sales sum.

Accepted Answer

Rolling 6-month sum: use window functions. SELECT customer_id, SUM(amount) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS rolling_6m FROM sales. Or RANGE for actual 6 months. Optimize: partition table by customer_id or date; index on (customer_id, sale_date). Pre-aggregate in summary table if queried often. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 7

What is your notice period, and are you interviewing elsewhere?

Accepted Answer

**Situation**: Context of the challenge. **Task**: Your responsibility. **Action**: Specific steps, tools, collaboration. **Result**: Quantified outcome. Answer authentically and professionally. Example: "My notice period is X weeks. I'm exploring opportunities that align with my career goals—[this role/company] is among my top choices because of [specific reason]....

Question 8

What optimizations would you apply for partitioning strategies?

Accepted Answer

Partition optimization is a critical lever for cost and performance at scale. **Why it matters**: Misaligned partitions cause full-table scans; over-partitioning creates small-file hell (each file = metadata overhead + slower I/O). **Strategies**: (1) Align partition key with query filters—date, region—so the engine prunes at scan time. (2) Target 100MB–1GB per partition; under that, compaction merges small files....

Question 9

What technologies are you most comfortable with?

Accepted Answer

**Situation**: As a senior data engineer, I've worked across SQL, Spark, orchestration, and cloud. **Task**: Deliver reliable pipelines that scale. **Action**: Core stack: SQL (Snowflake, BigQuery, Spark SQL), Python, Spark/PySpark, dbt, Airflow/Prefect, cloud (AWS/GCP). Production experience: streaming (Kafka), lakehouse (Delta), ML pipelines. I focus on fundamentals—modeling, testing, observability—that transfer across tools....

Question 10

Write a SQL query to find employees earning the second-highest salary.

Accepted Answer

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1. Or: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Or DENSE_RANK: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM employees) WHERE r = 2. **Edge case**: Fewer than two employees—returns empty....

Fragma Data Systems SQL Interview Questions

Companies asking these questions

All 11 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Fragma Data Systems SQL Interview Questions

Companies asking these questions

All 11 Questions

More Interview Prep Guides

Get All Answers in PDF Format