Warehouse

In the Ultra Transcenders books

DP-600DP-700

Each book explains Warehouse in context, with comparison tables and the common traps.

Terms in this definition

• Deployment modes

  The two ways ARM can deploy: Incremental, the default, creates or updates what the template lists and ignores everything else; Complete also removes resource group contents absent from the template.

• T-SQL

  The dialect of SQL that Microsoft uses for Azure SQL and SQL Server. Azure Monitor logs are queried with KQL instead.

• DML

  Data Manipulation Language, the SQL statements SELECT, INSERT, UPDATE and DELETE, which act on the rows held in tables.

• DDL

  Data Definition Language, the SQL statements CREATE, ALTER, DROP and RENAME, used to make, change and delete database objects like tables and views.

• OVER

  Gives a T-SQL window function its window: PARTITION BY, ORDER BY and, if wanted, a ROWS or RANGE frame. Rankings and running totals can then be worked out while every row is kept.

• OneLake

  Built on Azure Data Lake Storage Gen2, it is the one logical data lake for an entire Microsoft Fabric tenant, provisioned automatically, and the place where every Fabric workload keeps its data.

• Lakehouse

  Fabric storage item in OneLake that holds structured and unstructured content side by side: managed Delta tables go under Tables, other files under Files. Spark is used for processing, and a read-only SQL analytics endpoint allows T-SQL queries.

• Serverless

  Compute tier for single Azure SQL databases that scales automatically, pauses when idle and charges by the second. It is offered in General Purpose and Hyperscale, not Business Critical, and reserved capacity does not apply.

Related terms

• Analytical data processing

  Working with big sets of historical data or business measures, mostly reading rather than writing, typically after loading them into a lake, warehouse or lakehouse and on into a semantic model for reporting. It contrasts with OLTP, or transactional, workloads.

• Auto Stop

  A SQL warehouse option that shuts the warehouse down after a chosen number of idle minutes. Pro and classic warehouses default to 45 minutes with a floor of 10; serverless ones default to 10.

• Automatic statistics

  When queries run against the Fabric Data Warehouse or the SQL analytics endpoint, the engine creates statistics (histograms, cardinality and average column length) on columns used for filtering, joining, grouping, sorting or DISTINCT. The histogram objects are named with a _WA_Sys_ prefix.

• Azure Synapse Analytics

  An older analytics offering in Azure that combined Spark, pipelines and SQL-pool data warehousing. People new to warehousing are now pointed by its documentation to Fabric Data Warehouse.

• Background operation

  Refreshes, scheduled jobs and most warehouse work fall into this Fabric capacity category, and their compute usage is smoothed across a full 24 hours. Interactive work, report queries for example, is smoothed over a much shorter 5 to 64 minutes.

• CAN MONITOR

  Meant for power users, this permission on a SQL warehouse is stronger than CAN USE but weaker than CAN MANAGE. It allows running queries and watching the warehouse, for example through its history of queries and their profiles.

• Compute permissions

  Access rules that live inside one Fabric engine and govern only queries run through it. They include T-SQL GRANT or DENY, masking and row-level security on a warehouse or SQL analytics endpoint, and DAX-based security in a semantic model.

• CONNECT

  In SQL Server, the right to open a connection to a database. Fabric's equivalent is Read permission on an item, which allows connecting to a warehouse or SQL endpoint but not querying anything in it.