Data Modeling Examples for Analytics: Star Schemas, Churn Models and Attribution Tables

Quick answer:

Data modeling for analytics means shaping raw data into tables that answer specific business questions, with a declared grain, clear names and fields that carry business value. Examples include a star schema (a fact table plus dimensions), last-touch attribution, churn cohorts with buckets, a history-keeping customer dimension and a wide BI table. Teams build these as SQL models, starting with views and preprocessing when queries get heavy.

Data modeling is an integral part of any organization’s ability to analyze and extract value from its data. Everyone involved, from collection to consumption, should know what data modeling is and how they, as stakeholders, can contribute to a successful data modeling practice. Read on to learn how developing a strong data model drives growth and productivity throughout your organization.

What is Data Modeling?

A data model determines how data is exposed to the end user. Optimally creating and structuring database tables to answer business questions is the desired role of data modeling, setting the stage for the best data analysis possible by exposing the end user to the most relevant data they require.

Data Modeling Is Not Data Analysis

There is some overlap between data modeling and data analysis (fields need to be understood in order to be mapped or tabled properly, or analyzing performance of the data model as a whole, for example), but it’s an important distinction to remember: proper data modeling is optimizing the process implemented to deliver clean, usable data for analysis.

Data Modeling Makes Analysis Easier

The fundamental objective of data modeling is to only expose data that holds value for the end user. Clearly delineating what questions a table should answer is essential, and deciding on how different types of data will be modeled creates optimal conditions for data analysis. So, while data modeling itself is highly technical, it nonetheless relies on input from non-technical personnel in order to properly define business objectives.

A good business example to consider is marketing attribution, where comparing and contrasting data from both first touch and last touch attribution perspectives may be very significant. Digging deeper, like building a marketing strategy based exclusively on anything “last touch” in the sales funnel (the final tweet, text alert, email promo, etc., that led to a conversion) requires amassing the raw data and filtering in just the last touch of the journey for analysis. This is hard to do with just a single query, which is why it’s important to execute before the time of analysis. Example 2 below shows the table this produces.

With the objectives outlined, database tables can be assembled with each field itself clearly defined. These definitions become part of a data dictionary, an integral part of any successful data model.

The Role of the Data Dictionary

Data definition is essential. An effective data dictionary is an inventory that describes the data objects and items in a data model, and will include at least two key ingredients: properly labeled tables and properly defined terms. At its core, these define the rows (elements) and columns (attributes). Clarity is key here, and it’s important to remember that tables without definitions are counterintuitive (at best). Whether it’s about marketing, web traffic, an email campaign, etc., the goal is exposing clean, raw data. It’s imperative to any successful data model that the definitions for the terms used are clear, concise, and uniform, and that any ambiguity when labeling and defining terms has been removed.

The data dictionary should be maintained by all the data’s stakeholders but especially those responsible for collection and storage. More on this below, but as we want to democratize data for a data-driven culture, having a data expert adjusting or pruning the data model as needed ensures data collection is structured in a way that enhances downstream use.

Today the dictionary often lives beside the models, as column descriptions in dbt YAML files published to a catalog such as Atlan or DataHub. Each field can show its owner and lineage from source to dashboard.

Where Is the Data Going? (And to Whom?)

Increased data volumes can produce barriers to accessibility, or provide a wealth of insight. All kinds of business questions arise, requiring data to be structured accordingly. The comprehension level of the end user is a factor, but the guiding principle is modeling data in a way that makes it very easy to query, so that any end user can utilize the data once received. In other words, it’s meant to be useful. Flooding the user with extraneous and irrelevant data is as frustrating as it is time-wasting. Because there are always fields for engineers (like update timestamps or batch IDs) that hold zero benefit for the end user, attention must be paid to the key takeaway: what fields are exposed to the end users, and how much will those fields denote true business value?

Granularity

All data have different kinds of structure and granularity. Tables are structured to suit end user needs, and granularity defines the level of detail the data provides. Take transactional data as an example. Each row of data could represent an item purchased, and include where it was purchased, how it was purchased, or when it was purchased, even down to the second. (As an example, the latter might be a significant metric for anyone in retail monitoring sales on Black Friday or the day after Christmas.)

Another common business reference is the construction of a churn model, and the various parameters inherent in the end user’s needs. These needs are loosely defined as a time component, with contractual and non-contractual factors playing a role as well. Customer onboarding and retention behavior can vary substantially, and what the end user needs often exists at a more granular customer level: one day after a promo, one month after a free trial, measuring client satisfaction a year out, and so on.

Since the requirements are clear, a solution is easily modeled: the end user defines the stages or fields they care about, and the data modeler creates the model with tables exposing all relevant data. By exposing churn rate data at specific intervals, interpreting and then “bucketing” the interpreted data (adding an extra column to the table to provide better insight), a data model has been constructed that produces significant business value.

This is part of the best practices approach to data modeling: two deciding (human) factors: someone who understands the right questions to ask, and someone to build the data tables that provide answers and insights.

