Question 1

Compare PostgreSQL vs Snowflake. How do they handle duplicate record errors?

Accepted Answer

**Architectural Logic**: PostgreSQL is transactional RDBMS with strict constraint enforcement; Snowflake is a cloud warehouse with relaxed constraints—reflecting different design philosophies. **PostgreSQL**: PRIMARY KEY/UNIQUE constraints enforce uniqueness; INSERT of duplicates raises immediate error. Use ON CONFLICT (key) DO UPDATE for upsert. Row-level locking; ACID. **Snowflake**: No native PK enforcement; duplicates allowed by default....

Question 2

Explain ETL process flags and segregation of steps.

Accepted Answer

**Architectural Logic**: Flags enable observability, restartability, and audit. **Flags**: STARTED, RUNNING, SUCCESS, FAILED; custom: DATA_QUALITY_FAILED, PARTIAL_SUCCESS. **Segregation**: Extract, Transform, Load as distinct tasks; each has status. **Why**: Monitoring; resume from failed step; audit trail. **Example**: Log batch_id, step_name, status, rows_processed, timestamp. Orchestrators (Airflow) use task states. **Scalability**: Idempotent steps; flags enable partial rerun....

Question 3

Explain Union vs Union All in SQL.

Accepted Answer

**Architectural Logic**: UNION deduplicates; UNION ALL does not—semantic and performance differ. **UNION**: Combines; removes duplicates (implicit DISTINCT); slower (sort). **UNION ALL**: Combines; keeps all rows; faster. **Use UNION**: When dedup required. **Use UNION ALL**: When impossible or acceptable. **Example**: Disjoint date ranges = UNION ALL correct and faster. **Scalability**: UNION ALL avoids shuffle for dedup; UNION is O(n log n). **Cost**: UNION adds compute; know source data....

Question 4

Explain normalization and its disadvantages.

Accepted Answer

**Architectural Logic**: Normalization reduces redundancy; can hurt read performance. **Normalization**: 1NF–3NF, BCNF; eliminates redundancy; ensures integrity. **Advantages**: Consistency; efficient updates. **Disadvantages**: More joins; can hurt read performance; complexity. **Analytics**: Denormalization often preferred. **Trade-off**: Normalize OLTP for integrity; denormalize OLAP for reads. **Best Practice**: Use normalization where updates matter; denormalize where reads dominate....

Question 5

Explain peer code review and team lead review.

Accepted Answer

**Architectural Logic**: Peer vs lead review serve different purposes—quality and oversight. **Peer Review**: Teammates review before merge. Benefits: catch bugs; share knowledge; standards. Process: request review; reviewer examines code, tests, design; feedback; iterate. **Team Lead Review**: Lead/senior for critical changes; architecture; production impact. Additional gate. **Best Practice**: Focused reviews; constructive feedback; automate (lint, tests); define scope....

Question 6

Explain the difference between a clustered and non-clustered index.

Accepted Answer

**Architectural Logic**: Clustered = physical order; non-clustered = separate structure. **Clustered**: One per table; physical row order; leaf = data. **Non-clustered**: Separate structure; multiple per table; leaf = pointers. **Performance**: Clustered faster for range; non-clustered for lookups. **Best Practice**: Clustered on PK or high-range column; non-clustered on filter/join columns. Avoid over-indexing....

Question 7

Explain the difference between a fact table and a dimension table.

Accepted Answer

**Architectural Logic**: Fact = measures; dimension = context—different roles in star schema. **Fact**: Quantitative (amount, qty); FKs to dimensions; large, narrow; business events. **Dimension**: Descriptive (name, category); PKs for joins; smaller, wide; context (who, what, where, when). **Example**: fact_sales (amount, qty) + dim_product (name, category), dim_date. **Best Practice**: Fact for metrics; dimension for filtering/grouping. Surrogate keys in dimensions; conformed across facts....

Question 8

Explain the difference between a primary key and a unique key.

Accepted Answer

**Architectural Logic**: Both enforce uniqueness; PK is primary identity; unique is secondary. **Primary Key**: One per table; unique; NOT NULL; default clustered (SQL Server). **Unique Key**: Multiple per table; can allow NULL (DB-dependent). **Use**: PK for identity; unique for alternate identifiers (email, SSN). **Best Practice**: PK as main join key; unique for secondary lookups without duplicates....

Question 9

Find average salary by department, max average salary, and list of employees for the max salary department.

Accepted Answer

WITH dept_avg AS (SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department), max_dept AS (SELECT department FROM dept_avg ORDER BY avg_sal DESC LIMIT 1) SELECT e.* FROM employees e WHERE e.department = (SELECT department FROM max_dept). **Why CTE**: Readable; reusable. **Edge case**: Tied max avg—LIMIT 1 picks one; use RANK if need all....

Question 10

How do you make data ingestion fast with 5 tables of 50k records?

Accepted Answer

**Strategies**: (1) **Parallel loads**: 5 parallel jobs, one per table. (2) **Bulk insert**: COPY/BULK INSERT, not row-by-row. (3) **Batch size**: 5K–10K rows per batch. (4) **Staging**: Load to staging; single transaction swap. (5) **Indexes**: Disable during load; rebuild after. (6) **Network**: Co-locate (same region). **50K × 5**: Typically seconds with COPY....

Gartner SQL Interview Questions

Companies asking these questions

All 15 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Gartner SQL Interview Questions

Companies asking these questions

All 15 Questions

More Interview Prep Guides

Get All Answers in PDF Format