introduction to azure synapse analytics.

All code and step-by-step instructions provided
Basic SQL knowledge required
Azure Account
Basic Python programming knowledge required
Cloud fundamentals will be beneficia

1. Introduction to Azure Synapse Analytics

2. Azure Synapse Analytics is a limitless analytics
service that brings together data integration,
enterprise data warehousing and big data
analytics.
Azure Synapse Analytics -Introduction

3. Azure Synapse Analytics is a limitless analytics
servicethat brings together data integration,
enterprise data warehousing and big data
analytics.
Azure Synapse Analytics -Introduction

4. Azure Synapse Analytics is a limitless analytics
service that brings together data integration,
enterprise data warehousing and big data
analytics.
Azure Synapse Analytics -Introduction

5. Azure Synapse Analytics -Introduction
Data Warehouse
Emergence of Data Lakes
Modern Data Warehouse Architecture
Modern Data Warehouse -With Azure Synapse Analytics
Modern Data Warehouse –Without Azure Synapse Analytics

6. Data Warehouse
ETL
Data Consumers
Data Sources
Operational
Data
External DataData Warehouse
/ Mart

7. Data Warehouse
Lack of support for unstructured data
Longer to ingest new data
Proprietary data formats
Scalability
Expensive to store data
Lack of support for ML/ AI workloads

8. Data Lake
Ingest
Data Sources
Operational
Data
External Data
Transform
Data Science/ ML
workloads
Data LakeData Lake
BI Reports
Data Warehouse /
Mart

9. Modern Data Warehouse
Data Sources
Operational
Data
External Data
IngestExplore &
Prepare
Transform
& Enrich
Model &
Serve
Visualize
Data Lake Storage
Azure Data
Factory
ADF –Data
Flows
Azure
Databricks
Azure SQL
Data
Warehouse
Power BI
Azure Data Lake Gen2
ADF –Data
Flows
Azure
Databricks

10. Data
Visualization
Data
Integration
Storage
Modern Data Warehouse
Compute
Azure
DatabricksADF Data Flows
Azure SQL Data
Warehouse
Azure Data
Factory
Azure Data Lake Storage Gen2
Power BI
Development / Monitoring / Management & Security

11. Modern Data Warehouse
Too many services/ workspaces
Difficult to monitor
Management & Security overhead
No Serverless option
Metadata not shared

12. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Azure
DatabricksADF Data Flows
Azure SQL Data
Warehouse
Azure Data
Factory
Azure Data Lake Storage Gen2
Power BI
Development / Monitoring / Management & Security

13. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Azure
Databricks
Synapse Data
Flows
Azure SQL Data
Warehouse
Synapse
Pipelines
Azure Data Lake Storage Gen2
Power BI
Development / Monitoring / Management & Security

14. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Synapse Data
Flows
Azure SQL Data
Warehouse
Synapse
Pipelines
Azure Data Lake Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool

15. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool

16. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool
Serverless SQL
Pool

17. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake
Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool
Serverless SQL
Pool
Meta store

18. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake
Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool
Serverless SQL
Pool
Meta storeSynapse Link

19. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake
Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool
Serverless SQL
Pool
Meta storeSynapse Link

20. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure Synapse Analytics
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake
Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool
Serverless SQL
Pool
Meta storeSynapse Link

21. Azure Synapse Analytics –Preview Features
Data Explorer Pool
Synapse Link for SQL Server 2022
Many more features

22. Azure Synapse Analytics
Azure Synapse Analytics is a limitless analytics servicethat brings together
data integration, enterprise data warehousing and big data analytics.

23. Create Synapse Analytics Workspace
Lab

24. Create Synapse Analytics Workspace

25. User Subscription
Azure Managed Resource GroupUser Managed Resource Group
Synapse Workspace
Primary ADLS Gen2
Storage Account
Role -Storage Blob
Data Contributor
Serverless SQL
Pool
Workspace
Container
SQL Admin User
Create Synapse Analytics Workspace

26. Project Overview
What is NYC Taxi
NYC Taxi data source & datasets
Prepare the data for the project
Project Requirements
Solution Architecture

