DEV Community: Samuel Mwai

Unsupervised Learning: Key Concepts and Techniques

Samuel Mwai — Mon, 24 Aug 2026 07:07:38 +0000

Introduction

Unsupervised Learning is a branch of Machine Learning where a computer learns patterns and relationships from data without being given predefined labels or answers. Unlike supervised learning, where a model is trained using input data and known target values, unsupervised learning works mainly with unlabelled data.

The main goal is to discover hidden patterns, groups, structures, or relationships within a dataset. Unsupervised learning is widely used in areas such as customer segmentation, fraud detection, recommendation systems, image analysis, and data exploration.

What Is Unsupervised Learning?

In unsupervised learning, the dataset contains input features but does not contain a target variable that tells the model the correct answer.

For example, a business may have customer information such as:

Age
Income
Number of purchases
Amount spent
Location

Instead of telling the model which customers belong to which group, an unsupervised learning algorithm can analyse the data and discover groups of customers with similar characteristics.

This makes unsupervised learning particularly useful when labelled data is unavailable or expensive to obtain.

Key Techniques in Unsupervised Learning

1. Clustering

Clustering is one of the most common unsupervised learning techniques. It involves grouping similar observations together while keeping dissimilar observations in different groups.

One popular clustering algorithm is K-Means Clustering.

K-Means works by:

Choosing the number of clusters, represented by K.
Randomly selecting initial cluster centres.
Assigning each data point to the nearest centre.
Calculating new cluster centres.
Repeating the process until the clusters become stable.

For example, K-Means can be used by a company to divide customers into groups such as low-value, medium-value, and high-value customers.

A common method for choosing the value of K is the Elbow Method, which compares the number of clusters with the within-cluster variation, commonly measured using inertia.

2. Hierarchical Clustering

Hierarchical clustering creates a hierarchy of groups rather than requiring the number of clusters to be selected at the beginning.

There are two main approaches:

Agglomerative clustering – starts with individual data points and gradually combines similar groups.
Divisive clustering – starts with one large group and repeatedly divides it into smaller groups.

The results can be represented using a dendrogram, which is a tree-like diagram showing how observations or clusters are combined.

3. Dimensionality Reduction

Datasets can contain many features, making them difficult to analyse and visualise. Dimensionality reduction reduces the number of features while attempting to preserve important information.

One important technique is Principal Component Analysis (PCA).

PCA transforms the original variables into a smaller number of new variables called principal components. The first principal component captures the greatest amount of variation in the data, followed by the second component, and so on.

PCA can be useful for:

Reducing the complexity of datasets
Visualising high-dimensional data
Removing redundant information
Improving computational efficiency

4. Association Rule Learning

Association rule learning is used to discover relationships between items or events.

A common example is market basket analysis. A supermarket can analyse customer transactions to discover that customers who purchase one product are also likely to purchase another.

Important concepts include:

Support – how frequently an item or combination appears in the dataset.
Confidence – how often one item is purchased when another item is purchased.
Lift – measures how strongly two items are associated compared with what would be expected by chance.

Algorithms such as Apriori and FP-Growth can be used for association rule mining.

5. Anomaly Detection

Unsupervised learning can also be used to identify unusual observations known as anomalies or outliers.

For example, a bank may analyse transaction data and identify transactions that are significantly different from normal customer behaviour.

Techniques such as Isolation Forest, clustering, and statistical methods can be used for anomaly detection.

Data Preparation for Unsupervised Learning

Data preparation is an important part of unsupervised learning because poor-quality data can produce misleading patterns.

Common preprocessing steps include:

Handling Missing Values

Missing values may need to be removed or replaced using appropriate methods such as the mean, median, or a meaningful category such as "Unknown."

Encoding Categorical Variables

Machine learning algorithms generally require numerical input. Categorical variables such as gender, country, or education level may therefore need to be converted into numerical representations using techniques such as One-Hot Encoding.

Feature Scaling

Scaling is especially important for distance-based algorithms such as K-Means.

For example, if one feature ranges from 0–100 and another ranges from 0–1, the larger feature may have a greater influence on the distance calculation.

Common scaling techniques include StandardScaler and MinMaxScaler.

Evaluating Unsupervised Learning Models

Evaluating unsupervised learning can be more difficult than evaluating supervised learning because there may be no known correct labels.

For clustering, common evaluation methods include:

Silhouette Score – measures how similar an observation is to its own cluster compared with other clusters.
Inertia – measures the distance between observations and their cluster centres.
Davies-Bouldin Index – evaluates the separation and compactness of clusters.

Visualisation is also an important part of evaluation. Scatter plots and dimensionality reduction techniques such as PCA can help us understand whether discovered groups make sense.

Applications of Unsupervised Learning

Unsupervised learning has many real-world applications.

Customer segmentation: Businesses can group customers according to purchasing behaviour and preferences.

Fraud detection: Unusual transactions can be identified as potential fraudulent activities.

Recommendation systems: Patterns in user behaviour can be used to recommend products, movies, or other content.

Healthcare: Patient data can be grouped according to similar characteristics or conditions.

Marketing: Companies can discover groups of customers with similar interests and behaviours.

Data exploration: Analysts can use unsupervised learning to discover patterns before building predictive models.

Advantages and Limitations

One major advantage of unsupervised learning is that it does not require labelled data. This makes it useful when obtaining labels is difficult or expensive. It can also reveal patterns that were not previously known.

However, unsupervised learning also has limitations. The patterns discovered by an algorithm may not always have a clear real-world meaning. Choosing the appropriate algorithm and parameters can also be challenging. In addition, evaluating the quality of the results can be difficult because there may be no predefined correct answer.

Conclusion

Unsupervised learning is an important area of Machine Learning that focuses on discovering hidden patterns and structures in unlabelled data. Key techniques include K-Means clustering, hierarchical clustering, PCA, association rule learning, and anomaly detection.

Successful unsupervised learning requires proper data preprocessing, appropriate algorithm selection, and careful evaluation of the discovered patterns. As organisations continue to collect large amounts of data, unsupervised learning provides valuable tools for exploring that data and discovering insights that may not be immediately visible.

Understanding these concepts provides a strong foundation for more advanced Machine Learning and Data Science applications.

Star vs. Snowflake Schema: Choosing Your Data Warehouse Schema

Samuel Mwai — Wed, 19 Aug 2026 06:56:00 +0000

Introduction

A data warehouse is designed to store and organize large amounts of data so that organizations can efficiently perform analysis, reporting, business intelligence, and decision-making.

One of the most important decisions when designing a data warehouse is choosing the right schema.

Two of the most common approaches are the:

⭐ Star Schema
❄️ Snowflake Schema

Both organize data using fact tables and dimension tables, but they differ in how those dimensions are structured.

Choosing between them can affect:

Query performance
Storage requirements
Data complexity
Ease of reporting
Maintenance
Scalability
BI tools such as Power BI

This article explains both schemas, compares their advantages and disadvantages, and provides a practical guide for choosing the right one.

What Is a Data Warehouse Schema?

A schema is the structure used to organize tables and relationships inside a data warehouse.

For example, imagine a company wants to analyze its sales.

The company might have information about:

Customers
Products
Stores
Dates
Sales transactions

Instead of putting everything into one huge table, a data warehouse separates the information into related tables.

A typical design looks like this:

                DATA WAREHOUSE
                      │
         ┌────────────┴────────────┐
         │                         │
    FACT TABLES              DIMENSION TABLES
         │                         │
   Sales transactions       Customers
   Orders                   Products
   Payments                 Stores
                            Dates

The two major schema designs discussed in this article organize these tables differently.

Fact Tables vs. Dimension Tables

Before understanding star and snowflake schemas, you need to understand two important types of tables.

Fact Tables

A fact table contains measurable business events.

For a sales warehouse, a fact table might contain:

sale_id customer_id product_id date_id quantity sales_amount
1001 25 101 20260101 2 5000
1002 31 105 20260102 1 3000
1003 25 102 20260103 4 8000

The fact table typically contains:

Foreign keys
Numeric measurements
Business transaction identifiers

Examples of facts include:

Sales amount
Quantity sold
Profit
Revenue
Discount
Cost

Dimension Tables

Dimension tables provide context about the facts.

For example, instead of storing the customer's entire information in every sales record, we create a customer dimension.

Customer Dimension
customer_id customer_name city country
25 John Nairobi Kenya
31 Mary Mombasa Kenya
42 Peter Kampala Uganda

Dimensions answer questions such as:

Who?
What?
Where?
When?
Which category?

Common dimensions include:

Customer
Product
Date
Location
Employee
Store

The Star Schema ⭐

A star schema is a data warehouse design where a central fact table connects directly to several dimension tables.

It is called a star schema because the structure resembles a star.

The basic structure looks like:

Customer ─────────► SALES ◄───────── Product
│
│
▼
Store

The SALES table is the central fact table.

The dimensions surround it.

Example of a Star Schema

Imagine an online retailer.

The fact table could be:

Fact_Sales
sale_id customer_id product_id date_id store_id quantity revenue
1 101 501 20260101 10 2 5000
2 102 502 20260101 11 1 3000
3 101 503 20260102 10 3 9000

It could connect directly to:

Dim_Customer
customer_id customer_name city country
101 John Nairobi Kenya
102 Mary Mombasa Kenya
Dim_Product
product_id product_name category brand
501 Laptop Electronics Brand A
502 Phone Electronics Brand B
503 Headphones Accessories Brand C
Dim_Date
date_id date month quarter year
20260101 2026-01-01 January Q1 2026
20260102 2026-01-02 January Q1 2026

The important point is that the dimensions are relatively denormalized.

For example, the product category and brand can exist directly inside Dim_Product.

Advantages of Star Schema 6.1 Simple to Understand

Star schemas are relatively easy for analysts and BI developers to understand.

The structure is straightforward:

Dimensions → Fact Table

This makes it particularly suitable for reporting environments.

6.2 Fast Queries

Star schemas can provide efficient analytical queries because users often need only a small number of joins.

For example:

SELECT
p.category,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p
ON f.product_id = p.product_id
GROUP BY p.category;

The query only needs to join the fact table with the relevant dimension.

6.3 Good for BI Tools

Star schemas work particularly well with business intelligence tools such as:

Power BI
Tableau
Looker
Qlik

In Power BI, a clean star schema often makes the model easier to understand and helps create reliable relationships and measures.

6.4 Easier Reporting

Analysts can easily answer questions such as:

What were sales by country?

What was revenue by product category?

Which store generated the most sales?

The dimensions provide the descriptive information while the fact table provides the measurements.

Disadvantages of Star Schema

Despite its advantages, star schema has some limitations.

Data Duplication

Because dimensions are often denormalized, some information may be repeated.

For example:

product category department
Laptop Electronics Technology
Phone Electronics Technology
Monitor Electronics Technology

The category and department values are repeated.

Larger Dimension Tables

Denormalization can increase the amount of storage required.

Updating Data

If a value is duplicated across many records, maintaining consistency can become more difficult in certain designs.

The Snowflake Schema ❄️

A snowflake schema is similar to a star schema, but dimension tables are further normalized into multiple related tables.

Instead of having one large product dimension:

Dim_Product
│
├── Product
├── Category
└── Department

we separate the information:

             Dim_Department
                   │
                   ▼
              Dim_Category
                   │
                   ▼
              Dim_Product
                   │
                   ▼
               Fact_Sales

The resulting structure looks more complex, which is why it resembles a snowflake.

Example of a Snowflake Schema

Instead of:

Dim_Product
product_id product_name category department
501 Laptop Electronics Technology
502 Phone Electronics Technology
503 Desk Furniture Home

we could normalize the structure.

Dim_Product
product_id product_name category_id
501 Laptop 10
502 Phone 10
503 Desk 20
Dim_Category
category_id category_name department_id
10 Electronics 1
20 Furniture 2
Dim_Department
department_id department_name
1 Technology
2 Home

Now the information is divided among several related tables.

Why Normalize a Data Warehouse?

Normalization reduces unnecessary duplication.

Consider a product dimension with thousands of products.

If every product stores:

