Question 1

Explain the differences between Data Warehouse, Data Lake, and Delta Lake

Accepted Answer

**Data Warehouse**: Structured, schema-on-write; optimized for SQL analytics (Snowflake, BigQuery). High compute cost, fast queries. **Data Lake**: Raw/semi-structured object storage (S3, ADLS); schema-on-read; low cost, flexible. **Delta Lake**: Open-source storage layer on a data lake adding ACID transactions, schema enforcement, time travel, upserts. **Why the distinction**: Warehouses scale compute and storage together; lakes decouple them....

Question 2

Design a Custom API that can query a backend server and return customer data such as the number of orders placed by a user based on their user ID

Accepted Answer

**Section 1 — The Context (The 'Why')**
The primary challenge for this design in SQL is balancing scale, cost, and reliability. At scale, naive approaches fail: single points of failure cause cascades, schema evolution breaks consumers, and over-provisioning explodes cost. Failure modes include silent data loss from non-idempotent writes, cascading job failures from tight coupling, and operational burden from manual intervention....

Question 3

Design the data model for an ETL pipeline that ingests data from a database and loads it into Snowflake

Accepted Answer

**Section 1 — The Context (The 'Why')**
The primary challenge for this design in SQL is balancing scale, cost, and reliability. At scale, naive approaches fail: single points of failure cause cascades, schema evolution breaks consumers, and over-provisioning explodes cost. Failure modes include silent data loss from non-idempotent writes, cascading job failures from tight coupling, and operational burden from manual intervention....

Question 4

Explain normalization in databases and its importance. Write an SQL query to handle SCD-1 or SCD-3

Accepted Answer

**Architectural Logic**: Normalization ensures integrity; SCD-1/3 handles dimension updates. **Normalization**: 1NF–3NF; reduces redundancy; consistency; efficient updates. **SCD-1**: `UPDATE dim SET col = new_val WHERE key = X`. **SCD-3**: `UPDATE dim SET prev_col = col, col = new_val, updated = NOW() WHERE key = X`. **MERGE**: `MERGE dim USING staging ON dim.key = staging.key WHEN MATCHED THEN UPDATE SET ...`....

Question 5

How soon could you join Meesho if you are selected?

Accepted Answer

**Situation**: Standard availability question. **Task**: Communicate realistically; show professionalism. **Action**: "I can join in [X] weeks after receiving the offer. This allows me to complete my notice period and hand off responsibilities. If there's urgency, I can discuss expediting with my current employer." **Result**: Clear expectation; builds trust....

Question 6

How would you clean the data by filtering out records with null values in user_id?

Accepted Answer

**SQL**: SELECT * FROM t WHERE user_id IS NOT NULL. **PySpark**: df.filter(col('user_id').isNotNull()) or df.na.drop(subset=['user_id']). **Best practice**: Log count dropped; validate business impact; consider quarantine for analysis....

Question 7

Managed vs Unmanaged Tables

Accepted Answer

Same as Managed vs External for Hive/Spark. In Delta Lake: managed = Delta manages files in table directory; unmanaged = external location. In Databricks: managed tables stored in default location; unmanaged use external path. Same principle: managed = drop deletes data; unmanaged/external = drop keeps data. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead. **Scalability trade-offs**: Profile before optimizing; validate on sample then full....

Question 8

SQL Query to Find Average Sessions per User within 30-day period

Accepted Answer

Avg sessions per user in 30 days: SELECT user_id, COUNT(DISTINCT session_id)/30.0 AS avg_sessions_per_day FROM events WHERE event_date >= CURRENT_DATE - 30 GROUP BY user_id. For 30-day window per user: use date range in WHERE. If sessions span dates: COUNT(DISTINCT session_id) / 30. Or use date diff for actual active days. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 9

What is the difference between Data Lakehouse, Delta Lake, and a Data Warehouse?

Accepted Answer

Data Warehouse: Optimized for structured analytics; schema-on-write; SQL-native. Data Lake: Raw/store-all; schema-on-read; supports unstructured. Lakehouse: Hybrid—lake storage with warehouse features (ACID, schema enforcement, BI). Delta Lake: Table format adding ACID, time travel, scalability on lake storage (e.g., S3). Delta enables lakehouse pattern. Summary: DW = curated analytics; Lake = raw storage; Lakehouse = lake + warehouse capabilities; Delta = implementation for lakehouse....

Meesho SQL Interview Questions

Companies asking these questions

All 9 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Meesho SQL Interview Questions

Companies asking these questions

All 9 Questions

More Interview Prep Guides

Get All Answers in PDF Format