The modeling and erwin questions data modeler interviews keep coming back to, what each one tests, and answers checked against erwin's own docs.
Prepare erwin data modeler interview questions and answers on two levels at once. The interviewer wants to know that you can model: normalisation, relationships and keys, dimensional design. They also want to know that you have used erwin itself and did not just list it on your CV, so expect questions about forward and reverse engineering, Complete Compare, naming standards and how a team shares models. Be ready for a design exercise drawn live as well. Below are twelve questions grouped that way, each with what it tests and a short answer, then a worked design exercise and a one-week plan.
What the interview is checking
Concept questions show whether you can explain a model to a business sponsor and to a DBA. Tool questions let an interviewer separate hands-on users from keyword lists, because erwin's workflows have specific names and steps that are hard to fake. The design exercise shows how you think: whether you ask about scope before drawing, and whether you can defend each key you choose.
Modeling fundamentals
1. What is the difference between conceptual, logical and physical models?
Tests: whether you pitch a model at the right audience.
A conceptual model names the main business things and how they relate, for sponsors. A logical model adds attributes, keys and relationships but stays independent of any database. A physical model turns that into tables, columns and datatypes for one target platform. erwin's help lists four model types: Logical, Physical, Dimensional (facts and dimensions in a star schema) and Logical/Physical, "a single model that includes both the logical and physical models." Say which type you used on a real project and why.
2. Walk me through normalisation. When would you stop short of it?
Tests: theory, then judgement.
First normal form removes repeating groups: no Vendor Code 1 and Vendor Code 2 columns; vendors go in their own table. Second normal form means every non-key value depends on the whole primary key. Third normal form removes values that depend on something other than the key, such as an advisor's room number stored on a student record when it depends on the advisor. Microsoft's guide calls third normal form the highest level most applications need, and also says full compliance is not always practical. The strong answer adds where you would denormalise on purpose (read-heavy reporting tables) and how you would keep the copied data correct (the load process owns it, and the model documents it).
3. What is the difference between an identifying and a non-identifying relationship?
Tests: whether you know how keys migrate in erwin.
In an identifying relationship, the parent's key migrates into the child's primary key, which makes the child a dependent entity; erwin draws it as a solid line. In a non-identifying relationship, the foreign key goes into the child's non-key area and the line is dashed. It can be mandatory or optional; in the optional case, nulls are allowed in the foreign key. An order line identified by order number plus line number is identifying. An order pointing at the customer who placed it is non-identifying.
4. IDEF1X or IE notation?
Tests: whether you can read the diagrams your new team already has.
erwin supports both, plus a dimensional notation. In IDEF1X, an identifying relationship is a solid line with a dot near the child. In IE, the line has a crow's foot at the child end, and an optional non-identifying relationship is a dashed line with a cross and a circle at the parent end. Use whatever the team standardised on, but be able to read both.
Dimensional design
5. How do you start a dimensional model?
Tests: method, not vocabulary.
Kimball's four steps: select the business process, declare the grain, identify the dimensions, identify the facts. The grain is what one fact row represents, and Kimball calls declaring it "the pivotal step": every dimension and fact you add afterwards has to be consistent with it. Start at atomic grain, the lowest level the process captures, because it survives questions nobody predicted.
6. Star or snowflake schema?
Tests: whether you know the trade-off and have an opinion.
A star joins the fact table straight to flattened dimension tables; a snowflake normalises dimensions into sub-tables. Kimball advises avoiding snowflakes because business users find them harder to navigate and they can hurt query performance, while a flattened dimension holds exactly the same information. A good answer agrees, then names the case where you would still snowflake and why.
7. Explain a Type 2 slowly changing dimension.
Tests: how you keep history in a warehouse.
A change adds a new dimension row with the new values and keeps the old one. Kimball says to add at least three columns: row effective date, row expiration date and a current-row indicator. The new row gets a new surrogate key, and fact rows from that moment on point to it, so old sales stay attached to the customer's old region. Contrast it with Type 1 (overwrite, no history) and Type 3 (add a column for the prior value), and say which attributes in your last model needed which.
8. Why surrogate keys, and which facts can you sum?
Tests: two details that expose people who only read definitions.
Natural keys break once you keep history, because one customer now has several dimension rows, and keys from different source systems may clash. Kimball recommends simple integer surrogate keys for every dimension except the date dimension. On facts: sales amounts add up across every dimension; account balances add up across everything except time; ratios do not add up at all, so store the components (numerator and denominator) and calculate the ratio in the reporting layer.
erwin itself
9. How do you forward engineer a model?
Tests: whether you have done it.
In erwin, Actions, then Forward Engineer, then Schema opens the schema generation dialog. Pick the subject area to generate (the Main Subject Area is the default), choose options on the Options tab, click Preview to read the DDL, then Generate to create the schema on the target server. The strong answer stresses reviewing the preview before anything touches a shared database.
10. You inherit a database with no model. What do you do?
Tests: maintenance work, not just new designs.
Reverse engineer the database into a model, then tidy it: add definitions, subject areas and the relationships the database never declared. erwin's RE Scheduler can automate offline reverse engineering. From then on, use Complete Compare to keep model and database in step. It compares models, scripts and databases, shows the differences, lets you apply only the ones you choose, and generates ALTER scripts when needed. In the help, you start it from the Actions menu, set the compare level, filter objects and work through the Resolve Differences dialog.
11. How do you keep models consistent across a team?
Tests: governance in practice.
Three tools. Naming standards: each Naming Standard object holds rules for logical and physical names plus a glossary of words and their abbreviations, and you apply one to a model. Domains: a domain is a reusable template, and a column created from it inherits its properties, so "currency amount" is defined once. Workgroup Edition: models, naming standard glossaries and templates live in a shared repository (the Mart), with concurrent access, optional model locking, collision resolution, merge, version management and change impact analysis. Navigator Edition gives read-only access for analysts and reviewers.
12. Can erwin model NoSQL and cloud platforms?
Tests: whether your knowledge stops at relational databases.
Yes. erwin's support list includes MongoDB, Cassandra, Couchbase, DynamoDB and Neo4j among NoSQL targets; Databricks, Google BigQuery and Azure Synapse among big data targets; and Snowflake and Redshift among relational ones. Quest's datasheet also describes turning ERD models into JSON document models. Then add the modeling point: a document model is designed around how the application reads data, so you choose what to embed and what to reference rather than copying the relational design.
Worked example: model a library lending system
The prompt: "Members borrow books. The library owns several copies of each title. Draw the model."
Ask before drawing. Are reservations and fines in scope? One branch or several? Can a member borrow several copies at once? Two minutes of questions is part of the answer.
Logical model. Member, Title (ISBN, name), Author, Copy and Loan. A title can have many authors and an author many titles, so resolve that with a Title Author entity whose key is both parents' keys: two identifying relationships. Copy relates to Title. If copies carry a library-wide barcode, the barcode is Copy's key and the link to Title is mandatory non-identifying. If copy numbers restart for each title, Copy is dependent on Title and the relationship is identifying. Say which you chose and why. Loan has mandatory non-identifying relationships to Member and Copy, plus loan date, due date and a return date that stays empty until the book comes back.
Physical decisions. "A copy can only be on one open loan" is a business rule the logical model cannot express on its own. How you enforce it depends on the target platform, for example a unique index limited to rows with no return date where the database supports one.
The reporting follow-up. Expect something like: "Now they want to know how long books stay out." Declare the grain first: one row per loan. A loan moves through predictable milestones, so this suits what Kimball calls an accumulating snapshot: a date key for each milestone (borrowed, due, returned), typically all pointing at one date dimension used in different roles, a lag measure such as days on loan, and a row that is updated when the book returns.
Why this answer is strong: it asks about scope, fixes the many-to-many relationship, explains the Copy key choice both ways, separates a business rule from a physical constraint, and states the grain before adding a fact table. A weak answer draws one Book table with Author1 and Author2 columns (a first normal form failure) and never says what a fact row means.
How interviewers check your erwin experience
Expect follow-ups such as: which erwin version you used; whether models lived in a Mart or as files; the last time Complete Compare surprised you and what you did about it; who set up the naming standards and what broke when people ignored them. Prepare one true story per feature. If your hands-on tool was a different one, such as ER/Studio or Oracle SQL Developer Data Modeler, say so and map the concepts. That lands better than claiming erwin depth you cannot back up.
A one-week prep plan
- Days 1–2: Take a messy spreadsheet, normalise it to third normal form, then write down which tables you would flatten for reporting and why.
- Days 3–4: Build the library model as a Logical/Physical model, forward engineer it and read the DDL. Use erwin if you have access; otherwise any modeling tool, so you can explain the steps.
- Day 5: Design a star schema for loans with a Type 2 member dimension. State the grain in one sentence.
- Day 6: Reverse engineer a sample database, change a table by hand, and work out how Complete Compare would show and resolve the difference.
- Day 7: Answer the twelve questions out loud, under two minutes each.
For spoken practice, Verve AI's SQL interview copilot listens to the question and shows suggestions only you can see. Upload your resume and add STAR stories from your modeling projects to its Knowledge Base, and answers draw on the documents you have enabled. Related: SQL interview questions for three years of experience, SQL DBA interview questions and Snowflake interview questions.
Sources
- erwin model types (Logical, Physical, Dimensional, Logical/Physical): https://bookshelf.quest.com/bookshelf/9.7.00/Bookshelf_Files/HTML/ERwin%20Help/Model_Types.html
- Normal forms, examples, third normal form as the practical level: https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
- Identifying relationships, dependent entities, IDEF1X and IE line styles: https://bookshelf.quest.com/bookshelf/9.6/Bookshelf_Files/HTML/ERwin%20Overview/Identifying_Relationships.html
- Optional non-identifying relationships, non-key area, nulls, line styles: https://bookshelf.quest.com/bookshelf/public_html/12.5/Content/User%20Guides/erwin%20Help/Optional_Non_Identifying_Relationships_Logical.html
- erwin notations (IDEF1X, IE, dimensional), reverse engineering, product history: https://en.wikipedia.org/wiki/Erwin_Data_Modeler
- Four-step dimensional design process: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/four-4-step-design-process/
- Grain, atomic grain, declare grain before dimensions and facts: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/grain/
- Avoid snowflaked dimensions: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/snowflake-dimension/
- Type 2 slowly changing dimensions: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2/
- Dimension surrogate keys: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/dimension-surrogate-key/
- Additive, semi-additive and non-additive facts: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/additive-semi-additive-non-additive-fact/
- Accumulating snapshot fact tables: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/accumulating-snapshot-fact-table/
- Forward engineering steps and DDL preview: https://bookshelf.quest.com/bookshelf/9.7.00/Bookshelf_Files/HTML/ERwin%20Help/Select_schema_generation_options_Forward_Engineering.html
- Complete Compare steps: https://bookshelf.quest.com/bookshelf/public_html/12.5/Content/User%20Guides/erwin%20Help/Compare_two_data_models.html
- Complete Compare scope and ALTER scripts, RE Scheduler, domains and naming standards as reusable standards, Workgroup and Navigator editions, JSON document models: https://dlt.com/sites/default/files/resource-attachments/2024-05/erwin-data-modeler-datasheet-147769.pdf
- Naming Standard objects and glossary: https://bookshelf.quest.com/bookshelf/public_html/12.5/Content/User%20Guides/erwin%20Help/naming_standards.html
- Domain inheritance into columns: https://bookshelf.quest.com/bookshelf/public_html/12.5/Content/User%20Guides/erwin%20Help/Domain_Inheritance_and_Overrides.html
- Supported target databases: https://bookshelf.quest.com/bookshelf/public_html/14.0/Content/User%20Guides/erwin%20Help/Database%20Support.html
- Verve product claims: https://docs.vervecopilot.com/features/interview-copilot and https://docs.vervecopilot.com/features/knowledge-base
Ryan
CEO of Verve AI