Product
Category
Department
Division

the same category and department information may appear many times.

A snowflake schema can store those attributes separately.

For example:

10, Electronics, Technology

can exist once in the category table instead of being repeated across many product records.

This can improve:

Data consistency
Storage efficiency
Maintenance

However, normalization also introduces additional joins.

Advantages of Snowflake Schema 11.1 Reduced Data Redundancy

Because dimensions are normalized, repeated information can be reduced.

11.2 Better Data Consistency

If a department name changes, the change can be made in one location.

For example:

Technology

could be changed centrally rather than updating many product records.

11.3 More Structured Data

Snowflake schemas can be useful when dimensions have complex hierarchies.

For example:

Country
↓
Region
↓
City
↓
Store

or:

Department
↓
Category
↓
Subcategory
↓
Product

These relationships can be represented naturally using normalized tables.

Disadvantages of Snowflake Schema

The main disadvantage is complexity.

A query that could require one join in a star schema might require several joins in a snowflake schema.

For example:

SELECT
d.department_name,
SUM(f.revenue)
FROM fact_sales f
JOIN dim_product p
ON f.product_id = p.product_id
JOIN dim_category c
ON p.category_id = c.category_id
JOIN dim_department d
ON c.department_id = d.department_id
GROUP BY d.department_name;

Compare this with a star schema:

SELECT
p.department_name,
SUM(f.revenue)
FROM fact_sales f
JOIN dim_product p
ON f.product_id = p.product_id
GROUP BY p.department_name;

The star schema is simpler.

Star vs Snowflake

The fundamental difference can be summarized as:

STAR SCHEMA

          Customer
             │
             │

Product ───── Fact ───── Date
│
│
Store

versus:

SNOWFLAKE SCHEMA

             Department
                  │
               Category
                  │
               Product
                  │
                  ▼
               FACT
             /      \
         Customer    Date

The star schema keeps dimensions closer to the fact table.

The snowflake schema breaks dimensions into additional normalized tables.

Star vs Snowflake: Comparison Feature Star Schema ⭐ Snowflake Schema ❄️ Structure Simple More complex Dimensions Denormalized Normalized Number of tables Usually fewer Usually more Joins Fewer More Query simplicity High Lower Storage redundancy Higher Lower Maintenance Simple More structured BI friendliness Excellent Good Reporting Very good Good Complex hierarchies Less natural Very good Learning curve Easier Higher
Which One Is Faster?

There is no universal answer.

Performance depends on:

Database engine
Data size
Indexing
Partitioning
Query design
Columnar storage
Caching
Materialized views
Query optimizer

However, star schemas often have an advantage for analytical workloads because queries can require fewer joins.

Snowflake schemas may require more joins because dimensions are normalized.

Modern cloud data warehouses can optimize many of these operations effectively, so schema choice should not be based purely on theoretical join counts.

Star Schema in Power BI

Star schemas are particularly useful when creating a Power BI semantic model.

For example:

            Dim_Date
                │
                │

Dim_Customer ─── Fact_Sales ─── Dim_Product
│
│
Dim_Store

The fact table sits in the center.

Dimensions filter the fact table.

This creates a clean model for measures such as:

Total Sales =
SUM(Fact_Sales[SalesAmount])

You can then analyze total sales by:

Year
Month
Customer
Product
Category
Store
Country

without putting everything into one massive table.

Why Star Schema Is Popular in Power BI

Power BI works particularly well with a model where:

Dimension → Fact

relationships are clear.

For example:

Dim_Product[ProductID]
│
│ 1
▼
Fact_Sales[ProductID]
*

This represents a one-to-many relationship.

One product can appear in many sales transactions.

Similarly:

Dim_Customer
1
│
▼
Fact_Sales
*

One customer can have many sales.

This design makes filtering and DAX calculations much easier to manage.

When Should You Choose a Star Schema?

A star schema is usually a strong choice when:

You prioritize simplicity

Your analysts should be able to understand the model quickly.

You are building BI dashboards

Especially when using tools such as Power BI or Tableau.

You have straightforward dimensions

For example:

Customer
Product
Date
Store
You want simple queries

Fewer joins can make analytical SQL easier to write and maintain.

You want a semantic model

Star schemas are particularly effective for business reporting.

When Should You Choose a Snowflake Schema?

A snowflake schema may be appropriate when:

Dimensions have complex hierarchies

For example:

Country
↓
Region
↓
City
↓
Store
Reducing redundancy is important

Normalization can reduce repeated dimension information.

Dimensions are very large

Breaking them into smaller related tables may provide organizational or storage benefits depending on the platform.

Data governance is important

Centralizing shared attributes can make some updates and consistency rules easier to manage.

Can You Use Both?

Yes.

Real-world data warehouses do not always have to be purely star or purely snowflake.

A model can contain mostly denormalized dimensions while selectively normalizing particularly complex parts.

For example:

             Dim_Date
                │
                ▼

Customer ─────── Fact_Sales ───── Product
│
▼
Category
│
▼
Department

This is sometimes described as a hybrid approach.

The goal is not to follow a schema design purely because it has a particular name.

The goal is to create a model that balances:

Performance + simplicity + maintainability + business requirements.

A Practical Decision Framework

When deciding between star and snowflake schemas, ask the following questions.

Question 1: Who will use the data?

If the primary users are:

Business analysts
BI developers
Power BI users

a star schema is often easier.

Question 2: How complex are the dimensions?

Simple dimensions:

Customer
Product
Date

→ Star schema is usually a good fit.

Complex hierarchies:

Division
↓
Department
↓
Category
↓
Subcategory
↓
Product

→ Snowflake or a hybrid approach may make sense.

Question 3: How important is simplicity?

If your goal is easy reporting and self-service analytics, favor the simpler model.

Question 4: How much redundancy exists?

If dimensions contain large amounts of repeated information, normalization may be worth considering.

Question 5: What does your database platform support?

Modern data warehouse platforms differ significantly in how they handle joins, storage, compression, and query optimization.

Always evaluate the design against your actual workload.

Example: E-Commerce Data Warehouse

Imagine an e-commerce company wants to analyze:

Revenue
Products
Customers
Stores
Dates

A star schema could look like:

Dim_Customer ─────── Fact_Sales ─────── Dim_Product
│
│
▼
Dim_Store

The fact table contains:

sale_id
customer_id
product_id
date_id
store_id
quantity
sales_amount
profit

The dimensions contain descriptive information.

This would be an excellent starting point for a Power BI sales dashboard.

Example of a More Complex Snowflake

Suppose the company has a very detailed product hierarchy:

Department
│
▼
Category
│
▼
Subcategory
│
▼
Product
│
▼
Fact Sales

For example:

Technology
↓
Computers
↓
Laptops
↓
Gaming Laptop

A snowflake schema can represent this hierarchy without repeating department and category information for every product.

Common Mistakes Mistake 1: Creating One Giant Table

Putting every attribute into one table may seem simple at first, but it can lead to:

Huge tables
Duplicate data
Difficult maintenance
Poor data quality
Complex transformations
Mistake 2: Over-Normalizing

Normalization can be useful, but creating too many tables can make analytical queries unnecessarily complicated.

You don't want a simple sales report to require ten joins.

Mistake 3: Ignoring the Business Question

Schema design should begin with understanding what the organization wants to analyze.

Ask:

What business process are we modeling?

For example:

Sales
Inventory
Marketing
Finance
Customer service

Then identify the facts and dimensions.

Mistake 4: Choosing Based Only on Storage

Storage is important, but it is not the only consideration.

You should also consider:

Query performance
User experience
Maintainability
Data governance
BI tools
Complexity

A Simple Rule of Thumb

If you are unsure which design to choose:

Start with a star schema.

It is generally easier to understand, easier to query, and works very well for analytical reporting and BI.

Move toward snowflake or a hybrid design when there is a specific reason, such as:

Complex dimension hierarchies
Significant redundancy
Governance requirements
Very large dimensions
A database architecture that benefits from further normalization
Conclusion

Star and snowflake schemas are two important approaches to designing analytical data warehouses.

The star schema keeps a central fact table surrounded by relatively denormalized dimension tables.

The snowflake schema takes some of those dimensions and normalizes them into additional related tables.

The key difference is:

⭐ Star = simpler, more denormalized, fewer joins

❄️ Snowflake = more normalized, more tables, more joins

Neither schema is universally better.

The right choice depends on your:

Business requirements
Data structure
Query patterns
BI tools
Data volume
Performance requirements
Governance needs

For many modern analytics and Power BI projects, a well-designed star schema is an excellent default because it provides a balance of simplicity, performance, and usability.

# Statistics for Data Science

Samuel Mwai — Wed, 19 Aug 2026 06:51:05 +0000

Introduction

Statistics is one of the foundations of data science. Data scientists work with large amounts of information, but simply having data is not enough. The real challenge is understanding what the data means, identifying patterns, measuring uncertainty, and using evidence to make reliable decisions.

Statistics provides the mathematical tools needed to do this. It helps a data scientist summarize a dataset, understand how variables behave, identify unusual observations, measure relationships, test assumptions, and make conclusions about populations using samples. Probability is also closely connected to statistics because real-world data contains uncertainty and randomness.

A useful way to think about the role of statistics in data science is:

Collect data → Explore data → Summarize data → Analyze relationships → Test assumptions → Make conclusions → Build models

1. What Is Statistics?

Statistics is the study of how to collect, organize, analyze, interpret, and communicate data.

Suppose a company has information about 10,000 customers. The company might want to know:

What is the average amount customers spend?
What percentage of customers are likely to leave?
Which customer group spends the most?
Is income related to spending?
Did a new marketing campaign increase sales?
How confident are we in our conclusions?

Statistics provides methods for answering these questions.

In data science, statistics is particularly important because data scientists often work with samples rather than complete populations. Inferential statistics helps use sample information to draw conclusions about the larger population.

2. Why Statistics Is Important in Data Science

Statistics is used throughout the data science workflow.

Exploratory Data Analysis

Statistics helps us understand a dataset before building a machine learning model.

For example:

df.describe()

can provide:

Count
Mean
Standard deviation
Minimum
Quartiles
Maximum

Feature Selection

Statistical techniques can help determine whether variables have useful relationships with a target variable.

Machine Learning

Many machine learning techniques have statistical foundations. Understanding distributions, probability, sampling, variance, and estimation helps data scientists understand what models are doing rather than treating them as black boxes.

Experimentation

Statistics is essential for A/B testing and determining whether an observed difference is likely to represent a real effect rather than random variation.

Decision Making

Businesses can use statistical evidence to make decisions about customers, products, pricing, marketing, finance, and operations.

3. Types of Statistics

Statistics can broadly be divided into two major areas:

                    STATISTICS
                        │
            ┌───────────┴───────────┐
            │                       │
      DESCRIPTIVE              INFERENTIAL
      STATISTICS                STATISTICS
            │                       │
      Describe data          Draw conclusions
                              from samples

Descriptive Statistics

Descriptive statistics summarizes the data that has been collected.

Examples include:

Mean
Median
Mode
Range
Variance
Standard deviation
Percentages
Frequencies
Quartiles

Descriptive statistics focuses on describing the observed data rather than making claims about data outside the dataset.

Inferential Statistics

Inferential statistics uses sample data to make conclusions about a larger population.

For example, suppose a company has 100,000 customers but surveys only 1,000 of them.

The sample can be analyzed to estimate characteristics of the larger customer population.

Inferential statistics includes:

Confidence intervals
Hypothesis testing
t-tests
Chi-square tests
ANOVA
Regression analysis

4. Population and Sample

Two important concepts in statistics are population and sample.

Population

A population is the complete group we are interested in studying.

For example:

All customers of a company.

Sample

A sample is a smaller subset selected from the population.

For example:

1,000 customers selected from the company's 100,000 customers.

POPULATION
100,000 customers
        │
        │ Sampling
        ↓
SAMPLE
1,000 customers

Studying an entire population can be expensive, slow, or impossible. Therefore, data scientists frequently work with samples and use statistical methods to make inferences about the wider population.

5. Measures of Central Tendency

