Snowflake Online Training

Official Learning Partner

Course Highlights

Complete Practical and Real-time Snowflake Training with Basic to Advanced Cloud Concepts, ETL (Extract, Transform and Load), DWH (DataWarehouse), Big Data Storage, Big Data Analytics, Incremental Loads, External Stages, SnowPipes, Failsafe Concepts, Data Recovery, Security Management, ETL Management, Administration, Azure Integrations, Power BI Integrations, External Data Connections and more..

This Snowflake Training Classes also includes Concept wise FAQs, Real-time Project, Project Solution, Project FAQs for your Job Interviews, Snowflake Core Certification Guidance with Real-Life Scenarios.

Trainer: Mr. Sai Phanindra Tholeti
Profile: https://www.linkedin.com/in/saiphanindra/

Training Modes:

LIVE Online Self Paced Videos

Training Highlights:

DWH & Snowflake Introduction
Snowflake Architecture
Snowflake Service Form
Snowflake Objects
Using SnowSQL CLI, Snowflake Data

Sequence in Snowflake
Snowflake Stream & Tasks
SnowSQL Operators, SnowSQL Concepts
Data Loads & Validations

Staging Operations with Snowflake
Unloading Data from Snowflake
Virtual Warehouse in Snowflake
Replication and Business Continuity

Security Management with Snowflake
Snowflake with Azure Data Factory
Managing Governance in Snowflake
End to End Real-time Project @ Resume

Snowflake Training
Course Contents:

Brochure Download Register Now

Module 1: SQL Server & T-SQL Basics Training Content

Ch 1: DATABASE INTRODUCTION

Databases Introduction & Purpose
Database Types : OLTP, DWH, OLAP
Microsoft SQL Server Advantages, Use
SQL Server Components and Usage
Microsoft SQL Server – Career Options
Developer, DBA, Data Engineer
Data Analyst, Data Scientist Careers
SQL : Purpose, Real-time Usage Options
SQL Versus Microsoft T-SQL [MSSQL]
Course Plan, Real-time Project, Resume
24 x 7 Online Lab for Remote DB Access
Versions and Editions of SQL Server
SQL Server Pre-requisites : S/W, H/W
System Configuration Checker Tool

Ch 2: SQL SERVER INSTALLATION

SQL Server & SSMS Installation Plan
SQL Server Pre-requisites : S/W, H/W
SQL Server 2022 & 2019 Installation
Database Engine Feature, OLTP
Instances : Types and Properties
Default Instance, Named Instances
Service and Service Account Use
Authentication Modes and Logins
Windows Logins and SQL Logins
SQL Server Management Studio
Server Connections with SSMS Tool
Local and Remote Connections
System Databases: Master and Model
MSDB, TempDB, Resource Databases

Ch 3: SSMS Tool, SQL BASICS – 1

Creating Databases: Files [MDF, LDF]
Creating Tables in User Interface
Data Insertion & Report in User Interface
SQL : Purpose and Real-time Usage
SQL Versus T-SQL : Basic Differences
DDL, DML, SELECT, DCL and TCL
Creating SSMS Sessions : SPID
Create, Connect Databases using SQL
Creating Tables with INT, CHAR
Data Storage, Inserts – Basic Level
Table Data Verifications with Select
SELECT Statement for Table Retrieval
Identify Databases and Tables
Identify Sessions and Session ID

Ch 4: SQL BASICS – 2

Creating Tables: VARCHAR, FLOAT
Single Row Inserts, Multi Row Inserts
Rules for Data Insertion Statements
SELECT with WHERE Conditions
AND and OR Operators Usage
IN Operator and NOT IN Operator
Between, Not Between Operators
LIKE and NOT LIKE Operators
ORDER BY, TOP & OFFSET
Basic Sub Queries with SELECT
UPDATE Statement & Conditions
DELETE & TRUNCATE Statements
ALTER, ADD COLUMN Statements
DROP Statements: Table, Database

Ch 5: SQL Basics – 3, TSQL 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 Retrieval & 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 6: Constraints, Index Basics

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 & 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 7: Joins Basics, TSQL Queries

JOINS – Table Comparisons Queries
INNER JOINS For Matching Data
OUTER JOINS For (non) Match Data
Join Queries with “ON” Conditions
Left Outer Joins – Example Queries
Right Outer Joins – Example Queries
FULL Outer Joins: Realtime Scenarios
CROSS JOIN and CROSS APPLY
One-way, Two way Data Comparisons
Using Table Aliases & Column Aliases
Optimizing Join Queries with Indexes
Choosing Correct Comparison Columns
Joining Unrelated Tables in TSQL
Self References, Self Joins in TSQL

Ch 8: Group By, Views & Excel

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
Group By with Joins in TSQL
Query Execution Order & Aliases
Joins with Sub Queries, Formatting
Database Objects: Overview & Usage
Views: Types, Usage in Real-time
Creating, Executing & Verifying Views
Storing Queries in Database Views
Excel Analytics – Joins & Views
Excel Office Data Connection Reports

Ch 9: Functions, Procedures Basics

