Azure Synapse AnalyticsAuthorCloudBrum editorial teamLast reviewed28 Oct 2026Reading time~12 minLevelBeginner to advanced

• T-SQL over filesEvery Synapse workspace comes with a serverless SQL endpoint. Write a SELECT with OPENROWSET that points at a path in the lake, and it reads the files directly. There’s no cluster to start and nothing to load.
• Pay for data processedThe meter is the data the query processes, at about $5 per terabyte according to the sources we read, rounded up per query with a 10 MB minimum. An idle pool costs nothing.
• Views and external tablesWrap an OPENROWSET query in a view, or create external tables, so analysts and tools such as Power BI see ordinary tables while the files stay in the lake.
• CETAS to write results backCREATE EXTERNAL TABLE AS SELECT runs a query and writes the result as Parquet or CSV to the lake. It’s how you turn raw files into a compact, curated copy.
• Processing limitsSet a daily, weekly and monthly cap, in terabytes, on what the serverless pool may process. Once a cap is used up, new queries are refused until the period rolls over.
• Entra ID and managed identityPeople sign in with Microsoft Entra ID, and the workspace’s managed identity can read the lake through a credential, so no storage keys appear in scripts.
• Beyond serverless SQLThe same workspace can also hold dedicated SQL pools for a classic warehouse, Spark pools for notebooks and data pipelines. They’re billed on their own meters.
• Fabric is the stated directionMicrosoft recommends Microsoft Fabric for new analytics work. Existing Synapse workspaces are supported, and we found no retirement date, but check Microsoft’s lifecycle page for yours.

Workspace

The Synapse resource that groups SQL pools, Spark pools, pipelines and the studio. It’s tied to a default Data Lake Storage Gen2 account and container.

Serverless SQL pool

The built-in on-demand SQL endpoint, called Built-in in the studio. It has no provisioned size and bills by data processed.

Dedicated SQL pool

A provisioned data warehouse with its own storage, billed per hour for the size you choose. It’s a separate service from the serverless pool.

OPENROWSET

A T-SQL function that reads files from storage as if they were a table. You give it a path or wildcard, a format and, if needed, a column list.

External data source

A named location in storage, such as a container URL, with the credential used to read it.

Database scoped credential

A stored way to authenticate to storage: a managed identity, a shared access signature or the caller’s Entra identity.

External table

A table definition over files in the lake. Dropping it doesn’t delete the files.

CETAS

CREATE EXTERNAL TABLE AS SELECT. It writes query results to the lake and registers an external table over them.

filepath() and filename()

Functions that return parts of the path or the file name of each row, used to prune folders and to find which file a row came from.

Data processed

The amount of data a serverless query reads and handles, including any data returned. It’s the billing unit.

Cost control

The setting that caps data processed per day, week and month, set in Synapse Studio or with sp_set_data_processed_limit.

Collation

The sorting and comparison rules for text. A UTF8 binary collation such as Latin1_General_100_BIN2_UTF8 is recommended for Parquet and gives faster string filtering.

Delta Lake

An open table format over Parquet that adds a transaction log. Serverless SQL can read Delta folders.

Microsoft Fabric

Microsoft’s unified analytics platform, built on OneLake, that Microsoft recommends for new analytics projects. Its SQL analytics endpoint does a similar job to the serverless pool.

Query a data lake with the Azure CLI and T-SQL

1. Decide whether Synapse is still the right homeFor a new project, look at Microsoft Fabric first, since that’s where Microsoft is investing. Use Synapse when you already run it, or when a team needs the serverless SQL pool over an existing lake.
2. Create the storageMake a storage account with the hierarchical namespace turned on, and a container for the lake. Store data as Parquet under folders such as orders/order_date=2026-10-28/.
3. Create the workspaceCreate a Synapse workspace tied to that account and container. Choose Entra-only authentication if your policy allows, and set an Entra administrator.
4. Open the network the way you needAdd a firewall rule for your office or build agent, or turn on a managed virtual network and private endpoints if the lake is private.
5. Give the workspace access to the lakeGrant the workspace’s managed identity a data role on the storage account: Reader if it only queries, Contributor if it will write with CETAS.
6. Create a serverless databaseConnect to the serverless endpoint, create a database with a UTF-8 binary collation, then create the master key, the credential, the data source and the file format.
7. Query the filesRun a SELECT with OPENROWSET, using filepath() to filter on the partition folders. Add the columns and types yourself rather than leaving Synapse to infer them.
8. Set cost limitsCall sp_set_data_processed_limit for daily, weekly and monthly caps, and check sys.dm_external_data_processed to see usage.
9. Materialise what people use mostUse CETAS to write a curated, compressed Parquet copy of the frequently used data, and point views and reports at that.

