Forem: Ed J Wanjama

Database Schemas: Star Schema vs. Snowflake Schema and Choosing the Right Data Warehouse Design

Ed J Wanjama — Mon, 17 Nov 2025 11:07:34 +0000

In a data warehousing environment, the path you take to organize your data can significantly impact query performance, storage efficiency, and maintenance complexity.

Two of the most widely used dimensional modeling approaches are the star schema and snowflake schema.

Both are designed to optimize analytical queries but take different approaches with their data structuring design.

Star Schema is the simpler and more widely adopted of the two designs. It consists of a central fact table surrounded by denormalized dimension tables, creating a shape that resembles a star.

The fact table contains the measurable metrics or facts of your business process, while dimension tables contain descriptive attributes about those facts.

For example, in a retail sales data warehouse, the fact table might contain sales transactions with measures like quantity sold and revenue, while dimension tables would include information about products, customers, stores, and time periods. Each dimension table connects directly to the fact table through a foreign key relationship.

In the below example, the Sales table (fact) connects directly to Customer, Product, Store, and Date (dimensions).

Product
|
Customer — Sales — Date
|
Store

Snowflake Schema on the other hand takes normalization further by breaking down dimension tables into additional related tables, creating a structure that branches out like a snowflake.

Instead of storing all product information in a single dimension table, a snowflake schema might separate products into multiple tables: one for product details, another for product categories, and yet another for product subcategories.

Example: Using the same sales warehouse, the Product dimension might be normalized as:

Product → Product_Subcategory → Product_Category

And, your overall schema will take the shape of a snowflake:

Product_Category
↑
Product_Subcategory
↑
Customer — Sales — Date
↓
Store

Advantages of Star Schema

The star schema's popularity comes from several compelling considerations:

The query performance is typically superior because it requires fewer joins. When an analyst for example wants to examine sales by product category, the database only needs to join the fact table with the product dimension table, a straightforward operation that most query optimizers handle efficiently.

This simplified structure also makes the star schema more intuitive for business users and report developers. Non-technical stakeholders can more easily understand the data model, facilitating self-service analytics. This accessibility translates to faster report development and reduced dependency on IT teams.

From a query optimization perspective, star schemas work exceptionally well with modern analytical databases. Many data warehouse platforms are specifically built for star schema queries, leveraging techniques like bitmap indexing and columnar storage to deliver rapid query responses.

Advantages of Snowflake Schema

Despite the star schema's simplicity, the snowflake schema offers its own set of strengths:

Storage efficiency is often improved because normalization eliminates data redundancy. In a star schema, if you have thousands of products across dozens of categories, the category name is repeated for each product. The snowflake schema stores category information once, with products simply referencing the category ID.

Further, data integrity becomes easier to maintain with a snowflake schema. For example, when you need to update a category name, you modify it in one place rather than updating thousands of product records. This reduces the risk of inconsistencies and simplifies data maintenance procedures.

For organizations with highly complex hierarchies or dimensions with many attributes, the snowflake schema can provide better organizational clarity. When dimension tables become complex with dozens of columns, simplifying them into logical groupings can make the data model easier to manage and understand from a data modeling perspective.

Disadvantages and Trade-offs

The star schema's denormalization comes with notable shortcomings. Storage requirements are higher because of data redundancy. In environments with millions of dimension records, this can translate to significant storage costs, though modern storage is relatively inexpensive.

More importantly, data updates and maintenance can be more complex. Changing a product category name in a star schema might require updating thousands of records, increasing the risk of anomalies and requiring more sophisticated update procedures.

The snowflake schema's disadvantages primarily center on query difficulty and performance. Queries require more joins, which can slow down query execution, particularly for customized analytical queries that need to traverse multiple levels of normalization.

The increased number of tables can also make the schema more difficult for business users to navigate without proper documentation and training.

Database optimizers (both tools and professionals) may struggle more with snowflake schemas, as the additional joins create loopholes for poor execution. This can result in unpredictable query performance, particularly as data volumes grow.

Modern Applications and Best Practices

