Contents

1. Data Engineer Responsibilities........................................................................3

2. What is a Data Warehouse & How Does It Look Like?.....................................4
4. Defining Data Modeling.................................................................................. 4
4. Data-Centric vs. Pipeline-Centric....................................................................5
5. How is a Data Warehouse Different from Spark?............................................5
6. What are slowly changing dimensions (SCDs), and how do you handle them?
5
7. What is a watermark in streaming systems, and why is it used?....................6
8. Discuss the importance of idempotency in data processing pipelines and how
to achieve it........................................................................................................... 6
9. What is data lineage, and why is it important in data engineering?...............6
10. File Modes for Reading Data in Spark..........................................................7
11. Modes Available in Spark DataFrame Writer................................................7
12. Difference Between Client Mode and Cluster Mode in spark-submit......7
13. what is Job, stage and task spark, how it is created in spark......................9
14. what are narrow and wide transformation in spark.....................................9
15. How do you optimize join operations in a distributed processing
environment like Spark?...................................................................................... 11
16. What is eventual consistency, and when is it acceptable in a data system?
11
17. Explain how you would design a data pipeline to handle real-time analytics
on streaming data............................................................................................... 11
18. kappa and lambda architecture ,and difference........................................12
19. Explain exactly-once semantics in stream processing and how frameworks
achieve it............................................................................................................. 13
20. What is GitHub? How is it different from Git?............................................13
21. What is a pull request in GitHub?..............................................................13
22. How do you resolve merge conflicts in GitHub?........................................14
23. What is GitHub Actions?............................................................................ 14
24. What are GitHub webhooks?.....................................................................14
25. What is a namespace in Python?........................................................14
26. What are modules and packages in Python?.............................................14
27. What are data types and data structures in Python?.................................15
28. Differences Between List, Dictionary, Tuple, and Set................................15
29. What is an enumerator in Python?............................................................15
30. What is unit testing in Python?..................................................................15
31. What is static analysis of a Python script?.................................................15
32. Difference Between Iterator and Iterable..................................................16
33. Difference Between Generator and Iterator..............................................16
34. Difference Between Generator and List Comprehension...........................16
35. What is a closure?..................................................................................... 17
36. What is a free variable?.............................................................................17
37. What are raise and assert?........................................................................17
38. What is defaultdict?................................................................................... 17
39. What is a frozenset?.................................................................................. 17
40. What is a namedtuple?..............................................................................17
41. What is a class method and an instance method?....................................18
42. What are attributes and methods in a class?............................................18
43. What is the self parameter in a class method?..........................................18
44. What is an instance and an object in Python?...........................................18
45. What is MRO in Python?............................................................................ 18
46. What is the difference between is and == in Python?....................18
47. Difference Between Shallow Copy and Deep Copy....................................19
48. What is a Context Manager in Python?......................................................19
49. What is Docker?......................................................................................... 19
50. What is a Container?................................................................................. 19
51. What is Docker Engine?.............................................................................20
52. What is Docker Desktop?...........................................................................20
53. What is a Container Image and a Docker Image?......................................20
54. What is Docker Compose?.........................................................................20
55. What is a Dockerfile?................................................................................. 21
56. How Can You Persist Data in Docker Containers?......................................21
57. What Are the Different Components of Docker?........................................21
58. What is the Difference Between Virtual Machines and Docker Containers?
21
59. What is a Materialized View, and how does it differ from a Regular View? 22
60. What are Database Partitions, and why are they useful?..........................22
61. What is the difference between a Correlated Subquery and a Non-
Correlated Subquery?.......................................................................................... 23
62. Explain the EXPLAIN Command and Query Execution Plan.......................23
63. What is the Difference Between Sharding and Partitioning?......................23
64. How Does Indexing Affect SELECT, INSERT, UPDATE, and DELETE?...........24
65. What is a Recursive CTE, and How Is It Used?...........................................24
66. What are Deadlocks in SQL, and How Can You Prevent Them?.................24
67. How Do You Optimize SQL Queries for Large Datasets?............................25
68. What is a Surrogate Key, and When Should It Be Used Over a Natural Key?
25
69. Explain Row-Level Security (RLS) in SQL...................................................25
70. How Do NULL Values Affect SQL Aggregations and Comparisons?............26
71. What is the Difference Between ROW_NUMBER(), RANK(), and
DENSE_RANK()?................................................................................................... 26
72. What are Triggers in SQL, and How Are They Used?..................................26
73. What Are Isolation Levels in SQL Transactions?.........................................26
74. What is the correct execution priority order in SQL?.................................27

