SSIS LIVE Online Training (Integration Services with ETL & DWH)

SSIS  provides Data Extraction, Consolidation and Loading Options (ETL), SQL Server Coding enhancements, Data Warehousing and Customizations for SQL Server Developers, Administrators, BI Engineers and Data Analysts. In our SSIS Training course, we deal with complete SSIS Design, Development and Deployment & Management of Various ETL Transformations, Data Loads, Data Cleansing, Incremental Data Loading Techniques including DWH Design, Management, SCD, Profiling, Tuning and Fact Table Loads. This SSIS Online Training course also includes Azure Data Factory (ADF) Pipelines and SSIS Package Migrations to ADF with Self Hosted and Integrtion Runtime ENgines, Stored Procedures Executions, Watermark Columns and Azure Data Lake with SSIS Integrations. All sessions are completely practical and realtime.

Pre-requisites: Participant needs to have knowledge on  SQL Server T-SQL Queries  to join our Real-time Practical Data Modelling (SSAS) Training Course.

SSIS (Integration Services) Training Plans

  PLAN A PLAN B PLAN C
Description SSIS T-SQL & SSIS T-SQL & SSIS, ADF
Total Duration 2.5 Weeks 5.5 Weeks 8.5 Weeks
SSIS: Control Flow, Data Flow; SSIS DB
SSIS: Static & Dynamic Connections
SSIS: ETL, Data Warehouse Design
SSIS: Transformations, Data Loads
SSIS: Data Cleansing, Lookups
SSIS: Catalog Databases, Usage
SSIS: Deployments, Migrations
SSIS : Star & Snowflake Schemas
SQL : Basics, DDL, DML, SELECT
T-SQL : Constraints, Normal Forms
T-SQL: Joins, Group By, Queries
T-SQL: Merge, Pivot, Date & Time Formats
T-SQL: Excel Integrations, Pivot Charts
ADF : Azure Data Factory
ADF : Data Imports, ETL
ADF : Data Flows, Wrangling
ADF : Transformations, ETL
Synapse: Configuration, Loads
Synapse: ETL with ADF, DWH
Synapse: Performance Tuning
Synapse: MPP, cDWH, DIUs

Trainer: Mr. Sai Phanindra T

SQL Server & T-SQL Schedules
S No Time (IST, Mon - Fri) Start Date  
1 6 AM - 7 AM June 27th Register
2 8 PM - 9 PM July 12th Register
3 9 PM - 10 PM June 8th Register
4 11 PM - 12 AM June 19th Register
SSIS Training Schedules
S No Time (IST, Mon - Fri) Start Date  
5 8:30 AM - 9:30 AM June 7th Register
6 8:30 PM - 9:30 PM June 21st Register

If above schedule does not work, opt for SSIS Training Videos

 

SSIS TRAINING HIGHLIGHTS :

✔ Basic to Adv. ETL ✔ Basic to Adv. DAX
✔ Data Modelling ✔ OLAP DB Design
✔ Power Query ✔ Basic to Adv. MDX
✔ Kimball BI Design ✔ Inmon BI Design
✔ Star, Snowflake ✔ Data Warehousing
✔ OLTP, File Data ✔ JSON, XML Data
✔ Resume Guidance ✔ MCSA MSBI Exam

All Session Are Completely Practical & Real Time

SSIS LIVE Online Training Course Contents:

Ch 1: DATABASE INTRODUCTION

  • Data, Databases and RDBMS Software
  • Database Types : OLTP, DWH, OLAP
  • Microsoft SQL Server Advantages, Use
  • Versions and Editions of SQL Server
  • SQL : Purpose, Real-time Usage Options
  • SQL Versus Microsoft T-SQL [MSSQL]
  • Microsoft SQL Server - Career Options
  • SQL Server Components and Usage
  • Database Engine Component and OLTP
  • BI Components, Data Science Components
  • ETL, MSBI and Power BI Components
  • Course Plan, Concepts, Resume, Project
  • 24 x 7 Online Lab for Remote DB Access
  • Software Installation Pre-Requisites