In everyday data environments, the choice between star and snowflake schemas often depends on your specific platform and use case. Cloud data warehouses like Snowflake (despite the name), Google BigQuery, and Amazon Redshift are typically optimized for star schemas, where their columnar storage and distributed processing capabilities shine.

Many modern organizations adopt a hybrid approach. Core business processes use star schemas for optimal query performance, while specialized dimensions that change frequently or have complex hierarchies might be normalized. This pragmatic approach balances performance with maintainability.

The popularity of data modeling tools and frameworks has also influenced schema design decisions. Tools like dbt (data build tool) make it easier to maintain transformations that can flatten snowflake structures into star schemas for consumption layers, allowing teams to have the best of both worlds with data stored efficiently but queried simply.

For real-time analytics and operational reporting, star schemas are generally preferred because query speed is paramount. Business intelligence dashboards serving hundreds of users simultaneously benefit from the reduced join complexity.

On the other hand, data warehouses that primarily serve as systems of record, where data integrity and storage efficiency are more important than query milliseconds, might lean toward snowflake schemas or normalized structures.

Making Your Choice

When selecting between these approaches, consider your query patterns, user sophistication, platform capabilities, and maintenance resources. If your users need fast, intuitive access to data and your platform supports it well, the star schema is often the superior choice. If storage efficiency and data integrity are paramount and you have the technical expertise to manage more complex queries, the snowflake schema may serve you better.

Ultimately, both schemas remain fundamental in data architectural environments; the key is understanding your organization's specific needs and selecting the design pattern that closest matches with your analytical goals, technical capabilities, and business requirements.

Credits and References

Database Schemas: Star Schema vs. Snowflake Schema: https://medium.com/@DataWithSantosh/database-schemas-star-schema-vs-snowflake-schema-528163c4215d

Star Schema vs Snowflake Schema: 6 Key differences:https://www.thoughtspot.com/data-trends/data-modeling/star-schema-vs-snowflake-schema

Microsoft Power BI - Star Schema Design
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
Practical guidance for BI tool implementation

Airbyte - Star Schema vs Snowflake Schema (2025)
https://airbyte.com/data-engineering-resources/star-schema-vs-snowflake-schema

DataCamp - Star Schema vs Snowflake Schema (January 2025)
https://www.datacamp.com/blog/star-schema-vs-snowflake-schema

GeeksforGeeks - Difference between Star Schema and Snowflake Schema (2025)
https://www.geeksforgeeks.org/dbms/difference-between-star-schema-and-snowflake-schema/

Image credit:Data with Santosh: https://medium.com/@DataWithSantosh?source=post_page---byline--528163c4215d---------------------------------------

Is Excel Still Relevant in the Era of Power BI and Python?

Ed J Wanjama — Tue, 21 Oct 2025 05:38:39 +0000

As a business advisor for small organizations, a big part of my role is helping clients identify pivot opportunities that make their operations more efficient, data-driven, and competitive.

Recently, I had an interesting conversation with one of my clients — a small NGO with fewer than 10 staff members — that is looking to upgrade its tools, software, and hardware to attract better partnerships. During our discussion, they asked a familiar question:

“Excel feels outdated. Should we move to something more advanced — like Power BI or Python — for our analysis and reporting needs?”

Their question inspired this article. Should small enterprises and organizations really be shifting toward advanced analytics tools? And do those tools make sense in every context?

The Technology Leap — And the Misconception

It’s true that we’re living in a remarkable era of technological advancement. Tools like Power BI and Python have transformed how large organizations process data — handling massive datasets, generating real-time insights, and even powering machine learning models.

But when it comes to small organizations, the key question isn’t _what’s the most advanced tool available? _It’s what’s the most appropriate one?

Efficiency isn’t only about processing speed or automation — it’s also about applicability and accessibility.

The Case for Simplicity: David and Mary

Take David, a primary school teacher in rural Kenya. Every term, he records student attendance, exam scores, and fee payments. A Power BI dashboard might look impressive, but what David really needs is something simple and reliable — something his colleagues can use without special training.

Excel gives him exactly that: a familiar tool where he can enter data, apply formulas, and quickly visualize performance trends. For David, Excel isn’t outdated — it’s practical.