27. Data Overview –NYC Taxi Trips

28. Data Overview –NYC Taxis
Yellow Taxis

29. Data Overview –NYC Taxis
Yellow Taxis
Green Taxis

30. Data Overview –NYC Taxis
Yellow Taxis
For Hire Vehicles
Green Taxis
High Volume For Hire Vehicles

31. Data Overview –NYC Taxis
Yellow Taxis
For Hire Vehicles
Green Taxis
High Volume For Hire Vehicles
https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page

32. Trip Data
Taxi ZoneTrip Type
Payment Type
Vendor
NYC Taxi Data Files Overview
Rate Code
Calendar

33. Trip Data
Taxi ZoneTrip Type
Payment Type
Vendor
NYC Taxi Data Files Overview
Rate Code
Calendar
CSV
CSV
TSV
JSON
JSON
CSV
Quoted
Parquet
CSV
Delta

34. Import NYC Taxi Data to Data Lake

35. Project
Requirements

36. Data exploration capability on the raw data
Discovery using pay-per-query model
Discovery using T-SQL
Schema applied to the raw data
Data Discovery

37. Ingested data to be stored as Parquet
Ingestion using pay-per-query model
Ability to query the ingested data using SQL
Ingested data to be stored as tables/ views
Data Ingestion

38. Join the key information required for reporting to
create a new table.
Transformed data must be stored in columnar
format (i.e., Parquet)
Must be able to analyze the transformed data
via T-SQL
Data Transformation
Join the key information required for Analysis to
create a new table.

39. Taxi Demand
Reporting Requirements
Credit Card Campaign
Operational Reporting

40. Scheduled to run at regular interval
Scheduling Requirements
Ability to monitor pipelines
Ability to re-run failed pipelines
Ability to set-up alerts on failures

41. Azure Synapse
Data
Visualization
Data
Integration
Storage
Solution Architecture
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake
Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool
Serverless SQL
Pool
Meta storeSynapse Link

42. Report
Solution Architecture –Serverless SQL Pool
Bronze
Layer
Silver
Layer
Gold
Layer
IngestTransformPresent
NYC Taxi
Data
Discovery
Analysts
Synapse Pipelines

43. Report
Solution Architecture –Spark Pool
Bronze
Layer
Silver
Layer
Gold
Layer
Present
NYC Taxi
Data
Discovery
Analysts
TransformIngest
Synapse Pipelines

44. Report
Solution Architecture –Dedicated SQL Pool
Bronze
Layer
Silver
Layer
Gold
LayerNYC Taxi
Data
Discovery
Analysts
TransformPresentIngest
Synapse Pipelines

45. Azure Synapse AnalyticsAzure Cosmos DB Container
Auto
Sync
Transactional Store
Row store optimized
for transactional
reads and writes
Analytical Store
Column store
optimized for
analytical queries
Spark Pool
Serverless SQL
Pool
Machine Learning
Bigdata Analytics
BI Reporting
Azure Synapse Link
/ HTAP
Solution Architecture -Synapse Link
Transactional
Data
Data Exploration

46. Azure Synapse
Data
Visualization
Data
Integration
Storage
Azure SynapseAnalytics
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake
Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool
Serverless SQL
Pool
Meta storeSynapse Link

47. Section Overview –Serverless SQL Pool
Serverless SQL Pool Architecture
Cost Control
Connecting to Azure Data Studio
T-SQL Support
Features & Use Cases

48. Serverless SQL Pool

49. Azure Synapse
Data
Visualization
Data
Integration
Storage
Compute
Synapse Data
Flows
Dedicated SQL
Pool
Synapse
Pipelines
Azure Data Lake
Storage Gen2
Power BI
Development / Monitoring / Management & Security
Spark Pool
Serverless SQL
Pool
Meta storeSynapse Link
Serverless SQL Pool

50. Serverless SQL Pool
Serverless SQL pool is a serverless distributed
query engine that you can use to query data over
your data lake using T-SQL.

51. Serverless SQL Pool
Serverless SQL pool is a serverlessdistributed
query engine that you can use to query data over
your data lake using T-SQL.