Central tendency describes the center or typical value of a dataset.

The three most common measures are:

Mean
Median
Mode

Mean

The mean is the arithmetic average.

For example:

10, 20, 30, 40, 50

The mean is:

(10 + 20 + 30 + 40 + 50) / 5 = 30

In Python:

df["salary"].mean()

The mean is useful when the data is reasonably balanced, but it can be strongly affected by extreme values.

Median

The median is the middle value after the observations have been arranged in order.

For example:

10, 20, 30, 40, 50

The median is:

For an even number of observations:

10, 20, 30, 40

the median is:

(20 + 30) / 2 = 25

The median is often more appropriate than the mean when data contains extreme outliers.

For example, income data can be heavily skewed because a small number of people may earn extremely high incomes.

Mode

The mode is the most frequently occurring value.

Example:

10, 20, 20, 30, 40

The mode is:

Mode is particularly useful for categorical data.

For example, if the most common customer payment method is:

Mobile Money

then Mobile Money is the mode.

6. Measures of Dispersion

Central tendency tells us where the data is centered, but it does not tell us how spread out the observations are.

Measures of dispersion include:

Range
Variance
Standard deviation
Interquartile range

Range

Range is:

Maximum - Minimum

For:

10, 20, 30, 40, 50

the range is:

50 - 10 = 40

Variance

Variance measures how far observations tend to be from the mean.

A larger variance indicates greater spread.

The population variance can be represented as:

σ² = Σ(x - μ)² / N

where:

σ² = population variance
x = individual observation
μ = population mean
N = population size

In practice, Python can calculate variance directly:

df["salary"].var()

Standard Deviation

Standard deviation is the square root of variance.

It provides a measure of the typical spread of observations around the mean.

df["salary"].std()

For example, a dataset with salaries tightly clustered around the average will have a smaller standard deviation than a dataset with salaries spread widely across different values.

The distinction between standard deviation and standard error is important: standard deviation describes variability among observations, while standard error describes uncertainty in an estimate such as a sample mean.

7. Percentiles and Quartiles

Percentiles divide data according to position.

For example:

25th percentile
50th percentile
75th percentile

The 50th percentile is the median.

Quartiles divide data into four sections:

Q1 = 25th percentile
Q2 = 50th percentile
Q3 = 75th percentile

The Interquartile Range (IQR) is:

IQR = Q3 - Q1

The IQR is particularly useful for identifying outliers.

8. Understanding Distributions

A distribution describes how values are spread across a dataset.

One of the most important distributions in statistics is the normal distribution.

A normal distribution is:

Symmetrical
Bell-shaped
Centered around its mean
Characterized by its mean and standard deviation

In a perfectly normal distribution, the mean, median, and mode are equal.

Other important distributions include:

Binomial distribution
Poisson distribution
Uniform distribution
Exponential distribution
Bernoulli distribution

Understanding probability distributions helps data scientists model uncertainty and choose appropriate statistical methods.

9. Probability

Probability measures how likely an event is to occur.

It ranges from:

0 → Impossible
1 → Certain

For example, if a fair coin is flipped:

P(Heads) = 0.5

Probability becomes important in data science because real-world datasets contain uncertainty.

For example:

What is the probability that a customer will default on a loan?

or:

What is the probability that a transaction is fraudulent?

Probability forms part of the foundation for many statistical analyses and machine learning methods.

10. Correlation

Correlation measures the strength and direction of a relationship between two variables.

For example:

Hours studied ↑
        ↓
Exam score ↑

If students who study more generally achieve higher scores, the two variables may have a positive correlation.

Correlation coefficients typically range from:

-1 to +1

Positive correlation

X ↑ → Y ↑

Negative correlation

X ↑ → Y ↓

No linear correlation

No clear linear relationship

Correlation is useful, but an important principle is:

Correlation does not necessarily mean causation.

Two variables can be correlated without one directly causing the other.

11. Covariance

Covariance measures how two variables change together.

If two variables tend to increase together, covariance is positive.

If one tends to increase while the other decreases, covariance is negative.

Unlike correlation, covariance does not have a fixed range such as -1 to +1, which makes correlation easier to interpret when comparing relationships.

12. Hypothesis Testing

Hypothesis testing is used to evaluate claims about a population using sample data.

Suppose a company claims that a new training program increases employee productivity.

We could define:

Null hypothesis

H₀: The training program does not increase productivity.

Alternative hypothesis

H₁: The training program increases productivity.

We then collect data and perform an appropriate statistical test.

13. P-Values

The p-value is commonly used in hypothesis testing to quantify how compatible the observed data is with the null hypothesis under the assumptions of the test.

A small p-value provides stronger evidence against the null hypothesis.

For example, using a conventional significance level of 0.05:

p < 0.05

may lead researchers to reject the null hypothesis.

However, a p-value should not be interpreted as the probability that the null hypothesis is true. It also does not tell you how large or practically important an effect is.

Statistical significance and practical significance are different concepts.

14. Type I and Type II Errors

Hypothesis testing can produce two important types of errors.

Type I Error

A Type I error occurs when we reject a true null hypothesis.

This is commonly described as a:

False positive

Example:

Concluding that a new medicine works when it actually does not.

Type II Error

A Type II error occurs when we fail to reject a false null hypothesis.

This is commonly described as a:

False negative

Example:

Concluding that a medicine does not work when it actually does.

15. Confidence Intervals

A confidence interval provides a range of plausible values for a population parameter based on sample data.

For example, suppose a survey estimates that average customer spending is:

KES 5,000

with a 95% confidence interval of:

KES 4,700 – KES 5,300

The interval communicates uncertainty around the estimate.

Confidence intervals are an important part of inferential statistics because sample statistics are estimates and therefore have uncertainty.

16. Statistical Tests

Different questions require different statistical tests.

Some common examples include:

Statistical Test	Common Use
t-test	Compare means
Chi-square test	Analyze categorical variables
ANOVA	Compare means across multiple groups
Pearson correlation	Measure linear association
Regression	Model relationships between variables
Mann-Whitney U	Compare two groups without assuming normality
Kruskal-Wallis	Compare multiple groups without assuming normality

The choice of test depends on factors such as:

Data type
Number of groups
Sample size
Distribution
Independence
Research question
Statistical assumptions

17. Regression and Statistics

Regression is another major statistical technique used in data science.

For example, linear regression can model the relationship between an independent variable and a dependent variable.

A simple linear regression equation is:

y = β₀ + β₁x + ε

where:

y = dependent variable
x = independent variable
β₀ = intercept
β₁ = coefficient
ε = error term

For example, we could investigate whether advertising expenditure is associated with sales.

Advertising Spend → Sales

Regression can help with:

Prediction
Understanding relationships
Estimating effects
Forecasting
Feature analysis

18. Statistics and Machine Learning

Statistics and machine learning are closely connected.

Consider a classification problem where we want to predict whether a customer will leave a company.

The dataset might contain:

Age
Income
Tenure
Monthly spending
Number of complaints
Customer status

Statistics can help us:

Understand the variables.
Identify missing values.
Detect outliers.
Examine distributions.
Analyze relationships.
Select useful features.
Evaluate model performance.
Quantify uncertainty.

Machine learning then uses algorithms to learn patterns from the data and make predictions.

This is why a data scientist should not only know how to call:

model.fit(X_train, y_train)

but should also understand what the data looks like and what assumptions may affect the model.

19. Statistics in Python

Python provides several libraries for statistical analysis.

Pandas

Pandas is commonly used for data manipulation and descriptive statistics.

import pandas as pd

df.describe()

You can calculate individual statistics:

df["income"].mean()
df["income"].median()
df["income"].std()
df["income"].var()
df["income"].min()
df["income"].max()

NumPy

NumPy provides numerical and statistical functions.

import numpy as np

np.mean(data)
np.median(data)
np.std(data)

SciPy

SciPy provides many statistical tests.

from scipy import stats

stats.ttest_ind(group1, group2)

Statsmodels

Statsmodels is useful for statistical modelling and detailed statistical inference.

import statsmodels.api as sm

20. Example: Analyzing Business Income

Suppose a dataset contains the monthly income of several businesses:

We could calculate:

df["personal_income"].mean()
df["personal_income"].median()
df["personal_income"].std()

The extremely high value of 150000 may affect the mean substantially.

This is why it is important not to rely on a single statistic.

A data scientist should examine:

Mean
Median
Standard deviation
Distribution
Outliers
Quartiles

A boxplot can help identify potential outliers, while a histogram can show whether the distribution is symmetric or skewed.

21. The Central Limit Theorem

The Central Limit Theorem (CLT) is one of the most important ideas in statistics.

In simplified terms, when sufficiently large random samples are repeatedly drawn from a population, the distribution of their sample means tends toward a normal distribution under common conditions.

This helps explain why statistical inference can work even when the original population distribution is not normal.

The CLT is important for:

Confidence intervals
Hypothesis testing
Sampling distributions
Statistical estimation

Understanding sampling distributions also helps explain the difference between the variability of individual observations and the uncertainty of estimated population parameters.

22. Common Statistical Mistakes in Data Science

Knowing statistics is not only about knowing formulas. It is also about avoiding incorrect conclusions.

Mistake 1: Assuming correlation means causation

Two variables can move together without one causing the other.

Mistake 2: Ignoring outliers

Extreme observations can strongly influence statistics such as the mean and some models.

Mistake 3: Using the wrong statistical test

Different tests make different assumptions and answer different questions.

Mistake 4: Focusing only on p-values

A statistically significant result may have little practical importance.

Mistake 5: Ignoring sample bias

A large sample can still produce misleading conclusions if it is not representative of the population.

Mistake 6: Confusing standard deviation with standard error

Standard deviation describes variation among observations, while standard error describes the uncertainty of an estimate.

23. A Practical Statistics Workflow for Data Scientists

A useful workflow is:

1. Understand the dataset
          ↓
2. Identify variable types
          ↓
3. Check missing values
          ↓
4. Calculate descriptive statistics
          ↓
5. Visualize distributions
          ↓
6. Detect outliers
          ↓
7. Examine correlations
          ↓
8. Form statistical questions
          ↓
9. Choose appropriate tests
          ↓
10. Interpret the results
          ↓
11. Build statistical/ML models
          ↓
12. Communicate conclusions

This approach prevents the common mistake of immediately building a machine learning model without first understanding the data.

24. Statistics vs Machine Learning

Statistics and machine learning overlap, but they often emphasize different goals.

Statistics	Machine Learning
Understand relationships	Make predictions
Estimate parameters	Optimize predictive performance
Test hypotheses	Learn patterns from data
Quantify uncertainty	Evaluate generalization
Explain effects	Predict outcomes

In practice, modern data scientists frequently use both.

For example, a data scientist might use statistical analysis to understand which variables are associated with customer churn and then use machine learning to predict which customers are most likely to churn.

Conclusion

Statistics is not an optional skill for data scientists. It provides the foundation for understanding data, measuring uncertainty, testing ideas, and making evidence-based decisions.

The most important areas to learn include:

Descriptive statistics
Inferential statistics
Mean, median, and mode
Variance and standard deviation
Percentiles and quartiles
Probability
Probability distributions
Sampling
Correlation and covariance
Hypothesis testing
P-values
Confidence intervals
Statistical tests
Regression
Sampling distributions
The Central Limit Theorem

The most important mindset is to understand the data before trusting the model.

A machine learning algorithm can produce predictions, but statistics helps you understand whether the data supports those predictions, how uncertain your conclusions are, and whether the patterns you see are meaningful.

In short:

Statistics helps data scientists turn data into evidence, and evidence into informed decisions.

# SQL Joins and Window Functions: A Practical Guide

Samuel Mwai — Wed, 19 Aug 2026 06:45:52 +0000

SQL Joins and Window Functions: A Practical Guide

Introduction

SQL is one of the most important tools for working with data. Whether you are analyzing sales, customers, employees, financial transactions, or business performance, SQL allows you to retrieve, combine, transform, and analyze data stored in relational databases.

Two particularly important SQL concepts are joins and window functions.