Now consider Mary, who runs a small bakery in her neighborhood. She bakes bread and cakes daily and sells to walk-in customers. Mary uses Excel to track sales, calculate profits, and even forecast weekend demand. Would it make sense for her to pay for Power BI licenses or hire a Python developer? Not really.

Excel gives Mary just enough analytical power to make data-informed decisions while keeping her costs low.

For people like David and Mary — and for teachers, small business owners, and NGOs — Excel remains the everyday workhorse.

***

Why Excel Still Dominates in Sub-Saharan Africa
Across Kenya and Sub-Saharan Africa, here is what the data is pointing towards:

Micro and small enterprises (MSEs) dominate Kenya’s economy: According to the Kenya MSE Tracker and MSME reports, firms with 1–9 employees make up the majority of businesses. Many of these organizations have limited budgets and technical capacity — conditions where spreadsheets are the natural default.
Technology adoption remains basic: The Africa MSME Pulse survey shows that while MSMEs are embracing digital tools, their use often focuses on simple functions such as mobile payments, record-keeping, and communication — not complex analytics.
Informality remains high: Millions of micro and small enterprises in Kenya operate informally, using low-cost and familiar tools like paper ledgers, basic accounting apps, and Excel spreadsheets.
Infrastructure challenges persist:Inconsistent internet, limited power, and low IT infrastructure — especially in rural areas — make cloud-based tools like Power BI or Python workflows difficult to sustain.
Skills gaps are a major barrier: Digital transformation initiatives across Africa consistently cite training and upskilling as top SME needs. Many organizations simply lack the technical expertise to deploy and maintain advanced analytics systems.

Put together, these realities explain why Excel continues to be the most accessible, affordable, and effective option for millions of small organizations. The Right Tool for the Right Context.

So, is Excel still relevant in the age of Power BI and Python?
Absolutely — but the more important question is: which tool works best for your context?

a) For large-scale, complex, and real-time analytics, Power BI and Python are unmatched.

b) For small enterprises, NGOs, schools, and community organizations, Excel remains cost-friendly, familiar, and highly practical for everyday “light analysis.”

In fact, Excel can also serve as a stepping stone toward advanced analytics. For example, a nonprofit working with farmers might collect field data in Excel spreadsheets before importing it into Power BI or Python for deeper analysis.

***

Conclusion

Excel is far from obsolete or irelevant — it’s contextually powerful.

In an era where technology keeps evolving, Excel stands out not because it’s the newest tool — but because it’s the most relevant tool for many.

It empowers organizations that may not have access to high-end analytics to still make sense of their data, track performance, and make informed decisions.

References
Africa MSME Pulse 2024 Report: "Africa MSME Pulse 2024 Report - GeoPoll" https://www.geopoll.com/blog/africa-msme-pulse-2024/

Image credit
hcmagazine.com

Unlocking Agricultural Insights with Power BI and DAX: A Case from Kenya’s Crop Data

Ed J Wanjama — Thu, 16 Oct 2025 19:03:02 +0000

In today’s data-driven world, the ability to turn raw numbers into actionable insights is a powerful leverage.

In agriculture, farmers, agri-businesses, and policymakers rely on data insights to make decisions that impact food security, productivity, and profits.

One of the most effective tools for achieving this is Microsoft Power BI, a modern platform that turns complex datasets into interactive dashboards and insightful visualizations.