Ch 5: SQL Basics - 3, T-SQL INTRO

  • Database Objects : Tables and Schemas
  • Schemas : Group Tables in Database
  • Schemas : Security Management Object
  • Creating Schemas & Batch Concept
  • Using Schemas for Table Creation
  • Data Storage in Tables with Schemas
  • Data Retreival and Usage with Schemas
  • Table Migrations across Schemas
  • Import and Export Wizard in SSMS
  • Data Imports with Excel File Data
  • Performing Bulk Operations in SSMS
  • Temporary Tables : Real-time Use
  • Local and Global Temporary Tables
  • # and ## Prefix, Scope of Usage

Ch 9: Triggers & Transactions

  • Triggers - Purpose, Real-world Usage
  • FOR/AFTER Triggers - Real time Use
  • INSTEAD OF Triggers - Real time Use
  • INSERTED, DELETED Memory Tables
  • Using Triggers for Data Replication
  • Enable Triggers and Disable Triggers
  • Database Level, Server Level Triggers
  • Transactions : Types, ACID Properties
  • Transaction Types and AutoCommit
  • EXPLICIT & IMPLICIT Transactions
  • COMMIT and ROLLBACK Statements
  • Open Transaction Scenarios & Cause
  • Query Blocking Scenarios @ Real-time
  • NOLOCK and READPAST Lock Hints

Ch 2: SQL SERVER INSTALLATIONS

  • System Configuration Checker Tool
  • Versions and Editions of SQL Server
  • SQL Server and SSMS Installation Plan
  • SQL Server Pre-requisites : S/W, H/W
  • SQL Server 2022 / 2019 Installation
  • SQL Server 2017 Installation
  • Instance Name and Server Features 
  • Instances : Types and Properties
  • Default Instance, Named Instances
  • Port Numbers, Instance Differences
  • Service and Service Account Use
  • Authentication Modes and Logins
  • Windows Logins and SQL Logins
  • FileStream and Collation Properties

Ch 6 : CONSTRAINTS, INDEXES

  • Constraints and Keys - Data Integrity
  • NULL, NOT NULL Property on Tables
  • UNIQUE KEY Constraints: Importance
  • PRIMARY KEY Constraint: Importance
  • FOREIGN KEY Constraint: Importance
  • REFERENCES, CHECK and DEFAULT
  • Candidate Keys and Identity Property 
  • Database Diagrams and ER Models
  • Relationships Verification and Links
  • Indexes : Basic Types and Creation
  • Index Sorting and Search Advantages
  • Clustered and NonClustered Indexes
  • Primary Key and Unique Key Indexes
  • Need for Indexes - working with Keys

Ch 10: DB Architecture & Group By

  • Database Architecture: Data Files
  • Database Architecture: Log Files
  • Primary and Secondary Data Files
  • Log Files Creation and Realtime Use
  • Creating and Using Filegroups
  • Linking Tables with Filegroups
  • Scripting Database and Objects
  • GROUP BY: Importance, Realtime Use
  • GROUP BY Queries and Aggregations
  • Group By Queries with Having Clause
  • Group By Queries with Where Clause
  • Using WHERE and HAVING in T-SQL
  • Using Group By in Data Audits
  • Using Group By with Joins, Audits

Ch 3: SSMS Tool, SQL BASICS - 1

  • SQL Server Management Studio
  • Local and Remote Connections
  • System Databases: Master and Model
  • MSDB, TempDB, Resource Databases
  • Creating Databases : Files [MDF, LDF]
  • Creating Tables in User Interface
  • Data Insertion & Storage. Limitations 
  • SQL : Purpose and Real-time Usage
  • SQL Versus T-SQL : Basic Differences
  • DDL, DML, SELECT, DCL and TCL
  • Creating Tables using SQL Scripts
  • Data Storage, Inserts - Basic Level
  • Table Data Verifications with Select
  • SELECT Statement for Table Retrieval

