DEV Community: Collins Ochola

Understanding SQL: DDL, DML, and Data Transformation

Collins Ochola — Mon, 13 Apr 2026 14:07:41 +0000

Introduction

Structured Query Language (SQL) serves as the backbone for working with relational databases. Throughout my journey of learning SQL, two primary command categories have emerged as most important: DDL and DML. Grasping the distinction between these two is crucial for anyone aiming to work effectively with databases. This piece discusses the nature of DDL and DML, the application of common SQL commands, filtering methods, and the usefulness of conditional logic through CASE WHEN.

DDL (Data Definition Language)

DDL refers to the group of SQL commands concerned with the structure of database objects. You can think of it as the blueprint designer who plans everything before any actual data is placed.

Typical DDL Commands:

Command	Function
CREATE	Constructs databases, schemas, tables, and indexes
ALTER	Changes existing table layouts (add, rename, or remove columns)
DROP	Eliminates entire database objects
TRUNCATE	Empties all rows while preserving the table structure

DML (Data Manipulation Language)

DML deals with the actual information stored within those structures. If DDL acts as the blueprint designer, DML functions as the moving crew that positions, rearranges, or clears out furnishings.

Typical DML Commands:

Command	Function
INSERT	Introduces new rows of information
UPDATE	Changes existing information
DELETE	Erases specific rows
SELECT	Fetches information (sometimes categorized as DQL – Data Query Language)

CREATE – Establishing the Base

The CREATE command sets up databases, tables, and other structures. When building a table, you need to specify:

Column names – The labels for each column
Data types – INT, VARCHAR, DATE, and so on
Constraints – PRIMARY KEY, FOREIGN KEY, NOT NULL Key lesson: Defining primary keys and appropriate data types prevents data integrity issues later

ALTER
ALTER lets you modify existing table structures:

*INSERT *
INSERT adds new rows to a table. Always specify the column names to avoid errors if the table structure changes:

UPDATE
UPDATE modifies existing data. Always use a WHERE clause unless you intentionally want to update every row:

DELETE
DELETE removes specific rows. Like UPDATE, always use WHERE:
Critical warning: Without WHERE, DELETE FROM removes every row in the table!

FILTERING
it lets you target specific rows instead of operating on entire tables. This is essential for both querying data with SELECT and for safe UPDATE/DELETE operations.

Pattern Matching with LIKE
The LIKE operator uses wildcards for flexible text searching:

'A%' – Starts with 'A' (e.g., 'Alice', 'Andrew')

'%son' – Ends with 'son' (e.g., 'Johnson', 'Jackson')

'%Studies%' – Contains 'Studies' anywhere

'_at' – Underscore matches exactly one character (e.g., 'cat', 'bat', 'hat')

Combining Conditions
You can combine multiple conditions using AND and OR. Use parentheses to control logic order:

CASE WHEN
CASE WHEN is SQL's version of an IF-THEN-ELSE statement. It creates calculated columns without altering your original data. This is incredibly useful for reporting and data categorization.

Conclusion
SQL is two languages in one: DDL for shaping the container and DML for managing the contents. The WHERE clause is your precision tool for targeting specific data, and CASE WHEN is your transformation engine for creating meaningful categories on the fly.

The most important lesson I've learned is that every database change – whether structural (DDL) or data-related (DML) – requires careful thinking, testing, and often a double-check of WHERE clauses. One small mistake can have large consequences, but with proper understanding of these fundamental concepts, you can work with databases confidently and safely.

How to Publish a Power BI Report and Embed It on a Website Using iFrames

Collins Ochola — Mon, 06 Apr 2026 20:54:50 +0000

This guide walks you through the complete process: from creating a workspace in Power BI Service, to publishing your report, generating the appropriate embed code, and finally embedding it into a website using an iFrame. We’ll also cover critical security considerations so you can choose the right sharing method for your specific use case.

