Showing posts with label endpoints. Show all posts
Showing posts with label endpoints. Show all posts

25 February 2025

🏭💠🗒️Microsoft Fabric: T-SQL Notebook [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 25-Feb-2024

[Microsoft Fabric] T-SQL notebook

  • {def} notebook that enables to write and run T-SQL code within a notebook [1]
  • {feature} allows to manage complex queries and write better markdown documentation [1]
  • {feature} allows the direct execution of T-SQL on
    • connected warehouse
    • SQL analytics endpoint
    • ⇐ queries can be run directly on the connected endpoint [1]
      • multiple connections are allowed [1]
  • allows running cross-database queries to gather data from multiple warehouses and SQL analytics endpoints [1]
  • the code is run by the primary warehouse
    • used as default in commands which supports three-part naming, though no warehouse was provided [1]
    • three-part naming consists of 
      • database name
        • the name of the warehouse or SQL analytics endpoint [1]
      • schema name
      • table name
  • {feature} autogenerate T-SQL code using the code template from the object explorer's context [1] menu
  • {concept} code cells
    • allow to create and run T-SQL code
      • each code cell is executed in a separate session [1]
        • {limitation} the variables defined in one cell are not available in another cell [1]
        • one can check the execution summary after the code is executed [1]
      • cells can be run individually or together [1]
      • one cell can contain multiple lines of code [1]
        • users can select and run subparts of a cell’s code [1]
    • {feature} Table tab
      • lists the records from the returned result set
        • if the execution contains multiple result set, you can switch from one to another via the dropdown menu [1]
  • a query can be saved as 
    • view
      • via 'Save as' view
      • {limitation} does not support three-part naming [1]
        • the view is always created in the primary warehouse [1]
          • by setting the warehouse as the primary warehouse [1]
    • table
      • via 'Save as' table
      • saved as CTAS 
    • ⇐ 'Save as' is only available for the selected query text
      • the query text must be selected before using the Save as options
  • {limitation} doesn’t support 
    • parameter cell
      • the parameter passed from pipeline or scheduler can't be used [1]
    • {feature} Recent Run 
      • {workaround} use the current data warehouse monitoring feature to check the execution history of the T-SQL notebook [1]
    • {feature} the monitor URL inside the pipeline execution
    • {feature} snapshot 
    • {feature} Git support 
    • {feature} deployment pipeline support 
References:
[1] Microsoft Learn (2025) T-SQL support in Microsoft Fabric notebooks [link
[2] Microsoft Learn (2025) Create and run a SQL Server notebook [link
[3] Microsoft Learn (2025) T-SQL surface area in Microsoft Fabric [link
[4] Microsoft Fabric Updates Blog (2024) Announcing Public Preview of T-SQL Notebook in Fabric [link]

Acronyms
CTAS - Create Table as Select
T-SQL - Transact SQL

13 February 2025

🏭💠🗒️Microsoft Fabric: SQL Analytics Endpoint [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 12-Feb-2024

[Microsoft Fabric] SQL Analytics Endpoint

  • {def} a service that listens actively for requests providing a SQL-based experience for lakehouse Delta tables [1]
    • enables to query data in the lakehouse using T-SQL language and TDS protocol [1]
    • created by default for each lakehouses and mirrored databases provisioned in a workspace
      • every lakehouse has one SQL analytics endpoint [1]
    • manages the automatically generated tables so the workspace users can't modify them [1]
  • {feature} a background process is responsible for 
    • scanning lakehouse for changes [1]
      • automatically generates a table in the appropriate schema for every Delta table in the Lakehouse
        • tables are created with a minor delay [1]
        • data is refreshed automatically
          • the amount of time it takes to refresh the table is related to how optimized the Delta tables are [1]
        • {alternative} one can manually force a refresh of the automatic metadata scanning in the Fabric portal [1]
    • keeping SQL analytics endpoint up-to-date for all the changes committed to lakehouses in a workspace [1]
      • {characteristic} transparently managed
        • ⇐ the sync process is transparently managed by Microsoft Fabric platform [1]
        • when a change is detected in the lakehouse
          • a background process updates metadata [1]
          • the SQL analytics endpoint reflects the changes committed to lakehouse tables [1]
      • {characteristic} low latency
        • under normal operating conditions, the lag between a lakehouse and SQL analytics endpoint is less than one minute [1]
        • the actual length of time can vary from a few seconds to minutes [1]
      • the metadata synchronization is automatically triggered when the SQL Endpoint is opened [3]
        • ensures the SQL Analytics Endpoint remains up to date without the need for manual syncs [3]
          •  ⇐ provides a seamless experience [3]
  • {feature} can be enriched by adding database objects
    • schemas
    • views
    • procedures
    • other database objects
  • {feature} automatic metadata discovery
    • tracks changes committed to lakehouses [1]
      • is a single instance per Fabric workspace [1]
      • {issue} increased latency for changes to sync between lakehouses and SQL analytics endpoint, it could be due to large number of lakehouses in one workspace [1]
        • {resolution} migrate each lakehouse to a separate workspace [1]
          • allows automatic metadata discovery to scale [1]
      • {issue} changes committed to a lakehouse are not visible in the associated SQL analytics endpoint
        • create a new table in the  [1]
        • many committed rows may not be visible in the endpoint [1]
        • ⇐ may be impacted by ETL processing that generate large volumes of changes
        • {recommendation} initiate an on-demand metadata sync, triggered from the SQL query editor Refresh ribbon option [1]
          • forces an on-demand metadata sync, rather than waiting on the background metadata sync to finish [1]
      • {issue} if there's no maintenance scheduled for the parquet files, this can result in read overhead and this impacts time it takes to sync changes to SQL analytics endpoint [1]
        • {recommendation}schedule regular lakehouse table maintenance operations [1]
      • {limitation} not all Delta features are understood by the automatic sync process [1]
  • {feature} endpoint reprovisioning
    • if the initial provisioning attempt fails, users have the option to try again [2]
      • ⇐ without the need to create an entirely new lakehouse [2]
      • {benefit} empowers users to self-mitigate provisioning issues in convenient way in the UI avoiding the need for complete lakehouse re-creation [2]

References:
[1] Microsoft Learn (2024) Microsoft Fabric: SQL analytics endpoint performance considerations [link]
[2] Microsoft Learn (2024) Microsoft Fabric: What is the SQL analytics endpoint for a lakehouse? [link]
[3] Microsoft Learn (2024) Microsoft Fabric: What’s new in the Fabric SQL Analytics Endpoint? [link]
Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.