Data Modelling interview questions and answers

1. What a foreign key enforces

You add FOREIGN KEY (user_id) REFERENCES users(id) to the orders table. What does this constraint guarantee?

1. A.An order cannot point to a user id that does not exist in users
2. B.Lookups on orders.user_id become fast because an index is always created
3. C.Each user can have only one order
4. D.The user row is copied into each order row

2. Purpose of a primary key

What is the main job of a primary key in a relational table?

1. A.It makes the table load faster when the server starts
2. B.It links the table to a table in another database
3. C.It uniquely identifies each row and cannot be NULL
4. D.It encrypts the most important column in the table

3. Third normal form

An orders table stores order_id, customer_id, customer_city. customer_city depends on customer_id, not on order_id. Which normal form rule does this break?

1. A.Third normal form, because of a transitive dependency
2. B.First normal form, because of repeating groups
3. C.Second normal form, because there is no composite key
4. D.It breaks no rule; this design is fully normalized

4. What a primary key guarantees

A table has order_id as its primary key. What does this guarantee?

1. A.Each order_id is unique and not NULL
2. B.The table is sorted by order_id
3. C.order_id values are consecutive with no gaps
4. D.Every order_id exists in another table

5. Why build a date table

Why do Power BI models usually include a separate date table marked as a date table?

1. A.It gives one calendar for time intelligence and filtering
2. B.Power BI cannot store dates in fact tables
3. C.It makes the file smaller than any fact table
4. D.It is required for every bar chart

6. Star vs snowflake schema

What is the main difference between a snowflake schema and a star schema?

1. A.A snowflake schema has no fact table
2. B.A snowflake schema can only run on Snowflake
3. C.A snowflake schema normalises dimensions into extra related tables, such as product to category
4. D.A snowflake schema stores all data in one wide table

7. Goal of normalisation

What is the main goal of normalising an OLTP database to third normal form?

1. A.Make analytical queries need fewer joins
2. B.Store data in columnar files
3. C.Remove redundant copies of data so updates happen in one place and stay consistent
4. D.Allow duplicate primary keys

8. Picking the natural key

Which of these is a natural key for a table of registered Indian businesses?

1. A.An auto-increment id column
2. B.A UUID generated by the app
3. C.The business's GSTIN
4. D.The row number in the load file

9. The model with indexes and types

Which level of data model includes column data types, indexes and partitioning?

1. A.Conceptual model
2. B.Logical model
3. C.Business glossary
4. D.Physical model

10. A fully additive measure

In a sales fact table, which measure can be summed across every dimension, including time?

1. A.Account balance
2. B.Unit price
3. C.Profit margin percentage
4. D.Sales amount

11. Invoice number in the fact table

A sales fact table has an invoice_number column. It has no matching dimension table because there are no other invoice attributes. What is it called?

1. A.A junk dimension
2. B.A conformed dimension
3. C.A degenerate dimension
4. D.A role-playing dimension

12. A nullable coupon_id

orders.coupon_id is a foreign key to coupons and allows NULL. What does that say about the relationship?

1. A.Every order must use exactly one coupon
2. B.An order may have no coupon, or one coupon
3. C.A coupon can be used by only one order
4. D.An order can use many coupons

13. Storing age or date of birth

A form collects a customer's age. What should the table store?

1. A.Date of birth, and calculate age when needed
2. B.Age as an integer, updated by hand
3. C.Both, and let users fix any mismatch
4. D.Age as text so it can include months

14. Kimball versus Inmon

How does the Kimball approach to a data warehouse differ from the Inmon approach?

1. A.Kimball builds dimensional marts first; Inmon a normalised core first
2. B.Kimball uses only NoSQL stores; Inmon uses only relational SQL
3. C.Kimball avoids fact tables entirely; Inmon avoids dimensions
4. D.Kimball keeps no history at all; Inmon keeps every change forever

15. OLTP schema versus OLAP schema