Functions with SQL Server, TSQL
Scalar, Inline, Table Functions
Variables: Declare, Real-time Use
Creating, Executing Functions
Functions for Computations
Functions for Parameterized Joins
Procedures: Usage in Real-time
Using Parameters in SQL Server
Parameterized Joins in TSQL
Compilation with Stored Procedures
sp_help, sp_helptext, sp_helpindex
sp_helpdb, sp_rename, sp_recompile
System Views For Metadata Audits
DBID, DBName, ObjectID, ObjectName

Ch 10: 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
Batch Concept and Go Statement
Open Transactions in Real-time
Using Conditional Commits, Rollbacks

Ch 11: Normal Forms, Cursors

First Normal Form and Atomicity
Third Normal Form and MVD Property
Boycee-Codd Normal Form : BNCF
Fourth Normal Form : Advantages
Self Reference Keys and 4 NF Usage
1:1, 1:M, M:1, M:M Relationship Types
Computed Columns, Variant Type
Linked Servers, Remote Joins in TSQL
2 Part, 3 Part, 4 Part Naming Styles
Remote Joins Queries and Aliases
Cursors – Basics, Data Operations
Cursors – Life Cycle & Declaration
Cursors Types, FETCH Operations
Cursors – Deallocate, Real-world Use

Ch 12: TSQL Merge, Cursors

IIF() Function with SELECT Query
WHEN..THEN..ELSE
WHEN MATCHED, NOT MATCHED
Incremental Loads, Upsert Statement
Stored Procedures: Merge Statement
UNION and UNION ALL Operator
Window Functions: Rank, Dense Rank
Row_Number, PartitionBy in TSQL
Duplicate Row Identification, Deletion
Grouping, Cube, Rollup, Lag, Lead
Data Types: Numerical, Date, Time
Data Types: Characters, Real, Float
Date & Time Functions, DateAdd
String Functions, Concat, SubString

Case Study 1: Database Design with Tables,
Constraints, Keys & Relations

Case Study 2: Joins with Group By,
Sub Queries, Views, Excel Analytics

Module 2: Snowflake Training Content

Mod 1: Snowflake Architecture, Basics

Ch 1: Introduction to Cloud & Snowflake

Database Introduction & Database Types
OLTP, OLAP and Warehouse Databases
Need for Datawarehouses; Advantages
Popular Database Technologies
Popular Datawarehouse Technologies
Need for Cloud & Remote DWH Store
Cloud Implementation Types & Usage
IaaS: Infrastructure As A Service
PaaS: Platform As A Service
SaaS: Software As A Service
Snowflake Cloud: Introduction
Snowflake: Popular SaaS Platform
Cloud Datawarehouse Concepts
Advantages, Dependencies in Snowflake

Ch 2: Snowflake Account & Editions

Creating Snowflake Account (Cloud)
Snowflake Trail Account: Limitations, Uses
Snowflake Account Components
Cloud Platform: Azure, AWS, Google Cloud
Regions and Availability with Snowflake
Snowflake Editions, Credits; UI Usage
Default Accounts; Web UI & Snow Sight
Snowflake Storage: Ondemand, Capacity
Snowflake Editions and Comparisons
Standard Edition: Features, Advantages
Enterprise Edition: Features, Advantages
Business Critical Edition: Features, Usage
Virtual Private Edition (VPS) & Pricing
Snowflake Pricing: Ondemand Vs Capacity

Ch 3: Architecture, Warehouse (DWH)

Snowflake Architecture & Compute
Shared Disk, Shared Nothing Architecture
Cluster Nodes and Snowflake Clusters
CPU & Memory Resources in Clusters
Disk Storage and Network Communication
Storage Layer and Cloud Service Layer
Database Query Layer & Data Cycle
Snowflake Datawarehouse Architecture
MPP: Massively Parallel Processing
Compute and Storage Components
Column Store and Virtual Warehouse
Datawarehouse Creation in SnowSight
Classic UI with Snowflake; Navigations
Data Load and Billing; Auto Suspend

Ch 4: Snowflake Databases & Tables

Snowflake Databases and Data Storage
Need for Snowflake Warehouse, Compute
Database Objects and Hierarchy
Snowflake Worksheet Parameters
Database Creation with Snowflake UI
Retention Time, DB List & Connections
Permanent and Transient Databases
Snowflake Tables and their Usage
Permanent, Transient & Temp Tables
Creating Tables with SnowSight
Describe Options, Data Inserts
CREATE TABLE AS SELECT
Cloning Tables, Case Sensitivity Options
Collation, ALTER, DROP & UNDROP

Ch 5: Time Travel & Transient Tables

Time Travel Feature in Snowflake
DML Operations and Silent Audits
Continuous Data Protection Life Cycle
Invoking Time Travel Feature in Snowflake
Timestamp, Offset & Query ID Options
Time Travel using Offset Feature
Time Travel using Query ID Feature
Data Recovery using TIMESTAMP
Using OFFSET options in Real-world
Fail Safe and UNDROP Operations
Transient Tables and Real-time Usage
Permanent Table and Real-time Usage
Restrictions with Permanent Tables
Identical Names and Naming Conflicts

Ch 6: Schemas and Session Context

Schemas: Creation, Real-time Usage
Permanant and Transient Schemas
Managed Schemas in Real-time Usage
Verifying and Cloning Snowflake Schemas
Invoking Schemas & Cloning Operations
ALTER SCHEMA.. IF EXISTS Options
Creating, Working with Managed Schema
Snowflake Sessions (Workspaces)
Session Context: Role and Warehouse
Session Context: Database & Schema
Working with Fully Qualified Names
CTAS: Create Table As Select
Data Loading with GUI, SQL Scripts
Using Query and History Tab in GUI

