Azure

Question 1

What are the four benefits of Azure Data Lake Storage Gen2?

Answer 1

• Hadoop compatible access (data can be treated as if it’s stored in HDFS, can be saved in one place and accessed by Databricks, HDInsights, and Synapse Analytics)
• Security (supports access control lists (ACLs) and Portable Operating System Interface (POSIX) permissions that don’t inherit the permissions of the parent directory)
• Performance (data processing requires less computational resources, because the data is stored in a hierarchy of directories
• Data redundancy (takes advantage of the Azure Blob replication models that provide data redundancy in a single data center with locally redundant storage (LRS), or to a secondary region by using the Geo-redundant storage (GRS) option)

Question 2

How do you enable Azure Data Lake Storage Gen2?

Answer 2

Azure Data Lake Storage Gen2 isn’t a standalone Azure service, but rather a configurable capability of a StorageV2 (General Purpose V2) Azure Storage.
To enable Azure Data Lake Storage Gen2 in an Azure Storage account, you can select the option to ‘Enable hierarchical namespace’ in the Advanced page when creating the storage account in the Azure portal

Question 3

What’s the difference between Azure Blob Storage and Azure Data Lake Storage Gen2?

Answer 3

In Azure Blob storage, you can store large amounts of unstructured (“object”) data in a flat namespace within a blob container. Whereas Azure Data Lake Storage Gen2 builds on blob storage and optimizes I/O of high-volume data by using a hierarchical namespace that organizes blob data into directories, and stores metadata about each directory and the files within it

Question 4

When should Hierarchical Namespace/Azure Data Lake Storage Gen2 be enabled

Answer 4

When analysis will be performed on the data

Question 5

What is Azure Synapse Analytics?

Answer 5

It provides a cloud platform for all of analytical workloads (descriptive, diagnostic, predictive, prescriptive) through support for multiple data storage, processing, and analysis technologies in a single, integrated solution

Question 6

When should Azure Synapse Analytics be used?

Answer 6

• Large scale data warehousing
• Advanced analytics (native features and Azure Machine Learning)
• Data exploration and discovery (using serverless SQL pool functionality)
• Real time analytics (with Azure Synapse Link, Azure Stream Analytics, and Azure Data Explorer)
• Data integration (Azure Synapse Pipelines)
• Integrated analytics (integration of the analytics landscape into one service)

Question 7

What is KQL?

Answer 7

Kusto Query Language. A query language similar to SQL that is optimized for data that includes a time series component, such as realtime data from log files or IoT devices

Question 8

What are the two runtime environments provided by Azure Synapse SQL?

Answer 8

• Serverless SQL pool (on-demand SQL query processing, primarily used to work with data in a data lake)
• Dedicated SQL pool (Enterprise-scale relational database instances used to host data warehouses in which data is stored relational tables)

Question 9

What are common use cases for serverless SQL pools?

Answer 9

• Data exploration
• Data transformation
• Logical data warehouses (to define external objects such as tables and views in a serverless SQL database)

Question 10

What SQL function is used to query common data file formats (csv, json, parquet)?

Answer 10

OPENROWSET

Question 11

What is a CETAS statement and where is it used?

Answer 11

CREATE EXTERNAL TABLE AS SELECT. It can be used in a dedicated SQL pool or serverless SQL pool to persist the results of a query in an external table, which stores its data in a file in the data lake

Question 12

Why is encapsulating a data transformation in stored procedure a good practice?

Answer 12

This approach makes it easier to operationalize data transformations through parameters, retrieval of outputs, and possibility to include additional logic in a single procedure call

Question 13

What are the benefits of stored procedures?

Answer 13

• Encapsulates of Transact-SQL logic
• Reduces network traffic (procedure is executed as a single batch of code)
• Provides a security boundary (users with no direct permission can still use it)
• Eases maintenance (changes are only applied to stored procedure)
• Improves performance (executing plan is held in cache)

Question 14

What is a lake database?

Answer 14

It provides a relational metadata layer over one or more files in a data lake. You 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. The tables reference files in the data lake, enabling you to apply relational semantics to working with the data and querying it using SQL. However, the storage of the data files is decoupled from the database schema; enabling more flexibility than a relational database system typically offers