• Query Parquet or Delta, not CSV. Serverless SQL reads only the columns a query names from columnar files.
• Create the database with a UTF-8 binary collation such as Latin1_General_100_BIN2_UTF8, so string filters can be pushed down.
• Partition by folder and filter with filepath(), so only the matching folders are read.
• Write the column list and types yourself in the WITH clause, and avoid varchar(max) where a short length is enough.
• Keep files large, in the range of hundreds of megabytes, and avoid thousands of tiny ones.
• Create statistics on columns used in joins and filters of heavily used external tables.
• Use CETAS to write a pre-aggregated or filtered copy, and point repeated reports at it.
• Keep the storage account in the same region as the workspace.

FinOps is the habit of managing cloud spend together with engineering. These are the FinOps checks that matter most for Azure Synapse Analytics.

• Know the meter: data processed, about $5 per TB with a 10 MB minimum per query, rounded up per query.
• Set daily, weekly and monthly limits with sp_set_data_processed_limit, and alarm when usage nears them.
• Convert CSV and JSON to compressed Parquet. It cuts what a query processes by a large factor.
• Select only needed columns, because columns not named in the query aren’t read from Parquet.
• Give explicit schemas so Synapse doesn’t read extra data to infer them.
• Use CETAS for repeated questions, so reports read a small curated copy.
• Don’t forget the other meters in the workspace: dedicated SQL pools, Spark pools, pipeline runs and storage transactions.
• Pause or delete dedicated pools and Spark pools you don’t use.
• Review the top queries by data processed each week with the studio or the DMV.
• Tag the workspace and the storage account by team, and set a budget alert in Cost Management.

• Sign in with Microsoft Entra ID, prefer Entra-only authentication, and set an Entra group as the workspace administrator.
• Use the workspace managed identity to read the lake, and give it the smallest data role that works.
• Use a managed virtual network and private endpoints, and keep the storage account’s firewall closed to the public internet.
• Grant access to views and schemas rather than raw files, and control row and column access with permissions and security predicates.
• Avoid SAS tokens and account keys, and rotate any you must use.
• Encrypt the workspace and the storage account with Microsoft-managed or customer-managed keys.
• Turn on auditing and send logs to Log Analytics, and alert on failed sign-ins and unusual data processed.
• Use Microsoft Purview to classify and catalogue sensitive columns in the lake.
• Set processing limits so a compromised account can’t run an unlimited bill.
• Review role assignments on the workspace and storage regularly, and use Azure Policy for network and encryption rules.

The Azure Activity Log records who created or changed workspaces, SQL auditing records queries and sign-ins, Azure Policy applies the rules above, Defender for Cloud gives recommendations, and Microsoft Purview classifies the data. Microsoft runs the serverless engine and the workspace service, while you own the data in the lake, its layout, the access rules, the processing limits and the plan for the Fabric move.

• Process less: Parquet, partitions and selective columns reduce compute for each answer.
• Write curated copies with CETAS so repeated reports don’t re-read raw files.
• Pause dedicated SQL pools and Spark pools when nobody uses them.
• Delete outdated curated copies and scratch folders.
• Compress data, which saves storage and data moved.
• Choose a region with a cleaner grid for batch analysis that isn’t tied to a place, as far as data rules allow.
• The Azure Emissions Impact Dashboard reports a subscription’s footprint. Check how it treats Synapse before you quote a figure.