1. Data Engineer Responsibilities

A data engineer is an IT professional responsible for designing, building, and
maintaining data infrastructure for data collection, management,
transformation, and access. They work closely with data scientists and
analysts to ensure that data is available and usable for analysis and decision-
making.
Key Responsibilities:
 Design and Build Data Pipelines:
Data engineers create pipelines to move data from source systems to
storage systems (e.g., data warehouses or lakes). This includes cleaning,
transforming, and loading (ETL) processes.
 Maintain Data Infrastructure:
Manage servers, databases, and networks. Monitor data quality and
performance to ensure reliability.
 Collaborate with Data Scientists and Analysts:
Understand their data needs, provide access, and support the deployment
of data models.
 2. What is a Data Warehouse & How Does It
Look Like?
A data warehouse is a centralized repository that stores large volumes of data
from various organizational sources.
 Purpose: Efficient querying and analysis for business intelligence and
reporting.
 Goal: Consolidate data into a unified view.
Components of a Data Warehouse:
1. Database: Stores data from multiple sources.
2. Data Warehouse Management System (DWMS): Manages data and
provides analytical access.
3. ETL Tools:
o Extract, Transform, and Load data into the warehouse.

o Examples: Informatica PowerCenter, Oracle Data Integrator,

Microsoft SSIS.

4. Defining Data Modeling

Data modeling is the process of organizing and defining data to create a clear
representation of the information.
Components of Data Modeling:
1. Entities: Represent real-world objects (e.g., customers, products).
2. Relationships: Define links between entities (e.g., customer orders).
3. Data Types: Specify formats (e.g., text, number, date).
4. Constraints: Ensure data integrity (e.g., unique customer IDs).
Benefits:
 Improved Data Quality: Ensures accuracy and reliability.
 Enhanced Access: Simplifies finding and analyzing information.
 Better Communication: Provides shared understanding across teams.
Types of Data Models:
1. Conceptual: High-level overview.
2. Logical: Detailed description.
3. Physical: Specific implementation in storage systems.
 4. Data-Centric vs. Pipeline-Centric
Database-Centric Engineer:
 Focus: Design and optimization of database systems.
 Tasks: Schema design, query optimization, and ensuring data integrity.
 Example: Designing a normalized database for an e-commerce platform.
Pipeline-Centric Engineer:
 Focus: End-to-end data processing pipelines.
 Tasks: Data ingestion, transformation, and integration.
 Example: Developing ETL pipelines for cloud migration.

5. How is a Data Warehouse Different from a Database?

Feature Data Warehouse Database

Purpose Analytical queries and reporting. Transactional applications.

Schema Dimensional schema (optimized for Normalized schema (minimizes

Structure analysis). redundancy).

Complex query handling on large

Optimization Efficient for daily transactions.
datasets.

5. How is a Data Warehouse Different from

Spark?
Feature Data Warehouse Spark

Optimized for read-only analytical Distributed computing for data processing and
Purpose
queries. analysis.

Workloa
Read-only. Both read and write.
ds

Supports structured, semi-structured, and

Schema Well-defined schema.
unstructured data.

6. What are slowly changing dimensions

(SCDs), and how do you handle them?
SCDs are dimensions in a data warehouse that change slowly over time.
o Types:
  Type 1: Overwrite old data (no history).
 Type 2: Maintain history by adding a new row for each
