Apache Spark

Cache data into the memory of local executor

  • .cache() is used to cache data into the memory of local executor.
    • It allows for faster access to the cached data during subsequent operations without having to recompute or reload it from the source.

Spark Pool Clusters

  1. Spark Pools clusters are groups of computers that are treated as a single computer and handle the execution of commands issued from notebooks.

View Active Spark Streams

  1. Invoke spark.streams.active to view all the active Spark Streams in the spark application.

Data Shuffling

There are two control knobs of a shuffle that can used to optimise in Spark Job:

  1. spark.sql.shuffle.partitions. It controls the number of partitions being shuffled.
  2. spark.default.parallelism. It controls default parallel tasks that Spark will use for operation.

Azure Cosmos DB

NoSQL Database to support API models at planet scale with low latency performance

  1. Azure Cosmos DB is a globally distributed, multi-model database service that supports NoSQL databases with low latency performance at planet scale.
  2. It provides support for multiple APIs, including SQL, MongoDB, Cassandra, Gremlin, and Table.
  3. Azure Cosmos DB supports five-nines uptime (99.999%) and support response time below 10ms when its provisioned correctly.
  4. By using multi-master replication in Azure Cosmos DB, Azure Cosmos DB guarantee to achieve a response time of less than 10ms for reads and writes across the globe.

Schema Representation

  1. For SQL (Core) API accounts, the default schema representation is Well-Defined schema, meaning that the schema is explicitly defined and structured.
  2. For Azure Cosmos DB API for MongoDB accounts, the default schema representation is Full Fidelity schema, which ensures that the schema is preserved with all its details intact.

Azure Databricks

Key Components

  1. Apache Spark Clusters.
    1. Apache Spark Cluster in Azure Databricks are distributed data processing solution that uses clusters to scale processing across multiple compute nodes.
    2. Each cluster consists of a drive node to coordinate processing jobs, and one or more worker nodes where the processing occurs.
    3. This distributed model allows each node to work on a subnet of the job in parallel, reducing the overall job completion time.
  2. Databricks File System (DBFS).
    1. While each cluster node has its own local file system, the nodes in a cluster have access to a shared, distributed file system in which they can access and operate on data files.
    2. The Databricks File System (DBFS) enables you to mount cloud storage and use it to work with and persist file-based data.
  3. Notebooks.
    1. Notebooks provides an interactive environment in which you can combine text and graphics in Markdown format with cells containing code that you run interactively in the notebook session.
    2. The primary languages supported by Azure Databricks Notebooks are: Python, Scala, R, and SQL.
    3. In Databricks, the notebook interface acts as the driver program.
      1. This driver program contains the main loop for the program and creates distributed datasets on the cluster, then applies operations (transformations & actions) to these datasets.
  4. Hive Metastore (Unity Catalog).
    1. Hive Metastore is to store all the table definitions and details of the file system locations.
    2. A Hive metastore is created for each cluster when it’s created, but you can configure a cluster to use an existing external metastore if necessary.
  5. Delta Lake.
    1. Delta Lake is an open-source storage layer that brings ACID transactions, scalable metadata handling, and unified streaming and batch data processing.
    2. Delta Lake builds on the relational table schema abstraction over files in the data lake to add support for SQL semantics commonly found in relational database systems.
    3. Capabilities provides by Delta Lake include transaction logging, data type constraints, and the ability to incorporate streaming data into a relational table.
  6. SQL Warehouse.
    1. SQL Warehouse are relational compute resources with endpoints that enable client applicants to connect to an Azure Databricks workspaces and use SQL to work with data in tables.

Capacity Limits

  1. Maximum number of jobs that a workspace can create in an hour is 1000.
  2. Maximum number of concurrent jobs running in a workspace is 150.
  3. Maximum number of notebooks or execution context attached to a cluster is 150.
  4. Maximum number of Databricks API calls per hour is 1500.

Workspace Security

  1. Microsoft recommends that you should only deploy one workspace in any VNetto enable proper security and isolation between workspaces.
  2. Deploying multiple workspaces in the same VNet can lead to potential security vulnerabilities and conflicts.