Mod 2: Snowflake Concepts, Admin

Ch 7: Constraints and Data Types

Snowflake Constraints, Data Validations
NULL and NOT NULL Properties
Unique, Primary and Foreign Keys
Working with Named Constraints
Single Column, Multi Column Constraints
Inline and Out Of Line Constraints
Table Constraints Types, Real-time Use
Constraint Properties: ENFORCED
DEFERRED, IMMEDIATE Options
Numeric, String and Binary Data Types
Boolean Data Types and Usage
Date and Time Data Types
Semi Structured Data Types
Geospatial & Variant Data Types

Ch 8: Snowflake Cloning (Zero Copy)

Cloning Operations with Snowflake
Zero Copy and Schema Level Cloning
Real-time Uses: Cloning in Snowflake
Snapshot Concept, Metadata Options
Possible Objects for Snowflake Clone
Permissions for Snowflake Cloning
Accessing, Controlling Cloned Objects
Real-time Considerations @ Cloning
Storage Layer and Metadata Layer
Cloning with Foreign Key Constraints
Cloning Snowflake Databases
Cloning Snowflake Schemas, Tables
Security and MANAGE GRANTS
Cloning and COPY GRANTS Options

Ch 9: Snowflake Procedures & Views

Snowflake Procedures and Functions
Creating and Using Stored Procedures
Using CALL Command in Snowflake
SQL and JavaScript Options with SPs
Snowflake Deferred Name Resolution
Overloading with Snowflake SPs
Transactions & Injection
Variables & CALL Command
Using execute() with Procedures
sqlText:command, createStatement
Cursoring Operations
Dynamic DML Operations with SPs
Loops & next.scan()
RETURN and RETURNS Statements
Views & Query Storage
DML and SELECT Operations on Views
Regular Views, System Predefined Views
Recursive Views, Parameterized Views

Ch 10: Security Management

Security Management Concepts
Security Entities with Snowflake
Securable Objects, Users & Roles
Prevligies and Privilege Groups
Snowflake Security Hierarchy
Organization, Account, Users, Roles
Schema, Tables, Other DB Objects
Creating and Using Roles, Users
System Defined Roles and Usage
Role Hierarchy and Dependency
Creating and Working with Users
Auditing Users and Password Policy
RBAC: Role Based Access Control
DAC: Discretionary Access Control

Ch 11: Snowflake Transactions

Working with Transactions (ACID)
Atomicity, Consistency, Isolation
Durability and Data Storage Options
Transaction Types with Snowflake
Implicit, Explicit and Auto Commit
BEGIN TRANSACTION & COMMIT
DDL Statements and Transactions
BEGIN WORK Versus START
current_transaction() and Usage
to_timestamp_ltz and Usage
Failed Transactions with SPs
Batches Versus Transactions
Transactions with Stored Procedures
Scoped and INNER Transactions

Ch 12: Snowflake Streams & Audits

Working with Snowflake Streams
Stream Object and DML Auditing
Snapshot Creation, Offset Options
METADATA$ACTION Parameter
METADATA$ISUPDATE Parameter
METADATA$ROW_ID Parameter
Stream Types: Standard Stream
Append Only Stream & Usage
Insert Only Stream & Usage
Data Flow with Snowflake Streams
Auditing INSERT, UPDATE, DELETE
show streams; desc streams
Streams on Transient Tables
Time Travel; Using Stream Tables

Mod 3: ETL, Stages & Pipes

Ch 13: Snowflake Tasks, Partitions

Snowflake Tasks and Real-time Use
Snowflake Serverless Compute Model
User Managed & Snowflake Managed
Tasks Tree: Root and Link Concepts
Directed Acyclic Graph (DAG)
Tasks Schedules and RESUME Options
CREATE, ALTER, DESCRIBE, SHOW
CREATE TASK … AFTER Statement
ALTER TASK … ADD AFTER Statement
CRON Syntax with Tasks, Procedures
Virtual Warehouse Concepts (DWH)
Multi Cluster Warehouse Config
Client Utilities, Drivers & Connectors
Auto Scale Options, Billing (Pricing)

Ch 14: SnowSQL and Variables

SnowSQL: Concepts, Client Installation
SnowSQL Tool: Configuration Options
SnowSQL: Account Authorization
DDL, DML and SELECT Operations
Snowflake SQL Query Syntax Format
SnowSQL Command Line Options
Working with DB, Schema and Tables
Snowflake Variables, Batch Processing
Snowflake SQL Data Types, Usage
DECLARE, LET, BEGIN and END
EXECUTE IMMEDIATE, FOR, END FOR
Creating Warehouse, Database, Tables
Granting Permissions, Query Execution
Writing Output to Files (Win, MAC OS)

Ch 15: Snowflake Partitions, Stages

Snowflake Partitions, Real-time use
Service and Storage Layer Concepts
Micro Partition with DML, CDC
Cluster Key: Usage, ReClusters
Depth and Overlap Properties
Internal Partition Types & Usage
List, Range and Hash Partitions
SYSTEM$CLUSTERING_INFORMATION
Snowflake Stages and Usage
Internal and External Stages
User Stages : Creation, Usage
Table Stages: Creation, Usage
Internal Named Stages, Usage
COPY Command, Bulk Data Loads

