Design a model

A good semantic model starts with a small, explicit design. Use this guide to scope a small model you can implement and validate without rework.

Before you start, make sure that:

• You have a target business use case with at least one decision or analysis outcome.
• You know the rough shape of your source data.

Step 1: Define the model’s scope

Start by defining a clear scope before you pick concepts, relationships, or properties. Scope is the set of questions your model must answer and the workflow it supports. A tight scope keeps the first version small enough to implement and validate.

For example, if your goal is to identify delayed orders and explain the likely cause, include orders, shipments, and the timestamps needed to detect delay and categorize causes. Explicitly exclude related work that is not required for those questions, like inventory planning, returns/refunds, customer support tickets, and carrier contract optimization.

Start with 1–3 questions like:

• Which orders are delayed beyond their promised ship date?
• Which delayed orders are missing a shipment record?
• Which delayed orders were handed off late to the carrier?

Make scope concrete with a simple goal/scope table:

Goal	In scope	Out of scope
Identify orders delayed beyond their promised ship date.	Orders and the timestamps needed to compare promised vs actual ship timing.	Shipments, carrier handoff timing, returns/refunds.
Explain delays caused by missing shipment records.	Orders, shipments, and the link between them.	Carrier handoff timing, tracking scan events, inventory planning.
Explain delays caused by late handoff to the carrier.	Orders, shipments, and the timestamps needed to detect late handoff.	Tracking scan events, delivery status, contract optimization.

Tip

• Start with 1–3 high-value questions. If you have more than three, pick the most important and trim the rest.
• In scope: list only what you need to answer the questions. Avoid “nice to have” items that add complexity without changing the outcome.
• Out of scope: name explicit non-goals to prevent scope creep.

Step 2: Determine concepts and relationships

Once you have a clear scope, you can determine the core concepts and relationships you need to model:

• Concepts are the key entities in your domain (for example, Order, Shipment, Customer).
• Relationships are the domain verbs that connect concepts (for example, Order placed_by Customer, Order has_shipment Shipment).

Start by listing the concepts you need. For the running example, that includes:

• Customer: Places orders.
• Order: The unit you measure delay on.
• Shipment: The event (or record) that indicates an order was shipped.
• Product: The item being sold.
• OrderItem: The line-level link between an order and the products it contains.

Then sketch the relationships between them:

Tip

• Keep the diagram small (4–10 concepts) to prevent over-modeling.
• Focus on nouns and verbs that are central to your questions and outcomes. Use domain language that your stakeholders would recognize, rather than technical terms or source schema names.
• Keep it high level: you will add properties and source mapping later, so don’t worry about including every detail at this stage.
• Use the diagram as a coverage check: for each question, identify the concept(s) it talks about and the relationship(s) you need to connect facts. If you cannot map a question, either add the missing concept/relationship or remove the question from scope.

Step 3: Choose concept identifiers

Identifiers determine when two records represent the same entity in your model. Choose them before you implement concepts or start joining sources. If an identifier is unstable, you’ll create duplicate entities and brittle joins.

Use a concept-to-identifier table to force a decision for every concept:

Concept	Identifier strategy	Notes
Customer	customer_id	Use the customer ID from your system of record. Avoid emails—they can change.
Order	order_id	Use an order ID that stays the same across edits and reimports. Avoid row numbers.
Product	sku (or product_id)	Use SKU if it’s stable and globally unique. Otherwise use a product master ID.
Shipment	shipment_id	Carrier IDs can collide. If you have multiple carriers, use (carrier, shipment_id).
OrderItem	(order_id, line_number)	Line items often have no ID. (order_id, line_number) is usually enough.

Tip

• Start from your source data. Pick the field (or fields) that best represent the real-world identity of the concept, not just a convenient row key.
• Name identifiers using domain language. They do not need to match the source column name.
• Prefer keys that are stable and unique for the lifetime you care about.
• When a single field isn’t enough, use a composite identifier. This makes uniqueness explicit and helps you de-duplicate across sources.
• If a concept has no plausible stable identifier, mark it as temporary with a replacement plan. If you can’t name a replacement, drop it from scope or change the question(s) to avoid it.

Step 4: Choose properties

Properties are the attributes you use to answer your in-scope questions and implement your first rules. Choose them after you have picked concepts, relationships, and identifiers. Start with the minimum set you need for initial queries.

Capturing every source column increases complexity with little value. If a field is only used to connect two concepts, model it as a relationship.

A simple must-have vs later table helps keep scope small:

Concept	Must-have	Why it matters (ties to questions)	Later (out of scope)
Customer	customer_id	Needed to group and debug delayed orders by customer.	Customer lifetime value, segmentation.
Order	order_id, placed_at, promised_ship_date, customer_id	promised_ship_date is required to detect delayed orders. customer_id is required to relate orders to customers. You usually model that link as Order placed_by Customer.	Discounts, coupons, marketing attribution.
OrderItem	order_id, line_number, sku	Needed to relate an order to the products it contains. This supports explanations like “delays are concentrated in a specific SKU”.	Unit price, tax, promotions, per-item fulfillment events.
Product	sku	Needed to identify and group items by product. Keep product attributes out of scope until they answer a specific question.	Full catalog attributes, merchandising metadata.
Shipment	shipment_id, order_id, shipped_at, carrier, tracking_number	Needed to detect missing shipments and late handoff to the carrier. order_id is required to relate a shipment back to an order.	Full scan events, delivery signature.