Why is an app's transactional (OLTP) database usually normalised, while the analytics (OLAP) warehouse is often dimensional?

1. A.OLTP never needs joins; OLAP always does
2. B.OLTP handles many small writes; OLAP serves large reads
3. C.OLAP databases cannot store foreign keys
4. D.OLTP data is smaller, so design does not matter

16. Bronze, silver and gold layers

In a medallion (bronze/silver/gold) lakehouse, what usually lives in the silver layer?

1. A.Raw files exactly as they arrived
2. B.Business-ready aggregates for dashboards
3. C.Backups of the gold tables
4. D.Cleaned, de-duplicated and conformed data

17. created_at and updated_at columns

Why do many tables include created_at and updated_at columns?

1. A.To replace the primary key of the table
2. B.To store the time zone of each user
3. C.To record when rows were added and last changed
4. D.To make the table faster to update

18. Where DynamoDB design starts

When designing a DynamoDB table, what should you work out first?

1. A.The full normalised ER diagram
2. B.The access patterns the app needs to run
3. C.The list of foreign keys to enforce
4. D.The joins that reports will need

19. Where a JSON column fits

A product table has fixed core fields, plus a few optional attributes that differ by category and are rarely filtered on. Where could those optional attributes go?

1. A.In fifty nullable columns, one per attribute
2. B.In a separate database per category
3. C.In the product name as text
4. D.In a JSONB column next to the core columns

20. Kimball and Inmon approaches

Which statement best contrasts the Kimball and Inmon approaches to warehouse design?

1. A.Kimball uses only cloud tools, Inmon only on-premise tools
2. B.Kimball forbids fact tables, Inmon requires them
3. C.They are two names for the same method
4. D.Kimball starts with star marts; Inmon with a 3NF warehouse

21. Turning on both-direction filtering

A developer sets every relationship in a Power BI model to filter in both directions "to make slicers work". What is the main risk?

1. A.Reports can no longer be published
2. B.Ambiguous filter paths and unexpected totals
3. C.Measures stop working in cards
4. D.Dates stop sorting correctly

22. Tracking customer address history

Analysts need to report sales by the customer's city at the time of each order, even after customers move. Which approach fits?

1. A.SCD Type 1: overwrite the city when it changes
2. B.Delete old customers and reinsert them
3. C.Store only the latest city in the fact table
4. D.SCD Type 2: add a new row per change with valid_from, valid_to and an is_current flag

23. Product name in order items

The table below has primary key (order_id, product_id). product_name depends only on product_id. Which normal form does it break?

1. A.First normal form
2. B.Third normal form, but not 2NF
3. C.Second normal form
4. D.None; it is fully normalised

24. Department name in employees

employees(emp_id, name, dept_id, dept_name) has emp_id as the key. dept_name depends on dept_id. What is the 3NF fix?

1. A.Make (emp_id, dept_id) the composite primary key
2. B.Add a UNIQUE constraint on the dept_name column
3. C.Move dept_name to a departments table keyed by dept_id
4. D.Copy dept_name into every table that uses dept_id

25. Monthly total of daily balances

A fact table stores each account's closing balance every day. A report shows "March balance" as SUM(balance) over all days in March. What is wrong?

1. A.Balances are semi-additive; take month-end or average, not a sum
2. B.Balances must also be averaged across accounts, not summed
3. C.SUM is correct as long as weekends and holidays are excluded
4. D.The report should take MAX of the balance across all accounts

26. Storing a conversion rate in a fact

A daily fact table stores conversion_rate per campaign. Analysts need the rate for a whole month across campaigns. What should the table store instead?

1. A.Only the conversion rate, rounded to two places
2. B.The numerator and denominator, such as orders and sessions
3. C.The average conversion rate of the week
4. D.The rate multiplied by 100 to avoid decimals

27. Daily stock level per warehouse

Operations wants to see how much stock each warehouse held at the end of every day. Which fact table type fits best?