Ch 16: Azure & External Storage

Working with Azure Storage
Azure Subscription, Resources
Create, Use Azure Storage Account
Storage Containers, BLOB Data
Using SnowSQL with Azure BLOB
SAS [Shared Access Signature]
Using SAS Key and FILE PATH
External Stages in Snowflake
File Formats: Creation, Usage
Creating & Using External Stages
Azure Storage with BLOB
COPY INTO Command Usage
Listing Stages with Snowflake
Snowflake Patterns & RegEx

Ch 17: SnowPipes & Incremental Loads

Snowflake SnowPipes: Incremental Loads & SnowPipe
Creating Azure Account For BLOB
Creating Container, Generate SAS Key
Creating Azure Queue and Event Grid
Notification Integration in Snowflake
Integrations: Show, Describe, Use
Azure Active Directory & IAM
Linking Azure AD with Snowflake
Enterprise Application, Authentication
File Formats with Regular Expr
External Data Stages
Data Unloading Concepts
PUT and GET with SnowSQL
Bulk Unloads; Data Preparation & Stages
Data Unloading to User Stages
Data Unloading to Table Stages

Ch 18 : REAL-TIME PROJECT

Phase 1:
Legacy Data Systems
Heterogenous Data Platforms
File Data Sources
Phase 2
Desining DWH in Snowflake
Incremental Loads
Capacity Planning
External Data, Integrations
SnowPipes and Azure BLOB
Phase 3
Data Visualizations, Analytics
End to End Implementations
Project Solution & FAQs
Resume & Certification Guidance

Module 3 : Azure Data Engineer [Applicable for Power BI Plan C]

Part 1: Azure Data Factory, Synapse Analytics

Chapter 1: Cloud Basics, Azure SQL

Cloud Introduction and Azure Basics
Azure Implementation: IaaS, PaaS, SaaS
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
Azure Account, Subscription (Free)
Azure SQL Server [Logical Server]
Firewall Rules and Azure Services
Azure SQL Database Deployment
Azure SQL Pool Deployment
Compute: DTU Versus DWU
Test Connections from SSMS

Chapter 2: Synapse SQL Pools (DWH)

Dedicated SQL Pools in Azure
Data Warehouse with Synapse
Massively Parallel Processing (MPP)
Control Nodes and Compute Nodes
DMS: Data Movement Service
Start/Resume/Pause & Scaling
SQL Pool Config @ TSQL Scripts
Start/Resume/Pause, Scaling Options
Table Creations @ TSQL Scripts
Table Partitions: Left & Right
Distributions: Round Robin, Hash
Distributions: Replicate and Usage
Auto Indexing & Column Store
Planning for Big Data Loads
Need for ADF: Azure Data Factory

Chapter 3: Azure Data Factory, Pipelines

Azure Data Factory (ADF) Concepts
ADF Pipelines : Architecture
Integration Runtime (IR) & Use
Linked Services and Datasets
Pipeline Activities: Copy Data Tool
DIU : Data Integration Units
DTU Vs DWUs Vs DIU
ADF Pipeline with Copy Data Tool
Azure SQL DB to Synapse Data Loads
Multi Tables Data Loads with ADF
Bulk Insert, Data Copy Methods
ETL Staging: Storage Account
Staging Container Connections
DIU Allocations & Publish
ETL Pipeline Monitoring, Runs

Chapter 4: OnPremise Data Loads, Upsert

Copy Data Tool : Incremental Loads
On-Premise Data Sources with Azure
Self Hosted Integration Runtime (IR)
Access Keys, Remote Linked Service
Synapse SQL Pool (DW), OnPremise
ETL Staging with Storage Account
Copy Method: Polybase – Tuning
Polybase : Big Data Loads
ETL Pipelines for Incremental Loads
Business Keys For Table Upsert
Pipeline Schedules with ADF
ETL Logging with Storage Account
Copy Method: UPSERT
DIU, DOCP & Publish
Manual Pipeline Executions in ADF

Chapter 5: File Incremental Loads in ADF

Incremental Loads with Files (BLOB)
ETL Schedules: Tumbling Window
Execution Retry and Delay Options
Binary Copy, Structural Data Loads
Incremental Loads Verification Tests
Incompatible Rows & Fault Tolerance
Pipeline Compression & Tuning
Pipeline Publish, Monitor Options
Azure Monitor Resource : Metrics
ADF Metrics and Pipeline Runs
ADF: Pipeline Monitoring and Alerts
Synapse: Storage Monitoring, Alerts
Conditions, Signal Rules and Metrics
Alerts & Action Groups: Emails
Email Notifications with Azure

Chapter 6: ADF Data Flow – 1

Data Flow Task, Data Flow Activity
Transformations with Data Flow
Spark Cluster For Debugging
Cluster Node Configurations
Spark Cluster Types & Sizing
Transaction Optimized – Capacity
Memory Optimized – Capacity
Data Cleansing with ADF
Data Orchestration with Data Flow
SELECT Transformation & Options
Conditional Split Transformation
UNION, SELECT Transformation
Spark Cluster For Pipeline Executions
Pipeline Monitoring & Run IDs
Adding Data Flow into Pipelines