Network & Network Security

  1. Azure VNet service endpoints.
    1. VNet service endpoints extend your virtual network private address space.
    2. Endpoints allow you to secure your critical Azure service resources to only your virtual networks.
  2. VNet Injection.
    1. You can deploy the Azure Databricks within your own VNet.
    2. Need to select the Deploy Azure Databricks workspace in your own VNet option.
  3. VNet Peering.
    1. VNet Peering allows the virtual network in which your Azure Databricks resource is running, to peer with another Azure VNet.
    2. Traffic between virtual machines in the peered virtual networks is routed through the MS backbone infra, much like traffic is routed between virtual machines in the same VNet, through private IP address only.
  4. Azure Private Link.
    1. Private Link enables you to access Azure PaaS services (e.g. Azure Storage, Azure Cosmos DB) and Azure hosted customer services over a Private Endpoint in your VNet.
    2. All the traffic will through MS backbone infra, and not be routed to public internet.

Azure Databricks Cluster Types

  1. Single Node.
    1. Single Node cluster mode is used when only one node is required for processing tasks.
    2. It is suitable for small workloads or testing purposes where a single node is sufficient.
  2. Standard (Multi-node).
    1. Standard cluster is suitable for general-purpose workloads and provide a balance between performance and cost.
    2. It is common choice for most use cases in Azure Databricks.
  3. High Concurrency.
    1. High Concurrency is designed to handle multiple users and workloads simultaneously.
    2. It is optimised for scenarios where there is a need for high concurrency and resource sharing.

Azure Data Factory

Azure Data Factory Core Concepts

  1. Data Set.
    1. Data Set is basically collected data users required which are used as input for the ETL process.
    2. Data sets have different formats, they can be in JSON, CSV, OCR, or text format.
  2. Activity.
    1. Activity is a specific action performed on the data in a pipeline like the transformation or ingestion of the data.
    2. Each pipeline can have one or more activities in it.
  3. Pipeline.
    1. Pipeline is created to perform a specific task by composing the different activities in the task in a single workflow.
    2. Activities in the pipeline can be data ingestion (Copy data to Azure) -> Data Processing (Perform Hive Query).
  4. Linked Service.
    1. Linked Service has information on the different data sources, and the data factory uses this information to connect to the originating data sources.
    2. It is mainly used to locate the data stores in the machines, and also represent the compute services for the activity to be executed.
    3. Parameterising a linked service in Azure Data Factory allows for the dynamic passing of values at runtime, eliminating the need to create a separate linked service for each database on the same SQL Server.

Global parameters

  1. Setting Global Parameters allows you to use constants for consumption in pipeline execution.
    1. A user-case for setting global parameters is when you have multiple pipelines where the parameters names and values are identical.
  2. When using Global Parameters in a pipeline in Azure Data Factory, it is mostly referenced in pipeline expression.
    1. The command or reference of global parameters in Azure Data Factory is: pipeline().globalParameters.
  3. When you integrate Global Parameters in a Pipeline using CI/CD with Azure Data Factory, there are two ways to achieve that:
    1. Include Global Parameters in the Azure Resource Manager (ARM) template.
    2. Deploy Global Parameters via a PowerShell script.
    3. In most CI/CD practices, it’s beneficial to include global parameters in the ARM template.

Interact with Data Factory using Programming Languages

  1. Azure Resource Manager (ARM) templates
  2. .Net
  3. RESTful APIs
  4. PowerShell
  5. Python

Linked Service

  1. Linked service in Azure Data Factory is responsible for providing information on data sources and compute services.
  2. It allows Data Factory to connect data sources, and represent compute services for executing activities like running Spark jobs or Hive queries.

Diagnostic Logs

  1. Azure Data Factory retain diagnostic logs for a default period of 45 days.
  2. However, Azure Monitor can keep the diagnostic logs for 730 days (24 months).

Integration Runtime

  1. Integration Runtime is referenced by the linked service or activity, and provides the compute environment where the activity either runs on or get dispatched from.
    1. Data Flow
    2. Activity dispatch
    3. SSIS Package Execution
    4. Data Movement
  2. It provides the compute infrastructure used by Azure Data Factory.
  3. Data Factory offers three types of Integration Runtime (IR):
    1. Azure.
    2. Self-hosted.
    3. Azure-SSIS.
  4. All three types of Integration Runtime (IR) in Azure Data Factory support private network connectivity.

Azure Data Lake Gen2 (ADLS)

Staging Area

  1. Azure Data Lake is typically used as a staging area in a modern data warehousing architecture, due to its ability to store large amounts of structured and unstructured data in its native format.
  2. It provides a cost-effective solution for data storage and processing before loading it into a data warehouse.

Processing Analytical Data workloads for best Performance

  1. On the Advanced Tab, set the Hierarchical Namespace to Enabled.
    1. This allows for efficient data organisation and management by providing a hierarchical structure that improves data processing capabilities.
    2. Similar to Data Partitioning.

