Data Modeling

Conceptual data model: This model focuses on high-level business concepts and how data is used in an organization. Rather than explaining technical details, this model defines the scope of a data system by identifying the type and kind of data, the attributes and the relationships between them. Conceptual data models offer a shared understanding of your data landscape to both technical and nontechnical audiences, helping bridge the technical gap and promote alignment across teams.

Logical data model: This model builds on the conceptual data model by adding more detailed and technical information, such as a defined structure, organization and relationship of the data. This model focuses on the representation of your data and the ways it is logically organized, but it doesn’t get into the specifics of how that data is stored or accessed, such as within a database management system or storage technology. This model can help designers and developers ensure that the final database design meets organizational outcomes as well as the functional needs of the team.

Physical data model: This is a detailed representation of how data is stored, organized and managed in a specific database management system. This model translates the logical data model into a technical blueprint to create and maintain a working database, such as in a SQL Server or other data warehouse. The physical model also optimizes queries through indexing, defining table partitioning or specifying storage requirements.

The Key Components of Data Modeling

Data modeling maps several key features to trace the organization of data within a system, database or application.

Entities: Entities refer to real-world objects or concepts that hold data and need to be tracked. Examples include customer information, a product, an order or a location. Entities usually function as the cornerstone for any data model and are usually structured as a table within a relational database.

Attributes: These are the specific characteristics that describe or define the entity. These can be used to group, filter or reorder a dataset, but they cannot be reduced any further. For example, if an entity is one of your products, the attribute may be the particular SKU, description, price or category.

Relationships: Within the data model, relationships refer to the connections between entities and their attributes and help ensure the model accurately reflects the real-world actions or dependencies between entities. This is an essential feature of any model to maintain data integrity and support queries that span multiple entities. There are three types of relationships that data modeling tracks:

1. One-to-one: This is used in a data model when each instance of one entity is associated with exactly one instance of another entity. For example, one person may have a one-to-one relationship with their driver’s license.
2. One-to-many: This is the most common type of relationship in data modeling, and it refers to one entity having multiple instances of another entity. For example, one customer entity may be related to multiple orders; in this case, there may be many orders, but they belong to only one customer.
3. Many-to-many: This occurs when multiple instances of one entity are associated with multiple instances of another entity. This is the most complex type of relationship, and it’s often mapped to a table to track and manage the relationships. An educational institution may use this model to track students and courses; a student can enroll in many courses, while courses have many students enrolled.

Constraints: To ensure data models are accurate, valid and consistent, they must adhere to specific rules or conditions in how data is stored, related and manipulated. The most common types of constraints include:

• Primary keys uniquely identify each record within a table and ensure no duplicates.
• Foreign keys establish and enforce the relationships between tables.
• Unique constraints ensure that a specific column or columns contain unique values across all rows.
• Not null constraints require that certain files must have a value, which prevents incomplete data entries.
• Check constraints help enforce conditions that each value in a column must satisfy.

Taken together, these constraints make sure a database structure aligns with the intended real-world use cases and leads to meaningful analysis.

Metadata: Metadata is essentially “data about data.” It plays a crucial role in effective data modeling by providing essential context and documentation for your data structures. This includes information such as data definitions, data lineage, source systems, update frequencies, data quality metrics and business rules that govern how data should be interpreted and used. In data modeling, metadata helps ensure that entities, attributes and relationships are properly documented and understood across different teams and systems. It also supports data governance initiatives by tracking data ownership, access permissions and compliance requirements. Well-managed metadata enables better model maintenance, facilitates impact analysis when changes are needed and helps prevent misinterpretation of data elements. Modern data modeling tools often include metadata repositories that automatically capture and maintain this information, making it easier to understand how data flows through your organization and ensuring that your models remain accurate and useful over time.

Challenges in Data Modeling