change.
 Type 3: Maintain partial history by adding a new column for
the change.

7. What is a watermark in streaming

systems, and why is it used?
A watermark is a threshold that defines the maximum delay allowed for
late-arriving data in stream processing.
o Use Case: Ensures correctness by processing late data without
indefinite waiting

8. Discuss the importance of

idempotency in data processing
pipelines and how to achieve it.
Importance:
o Reliability: Ensures consistent results despite retries or failures.

o Data Integrity: Prevents duplicate processing and side effects.

Achieving Idempotency:
o Unique Identifiers: Use unique keys to track processed records.

o Stateless Operations: Design functions that don't rely on external

state.
o Idempotent APIs: Implement APIs that handle repeated requests
gracefully.
o Database Constraints: Use primary keys or unique constraints to
prevent duplicates.

9. What is data lineage, and why is it

important in data engineering?
o Data Lineage: Tracks the origin, movement, and transformations
of data throughout its lifecycle.
o Importance:
  Ensures traceability for debugging or audits.
 Helps identify bottlenecks and inconsistencies in pipelines.

10. File Modes for Reading Data in Spark

When reading data into Spark, modes control how invalid data is handled:
 DROPMALFORMED: Skips invalid rows (most common and efficient).
 FAILFAST: Fails on encountering invalid data (useful for debugging).
 PERMISSIVE: Reads all rows, even with invalid data (useful for corrupted
sources).

11. Modes Available in Spark DataFrame

Writer
1. Overwrite: Overwrites existing data in the output directory.
2. Append: Adds new data to existing data.
3. ErrorIfExists: Throws an error if the output directory exists.
4. Ignore: Skips writing if the output directory already exists.

Highlights:
 Data engineers focus on designing pipelines, maintaining infrastructure,
and collaborating with data scientists.
 Data warehouses serve analytical needs, unlike transactional databases or
distributed frameworks like Spark.
 Data modeling is essential for organizing and leveraging data effectively.
 Spark offers flexible modes for handling data ingestion and writing.

12. Difference Between Client Mode

and Cluster Mode in spark-submit.
1. Client Mode
 Driver Location: The driver runs on the machine where the spark-submit
command is executed (your local machine).
 Communication: The driver communicates with the cluster's worker
nodes to coordinate tasks and collect results.
  Use Case:
o Ideal for interactive sessions or when immediate debugging is
required.
o Suitable when the submitting machine has stable network
connectivity to the cluster.
 Example Scenario: Running Spark on your laptop, where you can
monitor and control the job directly.
Advantages:
 Easier to debug and monitor as logs are accessible locally.
 Ideal for development and testing environments.
Limitations:
 Relies on the stability of the local machine; if the machine disconnects,
the job fails.
 Not suitable for long-running jobs submitted from remote machines.

2. Cluster Mode
 Driver Location: The driver runs on one of the worker nodes within the
cluster.
 Communication: Tasks are coordinated entirely within the cluster, and
the submission machine is not directly involved during execution.
 Use Case:
o Best for production environments or jobs submitted from remote
machines.
o Allows for better fault tolerance, as the driver remains within the
cluster even if the submission machine disconnects.
 Example Scenario: Submitting a Spark job from a remote machine to run
on a cluster in AWS EMR or Kubernetes.
Advantages:
 No dependency on the submission machine's availability or stability.
 Supports fault-tolerant execution for long-running batch jobs.
Limitations:
 Harder to debug directly, as logs are stored on the cluster's worker nodes.
 Slightly more complex setup for accessing logs and monitoring progress.
Feature Client Mode Cluster Mode

Driver Runs on a worker node in the

Runs on the submission machine.
Location cluster.

Requires a stable connection No dependency on the

Network
between the driver and worker submission machine after
Dependency
nodes. submission.

Development, debugging, and Production jobs and remote

Use Case
interactive applications. submissions.

Fault Low – the driver fails if the High – the driver remains
Tolerance submission machine disconnects. within the cluster.

