Data modeling: star schemas, customer 360 profiles, event taxonomies
The discipline of structuring data into optimized schemas for analytics and operations-specifically through dimensional modeling for BI (star schemas), unified entity views for actionable insights (customer 360), and standardized behavioral tracking (event taxonomies).
It directly enables data-driven decision-making, personalization, and scalable data operations, reducing ad-hoc data requests and increasing the reliability of business intelligence. This skill transforms raw data into a strategic asset, impacting everything from marketing attribution to product development prioritization.
1Careers
1Categories
8.7 Avg Demand
20%
Avg AI Risk
How to Learn Data modeling: star schemas, customer 360 profiles, event taxonomies
Focus on understanding core terminology: fact tables, dimensions, surrogate keys (for star schemas); entity resolution, golden records, data sources (for C360); event names, properties, timestamps, user/session identifiers (for event taxonomies). Practice identifying grain and organizing simple business processes (e.g., sales transactions) into a star schema diagram.
Move to implementation: design a star schema for a moderately complex domain like e-commerce, incorporating slowly changing dimensions (SCD Type 2). Build a basic C360 profile by joining data from 2-3 sources (e.g., CRM, website clicks, support tickets) and resolving conflicts. Draft an event taxonomy for a mobile app, defining a naming convention and a data dictionary. Common mistake: conflating operational and analytical schemas; design explicitly for the query pattern.
Master strategic alignment: architect a scalable C360 platform that integrates real-time and batch data sources with a unified identity graph. Design event taxonomies that serve multiple stakeholder groups (product, marketing, data science) with governance and versioning. Optimize star schemas for specific analytical workloads (e.g., aggregating across multiple fact tables) and advise on the trade-offs between normalized, denormalized, and data vault models.
Practice Projects
Beginner
Project
Design a Star Schema for Retail Sales
Scenario
You are given a flat file of transaction records with fields like TransactionID, Date, ProductSKU, ProductCategory, StoreID, StoreLocation, Quantity, and Amount. You need to model this for a BI team to analyze sales by product category over time and by store region.
How to Execute
1. Identify the grain: one row per transaction line item. 2. Create a Fact_Sales table with foreign keys and measurable facts (Quantity, Amount). 3. Create Dimension tables: Dim_Date (with keys for month, quarter, year), Dim_Product (with SKU, Category, Subcategory), Dim_Store (with StoreID, City, State, Region). 4. Draw an ER diagram showing the foreign key relationships from the fact table to each dimension.
Intermediate
Project
Build a Customer 360 Profile from Disparate Sources
Scenario
Merge data from three systems: a CRM (CustomerID, Name, Email, Plan), a web analytics platform (AnonymousID, UserID, PagesViewed, Sessions), and a support ticket system (TicketID, CustomerEmail, IssueType, Resolution). The goal is to create a single view per customer.
How to Execute
1. Define the primary identifier (CustomerID or Email). 2. Create a staging area to clean and standardize data (e.g., unify email formats). 3. Design an entity resolution strategy: use email as a deterministic match key between CRM and support; use UserID to link web data. 4. Create a final C360 table or view joining the resolved entities, including attributes like TotalSupportTickets, AvgSessionDuration, CurrentPlan.
Advanced
Project
Architect a Scalable Event Taxonomy for a SaaS Platform
Scenario
Design an event taxonomy for a B2B SaaS application that serves product managers, marketing, and data science teams. Events need to track user onboarding, feature adoption, and revenue attribution.
How to Execute
1. Facilitate a cross-functional workshop to define business objectives and required metrics. 2. Draft a taxonomy structure with categories (e.g., Account, User, Feature, Revenue), using a consistent naming convention (e.g., `object_action`). 3. Define a schema for each event including required properties (e.g., `feature_used.name`, `user.id`, `timestamp`). 4. Implement governance: create a data dictionary in a tool like Notion or a dedicated catalog, and establish a review process for adding new events.
Tools & Frameworks
Data Modeling & Design Tools
dbt (data build tool)Erwin Data ModelerLucidchart / Draw.ioSQL
dbt is essential for transforming data in the warehouse and documenting models. ERD tools visualize schemas. SQL is the fundamental language for implementing and querying the models.
Identity Resolution & C360 Platforms
RudderStack / Segment (Customer Data Platforms)AmperityCustom SQL/Python scripts
CDPs provide out-of-the-box identity resolution and data pipelines for building profiles. Custom solutions offer more control but require engineering effort.
These platforms provide the instrumentation SDKs, validation, and analysis layers for event taxonomies. Snowplow offers a high degree of ownership over data and schema.
Kimball's methodology is the gold standard for star schema design. The ETC provides a structured approach to defining events. Identity Graph Design is critical for linking user identifiers across devices and sessions.
Interview Questions
Answer Strategy
The interviewer is testing your understanding of performance trade-offs and advanced modeling. Use Kimball's concepts. Answer: 'I'd evaluate two approaches. First, aggregating fact tables: create a pre-aggregated fact table at the monthly grain for fast dashboards, keeping the granular fact table for ad-hoc drill-through. Second, consider a 'drill-across' design using conformed dimensions. The key is to balance storage cost, ETL complexity, and query performance based on the most critical access patterns.'
Answer Strategy
Tests leadership and conflict resolution. Use the STAR method. Sample: 'Situation: Product wanted events named by UI components (e.g., 'button_click'), while marketing wanted them named by user intent (e.g., 'contact_sales_clicked'). Task: I needed a unified schema that served both analytical needs. Action: I organized a workshop to map each stakeholder's key metrics back to underlying user actions. We agreed on a hybrid naming convention: `intent_object_action` (e.g., 'generate_lead_button_click'). I documented this in a central data dictionary. Result: This reduced downstream data reconciliation work by ~30% and became the single source of truth for analytics.'
Careers That Require Data modeling: star schemas, customer 360 profiles, event taxonomies