Data modeling can be a complex task. One of the primary issues is choosing the right data model and ensuring that it accurately reflects the real-world entities and relationships. This requires an organization to have a clear idea of both the business requirements and the data.

Another common challenge is managing data complexity, especially when dealing with large datasets or systems that involve multiple data sources. Integrating data from various sources often leads to inconsistencies or discrepancies in how the data is structured or represented. While a lakehouse can alleviate some of the complexity involved with collecting and storing the data, any model needs to have a thorough extract, transform, load (ETL) process to remove duplicated or missing data.

Any data model must also be nimble and responsive to shifting business needs, market trends and technology updates, all while retaining data integrity. This requires ongoing testing and maintenance of datasets, as well as periodic reviews to ensure the models are still aligned with overall business goals and governance standards.

Model proliferation and degradation: A significant challenge in traditional data architectures is the proliferation of multiple, disconnected data models across different systems. Organizations often end up with separate models for their ETL processes, business intelligence tools, data warehouses and analytics platforms, leading to inconsistent definitions, duplicated logic and conflicting results. Over time, these disparate models drift apart as different teams make changes in isolation, creating a fragmented data landscape where the same business metric might be calculated differently across various systems. This model degradation undermines trust in data and creates maintenance overhead as teams struggle to keep multiple versions synchronized. A unified lakehouse architecture addresses this challenge by providing a single system that serves both business intelligence (BI) and ETL workloads, eliminating the need for separate data models. With one authoritative source of truth, organizations can maintain consistent business logic, unified data definitions and centralized governance across all analytical use cases. This approach not only reduces complexity and maintenance costs but also ensures that business users, data engineers and data scientists are all working with the same underlying data model, creating alignment and trust across the organization while accelerating time to insight.

Data modeling for AI and BI integration

The convergence of AI and BI has transformed how organizations approach data modeling. Traditional data models were primarily designed to support reporting and analytics, but the integration of AI capabilities requires a more sophisticated approach that can serve both structured BI queries and the complex data requirements of machine learning (ML) algorithms.

Unified data architecture for AI/BI: Modern data modeling must accommodate the demands of both BI and AI workloads. BI systems typically require highly structured, normalized data for consistent reporting and dashboards, while AI applications often need flexible, feature-rich datasets that can handle both structured and unstructured data. A well-designed data model bridges this gap by creating a unified architecture that supports both use cases without compromising performance or data integrity.

Feature engineering and model preparation: Data models in an AI/BI environment must be designed with feature engineering in mind. This involves structuring data not just for traditional reporting dimensions and measures, but also for creating meaningful features that machine learning algorithms can leverage. The model should facilitate the creation of training datasets, support data normalization for ML algorithms and enable efficient feature extraction while maintaining the referential integrity needed for business reporting.

Real-time and historical data integration: AI applications often require real-time data processing for predictive analytics and automated decision-making, while BI systems need historical data for trend analysis and performance monitoring. Data models must be architected to handle both batch processing for historical BI reports and stream processing for real-time AI predictions. The dual capability ensures that business users can access traditional reports while data scientists can deploy models that respond to changing conditions in real time.

Governance and lineage across AI/BI workflows: As data flows through both AI and BI pipelines, maintaining data governance becomes increasingly complex. Data models must incorporate robust lineage tracking that shows how data moves from source systems through transformation processes to both BI dashboards and AI model training. This transparency is crucial for ensuring data quality and regulatory compliance, and for building trust in both traditional business reports and AI-driven insights.

The integration of AI and BI capabilities within a single platform requires more adaptive and comprehensive data models than traditional approaches. These models must support the full spectrum of analytical needs, from descriptive reporting to predictive modeling.

Data modeling with Databricks

Data warehouse

Traditional data models use a data warehouse, which is structured and optimized for storing and querying processed, cleaned and organized data. Data warehouses typically handle structured data, and they’re designed to ensure data integrity and consistency. One widely used approach is star schema. This design pattern consists of a central fact table surrounded by dimension tables, allowing for efficient querying and analysis of transactional data. Key characteristics of star schema include fact tables and dimension tables.