Log Logs available on the submission Logs stored on the worker

Accessibility machine. node running the driver.

13. what is Job, stage and task spark,

how it is created in spark.
 Job:
A Job is a complete task triggered by an action like saving data, counting
rows, or collecting results. It's the entire work Spark needs to do to
complete the action.
 Stage:
A Stage is a smaller step within a Job. It’s a group of tasks that can run
without moving data between cluster nodes (no shuffle).
 Task:
A Task is the smallest unit of work in Spark. It processes a single partition
of data in a Stage and runs on a worker node

How It Works Together

 Job: Spark starts a Job for an action.
 Stages: The Job is divided into Stages based on shuffle boundaries.
 Tasks: Each Stage is split into Tasks, one per data partition, and run on
the cluster.
 14. what are narrow and wide
transformation in spark.

Transformations in Spark are operations that create a new RDD (or DataFrame)
from an existing one. These are categorized as narrow or wide based on how
data is shuffled across partitions.

1. Narrow Transformation
 Definition: A transformation where each output partition depends on a
single input partition.
 Key Feature: No data shuffling between partitions; data stays on the
same node.
 Examples:
o map()

o filter()

o flatMap()

 Use Case: Efficient because no network communication is required.

2. Wide Transformation
 Definition: A transformation where each output partition depends on
multiple input partitions.
 Key Feature: Data is shuffled across partitions, requiring network
communication.
 Examples:
o groupByKey()

o reduceByKey()

o join()

o distinct()

 Use Case: Necessary for operations that require data aggregation or

reorganization.

Key Difference:

Feature Narrow Transformation Wide Transformation

Dependen One-to-one between input and One-to-many or many-to-one

Feature Narrow Transformation Wide Transformation

cy output partitions. between partitions.

Shuffling No shuffling across partitions. Data is shuffled across partitions.

Performan Faster and less resource-

Slower due to network overhead.
ce intensive.

Examples map, filter, flatMap groupByKey, join, reduceByKey

15. How do you optimize join operations

in a distributed processing environment
like Spark?
Optimization Techniques:
o Broadcast Joins: Broadcast small datasets to all nodes to avoid
shuffling.
o Partitioning Strategy: Co-partition datasets on join keys.

o Data Skew Handling: Detect and mitigate skewed data to balance

workload.
o Caching Intermediate Results: Cache frequently accessed data
to reduce computation.
o Predicate Pushdown: Filter data early to reduce the amount
processed.

16. What is eventual consistency, and

when is it acceptable in a data system?
Eventual Consistency is a consistency model where updates to a distributed
system will propagate to all nodes, but not necessarily immediately.
Acceptable When:
o Use Cases Tolerate Latency: Applications where real-time
consistency is not critical (e.g., social media feeds).
o High Availability is Prioritized: Systems that must remain
operational despite network issues.
 17. Explain how you would design a data
pipeline to handle real-time analytics on
streaming data.
Design Steps:
o Data Ingestion: Use scalable messaging systems like Apache
Kafka.
o Stream Processing: Employ real-time processing frameworks like
Apache Flink or Spark Structured Streaming.
o Data Storage: Use databases optimized for real-time analytics,
such as Druid or ClickHouse.
o Visualization and Querying: Integrate with tools like Apache
Superset or Grafana.
o Scalability and Fault Tolerance: Implement auto-scaling and use
checkpointing mechanisms.

18. kappa and lambda architecture ,and

difference
 Lambda Architecture uses both batch and stream processing,
making it suitable for complex systems that need historical and real-time
data analysis.
 Kappa Architecture simplifies data processing by relying on
streaming only, making it ideal for real-time and event-driven
applications.
Choosing Between Lambda and Kappa
 Choose Lambda:
o When both batch and real-time processing are required.

o When historical accuracy and completeness are critical.

o For complex use cases combining real-time dashboards and

historical reports.
 Choose Kappa:
o For real-time data pipelines with minimal historical batch processing
needs.
o When simplicity and low latency are the priorities.

