Primary Keys
Last updated
Last updated
Models are critical to DinMo's functionality, serving as the backbone for most operations within the platform. Many objects and features in DinMo depend directly on models, including:
Segments: Subsets of models used for precise targeting.
Activations: Synchronisation of model or segment results with external platforms.
Predictions: Insights and forecasts powered by data relationships.
Through a relational schema, models describe the customer journey and link to each other via primary and foreign keys. This setup supports:
Complex segment calculations.
Creation of dynamic fields that update in real time.
Advanced data analyses and insights.
To ensure that the system operates smoothly and that all downstream tasks produce accurate results, it is essential to follow strict requirements regarding the primary key of each model.
Primary keys are the unique identifiers for each record in a model. They form the foundation of:
Data accuracy: Ensuring each record is distinct and identifiable.
Relationships: Enabling reliable joins between models.
System efficiency: Supporting smooth operations across all DinMo features.
When primary keys contain duplicates, it can disrupt this foundation, leading to cascading issues throughout the system. Below, we outline the specific impacts and challenges caused by duplicate primary keys.
DinMo automatically calculates key metrics to help users monitor Models, Segments, and Activations. For Segments, this includes statistics such as turnover, size, percentage of parent models, breakdowns, and overlaps.
Duplicate primary keys distort these metrics, leading to inaccurate results and flawed insights.
Example: If a model is used to calculate the average order value, duplicate primary keys can cause orders to be counted multiple times, inflating the results and misleading marketing users.
2. Relationships Between Models
DinMo leverages primary keys and foreign keys to establish clear relationships between models, enabling accurate joins and maintaining data integrity. Duplicates disrupt this by creating ambiguity and breaking expected parent-child relationships.
Example: Linking customer data to transactions may result in unexpected one-to-many relationships if duplicate keys exist in the customer model, leading to unreliable connections and data inconsistencies.
Calculated fields in DinMo rely on primary keys to generate precise metrics. Duplicate primary keys can distort these calculations, inflating or misrepresenting values and leading to inconsistencies.
Example: For instance, Lifetime Value (LTV) calculations per customer may double-count transactions if duplicate customer IDs are present, resulting in inaccurate metrics.
DinMo leverages CDC processes for Activations, using unique primary keys to track changes and calculate differences.
These primary-key-based diffs identify new or updated records, enabling efficient incremental syncs that optimize data warehouse compute usage while ensuring smooth synchronisation with external platforms. Duplicates disrupt this process, causing incomplete updates and inefficiencies.
Example: If the primary key is not unique, the CDC process will fail to identify records correctly, potentially syncing the same record multiple times to a CRM. This can lead to redundant updates, increased API calls, and significant inefficiencies in synchronization.
Segments in DinMo depend on unique primary keys for accurate targeting and efficient activation. Duplicates can inflate segment sizes and introduce errors in inclusion or exclusion lists, undermining campaign effectiveness.
Example: If the transactions model's primary key is not unique and you want to calculate all customers with more than three transactions in the last 12 months, the calculation will be inflated due to duplicate keys.
For guidance on ensuring primary key uniqueness, consult the corresponding section Detect duplicate primary keys
