Synapse Serverless query guide

After every extract, the job writes a SQL script to ADLS:

abfss://archive@<storage>.dfs.core.windows.net/<job_id>/_catalog/synapse.sql

Run this script once in Synapse Serverless SQL pool and you get queryable views over the archived Parquet — no Oracle restore needed.

What’s in synapse.sql

-- 1. External data source (one-time per ADLS container)
IF NOT EXISTS (SELECT 1 FROM sys.external_data_sources WHERE name = 'oradls_archive')
  CREATE EXTERNAL DATA SOURCE oradls_archive
    WITH (LOCATION = 'abfss://archive@<storage>.dfs.core.windows.net');
GO

-- 2. Schemas (one per Oracle schema in the job)
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'HR')
  EXEC('CREATE SCHEMA [HR]');
GO

-- 3. Views (one per Oracle table — OPENROWSET infers schema from Parquet)
CREATE OR ALTER VIEW [HR].[EMPLOYEES] AS
SELECT * FROM OPENROWSET(
  BULK '<job_id>/HR/EMPLOYEES/*.parquet',
  DATA_SOURCE = 'oradls_archive',
  FORMAT = 'PARQUET'
) AS r;
GO

The script is idempotent — re-running it after each extract creates new views for that job_id alongside any existing ones (CREATE OR ALTER VIEW).

Step-by-step

1. Open your Synapse workspace’s built-in serverless SQL pool

In Azure Portal → your Synapse workspace → Data → Workspace → right-click → New SQL script (or use Synapse Studio).

Make sure the Connect to dropdown shows your Built-in serverless pool (not a dedicated SQL pool).

2. Create a database to hold the views

CREATE DATABASE oracle_archive;
USE oracle_archive;

3. Grant the serverless pool access to your ADLS

Two options:

a) Storage Account RBAC (preferred — uses Microsoft Entra identity)

In Azure Portal → your storage account → Access Control (IAM)
Add role assignment: Storage Blob Data Reader
Assignee: Managed Identity of your Synapse workspace
Or: assignee is the Microsoft Entra user running the query

b) Storage Account Key (if RBAC isn’t an option)

CREATE DATABASE SCOPED CREDENTIAL ArchiveCred
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
       SECRET = '<sas-token>';

Then update the external data source to use it:

ALTER EXTERNAL DATA SOURCE oradls_archive
  SET CREDENTIAL = ArchiveCred;

4. Download the catalog SQL from ADLS

Use Azure Storage Explorer, or:

az storage blob download \
  --account-name <storage> \
  --container-name archive \
  --name '<job_id>/_catalog/synapse.sql' \
  --file synapse.sql \
  --auth-mode login

5. Run the SQL

Paste into the Synapse SQL script editor and run. Should take <5 seconds even for hundreds of tables (DDL only — no data is moved).

6. Query

USE oracle_archive;

SELECT TOP 10 * FROM HR.EMPLOYEES;

SELECT COUNT(*) FROM HR.EMPLOYEES WHERE hire_date > '2020-01-01';

-- Join across schemas
SELECT e.name, d.dept_name
FROM HR.EMPLOYEES e
JOIN HR.DEPARTMENTS d ON e.dept_id = d.id;

The Parquet is read directly from ADLS each query. Cold queries are slower (~5-30s depending on table size); subsequent queries against the same files hit Synapse’s result cache.

Tips

Multiple extract runs: each extract gets its own job_id folder under archive/. To query across snapshots, the catalog SQL uses CREATE OR ALTER VIEW so each new run overwrites the views. To keep historical access, rename the views before running the next catalog:
```
EXEC sp_rename 'HR.EMPLOYEES', 'EMPLOYEES_20260516';
```
Cost: Synapse Serverless charges per TB scanned. With Parquet’s predicate pushdown and column pruning, a SELECT COUNT(*) against a 100 GB table scans ~kilobytes of metadata. Costs scale with column count, not row count, for typical analytics queries.
Performance: For repeated heavy queries, materialize as Delta in Fabric or as a Synapse Dedicated table. The Parquet views are best for ad-hoc / occasional access.
Schema evolution: if you re-extract a table whose columns changed, the view auto-picks up the new schema on next query — Parquet stores its own schema.

Fabric Lakehouse: same syntax, different endpoint

If your team is on Fabric instead of Synapse, use fabric.sql (also in the _catalog/ folder). The SQL is essentially identical because both products use the same OPENROWSET dialect. Run it in your Lakehouse’s SQL endpoint the same way.

See FABRIC-QUERY-GUIDE.md for Fabric-specific notes.