NTT DATA Interview Questions

SQL NoSQL Schema Fixed, predefined Flexible Data model Tables (rows/columns) Document, key value, column family, graph ACID Full Varies (often eventual...

• What is the difference between SQL and NoSQL databases?

  SQL NoSQL Schema Fixed, predefined Flexible Data model Tables (rows/columns) Document, key value, column family, graph ACID Full Varies (often eventual consistency) Scaling Vertical Horizontal Query language SQL (standardized) Varies by DB Examples MySQL, PostgreSQL MongoDB, Cassandra, DynamoDB, Redis Use SQL for structured transactional data; NoSQL for high volume, flexible schema, or distributed workloads.

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

• How do you optimize SQL queries for performance?

  Use indexes on WHERE, JOIN, and ORDER BY columns Avoid SELECT — specify only needed columns Replace correlated subqueries with JOINs Use EXISTS over IN for large subquery sets Avoid functions on indexed columns in WHERE ( WHERE YEAR(date) = 2024 → use range) Use EXPLAIN / EXPLAIN ANALYZE to inspect execution plans Partition pruning : include partition columns in filters Use materialized views for frequently queried expensive aggregations

• What are some common data integrity issues you have encountered, and how did you resolve them?

  Duplicate records : source systems retransmitting events. Fixed with deduplication using ROW NUMBER() or Delta MERGE on a unique key. Null in required fields : upstream schema changes dropped mandatory fields. Added null checks at ingestion with quarantine for invalid records. Referential integrity violations : fact table rows with no matching dimension key. Added FK validation step before loading; orphaned records go to a reject table. Stale data : pipeline ran but source hadn't updated. Added

• Explain the concept of normalization and denormalization in databases.

  Normalization : organizing tables to eliminate redundancy and ensure data consistency. Normal forms: 1NF : atomic values, no repeating groups 2NF : no partial dependencies on composite PK 3NF : no transitive dependencies Result: less storage, cleaner updates, but more JOINs needed. Denormalization : intentionally adding redundancy by merging tables or duplicating columns to improve read performance. Used in data warehouses and OLAP where reads dominate. Trade off: faster reads, slower writes, ri

• What is ETL, and how have you implemented it?

  ETL (Extract, Transform, Load) is the process of moving data from sources to a target system: Extract : connect to source (DB, API, files, streaming) and read data — incrementally where possible Transform : clean (nulls, dupes), cast types, apply business logic (joins, aggregations, derived fields), validate Load : write to target (warehouse, lake) using appropriate strategy (append, upsert, full replace) Tools used : ADF for orchestration, PySpark for large scale transformations, dbt for SQL tr

• What is data partitioning, and why is it important?

  Partitioning divides data into logical subsets based on a column value, stored in separate files or directories. Why it matters : Query performance : Spark/SQL engines skip partitions that don't match the filter (partition pruning) Parallelism : each partition can be processed independently on a separate executor Manageability : easier to drop or reload a specific date partition Example : PARTITIONED BY (year, month) — a query with WHERE year=2024 AND month=01 reads only that partition instead o

• Explain the concept of data shuffling in Spark.

  A shuffle happens when Spark needs to redistribute data across partitions — triggered by wide transformations: groupBy , join , distinct , repartition , sortBy . During a shuffle: 1. Each executor writes its partition data to local disk (shuffle write) 2. Data is transferred over the network to the appropriate executor 3. The receiving executor reads and processes it (shuffle read) Why it's expensive : disk I/O + serialization + network transfer. Minimizing shuffles is key to Spark performance: