SQL - DriveDataScience

SQL SET Operations, PIVOT, UNPIVOT, Dynamic SQL, and Cursors: Combining Results, Reshaping Data, and Advanced Patterns

Complete your SQL toolkit. UNION vs UNION ALL vs INTERSECT vs EXCEPT with playlist analogy, SET operations rules, four real-world patterns (multi-source combine, missing records, reconciliation, categorized union). PIVOT and UNPIVOT with the portable CASE WHEN alternative. Dynamic SQL with sp_executesql (safe) vs EXEC (dangerous) and SQL injection warning. Cursors explained and why set-based operations are 100-1000x faster.

SQL SET Operations, PIVOT, UNPIVOT, Dynamic SQL, and Cursors: Combining Results, Reshaping Data, and Advanced Patterns Read More »

SQL Normalization and Star Schema: 1NF, 2NF, 3NF, Dimensional Modeling, and Designing Databases Like a Data Engineer

Leave a Comment / Data Engineering, SQL

Database design from both sides. Normalization: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies) with real examples. Dimensional modeling: star schema with fact tables (measures) and dimension tables (context), snowflake schema, star vs snowflake comparison, surrogate vs natural keys, junk/degenerate/role-playing dimensions, complete star schema SQL, and how it maps to our Medallion Architecture blog posts.

SQL Normalization and Star Schema: 1NF, 2NF, 3NF, Dimensional Modeling, and Designing Databases Like a Data Engineer Read More »

SQL Stored Procedures, Functions, and Triggers: Reusable SQL Logic, Automation, and When to Use Each

Leave a Comment / Data Engineering, SQL

Automate SQL with stored procedures, functions, and triggers. Procedures with input/output parameters, TRY/CATCH error handling, our pipeline logging procedure. Scalar functions and table-valued functions with use cases. AFTER triggers for audit logging, INSTEAD OF triggers for soft deletes, inserted/deleted tables. Procedures vs functions comparison, three real-world patterns, and trigger best practices.

SQL Stored Procedures, Functions, and Triggers: Reusable SQL Logic, Automation, and When to Use Each Read More »

SQL Views, Temp Tables, Table Variables, and CTEs: When to Use Which and Why

Leave a Comment / Data Engineering, SQL

Four intermediate storage options compared. Views (saved queries, security layer, updatable), temp tables (session-level, indexable, large data), table variables (batch-level, small data, no statistics), CTEs (single query, readability). Complete comparison table, decision tree, materialized/indexed views, three production patterns (staging with temp table, security with views, CTE for readability), and the table variable statistics trap.

SQL Views, Temp Tables, Table Variables, and CTEs: When to Use Which and Why Read More »

SQL Indexes and Execution Plans: How Databases Find Data, Why Queries Are Slow, and How to Fix Them

Leave a Comment / Data Engineering, SQL

Master SQL performance with the book index analogy. Table scan vs index seek, clustered vs non-clustered indexes, composite indexes with leftmost prefix rule, covering indexes with INCLUDE, reading execution plans, five common slow query patterns with fixes (missing index, function on column, leading wildcard, implicit conversion, SELECT *), index fragmentation and rebuild, and the index design checklist.

SQL Indexes and Execution Plans: How Databases Find Data, Why Queries Are Slow, and How to Fix Them Read More »

SQL DDL, DML, and Constraints: CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, MERGE, and Database Design Fundamentals

Leave a Comment / Data Engineering, SQL

The complete DDL, DML, and constraints guide. CREATE TABLE with all data types, every constraint explained (PK, FK, UNIQUE, CHECK, DEFAULT, NOT NULL, composite keys), CASCADE options, ALTER TABLE, DROP vs TRUNCATE vs DELETE comparison, INSERT (single, multi, from SELECT), UPDATE with joins, DELETE with subqueries, MERGE for upsert with three clauses, IDENTITY auto-increment, SELECT INTO, and schema design best practices.

SQL DDL, DML, and Constraints: CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, MERGE, and Database Design Fundamentals Read More »

SQL Functions Every Data Engineer Must Know: String, Date, Numeric, Null Handling, Conversion, and Conditional Functions

Leave a Comment / Data Engineering, SQL

The complete SQL function reference for data engineers. 50+ functions organized by category: string (TRIM, CONCAT, REPLACE, SUBSTRING, STUFF, STRING_AGG), date (DATEDIFF, DATEADD, DATEPART, EOMONTH, FORMAT), numeric (ROUND, CEILING, FLOOR, ABS, MOD), null handling (COALESCE, NULLIF, ISNULL, IS DISTINCT FROM), conversion (CAST, TRY_CAST, CONVERT), and conditional (IIF, CASE). Includes SQL Server vs PostgreSQL vs MySQL comparison table and complete data cleaning pipeline.

SQL Functions Every Data Engineer Must Know: String, Date, Numeric, Null Handling, Conversion, and Conditional Functions Read More »

Artificial Intelligence and Machine Learning for Data Engineers: What It Actually Is, How Companies Use It, and the Complete Introduction Before You Touch an Algorithm

Leave a Comment / Data Engineering, SQL

The complete AI and ML introduction for data engineers — not hype, reality. AI vs ML vs DL vs GenAI hierarchy, supervised vs unsupervised vs reinforcement learning, classification vs regression with decision framework, every traditional ML algorithm and deep learning algorithm with analogies, real-world ML use cases across 6 industries, the ML project lifecycle, where data engineers fit, feature engineering as the bridge, and the complete learning path forward.

Artificial Intelligence and Machine Learning for Data Engineers: What It Actually Is, How Companies Use It, and the Complete Introduction Before You Touch an Algorithm Read More »

SQL Subqueries, Correlated Subqueries, EXISTS, and Joins vs Subqueries: When to Use Which and Why Performance Matters

Leave a Comment / Data Engineering, SQL

Master all subquery types with the research analogy. WHERE/FROM/SELECT subqueries, correlated subqueries with step-by-step row execution, EXISTS and NOT EXISTS, the same question solved five ways (JOIN, IN, EXISTS, derived table, CTE), performance comparison table, decision tree, subqueries in INSERT/UPDATE/DELETE, and five real-world patterns.

SQL Subqueries, Correlated Subqueries, EXISTS, and Joins vs Subqueries: When to Use Which and Why Performance Matters Read More »

SQL GROUP BY, Aggregations, HAVING, CASE WHEN, and Null Handling: The Complete Guide with Real-Life Analogies

Leave a Comment / Data Engineering, SQL

Master SQL aggregations with the post office analogy. GROUP BY rules, COUNT/SUM/AVG/MIN/MAX with NULL behavior, WHERE vs HAVING, CASE WHEN in SELECT/WHERE/ORDER BY/aggregations (pivot pattern), COALESCE, NULLIF, division by zero protection, aliases and scope, STRING_AGG, and conditional aggregation crosstab.

SQL GROUP BY, Aggregations, HAVING, CASE WHEN, and Null Handling: The Complete Guide with Real-Life Analogies Read More »