1. A.Periodic snapshot fact
2. B.Transaction fact
3. C.Accumulating snapshot fact
4. D.Factless fact

28. Many small flags on a fact

An orders fact has is_gift, is_express, payment_type and channel, each with only a few values. Adding each as its own dimension clutters the model. What is the usual design?

1. A.Store each flag as a separate fact table
2. B.Put the flags in the customer dimension
3. C.Combine them into one junk dimension
4. D.Drop the flags from the warehouse

29. Order date and ship date keys

A fact table has order_date_key and ship_date_key, and both join to the same dim_date table. What is this pattern called?

1. A.A role-playing dimension
2. B.A conformed fact
3. C.A degenerate dimension
4. D.A bridge table

30. Visits with several diagnoses

A hospital visit fact can have several diagnoses, and the number varies. How do you link visits to a diagnosis dimension?

1. A.Add diagnosis_1 to diagnosis_10 columns to the fact
2. B.Use a bridge table between the fact and the dimension
3. C.Repeat the whole visit row once per diagnosis
4. D.Store diagnoses as one long text column

31. Storing who reports to whom

Every employee has one manager, who is also an employee. What is the simplest way to model this?

1. A.A separate managers table copying employee data
2. B.One column per level: level1, level2, level3
3. C.A manager_id column that references employees.emp_id
4. D.A text column with the manager's name

32. Facts with a missing product

Some sales rows have no valid product. Why do warehouse designers usually point them to an "Unknown" product row (for example key −1) instead of a NULL key?

1. A.Inner joins keep the rows, so totals stay complete
2. B.NULL keys take more storage than −1
3. C.It hides the data quality problem from users
4. D.Fact tables cannot store NULL in any column

33. Rows in a daily stock snapshot

A retailer wants a daily periodic snapshot at grain store × product × day. It has 200 stores and 5,000 products. How many rows does one year add?

1. A.1.825 million
2. B.1 million
3. C.73,000
4. D.365 million

34. Comments on posts and photos

A comments table uses commentable_type ('post' or 'photo') and commentable_id to point at either table. What is the main weakness?

1. A.Comments can no longer have their own primary key
2. B.Joins from comments to posts become impossible in SQL
3. C.It needs photos and posts in separate databases
4. D.No foreign key can be enforced on commentable_id

35. Enum type or lookup table

Order statuses change a few times a year, and each needs an English and an Arabic label. Which design fits best?

1. A.A PostgreSQL ENUM type
2. B.A CHECK constraint listing the codes
3. C.A lookup table with code and label columns
4. D.Free text with no constraint

36. Spotting changed rows with a hash

Your SCD2 load must detect whether any of 25 tracked attributes changed for each customer. What is a common efficient method?

1. A.Compare the source updated_at with the last load time
2. B.Compare a hash of tracked columns with the stored hash
3. C.Reload every customer as a new version daily
4. D.Compare the row count of source and target

37. Grain of an exchange rate table

You need daily INR conversion for sales in AED, USD and SAR. What grain should the exchange rate table have?

1. A.One row per currency, overwritten daily
2. B.One row per sale with its rate
3. C.One row per currency per day
4. D.One row per month for all currencies

38. A DynamoDB key on order status

An orders table in DynamoDB uses status (PENDING, SHIPPED, DELIVERED) as its partition key. Writes start being throttled. Why?

1. A.Few key values send most traffic to a few partitions
2. B.Status values are too long for a partition key
3. C.DynamoDB only allows numbers as partition keys
4. D.The table needs a foreign key on status

39. Embedding every login event

A MongoDB users document embeds an array of every login event. Active users log in many times a day. What problem will this cause?

1. A.MongoDB cannot build an index on any array field
2. B.Documents grow without limit toward the 16 MB cap
3. C.Reads of the user become impossible
4. D.Each login creates a new database

40. Keeping every status change

orders.status is overwritten as an order moves from placed to delivered. The business now wants to know how long orders spend in each status. What model change helps?

