Video Script: Serverless SQL Mastery¶

-- Query CSV files with automatic schema inference
SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/raw/sales/*.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) AS sales_data

-- Define explicit schema for better performance
SELECT
    customer_id,
    product_name,
    sales_amount,
    sale_date
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/raw/sales/2024/*.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
)
WITH (
    customer_id INT,
    product_name VARCHAR(200),
    sales_amount DECIMAL(18,2),
    sale_date DATE
) AS sales_data
WHERE
    sale_date >= '2024-01-01'
    AND sales_amount > 100
ORDER BY
    sales_amount DESC

-- Query Parquet files (much faster than CSV)
SELECT
    product_category,
    COUNT(*) as transaction_count,
    SUM(sales_amount) as total_sales,
    AVG(sales_amount) as avg_sale,
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/curated/sales_partitioned/**/*.parquet',
    FORMAT = 'PARQUET'
) AS sales_parquet
WHERE
    sales_parquet.filepath(1) = 'year=2024'  -- Partition pruning
    AND sales_parquet.filepath(2) IN ('month=01', 'month=02', 'month=03')
GROUP BY
    product_category
HAVING
    SUM(sales_amount) > 100000
ORDER BY
    total_sales DESC

-- Query JSON files and extract nested properties
SELECT
    JSON_VALUE(doc, '$.userId') AS user_id,
    JSON_VALUE(doc, '$.event.type') AS event_type,
    JSON_VALUE(doc, '$.event.timestamp') AS event_timestamp,
    JSON_QUERY(doc, '$.event.properties') AS event_properties,
    JSON_VALUE(doc, '$.session.id') AS session_id
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/logs/events/2024/01/**/*.json',
    FORMAT = 'CSV',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b',
    ROWTERMINATOR = '0x0b'
) WITH (doc NVARCHAR(MAX)) AS events
CROSS APPLY OPENJSON(doc)
WHERE
    JSON_VALUE(doc, '$.event.type') = 'purchase'

-- Query Delta Lake tables with time travel
SELECT
    product_id,
    product_name,
    current_price,
    last_updated
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/delta/products/',
    FORMAT = 'DELTA'
) AS products
WHERE
    current_price BETWEEN 50 AND 500

-- Time travel query (requires timestamp)
SELECT
    product_id,
    product_name,
    price_yesterday
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/delta/products/',
    FORMAT = 'DELTA',
    VERSION_AS_OF = (
        SELECT MAX(version) - 1
        FROM OPENROWSET(
            BULK 'https://mydatalake.dfs.core.windows.net/delta/products/_delta_log/*.json',
            FORMAT = 'CSV'
        ) AS log
    )
) AS products_yesterday

-- Create database for external objects
CREATE DATABASE SalesAnalytics
GO

USE SalesAnalytics
GO

-- Create master key for encryption (one time)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password_123!'
GO

-- Create database scoped credential
CREATE DATABASE SCOPED CREDENTIAL DataLakeCredential
WITH IDENTITY = 'Managed Identity'  -- Or use Shared Access Signature
GO

-- Create external data source
CREATE EXTERNAL DATA SOURCE ContosoDatalake
WITH (
    LOCATION = 'https://mydatalake.dfs.core.windows.net/curated',
    CREDENTIAL = DataLakeCredential
)
GO

-- Define reusable file formats
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
GO

CREATE EXTERNAL FILE FORMAT CsvFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,  -- Skip header
        USE_TYPE_DEFAULT = FALSE,
        ENCODING = 'UTF8'
    )
)
GO

CREATE EXTERNAL FILE FORMAT DeltaFormat
WITH (
    FORMAT_TYPE = DELTA
)
GO

-- Create external table for sales data
CREATE EXTERNAL TABLE dbo.Sales
(
    sale_id BIGINT,
    customer_id INT,
    product_id INT,
    product_category VARCHAR(100),
    sales_amount DECIMAL(18,2),
    quantity INT,
    sale_date DATE,
    sale_timestamp DATETIME2
)
WITH (
    LOCATION = 'sales_partitioned/**/*.parquet',
    DATA_SOURCE = ContosoDatalake,
    FILE_FORMAT = ParquetFormat
)
GO

