SQL Basics

Setting up the development environment – Introduction to SQL Server Management Studio

Querying Data Using T-SQL

•   Basic database concepts

•   SQL Server data types

•   Retrieving data: SELECT statement

•   Filtering data: WHERE, logical operators, and expressions

•   Organizing retrieved data: ORDER BY, GROUP BY, TOP N

•   Using system-supplied scalar functions

•   Using aggregate (group) functions

•   Multi-table queries: JOINs

•   Using simple subqueries

•   Modifying data using DML statements: INSERT, UPDATE, DELETE

Database Design

Database design

•   Working with data types

•   Designing and implementing tables

Ensuring data integrity through constraints

Table structures and indexi

•   Indexes and SQL statement performance

•   Advanced table structures: partitioning, columnstore indexes

Advanced SQL Techniques & T-SQL Programming

Advanced SQL tools and techniques

•   Advanced subqueries (derived tables, correlated subqueries, EXISTS)

•   Ranking and analytic functions

•   The MERGE statement

•    Transactions and locking mechanisms

Programming with Transact-SQL

•    Using variables

•    Displaying data in the Query and Results panes

•     Control flow: loops and conditions

•     Working with result sets: temporary tables and table variables

Database code objects

•   Designing and implementing views

•   Designing and implementing stored procedures

•   Designing and implementing user-defined functions

•   Additional code objects – overview:

  • Triggers

  • .NET (CLR) integration

  • R language integration

  • XML and JSON support – working with semi-structured data

Data Warehouse Fundamentals

Business Intelligence and Data Warehousing

•  Data warehouse concepts and terminology

•  Planning and managing data warehouse projects

Designing a dimensional model

•  Normalization

•  Star and Snowflake schemas

Advanced data modeling

•   Surrogate keys

•   Snapshots and logs

•   Slowly Changing Dimensions (SCD)

SQL Server Integration Services (SSIS 2016)

Introduction to SQL Server Integration Services (SSIS 2016)

•   ETL architecture overview

•   Microsoft SSIS overview

•   Connection manager levels

•   Basic data flow task transformations

Control Flow

•   Using variables and parameters

•   Control flow tasks

•   Precedence constraints

•   Control flow containers

Debugging and troubleshooting

•   Debugging techniques

•   Logging and event handlers

Deploying and configuring SSIS packages

•   Package deployment

ETL Concepts & Techniques

ETL concepts and techniques

•   ETL process design

•   Staging area

•   Full and incremental loading techniques

•   Data integrity preservation

•   QA and validation

•   ETL administration and monitoring

Power BI

• Introduction to Power BI

  •  Self-Service BI

  •  Tool architecture

  •  Development workflow (end-to-end flow)

• Data ingestion and transformation with Power BI Query Editor

  •  Introduction to Power BI Query Editor

  •  Extracting data from multiple sources: File (Excel, text files),SQL,Folders

  • Transforming data and process automation

  •  Creating a date dimension

  • Automating data preparation: 

    • Table joins

    • Calculated columns

    • Data filtering

    • Splitting and merging columns

    • Data cleansing

  • Introduction to the M language

• Data modeling and measures

  •  Data modeling principles

  •  Modeling methodoloies

  • Star and Snowflake schemas

  • Defining relationships between tables

• Measures and KPIs

  • Measures and KPI definition

  • Introduction to DAX and the impact of the data model on measure design

  •  Developing DAX measures

  •   Time intelligence measures for period-over-period and cumulative analysis

• Reports and dashboards

  •   Report design and development

  •   Best practices for report design

  •   Filters and visualizations

  •   Custom visuals from Microsoft AppSource

  •   Bookmarks, tooltips, and drillthrough

  •   Buttons and interactions

  •  Mobile-optimized design

• Power BI Service (Cloud)

  •  Overview of the Power BI Service

  •  Publishing models to the cloud

  •  Installing and configuring the Gateway

  •  Creating dashboards

  •  Sharing reports and dashboards

  •  Managing user permissions and workspaces

  •  Scheduling refresh and configuring data sources via the Gateway

Internship Project

A structured, mentored capstone project that integrates all previously learned tools and concepts. The project includes working with a data source, extracting and transforming relevant data for a business question, designing a data warehouse and ETL processes, building a data model and Power BI dashboard using advanced visualization techniques. The final deliverable is a business-driven dashboard addressing real organizational needs.

Data Manipulation with Python

Basic Python

• Fundamentals

  • Introduction

  • Python essentials

  • Working environment (IDEs, Jupyter)

• Data types

  •  Numbers

  •  Strings

  •  Booleans

  •  None

• Collections

  •  Lists

  • Tuples

  • Dictionaries

  • Sets

• Control flow

  •  if…else

  • for…in

  •  list comprehensions

  •  while

• Textual interface

  •  input

  •  format

Intermediate Python

• Functions

  • User-defined functions

  • *args and **kwargs

  •  Built-in functions

  •  Lambda expressions

• Working with text files

• Standard library

  •  import

  •  datetime

Python Tools for Data Work

• NumPy

  •  Arrays

  •  Broadcasting

• Matplotlib

  •  Plotting and objects

  •  Seaborn

• Pandas

  •  Series and Index

  •  DataFrame

  • GroupBy

  • Visualizations

• Practice: Use Cases and Exploratory Data Analysis (EDA)

  •  Diamonds dataset

  •  IMDB dataset

  •  San Francisco Crime dataset

  •  Adult US Census dataset

• General tools

  •  Regular expressions (re)

  •  APIs and connecting to data resources (JSON, SQLAlchemy)