Before building a table, write its grain as a sentence: “one row per order line” or “one row per customer per month.” Mixing grains, such as order totals alongside line items, is a frequent cause of double-counted revenue in dashboards.

Data Modeling in Practice

Utilizing a Domain Knowledge Expert

As mentioned above, defining what a table should look like means modeling data in a way that makes it very easy to query: in essence, so any end user or BI tool can use it. The data engineers do the heavy lifting once they understand the business questions to be answered. And just as someone with business domain knowledge is required for providing the right questions to ask, a data domain expert is necessary for interpreting the technical nuances in the data, what it looks like in its raw form, understanding the instrumentation of the data, and translating it into a model that’s easy to comprehend. Their knowledge is key to what you can and can’t model, and how the tools utilized will be implemented most effectively.

What event the data represents will most likely vary by perspective: for example, a marketing person may see the event as part of a funnel stage (one step has been completed, while another has not), whereas from an engineering standpoint the event might be defined as when a specific POST request was sent. This speaks to another best practice of data modeling: Trust. Both types of expertise require the other to complete the picture and create a model that works for everyone.

As business priorities evolve, the data model must likewise adapt and modify. The entities (and relationships between entities) that make up the schema for queries will change with time and the demands of the enterprise; a data domain expert will ensure that the data model stays up to date and agile enough to continue exposing raw data that is relevant and purposeful.

Runtime vs Preprocessing: It’s About Optimizing Performance

Mapping arcane, technical details within a raw data source and directing it to a user-friendly, easy-to-read outcome can be done with database views and processed at query time. However, if a new table is built on top of that within a data warehouse, modeling the data appropriately as a specific schedule might dictate, that data will be preprocessed.

When weighing the tradeoffs between using runtime for modeling over preprocessed, or pre-calculated, choosing runtime over non-runtime is preferred whenever possible. The more that can be done with the model in runtime, the better (in general), as this translates to less maintenance, while multiple steps with persistent data equate to more management.

Preprocessing is preferred when it’s both calculation-intensive and necessary, as in the churn model referenced previously: looping it through logic is inefficient in runtime, since it would require measuring a ton of data (multiple queries), thereby taking too long to deliver timely insights. Documenting past or forecasting future customer churn rates require different models, each using preprocessed output tables to give desired numbers.

Drawing the line between runtime and preprocessing is the job of an experienced data engineer; as a general rule, it’s good to start “raw” and trend toward more complex models as enterprise needs become more nuanced. Single query works for some tasks; numerous queries may require preprocessing.

In dbt, materialization is a per-model setting: a view runs at query time, a table rebuilds on every run, and an incremental model processes new or changed rows. Where supported, materialized views let the warehouse maintain precomputed results; Snowflake and BigQuery both offer them. Starting with a view and promoting the model when queries become slow or costly follows the same start-raw rule.

The Role of the Analyst

New models are not created overnight. Having to wait hours (or longer) for data processing jobs to arrive, or only receiving once-a-day batched data, will continue to diminish in frequency. End users become more comfortable deploying BI tools for everyday tasks, and the tools themselves continue to become more powerful, reducing the complexity of queries to do analyses, and enabling “self-service” analytics. All are positive developments, but without the interaction and oversight of a data analyst the potential exists for end users to just as easily draw the wrong conclusions from the accelerated access to data.

An analyst assesses data quality and performs data structure checks, isolating and removing bad values. They may create new tables that track volume of data or row counts of data from a specific raw table. The analyst can also automate a data quality model on top of a model that sets a query for customization, identifying poor quality and outliers.

For example, a query structured to evaluate sales data for the current Monday when compared to the previous six months of Mondays would benefit from built-in exceptions in the quality model (think Cyber Monday or Labor Day weekend) that furnish more nuanced, usable analytics.

Defining the role of the analyst ties into the essence of defining the data model, helping shape what the tables will look like and what queries those tables will serve. And that analyst is part of a team serving a data warehouse, all operating with the goal of delivering relevant, real-time, 360-degree data for all end users. When a change to the logic of a model occurs, they’ll be the ones testing it to make sure it still returns the right numbers.

Much of this checking now lives as code beside the models. dbt data tests for uniqueness, nulls, accepted values and relationships run with each build and can block broken changes before dashboards consume them. Data observability tools watch row volume, freshness and schema changes in production. Exceptions such as the holiday Mondays above still need the analyst’s judgment.

Five Data Modeling Examples for Analytics

The five examples below cover common analytics requests, and three of them build on the attribution and churn cases above. Each starts with a business question and a declared grain.

Example 1: A Star Schema for Order Lines

How does revenue vary by product, customer segment, region and week? Declare the grain as one row per product line on an order. The fact table holds quantity and net revenue measures, alongside keys linking to customer, product and date dimensions. Analysts filter and group by dimension attributes, such as customer segment, without knowing the source systems; revenue stays at order-line grain.

Table	Type	Grain	Example columns
fct_order_lines	Fact	One row per product line on an order	order_id, customer_key, product_key, date_key, quantity, net_revenue
dim_customer	Dimension	One row per customer (per version, see Example 4)	customer_key, customer_id, segment, region
dim_product	Dimension	One row per product	product_key, sku, category, brand
dim_date	Dimension	One row per calendar day	date_key, date, week, month, is_holiday