-- Query like a regular table
SELECT
    product_category,
    COUNT(*) as sales_count,
    SUM(sales_amount) as revenue
FROM dbo.Sales
WHERE sale_date >= DATEADD(month, -3, GETDATE())
GROUP BY product_category
ORDER BY revenue DESC

-- Create view with business logic
CREATE VIEW dbo.SalesQuarterlySummary
AS
SELECT
    DATEPART(YEAR, sale_date) as sale_year,
    DATEPART(QUARTER, sale_date) as sale_quarter,
    product_category,
    COUNT(*) as transaction_count,
    SUM(sales_amount) as total_revenue,
    AVG(sales_amount) as avg_transaction_value,
    SUM(quantity) as units_sold
FROM dbo.Sales
GROUP BY
    DATEPART(YEAR, sale_date),
    DATEPART(QUARTER, sale_date),
    product_category
GO

-- Create view joining multiple sources
CREATE VIEW dbo.EnrichedSales
AS
SELECT
    s.sale_id,
    s.sale_date,
    s.sales_amount,
    c.customer_name,
    c.customer_tier,
    p.product_name,
    p.category_name
FROM dbo.Sales s
INNER JOIN dbo.Customers c ON s.customer_id = c.customer_id
INNER JOIN dbo.Products p ON s.product_id = p.product_id
GO

-- Query the view
SELECT * FROM dbo.SalesQuarterlySummary
WHERE sale_year = 2024
ORDER BY total_revenue DESC

-- Leveraging partition structure: /year=2024/month=01/day=15/
SELECT
    customer_id,
    SUM(sales_amount) as total_spent
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/sales/**/*.parquet',
    FORMAT = 'PARQUET'
) AS sales
WHERE
    sales.filepath(1) = 'year=2024'
    AND sales.filepath(2) IN ('month=01', 'month=02', 'month=03')
GROUP BY customer_id

-- Combined with column pruning
SELECT
    -- Only select needed columns
    product_id,
    SUM(quantity) as units_sold
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/sales/year=2024/month=01/**/*.parquet',
    FORMAT = 'PARQUET'
) AS sales
GROUP BY product_id

-- View query cost
-- Check "Data processed (MB)" in query results

-- CSV Query (slowest)
SELECT COUNT(*) FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/data/sales.csv',
    FORMAT = 'CSV', PARSER_VERSION = '2.0', HEADER_ROW = TRUE
) AS data
-- Result: 45 seconds, 2.5 GB scanned

-- Parquet Query (fast)
SELECT COUNT(*) FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/data/sales.parquet',
    FORMAT = 'PARQUET'
) AS data
-- Result: 3 seconds, 450 MB scanned

-- Parquet with compression (fastest)
-- Snappy or GZIP compression
-- Result: 2 seconds, 180 MB scanned

-- First run: scans data lake
SELECT
    product_category,
    COUNT(*) as count,
    SUM(sales_amount) as revenue
FROM dbo.Sales
WHERE sale_date >= '2024-01-01'
GROUP BY product_category
-- Execution: 8 seconds, 2.1 GB scanned

-- Second run: uses cache
-- Same query executed again
-- Execution: <1 second, 0 GB scanned, $0 cost!

-- Enable actual execution plan
SET STATISTICS IO ON
SET STATISTICS TIME ON

-- Run query
SELECT
    c.customer_tier,
    COUNT(DISTINCT s.customer_id) as customer_count,
    SUM(s.sales_amount) as total_revenue
FROM dbo.Sales s
INNER JOIN dbo.Customers c ON s.customer_id = c.customer_id
WHERE s.sale_date >= '2024-01-01'
GROUP BY c.customer_tier

-- Review execution plan for:
-- - Data scanned per table
-- - Join strategy used
-- - Aggregation method

-- 1. Azure AD Authentication (recommended)
-- Users authenticate with Azure AD credentials
-- No credentials in connection string

-- 2. Managed Identity
CREATE DATABASE SCOPED CREDENTIAL ManagedIdentityCredential
WITH IDENTITY = 'Managed Identity'
GO

-- 3. Shared Access Signature (SAS)
CREATE DATABASE SCOPED CREDENTIAL SASCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupiytfx...'
GO

-- 4. Storage Account Key (least secure)
CREATE DATABASE SCOPED CREDENTIAL AccountKeyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'your-storage-account-key'
GO