Let’s explore why Power BI is transforming agricultural analysis — and how DAX (Data Analysis Expressions) makes it even more powerful, using examples from this dataset:[https://docs.google.com/spreadsheets/d/1G2RmUQOLM5C_LKWkm0UgDby7Xd0eaRe2zp1PuQ-8LwI/edit?usp=sharing]Kenya Crops Dataset.

***

Power BI and Why It So Useful:

Power BI is a business intelligence tool that connects, cleans, and visualizes data from multiple sources. Its strongest leverage lies in simplicity — you don’t need years of data science experience to use it effectively.

For agriculture, Power BI can show for example how different crops, soil types, or irrigation methods influence yield and profit across counties. With a few steps of data structuring, farmers can uncover trends like:

Which counties produce the highest revenue from specific crops, how weather affects yield during different seasons Or which fertilizer leads to better performance in specific soil types.

In summary, Power BI empowers users to see the bigger picture and make better, data-informed decisions.

***

Data Analysis Expressions (DAX) The Magic Behind Power BI’s analysis:

At the heart of Power BI’s analytics engine lies DAX (Data Analysis Expressions) — a formula language designed for building custom calculations and business logic within Power BI reports.

Think of DAX as the Excel formulas of Power BI, but far more powerful. It allows users to define new metrics, create time intelligence comparisons, and build dynamic insights that automatically update with the data.

Here’s how DAX functions come alive using our Kenya Crops Dataset:

Mathematical Functions:Measuring Performance Example Functions:SUM(), AVERAGE()

DAX: Total Revenue = SUM('Kenya Crops Dataset'[Revenue (KES)])
Average Yield = AVERAGE('Kenya Crops Dataset'[Yield (Kg)])

These formulas calculate the total revenue earned by all farmers and the average crop yield across counties — giving instant insights into performance at a national or regional level.

Text Functions: Creating Clearer Context. Example Functions: LEFT(), RIGHT(),CONCATENATE()

DAX: CountyCode = LEFT('Kenya Crops Dataset'[County], 3) FarmerInfo = CONCATENATE('Kenya Crops Dataset'[Farmer Name], " - ", 'Kenya Crops Dataset'[Crop Type])

With these, you can easily standardize county codes or combine fields into readable identifiers like “Farmer 1 - Potatoes,” improving clarity in dashboards and reports.

Date & Time Functions: Tracking Performance Over Time Example Functions: YEAR(), TOTALYTD(), SAMEPERIODLASTYEAR()

DAX: PlantingYear = YEAR('Kenya Crops Dataset'[Planting Date])
YTD_Revenue = TOTALYTD(SUM('Kenya Crops Dataset'[Revenue (KES)]), 'Kenya Crops Dataset'[Harvest Date])Revenue_LastYear = CALCULATE(SUM('Kenya Crops Dataset'[Revenue (KES)]), SAMEPERIODLASTYEAR('Kenya Crops Dataset'[Harvest Date]))

These functions allow users to analyze trends across seasons or years, showing how revenues or yields change — and whether policies or farming practices are improving results over time.

Logical Functions: Turning Data into Decisions. Example Functions: IF(), SWITCH()

DAX: Profit_Status = IF('Kenya Crops Dataset'[Profit (KES)] > 1000000, "High Profit", "Low Profit")

WeatherEffect = SWITCH('Kenya Crops Dataset'[Weather Impact], "Mild", "Slightly Affected", "Severe", "Highly Affected", "None", "No Impact","Unknown")

These functions help categorize farmers or conditions automatically, making dashboards more intuitive. For instance, you can instantly see which farmers achieved high profits or which weather conditions affected crop outcomes.

***

Turning Insights into Action
With Power BI and DAX:

Farmers, agronomists, and policymakers can go beyond just collecting data — they can translate it into decisions.
Farmers can identify which crops bring higher returns under specific conditions.

County governments can allocate resources more effectively.
Agricultural cooperatives can plan ahead for market fluctuations or weather risks.

In summary, Power BI and DAX make it easier to move from guesswork to growth.

In my experience, Power BI and DAX are more than just analytical tools — they’re clarity engines. They simplify complex realities and make insights accessible to everyone, not just data experts.
For Kenyan farmers and agri - businesses, this means understanding where opportunities lie, improving productivity, and building resilience against uncertainty.

Data becomes a partner — not just a report.

***

Final Thought:

If you’re a farmer, agribusiness leader, or data professional, it’s time to explore how Power BI can transform the way you make decisions.

Start small — visualize your crop data, experiment with DAX formulas, and let your insights guide your next move.

Because when you harness the power of your data, you don’t just grow crops — you grow confidence, efficiency, and impact.

Case study data source:
a) Kenya Crops Dataset: [https://docs.google.com/spreadsheets/d/1G2RmUQOLM5C_LKWkm0UgDby7Xd0eaRe2zp1PuQ-8LwI/edit?usp=sharing]