Ch 7: JOINS & LINKED SERVERS

  • JOINS - Table Comparisons Queries
  • INNER JOINS For Matching Data
  • OUTER JOINS For (non) Match Data
  • Left Outer Joins with Example Queries
  • Right Outer Joins with Example Queries
  • FULL Outer Joins - Realtime Scenarios
  • Join Queries with "ON" Conditions 
  • CROSS JOIN and CROSS APPLY
  • One-way, Two way Data Comparisons
  • Linked Servers Configurations
  • Linked Servers: RPC Settings & Tests
  • Linked Servers, Remote Joins in TSQL
  • Multi Server Connections, DB Access
  • 2 Part, 3 Part, 4 Part Name Conventions

Ch 11: TSQL Queries: Group By, Joins

  • Joins with Group By Queries in TSQL
  • Joining 3 Tables with Group By
  • Joining  4 Tables with Group By
  • Multi Table Joins with Table Aliases
  • Table & Column Aliases with Joins
  • Joins with HAVING Conditions
  • Joins with WHERE & Aggregations
  • Joins with Sub Queries, Formatting
  • Joins with IIF() Function, Conditions
  • Joins with CASE Statement Conditions
  • UNION and UNION ALL Operator
  • Storing Queries in Database Views
  • Office Data Connections, Excel Reports
  • Manual Data Refresh & Reports

Ch 4: SQL BASICS - 2

  • Creating Databases & Tables in SSMS
  • Single Row Inserts, Multi Row Inserts
  • Rules for Data Insertion Statements
  • SELECT Statement @ Data Retrieval
  • SELECT with WHERE Conditions
  • Batch Concept and Go Statement
  • AND and OR Operators Usage
  • IN Operator and NOT IN Operator
  • Between, Not Between Operators
  • LIKE and NOT LIKE Operators
  • UPDATE Statement & Conditions
  • DELETE & TRUNCATE Statements
  • Logged and Non-Logged Operations
  • ADD, ALTER and DROP Columns

Ch 8: VIEW, SPs, FUNCTIONS BASICS

  • Database Objects: Overview & Usage
  • Views: Types, Usage in Real-time
  • System Predefined Views and Audits
  • Listing Databases, Tables, Schemas
  • Functions: Types, Usage in Real-time
  • System Predefined Functions, Audits
  • DBId, DBName, ObjectID, ObjectName
  • Using Parameters in SQL Server
  • Dynamic Joins for Database Audits
  • Procedures: Types, Usage in Real-time
  • User & System Predefined Procedures
  • Parameters and Dynamic SQL Queries
  • Sp_help, Sp_helpdb and sp_helptext
  • sp_pkeys, sp_rename and sp_help

Ch 12 : ER MODELS, NORMAL FORMS

  • First Normal Form and Atomicity
  • Second Normal Form, Candidate Keys
  • 3rd Normal Form Multi Value Dependency
  • Boycee-Codd Normal Form : BNCF
  • Fourth Normal Form Realtime Advantages
  • Self Reference Keys and 4 NF Usage
  • 1:1, 1:M, M:1, M:M Relationship Types
  • MERGE Statement - Comparing Tables
  • WHEN MATCHED and NOT MATCHED
  • Incremental Load with MERGE Statement
  • UPSERT Operations with MERGE
  • Stored Procedures for Merge Statement
  • DML Operations with ON Keyword
  • Tuning / Optmizing Merge Statement
Case Study 1 -
For Database, Table Design (Constraints, Keys)
Case Study 2 -
For Query Writing + Window Functions
(Rank, RowNumber)

Module I: Basic SSIS Development & ETL - DWH

Module II: Advanced SSIS Development & Deployments