Chapter 7: ADF Data Flow – 2

ADF Pipelines For ETL Operations
Data Flow Tasks, Activities in Synapse
JOIN & EXISTS Transformations
Aggregate & Group By Transformations
Window Functions, Rank in Data Flow
Rank / DenseRank / Row Number
Derived Column Transformation
Lookup, Surrogate Key, Parse
Type Convert, Cast Transformations
Reusing Data Flow Tasks in Synapse
Pipeline Validations & Executions
Inline Datasets, Schema Drift
Data Deduplication with ADF
DFT Optimization Techniques
Data Flow Task – Staging, Logging

Chapter 8: Azure Synapse Analytics

Azure Synapse Analytics Resource
Azure Synapse Analytics Workspace
Managed Resource Group, SQL Account
Synapse Workspace & Synapse Studio
Operations with Synapse Workspace
ADLS Gen 2 Storage Account, Container
Synapse Studio: Scripts & Pipelines
Dedicated SQL Pools : Creation, Use
Synapse Tables, Data Loads with TSQL
COPY INTO Statements with T-SQL
Row Terminator and Compressions
T-SQL Queries and Aggregations
Aggregation Data Loads in Synapse
Creating Synapse Pipelines with TSQL
Stored Procedure Activity & Triggers

Chapter 9: Synapse Analytics with Spark

Synapse Pipelines: Performance Advantage
Pivot Transformation For Normalization
Generate Pivot Column, Aggregations
Pivot Transformation & Pivot Setting
Pivot Key Selection, Value and Nulls
Pivoted Columns and Column Pattern
Column Prefix, Help Graphic, Metadata
Denormalized Data and Aggregations
Apache Spark Pool in Azure Synapse
Spark Cluster Nodes: Vcores, Memory
Notebooks : Purpose, Usage Options
Python Notebooks For Remote Access
Creating Databases in Apache Spark Pool
Data Loads from Dedicated SQL Pools
PySpark Code for Data Operations, Writes

Chapter 10: Synapse Security & Parameters

Azure Active Directory (AAD) Users, Groups
IAM: Identity & Access Management
Synapse Workspace Security with RBAC
ADF Security: RBAC, Owner, Contributor
Azure Synapse SQL Pool Security: Logins
Creating SQL Logins & Users : master
SQL Users in Azure SQL DB and SQL Pool
Grant, Control, Revoke: Security Roles
Parameters – Creation and Use in Pipelines
Dynamic Connections with Credentials
User Name and Password Connectivity
Dynamic Dataset Configurations
Pipeline Expressions with Parameters
Resource Classes and Usage with SQL Pool

Chapter 11: Change Data Capture (CDC)

Change Data Capture (CDC) Data Loads
Incremental Loads with CDC Types
SQL Server CDC : ETL Load Dates
Pipeline Expression, Data Window
JSON Parameters, Pipeline Scheduling
ETL Optimization Techniques
Serverless Pool in Azure Synapse
Connections, Use with Serverless Pool
Using Azure OpenDatasets in Synapse
OPENROWSET and BULK Data Loads
Working with Parquet Files in Synapse
Python Notebooks (Pyspark) in Synapse

Part 2: Data Lake Storage, Stream Analytics

Chapter 1: Azure Fundamentals – Storage

- Azure Resources: Storage Components
- Storage Resources and Properties
- Resource Groups & Subscriptions
- Azure Storage : Files, Tables and ETL
- Azure Storage Account & Use
- Data Lake Storage Account (ADLS)
- Advanced Options: HNS Property
- Resource Location, Resource Group

Azure Portal: Deployment Verifications
Azure Portal: Deployment Verification
Storage Account : Basic Properties
Overview Page: Status, HNS State
Azure Storage : Access Options
Azure Storage Explorer Tool
Explorer Tool : Configuration

Chapter 2: Azure Storage Operations

BLOB: Binary Large Objects
Storage Browser and Service Pages
Storage Browser: Container Creation
Storage Browser: Folder, File Uploads
Service Page: Container Creation
Service Page: Folder, File Uploads
Container, Folder, File Properties
Limitations with Storage Portal
Azure Data Explorer Tool : Usage
Contrainer: Creation, Properties
File Uploads, Edits and Access URLs
Azure Storage Explorer Tool Usage
Azure Account Options in Explorer
Directory Creation, File Operations
Limitations with Explorer Tool

Chapter 3: Azure Storage Security, ACLs

Azure Data Lake Storage Security Options
Shared Access Keys: Primary, Secondary
SAS Key Generation: Container, Tables
SAS Key Permissions, Validation Options
Access Keys: Account Level Permissions
Azure Active Directory: Users, Groups
Azure AD Security: RBAC, IAM, ACLs
Owner Role, Contributor, Reader Role
Azure Data Lake Storage Security
ACL : Access Control Lists & Security
Azure BLOB Storage Containers & ACLs
Folder Level and File Level Security
ACL Permissions: Read, Write, Execute
Access Policy: Creation, Realtime Use
rwacdl; Azure Principals, CORS

Chapter 4: SQL Database Migrations