Azure Event Hubs

Options for an input to Azure Stream Analytics job with low latency and high throughput

  1. Azure Event Hubs is highly scalable data streaming platform and event ingestion service that can handle millions of events per second.

Azure Storage

Storage Account Name

  1. Azure Storage Account name has to be globally unique.

Storage Analytics

  1. Storage Analytics logs every operation in Azure Storage Account activity in real-time.
  2. It provides detailed insights into the usage and performance of the storage account.
  3. It allows users to search the logs for specific requests and filter based on various criteria, such as authentication mechanism, operation success, and accessed resources.

Storage RESTful API

  1. Azure Storage provides a RESTFUL API to work with the containers and data stored in each account.
  2. To work with data in a storage account, your app will need: Access Key, and RESTFUL API Endpoint.

Access data in Storage Account with REST API

  1. Access key.
  2. REST API Endpoint.

Log every operation in Azure Storage Account activity in real-time

  1. Storage Analytics logs every operation in Azure Storage Account activity in real-time, providing detailed insights into the usage and performance of the storage account.
  2. It allows users to search teh logs for specific requests and filter based on various criteria, such as authentication mechanism, operation success, and accessed resources.

Azure Synapse Analytics

What is Azure Synapse Analytics?

  1. Azure Synapse Analytics is a unified analytics service that provides capabilities for running data pipelines and managing analytical data in a data lake or relational data warehouse.
  2. It combines big data and data warehousing into a single service, making it a suitable choice for data engineering workloads.

Common Synapse Use cases

  1. Modern Data Warehousing
    1. Store and analyse large volumes of structured and unstructured data efficiently.
  2. Advanced Analytics
    1. Provides tools and capabilities for running complex analytics, machine learning, and AI workloads on large datasets.
  3. Integrated Analytics
    1. Enables organisations to bring data together from different sources, and perform analytics, machine learning, and AI on unified platform.
  4. Real-time Analytics
  5. Data Integration.
  6. Integrate data from various sources, transforming it, and loading it into a data warehouse for analysis.

Core Components

  1. Synapse SQL. Serverless and dedicated SQL pools for querying and data warehousing.
  2. Apache Spark for Synapse. Distributed big data processing using Spark.
  3. Synapse Pipelines. Data Integration and orchestration for ETL/ELT processes.
  4. Synapse Studio. Unified interface for managing, querying, and processing data.
  5. Data Explorer. High-performance log and time-series analytics.
  6. Data Lake Storage Integration. ADLS Gen2 for unified data storage.
  7. Linked Services. Integration with Power BI, Azure ML, and other Azure services.
  8. Power BI Integration. For real-time visualisation and reporting.

Faster Load Time

  1. Azure Synapse Analytics allows you to create, control, and manage resource availability when workloads are completing.
  2. To facilitate faster load times, you can create a workload classifier for the load user with the Importance set to above_normal or High.

Materialised Views

  1. Materialised Views are pre-computed views that store the results of a query in a physical table.
  2. They are commonly used to improve query performance by reducing the need to recompute complex queries with joins and filters every time they are executed.
  3. As the data in the underlying table changes, the data in the materialised view will automatically update without user interaction.
  4. Materialised View is not supported by Azure Synapse Analytics.

Materialised Views Restrictions

  1. The SELECT statement used to create the Materialised View needs to meet following two criteria:
    1. The SELECT list contains an aggregation function, e.g. AVG, COUNT, MIN, MAX, SUM, COUNT_BIG, VAR, STDEV.
    2. GROUP BY is used in the materialised view definition, and all the columns in the GROUP BY clause are included in the SELECT list.
  2. Materialised Views are not supported in Azure Synapse Analytics.

Azure Synapse SQL

Azure Synapse SQL

  1. Azure Synapse SQL offers both serverless and dedicated resource models for working with descriptive and diagnostic analytics scenarios.
  2. It enables the implementation of data warehousing and data visualisation scenarios using standard T-SQL, making it a crucial component for Azure Synapse Analytics.

Azure Synapse Dedicated SQL Pool

  1. Dedicated SQL Pool represents a collection of analytic resources that are provisioned when using Synapse SQL.
  2. The size of dedicated SQL Pool is determined by the number of data warehouse units (DWU).
  3. Dedicated SQL Pool stores data in relational tables with columnar storage.
    1. This format reduces the data storage costs, and improves query performance.
  4. Serverless SQL pool supports to use CSV format or Parquet format to store query results.
  5. This is similar to AWS Redshift.
  6. You can also use Azure Synapse SQL Link for SQL to replicate data from SQL Server 2022 or Azure SQL Database, to a dedicated SQL Pool with low latency.
    1. This feature allows for near-real-time analysis of operational data without impacting the transactional data store significantly.

