Schema-on-Read: Store Raw Data, Apply Structure Later

Schema-on-Write - Pause a minute and think if you knew about this concept ? Most probably the answer is No until ..... I tell you about it. Schema-on-Write simply means - Define the structure before storing data. Every incoming record must follow - - Fixed columns - Fixed data types - Defined rules If it doesn’t - Record gets rejected or transformed. #Example - You have a table with below columns and input data : Order_ID | Amount | Order_Date - (101, 500, "2026-04-01") - Stored - (102, "Five Hundred", "April 1") - Rejected Before storing the 2nd record it should be cleaned : - "Five Hundred" - 500 - "April 1" → proper date format This approach keeps your data clean and consistent from the beginning. Because of this, queries run faster and reports are more reliable. But it also means less flexibility when source data changes. Follow Manish Kumar Singh for more such insights #dataengineering #snowflake #etl #sql #linkedin

Day 21/30 of SQL Challenge Today I applied everything I learned about JOINs into a small real-world query. Topic: Mini Project using JOIN + Aggregation Problem: Find: * Customer name * Total number of orders * Last order date Query: SELECT c.name, COUNT(o.id) AS total_orders, MAX(o.order_date) AS last_order_date FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name; Explanation: * LEFT JOIN ensures all customers are included (even those with no orders) * COUNT(o.id) calculates total orders per customer * MAX(o.order_date) finds the most recent order * GROUP BY combines results per customer Key understanding: This query combines multiple concepts: * JOIN to connect tables * Aggregation to summarize data * GROUP BY to organize results Practical thinking: This type of query is very common in real systems: * Customer activity tracking * Business reporting * User behavior analysis Important note: Using LEFT JOIN instead of INNER JOIN helps include customers with zero orders, which can be important for analysis. Reflection: Today felt like a real-world use case not just learning concepts, but solving an actual business problem using SQL. #SQL #LearningInPublic #Data #BackendDevelopment #SQLPractice #BuildInPublic

Database Normalization Stop repeating data! Database normalization is the process of organizing tables to reduce redundancy and improve data integrity. Instead of one massive, messy spreadsheet, you split data into logical pieces. Example: ❌ Bad: Storing the user's address inside every single "Order" row. ✅ Good: A separate Users table and Orders table, linked by a UserID. Benefits: 🔹 Saves storage space 🔹 Prevents update anomalies 🔹 Keeps your data clean and scalable Are you normalizing your schemas or keeping it all in one flat file? Let’s discuss! 👇 #DatabaseDesign #SQL #DataEngineering #CodingTips

Practiced SQL Joins today using a sample orders dataset. Created separate customer, product, and orders tables, then used joins to combine them and analyze the data more effectively. This is helping me understand how raw data can be structured and queried in a more practical way. #SQL #Joins #DataAnalytics #DataAnalyst #LearningSQL

🚀 How I Boosted SQL Performance Using Table Partitioning Recently, I worked on optimizing a large transactional table in our system that was growing rapidly 📈 🔍 The Problem - Table had millions of records (continuously increasing) - Queries using date filters were becoming very slow ⏳ - Index rebuild & maintenance operations were taking too much time - Data archival was difficult 💡 The Solution – Table Partitioning Instead of storing all data in one large table, I divided it into smaller logical partitions based on date I used: ✔️ Partition Function → Defines how data is split (monthly range) ✔️ Partition Scheme → Maps partitions to storage (filegroups) ✔️ Staging Table → For efficient data loading & switching ⚙️ How I Implemented It 1. Created a Partition Function on a date column (monthly split) 2. Designed a Partition Scheme to organize partitions 3. Inserted new data into a Staging Table 4. Used Partition Switching to move data instantly ⚡ (no heavy insert load) 📊 Results / Impact - Query performance improved significantly (especially date filters) - Faster index maintenance - Easy data archival (old partitions can be managed separately) - Better scalability for future data growth 🧠 Key Learning Partitioning is not just about splitting tables — it’s about handling large-scale data smartly and efficiently 💬 If you're working with large datasets, this approach can be a game changer! #SQLServer #DatabaseOptimization #PerformanceTuning #DataEngineering #Backend

Why Your SQL Query Is Slow — Even When It Looks Correct I was working on a query to analyze sales data. The logic was simple. But the query was extremely slow. The issue wasn’t complexity. It was how the query was written. What I initially did: Used multiple JOINs on large tables Selected all columns (SELECT *) Applied filters at the end Result: full table scan + slow execution What was actually wrong: Too much unnecessary data being processed No early filtering Joining before reducing dataset What I changed: Applied filters early (WHERE clause before JOIN impact) Selected only required columns Aggregated data before joining large tables Checked execution plan Key insight: SQL performance is not about writing queries that work — it’s about writing queries that scale If your query is slow: 👉 Don’t just optimize syntax 👉 Reduce the data being processed #SQL #DataAnalytics #DataEngineering  #QueryOptimization #Database  #AnalyticsEngineering #SQLPerformance

In today’s data-driven world, ETL (Extract, Transform, Load) is the backbone of effective analytics and reporting. Whether you’re building a data warehouse or integrating multiple data sources, SQL Server offers powerful tools to make ETL seamless and efficient.

---  Querying a Data Vault is not hard if you understand it from the beginning.  The problem is — most people see 50+ tables and panic.  So I built an interactive schema from a real FMCG pipeline. Click any table — see the dbt model. Click any relationship line — see the join SQL.  Hub to satellite — join on hash key.  Hub to link — a link connects two or more hubs through their hash keys.  3 patterns across the entire model. Once you learn them on day one, you can query any Data Vault — even one you've never seen before.  Built with dbt + automate_dv on PostgreSQL.  👉 https://lnkd.in/ew3-VnzP  #datavault #dbt #dataengineering #datawarehouse

𝟏𝟎𝟎 𝐄𝐬𝐬𝐞𝐧𝐭𝐢𝐚𝐥 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬 𝐂𝐡𝐞𝐚𝐭 𝐒𝐡𝐞𝐞𝐭! . . . . Whether you're a complete beginner or just need a quick reference, this guide is packed with the core queries you need for everything from data cleaning to advanced analysis. #SQL #DataEngineering #DataAnalytics #BigData #ETL #DataEngineer #SQLInterview #Database #CloudData #Snowflake #DataScience #TechCareers #LearningSQL #DataWarehouse #CareerGrowth

Day 16/30 of SQL Challenge Today I learned: LEFT JOIN After understanding INNER JOIN, I realized it only shows matching data. But in real-world scenarios, we often need to see all records from one table-even if there is no match in the other table. Concept: LEFT JOIN returns all records from the left table, and the matching records from the right table. If there is no match, NULL values are returned for the right table columns. Basic syntax: SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column; Example: SELECT customers.name, orders.id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id; Explanation: * All customers are included * If a customer has orders, those are shown * If a customer has no orders, order columns will be NULL Key understanding: LEFT JOIN helps identify missing relationships in data. Practical use cases: * Finding customers who have not placed any orders * Identifying unmatched records * Data completeness checks Extended example (finding customers with no orders): SELECT customers.name FROM customers LEFT JOIN orders ON customers.id = orders.customer_id WHERE orders.id IS NULL; This returns customers who never placed an order. Reflection: Today helped me understand that missing data is also important in analysis-not just the existing matches. #SQL #LearningInPublic #Data #BackendDevelopment #SQLPractice #BuildInPublic