o For applications that rely heavily on event-driven architectures or

stream processing.
Key Differences Between Lambda and Kappa Architecture

Aspect Lambda Architecture Kappa Architecture

Processing Batch Layer + Speed Layer + Single streaming layer +

Layers Serving Layer Serving Layer

High: Requires maintaining

Low: Unified architecture for
Complexity separate batch and stream
real-time processing.
layers.

Separate codebases for batch Single codebase for all data

Codebase
and stream processing. processing.

Reprocessin Historical data is reprocessed in Replay event logs to reprocess

g the batch layer. historical data.

Provides both high-latency Provides low-latency

Latency
(batch) and low-latency (speed). processing.

Streaming logs as the primary

Data Storage Batch storage + streaming logs
source of truth.

Suitable for systems requiring Best for real-time-only systems

Use Case
real-time + batch analytics. or simple use cases.

Kafka, Flink, or Spark

Hadoop, Spark, Storm for batch
Examples Streaming for real-time
and stream processing.
processing.

19. Explain exactly-once semantics in

stream processing and how frameworks
achieve it.
Exactly-once semantics ensure that each record in a data stream is processed
only once, even in the presence of failures.
Achieved Through:
o Atomic Writes: Ensuring that writes to external systems are
atomic.
o Stateful Operators with Checkpointing: Periodically saving the
state to persistent storage.
o Transactional Messaging Systems: Using systems like Kafka
with transactional guarantees.
o Idempotent Operations: Designing operations that can safely be
retried without side effects.
Frameworks Implementing It:
o Apache Flink: Provides end-to-end exactly-once guarantees.

o Apache Spark Structured Streaming: Supports exactly-once

processing with certain sinks.

20. What is GitHub? How is it different

from Git?
GitHub is a web-based platform for version control and collaboration using
Git.
o Git is a version control system for managing source code.

o GitHub provides hosting for Git repositories with additional features

like pull requests, issue tracking, and CI/CD integration.

21. What is a pull request in GitHub?

A pull request is a request to merge changes from your branch (or fork)
into another branch. It allows code review and discussion before merging.

22. How do you resolve merge conflicts

in GitHub?
o Identify the conflicting files after attempting to merge.

o Open the files and manually resolve the conflicts by editing the
code.
o Mark the conflict as resolved using git add.

o Commit the changes and push the branch to GitHub.

23. What is GitHub Actions?

GitHub Actions is a CI/CD tool for automating workflows directly
within GitHub. It helps in building, testing, and deploying code with
YAML-based configuration.
24. What are GitHub webhooks?
Webhooks are automated messages triggered by events in a
repository (e.g., pushing code). They notify external systems about
changes or updates.

25. What is a namespace in Python?

A namespace is a container that holds names (variables, functions,
objects) mapped to corresponding objects in memory. It helps avoid
naming conflicts.
Types of Namespaces:
 Local Namespace: Exists within a function and contains variables
defined in it.
 Global Namespace: Exists at the script level and contains variables and
functions defined outside any function.
 Built-in Namespace: Contains Python's built-in functions and objects,
such as print() and len().

26. What are modules and packages in

Python?
 Module: A Python file containing code such as functions, variables, and
classes, which can be imported and reused in other programs.
 Package: A collection of modules organized in a directory with an
__init__.py file. Packages help organize code hierarchically.

27. What are data types and data

structures in Python?
 Data Types: Define the type of data, such as int, float, str, and bool.
 Data Structures: Containers used to store and organize data efficiently,
such as list, dict, tuple, and set.
28. Differences Between List, Dictionary,
Tuple, and Set

29. What is an enumerator in Python?

An enumerator allows iterating through a sequence while keeping
track of the index. It is created using the enumerate() function.

30. What is unit testing in Python?

Unit testing is a method of testing individual units or components of
a program to ensure they work as expected. Python's unittest
module provides tools for creating and running unit tests.

31. What is static analysis of a Python