Azure Synapse Serverless SQL Pool

  1. Serverless SQL pool is a query service over the data in your data lake. It enables you to access your data through:
    1. A familiar T-SQL syntax to query data in the place without the need to copy or load data into a specialised zone.
    2. Integrated connectivity via the T-SLQ interface that offers a wide range of BI and ad-hoc tools, and popular drivers.
  2. Serverless SQL pool is serverless, hence there is no infrastructure to setup or clusters to manage. A default endpoint for this service is provided within every Azure Synapse workspace, so you can start querying data as soon as the workspace is created.
  3. This is similar to AWS Athena or AWS Redshift Spectrum.
  4. LOCATION = 'folder_or_filepath' specifies the folder or file path and file name for the actual data files in Azure Blob Storage.
    1. The location starts from the root folder, and root folder is the data location specified in the external data source.
    2. Unlike Hadoop external tables, native external tables doesn’t return subfolder unless you specify /** at the end of the path.
  5. In a Serverless SQL pool, if statistics are missing, the query optimiser does not create statistics on entire table in query predicate or join conditions. It uses dynamic sampling to generate cardinality estimates for query optimisation.

Azure Synapse Pipeline

Operationalise Data Flow Activities

  1. Flow capabilities.
    1. This refer to the ability to design and configure the data flow logic within Azure Synapse pipelines, including defining the transformation, aggregation, and data manipulation that need to be performed on the data as it flows through the pipeline.
  2. Data Factory scheduling.
    1. This allows you to schedule and orchestrate the execution of data flow activities within Azure Synapse pipelines, to define when and how often the data flows should run.
  3. Control capabilities.
    1. This enable you to manage and control the execution of data flow activities, including defining dependencies between different data flows, setting up triggers for automated execution, and handling error and retries.
  4. Monitoring capabilities.
    1. This allows you to track the performance and status of data flow activities in real-time, like monitoring data flow execution, identifying bottlenecks or issues, and ensuring data processing is running smoothly.

Azure Synapse Studio

Azure Synapse Studio - Developer Hub

  1. In Azure Synapse studio, the Developer Hub is where you manage SQL scripts, Synapse Notebooks, data flows, and Power BI reports.

Azure Synapse Resource Model Comparison

Dedicated SQL PoolServerless SQL Pool
SELECTYes. but FOR XML, FOR JSON and MATCH are not supported.Yes. but FOR XML, FOR JSON and MATCH are not supported.
INSERTYesNo
UPDATEYesNo
DELETEYesNo
MERGEYesNo
CTAS statementYesNo, CTAS is not supported in the Serverless SQL pool
CETAS statementYesYes, CETAS supports Parquet and CSV output formats.
TransactionsYesYes
Data LoadYes, prefer using COPY statement, but supports both BULK load (BCP) and CETASNo, cannot load data into Serverless SQL pool. You can initially load data into an external table using CETAS statement
Data ExportYes, using CETASYes, can export data from external storage into Azure Data Lake using CETAS
Cross-database queriesNoYes, cross-database queries and the 3-part-name references are supported. Cross-workspace queries are not supported.

Resource Monitoring

  1. In an Apache Spark Pool with autoscale enabled, Synapse Analytics monitors resource load by checking metrics such as CPU usage, memory and node performance every 30 seconds to make scaling decisions.

Common Performance Optimisation Techniques

  1. Choose the data abstraction
  2. Use the optimal data format
  3. Use the cache option
  4. Check the memory efficiency
  5. Use Bucketing
  6. Optimise Joins and Shuffles if appropriate
  7. Optimise Job Execution

Table Bucketing

  1. Table Bucketing involves dividing the data into a set number of buckets based on the hash of the column(s) you choose as the “bucketing key”.
  2. The hash determines which bucket the data will go into.
  3. Unlike partitioning, which divides the data into separate files based on a range of values, bucketing distributes rows across a fixed number of files (buckets) evenly by hashing.
  4. Recommended Practices
    1. Move joins that increase the number of rows after aggregations.
      1. This means to do aggregation first, and then do the join.
      2. By performing aggregation before joining, you can reduce the amount of data being processed and improve query efficiency.
    2. The order of the different type of joins does matter when it comes to resource consumption.
      1. Choosing the most efficient join type based on the data distribution and query requirements can help optimise performance.
    3. Avoid the use of SortMerge join when possible.
      1. SortMerge can be resource-intensive and may impact the performance of queries on bucketed tables.

Azure Advisor

Azure Advisor Recommendations

  1. Cost Effectiveness
  2. Reliability (High Availability)
  3. Performance
  4. Security
  5. Operational Excellence

General

Data Engineer

A Data Engineer is responsible for

  1. provisioning and setting up data platform pipelines
  2. managing and securing the flow of structured and unstructured data
  3. ensuring seamless integration with other data platform technologies or application services
  4. They work with various data sources and platforms, including relational databases, non-relational databases, data streams, and file stores.

RPO, RTO, TCO

  • Recovery Point Object (RPO) refers to maximum tolerable amount of data loss that an organisation is willing to accept during a disaster or disruption.
  • Recovery Time Object (RTO) refers to the targeted duration of time within which a business process must be restored after a disaster or disruption.
  • Mean Time to Detect (MTD) refers to the average time taken to detect an issue or incident within a system or process.
  • Total Cost of Ownership (TCO) describes the final cost of owning a given technology.
    • It includes hardware, software licensing, labour, and datacenter overhead costs in on-premises systems.
    • TCO is essential for evaluating the overall cost effectiveness of technology investments.

CI/CD

  1. CI: Continuous Integration
  2. CD: Continuous Deployment & Continuous Delivery
    1. Continuous Delivery focuses on automating the software delivery process to ensure that code changes can be safely and quickly released to production.
    2. Continuous Deployment goes a step further by automatically deploying code change that passes automated tests to production, making the release process even faster and more efficient.

Access Control

  1. Role-based Access Control (RBAC) allows you to assign permissions to specific roles within your Azure Synapse environment.
    1. RBAC works at the Azure Synapse workspace level, and controls access to resources like databases and tables based on roles.
  2. Row-Level Security (RLS) goes a step further by restricting access to rows within a table based on the user’s role.
    1. For example, doctors and nurses would only be able to see rows of data containing medical records, while billing staff would not be able to see these rows at all.
    2. RLS is implemented through security predicates, which are conditions added to the database table to filter data based on the user’s role or identity.

HDInsight

  1. HDInsight is Microsoft’s implementation of Open Source Spark managed on the Azure platform.
  2. It provides an SLA for users who are familiar with Apache Spark but require additional support and reliability from a managed service on Azure.
  3. Similar to AWS EMR.

OPENROWSET

  1. OPENROWSET is a SQL function that allows you to read data from external data sources, including files stored in a data lake.
  2. It can be used to query and explore data in various file formats within the data lake, it supports CSV, Parquet, and Delta formats.

Truncate vs Delete

  1. Executing a DELETE statement where the value in the Date column is more than 36 months ago will be a slower process compared to TRUNCATE the partitions.
  2. TRUNCATE is a faster process compared to DELETE because it directly removes the data from the specified partitions without the need to log each individual row deletion.
  3. DELETE statement, on the other hand, scans the table for each row and logs the deletion of each row, which can be a slower process for large tables with many rows.

Tumbling Window vs Sliding Window vs Hopping Window

  1. Tumbling Window: No-overlapping windows, good for distinct, periodic batch-like processing where you want data grouped into discrete windows.
    1. Example: Tumbling windows with a duration of 5 minutes:
      1. Window 1: 00:00 - 00:05
      2. Window 2: 00:05 - 00:10
      3. Window 3: 00:10 - 00:15
  2. Sliding Window: Overlapping windows, suitable for real-time monitoring and calculating rolling aggregates or moving averages, where frequent updates are needed.
    1. Example: Sliding window with a duration of 5 minutes, and slide interval of 2 minutes:
      1. Window 1: 00:00 - 00:05
      2. Window 2: 00:02 - 00:07
      3. Window 3: 00:04 - 00:09
    2. Windows overlap because the slide interval (1 minutes) is smaller than the window duration (5 minutes)
  3. Hopping Window: Similar to sliding window, but windows “hop” forward at regular intervals (e.g. every 2 minutes), allowing for periodic updates over overlapping time periods.
    1. Example: Hopping window with a duration of 5 minutes and a hop size of 2 minutes:
      1. Window 1: 00:00 - 00:05
      2. Window 2: 00:02 - 00:07
      3. Window 3: 00:04 - 00:09
    2. Window hop forward every 2 mins, which results in overlapping windows similar to sliding window.