SQL Server Roadmap for Data Engineers (7 Days)

7-Day SQL Roadmap for Data Engineers

Dataset Used:

  • dbo.orders
  • dbo.returns
  • dbo.people

Day 1 - SQL Fundamentals

Topics

  • SELECT
  • WHERE
  • ORDER BY
  • DISTINCT
  • TOP
  • GROUP BY
  • HAVING
  • Aggregate Functions

Practice Questions

  1. Total Sales
  2. Total Profit
  3. Total Orders
  4. Orders by Region
  5. Sales by Region
  6. Profit by Category
  7. Top 10 Products by Sales
  8. Customers with Sales > 5000
  9. States with Negative Profit
  10. Average Sales by Category

Day 2 - Joins & Data Modelling

Topics

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN

Practice Questions

  1. Find Returned Orders
  2. Find Non-Returned Orders
  3. Return Percentage by Region
  4. Sales by Regional Manager
  5. Most Returned Products
  6. Regions without Returns
  7. Orders with Manager Information
  8. Sales by Person
  9. Profit by Person
  10. Customer Sales with Return Flag

Day 3 - CTEs & Subqueries

Topics

  • CTE
  • Recursive CTE
  • Scalar Subquery
  • Correlated Subquery

Practice Questions

  1. Customers Above Average Sales
  2. Products Above Category Average
  3. Top Customer per Region
  4. Lowest Profit Product per Category
  5. Monthly Sales Trend
  6. Running Profit
  7. Recursive Calendar Table
  8. Region Ranking
  9. Product Ranking
  10. Top N Products per Category

Day 4 - Window Functions

Topics

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • LEAD()
  • LAG()
  • NTILE()
  • SUM OVER()

Practice Questions

  1. Rank Customers by Sales
  2. Top 5 Customers per Region
  3. Running Sales Total
  4. Previous Month Sales
  5. Month-on-Month Growth
  6. Detect Sales Drops
  7. First Order per Customer
  8. Latest Order per Customer
  9. Top Product by State
  10. Moving Average Sales

Day 5 - Data Engineering SQL

Topics

  • Deduplication
  • Incremental Loads
  • Watermark Logic
  • MERGE
  • Audit Columns

Practice Questions

  1. Remove Duplicate Orders
  2. Latest Record per Customer
  3. Incremental Load by Order Date
  4. Create Watermark Table
  5. Detect Changed Records
  6. Merge Source into Target
  7. Add Audit Columns
  8. Soft Delete Records
  9. Detect Missing Records
  10. Data Reconciliation Report

Day 6 - Data Warehouse SQL

Topics

  • Fact Tables
  • Dimension Tables
  • Star Schema
  • SCD Type 1
  • SCD Type 2

Practice Questions

  1. Create DimCustomer
  2. Create DimProduct
  3. Create FactSales
  4. SCD Type 1 Update
  5. SCD Type 2 Insert
  6. Expire Existing Records
  7. Current Active Customer Record
  8. Historical Customer Lookup
  9. Generate Surrogate Keys
  10. Fact-Dimension Join Query

Day 7 - Senior Data Engineer SQL

Topics

  • Indexing
  • Execution Plans
  • Partitioning
  • Metadata Driven ETL
  • Performance Tuning

Practice Questions

  1. Clustered vs Nonclustered Index
  2. Find Missing Indexes
  3. Analyze Slow Query
  4. Partition Sales Table
  5. Monthly Partition Query
  6. Metadata Driven Load Design
  7. Dynamic SQL Generator
  8. Generic Incremental Load Procedure
  9. Generic Audit Framework
  10. End-to-End ETL Design

Top SQL Interview Topics

Priority Topic
1Window Functions
2CTEs
3Joins
4Incremental Loads
5MERGE
6SCD Type 2
7Deduplication
8Indexes
9Star Schema
10Metadata Driven ETL

Success Criteria

  • Solve all 70 questions using the Superstore dataset.
  • Write every query without looking at answers.
  • Practice CTEs and Window Functions daily.
  • Implement SCD Type 1 and Type 2 using SQL Server.
  • Build a small ETL framework using metadata tables.
  • Be able to explain every query during interviews.

Popular posts from this blog

Exploring the Largest UK Employers: A Power BI Visualization

Image
Understanding employment distribution among top companies can provide valuable insights into industry dominance and workforce trends. In this blog, I analyze the largest employers in the UK using a Power BI table visualization, sourced from CompaniesMarketCap . Source:  CompaniesMarketCap Key Insights from the Data: Compass Group leads the ranking with 550,000 employees, dominating the food service industry. Tesco, the retail giant, follows with 330,000 employees. HSBC, a major player in banking, employs over 215,000 people. The total workforce among the top companies surpasses 1.98 million employees. Visualizing in Power BI: Using a table visualization, we can clearly compare the number of employees across different companies. Power BI’s sorting, aggregation, and filtering features enhance data readability and analysis. However, incorporating bar charts, conditional formatting, and KPIs could make the insights even more compelling. What’s Next? Would you add more interactive eleme...
Read more

Master Databricks Asset Bundles Through Hands-On Practice

15 min read | 100% Practical Guide Forget theory. Forget abstract examples. This is a hands-on, build-as-you-learn guide to mastering YAML through the lens of Databricks Asset Bundles (DABs) . By the end of this post, you'll go from never writing YAML to confidently deploying production-grade data pipelines as code. 🎯 What You'll Build: A complete Databricks workspace configuration including jobs, clusters, notebooks, and permissions—all defined in YAML and deployable with a single command. Level 0: YAML Basics BEGINNER The Golden Rules Rule #1: YAML uses spaces for indentation , never tabs. Standard is 2 spaces per level. Rule #2: YAML is case-sensitive . Name ≠ name Rule #3: Indentation = Structure . It defines parent-child relationships. ...
Read more

6 Common Databricks Mistakes Data Engineers Make (with Practical Fixes)

While working with Databricks, I noticed something important: most production issues don’t come from Spark itself — they come from how Databricks is used . The good news is that many of these problems are preventable with a few solid engineering practices. TL;DR Use job clusters for scheduled workloads Set aggressive auto-termination Use Delta Lake properly (ACID, schema evolution, time travel) Get file sizing + partitioning right Build idempotent pipelines with retries Add monitoring + alerting so systems catch failures early The 6 mistakes (and practical fixes) Below are the mistakes I see most often in Databricks projects — especially when teams move from development to production. Mistake 1: Using all-purpose clusters for scheduled jobs Why it hurts: All-purpose clusters are great for interactive exploration, but scheduled jobs on them often lead to noisy ...
Read more