script?
Static analysis examines code without executing it to identify
potential errors, enforce coding standards, and optimize
performance. Tools like pylint are commonly used for this purpose.

32. Difference Between Iterator and

Iterable
Feature Iterator Iterable

Definition Object used to Object that can be

 Feature Iterator Iterable

traverse data looped over

Requires
Yes No
__next__

List, tuple, string,

Example Output of iter()
etc.

33. Difference Between Generator and

Iterator
Feature Generator Iterator

Special type of General object with

Definitio
iterator created __iter__ and
n
with yield __next__

Creates Yes, maintains state Requires manual

State automatically state management

34. Difference Between Generator and

List Comprehension
List
Feature Generator
Comprehension

Memory Efficient, computes Stores entire list in

Usage on demand memory

Syntax Uses () Uses []

35. What is a closure?

A closure is a function that retains access to variables in its
enclosing scope even after the outer function has finished
executing.

36. What is a free variable?

A free variable is a variable used in a function but not defined in
that function. It is instead defined in an enclosing scope.
37. What are raise and assert?
 Raise: Used to explicitly raise an exception.
 Assert: Used for debugging by testing conditions that must be true during
execution.

38. What is defaultdict?

A defaultdict is a dictionary subclass from the collections module
that provides a default value for non-existent keys.

39. What is a frozenset?

A frozenset is an immutable version of a set. It cannot be modified
after creation but retains all set operations like union and
intersection.

40. What is a namedtuple?

A namedtuple is a subclass of tuples in the collections module that
allows accessing elements by name instead of index.

41. What is a class method and an

instance method?
 Class Method: A method bound to the class, not the instance. Defined
using @classmethod.
 Instance Method: A method bound to an instance of the class.

42. What are attributes and methods in a

class?
 Attributes: Variables that hold data about an object.
 Methods: Functions defined in a class that perform operations on
attributes.
43. What is the self parameter in a class
method?
The self parameter represents the instance of the class and allows
access to its attributes and methods.

44. What is an instance and an object in

Python?
 Instance: A specific realization of a class.
 Object: A generic term for any instance of a class.

45. What is MRO in Python?

MRO (Method Resolution Order) defines the order in which
Python looks for methods in a hierarchy of classes. It follows the C3
Linearization Algorithm.

46. What is the difference between

is and == in Python?
 is: Checks if two references point to the same object.
 ==: Checks if two objects have the same value.

47. Difference Between Shallow Copy

and Deep Copy
Shallow
Feature Deep Copy
Copy

Copies the
Creates an
object but
Definiti independent copy
references
on of the object and
nested
all nested objects.
objects.

Module [Link]() [Link]()

48. What is a Context Manager in
Python?
A context manager is a construct that manages resources using
with statements, ensuring proper resource cleanup (e.g., closing
files).
Example: with open('[Link]') as f: automatically closes the file
when the block ends.

49. What is Docker?

Docker is an open-source platform for automating the deployment
of applications in lightweight, portable containers.
It allows developers to package applications and their
dependencies into a single unit (container) for consistency across
environments.

50. What is a Container?

A container is a lightweight, standalone, and executable package
that includes everything needed to run an application, such as code,
runtime, libraries, and dependencies. Containers share the host
system's kernel but are isolated from each other.

51. What is Docker Engine?

Docker Engine is the core component of Docker. It is a client-server
application that creates and manages Docker containers.
It consists of:
 Server (Daemon): Handles requests from the Docker client and manages
containers.
 REST API: Provides a programmatic way to interact with the daemon.
 CLI (Command-Line Interface): Allows users to interact with Docker.
52. What is Docker Desktop?
Docker Desktop is an easy-to-use application for managing Docker
on your local system. It includes Docker Engine, Docker CLI, Docker
Compose, and a user-friendly GUI for managing containers, images,
and configurations.

53. What is a Container Image and a

Docker Image?
 Docker Image: A Docker-specific container image that serves as a