Understanding Your Publishing and Embedding Options
Before diving into the step-by-step instructions, it’s crucial to understand that Power BI offers different embedding methods for different scenarios. Choosing the wrong option can either expose sensitive data or prevent legitimate users from accessing your reports.

Methods:

Publish to Web (Public)-Anyone with the link can view
Secure Embed (Website or portal)-Only Authenticated Power BI users
SharePoint Online-Only Authenticated Power BI users
Power BI Embedded-Any app user (no Power BI license needed)

To Note
Only Use Publish to web option when your data don't contain any sensitive information

Prerequisites
Before starting, ensure you have:

A completed Power BI report saved locally as a .pbix file

Power BI Desktop installed (free from Microsoft Store)

A work or school Microsoft account (personal accounts like @gmail.com or @outlook.com will not work for publishing to Power BI Service)

Appropriate permissions from your Power BI administrator (especially for “Publish to web” if that’s your chosen method)

How to Create a Workspace:
Open your web browser and navigate to app.powerbi.com

In the left navigation pane, click Workspaces

Click the + New workspace button (usually found at the bottom of the workspace list)

Fill in the workspace details:

Name: Use a clear, descriptive name (e.g., “Sales Analytics – Q1 2026”)

Description: Optional but helpful for team members

Advanced options: Configure license mode and access settings as needed

Click Apply or Save

Publish Your Report from Power BI Desktop
Once your workspace is ready, you can upload your report from Power BI Desktop.

Publishing Steps:
Open your report in Power BI Desktop

Save your file (Ctrl+S) to ensure all recent changes are captured

Look for the Sign in button on the top-right corner of the ribbon. Sign in with your Microsoft work or school account

On the Home ribbon, click the Publish button

![Power BI Desktop Home ribbon with Publish button highlighted]

A dialog box will appear showing available workspaces. Select the workspace you just created (or an existing one you have permission to use)

Click Select

Power BI will upload your file. This usually takes 30 seconds to a few minutes, depending on file size and model complexity

Once complete, you’ll see a success dialog with a link that says “Open ‘your-report-name.pbix’ in Power BI” – click this link to go directly to your published report in Power BI Service
Success confirmation dialog showing that the report has been published with a link to open in Power BI

After Publishing:
Navigate to your workspace in Power BI Service (app.powerbi.com). You should see three items:

The Report – your visualizations

The Dataset – your data model and calculated columns/measures

A Dashboard (optional) – if you created one

Click on the report name to open it. Test all interactions—slicers, drill-throughs, and page navigation—to ensure everything works as expected. Occasionally, embedded images may not upload correctly and need to be re-added directly in the Service .

Generate the Embed Code
Now that your report lives in the cloud, you can generate the code snippet needed to embed it. The steps differ slightly depending on which embedding method you need.
alt text

Publish to Web (Public Embedding) – For Public Data Only
⚠️ Important Warning: This method makes your report and all underlying data publicly accessible to anyone on the internet. Search engines can index it, and there is no authentication required. Never use this for confidential or sensitive information .

Only proceed if you are certain your data is appropriate for public sharing.

Steps:
Open your report in Power BI Service

From the top menu bar, click File → Embed report → Publish to web (public)

A warning dialog will appear explaining that anyone on the internet can view your report. Read it carefully

If you agree, click Create embed code

A second confirmation dialog appears. Click Publish

In the success dialog, you’ll see two options:

Link: A direct URL to view the report in full browser

HTML iFrame code: The embed code to paste into your website

[Publish to web success dialog showing the iFrame embed code and link options]

Understanding Data Modeling in Power BI

Collins Ochola — Tue, 31 Mar 2026 11:00:42 +0000

Any successful Power BI solution starts with data modeling. A well-organized model guarantees precise computations, peak performance, and a user-friendly report. On the other hand, a weak model produces complicated DAX formulae, long refresh times, and confused outcomes. Data modelling has three core concepts

Relationships: A relationship in Power BI describes the connections and interactions between two tables. Relationships in Power BI maintain table separation while establishing a filter propagation path. Properties of Relationships Property Options Explanation . Cardinality This defines how rows in one table relate to rows in another.

