Build data analytics solutions using Azure Synapse serverless SQL pools

Question 1

Azure Synapse Analytics

Answer 1

1. includes serverless SQL pools, which are tailored for querying data in a data lake
2. can use SQL code to query data in files of various common formats without needing to load the file data into database storage.

Question 2

Azure Synapse SQL

Answer 2

a distributed query system in Azure Synapse Analytics

Question 3

Azure Synapse SQL runtime environments

Answer 3

1. Serverless SQL pool
2. Dedicated SQL pool

Question 4

Dedicated SQL pool

Answer 4

Enterprise-scale relational database instances used to host data warehouses in which data is stored in relational tables

Question 5

Serverless SQL pool

Answer 5

1. primarily used to work with data in a data lake
2. pay-per-query endpoint to query the data in your data lake

Question 6

Benefits of using an SQL pool

Answer 6

1. A familiar Transact-SQL syntax to query data in place (no load)
2. Integrated connectivity from a wide range of business intelligence and ad-hoc querying tools
3. Distributed query processing that is built for large-scale data
4. Built-in query execution fault-tolerance - high success rate for long queries
5. No infrastructure to setup or clusters to maintain.
6. No charge for resources reserved, only for queries

Question 7

When to use serverless SQL pools

Answer 7

1. tailored for querying the data in the data lake
2. great for unplanned or “bursty” workloads
3. Workloads that require millisecond response times and are looking to pinpoint a single row in a data set are not good fit for serverless SQL pool.

Question 8

Common use cases for serverless SQL pools include:

Answer 8

1. Data exploration
2. Data transformation
3. Logical data warehouse.

Question 9

Logical data warehouse

Answer 9

1. can define external objects such as tables and views in a serverless SQL database.
2. emains stored in the data lake files, but are abstracted by a relational schema that can be used by client applications and analytical tools to query the data as they would in a relational database hosted in SQL Server

Question 10

File formats that can be queries

Answer 10

1. Delimited text, such as comma-separated values (CSV) files.
2. JavaScript object notation (JSON) files.
3. Parquet files.

Question 11

External data source

Answer 11

1. if you plan to query data in the same location frequently, it’s more efficient to define an external data source that references that location
2. benefit of an external data source, is that you can simplify an OPENROWSET query to use the combination of the data source and the relative path to the folders or files you want to query
3. ou can assign a credential for the data source to use when accessing the underlying storage, enabling you to provide access to data through SQL without permitting users to access the data directly in the storage account.

Question 12

External file format

Answer 12

encapsulate settings for delimited text files

CREATE EXTERNAL FILE FORMAT CsvFormat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS(
FIELD_TERMINATOR = ‘,’,
STRING_DELIMITER = ‘”’
)
);
GO

Question 13

External table

Answer 13

using the OPENROWSET function can result in complex code that includes data sources and file paths. To simplify access to the data, you can encapsulate the files in an external table

Question 14

Some delimiter file settings

Answer 14

1. With and without a header row.
2. Comma and tab-delimited values.
3. Windows and Unix style line endings.
4. Non-quoted and quoted values, and escaping characters.

Question 15

Persist the results of a query in an external table

Answer 15

1. CREATE EXTERNAL TABLE AS SELECT (CETAS)
2. an external table, stores its data in a file in the data lake.

Question 16

CETAS data sources

Answer 16

1. an existing table
2. view in a database
3. OPENROWSET function that reads file-based data from the data lake

Question 17

Types of objects to be created to use with CETAS

Answer 17

1. external data source
2. external data format

Question 18

External data source

Answer 18

1. encapsulates a connection to a file system location in a data lake
2. use this connection to specify a relative path in which the data files for the external table crea

Question 19

LOCATION and BULK parameters

Answer 19

relative paths for the results and source files respectively

NB relative to the file system location referenced by the files external data source.

Question 20

External table

Answer 20

external tables are a metadata abstraction over the files that contain the actual data. Dropping an external table does not delete the underlying files.

Question 21

Benefits of stored procedures

Answer 21

1. Reduces client to server network traffic (commands are executed in a single batch)
2. Provides a security boundary
3. Eases maintenance
4. Improved performance (execution plan is held in the cache and reused on subsequent runs)

Question 22

a pipeline for the data transformation enables you to

Answer 22

schedule the operation to run

1. at specific times
2. based on specific events

Question 23

A lake database

Answer 23

1. provides a relational metadata layer over one or more files in a data lake.
2. can create a lake database that includes definitions for tables, including column names and data types as well as relationships between primary and foreign key columns.
3. , the storage of the data files is decoupled from the database schema; enabling more flexibility than a relational database system typically offers.

Question 24

Lake database storage

Answer 24

1. stored in the data lake as Parquet or CSV files
2. can be managed independently of the database tables, making it easier to manage data ingestion and manipulation with a wide variety of data processing tools and technologies

Question 25

Lake database compute

Answer 25

1. Azure Synapse serverless SQL pool 2. Azure Synapse Apache Spark (Spark SQL API)

Question 26

Azure Synapse database designer

Answer 26

1. can define the schema for your database 2. Specifying the name and storage settings for each table. 3. Specifying the names, key usage, nullability, and data types for each column. 4. Defining relationships between key columns in tables.

Question 27

Serverless SQL pool authentication

Answer 27

how users prove their identity when connecting to the endpoint

Question 28

Two SQL Pool authentications

Answer 28

1. SQL Authentication (username and password) 2. Microsoft Entra authentication (managed by Microsoft Entra ID)

Question 29

Authorization

Answer 29

1. is controlled by your user account's database role memberships and object-level permissions.

Question 30

SQL Authentication

Answer 30

SQL user exists only in the serverless SQL pool and permissions are scoped to the objects in the serverless SQL pool. Access to securable objects in other services (such as Azure Storage) can't be granted to a SQL user directly since it only exists in scope of serverless SQL pool. The SQL user needs get authorization to access the files in the storage account.

Question 31

Microsoft Entra authentication

Answer 31

a user can sign in to a serverless SQL pool and other services, like Azure Storage, and can grant permissions to the Microsoft Entra user

Question 32

Serverless SQL pool supports the following authorization types (for Azure storage)

Answer 32

1. Anonymous access (publicly available files) 2. Shared access signature (SAS) 3. Managed Identity. (?) 4. User Identity (?)

Question 33

Shared access signature (SAS)

Answer 33

1. delegated access to resources in storage account 2. can grant clients access to resources in storage account, without sharing account keys 3. gives you granular control over the type of access you grant to clients who have the SAS: validity interval, granted permissions, acceptable IP address range, acceptable protocol

Question 34

Azure Storage Access Control Types

Answer 34

1. Azure role-based access control (Azure RBAC) 2. Access control lists (ACLs)

Question 35

ACL

Answer 35

1. Each file and directory in your storage account has an access control list 2. ACL check determines whether that security principal has the correct permission level to perform the operation.

Question 36

Kinds of access control lists:

Answer 36

1. Access ACLs: access to an object 2. Default ACLs: emplates of ACLs associated with a directory that determine the access ACLs for any child items that are created under that directory. Files do not have default ACLs.

Question 37

Permission types

Answer 37

1. Read 2. Write 3. Execute

Question 38

Guidelines in setting up ACLs

Answer 38

1. Always use Microsoft Entra security groups as the assigned principal in an ACL entry 2. Resist the opportunity to directly assign individual users or service principals

Question 39

Roles: For users which need read only acces

Answer 39

Storage Blob Data Reader