-- Create security predicate function
CREATE FUNCTION dbo.fn_SecurityPredicate(@Region VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_SecurityPredicate_result
    WHERE
        @Region = USER_NAME()  -- Simplified example
        OR IS_MEMBER('SalesManagers') = 1  -- Managers see all regions
GO

-- Create security policy
CREATE SECURITY POLICY dbo.RegionalSalesFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(region)
ON dbo.Sales
WITH (STATE = ON)
GO

-- Now queries automatically filter by user
SELECT * FROM dbo.Sales
-- Users only see their region
-- Managers see all regions

-- Create view with conditional masking
CREATE VIEW dbo.CustomersMasked
AS
SELECT
    customer_id,
    -- Mask email for non-privileged users
    CASE
        WHEN IS_MEMBER('DataPrivacyOfficers') = 1
        THEN email
        ELSE LEFT(email, 3) + '***' + RIGHT(email, CHARINDEX('@', REVERSE(email)))
    END AS email,
    -- Mask phone
    CASE
        WHEN IS_MEMBER('DataPrivacyOfficers') = 1
        THEN phone
        ELSE 'XXX-XXX-' + RIGHT(phone, 4)
    END AS phone,
    customer_name,
    city,
    state
FROM dbo.Customers
GO

-- Grant access to masked view
GRANT SELECT ON dbo.CustomersMasked TO PublicUsers
GO

-- ✅ DO:
-- Use Azure AD authentication
-- Implement row-level security for multi-tenant scenarios
-- Use managed identities for service principals
-- Encrypt sensitive data at rest
-- Audit data access with Azure Monitor
-- Grant minimum required permissions

-- ❌ DON'T:
-- Store credentials in code or connection strings
-- Use storage account keys (use SAS or managed identity)
-- Grant broad SELECT permissions
-- Expose PII without masking
-- Skip access auditing

-- Error: "Failed to execute query. Error: Access to the path is forbidden"

-- Solution 1: Grant RBAC permissions
-- Azure Portal > Storage Account > Access Control (IAM)
-- Add role: "Storage Blob Data Reader" or "Storage Blob Data Contributor"

-- Solution 2: Verify credential
CREATE DATABASE SCOPED CREDENTIAL FixedCredential
WITH IDENTITY = 'Managed Identity'  -- Ensure managed identity is enabled
GO

-- Solution 3: Check firewall rules
-- Azure Portal > Storage Account > Networking
-- Add your IP or enable "Allow Azure services"

-- Error: "Query timeout expired"

-- Solution: Optimize query
-- 1. Add WHERE clause to reduce data scanned
SELECT * FROM dbo.Sales
WHERE sale_date >= DATEADD(month, -1, GETDATE())  -- Add filter

-- 2. Use partition pruning
WHERE sales.filepath(1) = 'year=2024'

-- 3. Reduce result set size
SELECT TOP 1000 * FROM dbo.Sales  -- Limit rows

-- 4. Create external table for repeated queries

-- Error: "Column name 'xyz' is not a valid column name"

-- Solution: Define explicit schema
SELECT * FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/data/file.csv',
    FORMAT = 'CSV', PARSER_VERSION = '2.0'
) WITH (
    column1 VARCHAR(100),
    column2 INT,
    column3 DECIMAL(18,2)
) AS data

-- 1. File Format
✅ Use Parquet with Snappy compression
✅ Partition large files by date or category
✅ Keep file sizes between 100MB-1GB

-- 2. Query Optimization
✅ Select only needed columns
✅ Use WHERE clauses on partition columns
✅ Create external tables for repeated queries
✅ Leverage result caching (identical queries)
✅ Use statistics for query optimization

-- 3. Cost Management
✅ Monitor data scanned in query results
✅ Set up cost alerts in Azure Monitor
✅ Use views to enforce column selection
✅ Educate users on cost-effective patterns

-- 4. Security
✅ Use Azure AD authentication
✅ Implement row/column-level security
✅ Audit access with diagnostic logs
✅ Never expose credentials in queries

-- 5. Monitoring
✅ Enable diagnostic logging
✅ Track query performance in Azure Monitor
✅ Set up alerts for failed queries
✅ Review cost trends weekly