Ch 1: SSIS INTRO, INSTALLATION

  • Integration Services (SSIS) & ETL / DWH
  • SSIS for Data Loads, ETL, Warehouse
  • SSDT : SQL Server Data Tools
  • SSIS Development, LIVE (Deployment)
  • Data Warehouse Design & ETL Process
  • DWH and ETL Structures Implementation
  • SSIS ETL for Data Reads, Data Cleansing
  • Data Warehouse (DWH) Design Principles
  • SSIS 2019, 2017 : SSIS DB Installations
  • SSIS Database, Catalog Folders, Storage
  • SSIS Catalog Database (SSIS DB) Creation
  • SQL Server Data Tools - SSDT / Visual Studio
  • SSDT Installation and Catalog Verification
  • SSIS, ETL, DWH, Data Flow, Data Buffer
  • SSIS Package Environment, SSDT Projects
  • SSIS & ETL Training - Lab Plan, Resources

Ch 7: Checksum & DWH Design

  • Checksum Transformation in ETL Loads
  • Configuring Checksum: SSIS 2019, 2017
  • Transformation Logic, Parity Checks CRC
  • Checksum For Type I, Type II ETL DWH
  • DWH Dimension Tables With Checksum
  • Lookup Transformation, Row Redirection
  • OLE DB Command and Input Parameters
  • Parameter Mapping, Dynamic Updates
  • Cache Transformation CAW Memory Files
  • Memory Connection Lookup with Cache
  • Tuning Lookup: Caching, Index Options
  • Pre-ETL Activities, DB Recovery Models
  • FULL/ PARTIAL CACHE & NOCACHE
  • Performance Tuning and Pre-ETL Loads
  • Dependent Data Flow Tasks, Post ETL
  • Internal Parameters and Query Updates
  • Cache Allocation Options with ETL DWH

Ch 2: SSIS ETL PACKAGES: BASICS

  • Control Flow Tasks Architecture, Purpose
  • Data Flow Tasks Architecture, Purpose
  • SSIS Packages @ Basic Data Flow, ETL
  • SSIS Projects and Package Creation
  • Data Pipelines in Data Flow Tasks
  • SSIS Packages Execution Process
  • Data Flow Objects, OLE DB Connections
  • SSIS Package Creation - Control Flow
  • DTSX Files for Package Execution
  • SSIS Execution, Package Errors & Logs
  • SSIS Transformation: Conditional Split
  • Excel Connection and Memory Reference
  • Source and Destination Assistants
  • DAT File Imports and Annotations
  • SSIS Project Configurations, Debugging
  • SSIS 64 Bit, 32 Bit Configurations

Ch 8: CDC Transformations for DWH

  • DML Audits with Change Data Capture
  • CDC Tables with SQL Server Connections
  • CDC Connections & ADO.NET Integration
  • CDC Control Flow and CDC State Values
  • INITIAL LOADS & PRCESSING RANGE
  • State Variables, Net Changes, Logging
  • Initial & Incremental Dimension Loads
  • Dynamic CDC Control, OLEDB Command
  • Internal Parameters and Usage Options
  • Parameter Mapping For ETL Type1, Type2
  • Integrating Control Flow for CDC @ ETL
  • CDC Splitter - Row Inserts, Updates
  • CDC Precautions, Input & Output Range
  • Derived Column Transformations with CDC
  • Limitations of ADO.NET Connections
  • Master Child Packages,Parameter Binding
  • Package Passwords, Project Parameters
  • Project Configuration Options(32,64 bit)

Ch 3: MERGE & FUZZY LOOKUP

  • MERGE and UNION ALL Transformations
  • SORT, NOSORT and Advanced Sort
  • Synchronous, Asynchronous Tfns
  • Row, Partial Blocking Transformations
  • Fully Blocking Transformations - Buffers
  • Avoiding Fully Blocking Transformation
  • Bulk Load Operations, SSIS Data Imports
  • IsSorted & SortKey Position Options
  • SSIS Package Performance & Resources
  • Data Conversion Expressions
  • Fuzzy Lookup Transformation, References
  • Nomatch Cleansing @ Conditional Split
  • Index Creations, Lookup Transformation
  • Data Conversion, Derived Columns
  • Varchar, Nvarchar, Error Redirections
  • Threshold Values, Search Delimiters
  • _Similarity, _Confidence Columns