Joins allow you to combine information from multiple tables, while window functions allow you to perform calculations across related rows without collapsing the individual rows.

Understanding both concepts is essential for anyone working in data analysis, data science, business intelligence, or database development.

Part 1: SQL Joins

What Is a SQL Join?

A SQL join is used to combine rows from two or more tables based on a related column.

For example, imagine we have two tables:

Customers

customer_id	customer_name	country
1	John	Kenya
2	Mary	Uganda
3	Peter	Tanzania

Orders

order_id	customer_id	amount
101	1	50000
102	2	30000
103	1	20000

The common column is customer_id.

We can use it to connect the two tables:

SELECT
    customers.customer_name,
    orders.order_id,
    orders.amount
FROM customers
JOIN orders
    ON customers.customer_id = orders.customer_id;

The result would be:

customer_name	order_id	amount
John	101	50000
Mary	102	30000
John	103	20000

The join allows us to combine customer information with order information.

1. INNER JOIN

An INNER JOIN returns only rows that have matching values in both tables.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id;

If a customer has never placed an order, that customer will not appear in the result.

For example:

Customers          Orders

1 John             1
2 Mary             2
3 Peter

An inner join returns John and Mary because they have matching orders.

When to use INNER JOIN

Use an inner join when you only want records that exist in both tables.

Examples include:

Customers who have placed orders
Employees assigned to departments
Products that have sales
Students enrolled in courses

2. LEFT JOIN

A LEFT JOIN returns every row from the left table and matching rows from the right table.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

The result might be:

customer_name	order_id	amount
John	101	50000
Mary	102	30000
John	103	20000
Peter	NULL	NULL

Peter appears even though he has no order.

This is one of the most useful joins in data analysis because it allows you to find records that don't have matching information.

For example:

SELECT
    c.customer_name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This finds customers who have never placed an order.

3. RIGHT JOIN

A RIGHT JOIN returns every row from the right table and matching rows from the left table.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id;

RIGHT JOIN is less commonly used because the same result can usually be achieved by switching the table order and using a LEFT JOIN.

For example:

FROM orders o
LEFT JOIN customers c

is often easier to read.

4. FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables.

If a row has no match, SQL fills the missing values with NULL.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
FULL OUTER JOIN orders o
    ON c.customer_id = o.customer_id;

This is useful when you want to identify records that exist in one table but not the other.

For example, you might use it to compare two datasets and find:

Customers missing from another system
Products missing from an inventory database
Transactions that don't match
Data quality problems

5. CROSS JOIN

A CROSS JOIN produces every possible combination of rows from two tables.

Suppose:

Products

Laptop
Phone
Tablet

and:

Countries

Kenya
Uganda
Tanzania

A cross join produces:

Laptop  Kenya
Laptop  Uganda
Laptop  Tanzania
Phone   Kenya
Phone   Uganda
Phone   Tanzania
Tablet  Kenya
Tablet  Uganda
Tablet  Tanzania

SQL:

SELECT
    p.product_name,
    c.country
FROM products p
CROSS JOIN countries c;

If one table has 3 rows and another has 3 rows, the result contains:

3 × 3 = 9 rows

Because cross joins can produce very large results, they should be used carefully.

6. SELF JOIN

A SELF JOIN joins a table to itself.

This is useful when records within the same table are related.

For example, an employee table might contain:

employee_id	employee_name	manager_id
1	John	NULL
2	Mary	1
3	Peter	1

Here, manager_id refers back to employee_id in the same table.

We can use a self join to display each employee and their manager:

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

Result:

employee	manager
John	NULL
Mary	John
Peter	John

Part 2: SQL Window Functions

What Is a Window Function?

A window function performs a calculation across a set of related rows while keeping the individual rows in the result.

This is the key difference between window functions and regular aggregate functions.

For example, consider:

employee	department	salary
John	IT	60000
Mary	IT	70000
Peter	Sales	50000
Jane	Sales	80000

A regular aggregation might calculate:

SELECT
    department,
    AVG(salary)
FROM employees
GROUP BY department;

Result:

department	average_salary
IT	65000
Sales	65000

The individual employees disappear from the result.

A window function allows us to calculate the average while keeping every employee:

SELECT
    employee,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_average
FROM employees;

Result:

employee	department	salary	department_average
John	IT	60000	65000
Mary	IT	70000	65000
Peter	Sales	50000	65000
Jane	Sales	80000	65000

This is why window functions are extremely powerful for analytics.

7. The OVER() Clause

Window functions use the OVER() clause.

For example:

AVG(salary) OVER ()

The OVER() clause tells SQL that we want the calculation to operate as a window over the result set.

There are two particularly important components:

PARTITION BY
ORDER BY

8. PARTITION BY

PARTITION BY divides the data into groups for the window calculation.

For example:

AVG(salary) OVER (
    PARTITION BY department
)

This calculates the average salary separately for each department.

It is similar to GROUP BY, but it does not collapse the rows.

Think of it as:

GROUP BY → reduces rows

PARTITION BY → keeps rows

9. ORDER BY in Window Functions

ORDER BY determines the order in which the window function processes rows.

For example:

SUM(sales) OVER (
    ORDER BY sale_date
)

This can be used to calculate a running total.

Suppose we have:

sale_date	sales
Jan 1	100
Jan 2	200
Jan 3	150

Query:

SELECT
    sale_date,
    sales,
    SUM(sales) OVER (
        ORDER BY sale_date
    ) AS running_sales
FROM sales;

Result:

sale_date	sales	running_sales
Jan 1	100	100
Jan 2	200	300
Jan 3	150	450

This is called a running total.

10. ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row.

SELECT
    employee,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_number
FROM employees;

Result:

employee	salary	row_number
Jane	80000	1
Mary	70000	2
John	60000	3
Peter	50000	4

You can also partition the results:

ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
)

This ranks employees separately within each department.

11. RANK()

RANK() assigns rankings but gives tied values the same rank.

For example:

employee	salary
Jane	80000
Mary	70000
John	70000
Peter	50000

Query:

SELECT
    employee,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Result:

employee	salary	salary_rank
Jane	80000	1
Mary	70000	2
John	70000	2
Peter	50000	4

Notice that there is no rank 3 because Mary and John share rank 2.

12. DENSE_RANK()

DENSE_RANK() is similar to RANK(), but it does not skip ranking numbers.

Using the same data:

SELECT
    employee,
    salary,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Result:

employee	salary	salary_rank
Jane	80000	1
Mary	70000	2
John	70000	2
Peter	50000	3

The difference is:

ROW_NUMBER()
1, 2, 3, 4

RANK()
1, 2, 2, 4

DENSE_RANK()
1, 2, 2, 3

13. LAG()

LAG() allows you to access a value from a previous row.

This is extremely useful when analyzing changes over time.

For example:

SELECT
    sale_date,
    sales,
    LAG(sales) OVER (
        ORDER BY sale_date
    ) AS previous_sales
FROM sales;

Result:

sale_date	sales	previous_sales
Jan 1	100	NULL
Jan 2	200	100
Jan 3	150	200

We can then calculate the change:

SELECT
    sale_date,
    sales,
    sales - LAG(sales) OVER (
        ORDER BY sale_date
    ) AS sales_change
FROM sales;

Result:

sale_date	sales	sales_change
Jan 1	100	NULL
Jan 2	200	100
Jan 3	150	-50

This is useful for:

Month-over-month analysis
Year-over-year analysis
Stock analysis
Customer activity
Revenue changes

14. LEAD()

LEAD() does the opposite of LAG().

It allows you to access a value from a future row.

SELECT
    sale_date,
    sales,
    LEAD(sales) OVER (
        ORDER BY sale_date
    ) AS next_sales
FROM sales;

Result:

sale_date	sales	next_sales
Jan 1	100	200
Jan 2	200	150
Jan 3	150	NULL

15. FIRST_VALUE() and LAST_VALUE()

Window functions can also retrieve the first or last value within a window.

For example:

SELECT
    employee,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS highest_salary
FROM employees;

This allows every employee to see the highest salary within their department.

16. Window Functions vs GROUP BY

One of the most important concepts to understand is the difference between GROUP BY and window functions.

GROUP BY

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Produces one row per department.

Window Function

SELECT
    employee,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS average_salary
FROM employees;

Keeps every employee while adding the department average.

In simple terms:

GROUP BY summarizes rows.

Window functions analyze rows without removing them.

17. Combining Joins and Window Functions

Joins and window functions are often used together in real-world data analysis.

Suppose you have:

Customers
Orders

You could first join the tables:

SELECT
    c.customer_name,
    o.order_date,
    o.amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Then use a window function to rank each customer's orders:

SELECT
    c.customer_name,
    o.order_date,
    o.amount,
    ROW_NUMBER() OVER (
        PARTITION BY c.customer_id
        ORDER BY o.amount DESC
    ) AS order_rank
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Now you can identify the largest order for each customer.

18. Finding the Top Record per Group

One of the most common real-world uses of window functions is finding the top record within each group.

For example, suppose you want the highest-paid employee in every department.

First:

SELECT
    employee,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS rn
FROM employees;

Then use a subquery:

SELECT *
FROM (
    SELECT
        employee,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rn
    FROM employees
) ranked
WHERE rn = 1;

This returns the highest-paid employee from each department.

19. Real-World Applications

SQL joins and window functions are widely used in data analytics.

Sales Analysis

You can use joins to combine:

Customers
Products
Orders
Sales

Then use window functions to calculate:

Customer rankings
Running revenue
Previous month's sales
Top products
Sales growth

Financial Analysis

Window functions can be used for:

Running balances
Transaction rankings
Month-over-month changes
Portfolio performance
Cumulative revenue

Human Resources

You can calculate:

Employee salary rankings
Department averages
Highest-paid employees
Employee salary differences
Hiring trends

Customer Analytics

You can analyze:

First purchase
Most recent purchase
Customer order rankings
Previous purchases
Customer spending trends

20. Best Practices

When working with joins and window functions, several practices can make your SQL easier to understand and more reliable.

Use table aliases

Instead of:

customers.customer_name

use:

c.customer_name

after defining:

FROM customers c

Always specify the join condition

For example:

ON c.customer_id = o.customer_id

Avoid accidentally creating a Cartesian product unless you intentionally need a CROSS JOIN.

Understand your keys

Before joining tables, determine whether the join key is unique.

Joining a one-to-many table incorrectly can create duplicate rows and inflate totals.

Use meaningful window ordering

For example:

ORDER BY sale_date

is meaningful for a running sales calculation.

Use PARTITION BY when analysis needs groups

For example:

PARTITION BY customer_id

allows calculations to restart for every customer.

Conclusion

SQL joins and window functions are essential skills for data analysts and data scientists.

Joins allow you to bring information from different tables together, while window functions allow you to analyze related rows while preserving the original rows.

The most important joins to understand are:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
SELF JOIN

The most useful window functions include:

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()
SUM() OVER()
AVG() OVER()
FIRST_VALUE()
LAST_VALUE()

A simple way to remember the concepts is:

Joins combine tables. Window functions analyze rows.

Once you understand these two concepts, you can solve much more advanced SQL problems, from identifying top-performing customers to calculating running totals and comparing current performance with previous periods.

Data Modelling in Power BI: Joins, Relationships and Schemas Explained

Samuel Mwai — Wed, 19 Aug 2026 06:41:42 +0000

Data modelling is one of the most important concepts in Power BI. A good data model allows reports to produce accurate results, perform efficiently, and remain easy to maintain. A poor data model, on the other hand, can lead to incorrect calculations, confusing relationships, duplicate data, and slow reports.

In Power BI, data modelling involves organising tables and defining how those tables interact with one another. Three concepts are particularly important: joins, relationships, and schemas.

1. What Is Data Modelling?

Data modelling is the process of organising data into tables and defining the connections between those tables.

For example, imagine a company has three tables:

Customers – contains customer information.
Products – contains product information.
Sales – contains information about each sale.

The Sales table might contain:

SaleID	CustomerID	ProductID	Quantity	SalesAmount
1001	C001	P01	2	2,000
1002	C002	P03	1	1,500
1003	C001	P02	4	3,200