Users can implement a star schema in Databricks SQL by leveraging some of these best practices:

• Use managed Delta Lake tables for both fact and dimension tables
• Implement surrogate keys using Generated as Identity columns or hash values
• Utilize Liquid Clustering based on frequently filtered attributes for improved query performance
• Define appropriate constraints (e.g., primary Key, foreign Key) for data integrity and query optimization
• Leverage Delta Lake features like Time Travel for historical data access
• Document tables and columns using comments and tags for enhanced data governance

Databricks SQL uses the data lakehouse architecture to accommodate an array of structured and unstructured data. This provides an open, unified platform to ingest, transform, query, visualize and serve data. The primary benefit is being able to use different clouds, different platforms and different formats.

Leveraging ERD and data lineage for effective data modeling

Modern data modeling requires more than just understanding individual tables and their structures. It also requires a comprehensive view of how data entities relate to each other and how information flows through your organization. Entity Relationship Diagrams (ERDs) and data lineage provide this holistic perspective, enabling data architects to make informed decisions when designing new data models or optimizing existing ones.

ERDs for visual data architecture: ERDs act as the visual blueprint of your data architecture, illustrating the primary key and foreign key relationships between tables in an intuitive graph format. These diagrams help data modelers understand the existing data landscape before they design new structures, ensuring that new models align with established relationships and maintain referential integrity. By visualizing how entities connect, ERDs reveal patterns in data usage, identify potential areas for optimization and help prevent the creation of redundant or conflicting data structures.

Data lineage as a modeling foundation: Data lineage tracks data’s journey from its origin through various transformations to its final destination, providing insights into how data flows through a system. This information is invaluable when designing data models because it reveals which data sources feed into specific tables, how data is transformed along the way and which downstream systems depend on particular data structures. Understanding these dependencies allows modelers to make informed decisions about schema changes, identify opportunities for consolidation and ensure that new models support existing analytical workflows.

Unity Catalog: Centralized metadata management: Databricks Unity Catalog is a comprehensive metadata repository that automatically captures and maintains both ERD relationships and data lineage information. Through Catalog Explorer, users can easily access ERDs for any table with foreign key restraints, visualizing relationships at a glance and understanding how data entities connect across their lakehouse architecture. This centralized approach to metadata management ensures that data modeling decisions are based on complete, up-to-date information about existing data structures and dependencies.

Informed data modeling decisions: By combining ERD visualization with comprehensive data lineage tracking, organizations can approach data modeling with a complete understanding of their existing data ecosystem. This knowledge enables modelers to design schemas that leverage existing relationships, avoid unnecessary duplication and ensure that new models integrate seamlessly with established data flows. The result is a more cohesive, maintainable data architecture that supports both current analytical needs and future growth.

This integrated approach to data modeling, supported by Unity Catalog’s metadata management capabilities, transforms data modeling from a siloed activity into a strategic initiative that considers the entire data ecosystem.

Leveraging the Databricks Data Intelligence Platform

Databricks SQL is the intelligent data warehouse built on the Databricks Data Intelligence Platform. It represents a paradigm shift in data warehousing to data lakehouse architecture by combining the best elements of traditional data warehouses with the flexibility and scalability of modern cloud architecture, while adding the power of artificial intelligence. It enhances the capabilities of the Databricks Data Intelligence Platform by facilitating data transformation and analysis for a wide range of users, from business intelligence analysts and data architects to data engineers.

Built on the well-architected lakehouse, Databricks SQL users can:

• Curate data and offer trusted data as a product (DaaP)
• Remove data silos and minimize data movement
• Democratize value creation through self-service experience
• Adopt an organization-wide data governance strategy
• Encourage the use of open interfaces and open formats
• Build to scale and optimize for performance and cost