Week 5/31: Data Modelling for Data Engineering Interviews (Part #2)

Week 5 of 33-Week Data Engineering Interview Guide

In this post, we explore common data modelling interview topics by solving interview questions, including:

• Normalised Forms

• Types of Slowly Changing Dimensions (SCDs)

• Differences between the Kimball and Inmon approaches

• The concept of "One Big Table"

Additionally,

• We solve a case study on converting data tables into 3NF form.

• We solve a case study on SCDs and Star Schema.

• We share resources for a mock Airbnb data modelling interview to help you practise.

In the first post of this series, we covered key concepts such as:

• Definitions of data modelling

• Types of data models

• ER diagrams

• Distinctions between OLTP and OLAP systems

If you haven’t already, check out the post below for a strong foundation:

Week 4/33: Data Modelling for Data Engineering Interviews (Part #1)

Importance of Data Modelling Interviews

Data modelling interviews assess a data engineer’s ability to turn business needs into effective data solutions. Success requires both technical expertise and strong communication skills. During these interviews, asking clarifying questions can provide additional context, as interviewers often reveal more details during discussions.

---

Question 1

Case Study: Normalising Data to 3NF

Identify the current form of the given data and normalise it to 3NF.

We are provided with the above tables. We start by identifying their current form (e.g., 1NF, 2NF, 3NF). If the tables are not in 3NF, we explain the steps to normalise them.

To answer effectively, let’s remember the rules:

• 1NF:

  • All values must be atomic.

  • Each cell contains a single value.

  • Each row is unique.

• 2NF:

  • Satisfy all 1NF rules.

  • All data must depend on the primary key. Columns not dependent on the primary key should be moved to separate tables.

• 3NF:

  • Satisfy all 2NF rules.

  • The primary key must fully define all columns, and no column should depend on any other non-primary key.

Checking the Membership Table:

1. Original Table

   • This table meets 1NF and 2NF rules.

   • The MEMBERSHIP ID determines the FULL NAMES and PHYSICAL ADDRESS.

   • However, the FULL NAMES and PHYSICAL ADDRESS don’t depend on any other key.

   • Salutations are not determined by the MEMBERSHIP ID, violating 3NF rules.

2. Normalisation Steps

   • Create a new table for salutations.

   • Ensure the original table only contains columns that fully depend on the primary key.

3. Final Structure

   • A normalised table in 3NF with clear relationships to supporting tables (e.g., Salutations).

Checking MOVIES table:

• Original Table

  • This table meets 1NF, 2NF and 3NF rules.

• Normalisation Steps

  • The requirement only extends up to 3NF, so there’s no need to go beyond that level of normalization.

• Final Structure

  • No structure change is required.

Tip: We suggest having the below visual in your pocket as a normal forms reference.

---

Question 2

What is the impact of data modelling on data storage and scalability?

Answer (Direct):

Data modelling defines the structure and organisation of data, which has a significant impact on storage and scalability.

Effective data modelling:

1. Reduces storage requirements by eliminating redundant data.

2. Improves data retrieval efficiency, enabling faster and more reliable access to information.

3. Enhances scalability, allowing systems to handle increasing data volumes without performance degradation.

4. Supports system flexibility, enabling changes or updates to be implemented without affecting existing operations.

Tip: Sometimes it is better to explain things by using metaphors it helps explaining them clearly while allowing us to showcase our understanding.

Alternative Answer (with Metaphors):

Data modelling is like creating a blueprint for how your data is organized. Good data modelling helps you:

1. Save storage space by eliminating data redundancy.

2. Access and retrieve data quickly and efficiently.

3. Scale your system seamlessly as data volume grows.

4. Implement system changes without disrupting existing functionality.

Think of it like organising a library, when books are well-categorised and clearly labelled, it's easier to store them efficiently, find what you need, add new books, and reorganise sections when required.