Purchase options

What you actually pay for

• Data processed by every serverless SQL query, rounded up with a 10 MB minimum
• Dedicated SQL pools while they run, and their storage
• Spark pool and pipeline usage in the same workspace
• Data Lake Storage capacity and transactions
• Private endpoints, monitoring and data transfer

Order data stored as Parquet in a Data Lake Storage Gen2 container, partitioned by day. The Azure CLI tab creates the storage and the workspace and gives the workspace identity access, then the T-SQL part creates a serverless database, caps data processed, queries the last seven days with OPENROWSET and writes October to a compact table with CETAS. Terraform builds the storage and the workspace. The Python tab runs the query with an Entra token and prints the data processed, and the C# tab runs it with the SQL client. Replace the names, subscription and passwords with your own. A query costs by the data it processes, so start with a single day.

# Placeholders: replace the names, the admin group and the password with your own.
RG=rg-orders-analytics
LOC=centralindia
STORAGE=storordersdlexample
WS=syn-orders-example
ADMIN_GROUP_ID=00000000-0000-0000-0000-000000000000
SQL_ADMIN_PASSWORD='replace-with-a-long-random-password'

az group create --name $RG --location $LOC

# A storage account with the hierarchical namespace (Data Lake Storage Gen2) and one container for the lake.
az storage account create --name $STORAGE --resource-group $RG --location $LOC --sku Standard_LRS --kind StorageV2 \
  --enable-hierarchical-namespace true --allow-blob-public-access false --min-tls-version TLS1_2
az storage fs create --name lake --account-name $STORAGE --auth-mode login

# The workspace, tied to that container.
az synapse workspace create --name $WS --resource-group $RG --location $LOC \
  --storage-account $STORAGE --file-system lake \
  --sql-admin-login-user sqladminuser --sql-admin-login-password "$SQL_ADMIN_PASSWORD"
az synapse workspace firewall-rule create --workspace-name $WS --resource-group $RG --name office --start-ip-address 203.0.113.10 --end-ip-address 203.0.113.10

# Who administers the workspace, and what the workspace identity may do in the lake (read, plus write for CETAS).
az synapse role assignment create --workspace-name $WS --role "Synapse Administrator" --assignee $ADMIN_GROUP_ID
WS_IDENTITY=$(az synapse workspace show --name $WS --resource-group $RG --query identity.principalId -o tsv)
az role assignment create --assignee-object-id $WS_IDENTITY --assignee-principal-type ServicePrincipal \
  --role "Storage Blob Data Contributor" --scope $(az storage account show --name $STORAGE --resource-group $RG --query id -o tsv)

# From here on it's T-SQL against the serverless endpoint, with your Entra sign-in (-G).
sqlcmd -S $WS-ondemand.sql.azuresynapse.net -d master -G -b -Q "CREATE DATABASE orders_lake COLLATE Latin1_General_100_BIN2_UTF8"

cat > orders_lake.sql <<'SQL'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'another-long-random-password';
CREATE DATABASE SCOPED CREDENTIAL lake_identity WITH IDENTITY = 'Managed Identity';
CREATE EXTERNAL DATA SOURCE orders_lake_src WITH (LOCATION = 'https://storordersdlexample.dfs.core.windows.net/lake', CREDENTIAL = lake_identity);
CREATE EXTERNAL FILE FORMAT parquet_ff WITH (FORMAT_TYPE = PARQUET);
GO

-- No more than 1 TB a day, 3 TB a week and 10 TB a month may be processed.
EXEC sp_set_data_processed_limit @type = N'daily', @limit_tb = 1;
EXEC sp_set_data_processed_limit @type = N'weekly', @limit_tb = 3;
EXEC sp_set_data_processed_limit @type = N'monthly', @limit_tb = 10;
GO

