Please enable JavaScript.

Coggle requires JavaScript to display documents.

DE on Azure - Coggle Diagram

- - - - What is happening
    - - Why is it happening
    - - What is likely to happen in the future based on previous trends and patterns?
    - - autonomous decision making based on real-time or near real-time
  - - - core resources in a Synapse Analytics workspace is a data lake
        
        in which data files can be stored and processed at scale
    - - a linked service to an ADLS Gen2 container
      - Data tab
    - - create, run, manage pipelines (orchestrate)
      - Integrate tab
    - - Develop tab
      - notebooks
    - - Azure Synapse Link
      - Power BI
      - Mcs Purview
      - Azure ML
  - - - two kinds of runtime environments
        
        dedicated SQL pool
        
        host data warehouses
        
        data is stored in relational tables
        
        Enterprise-scale relational database instances
        
        to create Data Warehouses
        
        DWH schema
        
        is optimized for multidimensional modeling
        
        table type
        
        DIM
        
        describe business entities, such as products, people, places, and dates
        
        two key
        
        2 more items...
        
        Why have two keys
        
        2 more items...
        
        FACT
        
        store details of observations or events
        
        for example, sales orders, stock balances, exchange rates, or recorded temperatures.
        
        contains
        
        2 more items...
        
        staging tables
        
        schema designs
        
        star schema
        
        snowflake schema
        
        Data integrity constraints
        
        Indexes
        
        default index type is clustered columnstore.
        
        Distribution
        
        Hash
        
        2 more items...
        
        Round-robin
        
        2 more items...
        
        Replicated
        
        2 more items...
        
        Process to load data into DBW
        
        Step1: Ingest the new data to be loaded into a data lake, applying pre-load cleansing or transformations as required.
        
        Step2: Load the data from files into staging tables in the relational data warehouse.
        
        Step3:Load the dimension tables from the dimension data in the staging tables, updating existing rows or inserting new rows and generating surrogate key values as necessary.
        
        Step 4: Load the fact tables from the fact data in the staging tables, looking up the appropriate surrogate keys for related dimensions.
        
        Step 5: Perform post-load optimization by updating indexes and table distribution statistics.
        
        Load slowly changing dimensions
        
        Ranking funcs
        
        ROW_NUMBER
        
        1, 2,3
        
        RANK
        
        1,2,2,4
        
        DENSE_RANK
        
        1,2,2,3
        
        Manage and monitor data warehouse activities
        
        Scale compute resources
        
        data warehouse unit
        
        the unit of scale is an abstraction of compute power
        
        Compute is separate from storage
        
        scale up and scale down
        
        Pause compute
        
        manually
        
        auto - set the number of minutes idle
        
        Manage workloads
        
        create, control and manage resource availability
        
        dedicated SQL pool workload management: 3 high-level concepts
        
        Workload Classification
        
        4 more items...
        
        Workload Importance
        
        2 more items...
        
        Workload Isolation
        
        2 more items...
        
        Azure Advisor to review recommendations
        
        Azure Advisor
        
        provide info on best practices to optimize the set up of Azure services
        
        help improve
        
        5 more items...
        
        free
        
        The telemetry data captured by Synapse
        
        Data Skew & replicated table info
        
        Column statistic
        
        TempDB utilization
        
        Adaptive Cache
        
        Dynamic Management Views
        
        identify and troubleshoot query performance
        
        provide a programmatic experience for monitoring Synapse SQL pool
        
        SELECT * FROM dynamic_view
        
        sys.dm_pdw_exec_sessions
        
        sys.dm_pdw_exec_requests
        
        1 more item...
        
        ....
        
        secure
        
        network security
        
        Firewall rules
        
        define a traffic is allowed or denied
        
        configured at the workspace level
        
        apply to all public endpoints of workspaces, including
        
        3 more items...
        
        virtual networks
        
        VNet (Azure virtual network)
        
        private endpoints
        
        Conditional Access
        
        Authen
        
        Author
        
        column & row level security
        
        Dynamic Data Masking
        
        sensitive data
        
        encryption
        
        serverless SQL pool
        
        use cases
        
        are tailored for querying data in a data lake
        
        without needing to load the file data into database storage
        
        various common formats
        
        JSON, CSV, parquet
        
        use SQL code to query data in files
        
        unplanned or "bursty" workloads that can be processed using the always-on serverless SQL endpoint
        
        Data exploration involves browsing the data lake to get initial insights about the data
        
        enables you to perform SQL-based data transformations; either interactively or as part of an automated data pipeline.
        
        Logical data warehouse: define external objects such as tables and views in a serverless SQL database.
        
        on-demand SQL query processing
        
        benefits
        
        familiar Transact-SQL syntax
        
        without the need to copy or load data into a specialized store
        
        Distributed query processing for large-scale data, and computational functions -> fast query performance
        
        fault-tolerance, resulting in high reliability and success rates even for long-running queries
        
        No infrastructure to setup or clusters to maintain.
        
        No charge for resources reserved,
        only charged for the data processed by queries
        
        Secure data and manage users
        
        authentication method
        
         SQL Authentication
        
         Microsoft Entra authentication
        
        authorization
        
        o Anonymous access
        
        o Shared access signature (SAS)
        
        o Managed Identity
        
        o User Identity
        
        user permissions
        
         Azure role-based access control (Azure RBAC)
        
         ACLs like Portable Operating System Interface for Unix (POSIX)
        
        o Access ACLs
        
        o Default ACLs
    - - Types
        
        sparkxxxxxxx
        
        use on-demand to explore or process data in a data lake
        
        using programming languages like Scala or Python
        
        adxxxxxxxx
        
        use to analyze data
        
        Kusto Query Language (KQL)
      - Operations
        
        Running Spark code in notebooks
        
        work with data from various sources, including
        
        data lake based on the primary storage account for the Azure Synapse Analytics workspace
        
        data lake based on storage defined as a linked service in the workspace
        
        dedicated or serverless SQL pool in the workspace
        
        Azure SQL or SQL Server database
        
        Azure Cosmos DB
        
        Azure Data Explorer Kusto database
        
        external Hive metastore
        
        visualize data byy built-in notebook charts
      - About Apache Spark
        
        Spark is an open source parallel processing framework for large-scale data processing and analytics
        
        Spark Context
        
        converting an application to a directed acyclic graph (DAG)
        
        coordinates Spark applications in your main program (called the driver program)
        
        SparkContext connects to the cluster manager
      - a runtime for Spark operations
      - Config
        
        name for the spark pool
        
        size of virtual machine (VM)
        
        version of the Spark Runtime
        
        number of nodes in the pool
      - Partition data files
        
        an optimization technique
        
        More performance gains can be achieved when filtering data in queries by eliminating unnecessary disk IO
        
        partitionBy method
      - Table
        
        Table definitions in Spark are stored in the metastore
        
        metastore, a metadata layer that encapsulates relational abstractions over files.
        
        external table
        
        External tables are "loosely bound" to the underlying files and deleting the table does not delete the files
        
        External tables are relational tables in the metastore that reference files in a data lake location that you specify.
        
        managed table
        
        Managed tables are "tightly-bound" to the files, and dropping a managed table deletes the associated files.
        
        managed tables, for which the underlying data files are stored in an internally managed storage location associated with the metastore
  - - - can use to implement a data lakehouse architecture in Azure Synapse Analytics.
      - an open-source storage layer
      - adds relational database semantics to Spark-based data lake processing
    - - Relational tables that support querying and data modification (CRUD)
      - Support for ACID transactions.
        
        atomicity (transactions complete as a single unit of work)
        
        consistency (transactions leave the database in a consistent state)
        
        isolation (in-process transactions can't interfere with one another)
        
        durability (when a transaction completes, the changes it made are persisted)
      - Data versioning and time travel
      - Support both batch and streaming data.
      - Standard formats and interoperability.
  - - - data movement
      - processing
  - - - workload group
      - workload classifier
    - - influences the order in which a request gets access to resources
      - • a request with higher importance has first access to resources
      - Five levels of importance
        
        • low,
        
        • below_normal,
        
        • normal, (default)
        
        • above_normal,
        
        • and high
    - - Azure Advisor provides you with personalized messages that provide information on best practices to optimize the setup of your Azure services
      - provides recommendations in the following areas
        
        Cost
        
        Security
        
        Reliability
        
        Operational excellence
        
        Performance
    - - get info about
        
        Connection information and activity
        
        SQL execution requests and queries
        
        Index and statistics information
        
        Resource blocking and locking activity
        
        Data movement service activity
        
        Errors
- - - - establishing links between operational and analytical services and data sources
      - enable secure, reliable access to data across multiple systems.
    - - ETL
      - ELT
    - - process of combining data into a consistent structure
      - usually to support analytics and reporting
  - - - Operational data is usually transactional data that is generated and stored by applications
      - Analytical data is data that has been optimized for analysis and reporting, often in a data warehouse.
    - - data that generate data values in real-time, often relating to specific events.
    - - to orchestrate activities that transfer and transform data.
      - implement repeatable ETL solutions that can be triggered based on a schedule or in response to events
    - - provides file-based storage
      - usually in a distributed file system
      - high scalability
      - a repository of data
    - - a parallel processing framework
      - takes advantage of
        
        in-memory processing
        
        a distributed file storage
  - - - perform data preparation and model training and scoring for ML solutions
- - - - treat the data as if it's stored in a (HDFS)
      - access it through
        
        Databricks
        
        Azure HDInsight
        
        Synapse Analytics
    - - supports
        
        access control lists (ACLs)
        
        Portable Operating System Interface (POSIX) permissions
      - set permissions at a directory level or file level
      - security is configurable through
        
        Hive and Spark
        
        Azure Storage Explorer
      - All data that is stored is encrypted at rest by using either Microsoft or customer-managed keys.
    - - stored data into a hierarchy of directories and subdirectories
    - - takes advantage of the Azure Blob replication models
      - data redundancy
        
        in a single data center with locally redundant storage (LRS)
        
        to a secondary region by using the Geo-redundant storage (GRS) option
  - - - flat namespace within a blob container
      - Blob names can include "/" characters to organize blobs into virtual "folders"
    - - a hierarchical namespace that organizes blob data into directories
      - stores metadata about each directory and the files
      - better storage and retrieval performance for an analytical use case