Data Preparation Best Practices

Use these practices to prepare source tables before building a Kumo Relational schema.

Table Structure and Entity Design

1. Model one entity type or event type per table:

   # Good: Separate tables for different entities
   users = df[['user_id', 'user_name', 'user_email']]
   transactions = df[['transaction_id', 'user_id', 'amount', 'timestamp']]
   # Avoid: Mixing entities in one table
   mixed_data = df[['user_id', 'user_name', 'transaction_id', 'amount']]

2. Use a single event-time column per table:

   # Good: Split tables with multiple timestamps
   policies = df[['policy_id', 'user_id', 'start_date', 'policy_type']]
   claims = df[['claim_id', 'policy_id', 'claim_date', 'claim_amount']]
   # Avoid: Multiple time columns in one table
   mixed_times = df[['policy_id', 'start_date', 'claim_date', 'end_date']]

3. Model many-to-many relationships with junction tables:

   # Good: Junction table pattern
   users = df[['user_id', 'user_name']]
   skills = df[['skill_id', 'skill_name']]
   user_skills = df[['user_skill_id', 'user_id', 'skill_id', 'proficiency']]

Data Preparation

1. Modify dtypes at the pandas.DataFrame level before building a schema:

   # Good: Set proper pandas dtypes first
   df['user_id'] = df['user_id'].astype('int64')
   df['category'] = df['category'].astype('string')
   df['timestamp'] = pd.to_datetime(df['timestamp'])

2. Use consistent naming conventions:

   # Good: Consistent foreign key naming
   users.user_id, transactions.user_id, profiles.user_id
   # Avoid: Inconsistent naming
   users.id, transactions.uid, profiles.customer_id

   Relationship inference links a column only to a primary key of exactly the same name, so orders.user_id finds users.user_id but not users.id. A name shared by more than one table’s primary key is ambiguous and links nothing.

3. Ensure that primary keys are unique and non-null:

   # Validate uniqueness
   assert df['user_id'].nunique() == len(df)
   assert df['user_id'].notna().all()
   # Stop and correct invalid keys before building the schema.

Plan Semantic Types

Before building the schema, determine how Kumo Relational must interpret important columns:

• Identifiers and relationship keys must use ID.
• Free-form descriptions must use text. The current model does not read text features for prediction — see Data Types — so this is about correct typing, not a way to improve predictions.
• Labels from a finite set must use categorical.
• Quantities and measurements must use numerical.
• Event times must use timestamp.

After building the schema, review and correct the inferred semantic types.

Plan Prediction-Ready Relationships

Consider the PQL queries that you want to run when preparing source tables. For example, predicting future transaction activity for each user requires an entity table for users, a timestamped event table for transactions, and a key that relates each transaction to a user.

Common Data Modeling Patterns

Entity-event pattern

# Users (entity) with transactions (events)
users = pd.DataFrame({'user_id': [1, 2], 'name': ['Alice', 'Bob']})
transactions = pd.DataFrame({
    'transaction_id': [1, 2, 3],
    'user_id': [1, 1, 2],
    'amount': [100, 50, 200],
    'timestamp': ['2023-01-01', '2023-01-02', '2023-01-03']
})

Hierarchical entities

# Multiple levels: company -> department -> employee
companies = pd.DataFrame({'company_id': [1, 2], 'company_name': ['ACME', 'TechCorp']})
departments = pd.DataFrame({'dept_id': [1, 2], 'company_id': [1, 1], 'dept_name': ['Engineering', 'Sales']})
employees = pd.DataFrame({'emp_id': [1, 2, 3], 'dept_id': [1, 1, 2], 'emp_name': ['Alice', 'Bob', 'Charlie']})

Junction table for a many-to-many relationship

# Products and categories with many-to-many relationship
products = pd.DataFrame({'product_id': [1, 2], 'product_name': ['Laptop', 'Mouse']})
categories = pd.DataFrame({'category_id': [1, 2], 'category_name': ['Electronics', 'Accessories']})
product_categories = pd.DataFrame({
    'product_category_id': [1, 2, 3],
    'product_id': [1, 1, 2],
    'category_id': [1, 2, 2]
})

Pairs table, for ranking over a fixed candidate set

# One row per (customer, product) pair you want scored
pairs = pd.DataFrame({
    'pair_id': ['1_501', '1_502', '2_501'],
    'customer_id': [1, 1, 2],
    'product_id': [501, 502, 501],
})

See Prediction Types for why this pattern scales better than ranking a raw candidate list.

Summary

Following these best practices helps ensure that your Kumo Relational datasets are well-structured, validated, and optimized for performance.

Table design:

• Model one entity type or event type per table.
• Use a single event-time column per table.
• Use unique primary keys with consistent key names.
• Model many-to-many relationships with junction tables.

Data Preparation:

• Set the appropriate pandas dtypes before building the schema.
• Plan meaningful semantic types such as ID, categorical, text, and numerical.

Relationship planning:

• Prepare consistent key columns for meaningful relationships between entities.
• Consider PQL query requirements when structuring the data.

These patterns help produce source data that is ready for schema building.