If your must-have list exceeds ~10–15 items, it is likely over-scoped. Pick the single question with highest value and trim.

Tip

• For each must-have property, be able to point to a specific question it helps answer.
• Treat foreign keys (for example order_id, customer_id, sku) as relationship inputs. You can keep them in your source mapping even if you do not expose them as end-user properties.
• Prefer one good timestamp over many ambiguous ones. If you have multiple candidates (for example shipped_at vs handed_off_at), pick one and record the assumption.
• If you need more than ~10–15 must-have properties, your scope is probably too broad. Trim to the single highest-value question and revisit later.

Step 5: Map the model to source data

Source mapping connects your model design to real tables, files, or in-memory objects. Do this before you start writing Model.define workflows. Keep it minimal: map only what you need to answer your first questions and support your identifier strategy.

Organize your source data

Before you map anything, decide whether to use your tables as they are or change them first.

Most of the time, you can start with the tables you already have. PyRel connects tables through relationships, so you don’t need to join them into one large table first. For example, if an orders table has a customer_id column that points to a customers table, you can map that column to a relationship instead of merging the two tables.

Use the table below to decide how to organize each part of your model. The first row covers most cases; use the other rows only when your data calls for it.

If your source data…	Start with…	How it maps to your model
Already has the columns and keys you need	The table or view, used as-is	Each table becomes one or more concepts, and its keys become relationships.
Has extra columns, or needs columns renamed, filtered, or converted	A view built from it	A view lets you pick and clean up columns without changing the original table.
Stores repeating values, or connects two concepts through a separate table	A child or link table	Each row becomes one entity, or one relationship between two entities.
Needs heavier cleanup before it’s usable	A table you build and refresh yourself	You control what the table holds and when it refreshes.

Note that:

• Starting with the separate tables you already have is a good default, not a requirement. Build a view or a table of your own only when it makes your data easier to work with.
• Tables and model elements don’t have to line up one-to-one. One row can supply facts to several concepts, and several tables can feed the same model.
• Keep the columns that link your tables together, such as an order_id or a customer_id.
• Make sure each source is a supported type. PyRel reads standard Snowflake tables and views (and Iceberg tables), but not temporary, transient, dynamic, or external tables. See which Snowflake objects are supported.

Related resources:

• Declare data sources
• Map foreign key columns to entities
• Define relationships from a join table
• Prepare your data sources

Create a source mapping

Once you’ve chosen a shape for each source, map every concept and relationship to a concrete source. Follow these steps:

1. List the elements you will implement

   Write down the concepts and relationships your first model version needs. Keep the list aligned to your in-scope questions.

2. Choose a concrete source and key fields for each element

   For each element, name the table/file/object you will pull from. Record the identifier fields and join keys you will rely on.

3. Record the minimum transformations and assumptions

   Note the few rules you must apply to make the data usable. For example: normalization, type casts, deduping, and late-arriving data.

4. Sanity-check one happy-path example

   Pick one known-good row per concept and confirm it produces exactly one entity with the identifier strategy you chose.

A mapping matrix makes the plan concrete. Here are some example rows in a mapping matrix:

Model element	Source (type)	Source field → model mapping	Notes
Customer	customers (table)	customers.customer_id column maps to Customer identifier
Order	orders (table)	orders.order_id column maps to Order identifier orders.prom_date maps to Order.promised_ship_date	orders.prom_date is a string, so it must be converted to a date
Order placed_by Customer	orders (table)	orders.customer_id column maps to “Order placed_by Customer” relationship	Some orders do not have a customer_id so we need to handle NULLs
OrderItem	order_items (table)	order_items.(order_id, line_number) columns map to OrderItem identifier order_items.sku column maps to “OrderItem for_product Product” relationships
Shipment shipped_for Order	shipments (table)	shipments.order_id column maps to “Shipment shipped_for Order” relationship	Some orders have multiple shipments because they were shipped in parts as products became available

Once every in-scope concept and relationship has at least one mapped source, you are ready to start implementation. Start by creating a Model instance in Python, declaring concepts, relationship and properties, and data sources, then move on to defining base facts and derived logic.

Model design worksheet

Use the following worksheet template to help you work through the design steps in a structured way and capture your decisions in one place:

# Model design worksheet

## Scope

Write 1–3 questions your model must answer:

- One sentence question
- One sentence question
- One sentence question

| Goal | In scope | Out of scope |
| --- | --- | --- |
| One sentence outcome | Workflow + elements included | Explicit non-goals |

## Concepts and relationships

- Concepts: `ConceptA`, `ConceptB`, `ConceptC`
- Relationships (domain verbs): `ConceptA` *verb* `ConceptB`, `ConceptB` *verb* `ConceptC`

Draft a relationship diagram (keep it small).

## Identifiers

| Concept | Identifier strategy | Stable or temporary? | Notes |
| --- | --- | --- | --- |
| `Concept` | `field` (or `field1 + field2`) | `stable` or `temporary` | Why this key works, or replacement plan |

## Properties

| Concept | Must-have | Later (out of scope) | Notes |
| --- | --- | --- | --- |
| `Concept` | `prop1`, `prop2` | `prop3`, `prop4` | Tie must-haves to key question(s) |

## Source mapping

| Semantic element | Source | Key fields | Notes |
| --- | --- | --- | --- |
| `Concept or relationship` | `table/file/object` | `join keys / id fields` | Transforms, gaps, and workarounds |