Question 1

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

Accepted Answer

**Using subquery with MAX**:
```sql
SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);
```

**Using LIMIT/OFFSET** (MySQL, PostgreSQL):
```sql
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
```

**Using DENSE_RANK** (ANSI SQL, most robust):
```sql
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employee
) t
WHERE rk = 2;
```

**Architectural Logic & Trade-offs**:
- **Subque...

Question 2

Compare Redshift, BigQuery, and Snowflake in terms of cost, performance, and scalability.

Accepted Answer

**Architectural Logic**: Each warehouse optimizes for different workload and cost profiles. **Redshift**: Cluster-based; provisioned nodes; predictable cost for stable workloads. Manual scaling; strong for high-volume, consistent batch. Requires tuning (sort keys, distribution keys). **BigQuery**: Serverless; pay-per-query; auto-scales to zero. Best for variable, ad-hoc analytics; no provisioning. **Snowflake**: Hybrid; compute and storage separate; multi-cloud....

Question 3

Describe a scenario where you had to collaborate with a cross-functional team to deliver a solution.

Accepted Answer

**Situation**: Customer 360 dashboard required data from engineering (events), marketing (campaigns), finance (transactions). **Task**: Deliver in 8 weeks with aligned ownership. **Action**: Formed cross-functional working group; defined RACI and SLAs. Created integration contracts (schemas, cadence); used weekly syncs and shared Jira board. Resolved conflicts by aligning on business outcomes; escalated blockers early. Used schema registries for versioning. **Result**: Delivered in 8 weeks....

Question 4

Describe how you would implement Slowly Changing Dimensions (SCD) in an ETL workflow.

Accepted Answer

**Architectural Logic**: SCD implementation depends on type; MERGE is the workhorse for atomicity. **SCD1**: UPDATE dim SET col = new_val WHERE key = X. **SCD2**: INSERT new row with effective_from; UPDATE current row's effective_to; set current_flag. **SCD3**: Add prev_col; on change, shift current→prev. **ETL Flow**: (1) Extract changed records. (2) Lookup dimension. (3) Apply SCD logic (MERGE or INSERT/UPDATE). (4) Generate surrogate keys....

Question 5

Describe strategies for optimizing a slow-running query on a massive dataset.

Accepted Answer

**Architectural Logic**: Optimize in order—partition pruning, clustering, query rewrite, materialization, approximate methods. **Strategies**: (1) Partition pruning—predicate on partition column. (2) Clustering/sort keys—colocate related data. (3) Query rewrite—avoid SELECT *; push filters; reduce shuffle. (4) Materialized views—pre-aggregate common patterns. (5) Approximate—sampling, HyperLogLog when exact not needed. (6) Incremental—process only changed data....

Question 6

Discuss a situation where you had to balance technical priorities and business goals.

Accepted Answer

**Situation**: Engineering wanted refactor; business needed new reports. **Task**: Balance both without blocking. **Action**: Prioritized by impact—delivered high-value reports with minimal changes first. Created backlog for refactor; communicated timeline. Used RICE or similar; involved stakeholders in prioritization. Scheduled tech-debt sprints. **Result**: Business needs met; technical debt addressed in phases. **Leadership**: Transparent prioritization; aligned on shared goals....

Question 7

Explain the difference between Star and Snowflake schemas. When would you choose one over the other?

Accepted Answer

**Star**: Central fact table with denormalized dimensions; one join per dimension. **Snowflake**: Normalized dimensions with hierarchies (e.g., region → country → city). **Why choose Star**: Query simplicity drives performance—fewer joins mean faster execution; BI tools generate simpler SQL; caching works better....

Question 8

Explain the use of surrogate keys vs. natural keys in data modeling.

Accepted Answer

**Natural keys**: Business identifiers (product_code, SSN); can change; composite; meaningful. **Surrogate keys**: System-generated (auto-increment, UUID); stable; single column; no business meaning. **Why surrogate for warehousing**: SCD Type 2 requires stable PK when natural key changes; simpler joins (single int vs. composite); hash-based dedup. **Why keep natural**: Validation, debugging, audit. **Trade-off**: Surrogate adds column; natural can leak PII....

Question 9

Given an unoptimized query execution plan, how would you diagnose and improve performance?

Accepted Answer

**Diagnose**: (1) EXPLAIN ANALYZE—actual vs. estimated rows; large gap = stale stats. (2) Identify: Seq Scan on large table, nested loop with high cost, sort spill. (3) Check join order—small table should drive when possible. **Improve**: Add index on filter/join columns; ANALYZE; rewrite (join vs. subquery); partition prune; materialized view....

Question 10

Kafka Partitioning: How would you ensure even load distribution across Kafka partitions in a high-volume system?

Accepted Answer

Even Kafka partition distribution: (1) Use meaningful keys—hash(key) % num_partitions; avoid hot keys (e.g., few keys dominate). (2) Use composite keys—e.g., customer_id + timestamp. (3) Add random component for high-cardinality keys. (4) Monitor partition lag and rebalance. (5) Increase partitions if needed (creates new partitions, doesn't rebalance existing data). (6) Use custom partitioner when default hash is skewed. (7) Consider key salting for hot partitions....

BCG SQL Interview Questions

Companies asking these questions

All 14 Questions

More Interview Prep Guides

Get All Answers in PDF Format

BCG SQL Interview Questions

Companies asking these questions

All 14 Questions

More Interview Prep Guides

Get All Answers in PDF Format