The Customers table could contain:

CustomerID	CustomerName	Country
C001	John	Kenya
C002	Mary	Uganda

The Products table could contain:

ProductID	ProductName	Category
P01	Laptop	Electronics
P02	Monitor	Electronics
P03	Printer	Electronics

Power BI can connect these tables using CustomerID and ProductID.

This creates a data model that allows us to answer questions such as:

How much did each customer spend?
Which products generated the most revenue?
Which country generated the highest sales?
How many products were sold in each category?

2. Joins Explained

A join combines data from two or more tables based on a common column.

Joins are commonly encountered when using Power Query in Power BI.

For example, suppose we have:

Customers

CustomerID	CustomerName
C001	John
C002	Mary
C003	Peter

Sales

CustomerID	SalesAmount
C001	2000
C002	1500
C004	3000

The common column is CustomerID.

A join can combine these tables using that column.

Types of Joins

Power Query provides several join types.

Inner Join

An Inner Join returns only records that exist in both tables.

In our example, C001 and C002 exist in both tables, while C003 and C004 do not.

The result would be:

CustomerID	CustomerName	SalesAmount
C001	John	2000
C002	Mary	1500

Inner joins are useful when you only want records with matching values in both tables.

Left Outer Join

A Left Outer Join keeps every record from the left table and adds matching records from the right table.

For example:

CustomerID	CustomerName	SalesAmount
C001	John	2000
C002	Mary	1500
C003	Peter	null

Peter remains in the result because he exists in the left table, even though he has no matching sales record.

This is one of the most commonly used joins.

Right Outer Join

A Right Outer Join keeps every record from the right table and adds matching records from the left table.

Using our example, C004 would be included because it exists in the right table.

Full Outer Join

A Full Outer Join keeps all records from both tables.

It includes matching and non-matching records.

CustomerID	CustomerName	SalesAmount
C001	John	2000
C002	Mary	1500
C003	Peter	null
C004	null	3000

Anti Joins

Anti joins are useful for finding records that do not have a match.

For example, a Left Anti Join could identify customers who have never made a purchase.

This can be extremely useful for data-quality checks and business analysis.

3. Joins vs Relationships

Joins and relationships are related concepts, but they are not the same thing.

A join physically combines columns from tables into a new table during data preparation.

A relationship connects existing tables within the Power BI data model.

For example:

Customers
CustomerID
CustomerName
Country
      |
      | CustomerID
      |
      ↓
Sales
SaleID
CustomerID
ProductID
SalesAmount

The tables remain separate, but Power BI knows how they are connected.

This distinction is important.

Joins are mainly used for:

Combining columns
Cleaning data
Preparing data
Creating a new query/table

Relationships are mainly used for:

Connecting tables
Filtering data
Creating measures
Building reports
Supporting DAX calculations

In many Power BI models, it is better to keep related tables separate rather than repeatedly joining everything into one large table.

4. Relationships in Power BI

A relationship tells Power BI how two tables are connected.

For example:

Customers[CustomerID]
          |
          |
          ↓
Sales[CustomerID]

Power BI can then understand that a particular sale belongs to a particular customer.

Relationships are created using columns that contain related values.

Usually, one table contains a unique value while another table contains repeated values.

For example:

Customers

CustomerID	CustomerName
C001	John
C002	Mary
C003	Peter

Each CustomerID appears once.

Sales

SaleID	CustomerID	Amount
1	C001	1000
2	C001	500
3	C002	800

CustomerID can appear many times in Sales.

This creates a one-to-many relationship.

5. Cardinality

Cardinality describes how many records in one table can be related to records in another table.

Power BI supports several types.

One-to-Many (1:*)

This is the most common relationship in a well-designed Power BI model.

For example:

Customers  1 ───────── *  Sales

One customer can have many sales.

The Customers table is the one side, while Sales is the many side.

Many-to-One (*:1)

This is essentially the same relationship viewed from the opposite direction.

Sales  * ───────── 1  Customers

Many sales belong to one customer.

One-to-One (1:1)

Each record in one table corresponds to exactly one record in another table.

Employee  1 ───────── 1  EmployeeDetails

This relationship is less common and should be used carefully.

Many-to-Many (:)

Multiple records in one table can correspond to multiple records in another.

For example:

Students  * ─────── *  Courses

A student can take many courses, while a course can have many students.

Many-to-many relationships can be useful, but they can also create ambiguity and unexpected filtering behaviour. A bridge table is often a better solution.

6. Primary Keys and Foreign Keys

Relationships usually depend on keys.

A primary key uniquely identifies each record in a table.

For example:

CustomerID
C001
C002
C003

Each CustomerID is unique.

A foreign key is a column that refers to a key in another table.

For example, the Sales table may contain:

CustomerID
C001
C001
C002
C003

Here, CustomerID is a foreign key because it refers to CustomerID in the Customers table.

A typical model therefore looks like:

Customers
----------
CustomerID ← Primary Key
CustomerName
Country

       1
       |
       |
       *
Sales
----------
SaleID ← Primary Key
CustomerID ← Foreign Key
SalesAmount

7. Schemas Explained

A schema is the overall structure or organisation of tables and their relationships.

When working with Power BI, two important schema designs are the Star Schema and Snowflake Schema.

8. Star Schema

The Star Schema is generally the preferred modelling approach for Power BI.

It gets its name because the model looks like a star.

At the centre is a fact table, surrounded by dimension tables.

For example:

                 Customers
                     |
                     |
Products ─────── Sales ─────── Date
                     |
                     |
                 Employees

The central Sales table is the fact table.

The surrounding tables are dimension tables.

Fact Tables

A fact table contains business events or transactions.

For example:

Sales
----------------
SaleID
DateID
CustomerID
ProductID
Quantity
SalesAmount

A fact table normally contains numerical values that can be aggregated, such as:

Sales
Revenue
Quantity
Cost
Profit
Discount

These values are often called measures or metrics.

Dimension Tables

Dimension tables provide descriptive information about the facts.

For example:

Customer Dimension

CustomerID
CustomerName
Gender
Country
Age

Product Dimension

ProductID
ProductName
Category
Brand

Date Dimension

DateID
Date
Year
Month
Quarter
Day

Dimensions allow users to analyse facts from different perspectives.

For example:

Total sales by country

uses the Customer dimension.

Total sales by product category

uses the Product dimension.

Total sales by month

uses the Date dimension.

9. Snowflake Schema

A Snowflake Schema is similar to a Star Schema, but dimension tables are further divided into additional tables.

For example:

                 Country
                    |
                    |
Customers ─────── Sales ─────── Products
                                |
                                |
                             Category

Instead of keeping all product information in one Products table, some information may be separated into another table.

For example:

Products
---------
ProductID
ProductName
CategoryID

and:

Categories
----------
CategoryID
CategoryName

This reduces repeated information but creates additional relationships.

10. Star Schema vs Snowflake Schema

Feature	Star Schema	Snowflake Schema
Structure	Simple	More complex
Dimensions	Usually denormalised	More normalised
Number of tables	Fewer	More
Relationships	Easier	More complex
Power BI usability	Excellent	Good
Report development	Easier	More difficult
Performance	Often very good	Can require more relationship navigation

For most Power BI reporting projects, a Star Schema is usually the preferred starting point because it is simple, intuitive, and works well with Power BI's analytical engine.

11. Filter Direction

Relationships also have a concept called cross-filter direction.

The two common options are:

Single
Both

With a single-direction relationship:

Customers  →  Sales

Filters generally flow from the Customers table toward Sales.

For example, selecting Kenya in the Customers table can filter the Sales table to show sales from Kenya.

With Both, filtering can flow in both directions.

Although bidirectional filtering can be useful in some situations, it should not be used everywhere because it can introduce ambiguous filter paths and make models harder to understand.

A good model generally uses single-direction filtering where possible.

12. Why Data Modelling Matters

Good data modelling provides several benefits.

1. Accurate Results

Relationships determine how filters move through your model. Incorrect relationships can produce incorrect totals and calculations.

2. Better Performance

A properly designed model can reduce unnecessary data duplication and improve report performance.

3. Easier DAX

Measures become easier to write when tables have clear relationships.

For example:

Total Sales = SUM(Sales[SalesAmount])

You can then analyse Total Sales by customer, product, country, or date without having to manually combine the tables inside every calculation.

4. Easier Report Development

A clean model makes it easier to drag fields into visuals and understand where each field comes from.

5. Easier Maintenance

If the business changes, a well-structured model is easier to update.

13. Common Data Modelling Mistakes

Several mistakes frequently occur when building Power BI models.

Creating One Huge Table

Beginners often join every table together into one massive table.

Although this may seem easier, it can result in:

Duplicate information
Larger datasets
More complicated calculations
Poorer performance
Difficult maintenance

A Star Schema is often a better approach.

Creating Incorrect Relationships

For example, connecting two tables using columns that do not uniquely identify records can produce incorrect results.

Always understand the meaning of the columns before creating a relationship.

Using Many-to-Many Unnecessarily

Many-to-many relationships can create unexpected results.

Where appropriate, consider introducing a bridge table.

Duplicate Values on the "One" Side

The one side of a one-to-many relationship should normally contain unique values.

For example, if CustomerID appears multiple times in the Customers table, Power BI cannot treat it as a proper one-side key.

Unnecessary Bidirectional Relationships

Setting every relationship to Both can make a model complicated and introduce ambiguous filtering.

Use it only when there is a clear reason.

14. A Practical Power BI Model

Imagine you are analysing a company's sales data.

You might have:

                 DimCustomer
                      |
                      |
DimProduct ───── FactSales ───── DimDate
                      |
                      |
                 DimEmployee

The FactSales table contains:

SaleID
CustomerID
ProductID
DateID
EmployeeID
Quantity
SalesAmount
Cost

The dimension tables contain descriptive information.

You could then create measures such as:

Total Sales = SUM(FactSales[SalesAmount])

Total Cost = SUM(FactSales[Cost])

Profit = [Total Sales] - [Total Cost]

Because the relationships are correctly established, you can place Country, Product Category, or Year in a visual and Power BI can automatically filter the sales data appropriately.

Conclusion

Data modelling is the foundation of an effective Power BI report. Understanding the difference between joins, relationships, and schemas is essential for building reliable data models.

Joins are primarily used to combine tables during data preparation, especially in Power Query. Relationships connect tables within the Power BI data model and allow filters and calculations to work across those tables. Schemas describe how the overall model is organised, with the Star Schema being one of the most useful designs for Power BI.

A strong Power BI model usually has a clear fact table, well-defined dimension tables, appropriate one-to-many relationships, unique keys, and sensible filter directions.

The goal is not simply to connect every table together. The goal is to create a model that represents the business logically, makes calculations reliable, and allows users to analyse their data efficiently.

Once you understand joins → relationships → cardinality → fact and dimension tables → Star Schema, you have the foundation needed to build much more professional Power BI reports.

# Statistics: Parametric and Non-Parametric Tests in Data Science

Samuel Mwai — Mon, 17 Aug 2026 09:55:07 +0000

Statistics: Parametric and Non-Parametric Tests in Data Science

Introduction

Statistics is one of the foundations of data science. Although data scientists work with programming languages such as Python and R, statistical methods provide the mathematical tools needed to understand patterns, compare groups, test assumptions, and determine whether observed differences are likely to be meaningful.

When analyzing data, one common goal is to determine whether there is enough evidence to support a particular claim. For example:

Does a new marketing campaign increase sales?
Do two groups have significantly different average incomes?
Is there a relationship between two variables?
Does a treatment produce a different outcome?
Are several groups significantly different from one another?

Statistical tests help answer these questions. Two major categories of statistical tests are parametric tests and non-parametric tests.

The main difference is that parametric tests generally make stronger assumptions about the underlying population distribution, while non-parametric tests generally require fewer distributional assumptions. IBM describes non-parametric tests as methods that make minimal assumptions about the underlying distribution of the data. (IBM)

Understanding when to use each type of test is an important skill for anyone working in data science.

1. What Are Statistical Tests?

A statistical test is a mathematical procedure used to evaluate evidence in a sample and make an inference about a population.