OnPremise SQL DB to Azure Migration
SSMS Tool, SQL Database Installation
Source Database Scripts & Validations
BACPAC File Generation: SSMS Tool
Table Selection & Advanced Options
Azure Data Lake Storage, SSMS Access
Azure Storage Container, BACPAC Files
IAM and Account Key Authentication
Azure SQL Server Creation From Portal
Azure SQL Database Deployment
DTU : Data Transaction Units, Pricing
Azure Firewall Configuration, Security
Azure SQL Database Imports (bacpac)
Azure SQL Server with ADLS Containers
Azure SQL DB Migrations, Verification

Chapter 5: Azure Tables & Replication

Azure Tables – SchemaLess Design
Azure Tables: Creation, Data Inserts
Tables, Entities, Properties Concepts
Structured, Relational Data Storage
Azure Tables: GUI, Data Types
Azure Tables: Big Data Imports
Data Edits, Queries, Delete Operations
Odata Options (REST API), End Points
Azure Storage: Replications, DR Options
LRS: Locally Redundant Storage
GRS: Globally Redundant Storage
ZRS: Zone Redundant Storage
Replication Options and Advantages
Replication Verification, Modifications
Storage Endpoints, Failover Partner

Chapter 6: Azure Stream Analytics, IoT

Azure Stream Analytics Real-time Use
Real-time Data Processing, Events
Ingest, Deliver & Analysis Operations
Azure Stream Analytics Jobs Concept
Understanding Input, Output Options
SAQL Queries: Stream Analytics Jobs
IoT: Internet Of Things, Real-time Data
Need for IoT Hubs and Event Hubs
Conditional Split Transformation
Creating IoT Device for Data Inputs
Creating Azure Stream Analytics Job
Stream Analytics for Historical Data
Azure SQL Database for ASA Jobs
SAQL: Query Formatting, Validation
Historical Data Upload, ASA Jobs

Chapter 7: Azure Event Hubs

Azure Stream Analytics For API Data
IoT Hubs, IoT Devices, Connection Strings
Rasberry APP Connections with IoT Hub
Azure Storage Account and Container
Creating Azure Stream Analytics Job
Configuring Input Aliases with IoT Hub
Output Aliases with ADLS Gen 2
SAQL Query, Job Executions; Monitoring
Azure Event Hubs and Event Instances
Event Hub Namespaces, Partition Counts
Access Policies, Permissions & Defaults
RootManageSharedAccessKey & Options
Connection Strings & Event Service Bus
Telco App : Executions & LIVE Data
On-Premise App Integration, ASA Jobs

Chapter 8: Storage Architecture, Queues

Azure Storage Account : Architecture
Etag: Replication & Encryption Use
BLOB Types: Block, Append & Page
Access Tiers: Hot, Cool, Cold Types
Archive Access Tier & Retention
Legal Hold & Time Bound Access
Pricing : HNS, Security, Encryption
EndPoint URL & Read-Only Use
Azure File Share Service (Files)
Mounting Files From On-Premise
SMB File Share : Hot, Optimized
Azure Queue Service & Messages
Message Queues : Operations
Storage Explorer Tool with Shares
Azure Storage Services: ETL Needs

Chapter 9: Monitoring & Key Vaults

Azure Monitor, Metrics & Activity Logs
Monitoring Azure Storage Namespaces
Add KQL Metrics; Account, Blob and File
Total Ingress and Egress Metrics: Charts
Average Latency, Transaction Count
Request Breakdowns, Signal Logic
Azure Alerts & Conditions, Notifications
Signal Logic Conditions and Emails
Key Vaults Types: Standard & Premium
Secret Page, Key Backups, Key Restores
Azure Key Vaults – Name and Vault URI
Inbuilt Managed Key and Azure Key Vault
Key Vaults Types: Standard & Premium
Secret Page, Key Backups, Key Restores
Managed Identity with ETL Process

Part 3: Databricks, Spark, Python

Chapter 1: Azure Intro, Azure Databricks

Azure Cloud : SaaS, PaaS, PaaS & IaaS
Azure Cloud : Storage, ETL Resources
Azure Databricks : Compute Resources
Need for Azure Databricks (ADB)
Azure Databricks : Purpose & Config
Azure Databricks Service Creation
Azure Databricks Components
Azure Databricks Workspace, Usage
Spark Cluster Configurations, Capacity
Driver Nodes, Worker Nodes in Spark
Cluster Types : Personal, Unrestricted
CPU, Memory & IO Resources
Virtual Machines (VM) for Clusters
Databricks : Runtime & DBFS Storage
DBFS : Files, Tables with Spark DB

Chapter 2: SparkDatabase, SQL Notebooks

DBFS : File Uploads from ON-Premise
Creating Spark Tables; Spark DB
Data Explorer: HIVE Metastore
Data Explorer: Spark Database, Tables
Notebooks: SQL, Python and Scala
Creating SQL Notebooks in Databricks
Creating User Defined Spark Databases
Connecting / Using Spark Databases
Spark SQL : Big Data Loads
Spark SQL : Database & Table List
Spark SQL : Data Aggregations, Jobs
Spark SQL : Data Analytics, Reports
Analytics: X, Y Axis, Group By
Notebooks : Export, Import, Clone
Notebooks : Storage & Versions

Chapter 3: Python Intro, Data Loads

