Data Modeling exercises for data analyst interviews

Designing an effective data model is crucial for ensuring the performance, scalability, and usability of analytical applications. Consider a scenario where you are tasked with integrating diverse data sources, such as sales transactions, customer profiles, and product details. Striking the balance between normalization for data integrity and denormalization for query performance can be a daunting challenge.

The Core of Data Modeling

To dive into practical exercises, let’s analyze a simple scenario of sales data. Below is a visual representation of a basic relational data model for a sales analysis application.

Table Structure

This example involves three key tables:

1. Customers Table

   CustomerID	CustomerName	Contact	City	Country
   1	John Doe	123456789	London	UK
   2	Jane Smith	987654321	Paris	France

2. Products Table

   ProductID	ProductName	Category	Price
   101	Widget A	Tools	10
   102	Gadget B	Gadgets	15

3. Sales Transactions Table

   TransactionID	CustomerID	ProductID	Quantity	SaleDate
   1	1	101	2	2023-07-01
   2	1	102	1	2023-07-02
   3	2	101	3	2023-07-01

Exercise: Writing an SQL Query

Task: Calculate the total sales for each customer, excluding any transactions for discontinued products.

Here’s the SQL query to execute this task:

SELECT c.CustomerName, SUM(p.Price * st.Quantity) AS TotalSales
FROM Customers c
JOIN Sales_Transactions st ON c.CustomerID = st.CustomerID
JOIN Products p ON st.ProductID = p.ProductID
WHERE p.Discontinued = 0 -- Assume this column exists  
GROUP BY c.CustomerName;

Expected Result:

CustomerName	TotalSales
John Doe	20
Jane Smith	30

Common Mistakes

• Ignoring Product Status: Candidates often overlook filtering out discontinued products, assuming all products in the sales table are active.
• Using Incorrect Joins: Wrong joins lead to inflated sums, as candidates may connect tables without proper understanding of their relationships.
• Failing to Group Correctly: Forgetting to group by the customer name or incorrectly structuring the GROUP BY clause can yield misleading results.

Interview Traps

In interviews, candidates are often asked similar questions that reveal gaps in their understanding and can lead to common pitfalls:

• Normalization vs. Denormalization: Interviewers might probe your understanding of when to normalize data to avoid redundancy versus when to denormalize for performance, leading to discussions about trade-offs in query speed and maintenance complexity.
• Complex Queries: They may ask you to interpret complex SQL queries and clarify what data they return, which tests your ability to navigate not just syntax but logic and relationships in your data models.
• Performance Explanation: You might be required to explain your reasoning behind optimizing a specific query, focusing on indexing strategies and filter conditions.

Worked Example

Imagine an interviewer gives you the following task (paraphrased): "Given a new requirement to evaluate total sales in the current month while filtering out discontinued products, how would you adapt your previous query?"
To answer, start with the original query, then modify it to include a filter for the current month:

SELECT c.CustomerName, SUM(p.Price * st.Quantity) AS TotalSales
FROM Customers c
JOIN Sales_Transactions st ON c.CustomerID = st.CustomerID
JOIN Products p ON st.ProductID = p.ProductID
WHERE p.Discontinued = 0
AND MONTH(st.SaleDate) = MONTH(CURRENT_DATE())
AND YEAR(st.SaleDate) = YEAR(CURRENT_DATE())
GROUP BY c.CustomerName;

This query maintenance illustrates both query logic and date handling, which are vital for dynamic reporting skills.

On the Job: Real-World Applications

In practice, data modeling doesn’t end with table creation. Analysts spend considerable time ensuring that data flows from various sources seamlessly into their models. Some practical aspects include:

• Performance Tuning: Regularly analyzing query performance for optimization. This may involve revisiting joins, analyzing execution plans, and applying indexing where necessary.
• Data Quality Management: Ensuring that the data remains clean and usable. This includes regular audits and engagement with data collection processes to prevent anomalies.
• Collaboration: Working across teams to validate modeling assumptions, ensuring the data accurately reflects business needs and can adapt as those needs evolve.

Mastering these practical exercises not only prepares you for technical interviews but also equips you to handle real-world data challenges effectively.

References

• PostgreSQL Documentation: SQL Commands
• MySQL Documentation: Database Design
• Microsoft Docs: Entity Relationship Diagrams
• IBM: Data Modeling Concepts