Serverless SQL Architecture Video Script¶

Comparative positioning note

This document is written from the perspective of Microsoft Azure, Cloud Scale Analytics, and CSA Loom. Any description of third-party or competing products, services, pricing, or capabilities is derived from publicly available documentation and sources believed accurate at the time of writing, and is provided for general comparison only. We do not claim expertise in, or authority over, any non-Microsoft product or service; the respective vendor's official documentation is the authoritative source for their offerings, which may change over time. Nothing here is intended to disparage any vendor — where a competing product has genuine advantages, we aim to note them honestly. Verify all third-party details against the vendor's current official documentation before making decisions.

Overview¶

Video script covering Azure Synapse Serverless SQL Pool architecture, query optimization, and design patterns for cost-effective data exploration.

Script Content¶

Opening (0:00 - 1:00)¶

NARRATOR: "Query petabytes of data without provisioning infrastructure. Azure Synapse Serverless SQL Pool enables on-demand analytics with pay-per-query pricing. Let's explore its architecture and optimization strategies."

Section 1: Architecture Overview (1:00 - 5:00)¶

Serverless Engine¶

```textQuery Request ↓ Query Optimizer ↓ Distributed Query Execution ↓ Data Lake Storage (Parquet/Delta/CSV) ↓ Results (pay for data scanned)

**Key Characteristics**:
- No servers to manage
- Automatic scaling
- Pay per TB scanned
- Instant availability

### Section 2: Query Patterns (5:00 - 11:00)

#### OPENROWSET Basics

```sql
-- Query Parquet files
SELECT *
FROM OPENROWSET(
    BULK 'https://datalake.dfs.core.windows.net/data/sales/*.parquet',
    FORMAT = 'PARQUET'
) AS sales
WHERE sale_date >= '2024-01-01';

-- Query with schema inference
SELECT *
FROM OPENROWSET(
    BULK 'https://datalake.dfs.core.windows.net/data/customers.json',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) WITH (
    customer_id INT,
    name VARCHAR(100),
    email VARCHAR(100)
) AS customers;

External Tables¶

-- Create external data source
CREATE EXTERNAL DATA SOURCE DataLake
WITH (
    LOCATION = 'https://datalake.dfs.core.windows.net/',
    CREDENTIAL = ManagedIdentity
);

-- Create external file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

-- Create external table
CREATE EXTERNAL TABLE Sales
(
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(19,2)
)
WITH (
    LOCATION = 'data/sales/',
    DATA_SOURCE = DataLake,
    FILE_FORMAT = ParquetFormat
);

Section 3: Optimization Techniques (11:00 - 14:00)¶

Minimize Data Scanned¶

-- BAD: Scans all data
SELECT *
FROM OPENROWSET(
    BULK 'https://datalake.dfs.core.windows.net/data/**/*.parquet',
    FORMAT = 'PARQUET'
) AS data;

-- GOOD: Uses partition pruning
SELECT *
FROM OPENROWSET(
    BULK 'https://datalake.dfs.core.windows.net/data/year=2024/month=01/*.parquet',
    FORMAT = 'PARQUET'
) AS data;

-- BEST: Specific columns + partition pruning
SELECT customer_id, sale_date, amount
FROM Sales
WHERE year = 2024 AND month = 1;

File Size Optimization¶

Recommendations: - Optimal file size: 100MB - 1GB - Too small: Overhead increases - Too large: Cannot parallelize - Use compaction for small files

Section 4: Cost Management (14:00 - 16:00)¶

Cost Calculation¶

```textCost = Data Scanned (TB) × $5.00 per TB

Example: - Query scans 100GB - Cost = 0.1 TB × $5.00 = $0.50 ```

Cost Optimization Strategies¶

Partition Data: Reduce data scanned
Use Parquet: 10x less data than CSV
Select Specific Columns: Avoid SELECT *
Compress Files: Snappy compression
Create External Tables: Reuse metadata

Conclusion¶

Best Practices: - Use partition elimination - Choose optimal file formats - Create external tables for reuse - Monitor query costs - Compress data appropriately

Last Updated: January 2025