Python : Introduction, Real-time Use
Python For ETL and DWH
Python For Azure: Data Engineer
Python Data Frames & Purpose
Python Dataframes – Pandas
Python with Spark Integrations
PySpark for DDL and ETL
PySpark Versus SQL Notebooks
Reading DBFS Data into Spark
Creating Dataframes for ETL
Temporary Views & Dataframes
Spark Temp Views: Aggregations
Spark Table Loads, HIVE Data
write.format() & overwrite
Parquet Tables with Spark DB

Chapter 4: PySpark with ADLS

Azure Storage Account : Creation
Azure Data Lake Storage : HNS
Creating Containers in ADLS
BLOB File Uploads / Generation
Account Key : Access Key, SAS Key
BLOB Access URL for Databricks
WASBS URL for PySpark Notebook
Generating PySpark Script
PySpark Connection Variables
Databricks : Data Import Scripts
Config Options with ADLS, Spark
config (), Session Context
DataFrames with Temp Tables
Escape Sequence with SparkSQL
Data Explorer: HIVE & Spark DB

Chatper 5: PySpark Widgets

Widgets : Notebook Parameters
widget module : Text, Combo
Dropdown, Multi Select Parameters
dbutils help(), get() & remove()
Dataframes, Spark SQL @ Variables
Python Data Frames, Spark SQL
Reading Parameters Values
Parameters Versus Variables
Using Parameters For Temp Tables
Using Parameters for Spark Tables
Data Storage and HIVE Metastore
Reading Parameterized Data
Format Strings with PySpark
Dynamic Queries with Spark SQL
Aggregations and f Strings

Chapter 6: Architecture, Workflows

Driver Nodes, Worker Nodes, DBUs
RDD : Resilent Data Distribution
DAG : Directed Acyclic Graph
Hadoop HDES and Spot Instance
Cluster Manager, Master Node
RDDS, Worker, Excecutor & Slave
Hadoop HDES & Databricks Runtime
Databricks Optimization Techniques
Spot Instance, Photon Acceleration
All Purpose Cluster, Job Cluster
Databricks Jobs: Creation & Tasks
Jobs with Parameters, Executions
Task Dependency & Notifications
Continuous & Manual Schedules
Active Jobs, Recent Run Jobs, Monitor

Chapter 7: Databricks Security, Scala

Azure Databricks Security Operations
Azure Active Directory (Azure AD)
AD Users and RBAC with IAM
Owner, Contributor & Reader Roles
Workspace Admin Permissions
Notebook Permissions & Share
Workflow Security, HTTP Path
User Tokens & ServerName
Scala : Differences with PySpark
Scala : Variables Declaration, Usage
SparkSQL with Scala Notebooks
Temp Views with Scala Notebooks
Aggregations with Scala Notebooks
Visual Data Analytics with Scala
PySpark to Scala Conversions

Chapter 8: Scala with ADLS, Azure SQL

Data Imports with Azure SQL DB
Using Scala for Big Data Loads
Spark SQL Queries @ Temp Views
Variables, display(), read()
Scala Transformations, display()
JSON, AVRO and DBFS Mounts
azure.sas.container @ ADLS
write.jdbc() & JVM
JDBC Connection, DataframeWriter
Data Extraction, SQLContext
Spark Context and Spark Session
SQLServerDriver with Scala
ADLS with Scala Notebooks
Parameters (Widgets) with Scala
Compare Python with Scala

Chapter 9: DeltaLake Incr Loads, DWH

Azure DeltaLake Implementation
ACID Properties, Upsert Advantages
Delta Engine Optimizations & Uses
Pipeline Creation: JSON Files in DBFS
Delta Tables Creation, Data Loads
Spark Cluster Settings: Auto Optimize
Auto Compact, Delta Table Optimize
JSON Files, Delta Streaming Location
Joins and Merge with Delta Tables
Incremental Loads, Delta Tables
Create & Use DWH with Databricks
Upsert (Merge) with Spark Tables
Big Data & Jupyter Notebooks
Databricks with Data Factory (ADF)
End to End Implementations

Snowflake Training Plans

	Plan A 1. Snowflake	Plan B 1. SQL Server TSQL 2. Snowflake	Plan C 1. SQL Server TSQL 2. Snowflake 3. Azure Data Engineer