-- Revenue per day for the last seven days. filepath(1) is the value of the first * in the path, so only seven folders are read.
SELECT r.filepath(1) AS order_date, COUNT(*) AS orders, ROUND(SUM(r.total), 2) AS revenue
FROM OPENROWSET(BULK 'orders/order_date=*/*.parquet', DATA_SOURCE = 'orders_lake_src', FORMAT = 'PARQUET')
     WITH (order_id VARCHAR(40), customer_id VARCHAR(40), total FLOAT, currency CHAR(3), status VARCHAR(20)) AS r
WHERE r.filepath(1) >= CONVERT(VARCHAR(10), DATEADD(day, -7, GETDATE()), 23) AND r.status = 'PAID'
GROUP BY r.filepath(1) ORDER BY r.filepath(1);
GO

-- Compact October into one tidy table.
CREATE SCHEMA curated;
GO
CREATE EXTERNAL TABLE curated.orders_2026_10
WITH (LOCATION = 'curated/orders_2026_10/', DATA_SOURCE = orders_lake_src, FILE_FORMAT = parquet_ff)
AS SELECT r.filepath(1) AS order_date, r.order_id, r.customer_id, r.total, r.status
FROM OPENROWSET(BULK 'orders/order_date=2026-10-*/*.parquet', DATA_SOURCE = 'orders_lake_src', FORMAT = 'PARQUET')
     WITH (order_id VARCHAR(40), customer_id VARCHAR(40), total FLOAT, status VARCHAR(20)) AS r;
GO

-- How much has been processed so far this period?
SELECT type, data_processed_mb FROM sys.dm_external_data_processed;
SQL
sqlcmd -S $WS-ondemand.sql.azuresynapse.net -d orders_lake -G -b -i orders_lake.sql

# Placeholders: names are examples, storage account names are global. Pass the admin group in as a variable.
variable "location" {
  type    = string
  default = "centralindia"
}

variable "admin_group_object_id" {
  type = string
}

variable "tenant_id" {
  type = string
}

resource "azurerm_resource_group" "analytics" {
  name     = "rg-orders-analytics"
  location = var.location
}

resource "azurerm_storage_account" "lake" {
  name                            = "storordersdlexample"
  resource_group_name             = azurerm_resource_group.analytics.name
  location                        = azurerm_resource_group.analytics.location
  account_tier                    = "Standard"
  account_replication_type        = "LRS"
  account_kind                    = "StorageV2"
  is_hns_enabled                  = true
  min_tls_version                 = "TLS1_2"
  allow_nested_items_to_be_public = false
}

resource "azurerm_storage_data_lake_gen2_filesystem" "lake" {
  name               = "lake"
  storage_account_id = azurerm_storage_account.lake.id
}

resource "azurerm_synapse_workspace" "orders" {
  name                                 = "syn-orders-example"
  resource_group_name                  = azurerm_resource_group.analytics.name
  location                             = azurerm_resource_group.analytics.location
  storage_data_lake_gen2_filesystem_id = azurerm_storage_data_lake_gen2_filesystem.lake.id
  managed_virtual_network_enabled      = true
  azuread_authentication_only          = true

  identity {
    type = "SystemAssigned"
  }
}

resource "azurerm_synapse_workspace_aad_admin" "orders" {
  synapse_workspace_id = azurerm_synapse_workspace.orders.id
  login                = "synapse-admins"
  object_id            = var.admin_group_object_id
  tenant_id            = var.tenant_id
}

# The workspace identity reads the lake, and writes to it for CETAS.
resource "azurerm_role_assignment" "workspace_lake" {
  scope                = azurerm_storage_account.lake.id
  role_definition_name = "Storage Blob Data Contributor"
  principal_id         = azurerm_synapse_workspace.orders.identity[0].principal_id
}

resource "azurerm_synapse_firewall_rule" "office" {
  name                 = "office"
  synapse_workspace_id = azurerm_synapse_workspace.orders.id
  start_ip_address     = "203.0.113.10"
  end_ip_address       = "203.0.113.10"
}

output "serverless_endpoint" {
  value = azurerm_synapse_workspace.orders.connectivity_endpoints["sqlOnDemand"]
}

# Runs a query on the serverless SQL endpoint with an Entra token (no SQL password) and reports data processed.
# pip install pyodbc azure-identity   (and the Microsoft ODBC Driver 18 for SQL Server)
import struct
import sys