Suppose a company claims that its average customer satisfaction score is 80. You collect a sample of customers and calculate an average score of 84.

The difference between 80 and 84 might be meaningful—or it might simply be the result of random sampling variation.

A statistical test helps determine whether the evidence is strong enough to reject a null hypothesis.

Null hypothesis

The null hypothesis, commonly represented as H₀, usually states that there is no meaningful difference, relationship, or effect.

For example:

H₀: There is no difference in the average satisfaction scores between the two groups.

Alternative hypothesis

The alternative hypothesis, represented as H₁ or Hₐ, represents the possibility that a difference or relationship exists.

H₁: There is a difference in the average satisfaction scores between the two groups.

Statistical testing then produces a test statistic and usually a p-value, which can be used as evidence when evaluating the null hypothesis.

2. Parametric Tests

Parametric tests are statistical tests that make assumptions about the parameters or distribution of the population being studied.

A common assumption is that the data—or the relevant model residuals—follow a particular distribution, often a normal distribution.

Parametric tests are often powerful when their assumptions are reasonably satisfied.

Common parametric tests include:

One-sample t-test
Independent-samples t-test
Paired-samples t-test
ANOVA
Pearson correlation

The exact assumptions depend on the particular test. Therefore, it is not enough to simply label a dataset "parametric" or "non-parametric"; the suitability of a test depends on the data, study design, and assumptions being evaluated.

3. The t-Test

The t-test is one of the most commonly used parametric tests.

It is generally used when comparing means.

There are several common types.

One-Sample t-Test

A one-sample t-test compares the mean of a sample with a specified value.

For example, suppose a company believes its employees work an average of 40 hours per week.

You could test whether the average working time in your sample differs from 40 hours.

H₀: μ = 40
H₁: μ ≠ 40

Independent-Samples t-Test

An independent-samples t-test compares two independent groups.

For example:

Is the average salary of Group A different from the average salary of Group B?

Paired-Samples t-Test

A paired t-test is used when observations are naturally paired, such as measurements taken from the same subjects before and after an intervention.

For example:

Did the average test score change after students completed a training program?

The important point is that the observations are paired rather than treated as two unrelated groups.

4. ANOVA

ANOVA, or Analysis of Variance, is used when comparing the means of multiple groups.

For example, imagine that a company wants to compare the average sales generated by three different marketing strategies:

Strategy A → Average sales
Strategy B → Average sales
Strategy C → Average sales

Instead of conducting many separate t-tests, a one-way ANOVA can be used to test whether there is evidence that the group means are not all equal.

The basic idea is to compare:

Between-group variation

with

Within-group variation

genui{"learning_viz":{"type_id":"ANOVA_DECOMPOSITION"}}

A large ratio of between-group variation to within-group variation provides stronger evidence against the hypothesis that all group means are equal.

However, ANOVA tells you that a difference exists somewhere among the groups; it does not automatically identify which specific groups differ. Follow-up comparisons may be required.

5. Non-Parametric Tests

Non-parametric tests are statistical procedures that generally make fewer assumptions about the underlying distribution of the data.

This can be useful when data are:

Highly skewed
Ordinal
Affected by extreme outliers
Not reasonably modeled by the assumptions of a parametric test
Based on small samples where distributional assumptions are questionable

IBM notes that non-parametric tests make minimal assumptions about the underlying distribution and can be organized according to whether they involve one sample, related samples, or independent samples. (IBM)

Common non-parametric tests include:

Mann–Whitney U test
Wilcoxon signed-rank test
Kruskal–Wallis H test
Spearman rank correlation
Chi-square tests

6. Mann–Whitney U Test

The Mann–Whitney U test is commonly used to compare two independent groups when a t-test may not be appropriate.

For example, suppose we want to compare customer spending between two groups:

Group A → 100, 120, 150, 170, 200
Group B → 80, 90, 110, 130, 140

If the data are strongly skewed or otherwise do not satisfy the assumptions needed for a conventional t-test, a Mann–Whitney test may be considered.

Rather than relying directly on the raw values in the same way as a t-test, the Mann–Whitney procedure works with the ranks of observations.

7. Wilcoxon Signed-Rank Test

The Wilcoxon signed-rank test is commonly used as a non-parametric alternative for paired data.

For example, suppose you measure employees' productivity before and after training:

Employee	Before	After
A	60	65
B	72	76
C	55	61
D	80	81

Instead of using a paired t-test, you could consider a Wilcoxon signed-rank test when the assumptions for the paired t-test are not appropriate.

IBM lists the Wilcoxon signed-rank test among its one-sample non-parametric procedures for continuous and ordinal fields. (IBM)

8. Kruskal–Wallis Test

The Kruskal–Wallis H test is commonly used to compare multiple independent groups when a one-way ANOVA is not appropriate.

For example, imagine comparing customer spending across three cities:

Nairobi
Mombasa
Kisumu

A Kruskal–Wallis test can be used to investigate whether the distributions differ across the groups.

IBM describes the Kruskal–Wallis H test as a non-parametric analogue of one-way ANOVA for several independent samples. (IBM)

If the test indicates a significant overall difference, additional post-hoc comparisons may be needed to determine which groups differ.

9. Pearson vs Spearman Correlation

Correlation measures the relationship between variables.

Pearson Correlation

Pearson correlation is commonly used to measure the strength and direction of a linear relationship between numerical variables.

For example:

Is there a linear relationship between advertising expenditure and sales?

The correlation coefficient ranges from:

-1  ←─── 0 ───→  +1

A value close to +1 indicates a strong positive linear relationship, while a value close to -1 indicates a strong negative linear relationship.

Spearman Rank Correlation

Spearman correlation is based on ranks and can be useful when the relationship is monotonic but not necessarily linear, or when the assumptions behind Pearson correlation are not appropriate.

It is particularly useful for ordinal data.

10. Parametric vs Non-Parametric Tests

Situation	Parametric Test	Non-Parametric Alternative
One sample vs a value	One-sample t-test	Wilcoxon signed-rank
Two independent groups	Independent t-test	Mann–Whitney U
Two paired groups	Paired t-test	Wilcoxon signed-rank
Three or more independent groups	One-way ANOVA	Kruskal–Wallis
Linear correlation	Pearson correlation	Spearman correlation

This table should be treated as a general guide, not an automatic substitution rule. The appropriate test depends on the measurement scale, study design, distribution, independence of observations, and the precise research question.

11. How to Choose the Right Test

Before selecting a statistical test, a data scientist should ask several questions.

Step 1: What type of data do I have?

Determine whether your variables are:

Numerical
Ordinal
Nominal
Binary
Continuous or discrete

Step 2: How many groups am I comparing?

Are you comparing:

One group?
Two groups?
Three or more groups?

Step 3: Are the observations independent?

Two groups may be independent, such as customers from two unrelated regions.

Alternatively, the observations may be paired, such as measurements taken from the same individuals before and after treatment.

Step 4: What does the distribution look like?

Exploratory data analysis can help you understand distributions, identify outliers, and determine whether the assumptions of a statistical method are reasonable. IBM describes EDA as a process for investigating datasets, summarizing their characteristics, discovering patterns, identifying anomalies, and checking assumptions before more formal analysis. (IBM)

Useful tools include:

Histograms
Box plots
Q-Q plots
Descriptive statistics
Normality assessments

Step 5: Check assumptions

Don't automatically use a parametric test simply because your variable is numerical.

Check assumptions relevant to the specific test, which may include:

Independence
Distributional assumptions
Equal or comparable variances
Appropriate measurement level

12. Statistical Testing in Python

Python provides several libraries for statistical analysis, including SciPy, which contains many statistical tests.

For example, an independent t-test can be performed with:

from scipy.stats import ttest_ind

statistic, p_value = ttest_ind(group_a, group_b)

print("Statistic:", statistic)
print("P-value:", p_value)

A Mann–Whitney U test can be performed with:

from scipy.stats import mannwhitneyu

statistic, p_value = mannwhitneyu(group_a, group_b)

print("Statistic:", statistic)
print("P-value:", p_value)

The important lesson is that running the code is only one part of statistical analysis. A data scientist must also understand why a particular test was selected and how its assumptions affect interpretation.

13. Understanding the P-Value

The p-value is one of the most commonly misunderstood concepts in statistics.

In hypothesis testing, the p-value represents the probability, under the null hypothesis and the test's assumptions, of obtaining a result at least as extreme as the observed result.

A common significance level is:

α = 0.05

If:

p-value < 0.05

researchers often describe the result as statistically significant at the 5% level and reject the null hypothesis.

If:

p-value ≥ 0.05

we generally fail to reject the null hypothesis.

Importantly, failing to reject the null hypothesis does not prove that the null hypothesis is true.

Statistical significance also does not automatically mean that an effect is practically important. A data scientist should consider the effect size, confidence interval, sample size, and real-world context alongside the p-value.

14. Why These Tests Matter in Data Science

Statistics plays an important role throughout the data science workflow.

A typical process might look like:

Raw Data
   ↓
Data Cleaning
   ↓
Exploratory Data Analysis
   ↓
Statistical Testing
   ↓
Feature Analysis
   ↓
Machine Learning
   ↓
Model Evaluation
   ↓
Business Decision

Statistical testing can help data scientists determine whether patterns observed during analysis are likely to represent meaningful relationships rather than random variation.

For example, before building a machine-learning model, you might investigate whether certain variables differ between groups or whether particular variables are associated with an outcome.

However, statistical significance should not be confused with predictive usefulness. A variable can have a statistically significant relationship with an outcome while contributing little practical value to a predictive model.

15. A Practical Example

Imagine that a company wants to determine whether a new training program improved employee productivity.

The company measures productivity before and after training for the same employees.

Because the measurements are paired, the analyst might consider:

Parametric approach

Paired t-test

if the assumptions for that test are reasonably satisfied.

Non-parametric approach

Wilcoxon signed-rank test

if a non-parametric approach is more appropriate.

The hypotheses could be:

H₀: The training did not change productivity.

H₁: The training changed productivity.

After performing the test, the analyst examines the p-value along with the size and direction of the observed change.

The final conclusion should consider both statistical evidence and the practical importance of the result.

Conclusion

Parametric and non-parametric tests are important tools in statistical analysis and data science.

Parametric tests can be powerful when their assumptions are reasonably satisfied. Common examples include the t-tests, ANOVA, and Pearson correlation.

Non-parametric tests provide alternatives when stronger distributional assumptions are inappropriate or when the data are ordinal, highly skewed, or otherwise unsuitable for a particular parametric procedure. Examples include the Mann–Whitney U test, Wilcoxon signed-rank test, Kruskal–Wallis test, and Spearman correlation. (IBM)

The goal is not to memorize a list of tests. A good data scientist should understand the research question, type of data, study design, assumptions, and interpretation before selecting a statistical method.

Ultimately, statistical testing helps transform raw observations into evidence that can support better scientific, business, and data-driven decisions.

# Introduction to SQL: DDL, DML, and Data Querying

Samuel Mwai — Mon, 17 Aug 2026 09:48:56 +0000

In today's data-driven world, databases are used to store enormous amounts of information. Businesses use them to manage customers, transactions, employees, products, financial records, and many other types of data. To interact with these databases, one of the most important languages to learn is SQL, or Structured Query Language.

SQL allows users to create database structures, insert and modify information, and retrieve specific data for analysis. PostgreSQL, MySQL, SQL Server, and Oracle are examples of database systems that support SQL.

For anyone learning data science, data analytics, or database management, understanding the basic SQL commands is essential. Three important areas to understand are Data Definition Language (DDL), Data Manipulation Language (DML), and data querying.

1. Understanding DDL

[IMAGE: SQL DDL CREATE ALTER DROP TRUNCATE infographic]

DDL stands for Data Definition Language.

DDL is concerned with the structure of the database rather than the individual records stored inside it. It is used to create database objects and modify their structure.

Common DDL commands include:

CREATE
ALTER
DROP
TRUNCATE

CREATE

The CREATE command can be used to create a table.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    city VARCHAR(50)
);