No of Weeks	4 Weeks	7 Weeks	14 Weeks
Snowflake: DWH and ETL	✔	✔	✔
Snowflake: Cloud Configurations	✔	✔	✔
Snowflake: DWH Architecture	✔	✔	✔
Snowflake: Database, Schemas	✔	✔	✔
Snowflake: Constraints, SP, Tasks	✔	✔	✔
Snowflake: Partitions & Tuning	✔	✔	✔
Snowflake: Security Management	✔	✔	✔
Snowflake: Automated Backups	✔	✔	✔
Snowflake: SnowSQL Concepts	✔	✔	✔
Snowflake: Variables & Tasks	✔	✔	✔
Snowflake: File Formats, Stages	✔	✔	✔
Snowflake: External Stages	✔	✔	✔
Snowflake: Azure Integration	✔	✔	✔
Snowflake: Snow Pipes & Upserts	✔	✔	✔
Snowflake: Power BI Analytics	✔	✔	✔
TSQL: Database Basics, T-SQL	✖	✔	✔
TSQL : Constraints, Joins, Queries	✖	✔	✔
TSQL: Views, Group By, Self Joins	✖	✔	✔
TSQL: SPs, Udf, Transaction Basics	✖	✔	✔
ADF : Azure Data Factory	✖	✖	✔
ADF : Data Imports, ETL	✖	✖	✔
ADF : Data Flows, Wrangling	✖	✖	✔
ADF : Transformations, ETL	✖	✖	✔
Synapse: Configuration, Loads	✖	✖	✔
Synapse: ETL with ADF, DWH	✖	✖	✔
Synapse: MPP, cDWH, DIUs	✖	✖	✔
ADB : Azure Data Bricks	✖	✖	✔
ADB : Architecture, Data Loads	✖	✖	✔
ADB : Workspace, Delta Tables	✖	✖	✔
DP 203 Certification Guidance	✖	✖	✔
DP 500 Certification Guidance	✖	✖	✔
Total Course Fee*	INR 15000USD 200	INR 20000USD 300	INR 44000USD 500

SQL Server & T-SQL Schedules

S No	Time (IST, Mon - Fri)	Start Date	Trainer	Register
1	6 AM - 7 AM	Jan 28th	Mr. Sai Phanindra	Register
2	11 AM - 12 PM	Jan 2nd	Mr. Sai Phanindra	Register
3	7 PM - 8 PM	Jan 6th	Mr. Sai Phanindra	Register
4	8 PM - 9 PM	Jan 20th	Mr. Sai Phanindra	Register

Snowflake Training Schedules

S No	Time (IST, Mon - Fri)	Start Date	Trainer	Register
1	7 AM - 8 AM	Jan 21st	Mr. Badri	Register
2	9 PM - 10 PM	Jan 2nd	Mr. Badri	Register

Can’t find a batch you
were looking for?

If you need self
paced videos

CERTIFICATE OF COMPLETION

SQL SCHOOL

24x7 LIVE Online Server (Lab) with Real-time Databases.
Course includes ONE Real-time Project.

Technical FAQs

Who is SQL School? How far you have been in the training services ?

SQL School is a registered training institute, established in February 2008 at Hyderabad, India. We offer Real-time trainings and projects including Job Support exclusively on Microsoft SQL Server, T-SQL, SQL Server DBA and MSBI (SSIS, SSAS, SSRS) Courses. All our training services are completely practical and real-time.CREDITS of SQL School Training Center

We are Microsoft Partner. ID# 4338151
ISO Certified Training Center
Completely dedicated to Microsoft SQL Server
All trainings delivered by our Certified Trainers only
One of the few institutes consistently delivering the trainings for more than 19+ Years online as inhouse
Real-time projects in
- Healthcare
- Banking
- Insurance
- Retail Sales
- Telecom
- ECommerce

I registered for the Demo but did not get any response?

Make sure you provide all the required information. Upon Approval, you should be receiving an email containing the information on how to join for the demo session. Approval process usually takes minutes to few hours. Please do monitor your spam emails also.

Why you need our Contact Number and Full Name for Demo/Training Registration?

This is to make sure we are connected to the authenticated / trusted attendees as we need to share our Bank Details / Other Payment Information once you are happy with our Training Procedure and demo session. Your contact information is maintained completely confidential as per our Privacy Policy. Payment Receipt(s) and Course Completion Certificate(s) would be furnished with the same details.

What is the Training Registration & Confirmation Process?

Upon submitting demo registration form and attending LIVE demo session, we need to receive your email confirmation on joining for the training. Only then, payment details would be sent and slot would be allocated subject to availability of seats. We have the required tools for ensuring interactivity and quality of our services.

Please Note: Slot Confirmation Subject to Availability Of Seats.

Will you provide the Software required for the Training and Practice?

Yes, during the free demo session itself.

How am I assured quality of the services?

We have been providing the Trainings – Online, Video and Classroom for the last 19+ years – effectively and efficiently for more than 100000 (1 lakh) students and professionals across USA, India, UK, Australia and other countries. We are dedicated to offer realtime and practical project oriented trainings exclusively on SQL Server and related technologies. We do provide 24×7 Lab and Assistance with Job Support – even after the course! To make sure you are gaining confidence on our trainings, participans are requested to attend for a free LIVE demo based on the schedules posted @ Register. Alternatively, participants may request for video demo by mailing us to contact@sqlschool.com Registration process to take place once you are happy with the demo session. Further, payments accepted in installments (via Paypal / Online Banking) to ensure trusted services from SQL School™

YES, We use Enterprise Edition Evaluation Editions (Full Version with complete feature support valid for SIX months) for our trainings. Software and Installation Guidance would be provided for T-SQL, SQL DBA and MSBI / DW courses.

Why Choose SQL School

Azure DevOps and azure fundamentals video banner

100% Real-Time and Practical
ISO 9001:2008 Certified
Concept wise FAQs
TWO Real-time Case Studies, One Project
Weekly Mock Interviews
24/7 LIVE Server Access
Realtime Project FAQs
Course Completion Certificate
Placement Assistance
Job Support
Realtime Project Solution
MS Certification Guidance

Trending Courses

A laptop on a desk with a lamp. Azure DevOps

Snowflake Training: 100% Real-time, Practical