Question 1

Describe a scenario where partitioning and bucketing would improve query performance.

Accepted Answer

Situation: An events table with billions of rows serving time-range and user-level analytics. Task: Achieve sub-second query latency while controlling storage and compute costs. Why Partitioning: Partition pruning at read time eliminates entire data scans—a query filtering by date_range only touches relevant partition dirs. This reduces I/O by orders of magnitude (e.g., 365 partitions → scan 1 vs all)....

Question 2

When would you choose a Snowflake schema over a Star schema?

Accepted Answer

Star: One fact, denormalized dimensions—simple, fewer joins, fast. Snowflake: Normalized dimensions (e.g., dim_product → dim_category → dim_category_group)—more joins, less redundancy. Why Snowflake: When dimension tables are large and shared—a single dim_category serves many products; denormalizing would duplicate millions of rows. Avoids inconsistent attributes across copies (e.g., category name updated in one place). Storage: Snowflake saves space when hierarchy is deep and wide....

Question 3

Implement a query to find the top 5 customers by total sales amount.

Accepted Answer

**Architectural Logic**: Two approaches. 1. GROUP BY + ORDER BY + LIMIT: SELECT customer_id, SUM(sales_amount) total_sales FROM sales GROUP BY customer_id ORDER BY total_sales DESC LIMIT 5. 2. Window: SELECT * FROM (SELECT customer_id, SUM(sales_amount) total_sales, RANK() OVER (ORDER BY SUM(sales_amount) DESC) rk FROM sales GROUP BY customer_id) t WHERE rk <= 5. **Why**: LIMIT is simpler, stops early in some engines....

Question 4

Write an SQL query to find duplicate emails in a users table.

Accepted Answer

**Architectural Logic**: Simple: SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1. With context: SELECT email, COUNT(*) cnt, ARRAY_AGG(user_id) ids FROM users GROUP BY email HAVING COUNT(*) > 1. Window: SELECT DISTINCT email FROM (SELECT email, COUNT(*) OVER (PARTITION BY email) cnt FROM users) t WHERE cnt > 1. **Why**: GROUP BY + HAVING is standard; window useful if you need other columns per duplicate....

Question 5

Compare OLTP and OLAP systems in the context of financial transactions.

Accepted Answer

**Architectural Logic**: OLTP and OLAP solve orthogonal problems—transactional integrity vs analytical throughput—with different scaling and cost models. **OLTP**: Optimized for high-throughput, low-latency writes; ACID; normalized schemas; row-level locking. Powers core banking, trade execution. Scales vertically and via read replicas; write path is critical. **OLAP**: Denormalized star/snowflake; columnar storage; batch-oriented; powers risk aggregation, P&L, regulatory reporting....

Question 6

Describe a challenging project where you optimized a complex ETL process.

Accepted Answer

**Situation**: Daily ETL processing 50M+ rows took 6+ hours, blocking downstream reports and risking SLA. **Task**: Reduce runtime to under 1 hour while maintaining data quality. **Action**: (1) Profiled with Spark UI and query plans; identified full-table scans and unnecessary shuffles. (2) Partitioned source by date; enabled partition pruning. (3) Replaced cross-joins with broadcast joins for small dimensions. (4) Implemented incremental processing using watermark columns....

Question 7

Describe a scenario where you would use a CROSS JOIN vs. an INNER JOIN.

Accepted Answer

**Architectural Logic**: INNER JOIN = matching rows; CROSS JOIN = Cartesian product. **INNER JOIN**: Orders with valid customers; facts with dimensions; typical relational logic. **CROSS JOIN**: Generating combinations—all product×store for matrix, date×region for sparse facts. Example: `SELECT p.product, s.store FROM products p CROSS JOIN stores s`. **Why CROSS JOIN is Risky**: N×M row explosion; 1K products × 1K stores = 1M rows....

Question 8

Explain indexing and its impact on database performance.

Accepted Answer

**Architectural Logic**: Indexes speed reads; cost writes—balance by workload. **Mechanism**: B-tree (range), hash (equality), bitmap (low cardinality). Speeds WHERE, JOIN, ORDER BY. **Impact**: Read faster; INSERT/UPDATE slower (maintenance). **Scalability**: Strategic indexing; avoid over-indexing. **Warehouses**: BigQuery, Snowflake use partitioning + clustering instead of B-tree. **OLTP**: Indexes critical....

Question 9

Explain your approach to optimizing a slow-running query on a table with billions of rows.

Accepted Answer

**Approach**: (1) Execution plan—identify full scans, shuffle size, sort. (2) Partition pruning—filter on partition key (e.g., dt) before scan. (3) Predicate pushdown—ensure filters reach storage layer. (4) Index/Clustering—B-tree on filter; Z-order on multi-column. (5) Reduce scope—narrow columns; APPROX_COUNT_DISTINCT when exact not needed. (6) Incremental—process only new/changed data. (7) Materialized aggregation—pre-aggregate for frequent patterns....

Question 10

Given a complex nested query, how would you refactor it for better readability and efficiency?

Accepted Answer

**Refactor steps**: (1) Extract nested SELECTs to CTEs (WITH)—named, reusable. (2) Replace correlated subqueries with JOINs—planner optimizes better. (3) Window functions instead of self-joins for row comparisons. (4) Materialize hot paths—temp table if CTE scanned multiple times. (5) Push predicates into CTEs. **Why**: Readability = maintainability; optimizer may flatten CTEs or may not—benchmark....

Goldman Sachs SQL Interview Questions

Difficulty Breakdown

Key Topics Covered

How to Use This Guide

Companies asking these questions

All 16 Questions

More Interview Prep Guides

Unlock All Expert Answers