Ch 9: Fact Table Design, DWH Loads

  • Fact Table - Design and ER Model
  • DWH : STAR & SNOWFLAKE Schemas
  • Time Dimensions and ETL Date / Time
  • Link Time Dimension to Facts, Lookups
  • Parent-Child Packages for Fact Loads
  • Inferred Members for NULL Dimensions
  • SCD Wizard for DWH Fact Table Design
  • Parameter Mapping for Incr Updates
  • ETL Load IDs - Dimension Attributes
  • Error Handling, Event Handling in SSIS
  • Text Qualifiers with Flat File Sources
  • Fact Load Design for Initial, Incr Loads
  • End-to-End DWH Design Implementation
  • Direct Data Loads and Staging Tables
  • Fact Table Staging and Incr Updates

Ch 4: SSIS CHECKPOINT & PIVOT

  • Execute SQL Task and OLE DB Queries
  • Transaction Options For SSIS Executables
  • Precedence - Success/Failure/Completion
  • SSIS Package Rollbacks Execution Options
  • Checkpoints Purpose with Data Flow Tasks
  • Checkpoint Files and SSIS Logging Tasks
  • Transactions with Checkpoint File in SSIS
  • Checkpoint Option Advantages, Limitations
  • FailPackageOnFailure, Checkpoint Property
  • REQUIRED/SUPPORT/ NOTSUPPORTED
  • Transaction Property, CHECKPOINT Files
  • Legacy Data, Data Cleansing, Formatting
  • Denormalization, Keys. Need for OLTP
  • PIVOT Transformation, Connection Assistant
  • Pivot Usage - Implementation. Key Values
  • Lineage ID - Purpose. Data Mappings
  • Lineage IDs for Column Mapping, Pivot Keys
  • SSIS Input Columns and Mappings
  • Data Viewer : Data Transfer Verification
  • Data Type Conversions, Error Redirection

Ch 10: DWH MIGRATIONS, SCRIPT Task

  • DataWarehouse Migrations with SSIS
  • Using SSIS Containers, Db Integrity Task
  • Pre-Database Migration Task Precautions
  • Online and Offline DB Transfer Options
  • Copy / Move with DWH Migrations
  • SMTP : Simple Mail Transfer Protocol
  • SQL Server Agent and Package Events
  • Data Profiling with ADO.NET Connectors
  • XML Files & SSIS Data Profiler Tool
  • Script Task - Working in SSIS Control Flow
  • Script Task - VB.NET Program Compilations
  • Variables, Parameters with SSIS Script Task
  • Read Only, Read Write Variable Expressions
  • Expressions and Debugging, Break Points
  • Variables, Parameters Mapping Expressions
  • File System Tasks and Limitations
  • SQLDataAdapters & System.Data.SQLClient

Ch 5: EVENTS, LOOPS, EXPRESSIONS

  • SSIS Package Events, Validation, Execution
  • PreExecution, Progress, Cleanup Events
  • SSIS Events, Errors/Warnings/Information
  • Configuring sysssislog System Tables
  • Debugging : Data Viewers and Breakpoints
  • ForEach Loop Container. File Connections
  • Variables For Linking DFT, Control Flow
  • Dynamic Connections with Variables
  • Iterations, Fetch, Index Mapping
  • SSIS Expressions for ETL and DWH
  • FOR LOOP Expressions in SSIS
  • Init/EvalExpression, AssignExpression
  • SSIS Expressions, Functions, Values
  • Data Insertions, Data Serializations
  • Counter Values, Variables & Parameters
  • SSIS for OS Level Operations, Loops
  • Execute SQL Task : Return Values

