Overview
Data Modeling & Architecture is 10% of the ABDE exam. It covers creating dimensional models, developing logical and physical data models, designing overall system architecture, making technology selection decisions, implementing modern paradigms such as data mesh and data fabric, and managing schema evolution strategies. DASCA names DBeaver and MySQL Workbench, both database design and query tools, so expect to reason about entity-relationship diagrams, keys and table structures as well as large-scale architecture.
The domain exists because structure determines how easily data answers business questions. A well-chosen grain and star schema make reports fast and consistent, and a sound architecture keeps batch, streaming and AI workloads from fighting each other. Modeling decides the shape of tables that Domain 2 stores and partitions, and its schema evolution strategy is the long-term counterpart of the source-level changes handled in Domain 1.
How it's tested
Questions present a business process, reporting requirement or organizational situation and ask for the BEST model, architecture or paradigm. Expect to identify a grain, choose a slowly changing dimension type, pick between architectures such as Lambda and Kappa, or decide whether mesh or fabric fits the organization's problem.
-
Design the dimensional model
Identify the grain, facts, dimensions and slowly changing dimension handling from a business process description.
-
Select the architecture or technology
Match latency, volume, consistency and team constraints to an architecture or tool category.
-
Evolve without breaking consumers
Choose a schema change strategy such as additive changes, versioned views or deprecation periods.
Finance must report sales by the region a customer belonged to at the time of each sale, even after customers move. Overwriting the region (Type 1) would restate old sales under the new region. A Type 2 dimension adds a new customer row with a new surrogate key and effective dates when the region changes, so each fact keeps pointing to the version that was valid when the sale happened.
Practical applications 6
Official practical applications from the DASCA outline, in outline order.
5.1 Creating dimensional modelsFacts, dimensions, grain and slowly changing dimensions.
- What it means
- Dimensional modeling organizes analytic data into fact tables holding measurable events, such as sales amounts, and dimension tables holding descriptive context, such as customer, product and date, usually arranged as a star schema.
- What to understand
- Declare the grain first, meaning exactly what one fact row represents, such as one line item per order; mixing grains in one table produces double counting. Facts can be additive (summable across all dimensions, like revenue), semi-additive (like account balances, which cannot be summed over time) or non-additive (like ratios). Slowly changing dimensions record attribute changes: Type 1 overwrites, Type 2 adds a new row with effective dates to keep history, and Type 3 keeps a limited previous value in a separate column.
- How it shows up
- A report double counts after an order-level shipping fee was added to a line-item fact table, revealing a grain mismatch.
- Common confusion
- Choosing Type 1 when the scenario requires history as it was at the time of each event, which only Type 2 preserves fully.
5.2 Developing logical and physical data modelsFrom business entities to concrete tables and types.
- What it means
- A conceptual model names the main business entities and relationships, a logical model adds attributes, keys and relationships independent of technology, and a physical model implements it for a specific platform with data types, indexes, partitions and constraints.
- What to understand
- Normalization to third normal form removes redundancy and suits transactional systems, while analytic models deliberately denormalize for simpler, faster queries. Surrogate keys (system-generated integers) insulate dimensions from changes in natural business keys and make Type 2 history possible. The same logical model can have very different physical designs on a row-based database and on a columnar lakehouse.
- How it shows up
- A scenario asks which modeling stage should define data types and partitioning, or why a dimension uses a surrogate key instead of the source customer number.
- Common confusion
- Mixing up logical and physical concerns, such as debating index choices while still identifying entities.
5.3 Designing overall system architectureFitting ingestion, storage, processing and serving together.
- What it means
- System architecture defines the end-to-end components of a data platform, such as sources, ingestion, storage layers, processing engines, serving layers and consumers, and how batch and streaming paths relate.
- What to understand
- Lambda architecture runs a batch layer for complete, accurate results and a speed layer for low-latency results, then merges them, at the cost of maintaining two code paths. Kappa architecture uses a single streaming path and handles reprocessing by replaying the event log. Medallion-layered lakehouses are a common modern pattern; the choice depends on latency needs, reprocessing needs and team capacity.
- How it shows up
- A team struggles to keep batch and streaming logic consistent, which points to Kappa or a unified engine that runs the same logic for both.
- Common confusion
- Thinking Lambda means serverless functions; in data architecture it means separate batch and speed layers.
5.4 Making technology selection decisionsChoosing tools by workload, constraints and cost.
- What it means
- Technology selection matches requirements such as latency, volume, data structure, consistency, cost, skills and vendor constraints to tool categories, such as a relational database, a cloud warehouse, a lakehouse, a NoSQL store, a stream processor or an orchestrator.
- What to understand
- Start from access patterns: OLTP systems handle many small reads and writes, OLAP systems handle large scans and aggregations, and key-value or document stores handle high-volume lookups by key. Consider total cost of ownership, including operations and staff skills, not just license price. Managed services reduce operational burden, while open formats reduce lock-in.
- How it shows up
- A scenario lists requirements such as sub-second lookups by device ID at very high write volume and asks which store category fits.
- Common confusion
- Selecting the newest or most powerful tool rather than the simplest one that meets the stated requirements.
5.5 Implementing modern paradigms like data mesh/fabricDecentralized ownership versus integrated metadata-driven access.
- What it means
- Data mesh is an organizational approach in which business domains own and publish their data as products on a self-serve platform under federated governance, while data fabric is an architectural approach that uses integrated metadata, virtualization and automation to connect data across distributed systems.
- What to understand
- Mesh addresses a central data team becoming a bottleneck, and it requires domains to accept ownership, quality and documentation of their data products. Fabric addresses fragmented, hard-to-integrate data through technology and active metadata, and it can coexist with mesh. Neither is a single product to buy.
- How it shows up
- A company's central data team has a long backlog because every domain's request flows through it, which points to data mesh principles.
- Common confusion
- Treating mesh as a technology rather than an ownership and operating model, or treating mesh and fabric as identical.
5.6 Managing schema evolution strategiesChanging models over time without breaking consumers.
- What it means
- Schema evolution strategy is the planned approach to changing table and model structures, including compatibility rules, versioning, migrations and communication with consumers.
- What to understand
- Prefer additive changes, such as new nullable columns, which do not break existing queries. For breaking changes, use versioned tables or views, run old and new versions in parallel with a deprecation period, and use lineage to identify affected consumers. Data contracts between producers and consumers make expected schemas explicit and checkable in CI before deployment.
- How it shows up
- A team needs to rename a heavily used column, and the question asks for the approach that avoids breaking dashboards.
- Common confusion
- Renaming or retyping columns in place and expecting consumers to adapt immediately.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
Grain
The grain is the precise definition of what one row in a fact table represents, such as one order line or one daily account snapshot.
- Remember
- Declare it before choosing facts or dimensions, and never mix grains in one fact table.
- Tested as
- Double counting scenarios usually trace back to a grain mismatch.
- Trap
- Adding order-level amounts to a line-level fact table.
-
Star vs. snowflake schema
A star schema joins a fact table to denormalized dimensions, while a snowflake schema normalizes dimensions into further sub-tables.
- Remember
- Star schemas need fewer joins and are easier for BI tools; snowflaking saves little space in columnar storage.
- Tested as
- Model design questions favor stars for analytics unless a reason is given.
- Trap
- Normalizing dimensions out of OLTP habit.
-
Slowly changing dimension Type 2
Type 2 handles an attribute change by adding a new dimension row with a new surrogate key, effective dates and a current-row flag.
- Remember
- It preserves history so facts stay linked to the attribute values valid when they occurred.
- Tested as
- Any 'as it was at the time' reporting requirement points here.
- Trap
- Choosing Type 1, which overwrites history.
-
Surrogate key
A surrogate key is a system-generated identifier for a dimension row, independent of the source system's natural key.
- Remember
- It enables multiple versions of the same business entity in Type 2 dimensions and shields the warehouse from source key changes.
- Tested as
- Tested with SCD and source-merge scenarios.
- Trap
- Using the natural key as the primary key of a Type 2 dimension.
-
Additive, semi-additive and non-additive facts
Additive facts sum across all dimensions, semi-additive facts sum across some but not time (like balances), and non-additive facts such as ratios cannot be summed.
- Remember
- Store the components of ratios, such as numerator and denominator, and compute the ratio at query time.
- Tested as
- Appears in questions about wrong totals in reports.
- Trap
- Summing account balances across months.
-
Normalization (3NF) vs. denormalization
Third normal form removes redundancy so each fact is stored once, while denormalization repeats data to simplify and speed up reads.
- Remember
- 3NF suits transactional writes; denormalized dimensional models suit analytics.
- Tested as
- Questions contrasting OLTP and analytic designs.
- Trap
- Applying 3NF to a reporting model and creating many-join queries.
-
Lambda vs. Kappa architecture
Lambda combines a batch layer and a speed layer, while Kappa processes everything as a stream and reprocesses by replaying the log.
- Remember
- Lambda's cost is maintaining two code paths; Kappa needs a replayable, retained event log.
- Tested as
- Architecture selection questions with both real-time and historical needs.
- Trap
- Confusing Lambda architecture with serverless functions.
-
OLTP vs. OLAP
OLTP systems process many small transactional reads and writes, while OLAP systems run large analytical scans and aggregations.
- Remember
- Run analytics on a separate OLAP platform or replica instead of the production OLTP database.
- Tested as
- Technology selection and architecture questions.
- Trap
- Pointing BI dashboards at the production transactional database.
-
Data mesh principles
Data mesh rests on domain ownership, data as a product, a self-serve data platform and federated computational governance.
- Remember
- It is an organizational and operating model supported by a platform, not a single product.
- Tested as
- Scenarios with central-team bottlenecks and domain expertise.
- Trap
- Thinking mesh removes the need for shared governance standards.
-
Data fabric
Data fabric is an architecture that uses active metadata, integration and virtualization to provide unified access across distributed data sources.
- Remember
- It is technology-led integration and can complement a mesh.
- Tested as
- Scenarios about fragmented systems needing unified, governed access.
- Trap
- Using mesh and fabric as interchangeable terms.
-
Data contract
A data contract is an explicit agreement on schema, semantics and quality between a data producer and its consumers.
- Remember
- Contracts are checked automatically so breaking changes fail before deployment rather than in production.
- Tested as
- Schema evolution questions involving many consumers.
- Trap
- Relying on informal announcements of schema changes.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
SCD Type 1 vs. Type 2 vs. Type 3
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Type 1 | Overwrite the attribute value | No history; past facts reflect the new value | Using it when reports must show values as they were |
| Type 2 | Add a new row with new surrogate key and effective dates | Full history; facts link to the version valid at the time | Forgetting surrogate keys and current-row flags |
| Type 3 | Keep the previous value in an extra column | Limited history, usually just one prior value | Expecting it to track many successive changes |
Lambda vs. Kappa architecture
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Lambda | Batch layer plus speed layer, merged at serving | Accurate batch results plus fast approximate ones, but two code paths | Underestimating the cost of keeping both paths consistent |
| Kappa | Single streaming path, reprocessing by log replay | One code path, needs a retained replayable log | Choosing it when the event log is not retained long enough to replay |
Data mesh vs. data fabric
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Data mesh | Domain-owned data products on a self-serve platform with federated governance | Organizational change in ownership and accountability | Buying a tool and calling it a mesh |
| Data fabric | Metadata-driven integration layer across distributed systems | Technology-led unified access and automation | Expecting it to fix unclear data ownership |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Normalize everything
- What candidates pick
- A fully normalized 3NF reporting model with product, category and brand in separate tables.
- Why it looks right
- Normalization is taught as good database design.
- Why it's wrong
- Analytic queries then need many joins and are harder for BI users; a star schema with denormalized dimensions is simpler and faster for analysis.
Clue to watch for The consumers are analysts and dashboards, not a transactional application.
-
Overwrite the attribute
- What candidates pick
- Update the customer's region in place when they move.
- Why it looks right
- It is simple and keeps one row per customer.
- Why it's wrong
- Historical sales are restated under the new region; Type 2 keeps history so past facts remain tied to the old region.
Clue to watch for Reports must show attributes as they were when each event happened.
-
Mixed grain fact table
- What candidates pick
- Add an order-level shipping fee column to an order-line fact table.
- Why it looks right
- It keeps all order information in one place.
- Why it's wrong
- Summing the fee across lines multiplies it by the number of lines; it belongs in an order-grain fact or must be allocated to lines.
Clue to watch for Totals are too high for orders with several lines.
-
Mesh as a tool
- What candidates pick
- Purchase a data mesh platform to solve a central team's backlog.
- Why it looks right
- Vendors market products under the mesh label.
- Why it's wrong
- The backlog comes from centralized ownership; mesh requires domains to own data products under federated governance, with a platform as an enabler.
Clue to watch for The problem described is organizational, such as bottlenecks and unclear ownership.
-
Newest technology wins
- What candidates pick
- A distributed streaming engine for a 20 GB dataset refreshed weekly.
- Why it looks right
- Big data exams seem to reward big data tools.
- Why it's wrong
- The requirements are met by a simple warehouse or database job at far lower cost and complexity; technology should follow volume, latency and skills.
Clue to watch for Modest volume, relaxed latency or a small team in the scenario.
-
Lambda means serverless
- What candidates pick
- Interpret 'Lambda architecture' as running pipeline steps on serverless functions.
- Why it looks right
- AWS Lambda is a familiar product name.
- Why it's wrong
- In data architecture, Lambda refers to combining a batch layer and a speed layer, unrelated to serverless compute.
Clue to watch for The question discusses batch and real-time views of the same data.
Answer framework
For ABDE modeling and architecture questions, reason through these steps before choosing.
- Identify the business process and declare the grain of each fact table before picking measures or dimensions.
- Choose dimension handling from the history requirement: Type 1 for corrections, Type 2 when reports need values as they were.
- Separate logical decisions (entities, keys, relationships) from physical ones (types, partitions, indexes) and pick denormalized stars for analytics.
- Select architecture and technology from latency, volume, reprocessing and team constraints, preferring the simplest design that meets them.
- Plan schema changes as additive or versioned, backed by data contracts and a deprecation period, and match data mesh to ownership bottlenecks and data fabric to integration problems across fragmented systems.
Practice question
Want more like this? The ABDE practice test has questions for every domain.
Checklist
0 of 11
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Start with dimensional modeling, because grain, facts, dimensions and slowly changing dimensions show up across the exam. Take two or three business processes you know, such as online orders or subscriptions, and sketch a star schema for each in DBeaver or MySQL Workbench, writing the grain as a sentence first.
Next, study the logical-versus-physical distinction and when normalization helps or hurts. Candidates often underestimate semi-additive facts and the role of surrogate keys, so practice a Type 2 change by hand: insert a new row, close the old one with an end date, and confirm that old facts still join to the old version.
Then move to architecture and technology selection. Compare Lambda and Kappa, OLTP and OLAP, and warehouse, lakehouse and NoSQL stores in a table of latency, volume and cost. Read the four data mesh principles and contrast them with data fabric.
Finish with schema evolution strategies and data contracts. Afterwards, answer timed questions and, for each miss, note whether you misread the grain, the history requirement or the actual constraint. Then revisit Domain 2 to connect logical models with partitioning and file layout.
FAQ
How much of the ABDE exam is modeling and architecture?
DASCA weights Data Modeling & Architecture at 10%, which suggests about 8 of the 75 questions. DASCA does not publish per-domain counts, and modeling concepts also appear inside other domains' scenarios.
Why does DASCA mention DBeaver and MySQL Workbench?
They are database design and query tools that support entity-relationship diagrams and schema work. Questions focus on the modeling concepts they support, such as keys, relationships and table design, rather than tool menus.
Which slowly changing dimension type should I choose?
Use Type 1 when you are correcting errors or history does not matter, Type 2 when reports must reflect values as they were at the time of each event, and Type 3 when only a single prior value is needed.
Is a star schema always better than a snowflake?
For analytics, a star is usually simpler and faster because it needs fewer joins. Snowflaking can make sense for very large, shared hierarchies, but in columnar storage it rarely saves meaningful space.
What is the difference between data mesh and data fabric?
Data mesh is an organizational model in which domains own data products under federated governance. Data fabric is a technology approach that uses metadata and integration to unify access across systems; the two can coexist.
When should I choose Kappa over Lambda architecture?
Choose Kappa when one streaming code path can serve both real-time and historical needs and the event log is retained long enough to replay. Lambda fits when batch recomputation is needed for accuracy and the team can maintain two paths.
How does this domain connect to storage?
Modeling defines the logical shape of tables, while Domain 2 decides their physical storage, partitioning and file formats. Good designs consider both together.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 5 knowledge
You have covered dimensional models, logical and physical design, architecture, technology selection, data mesh and fabric, and schema evolution. Test whether you can apply them to exam scenarios.