What is normalization and denormalization? When would you use each?

Normalization is the process of organizing a database to reduce data redundancy and improve data integrity, typically used in transactional systems. Denormalization is the intentional introduction of redundancy to optimize read performance and simplify queries, commonly applied in analytical systems.

Mechanics and Why

* Normalization (e.g., 1NF, 2NF, 3NF): Aims to eliminate redundant data and ensure data dependencies make sense. For example, in 3NF, all non-key attributes are dependent on the primary key, the whole primary key, and nothing but the primary key. This creates a "single source of truth" for each piece of data, making updates and deletions easier and preventing inconsistencies. It's crucial for Online Transaction Processing (OLTP) databases where data integrity and write performance are paramount, supporting ACID properties.
* Denormalization: Involves adding redundant data or grouping data from multiple normalized tables into a single table. This reduces the number of joins required for common queries, significantly improving read performance. It's often employed in Online Analytical Processing (OLAP) systems, data warehouses, and data marts, where complex analytical queries are frequent and write operations are batched (e.g., via ETL/ELT). Wider, flatter tables can also benefit query engines by reducing data shuffling in distributed systems like Spark or improving micro-partition pruning in columnar databases like Snowflake.

When to Use Each

* Normalization: Primarily used for source operational databases (OLTP systems) where data is frequently updated, inserted, or deleted. Examples include CRM systems, order processing, or banking applications. The goal is to maintain data integrity and optimize for write operations.
* Denormalization: Applied in analytical environments (OLAP systems) like data warehouses or data marts. After data is extracted from normalized sources, it's transformed and loaded into denormalized structures, such as star or snowflake schemas, to facilitate faster reporting and complex analytical queries. Fact tables are often highly denormalized, containing foreign keys to dimension tables and all necessary measures.

Key Trade-offs

Normalization prioritizes data integrity, consistency, and write efficiency at the cost of potentially slower read performance due to more joins. Denormalization prioritizes read performance, query simplicity, and analytical speed at the cost of increased storage, potential data redundancy, and more complex ETL/ELT processes to maintain consistency.

In the interview, also mention that a robust data ecosystem often employs both: normalized source systems feeding into denormalized analytical layers.