The is_holiday flag makes the Cyber Monday exceptions in the analyst section easy to apply.

Example 2: A Last-Touch Attribution Table

Which channel supplied the last touch before a conversion? For the marketing attribution case above, the intended grain is one row per conversion.

Match each conversion to its preceding touchpoints, then keep only the latest. A window function performs this selection in the model rather than repeating it in every dashboard query. Swapping desc for asc gives first touch, so both attribution views can sit side by side. This query returns conversions with a matching touchpoint.

-- one row per conversion: the last touchpoint before it
select conversion_id, customer_id, channel, campaign, touched_at
from (
  select
    c.conversion_id,
    t.customer_id,
    t.channel,
    t.campaign,
    t.touched_at,
    row_number() over (
      partition by c.conversion_id
      order by t.touched_at desc
    ) as touch_rank
  from conversions c
  join touchpoints t
    on t.customer_id = c.customer_id
   and t.touched_at <= c.converted_at
) ranked
where touch_rank = 1;

Example 3: A Churn Cohort Table with Buckets

To track retention after signup, use one row per customer per month since signup, with signup_month, months_since_signup and is_active. This turns the churn model described earlier into a table that supports comparisons across cohorts.

The bucketing described under Granularity becomes a lifecycle_stage column: new for months 0 to 1, establishing for months 2 to 6, and mature for month 7 onward. Cohort retention is active customers divided by the cohort’s starting size, read along months_since_signup.

Because this model processes every customer and month, build it as an incremental table on a schedule rather than a view.

Example 4: A Customer Dimension That Keeps History

Which plan was a customer on when they purchased? If a customer moves from Basic to Pro in March, overwriting the plan field (a Type 1 change) restates earlier revenue as Pro.

A slowly changing dimension Type 2 adds a row instead. Each version carries valid_from and valid_to dates and an is_current flag; the fact table joins to the version whose validity interval contains the purchase date. dbt snapshots build this history-preserving pattern and add dbt_valid_from and dbt_valid_to columns.

Example 5: One Big Table for Self-Service BI

A wide table gives BI users order lines with customer, product and date attributes already joined onto every row. Users filter one table, with no joins to get wrong, and columnar warehouses compress repeated values well.

The trade-offs are duplicated attributes and maintenance when dimensions change: the table must be rebuilt to reflect those changes. Historical accuracy depends on the joins used to construct it. Many teams therefore build the wide table on top of the star schema as a final business-facing layer, rather than replacing the star schema.

How Teams Layer Models Today

In an ELT stack, raw data lands in the warehouse before SQL models shape it through three layers. Staging usually has one model per source table: rename fields, cast types and exclude engineer-only fields such as batch IDs from downstream output. Intermediate models handle joins and business logic, including touchpoint-to-conversion matching; marts hold business-facing facts, dimensions and wide tables. dbt, SQLMesh and Coalesce support these layers, with BI tools querying marts rather than sources or intermediate calculations.

Above the marts, a semantic layer defines metrics such as net revenue, active customers and retention once, so dashboards using those definitions return consistent numbers. Cube, LookML in Looker and the dbt Semantic Layer fill this role. The layers also map onto the responsibilities described earlier: the domain expert shapes staging and marts, while the analyst defines and tests the metrics.

Conclusion

Advances in cloud data warehousing have tied the capability to source both structured and unstructured data directly to an end user’s ability to analyze and implement that data within minutes. A comprehensive, pragmatic data model further facilitates BI tools turning raw data into business value.

Start with the question, declare the grain, keep models as views until performance calls for preprocessing, and test every change before end users rely on it.

Common questions

What are the main types of data models?

Conceptual, logical and physical models describe the same data at increasing levels of detail. For analytics, common physical designs include dimensional models such as star and snowflake schemas, plus wide denormalized tables. These designs determine how facts and descriptive attributes are organized for querying.

What is the difference between a star schema and a snowflake schema?

A star schema keeps each dimension in one flat table, while a snowflake schema splits dimensions into related sub-tables, such as a product dimension linked to a category table. Star schemas require fewer joins and are easier for BI users; snowflake schemas reduce duplicated attributes.

Should you use a star schema or one big table?

Use both: a star schema as the tested core and wide tables built from it for specific dashboards or self-service users. A wide table simplifies querying, but maintaining it alone becomes harder as dimensions change and historical attributes need to remain accurate.

What tools are used for data modeling today?

SQL transformation tools such as dbt, SQLMesh and Coalesce build models in the warehouse. Semantic layers such as Cube, LookML and the dbt Semantic Layer define shared metrics. Catalogs document fields and lineage, helping end users understand what each model contains and where its data originated.

Where to next

The previous chapter, Data Profiling Best Practices, checks raw data before modeling. Next, Practical Data Dictionary Design and Maintenance covers definitions. Preparing Data for Self-Service Analytics explains handing models to end users, while the Analytics Stack Guide gives an overview of every layer.