Ch 11: SSIS DEPLOYMENTS, UPGRADES

  • SSISDB Catalog Deployments, ISPAC Files
  • Package Builds, Verification, Scripts
  • Project Deployment Wizard Targets, Logs
  • DB Catalog Folders & Projects Creation
  • Package Executions - Scripts, Reports
  • Package Validations, 32/64 bit Options
  • Configurations & Parameter Management
  • Package Jobs @ SQL Agent. Job Steps
  • Job Schedules and Notifications Event Logs
  • Package Security - SSISDB Logins, Users
  • Folder Level and Project Level Security
  • Project Migration Utilities, Upgrades
  • Package Imports, Exports with ISPAC Files
  • Command-Line Deployment,Execution Utility
  • Package Execution & Validation Reports
  • Package Versions and Restores, Rollbacks

Ch 6: SSIS with ETL, Warehouse (DWH)

  • OLTP Database : Historical Data Loads
  • Data Warehouse (DWH) Purpose, Usage
  • Dimensions, Attributes, Members Types
  • Dimension Tables, Fact Tables Design
  • TYPE1 and TYPE2 ETL Implementation
  • SCD Type1, Type 2 for DWH in Sales
  • Inferred Members and Legacy Loads
  • Initial Data Loads with Data Marts
  • Business Keys & non Identity Columns
  • Surrogate Keys, Alternate Business Keys
  • Cascading OLTP / Stage to DWH Rows
  • Fixed Attributes, Changing Attributes
  • Historical Attributes. Inferred Updates
  • ETL Date, Row Status Transformations
  • Attribute Key Types in SCD, Limitations
  • Historical Attributes and Data Delta
  • SSIS Connection Assistants - Reuse
  • SCD Transformations in Real-time

Ch 12: SSIS PROJECT [ECommerce Domain]

  • SSIS ETL & DWH with STAR Schema
  • SSIS ETL & DWH with SNOWFLAKE Schema
  • Dimension Table Design & Business Keys
  • Fact Table Design & Data Integrity
  • Incremental Data Loads with Containers
  • Key Lookup and Business Key Identification
  • Staging Tables, Master - Child Packages
  • SSIS Package Deployment [Catalog Deployments]
  • SSIS Training : Project Specifications
  • Project Explanation and Solution
  • Project FAQs and Resume Support
  • Chapter Wise FAQs and Answers
  • Concept Wise Tasks and Solutions
  • SSIS DWH Databases with Reports
  • Technical Resume Points in SSIS
  • SSIS Limitations and Need for ADF
  • End to End Implementation [MSBI]
* Above curriculum applicable for registrations from Feb 10th, 2020

Module 1

Module 2

Module 3

Chapter 1: Azure Basics, Azure SQL DB

  • Cloud Introduction and Azure Basics
  • Azure Implementation: IaaS, PaaS, SaaS
  • Benefits of Azure Cloud Environment
  • Azure Data Engineer: Job Roles
  • Azure Storage Components
  • Azure ETL & Streaming Components
  • Need for Azure Data Factory (ADF)
  • Need for Azure Synapse Analytics
  • Azure Resources and Resource Types
  • Resource Groups in Azure Portal
  • Azure SQL Server [Logical Server]
  • Firewall Rules and Azure Services
  • Connections with SSMS & ADS Tools
  • DB Migration: OnPremise to Azure
  • Schema Migration & Data Migration

Chapter 5: OnPremise Data with ADF

  • On-Premise Data Sources with Azure
  • Self Hosted Integration Runtime (IR)
  • Access Keys, Remote Linked Services
  • Synapse SQL Pool (DW) with OnPremise
  • Staged Data Copy and Performance
  • Pipeline Executions and Monitoring
  • Pipeline RunIDs and Audits / Tracing
  • Incompatible Rows Skips, Fault Tolerance
  • Incremental Loads with Files (BLOB)
  • Pipeline Executions and Schedules
  • Regular Schedules and Tumbling Window
  • Execution Retry and Delay Options
  • Binary Copy, Last Modified Date in Blob
  • Automated Loops and Trigger Schedules
  • Incremental Loads Verification Tests