1. A.Add status_1 to status_8 timestamp columns to the orders table
2. B.Store a comma list of statuses in orders
3. C.Read past statuses from the app logs
4. D.Add an order_status_history table with one row per change

41. How to send money amounts in JSON

Your payments API serves clients in India and the UAE. Which JSON shape for amounts is safest?

1. A.{"amount": 1299.50} with the currency in the docs
2. B.{"amount": "₹1,299.50"} as display text
3. C.{"amount": 129950, "currency": "INR"} in minor units
4. D.{"amount": 1299.5, "currency": "rupees"}

42. Random UUID keys slow down inserts

A table uses random UUIDv4 primary keys. As it grows past RAM, insert throughput drops and WAL volume rises. What change helps most while keeping UUIDs?

1. A.Use time-ordered UUIDs such as UUIDv7, so new keys go to the right edge of the B-tree
2. B.Store the UUID as text instead of the uuid type
3. C.Use a hash index for the primary key
4. D.Increase max_connections

43. Pre-norm transformer blocks

Most modern large transformers put LayerNorm before each sub-layer (pre-norm) instead of after the residual add (post-norm). Why?

1. A.It halves the parameter count
2. B.Deep stacks train more stably
3. C.It removes the need for residuals
4. D.It makes attention linear in length

44. Monthly budget next to daily sales

Sales is daily by store; Budget is monthly by region. A developer related Sales directly to Budget and got many-to-many warnings and wrong totals. What is the better model?

1. A.Merge Budget into Sales in Power Query by copying values
2. B.Use bidirectional filters between Sales and Budget
3. C.Divide the budget by 30 and store it per day
4. D.Relate both facts to shared Date and Region dimensions

45. End dates for history rows

A customer address history has valid_from dates only. How do you fill valid_to for a type 2 dimension?

1. A.LAG(valid_from) per customer by date, minus one day; NULL means current
2. B.MAX(valid_from) per customer, so every version ends on the latest date
3. C.LEAD(valid_from) per customer by date, minus one day; NULL means current
4. D.valid_from plus one year for every version, capped at today's date

46. Double counting in an SCD2 join

dim_customer has two versions for customer 7: Dubai valid 2026-01-01 to 2026-05-01, and Abu Dhabi valid 2026-05-01 to 9999-12-31. Facts: an order on 2026-03-10 (50) and one on 2026-05-01 (100). What SUM(amount) does this query return?

SELECT SUM(f.amount)
FROM fact_orders f
JOIN dim_customer d
  ON f.customer_id = d.customer_id
 AND f.order_date BETWEEN d.valid_from AND d.valid_to;

1. A.150
2. B.100
3. C.250
4. D.200

47. 3NF but not BCNF

In enrolments(student, subject, teacher), each teacher teaches exactly one subject, and a student takes each subject from one teacher. The key is (student, subject). What is its normal form?

1. A.3NF but not BCNF
2. B.Not in 1NF
3. C.BCNF
4. D.Not in 2NF

48. Attributes that change every month

dim_customer has 50 million rows. Income band and loyalty tier change monthly for many customers, and type 2 tracking would explode the table. What design handles this?

1. A.Switch the whole customer dimension to type 1 updates
2. B.Add one column per month for each changing attribute
3. C.Keep those attributes only in the source CRM system
4. D.Move them into a mini-dimension keyed on the fact

49. Shipping charge counted three times

An order has three lines and one ₹90 shipping charge. The ETL copied ₹90 onto each line in fact_order_lines. Revenue reports now show ₹270 of shipping. What is the proper fix?

1. A.Allocate the charge across lines, or keep a header-level fact
2. B.Divide every shipping total by the order count
3. C.Report only the MAX shipping per order everywhere
4. D.Remove shipping from the warehouse

50. Weights in a joint account bridge

A joint bank account with a balance of AED 10,000 has two holders. It links to dim_customer through a bridge table with a weight of 0.5 for each holder. What do the weights achieve?