import pyodbc
from azure.identity import DefaultAzureCredential

ENDPOINT = "syn-orders-example-ondemand.sql.azuresynapse.net"
SQL_COPT_SS_ACCESS_TOKEN = 1256        # the pyodbc attribute that carries an access token

SEVEN_DAYS = """
SELECT r.filepath(1) AS order_date, COUNT(*) AS orders, ROUND(SUM(r.total), 2) AS revenue
FROM OPENROWSET(BULK 'orders/order_date=*/*.parquet', DATA_SOURCE = 'orders_lake_src', FORMAT = 'PARQUET')
     WITH (order_id VARCHAR(40), total FLOAT, status VARCHAR(20)) AS r
WHERE r.filepath(1) >= CONVERT(VARCHAR(10), DATEADD(day, -?, GETDATE()), 23) AND r.status = 'PAID'
GROUP BY r.filepath(1) ORDER BY r.filepath(1)
"""


def connect(database="orders_lake"):
    token = DefaultAzureCredential().get_token("https://database.windows.net/.default").token.encode("utf-16-le")
    packed = struct.pack("=i", len(token)) + token
    conn_str = "Driver={ODBC Driver 18 for SQL Server};Server=%s;Database=%s;Encrypt=yes" % (ENDPOINT, database)
    return pyodbc.connect(conn_str, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: packed})


def main(days):
    with connect() as conn:
        cur = conn.cursor()
        for order_date, orders, revenue in cur.execute(SEVEN_DAYS, days).fetchall():
            print(order_date, orders, revenue)
        used = {t: mb for t, mb in cur.execute("SELECT type, data_processed_mb FROM sys.dm_external_data_processed")}
        print("processed so far:", ", ".join("%s %s MB" % kv for kv in used.items()))


if __name__ == "__main__":
    main(int(sys.argv[1]) if len(sys.argv) > 1 else 7)

// dotnet add package Microsoft.Data.SqlClient
// Runs the seven-day revenue query on the serverless endpoint. "Active Directory Default" uses your Azure CLI,
// Visual Studio or managed identity sign-in, so the connection string holds no password.
using Microsoft.Data.SqlClient;

var connection = new SqlConnectionStringBuilder
{
    DataSource = "syn-orders-example-ondemand.sql.azuresynapse.net",
    InitialCatalog = "orders_lake",
    Authentication = SqlAuthenticationMethod.ActiveDirectoryDefault,
    Encrypt = SqlConnectionEncryptOption.Mandatory
}.ConnectionString;

const string sql = @"
SELECT r.filepath(1) AS order_date, COUNT(*) AS orders, ROUND(SUM(r.total), 2) AS revenue
FROM OPENROWSET(BULK 'orders/order_date=*/*.parquet', DATA_SOURCE = 'orders_lake_src', FORMAT = 'PARQUET')
     WITH (order_id VARCHAR(40), total FLOAT, status VARCHAR(20)) AS r
WHERE r.filepath(1) >= @since AND r.status = 'PAID'
GROUP BY r.filepath(1) ORDER BY r.filepath(1)";

await using var conn = new SqlConnection(connection);
await conn.OpenAsync();

await using var cmd = new SqlCommand(sql, conn) { CommandTimeout = 120 };
cmd.Parameters.Add("@since", System.Data.SqlDbType.VarChar, 10).Value = DateTime.UtcNow.AddDays(-7).ToString("yyyy-MM-dd");

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine($"{reader.GetString(0)}  orders={reader.GetInt32(1),5}  revenue={reader.GetDouble(2),12:N2}");

Short walkthrough videos for this service are being prepared. They will appear here and load only when you press play. Until then, follow CloudBrum on YouTube for new videos.

For a new analytics platform on Azure, look at Microsoft Fabric first. For application data and transactions use a database such as Azure SQL Database. The lake files themselves live in Azure Data Lake Storage, which is built on Azure Blob Storage, and heavy code-based transformations belong in Spark or Databricks.