52. Serverless SQL Pool
Serverless SQL pool is a serverless distributed
query enginethat you can use to query data over
your data lake using T-SQL.

53. Compute
Polaris -Distributed
Query Processing
Engine
Azure Storage
Control Node
Compute NodeCompute NodeCompute Node
Serverless SQL Pool -Architecture
User or
Application
T-SQL
https://www.vldb.org/pvldb/vol13/p3204-saborit.pdf

54. Serverless SQL Pool –Key Features
Serverless
Distributed query engine
Query using T-SQL
Robust
Pay-per-query pricing model
Not a storage
Synapse Link
Query spark tables

55. Serverless SQL Pool –Supported Data Sources
SQL API
Parquet
Delta Lake
Cosmos
Dataverse
Azure Storage Account
SQL Server 2022 (Preview)
MongoDB API
Delimited –CSV, TSV etc
JSON

56. Serverless SQL Pool –Use Cases
Discovery & Exploration
Logical Data Warehouse
Data Transformation

57. Serverless SQL Pool –Who is it far?
Data Engineers
Data Scientists
Data Analysts / BI Developers

58. Serverless SQL Pool
Cost Management

59. Cost Calculation
Amount of data read from storage
Billed for Data ProcessedAmount of data in intermediate results
Amount of data written to storage
Data Processed rounded to the nearest MB
Minimum of 10MB per query
Currently $6.25 per 1TB

60. Cost Control

61. Serverless SQL Pool
Cost Management
Lab

62. Azure Data Studio

63. Section Overview -Query CSV Files
Header Row
Field Terminator
Row Terminator
Quoted Files and escaping characters

64. Query CSV Files
OPENROWSET function overview
Query using OPENROWSET function
Data Types & Collations
Query subset of columns
Quoted strings & Escape Char
Query Tab Separated Values (TSV) file
Serverless SQL
Pool

65. OPENROWSET Function
SELECT *
FROM OPENROWSET(BULK ‘blob file path‘,
FORMAT = [‘CSV’ |
'PARQUET’ |
‘DELTA’]
) AS [file]
Azure Storage
User/
Application
OPENROWSET
Select

66. Section Overview -Query CSV Files
Line Delimited JSON

67. Section Overview -Query CSV Files
Single Line JSON
Standard JSON

68. Section Overview -Query CSV Files
Single Line JSON
Standard JSON
Classic JSON

69. Query JSON Files
OPENROWSET
CSV Parser
JSON_VALUE
OPENJSON
Line-delimited JSON
FIELDTERMINATOR –0x0b
FIELDQUOTE–0x0b
Standard JSON
FIELDTERMINATOR –0x0b
FIELDQUOTE–0x0b
ROWTERMINATOR –0x0b

70. Serverless SQL Pool –T-SQL Support
Databases
Schemas
Stored Procedures
Views
Inline table value functions
External Resources –data sources, file
formats and tables
Supported

71. Serverless SQL Pool –T-SQL Support
Tables
Triggers
DDL statements other than ones related
to views and security
Materialized views
DML statements
Not Supported

72. Serverless SQL Pool –T-SQL Support
Logins and users
Credentials to control access to storage accounts
Grant, deny, and revoke permissions per object level
Azure Active Directory integration
Security

73. Serverless SQL Pool –T-SQL Support
CETAS -CREATE EXTERNAL TABLE AS SELECT
Extension to OPENROWSET to aid querying data in
data lake
Additional
Features

74. Serverless SQL Pool –Monitoring Queries
Lab

75. Discovery & Exploration
Lab

76. Serverless SQL –Data Discovery
Identify the volume of the data
Qualify of data
Duplicates
Missing values
Invalid data
Ability join datasets (e.g. keys exist)
Ability to get business value
Total record count
Record count per day/ Week/ Month
Right columns exist
Transformations
Aggregations
Identify additional data required

77. Serverless SQL –Data Discovery
Identify duplicates in data
Check for missing data values
Invalid/ Unexpected data in columns
Join data from multiple files
Summarize/ Aggregate data
Apply some transforms