1. A.Each holder shows AED 10,000, the full balance
2. B.The account is hidden until weights add to 2
3. C.Each holder shows AED 5,000, so the total stays AED 10,000
4. D.Only the first holder gets the balance

51. Product specs as attribute rows

To support any product spec, a team stores specs as rows: (product_id, attribute_name, value), with value as text. What is the main cost of this design?

1. A.It cannot store more than 100 attributes
2. B.No real types or constraints, and queries need pivots
3. C.It makes adding a new attribute need a migration
4. D.Rows in the table can never be indexed

52. Reprinted invoice shows a new price

order_lines stores product_id and qty, and invoices join to products.price. After a price rise, last month's reprinted invoices show the new price. What is the right model?

1. A.Store the unit price on each order line at sale time
2. B.Never allow product prices to change
3. C.Recalculate old invoices with a discount
4. D.Keep only one product price per year

53. is_weekend for UAE dates

From January 2022 the UAE moved its official weekend from Friday–Saturday to Saturday–Sunday. How should your date dimension's is_weekend flag handle this?

1. A.Use Saturday–Sunday for all dates
2. B.Use Friday–Saturday for all dates
3. C.Set it per date using the rule in force on that date
4. D.Compute it in each report from the weekday

54. Hash diff misses some changes

A hash diff is built as MD5(first_name || last_name || city). Some real changes are never detected, and some rows hash to NULL. What is the fix?

1. A.Use SHA-256 instead of MD5
2. B.COALESCE each column and join them with a delimiter
3. C.Sort the columns alphabetically before concatenating
4. D.Hash only the primary key

55. Why Data Vault 2.0 uses hash keys

Data Vault 2.0 replaced sequence surrogate keys with hashes of business keys in hubs and links. What is the main benefit?

1. A.Hashes make business keys unnecessary
2. B.Hash keys remove the need for satellites
3. C.Hubs, links and satellites can load in parallel without lookups
4. D.They stop duplicate business keys arriving from source systems

56. Storing money as integer fils

A payments table stores amounts as integers in minor units, assuming 100 minor units per currency. It works for AED and INR. What breaks when Kuwait goes live?

1. A.KWD has 1,000 fils per dinar, so amounts are off by ten
2. B.KWD cannot be stored as an integer at all
3. C.Kuwait has no minor unit, so values are too large
4. D.Nothing; every currency uses 100 minor units

57. Two tables that reference each other

departments.head_emp_id references employees, and employees.dept_id references departments, both NOT NULL. You cannot insert the first department or employee. What PostgreSQL feature helps?

1. A.ON DELETE CASCADE on both foreign keys
2. B.DEFERRABLE INITIALLY DEFERRED foreign keys
3. C.A UNIQUE constraint on head_emp_id
4. D.Disabling the primary keys during inserts

58. A product moved to a new category

A product moved from "Home" to "Kitchen" in June. Finance wants last year's category totals to stay as reported, but merchandising wants all history under today's category. What design serves both?

1. A.Type 2 history plus a current-category column on each row
2. B.Type 1 overwrite of the category
3. C.Type 2 history only, with no current-category attribute
4. D.Two separate warehouses, one built for each team

59. Building blocks of Data Vault

A bank models its integration layer with Data Vault 2.0. What are hubs, links and satellites?

1. A.Hubs store reports, links store dashboards, satellites store users
2. B.Business keys, relationships, and attributes over time
3. C.Hubs are facts, links are dimensions, satellites are aggregates
4. D.Hubs are raw files, links are views, satellites are backups

60. Students, courses and grades

Students take many courses, each course has many students, and you need to store the enrolment date and final grade. What is the best data model?

1. A.Add grade and date columns to the Student table
2. B.Store a comma-separated list of courses in each student row
3. C.An associative entity, such as Enrolment, between Student and Course that holds the date and grade
4. D.Link Course to Student as one-to-many