This creates a customers table containing four columns.

ALTER

The ALTER command modifies an existing table.

For example, we can add an email column:

ALTER TABLE customers
ADD COLUMN email VARCHAR(100);

DROP

DROP removes a database object.

DROP TABLE customers;

This removes the entire customers table, including its structure and data.

TRUNCATE

TRUNCATE removes the rows from a table while keeping the table itself.

TRUNCATE TABLE customers;

Therefore, DDL can be thought of as the part of SQL responsible for building and changing the database structure.

2. Understanding DML

[IMAGE: SQL DML INSERT UPDATE DELETE infographic]

DML stands for Data Manipulation Language.

While DDL deals primarily with the structure of the database, DML deals with the data stored inside tables.

The most common DML commands are:

INSERT
UPDATE
DELETE

INSERT

INSERT is used to add new records.

INSERT INTO customers (name, age, city)
VALUES ('Samuel', 25, 'Nairobi');

This adds a new customer to the table.

Multiple records can also be inserted:

INSERT INTO customers (name, age, city)
VALUES
('John', 30, 'Mombasa'),
('Mary', 27, 'Kisumu'),
('Peter', 35, 'Nakuru');

UPDATE

UPDATE changes existing records.

UPDATE customers
SET city = 'Nairobi'
WHERE customer_id = 2;

The WHERE condition is extremely important because it specifies which record should be changed.

DELETE

DELETE removes records.

DELETE FROM customers
WHERE customer_id = 3;

Again, the WHERE clause prevents you from accidentally deleting every record in the table.

3. Data Querying with SELECT

[IMAGE: SQL SELECT query and database results illustration]

One of the most important things you will do with SQL is query data.

A query is a request for information from a database. The SELECT statement is used to retrieve data from a table.

For example:

SELECT *
FROM customers;

The * means that we want all columns.

You can also select only the columns you need:

SELECT name, city
FROM customers;

This makes the result more focused and easier to analyze.

4. Filtering Data with WHERE

[IMAGE: SQL WHERE clause filtering rows diagram]

When working with large datasets, you often don't want every record. You may only want records that meet a particular condition.

The WHERE clause allows you to filter your results.

For example:

SELECT *
FROM customers
WHERE city = 'Nairobi';

This returns only customers whose city is Nairobi.

You can also use comparison operators:

SELECT *
FROM customers
WHERE age > 30;

SQL supports operators such as:

=
>
<
>=
<=
<>

You can also combine conditions using:

AND
OR
NOT

For example:

SELECT *
FROM customers
WHERE city = 'Nairobi'
AND age > 30;

5. Sorting Data with ORDER BY

[IMAGE: SQL ORDER BY sorting data illustration]

Sometimes you want your results arranged in a particular order.

The ORDER BY clause allows you to sort the results.

For example:

SELECT *
FROM customers
ORDER BY age ASC;

This sorts customers from the youngest to the oldest.

To sort from highest to lowest:

SELECT *
FROM customers
ORDER BY age DESC;

ORDER BY is especially useful when analyzing rankings, sales, salaries, scores, or other numerical data.

6. GROUP BY and Aggregate Functions

[IMAGE: SQL GROUP BY COUNT SUM AVG visualization]

SQL is not only useful for retrieving individual records. It can also be used to summarize data.

Common aggregate functions include:

COUNT() — counts records
SUM() — calculates a total
AVG() — calculates an average
MIN() — finds the smallest value
MAX() — finds the largest value

For example:

SELECT COUNT(*)
FROM customers;

This returns the total number of customers.

We can also calculate the average age:

SELECT AVG(age)
FROM customers;

GROUP BY

GROUP BY allows us to organize records into groups.

For example, we can count customers by city:

SELECT city, COUNT(*)
FROM customers
GROUP BY city;

The result could look like:

City	Customer Count
Nairobi	25
Mombasa	12
Kisumu	8
Nakuru	15

This type of query is particularly useful in data analytics, because it allows large datasets to be summarized into meaningful information.

7. Putting SQL Together

[IMAGE: SQL query flow SELECT FROM WHERE ORDER BY LIMIT]

The real power of SQL comes from combining these commands.

Suppose we want to find the three oldest customers from Nairobi.

We could write:

SELECT name, age
FROM customers
WHERE city = 'Nairobi'
ORDER BY age DESC
LIMIT 3;

Here, several SQL concepts work together:

SELECT   → Choose the columns
FROM     → Choose the table
WHERE    → Filter the records
ORDER BY → Sort the results
LIMIT    → Restrict the number of results

This is the foundation of more advanced SQL queries, including joins, subqueries, common table expressions, and window functions.

8. DDL vs DML vs Data Querying

[IMAGE: SQL DDL DML DQL comparison infographic]