Chapter 9: Synapse Analytics with Spark

  • Apache Spark Pool in Azure Synapse
  • Spark Cluster Nodes: Vcores, Memory
  • Creating Spark Clusters @ Synapse Studio
  • Python Notebooks For Remote Access
  • Creating Databases in Apache Spark Pool
  • Data Loads from Dedicated SQL Pools
  • Table Creations, Aggregation Operations
  • PySpark Code for Data Operations, Writes
  • Serverless Pool in Azure Synapse
  • Connections, Usage with Serverless Pool
  • Using Azure OpenDatasets in Synapse
  • OPENROWSET and BULK Data Loads
  • Azure Storage Account : Data Analysis
  • Working with Parquet Files in Synapse
  • Python Notebooks (Pyspark) in Synapse

Chapter 2: Synapse SQL Pools (DWH)

  • Dedicated SQL Pools in Azure
  • Enterprise Data Warehouse with Synapse
  • DWU: Data Warehouse Units, Resources
  • Massively Parallel Processing (MPP)
  • Control Nodes and Compute Nodes
  • SQL Pool Access from SSMS Tool
  • T-SQL Queries @ SQL Pools
  • Start/Resume/Pause, Scaling Options
  • Creating Tables in Azure SQL Pool
  • Compression, MAX DOP & Indexes
  • Distributions: Round Robin, Hash
  • Distributions: Replicate and Usage
  • Data Imports with COPY Table
  • Dynamic Views (DMV) with PDW
  • Data Loads Monitoring, Resource Class

Chapter 6: ADF Data Flow - 1

  • Limitations with Copy Data Tool
  • Data Flow Task, Data Flow Activity
  • Transformations with Data Flow
  • Spark Cluster For Debugging
  • Cluster Node Configurations
  • Data Preview Options with DFT
  • SELECT Transformation & Options
  • JOIN Transformation and Usage
  • Conditional Split Transformation
  • Aggregate & Group By Transformations
  • Synapse Sink Options with DFT
  • DFT Optimization Techniques
  • Pipeline Debug Runs and ETL Testing
  • Spark Cluster For Pipeline Executions
  • Pipeline Monitoring & Run IDs

Chapter 10: Incremental Loads @ Synapse

  • Incremental Loads with Synapse Studio
  • Multi Table Merge Operations
  • On-Premise Data Sources & Timestamps
  • Azure SQL DB Destinations, Watermarks
  • Watermark Table Usage & Audits
  • Stored Procedures for Timestamp Updates
  • Table Data Type and Dynamic MERGE
  • SQL Queries for Datasets and Fetch
  • Lookup Activity and its Usage un Synapse
  • Expressions in ADF Portal for Lookup
  • Expressions in ADF Portal for Source
  • Output Pipeline Expression, Data Window
  • Concat Function, Run IDs Expressions
  • JSON Parameters, Pipeline Scheduling
  • Pipeline Validation, Trigger and Monitoring

Chapter 3: Azure Data Factory Concepts

  • Azure Data Factory (ADF) Concepts
  • Hybrid Data Integration at Scale
  • ADF Pipeline Components & Usage
  • Configure ADF Resource in Azure
  • Understanding ADF Portal and IR
  • Linked Services and Connections
  • Datasets and Tables / Files for ETL
  • ADF Pipelines: Design, Publish & Trigger
  • ADF Pipeline with Copy Data Tool
  • Creating Azure Storage Account
  • Storage Container, BLOB File Uploads
  • Data Loads with Azure BLOB Files
  • DIU Allocations and Concurrency
  • Creating Linked Services, Datasets
  • Pipeline Trigger, Author and Monitor