One-to-Many (1:*) Most common typeExample: One customer → many orders

Many-to-One (*:1) Same as above, just viewed from the other side

One-to-One (1:1) Each row in both tables matches exactly one row

Many-to-Many (:)Both tables can have multiple matching rows Requires careful handling (can cause ambiguity)

Joins
joins are used to combine data from multiple tables based on a related column. They are mainly applied in Power Query (Transform Data), unlike relationships which are used in the data model.
Schemas:
The schema defines how tables are organized and related. Power BI supports two primary approaches.

Flat Schema (Denormalized)
Flat schema Combines fact data and dimension data into a single table
Star Schema
a data modeling approach that organizes data into a central fact table (containing metrics/transactions) surrounded by multiple dimension tables (containing descriptive attributes).

Best Practices Summary
Schema Use the star schema at all times. Steer clear of snowflakes and flat surfaces. Cardinality: One-to-many is preferred. Many-to-many only under dire circumstances. One direction (one→many) is the default filter direction. Use bidirectional only when necessary. Date table Make a distinct date table, label it as such, and link all date fields to it. Naming Make sure your names are consistent and unambiguous. Differentiate between dimensions (like DimCustomer and DimProduct) and fact tables (like Sales and Inventory). Keep foreign keys hiddenTo prevent misunderstanding, hide foreign key columns from report view. Row-level security To ensure consistent filtering, define RLS on dimension tables.

Conclusion
Finding a balance between analytical flexibility, performance, and simplicity is essential for successful data modeling with Power BI. You can create effective and sustainable models by adopting the star schema, knowing the difference between joins and relationships, and using consistent best practices.

How Excel Powers Real-World Data Analysis

Collins Ochola — Mon, 30 Mar 2026 11:23:46 +0000

In its existence for the last 40 years, Excel still remain a valuable assert in the field of data analysis.
Despite the emergence of python, SQL and machine learning, Most big organizations still trust on the versatility of excel to power their data analysis requirements.

1. Excel and Data Cleaning
In theory, data arrives clean. In reality, it arrives with merged headers, inconsistent date formats, phone numbers scattered across three columns, and duplicate entries that quietly sabotage results.

Excel remains the fastest tool for manual inspection and correction. Analysts use Text to Columns to split messy fields, TRIM to remove rogue spaces, and conditional formatting to highlight duplicates or outliers at a glance. A 30-second Excel fix can save hours of debugging later—which is why experienced analysts often open a CSV in Excel before touching anything else.

*2. Excel is Universal *
Excel allow users to routinely pull raw data from SQL or APIs into Excel, transform it, and deliver insights to stakeholders who may never open to other tool. Its ability to allow data from other sources make it an invaluable assert in the data analysis field.

3. Excel Power Query
Power Query is a data transformation tool built directly into modern versions of Excel, it allows one to connect to almost any data source—databases, web APIs, PDFs, folders of CSV files—and apply radical changes.

Before Power query analyst used to manually copy-paste or VBA scripting
but this can now be set up once and refreshed with a single button.
Power Query alone can cut hours of work down to minutes.

4. Pivot Table and Dashboard In Excel

Pivot table and slicers in Excel allows user to communicate insights effectively. Excel allows users to build interactive models that respond instantly, without waiting for a developer to run a script.The Pivot tables and slicers allow analyst to analyze trends and provide insights that can be used to develop dashboard that can communicate effectively to the end stakeholders.

** 5. The Cons of using Excel**
Excel isn't the right tool for everything. It struggles with datasets over a million rows, lacks robust version control, and can't handle complex machine learning or advanced statistical modeling.

But for analysis that involves cleaning, exploring, testing scenarios, and communicating results, Excel remains unmatched in speed and accessibility.

Conclusion

In as much as Excel can't replace other high end data analysis software's, mastering it is one of the highest-return on investment any analyst can make. It's ability to clean data quickly, test scenarios and communicate insights remain invaluable