Category	Purpose	Common Commands
DDL	Defines database structure	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`
DML	Manipulates stored data	`INSERT`, `UPDATE`, `DELETE`
Querying	Retrieves information	`SELECT`, `WHERE`, `GROUP BY`, `ORDER BY`

The easiest way to remember the difference is:

DDL → Structure

Build and modify the database structure.

DML → Data

Add, change, and remove records.

Querying → Information

Retrieve and analyze information stored in the database.

Conclusion

SQL is one of the fundamental skills for anyone working with databases and data. DDL provides the commands needed to create and modify database structures, while DML allows users to insert, update, and delete information. Data querying, particularly through SELECT, allows users to retrieve and analyze the information stored in those structures.

Once these fundamentals are understood, you can progress to more powerful SQL concepts such as JOINs, subqueries, CTEs, window functions, views, indexes, and advanced aggregation.

For a data analyst or data scientist, SQL is more than simply a database language—it is a practical tool for turning large amounts of raw data into information that can be analyzed and used for decision-making.

Summary

SQL provides a structured way to work with databases:

DDL manages database structure.
DML manages the data stored in tables.
SELECT retrieves information.
WHERE filters data.
ORDER BY sorts data.
GROUP BY organizes data into groups.
Aggregate functions summarize data.

Together, these concepts form the foundation for working with relational databases and provide an important starting point for anyone pursuing a career in data analytics, data science, or database management.

Understanding Subqueries and CTEs in SQL: A Complete Guide

Samuel Mwai — Mon, 17 Aug 2026 09:37:27 +0000

Understanding Subqueries and CTEs in SQL: A Complete Guide

beginners

tutorial

productivity
Working with relational databases often requires breaking down complex problems into manageable parts. Two powerful tools that help achieve this in SQL are subqueries and Common Table Expressions (CTEs). While they may seem similar at first, they serve different purposes and are best used in different scenarios.

This article explores what subqueries and CTEs are, their types, use cases, and how they compare in terms of performance and readability.

What is a Subquery?

A subquery is a query nested inside another SQL query. It is used to perform operations that depend on the result of another query.

Basic Example

SELECT

**
The inner query calculates the average salary.
The outer query retrieves employees earning above that average.

👉 In simple terms, a subquery provides intermediate results to the main query.

Types of Subqueries

Subqueries can be categorized based on how they are used and how they interact with the outer query.

Single-row Subquery
Returns only one row.

SELECT name
FROM employees
WHERE department_id = (
SELECT id FROM departments WHERE name = 'Sales'
);
Multi-row Subquery
Returns multiple rows and is used with operators like IN, ANY, or ALL.

SELECT name
FROM employees
WHERE department_id IN (
SELECT id FROM departments WHERE location = 'Nairobi'
);
Correlated Subquery
Depends on the outer query and is executed once for each row.

SELECT name
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);

👉 This is more dynamic but can be slower due to repeated execution.

Nested Subquery
A subquery inside another subquery.

SELECT name
FROM employees
WHERE department_id = (
SELECT id
FROM departments
WHERE location = (
SELECT location
FROM offices
WHERE city = 'Nairobi'
)
);
When Should Subqueries Be Used?

Subqueries are ideal when:

You need a value derived from another query
The logic is simple and contained
You want to filter results dynamically
You’re working with aggregates (AVG, MAX, MIN, etc.)

However, they can become inefficient or hard to read when deeply nested or correlated.

What are CTEs (Common Table Expressions)?

A Common Table Expression (CTE) is a temporary result set defined at the beginning of a query using the WITH keyword. It improves readability and organization, especially in complex queries.

Basic Example
WITH avg_salary AS (
SELECT AVG(salary) AS avg_sal
FROM employees
)
SELECT name
FROM employees, avg_salary
WHERE salary > avg_sal;
👉 Think of a CTE as a temporary named query you can reference within your main query.

Types and Use Cases of CTEs

Non-Recursive CTE
The most common type, used for simplifying complex queries.

WITH department_totals AS (
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
)
SELECT *
FROM department_totals
WHERE total_salary > 50000;

Use case:

Breaking down large queries into readable parts

Recursive CTE
Used to handle hierarchical or tree-structured data.

WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id
FROM employees
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN employee_hierarchy eh
ON e.manager_id = eh.id

)
SELECT * FROM employee_hierarchy;

Use case:

Organizational charts
Category hierarchies
Graph traversal
When Should CTEs Be Used?

CTEs are best when:

Queries are complex and need structure
You want to reuse a result multiple times
You need recursive logic
You want to improve readability and maintainability
Subqueries vs CTEs: A Clear Comparison

Readability Subqueries: Can become difficult to read when nested CTEs: Much cleaner and easier to understand
👉 Winner: CTEs

Performance Subqueries: Correlated subqueries can be slow Often re-executed multiple times CTEs: Sometimes optimized better by the database But in some systems, they may not be cached and can behave like inline views
👉 Winner: Depends on the database engine

For repeated logic → CTEs often better
For simple tasks → subqueries are fine

Reusability Subqueries: Cannot be reused easily CTEs: Can be referenced multiple times in the same query
👉 Winner: CTEs

Complexity Handling Subqueries: Good for simple conditions CTEs: Ideal for complex, multi-step logic
👉 Winner: CTEs

Recursion Subqueries: Cannot handle recursion CTEs: Support recursive queries
👉 Winner: CTEs

When to Use Each
Use Subqueries when:
The query is simple and short
You only need the result once
You’re filtering using aggregates
Use CTEs when:
The query is complex or layered
You need better readability
You want to reuse logic
You’re working with hierarchical data
Conclusion

Both subqueries and CTEs are essential tools in SQL, and understanding when to use each can significantly improve your queries.

Subqueries are concise and useful for straightforward operations
CTEs provide structure, clarity, and power for more advanced scenarios

In practice, experienced developers often prefer CTEs for maintainability, especially in large projects—but subqueries still have their place for quick, simple tasks.

Top comments (0)

Subscribe
pic
Add to the discussion

Code of Conduct • Report abuse
profile
The DEV Team
Promoted

Google article image

Architect A Personalized Multi-Agent System with Long-Term Memory
In support of our mission to accelerate the developer journey on Google Cloud, we built Dev Signal — a multi-agent system designed to transform raw community signals into reliable technical guidance by automating the path from discovery to expert creation.

Distributions and Their Impact on Data Science

Samuel Mwai — Mon, 22 Jun 2026 06:51:33 +0000

Introduction

Data science is built on the ability to extract meaningful insights from data. Before a data scientist can create predictive models or make business recommendations, they must first understand how the data is distributed. A distribution describes how values are spread across a dataset, showing the frequency, pattern, and behavior of data points.

Understanding distributions allows data scientists to identify trends, detect anomalies, select appropriate machine learning algorithms, and make reliable predictions.

What is a Distribution?

A distribution is the way in which data values are arranged and how often they occur. It answers questions such as:

Are most values clustered around a central point?
Is the data spread evenly or concentrated?
Are there extreme values (outliers)?
Does the data follow a predictable pattern?

For example, in a dataset containing customer spending, a distribution can reveal whether most customers spend similar amounts or whether a small number of customers contribute to a large portion of revenue.

Importance of Distributions in Data Science

1. Understanding Data Behavior

The first step in any data science project is Exploratory Data Analysis (EDA). By examining distributions through histograms, box plots, and density plots, data scientists can understand:

The center of the data (mean, median, mode)
The spread of the data (variance and standard deviation)
The presence of outliers
The shape of the data

This understanding helps determine the best approach for further analysis.

2. Detecting Outliers and Data Quality Issues

Distributions help identify unusual observations that may represent:

Data entry errors
Fraudulent transactions
Rare events
Significant business opportunities

For example, a sudden spike in a customer's purchasing behavior may indicate either a fraudulent transaction or a valuable customer who should receive special attention.

3. Choosing the Right Machine Learning Model

Many machine learning algorithms make assumptions about the underlying distribution of data.

For example:

Linear Regression often assumes that residual errors are normally distributed.
Naive Bayes uses probability distributions to calculate the likelihood of different classes.
Clustering algorithms can be influenced by how data points are spread.

Understanding the distribution of your data helps improve model accuracy and reliability.

4. Data Transformation and Feature Engineering

Real-world data is often messy and skewed. Data scientists may transform distributions using methods such as:

Log transformation
Square root transformation
Standardization
Normalization

These transformations can reduce skewness and make data more suitable for machine learning algorithms.

Common Types of Distributions in Data Science

1. Normal Distribution

The normal distribution, also known as the bell curve, is one of the most important distributions in statistics.

Characteristics:

Symmetrical around the mean
Mean, median, and mode are equal
Most observations cluster near the center

Examples include:

Human heights
Measurement errors
Standardized test scores

Many statistical techniques and machine learning methods rely on the assumption of normality.

2. Uniform Distribution

In a uniform distribution, every outcome has an equal probability of occurring.

Examples:

Rolling a fair die
Random number generation

It is commonly used in simulations and random sampling.

3. Binomial Distribution

The binomial distribution models the number of successes in a fixed number of independent trials.

Examples:

Number of customers who click an advertisement
Number of successful sales calls

It is widely used in marketing analytics and A/B testing.

4. Poisson Distribution

The Poisson distribution describes the number of events occurring within a fixed period of time or space.

Examples:

Number of website visitors per minute
Number of customer support requests per day

It is valuable for forecasting and resource planning.

5. Exponential Distribution

The exponential distribution models the time between events.

Examples:

Time until a customer makes a purchase
Time until a machine fails

It is commonly used in reliability analysis and survival studies.

The Role of Distributions in Real-World Data Science

Distributions influence almost every stage of a data science workflow:

Data Collection

They help determine whether collected data accurately represents a population.

Data Cleaning

They reveal missing values, unusual patterns, and outliers.

Exploratory Data Analysis

They provide a deeper understanding of relationships and trends.

Machine Learning

They help in feature selection, transformation, and model evaluation.

Decision-Making

They allow businesses to estimate risk, predict outcomes, and plan for the future.

Conclusion

Distributions are a fundamental concept in data science because they describe the underlying behavior of data. A skilled data scientist does not simply look at numbers; they analyze how those numbers are distributed to uncover patterns, detect problems, and build accurate predictive models.

From understanding customer behavior and forecasting sales to detecting fraud and developing artificial intelligence systems, distributions play a critical role in transforming raw data into valuable insights.

Pandas for Data Cleaning in Data Science Introduction

Samuel Mwai — Mon, 15 Jun 2026 05:05:37 +0000

In the field of data science and analytics, raw data is rarely perfect. Real-world datasets often contain missing values, duplicate records, incorrect formats, inconsistent text, and outliers that can affect the accuracy of analysis and machine learning models. Data cleaning is the process of detecting, correcting, and preparing raw data so that it becomes reliable and ready for analysis.

One of the most powerful tools for data cleaning in Python is Pandas. Pandas is an open-source Python library that provides easy-to-use data structures and functions for manipulating and analyzing structured data. With its DataFrame and Series objects, Pandas allows data professionals to efficiently clean datasets of any size.

Loading Data into Pandas

Before cleaning data, the first step is importing it into a Pandas DataFrame.

import pandas as pd

df = pd.read_csv("sales_data.csv")

To inspect the data:

df.head() # Displays first 5 rows
df.tail() # Displays last 5 rows
df.info() # Data types and missing values
df.describe() # Statistical summary
df.shape # Number of rows and columns

Understanding the structure of the dataset helps identify potential data quality issues.

Handling Missing Values

Missing data is one of the most common problems in datasets.

Detecting Missing Values
df.isnull()

Count missing values in each column:

df.isnull().sum()
Removing Missing Values

Remove rows with missing data:

df.dropna()

Remove columns containing missing values:

df.dropna(axis=1)
Filling Missing Values

Replace missing values with a specific value:

df.fillna(0)

Fill numerical data using the mean:

df["Age"] = df["Age"].fillna(df["Age"].mean())

Fill categorical data using the mode:

df["Country"] = df["Country"].fillna(df["Country"].mode()[0])

Removing Duplicate Data

Duplicate records can lead to inaccurate analysis.

Identifying Duplicates
df.duplicated()

Count duplicate rows:

df.duplicated().sum()
Removing Duplicates
df.drop_duplicates()

Remove duplicates based on specific columns:

df.drop_duplicates(subset=["Email"])

Correcting Data Types

Incorrect data types can cause errors during analysis.

Check data types:

df.dtypes
Converting Data Types

Convert a column to an integer:

df["Quantity"] = df["Quantity"].astype(int)

Convert a column to a datetime format:

df["Date"] = pd.to_datetime(df["Date"])

Convert text to a numeric type:

df["Price"] = pd.to_numeric(df["Price"])

Cleaning Text Data

Text data often contains unnecessary spaces, inconsistent capitalization, or formatting problems.

Removing Extra Spaces
df["Name"] = df["Name"].str.strip()
Changing Letter Case

Convert to lowercase:

df["City"] = df["City"].str.lower()

Convert to uppercase:

df["Country"] = df["Country"].str.upper()

Convert to title case:

df["Name"] = df["Name"].str.title()
Replacing Incorrect Values
df["Gender"] = df["Gender"].replace({
"M": "Male",
"F": "Female"
})

Renaming Columns

Column names may be unclear or inconsistent.

Rename a single column:

df.rename(columns={"Cust_Name": "Customer_Name"})

Rename all columns:

df.columns = [
"id",
"name",
"age",
"city"
]

Standardize column names:

df.columns = (
df.columns
.str.strip()
.str.lower()
.str.replace(" ", "_")
)

Filtering Incorrect Data

Sometimes datasets contain impossible or invalid values.

Example: Remove customers with negative ages.

df = df[df["Age"] >= 0]

Remove unrealistic values:

df = df[df["Salary"] <= 500000]

Detecting and Handling Outliers

Outliers are unusual values that significantly differ from the rest of the data.

Using the Interquartile Range (IQR) method:

Q1 = df["Salary"].quantile(0.25)
Q3 = df["Salary"].quantile(0.75)

IQR = Q3 - Q1

lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

df = df[
(df["Salary"] >= lower) &
(df["Salary"] <= upper)
]

Working with Dates

Dates often require cleaning and formatting.

Convert strings to dates:

df["Order_Date"] = pd.to_datetime(df["Order_Date"])

Extract useful information:

df["Year"] = df["Order_Date"].dt.year
df["Month"] = df["Order_Date"].dt.month
df["Day"] = df["Order_Date"].dt.day

Handling Inconsistent Categories

Categories may have different spellings representing the same value.

Example:

Before cleaning:

USA
U.S.A
United States
us

Standardize them:

df["Country"] = df["Country"].replace({
"U.S.A": "USA",
"United States": "USA",
"us": "USA"
})

Finding Unique Values

Checking unique values helps identify inconsistencies.

View unique entries:

df["Country"].unique()

Count each category:

df["Country"].value_counts()

Saving the Cleaned Dataset

After cleaning, save the dataset for future analysis.

Save as CSV:

df.to_csv("cleaned_data.csv", index=False)

Save as Excel:

df.to_excel("cleaned_data.xlsx", index=False)
Best Practices for Data Cleaning with Pandas
Always create a copy of the original dataset before cleaning.
Explore the dataset using head(), info(), and describe().
Handle missing values based on the context of the problem.
Maintain consistent naming conventions.
Validate data after every cleaning step.
Document all transformations to ensure reproducibility.
Use automated cleaning pipelines for large datasets.
Conclusion

Pandas is an essential library for data cleaning in Python and is widely used by data analysts, data scientists, and machine learning engineers. It provides powerful tools for identifying missing values, removing duplicates, correcting data types, standardizing text, handling outliers, and transforming datasets into a usable format.

Effective data cleaning improves the quality of insights, reduces errors in analysis, and creates a strong foundation for advanced tasks such as data visualization, statistical analysis, and machine learning. Mastering Pandas data cleaning techniques is therefore a fundamental skill for anyone pursuing a career in data science and analytics.

PYTHON IN DATA ANALYSIS

Samuel Mwai — Thu, 07 May 2026 17:44:04 +0000

Introduction to Python for Data Analytics

What is Data Analytics?

Data analytics is the process of collecting, cleaning, analyzing, and interpreting data to uncover meaningful insights and support decision-making. In today’s data-driven world, organizations rely on analytics to improve performance, understand customers, and predict future trends.

Python has emerged as one of the most popular programming languages for data analytics due to its simplicity, flexibility, and powerful ecosystem.

Why Use Python for Data Analytics?

Python is widely used in data analytics for several reasons:

1. Easy to Learn and Read

Python has a clean and simple syntax that resembles plain English. This makes it beginner-friendly and ideal for analysts who may not come from a programming background.

2. Powerful Libraries

Python offers a rich set of libraries specifically designed for data analysis:

Pandas – for data manipulation and analysis
NumPy – for numerical computations
Matplotlib & Seaborn – for data visualization
SciPy – for scientific computing

These libraries allow you to perform complex operations with minimal code.

3. Strong Community Support

Python has a large and active community. This means:

Plenty of tutorials and documentation
Open-source tools and libraries
Quick help when you run into issues

4. Versatility

Python is not limited to data analytics. It can also be used for:

Web development
Automation
Machine learning
Artificial intelligence

This makes it a valuable long-term skill.

Key Steps in Data Analytics Using Python

1. Data Collection

Data can come from various sources such as:

Databases (SQL)
CSV/Excel files
APIs
Web scraping

Python makes it easy to import data using libraries like Pandas.

2. Data Cleaning

Raw data is often messy. Cleaning involves:

Handling missing values
Removing duplicates
Fixing data types
Standardizing formats

Example:

import pandas as pd

df = pd.read_csv("data.csv")
df = df.drop_duplicates()
df['salary'] = pd.to_numeric(df['salary'], errors='coerce')

3. Data Exploration

This step helps you understand your data using:

Summary statistics
Data distributions
Relationships between variables

Example:

df.describe()
df['salary'].mean()

4. Data Visualization

Visualization helps communicate insights effectively.

Example:

import matplotlib.pyplot as plt

df['salary'].hist()
plt.show()

5. Data Analysis and Insights

This is where you answer business questions, such as:

What trends exist in the data?
Which factors influence outcomes?
What patterns can we identify?

Example:

df.groupby('department')['salary'].mean()

Python in Jupyter Notebooks

Jupyter Notebook is a popular environment for data analytics because it allows you to:

Write and execute code
Visualize data inline
Add explanations using text

It’s especially useful for:

Exploratory analysis
Reporting
Learning and experimentation

Real-World Applications

Python is used in many industries for data analytics, including:

Finance – risk analysis, trading strategies
Healthcare – patient data analysis
Marketing – customer segmentation
E-commerce – recommendation systems

Advantages of Python for Data Analysts

Fast development and prototyping
Integration with databases (SQL)
Strong visualization capabilities
Scalable for large datasets

Conclusion

Python is a powerful and accessible tool for data analytics. Its simplicity, combined with a rich ecosystem of libraries, makes it an excellent choice for beginners and professionals alike.

By mastering Python, you can:

Clean and analyze data efficiently
Build meaningful visualizations
Generate actionable insights

Whether you're just starting out or advancing your analytics skills, Python provides the foundation you need to succeed in the world of data.

Next Steps

To continue learning:

Practice with real datasets
Build small analytics projects
Learn advanced tools like machine learning

The best way to learn Python for data analytics is by doing.