Chapter 7: ADF Data Flow - 2

  • ADF Pipelines For ETL Operations
  • Data Flow Tasks and Activities in Synapse
  • Pivot Transformation For Normalization
  • Generating Pivot Column, Aggregations
  • Pivot Transformation and Pivot Settings
  • Pivot Key Selection, Value and Nulls
  • Pivoted Columns and Column Pattern
  • Column Prefix, Help Graphic & Metadata
  • Window Functions & Usage in Data Flow
  • Rank / DenseRank / Row Number
  • Over Clause and Input Options
  • Derived Column Transformations
  • Exists & Lookup Transformations
  • Reusing Data Flow Tasks in Synapse
  • Pipeline Validations & Executions

Chapter 11: Optimizations, Power Query

  • ADF ETL with GUI : Power Query
  • Power Query Resoruce Creation, Use
  • Source Data Configurations & Settings
  • Rename, Remove, Pivot, Group By, Order
  • Index, Filter, Remove Error Rows
  • Using Power Query Activity, ADF Pipelines
  • Spark Cluster Configurations for Pipelines
  • Concurrency, Big Data Recommendations
  • Storage Optimization Techniques
  • ETL Optimization Techniques
  • SQL Pool (Synapse) Optimizations
  • Indexes, Partitions, Distributions, DOP
  • Pipeline Optimization Techniques
  • Partitions, DOCP, Compressions, DIU
  • Staging, Polybase and Core Counts

Chapter 4: ADF Pipelines, Polybase

  • Copy Data Tool For ETL Operations
  • Azure SQL DB to Synapse Data Loads
  • Working with Multi Tables Data Loads
  • Query Options for Source Datasets
  • Transformations with Copy Data Tool
  • Rename, Rearrange & Remove Options
  • Pipeline Execution: DTU & DOCP
  • ADF Pipeline Monitoring Options
  • ADF Pipelines: Execution Settings
  • ADF Logging Options & Storage Account
  • Compression Option, DOP and DOCP
  • ETL Staging Advantages & Performance
  • Staging with Storage Account, Container
  • ADF Pipeline Triggers and Monitoring
  • Polybase For Azure Synapse, Advantages

Chapter 8: Azure Synapse Analytics

  • Azure Synapse Analytics Resource
  • Azure Synapse Analytics Workspace
  • Managed Resource Group, SQL Account
  • SQL Admin Account and its Purpose
  • Operations with Synapse Workspace
  • ADLS Gen 2 Storage Account, Container
  • Synapse Studio (Synapse Portal)
  • Dedicated SQL Pools & Spark Pools
  • Creating Dedicated SQL Pools
  • Synapse Tables, Data Loads with T-SQL
  • COPY INTO Statements with T-SQL
  • Clustered Column Store Indexes
  • Row Terminator and Compressions
  • T-SQL Queries and Aggregations
  • Aggregation Data Loads in Synapse

Chapter 12: Pipeline Monitoring, Security

  • Azure Monitor Resource and Usage
  • Pipeline Monitoring Techniques
  • ADF: Pipeline Monitoring and Alerts
  • Synapse: Pipeline Monitoring and Alerts
  • Synapse: Storage Monitoring and Alerts
  • Conditions, Signal Rules and Metrics
  • Email Notifications with Azure
  • Concurrency, Big Data Recommendations
  • Azure Active Directory (AAD) Users, Groups
  • IAM: Identity & Access Management
  • Synapse Workspace Security with RBAC
  • ADF Security with RBAC: Owner, Contributor
  • Azure Synapse SQL Pool Security: Logins
  • Users, Roles and Resource Classes (RC)
  • Assigning RCs to Users. Object Level Security

SQL Server T-SQL, Azure SQL, Azure DBA, Azure BI, Azure Data Engineer, Power BI Training

 

Other Trainings