Running Your First Query in Azure Synapse

What you need before you start

To follow this tutorial you need:

• An Azure subscription (a free account works fine)
• A Synapse workspace already created — if you haven’t done this yet, the Synapse overview page includes a CLI command to create one
• The workspace’s primary ADLS Gen2 account should have at least one folder with files — we’ll also show how to query the NYC Taxi public dataset if you have nothing of your own

No software to install. Everything runs in Synapse Studio in your browser.

Step 1: Open Synapse Studio

In the Azure portal, navigate to your Synapse workspace resource and click the Open Synapse Studio button. This opens a new browser tab at https://web.azuresynapse.net. Sign in with your Azure account if prompted.

The Studio loads with five navigation icons on the left sidebar:

• Home — shortcuts and recent items
• Data — browse linked services and databases
• Develop — notebooks, SQL scripts, and data flows
• Integrate — pipelines (Synapse Pipelines / ADF)
• Monitor — pipeline and Spark job run history
• Manage — pools, linked services, and access control

Step 2: Create a new SQL script

Click the Develop icon (the </> symbol) in the left sidebar. At the top of the panel, click the + button and select SQL script. A new editor tab opens.

In the toolbar above the script editor, make sure the Connect to dropdown says Built-in. “Built-in” is the serverless SQL pool — it is always available and costs nothing to provision.

Step 3: Query a public dataset

The easiest way to run your first query is to use the publicly available NYC Yellow Taxi dataset stored in Azure Open Datasets. Paste this query into the editor:

-- Query the NYC Yellow Taxi dataset from Azure Open Datasets
-- This uses OPENROWSET to read Parquet files directly - no loading required
SELECT
    YEAR(tpepPickupDatetime)        AS pickup_year,
    MONTH(tpepPickupDatetime)       AS pickup_month,
    COUNT(*)                        AS total_trips,
    AVG(tripDistance)               AS avg_distance_miles,
    AVG(totalAmount)                AS avg_fare_usd,
    SUM(totalAmount)                AS total_revenue_usd
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2019/puMonth=*/*.parquet',
        FORMAT = 'PARQUET'
    ) AS nyc_taxi
GROUP BY
    YEAR(tpepPickupDatetime),
    MONTH(tpepPickupDatetime)
ORDER BY
    pickup_year,
    pickup_month;

Click Run (or press F5). The query will take 20–40 seconds on first run as the serverless engine spins up distributed workers. You will see monthly trip counts, average distances, and revenue totals for 2019.

Step 4: Query your own files

If you have CSV or Parquet files in your workspace’s primary storage account, here is how to query them. First, browse to your files using the Data tab — expand the primary storage account in the left panel, find your file, right-click it, and select New SQL script > Select TOP 100 rows. Synapse Studio generates an OPENROWSET query for you automatically.

To write it manually for a CSV file:

-- Query a CSV file in your primary storage account
-- Replace the path with your actual container and file path
SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://yourstorageaccount.dfs.core.windows.net/yourcontainer/yourfolder/yourfile.csv',
        FORMAT = 'CSV',
        HEADER_ROW = TRUE,
        PARSER_VERSION = '2.0'
    ) AS csv_data;

For Parquet files, Synapse reads the schema automatically from the file metadata:

-- Auto-detect schema from a Parquet file
SELECT TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://yourstorageaccount.dfs.core.windows.net/silver/orders/**',
        FORMAT = 'PARQUET'
    ) AS parquet_data;

The ** wildcard reads all Parquet files recursively under the orders/ folder — useful for partitioned datasets where files are split into date-based subdirectories.

Step 5: Save a query as a view

Ad-hoc OPENROWSET queries work for exploration, but for regular reporting you want to create a proper database with external tables or views. This makes querying cleaner and allows Power BI to see table names instead of raw SQL.

-- Create a database for your serverless queries
CREATE DATABASE SilverAnalytics;
GO

-- Switch to that database
USE SilverAnalytics;
GO

-- Create a schema
CREATE SCHEMA reporting;
GO

-- Create a view over your lake data
-- Views in serverless SQL are just saved queries - no data is copied
CREATE OR ALTER VIEW reporting.v_monthly_orders AS
SELECT
    YEAR(order_date)    AS order_year,
    MONTH(order_date)   AS order_month,
    COUNT(*)            AS order_count,
    SUM(order_total)    AS revenue
FROM
    OPENROWSET(
        BULK 'https://yourstorageaccount.dfs.core.windows.net/silver/orders/**',
        FORMAT = 'PARQUET'
    ) AS orders
GROUP BY
    YEAR(order_date),
    MONTH(order_date);
GO

-- Now query the view cleanly
SELECT * FROM reporting.v_monthly_orders ORDER BY order_year, order_month;

Querying a dedicated SQL pool

If you have already created and resumed a dedicated SQL pool, querying it is identical to querying any SQL Server database. Change the Connect to dropdown in the toolbar from “Built-in” to your pool’s name, then write standard T-SQL:

-- Query a table in a dedicated SQL pool
SELECT
    c.country,
    COUNT(o.order_id)           AS order_count,
    SUM(o.order_total)          AS total_revenue,
    AVG(o.order_total)          AS avg_order_value
FROM
    dbo.FactOrders  o
    JOIN dbo.DimCustomer c ON o.customer_id = c.customer_id
WHERE
    o.order_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY
    c.country
ORDER BY
    total_revenue DESC;

Dedicated pool queries benefit from result-set caching. If the same query is run twice and the underlying data has not changed, the second run returns instantly from cache. You can check if a result was served from cache by looking at the query monitoring in the Monitor tab — cache hits show as CACHE HIT in the execution plan.

Monitoring your queries

In the Monitor tab (the bar chart icon in the left sidebar), click SQL requests to see the history of your serverless SQL queries, including execution time and data scanned. For dedicated pool queries, click SQL pools and then your pool’s name to see active queries, recent queries, and resource utilisation.

The Monitor tab also shows cost estimates for serverless queries — each completed query shows the gigabytes scanned, from which you can calculate the cost at the current regional rate.

Tips for faster serverless queries

The serverless pool reads files from ADLS, so query speed depends heavily on how your data is stored:

• Use Parquet instead of CSV. Parquet stores data in columns — the engine reads only the columns your query needs, not the entire file. A query selecting 3 columns from a 50-column CSV reads all 50; from Parquet it reads only 3.
• Partition your data by the columns you filter on most. If you store files in paths like /orders/year=2025/month=01/, Synapse can skip entire folders that do not match your WHERE clause. This is partition pruning and can reduce scanned data by orders of magnitude.
• Keep file sizes between 256 MB and 1 GB. Too many small files mean too many I/O operations. Too few large files mean poor parallelism. Re-compact small files using a Spark notebook on a regular schedule.
• Create statistics on external tables. The serverless SQL engine can generate statistics on external table columns, which improves query plan quality and reduces data movement.