Databases interview questions

ACID is a set of four guarantees, atomicity, consistency, isolation, and durability, that keep database transactions reliable even when errors or crashes occur.

Some are. Many document and key-value databases now support ACID transactions, either within a single record or across several records with some limits, while others choose weaker guarantees for scale and speed. Check each database's documentation for exactly what it guarantees.

ACID prioritizes correctness: every transaction is all-or-nothing and leaves the data immediately consistent. BASE, short for basically available, soft state, eventually consistent, prioritizes availability and allows copies of data to disagree briefly before they converge.

Isolation levels control how much concurrent transactions can see of each other's changes. The SQL standard defines four, from weakest to strongest: read uncommitted, read committed, repeatable read, and serializable. Stronger levels prevent more anomalies but can reduce throughput.

BASE describes distributed databases that favor availability over immediate consistency: they keep answering and let copies of data briefly disagree.

ACID guarantees that every transaction is all-or-nothing and that reads see committed data right away. BASE gives up that immediate consistency so the system can stay available and scale across many servers, with replicas agreeing eventually.

Eventual consistency is one of its three parts. BASE is the broader description of a system that also stays basically available and tolerates soft, changing state while replicas catch up.

Many NoSQL databases do by default, such as Apache Cassandra, Amazon DynamoDB and CouchDB, along with DNS and most caching layers. Most of them can also be configured for stronger consistency on specific requests.

The CAP theorem says that if a network failure splits a distributed database, the system must choose between consistency and availability; it can't have both.

It depends on the data. Bank balances and inventory counts usually need consistency, while social feeds, view counts, and shopping carts can tolerate brief staleness in exchange for staying available.

Not really. CAP applies to data replicated across multiple networked nodes; a single server has no partition between copies to worry about, although it can still simply go down.

Eventual consistency means that if no new updates are made, all copies of the data will eventually become identical. It is the typical guarantee of AP systems, which stay available during partitions and reconcile differences afterward.

A connection pool is a cache of open database connections that an application reuses across requests, avoiding the cost of opening a new connection every time.

Opening a new database connection for every request adds latency and puts extra load on the database. Reusing a small set of open connections makes queries start faster and keeps the number of connections under control.

Usually smaller than people expect: start with a few connections per CPU core on the database server, then tune with load tests. Remember that the total across all application instances must stay below the database's connection limit.

New requests wait in a queue until a connection is returned. If none frees up before the configured timeout, the request fails with an error, which is often a sign of slow queries or a connection leak.

A data lake is a central storage repository that holds large amounts of raw data in its original format, structured or not, until someone needs to analyze it.

A data lake stores raw data of any type cheaply and applies structure only when it is read. A data warehouse stores cleaned, structured data with a schema defined up front, which makes it faster and more reliable for business reporting.

A lakehouse keeps data in open file formats on lake storage but adds a table layer with transactions, schemas, and fast SQL queries. It aims to give warehouse-style reliability without copying data into a separate warehouse.

A data swamp is a data lake that has become disorganized, with undocumented, duplicated, or low-quality data that nobody can find or trust. Catalogs, ownership, and quality checks prevent it.

A data warehouse is a central database built for analytics that collects historical data from many sources so teams can run large reporting queries quickly.

A data warehouse is a type of database, but it is designed for large analytical queries over historical data. A regular application database is designed for many small, fast reads and writes that keep an app running.

A data lake stores raw data of any kind, such as logs, images, and JSON files, usually in cheap object storage. A data warehouse stores cleaned, structured data with a defined schema, so it is easier and faster to query.

ETL stands for extract, transform, load: data is pulled from source systems, cleaned and reshaped, and then loaded into the warehouse. In ELT, the raw data is loaded first and transformed inside the warehouse.

A database is an organized collection of data stored on a computer, managed by software that lets applications save, search, and update it efficiently.

The database is the organized data itself, while the DBMS (database management system) is the software, such as MySQL or PostgreSQL, that stores, queries, and protects that data.

A spreadsheet is designed for a person to view and calculate on a fairly small amount of data. A database is designed for applications and many users to store and query large amounts of data safely, with rules that keep the data consistent.

The two main families are relational (SQL) databases, which store data in tables, and NoSQL databases, which include document, key-value, wide-column, and graph databases.

A database index is a data structure that helps a database find rows quickly without scanning a whole table, much like the index at the back of a book.

Each index uses disk space and must be updated on every insert, update, and delete, so too many indexes slow down writes. Index the columns that your frequent queries filter, join, or sort on.

A clustered index determines the order in which the table's rows are physically stored, so a table can have only one. A non-clustered index is a separate structure that points to the rows, and a table can have many.

A composite index covers more than one column, such as (customer_id, created_at). It helps queries that filter on the first column, or on the first and second columns together, because the columns are sorted in that order.