<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Raasika Mageshwaran on Medium]]></title>
        <description><![CDATA[Stories by Raasika Mageshwaran on Medium]]></description>
        <link>https://medium.com/@raasikam?source=rss-63d0151ee4fa------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*uKpViQGkrjSKWr1ya3PppA.png</url>
            <title>Stories by Raasika Mageshwaran on Medium</title>
            <link>https://medium.com/@raasikam?source=rss-63d0151ee4fa------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 03:53:20 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@raasikam/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Joins in SQL — DE From Scratch Series]]></title>
            <link>https://raasikam.medium.com/joins-in-sql-de-from-scratch-series-844836978dfb?source=rss-63d0151ee4fa------2</link>
            <guid isPermaLink="false">https://medium.com/p/844836978dfb</guid>
            <category><![CDATA[sql-joins]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[dbms]]></category>
            <dc:creator><![CDATA[Raasika Mageshwaran]]></dc:creator>
            <pubDate>Wed, 11 Feb 2026 17:13:27 GMT</pubDate>
            <atom:updated>2026-02-11T17:13:27.505Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3opwrDe-8VLcmV_ova1iKw.png" /></figure><p>In real-world data systems, information almost never resides in a single table. Instead, data is intentionally distributed across multiple tables to maintain structure, avoid redundancy, and support scalability. Customer details may exist in one table, orders in another, while payments, products, and events are stored separately.</p><p>While this separation is essential for good database design, it creates a new challenge: <strong>how do we combine related pieces of information to answer meaningful business questions?</strong></p><p>Consider common analytical questions such as which customers placed orders last month, what products they purchased, or how much revenue each customer generated. None of these questions can be answered by querying a single table. They require connecting data that is spread across the system.</p><p>This is exactly where <strong>SQL Joins</strong> become indispensable.</p><p>In this article, we will break down SQL Joins from the ground up, exploring what they are, why they exist, and how different join types influence the results produced by queries. Because mastering joins is not about memorizing syntax, but about understanding <strong>how data connects</strong>.</p><p>To understand how SQL joins work in a realistic relational setup, we will use the <strong>Student</strong>, <strong>Course</strong>, and <strong>Course Enrollment</strong> tables defined above. These tables are intentionally designed following normalization principles, where student information, course details, and enrollment relationships are stored separately and connected through primary and foreign key constraints.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/372/1*WpFvfEE4IxaFtya1m4m-Yw.png" /><figcaption>Student Table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/349/1*eABWdwR9YtY1JuLwQlrhsQ.png" /><figcaption>Course Table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/213/1*hhGF9fH8I871vH9MI1ZJKg.png" /><figcaption>Course_Enrollment Table</figcaption></figure><p>Using the above schema, we will explore how joins help combine data across related tables, how constraints maintain data integrity, and how different join types shape query results. This approach mirrors real-world database designs, making the concepts both practical and easier to reason about.</p><h4>1. INNER JOIN:</h4><p>An inner join returns only the rows that have matching values in both tables based on the join condition. If a record does not satisfy the relationship defined in the ON clause, it is excluded from the result.</p><p>This join is typically used when you only care about valid intersections between datasets, such as students who actually enrolled in courses or orders that have corresponding customers.</p><p>For the following query between the Course_Enrollment Table and the Student Table,</p><pre>SELECT<br>    s.roll_no,<br>    s.student_name,<br>    s.gender,<br>    e.course_no<br>FROM Course_Enrollment e<br>INNER JOIN Student s<br>    ON e.roll_no = s.roll_no;</pre><p>The Resultant Table would look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/408/1*nBI9fAqlHfykAHaKZ3zGbQ.png" /><figcaption>Resultant Table</figcaption></figure><h4>2. RIGHT JOIN:</h4><p>A right join behaves like a mirror of the LEFT JOIN. It preserves all rows from the right table and includes matching rows from the left table. When no match exists, the left-side columns become NULL.</p><p>Although less frequently used in practice, it is helpful when the right table represents the primary dataset you want to retain, such as displaying all courses even if no students enrolled.</p><p>For the following query between the Course_Enrollment Table and the Course Table,</p><pre>SELECT<br>    e.roll_no,<br>    c.course_no,<br>    c.course_name,<br>    c.faculty_name<br>FROM Course_Enrollment e<br>RIGHT JOIN Course c<br>    ON e.course_no = c.course_no;</pre><p>The Resultant table would look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/409/1*wld9je1q9c0XFdWEdJHWsw.png" /><figcaption>Resultant table</figcaption></figure><h4>3. LEFT JOIN:</h4><p>A left join returns all rows from the left table and only the matching rows from the right table. If no match exists, the columns from the right table are filled with NULL values.</p><p>This join is extremely useful when you want to preserve the primary dataset while optionally enriching it, such as listing all students regardless of enrollment or showing all customers even if they placed no orders.</p><p>For the following query between the Course_Enrollment Table and the Course Table,</p><pre>SELECT<br>    e.roll_no,<br>    e.course_no,<br>    c.course_name,<br>    c.faculty_name<br>FROM Course_Enrollment e<br>LEFT JOIN Course c<br>    ON e.course_no = c.course_no;</pre><p>The Resultant table would look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/409/1*J83KtHs8MWXP5RpCAp6Yng.png" /><figcaption>Resultant Table</figcaption></figure><h4>4. FULL OUTER JOIN:</h4><p>A full outer join returns all rows from both tables, combining matching records where possible and filling non-matching sides with NULL values.</p><p>This join is ideal for reconciliation and validation scenarios, as it reveals both matched and unmatched data. It is commonly used when comparing datasets, identifying missing relationships, or performing data quality checks.</p><p>For the following query between the Student Table and the Course_Enrollment Table,</p><pre>SELECT<br>    s.roll_no,<br>    s.student_name,<br>    s.gender,<br>    e.course_no<br>FROM Student s<br>FULL OUTER JOIN Course_Enrollment e<br>    ON s.roll_no = e.roll_no;</pre><p>The Resultant Table would look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/407/1*mu1b_ndFjLjDbnCBTKoL2A.png" /><figcaption>Resultant Table</figcaption></figure><h4>5. CROSS JOIN (CARTESIAN JOIN) :</h4><p>A cross join returns every possible combination of rows between two tables, producing a Cartesian product. No join condition is required, and the result size equals the multiplication of row counts from both tables.</p><p>While rarely used in standard analytics queries, CROSS JOINs are valuable for generating test data, building combinations, or solving matrix-style problems.</p><p>For the following query between the Student Table and the Course Table,</p><pre>SELECT<br>    s.student_name,<br>    c.course_name<br>FROM Student s<br>CROSS JOIN Course c;</pre><p>The Resultant table would look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/254/1*rk6grx0EVW4B9t3u25iajA.png" /><figcaption>Resultant Table</figcaption></figure><h4>6. SELF JOIN:</h4><p>A self join occurs when a table is joined with itself using aliases to represent different logical roles.</p><p>It is typically used to model hierarchical or recursive relationships, such as employees and their managers or categories and their parent categories. Despite involving a single table, it behaves like a normal join, enabling comparisons or relationships within the same dataset.</p><p>Consider the Employee Table where an employee him/herself can in turn be the manager for other employees.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/308/1*RvtZST5Wy91NiSHeRLsPyQ.png" /><figcaption>Employee Table</figcaption></figure><p>The following self-join query results in finding the manager of each employee:</p><pre>SELECT<br>    e.emp_name AS employee,<br>    m.emp_name AS manager<br>FROM Employee e<br>LEFT JOIN Employee m<br>    ON e.manager_id = m.emp_id;</pre><p>The Resultant table would look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/211/1*S0HJjILv0KokOJP3e0Dunw.png" /><figcaption>Resultant Table</figcaption></figure><h4>CONCLUSION:</h4><p>SQL joins are far more than just a query feature, they are the foundation of how relational data systems derive meaning from distributed tables. In well-designed databases, information is intentionally separated to reduce redundancy and maintain integrity, and joins are what reconnect that data when analysis or business logic demands it.</p><p>Through the examples in this article, we explored how different join types shape query results, how matched and unmatched rows behave, and why choosing the correct join directly impacts data accuracy. From INNER JOINs that focus on valid intersections to OUTER JOINs that expose missing relationships, each join serves a distinct analytical purpose.</p><p>For Data Engineers, mastering joins is not simply about writing correct syntax. It is about understanding relationships, preventing data duplication, avoiding incorrect aggregations, and building reliable datasets for downstream analytics. Poor join logic can silently corrupt metrics, while well-designed joins enable trustworthy insights.</p><p>In practice, joins sit at the core of transformations, validations, modeling, and reporting, making them one of the most essential skills in Data Engineering.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=844836978dfb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Basics — DE From Scratch Series]]></title>
            <link>https://raasikam.medium.com/sql-basics-de-from-scratch-series-deb2fb4ee604?source=rss-63d0151ee4fa------2</link>
            <guid isPermaLink="false">https://medium.com/p/deb2fb4ee604</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[rdbms]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[dbms]]></category>
            <dc:creator><![CDATA[Raasika Mageshwaran]]></dc:creator>
            <pubDate>Mon, 09 Feb 2026 14:57:05 GMT</pubDate>
            <atom:updated>2026-02-09T14:57:05.665Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oUMVFq6tBLn99KVbfzwIyQ.png" /></figure><h3>SQL Basics — DE From Scratch Series</h3><p>In today’s data-driven world, almost every application, whether a mobile app, website, or enterprise system relies on databases to store and manage information. At the heart of these databases lies <strong>SQL (Structured Query Language)</strong>, the standard language used to communicate with relational database systems. From simple data retrieval to complex analytical operations, SQL plays a critical role in how modern software applications function.</p><p>SQL is not just a tool for database administrators or backend developers. Data analysts use it to uncover insights, data scientists rely on it to prepare datasets, and even product teams depend on SQL-driven reports to make informed decisions. Its simplicity, power, and wide adoption have made SQL one of the most essential skills for anyone working with data.</p><p>This article takes a structured journey through SQL, from foundational concepts to more advanced ideas. We begin by understanding what SQL is and how it works, then explore core command categories such as <strong>DDL, DML, DQL, TCL, and DCL</strong>. By the end, you will have a solid conceptual understanding of SQL and a strong base to confidently write queries and explore advanced database features.</p><h3><strong>What is SQL?</strong></h3><p><strong>SQL (Structured Query Language)</strong> is the standard language used to interact with relational databases. It enables users to store, retrieve, manage, and manipulate data efficiently, forming the backbone of most modern data-driven applications. Popular database systems such as MySQL, PostgreSQL, SQL Server, Oracle, and SQLite all rely on SQL as their primary interface.</p><p>With SQL, you can query data from tables, insert new records, update existing information, and remove data when necessary. It also allows you to define database structures, manage user access and permissions, and handle transactions safely to maintain data consistency.</p><p>Relational databases organize information into structured tables made up of rows and columns, and SQL acts as the bridge between users and this structured data, making it an essential skill for anyone working with data or software systems.</p><h3>Key SQL Components</h3><p>SQL commands are organized into logical categories based on the type of operation they perform. This separation helps developers and data professionals clearly understand <strong>what kind of action</strong> a command is intended to execute, whether it defines structure, manipulates data, retrieves information, or controls access. Below are the five core categories that form the foundation of SQL.</p><h4>Data Definition Language (DDL)</h4><p>Data Definition Language (DDL) is used to <strong>define and manage the structure of a database</strong>. These commands deal with database objects such as tables, schemas, indexes, and views. In simple terms, DDL answers the question: <em>How is the data stored?</em></p><p>With DDL, you can create new tables, modify existing table structures, or permanently remove database objects. Since DDL operations affect the database schema itself, they are generally auto-committed, meaning the changes take effect immediately and cannot be rolled back in most database systems.</p><p>DDL is typically used during database design and maintenance phases, making it essential for database administrators and backend developers.</p><h4>Data Manipulation Language (DML)</h4><p>Data Manipulation Language (DML) focuses on <strong>working with the data stored inside database tables</strong>. While DDL defines the structure, DML handles the actual records within that structure.</p><p>Using DML commands, you can insert new rows, update existing data, or delete records that are no longer needed. These operations are transactional in nature, meaning they can usually be committed or rolled back depending on the outcome of a transaction.</p><p>DML is one of the most frequently used parts of SQL, as it directly supports everyday operations such as updating user details, processing transactions, or maintaining application data.</p><h4>Data Query Language (DQL)</h4><p>Data Query Language (DQL) is responsible for <strong>retrieving data from the database</strong>. Its primary purpose is to allow users to read and analyze stored information without modifying it.</p><p>Through DQL, users can fetch entire tables, select specific columns, filter records using conditions, sort results, and perform aggregations such as counting or averaging values. Although often considered part of DML, DQL is commonly treated as a separate category due to its central role in data analysis and reporting.</p><p>DQL is especially important for data analysts, business intelligence teams, and anyone who needs to extract insights from data.</p><h4>Transaction Control Language (TCL)</h4><p>Transaction Control Language (TCL) manages <strong>database transactions</strong>, ensuring data consistency and reliability. A transaction is a sequence of operations performed as a single logical unit of work.</p><p>TCL commands allow you to save changes permanently, undo changes if something goes wrong, or define checkpoints within a transaction. This is crucial in scenarios where multiple operations must either all succeed or all fail, such as financial transactions or inventory updates.</p><p>By using TCL, databases maintain the principles of atomicity and consistency, which are core components of reliable database systems.</p><h4>Data Control Language (DCL)</h4><p>Data Control Language (DCL) is used to <strong>control access to data within the database</strong>. These commands determine who can read, modify, or manage database objects.</p><p>With DCL, administrators can grant specific privileges to users or revoke permissions when access is no longer required. This ensures that sensitive data is protected and that users only have access to what they need to perform their roles.</p><p>DCL plays a vital role in database security, compliance, and role-based access control, especially in enterprise environments.</p><h3>Common DDL Commands</h3><ol><li>CREATE: The CREATE command is used to create new database objects such as tables, databases, or indexes. When creating a table, you define its columns, data types, and constraints.</li></ol><pre>CREATE TABLE employees (<br>    id INT PRIMARY KEY,<br>    name VARCHAR(100),<br>    department VARCHAR(50),<br>    salary DECIMAL(10,2)<br>);</pre><p>2. ALTER: The ALTER command modifies an existing database object. It is commonly used to add, remove, or change columns in a table without recreating it.</p><pre>ALTER TABLE employees<br>ADD email VARCHAR(100);</pre><p>3. DROP: The DROP command permanently removes a database object along with all its data and structure.</p><pre>DROP TABLE employees;</pre><p>4. TRUNCATE: The TRUNCATE command removes all records from a table while preserving its structure. Unlike DELETE, it is faster and uses fewer system resources.</p><pre>TRUNCATE TABLE employees;</pre><h3>Common DML Queries</h3><ol><li>INSERT: The INSERT query is used to add new records into a table. Each insertion follows the table’s predefined structure and constraints.</li></ol><pre>INSERT INTO employees (id, name, department, salary)<br>VALUES (1, &#39;Alice&#39;, &#39;HR&#39;, 50000);</pre><p>2. UPDATE: The UPDATE query modifies existing records in a table. It is commonly used to correct data or reflect changes over time.</p><pre>UPDATE employees<br>SET salary = 55000<br>WHERE id = 1;</pre><p>3. DELETE: The DELETE query removes one or more records from a table based on a specified condition.</p><pre>DELETE FROM employees<br>WHERE department = &#39;HR&#39;;</pre><p>Best Practice: Always include a WHERE clause when using UPDATE or DELETE. Omitting it can result in modifying or deleting <strong>all rows</strong> in the table, leading to accidental data loss.</p><h3>Common DQL Queries</h3><ol><li>SELECT: The SELECT query retrieves data from one or more tables. This query fetches all columns and rows from the employees table.</li></ol><pre>SELECT * FROM employees;</pre><p>2. WHERE: The WHERE clause filters records based on specific conditions. This statement returns only employees whose salary exceeds 60,000.</p><pre>SELECT name, salary<br>FROM employees<br>WHERE salary &gt; 60000;</pre><p>3. ORDER BY: The ORDER BY clause sorts query results in ascending or descending order. Here, employees are listed from highest to lowest salary.</p><pre>SELECT *<br>FROM employees<br>ORDER BY salary DESC;</pre><p>4. AGGREGATE FUNCTIONS: Aggregate functions perform calculations on a set of values and return a single result.</p><pre>SELECT department, AVG(salary)<br>FROM employees<br>GROUP BY department;</pre><p>This query calculates the average salary for each department. Common aggregate functions include:</p><ul><li>COUNT() – counts the number of records</li><li>SUM() – calculates the total value</li><li>AVG() – computes the average value</li><li>MIN() – returns the minimum value</li><li>MAX() – returns the maximum value</li></ul><h3>Common TCL Queries</h3><ol><li>COMMIT: The COMMIT query is used to permanently save all changes made during the current transaction. Once executed, the changes become permanent and cannot be rolled back.</li></ol><pre>COMMIT;</pre><p>2. ROLLBACK: The ROLLBACK query undoes all changes made since the last COMMIT. This command is useful when an error occurs and you need to restore the database to its previous stable state.</p><pre>ROLLBACK;</pre><p>3. SAVEPOINT: The SAVEPOINT query creates a logical checkpoint within a transaction, allowing partial rollbacks. Here, a savepoint named sp1 is created, and the transaction can be rolled back to that specific point without undoing the entire transaction.</p><pre>SAVEPOINT sp1;<br>ROLLBACK TO sp1;</pre><h3>Common DCL Queries</h3><ol><li>GRANT: The GRANT query is used to assign specific privileges to users or roles on database objects. This command allows user1to read from and insert data into the employees table.</li></ol><pre>GRANT SELECT, INSERT<br>ON employees<br>TO user1;</pre><p>2. REVOKE: The REVOKE query removes previously granted privileges from a user or role. Here, the permission to insert data into the EMPLOYEES table is removed from user1.</p><pre>REVOKE INSERT<br>ON employees<br>FROM user1;</pre><h3>Conclusion</h3><p>SQL is a foundational skill for anyone working with data, forming the backbone of modern applications and analytics systems. From defining database structures using <strong>DDL</strong>, manipulating data with <strong>DML</strong>, retrieving insights through <strong>DQL</strong>, managing reliability via <strong>TCL</strong>, to enforcing security with <strong>DCL</strong>, each component of SQL serves a distinct and essential purpose.</p><p>Understanding these command categories helps you approach databases with clarity and confidence, knowing not just <em>how</em> to write queries, but <em>why</em> and <em>when</em> to use them. As data continues to grow in volume and importance, SQL remains a timeless and indispensable tool across industries.</p><p>Mastering SQL is a journey, not a one-time task. With consistent practice and real-world application, these fundamentals become the stepping stones to advanced topics such as joins, window functions, performance tuning, and large-scale data engineering workflows.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=deb2fb4ee604" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Modeling 101 (Article:02) — DE from Scratch Series — Normalization for OLTP Databases]]></title>
            <link>https://raasikam.medium.com/data-modeling-101-article-02-de-from-scratch-series-normalization-for-oltp-databases-008ad439ffd6?source=rss-63d0151ee4fa------2</link>
            <guid isPermaLink="false">https://medium.com/p/008ad439ffd6</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[oltp]]></category>
            <category><![CDATA[data-normalization]]></category>
            <dc:creator><![CDATA[Raasika Mageshwaran]]></dc:creator>
            <pubDate>Sat, 24 Jan 2026 08:19:17 GMT</pubDate>
            <atom:updated>2026-01-24T08:19:17.672Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AuanQdQG2Bz6HnRrns90YQ.png" /><figcaption>Thumbnail</figcaption></figure><h3>Data Modeling 101 (Article:02) — DE from Scratch Series — Normalization for OLTP Databases</h3><p>In the previous article of the <strong>Data Modeling 101 — DE From Scratch Series</strong>, we explored why OLTP databases rely on normalization and how it helps maintain data correctness, consistency, and integrity as systems scale.</p><p>If you haven’t read it yet, you can check it out here: <a href="https://medium.com/@raasikamageshwaran2510/data-modeling-101-article-01-de-from-scratch-series-43b96eef62f1?source=friends_link&amp;sk=baa8d3efd53b6df5bc1a403eeddea844">link for the article.</a></p><p>In this article, we go one level deeper.</p><p>Instead of just <em>why</em> normalization matters, we’ll focus on:</p><ul><li><strong>the real-world problems normalization solves</strong></li><li><strong>how normalization structurally fixes those problems</strong></li><li><strong>and how Normal Forms (1NF -&gt; 2NF -&gt; 3NF -&gt; BCNF -&gt;4NF -&gt; 5NF) guide clean OLTP database design</strong></li></ul><h3>I. Problems Normalization Solves in OLTP Systems</h3><p>OLTP systems are designed to handle continuous, real-time activity. Every second, they process actions such as placing orders, making payments, logging into applications, updating user details, and booking services. Because these systems operate at such high frequency and scale, even small design flaws can quickly turn into serious data issues.</p><p>This is where normalization becomes essential.</p><p>Without normalization, OLTP databases tend to develop structural problems that slowly erode data integrity and reliability. Let’s look at the most common problems normalization is designed to solve.</p><ol><li><strong>Data Duplication</strong></li></ol><p>One of the first issues that appears in non-normalized systems is data duplication. The same piece of information such as a customer’s name, email, or address, ends up being stored in multiple tables. For example, customer details might be repeated across orders, payments, and shipping tables.</p><p>Over time, this duplication leads to wasted storage, inconsistent data values, and increased maintenance effort. More importantly, it becomes difficult to guarantee which version of the data is correct.</p><blockquote>Normalization addresses this problem by ensuring that each fact is stored exactly once. Other tables reference that data using keys, rather than copying it. This keeps the database clean and consistent.</blockquote><p><strong>2. Updation Anomalies</strong></p><p>When duplicated data exists in multiple places, updates become risky. A simple change such as a customer updating their email address may need to be applied in several tables.</p><p>If even one of those updates fails, the system ends up with conflicting information. Different parts of the application may show different values for the same customer, making the data unreliable and hard to trust.</p><blockquote>Normalization prevents this by ensuring that updates occur in one central location, eliminating the possibility of partial or inconsistent updates.</blockquote><p><strong>3. Insertion Anomalies</strong></p><p>Another common issue in poorly designed schemas is the insert anomaly. This happens when inserting one type of data requires unrelated data to exist first.</p><p>For example, a system may not allow a customer to be added unless they have already placed an order. This creates unnecessary dependencies between entities and limits flexibility in how data can be stored.</p><blockquote>Normalization removes these artificial constraints by separating entities properly, allowing each type of data to be inserted independently and logically.</blockquote><p><strong>4. Deletion Anomalies</strong></p><p>Delete anomalies occur when removing one record unintentionally removes important related data. A classic example is deleting an order and accidentally losing all associated customer information.</p><p>In OLTP systems, where transactions are frequent and automated, this kind of data loss can be catastrophic.</p><blockquote>Normalization prevents such scenarios by clearly separating entities and defining relationships explicitly, ensuring that deleting one record does not remove unrelated business data.</blockquote><p><strong>5. Integrity Violations</strong></p><p>Without a normalized structure, relationships between tables often remain implicit rather than enforced. This means there may be no foreign keys, no constraints, and no guarantees that references are valid.</p><p>As a result, databases can contain orphan records, broken relationships, and logically incorrect data.</p><blockquote>Normalization enforces relational integrity by defining clear relationships, constraints, and dependencies. This ensures the database behaves predictably and maintains correctness as it scales.</blockquote><h3><strong>II. Normal Forms</strong></h3><p>To truly understand normalization, it’s best to see it in action, not as abstract rules.</p><p>In this article, we’ll take a single e-commerce table and progressively normalize it step by step, from a messy design to a clean OLTP-ready schema. Imagine an early-stage e-commerce application storing order data like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/767/1*_KjnrvoG_KHr88jl-CndqQ.png" /><figcaption>Order_Details: Denormalized Table</figcaption></figure><ol><li><strong>First Normal Form (1NF): Atomic Values</strong></li></ol><blockquote>Each column must contain atomic (indivisible) values.<br>No lists. No repeating groups.</blockquote><p>We split each product into its own row:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/683/1*wJeJNhpU3TU9hh3yJsXZaw.png" /><figcaption>Order_Items_1NF</figcaption></figure><p><strong><em>What 1NF Solves:</em></strong></p><ul><li>Makes rows predictable</li><li>Enables proper querying</li><li>Removes multi-valued columns</li></ul><p><strong>2. Second Normal Form (2NF): Remove Partial Dependencies</strong></p><blockquote>Must be in 1NF and all non-key columns must depend on the full primary key.</blockquote><p>Here, the composite key is Order_Id, Product.</p><ul><li>Customer_Name, Customer_Email , and Order_Date depend only on Order_Id.</li><li>They are repeated for every product in the same order</li></ul><p><strong><em>This causes data duplication.</em></strong></p><p>We normalize the table as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/461/1*51eWQlMZnTyX7jOSzijglA.png" /><figcaption>Orders</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/348/1*SQae-SPzM5zY1udpuIennA.png" /><figcaption>Order_Items</figcaption></figure><p><strong>What 2NF Solves?</strong></p><ul><li>Removes partial dependencies</li><li>Reduces redundancy</li><li>Each table represents a single concept</li></ul><p><strong>3. Third Normal Form (3NF): Remove Transitive Dependencies</strong></p><blockquote>Non-key attributes should not depend on the key attribute through any other non-key attributes.</blockquote><p>In Orders table:</p><ul><li>Customer_Email depends on Customer_Name (a non-key attribute)</li><li>Customer info is repeated across orders</li></ul><p><strong>This causes update anomalies if customer details change.</strong></p><p>We normalise the table by splitting the customer info seperately:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/351/1*PNKw7Pj0lmbkWFYmSBBGtA.png" /><figcaption>Customers</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/347/1*xNDtJIxTWjl4JWlTsE7OAA.png" /><figcaption>Orders</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/1*OnCm3xpQaWF7MiOTPu4UiQ.png" /><figcaption>Order_Items</figcaption></figure><p><strong>What 3NF Solves?</strong></p><ul><li>Eliminates hidden dependencies</li><li>Ensures clean updates</li><li>Improves data integrity</li></ul><p><strong>4. BCNF (Boyce–Codd Normal Form): Stronger Dependency Rules</strong></p><blockquote>Every determinant must be a candidate key.</blockquote><p>Suppose:</p><ul><li>Each product has exactly one price</li><li>Product -&gt; Product_Price</li></ul><p>Then Product_Price doesn’t really depend on Order_ID.</p><p>We split product details:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/347/1*FAAEOvYs7ySauTYIHsQZDw.png" /><figcaption>Products</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/232/1*6tccKr6FssHvtqlG_DDGzg.png" /><figcaption>Order_Items</figcaption></figure><p><strong>What BCNF Solves?</strong></p><ul><li>Removes subtle dependency issues</li><li>Ensures stronger correctness guarantees</li><li>Common in well-designed OLTP systems</li></ul><p><strong>5. Fourth Normal Form (4NF): Handling Multi-valued Dependencies</strong></p><p>Fourth Normal Form (4NF) addresses a situation where a single entity has <strong>multiple independent relationships</strong>. For example, a customer can have multiple phone numbers and multiple delivery addresses. If both of these are stored in the same table, the database ends up creating unnecessary combinations of phone numbers and addresses, even though they are unrelated to each other.</p><p>This leads to data redundancy and unnecessary complexity. 4NF solves this by separating independent multi-valued attributes into their own tables, such as Customer_Phones and Customer_Addresses.</p><p>While 4NF is not commonly required in simple systems, it becomes important in more complex OLTP schemas where entities have multiple independent attributes.</p><p><strong>6. Fifth Normal Form (5NF): Eliminating Join Dependencies</strong></p><p>Fifth Normal Form (5NF) focuses on eliminating <strong>join dependencies</strong>, ensuring that a table cannot be decomposed further without losing information.</p><p>In other words, data should not be split into multiple tables unless it can be reconstructed correctly through joins. 5NF is typically relevant in very complex relational scenarios involving multiple overlapping relationships and business rules.</p><p>In most real-world OLTP systems, 5NF is rarely implemented explicitly, but understanding it helps ensure that normalization does not go too far and compromise data integrity or meaning.</p><h3>Final Thoughts</h3><p>Normalization is not just a set of theoretical rules, it is a <strong>practical design approach</strong> that keeps OLTP databases reliable as systems scale. By structuring data correctly, normalization ensures that transactional systems can handle frequent inserts, updates, and deletes without breaking data integrity.</p><p>Through the e-commerce example in this article, we saw how normalization gradually transforms a messy design into a clean, maintainable schema. Each normal form solves a specific class of problems, from eliminating duplication and update anomalies to enforcing stronger dependency rules.</p><p>In real-world OLTP systems, <strong>Third Normal Form (3NF)</strong> or <strong>BCNF</strong> is usually the practical stopping point. While <strong>Fourth and Fifth Normal Forms</strong> are less common, understanding them helps recognize edge cases in complex schemas and prevents over- or under-normalization.</p><p>As a Data Engineer, knowing <em>when</em> and <em>why</em> to normalize is far more important than memorizing the rules. A well-normalized OLTP schema becomes the foundation for stable applications and ultimately, for reliable analytics downstream.</p><h3>Stay Connected</h3><p>If you enjoyed this breakdown and want more Data Engineering + Data Modeling concepts explained from scratch, feel free to connect with me on: <a href="http://www.linkedin.com/in/raasikamageshwaran">LinkedIn</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=008ad439ffd6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Modeling 101 (Article:01) — DE from Scratch Series]]></title>
            <link>https://raasikam.medium.com/data-modeling-101-article-01-de-from-scratch-series-43b96eef62f1?source=rss-63d0151ee4fa------2</link>
            <guid isPermaLink="false">https://medium.com/p/43b96eef62f1</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineer]]></category>
            <dc:creator><![CDATA[Raasika Mageshwaran]]></dc:creator>
            <pubDate>Fri, 23 Jan 2026 04:17:31 GMT</pubDate>
            <atom:updated>2026-01-24T07:34:51.471Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kMy_H8D0wkzAJSDaTH3bIQ.png" /></figure><h3>Data Modeling 101 (Article:01) — DE from Scratch Series — Intro to Data Modeling</h3><p>If you’ve ever wondered why dashboards don’t match, why SQL queries become messy, or why databases feel hard to maintain as data grows — chances are the real issue is not SQL…</p><p>It’s <strong>data modeling</strong>. In this first blog of Data Modeling 101 (A part of DE from Scratch Series), I’ll cover:</p><ol><li><em>What is Data Modeling</em></li><li><em>Types of Data Modeling</em></li><li><em>OLTP vs OLAP transactions</em></li><li><em>Normalization vs Denormalization</em></li><li><em>Why Normalization &amp; Denormalization Matters in OLTP and OLAP systems.</em></li></ol><h3><strong>I. What is Data Modeling?</strong></h3><p><strong><em>Data modeling is the process of designing how data is structured, stored, and connected.</em></strong></p><p>In simple terms:</p><blockquote>Data modeling is like creating a blueprint for data — so it becomes easier to store, understand, and query later.</blockquote><p>A data model helps answer questions like:</p><ul><li>What tables should exist?</li><li>What columns should each table have?</li><li>How are entities related (customer → order)?</li><li>Where should duplication be avoided?</li><li>How can data stay clean as it grows?</li></ul><h3><strong>II. Why is Data Modeling Important in Data Engineering?</strong></h3><p>In Data Engineering, data comes from multiple sources and pipelines. Without a good model:</p><ul><li>data becomes inconsistent</li><li>queries become slow and complex</li><li>teams lose trust in data</li><li>maintaining pipelines becomes painful</li></ul><p>A good data model improves:</p><ol><li><strong>Consistency</strong> (same definition of metrics everywhere)</li><li><strong>Data quality</strong> (less duplication &amp; fewer mismatches)</li><li><strong>Performance</strong> (faster querying)</li><li><strong>Scalability</strong> (easy to add new features/tables)</li><li><strong>Maintainability</strong> (simpler systems over time)</li></ol><h3><strong>III. Types of Data Modeling</strong></h3><p>When we say “data modeling”, it doesn’t always mean creating tables immediately. In reality, data modeling happens in <strong>three levels</strong>, and each level answers a different question.</p><p>Think of it as moving from <strong>business ideas → relationships → database implementation</strong>.</p><ol><li><strong><em>Conceptual Data Model (Business View)</em></strong></li></ol><p>A conceptual data model is the most high-level view of data. It focuses on answering one simple question:</p><p><strong>What are the main things (entities) involved in the business?</strong></p><p>At this stage, we don’t worry about columns, datatypes, or even relationships in detail. We only identify the key entities that matter.</p><p>For example, in an e-commerce business, the key entities could be:</p><ul><li>Customer</li><li>Product</li><li>Order</li><li>Payment</li></ul><p>This model is mainly used to help business stakeholders and technical teams align on <em>what data exists</em> and what needs to be tracked. It’s like the “big picture” of the business data.</p><p><strong><em>2. Logical Data Model (Relationship View)</em></strong></p><p>Once we know <em>what entities exist</em>, the next step is <strong>to define how these entities relate to each other.</strong> That’s exactly what a logical data model does.</p><p>This stage answers questions like:</p><ul><li>Can a customer place multiple orders?</li><li>Can an order contain multiple products?</li><li>Is a payment linked to one order or many?</li></ul><p>For example:</p><ul><li>A <strong>customer</strong> can place <strong>many orders</strong></li><li>An <strong>order</strong> can contain <strong>multiple products</strong></li></ul><p>At this stage, we still don’t commit to a specific database system (like PostgreSQL, MySQL, etc.). We focus on defining:</p><ul><li>Relationships</li><li>Business rules</li><li>Data consistency logic</li></ul><p><strong><em>3. Physical Data Model (Database View)</em></strong></p><p>The physical data model is where the design becomes real. This is the final step where <strong>we convert business entities and relationships into actual database structures</strong>.</p><p>This stage defines:</p><ul><li>actual <strong>tables and columns</strong></li><li><strong>datatypes</strong> (INT, VARCHAR, DATE, etc.)</li><li><strong>primary keys</strong></li><li><strong>foreign keys</strong></li><li>indexes (for performance)</li></ul><p>For example, instead of saying “Customer exists”, we define the table like:</p><p><em>customer_id INT PRIMARY KEY, order_id INT, etc.</em></p><p>This model represents what actually gets implemented inside:</p><ul><li>Operational databases (OLTP)</li><li>Warehouses/lakes (OLAP)</li></ul><p>To Summarize this section:</p><blockquote><strong>Conceptual model</strong> = <em>what exists</em> (entities)</blockquote><blockquote><strong>Logical model</strong> = <em>how they connect</em> (relationships)</blockquote><blockquote><strong>Physical model</strong> = <em>how it is stored</em> (tables + schema)</blockquote><h3><strong>IV. OLTP vs OLAP and Why Normalization vs Denormalization Matters?</strong></h3><p>One of the most important fundamentals in data modeling is understanding this simple truth:</p><blockquote><strong><em>The way we model data depends entirely on the purpose of the system.</em></strong></blockquote><p>Not all databases are designed for the same job. Some systems are built to run the business, while others are built to analyze the business. This is where the concepts of OLTP vs OLAP come in — and this difference is also the reason why normalization and denormalization exist.</p><p><strong><em>I. OLTP: Online Transaction Processing (Run the Business)</em></strong></p><p><strong>OLTP systems are transactional systems.</strong><br>They are the databases powering day-to-day operations — basically the systems where users interact and transactions happen.</p><p>Examples of OLTP use cases include:</p><ul><li>placing orders (e-commerce apps)</li><li>making payments (banking apps)</li><li>logging into applications</li><li>booking tickets or rides</li></ul><p>Since OLTP systems deal with real-time user activity, they have some common characteristics:</p><ul><li><strong>lots of small reads and writes</strong></li><li><strong>frequent INSERT/UPDATE operations</strong></li><li><strong>many concurrent users</strong></li><li><strong>data accuracy and consistency are critical</strong></li></ul><p>Because OLTP systems are updated constantly, the data model must be built in a way that avoids errors and prevents inconsistencies. That’s why OLTP systems typically use <strong>normalization</strong>.</p><blockquote><strong>OLTP databases are usually normalized because it keeps the data clean and consistent.</strong></blockquote><p>Common OLTP databases: <strong>PostgreSQL, MySQL, SQL Server, Oracle</strong></p><p><strong><em>II. OLAP: Online Analytical Processing (Understand the Business)</em></strong></p><p>On the other hand, <strong>OLAP systems are analytical systems.</strong><br>They are used not to run transactions, but to derive insights from data.</p><p>Examples of OLAP use cases include:</p><ul><li>dashboards and KPI tracking</li><li>business reporting</li><li>monthly revenue insights</li><li>trend and growth analysis</li></ul><p>Since OLAP systems are built for analysis, their workload looks different from OLTP:</p><ul><li><strong>large read-heavy queries</strong></li><li><strong>heavy aggregations (SUM, COUNT, AVG)</strong></li><li><strong>historical data analysis</strong></li><li>fewer users (mainly analysts/business teams)</li></ul><p>Here, the priority is not “fast updates”, but rather fast reads and simple querying across large datasets. That’s why OLAP systems typically use denormalization.</p><blockquote><strong>OLAP databases are usually denormalized to reduce joins and improve performance for analytics queries.</strong></blockquote><p>Common OLAP warehouses: <strong>Snowflake, BigQuery, Redshift, Azure Synapse</strong></p><h3><strong>V. Normalization vs Denormalization: The Reason Behind the Choice</strong></h3><p>Now that we understand OLTP and OLAP, normalization and denormalization become much easier to connect.</p><p><strong><em>I. What is Normalization and Why OLTP Uses It?</em></strong></p><p><strong>Normalization</strong> is the process of structuring data in a way that reduces duplication and improves data integrity.</p><p>It generally results in:</p><ul><li><strong>more tables</strong></li><li><strong>less redundancy</strong></li><li>accurate and consistent updates</li></ul><p>For example, in a normalized OLTP model:<br>Customer data is stored once in a customers table, and other tables refer to it using customer_id.</p><p>This is extremely important because in OLTP systems:</p><ul><li>If customer info changes, you update it once.</li><li>You avoid storing the same customer name/email in multiple places.</li><li>You prevent inconsistent values across the database.</li></ul><p>In short:</p><blockquote><strong><em>Normalization helps OLTP systems stay consistent while handling frequent updates.</em></strong></blockquote><p><strong><em>II. What is Denormalization and Why OLAP Uses It?</em></strong></p><p><strong>Denormalization</strong> means combining or flattening tables to reduce joins and make querying easier.</p><p>It typically results in:</p><ul><li>fewer tables</li><li>repeated values (controlled duplication)</li><li>faster reads for analytics</li></ul><p>For example, in an OLAP model:<br>Instead of joining customer details every time, some customer attributes might already be present in the analytics tables (or modeled through dimensional schemas like Star Schema).</p><p>This is preferred in OLAP systems because:</p><ul><li>Analytical queries often need data from many entities at once.</li><li>Multiple joins across huge datasets can become slow and expensive.</li><li>Denormalized models make queries simpler and faster for BI tools.</li></ul><p>In short:</p><blockquote><strong><em>Denormalization helps OLAP systems run analytics faster and more efficiently.</em></strong></blockquote><h3>VI. Final Thoughts</h3><p>To wrap it up — <strong>data modeling is the foundation of building reliable data systems</strong>. Whether you’re designing tables for day-to-day transactions or shaping data for analytics and reporting, a good data model ensures your data stays: consistent, scalable, easy to query, trustworthy for decision-making.</p><h3>Stay Connected</h3><p>If you enjoyed this breakdown and want more Data Engineering + Data Modeling concepts explained from scratch, feel free to connect with me on LinkedIn: <a href="http://www.linkedin.com/in/raasikamageshwaran"><strong>www.linkedin.com/in/raasikamageshwaran</strong></a></p><p>I’ll be sharing more posts and upcoming blogs regularly. See you in the next one!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=43b96eef62f1" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>