template to create containers. It contains the application, dependencies,
and an environment configuration.
 Container Image: A general term for an image used to create containers.
Docker images are a specific implementation of container images.
 Correct Usage: "Docker image" is specific to Docker, while "container
image" is a generic term.

54. What is Docker Compose?

Docker Compose is a tool for defining and managing multi-container
applications. It uses a [Link] file to specify the
services, networks, and volumes required by the application. With a
single command (docker-compose up), all services are started
together.

55. What is a Dockerfile?

A Dockerfile is a script-like text file containing instructions to build
a Docker image. It defines the application's environment,
dependencies, and configuration.

56. How Can You Persist Data in Docker

Containers?
Data persistence in Docker containers can be achieved using:
 Volumes: A preferred way to store data persistently. Volumes are
managed by Docker and exist outside the container's filesystem.
 Bind Mounts: Links a specific host directory to a container directory.
 Docker Storage Drivers: Used to manage container storage layers.

57. What Are the Different Components

of Docker?
 Docker Engine: Core service that builds, runs, and manages containers.
 Docker Client: CLI to interact with Docker.
 Docker Images: Blueprints for creating containers.
 Docker Containers: Running instances of Docker images.
 Docker Hub: A cloud-based registry to store and share Docker images.
 Docker Compose: Tool for managing multi-container applications.
 Docker Volumes: Mechanism for persisting data.

58. What is the Difference Between

Virtual Machines and Docker Containers?
Docker
Aspect Virtual Machine
Container

Includes a full OS, Shares the host

Architectur
hypervisor, and OS kernel;
e
app lightweight

Startup
Minutes Seconds
Time

Resource
Heavy Lightweight
Usage

Strong isolation at Isolation at the

Isolation
the hardware level process level

Portability Less portable Highly portable

Slower due to
Performanc Faster due to
overhead of full
e minimal overhead
OS

Running different Microservices and

Use Case
OS environments agile development
 59. What is a Materialized View, and how
does it differ from a Regular View?
o A Materialized View is a database object that stores the results of
a query physically on disk. It can be refreshed periodically or
manually.
o A Regular View, on the other hand, does not store data; it only
defines a query. Every time you query the view, the underlying
query is executed.
Use Case:
 Use Materialized Views for precomputing and caching expensive queries
in data warehouses or OLAP systems.

60. What are Database Partitions, and

why are they useful?
 Answer:
o Partitioning splits a table into smaller, more manageable pieces,
based on specific criteria like range, list, or hash.
o It improves query performance by allowing the database to scan
only relevant partitions instead of the entire table.
Example:
 Partitioning a large sales table by year can optimize queries filtering by
year.

61. What is the difference between a

Correlated Subquery and a Non-
Correlated Subquery?
o Correlated Subquery: Depends on the outer query for its values.
It executes once for every row of the outer query.
o Non-Correlated Subquery: Executes independently of the outer
query and is executed only once.
Performance Note:
 Correlated subqueries are slower because they are executed row by row.
Non-correlated subqueries are generally faster.
 62. Explain the EXPLAIN Command and
Query Execution Plan.
o The EXPLAIN command provides the execution plan of a query,
showing the steps the database takes to execute it.
o Key metrics:

 Cost: Estimated time or resources needed for each step.

 Rows: Number of rows expected to be processed.
 Indexes: Whether indexes are used in the query.
Optimization:
 Use EXPLAIN to identify slow joins, unnecessary full table scans, or missing
indexes.

63. What is the Difference Between

Sharding and Partitioning?
o Partitioning: Divides data within a single database into smaller
logical pieces for better performance.
o Sharding: Distributes data across multiple databases or servers to
scale horizontally and handle large volumes of data.
When to Use:
 Partitioning is useful within a single database for optimization.
 Sharding is required for scaling when a single database cannot handle the
load.

64. How Does Indexing Affect SELECT,

INSERT, UPDATE, and DELETE?
 Answer:
o SELECT: Indexing improves performance by reducing the search
space.
o INSERT: Slightly slower because the index must be updated.

o UPDATE: Slower if indexed columns are updated, as the index must

also be updated.
 o DELETE: Slower for the same reason as UPDATE.

Optimization Tip:
 Avoid excessive indexing for tables with frequent writes.

65. What is a Recursive CTE, and How Is

It Used?
o A Recursive Common Table Expression (CTE) allows you to
perform recursive queries, such as retrieving hierarchical data (e.g.,
an organizational chart or file system structure).
Example:
 Finding all employees under a specific manager in an organization.

66. What are Deadlocks in SQL, and How

Can You Prevent Them?
o A deadlock occurs when two transactions hold locks that the other
transaction needs, causing both to wait indefinitely.
Prevention:
1. Access resources in a consistent order.
2. Use shorter transactions.
3. Implement proper indexing to reduce locking time.
4. Use database deadlock detection and resolution mechanisms.

67. How Do You Optimize SQL Queries for

Large Datasets?
1. Indexing: Create indexes on frequently queried columns.
2. **Avoid SELECT ***: Retrieve only the necessary columns.
3. Partitioning: Use table partitions to limit the amount of data scanned.
4. Joins: Use smaller tables as the driving table in joins.
5. Caching: Cache frequent query results.
6. EXPLAIN PLAN: Analyze and refine the query execution plan.
 68. What is a Surrogate Key, and When
Should It Be Used Over a Natural Key?
o A Surrogate Key is a system-generated unique identifier (e.g., an
auto-increment column).
o A Natural Key is a unique identifier derived from actual data (e.g.,
email, SSN).
When to Use:
 Use a Surrogate Key when:
o Natural keys are large or complex.

o Natural keys can change, leading to cascading updates.

 Use a Natural Key if it is small, stable, and meaningful.

69. Explain Row-Level Security (RLS) in

SQL.
o Row-Level Security restricts access to specific rows in a table based
on the user’s role or attributes.
Use Case:
 In a multi-tenant application, ensure each tenant can only access their
data.

70. How Do NULL Values Affect SQL

Aggregations and Comparisons?
o Aggregations: NULL values are ignored (e.g., SUM, COUNT).

o Comparisons: NULL cannot be compared directly. Use IS NULL or IS

NOT NULL.
 71. What is the Difference Between
ROW_NUMBER(), RANK(), and
DENSE_RANK()?
o ROW_NUMBER(): Assigns a unique number to each row.

o RANK(): Assigns ranks with gaps for duplicate values.

o DENSE_RANK(): Assigns ranks without gaps for duplicate values.

Use Case:
 Use ROW_NUMBER() for unique ordering, RANK() for competitive ranking,
and DENSE_RANK() for consistent rankings.

72. What are Triggers in SQL, and How

Are They Used?
o Triggers are procedural code that automatically executes in
response to certain events (e.g., INSERT, UPDATE, DELETE).
Use Case:
 Maintaining audit logs or enforcing complex business rules.

73. What Are Isolation Levels in SQL

Transactions?
o Isolation levels define the degree to which transactions are isolated
from each other.
o Levels (from least to most strict):

1. Read Uncommitted: Allows dirty reads.

2. Read Committed: Prevents dirty reads.
3. Repeatable Read: Prevents non-repeatable reads.
4. Serializable: Prevents phantom reads.
When to Use:
 Use stricter levels (e.g., Serializable) for critical financial applications.
 74. What is the correct execution priority
order in SQL?

The correct execution priority order in SQL is:

1. FROM – Identifies source tables or views.
2. ON – Applies join conditions for multiple tables.
3. WHERE – Filters rows before grouping or aggregation.
4. GROUP BY – Groups rows for aggregation.
5. HAVING – Filters groups after aggregation.
6. SELECT – Selects and computes specific columns.
7. DISTINCT – Removes duplicate rows.
8. ORDER BY – Sorts the result set.
9. LIMIT/FETCH – Restricts the number of rows returned.