T-SQL Transformation in Azure Synapse Analytics

T-SQL Transformation in Azure Synapse Analytics

Why T-SQL Transformation in Azure Synapse Analytics Is Important

T-SQL (Transact-SQL) transformation is a foundational skill for Azure Data Engineers because it enables you to manipulate, cleanse, reshape, and enrich data directly within Azure Synapse Analytics. In modern data engineering pipelines, data rarely arrives in the exact format needed for analytics and reporting. T-SQL transformations allow you to bridge the gap between raw ingested data and analytics-ready datasets without needing to move data to external processing engines. For the DP-203 exam, Microsoft expects candidates to demonstrate proficiency in writing and optimizing T-SQL queries within both dedicated SQL pools and serverless SQL pools in Synapse Analytics.

What Is T-SQL Transformation in Azure Synapse Analytics?

T-SQL transformation refers to the use of Transact-SQL statements to transform data within Azure Synapse Analytics. This includes operations such as:

- Data type conversions using CAST() and CONVERT()
- String manipulations using functions like SUBSTRING(), REPLACE(), TRIM(), CONCAT()
- Date and time transformations using DATEADD(), DATEDIFF(), FORMAT(), GETDATE()
- Aggregations using GROUP BY, SUM(), AVG(), COUNT(), MIN(), MAX()
- Window functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), NTILE()
- Conditional logic using CASE expressions and IIF()
- Pivoting and unpivoting data using PIVOT and UNPIVOT operators
- JSON and XML parsing using OPENJSON(), JSON_VALUE(), JSON_QUERY(), and XML methods
- Common Table Expressions (CTEs) for readable, modular query logic
- MERGE statements for upsert (insert/update) operations
- CREATE TABLE AS SELECT (CTAS) for materializing transformation results into new tables
- External table queries using OPENROWSET() in serverless SQL pools to query files in Azure Data Lake Storage

These transformations can be applied in both dedicated SQL pools (formerly SQL Data Warehouse) and serverless SQL pools, though the capabilities and best practices differ between the two.

How T-SQL Transformation Works in Azure Synapse Analytics

1. Dedicated SQL Pools

Dedicated SQL pools use a Massively Parallel Processing (MPP) architecture. Data is distributed across 60 distributions, and T-SQL queries are compiled into distributed execution plans. Key considerations include:

- Distribution strategy: Tables can be distributed using HASH, ROUND_ROBIN, or REPLICATE. The choice of distribution affects join performance and data movement during transformations.
- CTAS pattern: The CREATE TABLE AS SELECT (CTAS) statement is the preferred pattern for transformations in dedicated SQL pools. It is more performant than INSERT...SELECT because it minimizes logging and leverages parallel data movement.
- Materialized views: You can create materialized views that pre-compute and store transformation results, improving query performance for repeated analytical queries.
- Stored procedures: T-SQL transformations can be encapsulated in stored procedures and orchestrated by Azure Synapse Pipelines or Azure Data Factory.
- Temporary tables and table variables: Temporary tables (prefixed with #) are session-scoped and useful for staging intermediate transformation results.

Example of CTAS transformation:
CREATE TABLE dbo.SalesTransformed
WITH (
  DISTRIBUTION = HASH(ProductId),
  CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT
  ProductId,
  CAST(SaleDate AS DATE) AS SaleDate,
  UPPER(TRIM(ProductName)) AS ProductName,
  Quantity * UnitPrice AS TotalAmount,
  ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY SaleDate DESC) AS RowNum
FROM dbo.SalesRaw;

2. Serverless SQL Pools

Serverless SQL pools allow you to query data directly in Azure Data Lake Storage (ADLS) without loading it into tables first. Key points include:

- OPENROWSET() function: Used to read Parquet, CSV, Delta Lake, and JSON files directly from ADLS.
- External tables: You can define external tables pointing to files in ADLS using CREATE EXTERNAL TABLE, enabling T-SQL transformations on data lake files.
- CETAS (CREATE EXTERNAL TABLE AS SELECT): Allows you to write transformation results back to the data lake as Parquet or CSV files.
- Schema inference and explicit schema definition: Serverless pools can infer schemas from Parquet files or you can explicitly define column names and types using the WITH clause in OPENROWSET().
- File metadata functions: filepath() and filename() functions allow you to filter and partition data based on folder structure and file names.

Example of serverless SQL pool transformation:
SELECT
  filepath(1) AS Year,
  filepath(2) AS Month,
  JSON_VALUE(doc, '$.customerId') AS CustomerId,
  CAST(JSON_VALUE(doc, '$.amount') AS DECIMAL(10,2)) AS Amount
FROM OPENROWSET(
  BULK 'https://mydatalake.dfs.core.windows.net/raw/sales/*/*/*/*.json',
  FORMAT = 'CSV',
  FIELDTERMINATOR = '0x0b',
  FIELDQUOTE = '0x0b'
) WITH (doc NVARCHAR(MAX)) AS rows;

3. Key Transformation Patterns

- Slowly Changing Dimensions (SCD): Use MERGE statements or CTAS with conditional logic to implement SCD Type 1 and Type 2 patterns.
- Data deduplication: Use ROW_NUMBER() with PARTITION BY to identify and remove duplicate rows.
- Data flattening: Use CROSS APPLY with OPENJSON() to flatten nested JSON arrays into tabular format.
- Star schema transformations: Use T-SQL to split raw denormalized data into fact and dimension tables.
- Incremental transformations: Use watermark columns (e.g., LastModifiedDate) combined with WHERE filters to process only new or changed data.

Key Differences Between Dedicated and Serverless SQL Pools for T-SQL

- Dedicated SQL pools support DML operations (INSERT, UPDATE, DELETE, MERGE) while serverless SQL pools are primarily read-only (with CETAS for writing to the lake).
- Dedicated SQL pools require data to be loaded first; serverless pools query data in place.
- Not all T-SQL features are available in both pools. For example, serverless pools do not support creating regular tables, indexes, or stored procedures with the same capabilities as dedicated pools.
- Dedicated pools are best for repeated, complex transformations on large datasets. Serverless pools are ideal for ad-hoc exploration and lightweight transformations.

Performance Optimization Tips for T-SQL Transformations

- Use CTAS instead of INSERT...SELECT in dedicated pools for better performance.
- Choose the correct distribution key to minimize data movement during joins and aggregations.
- Use result set caching in dedicated pools for frequently repeated queries.
- Use statistics (CREATE STATISTICS or AUTO_CREATE_STATISTICS) to help the query optimizer generate efficient plans.
- Avoid data movement operations (ShuffleMove, BroadcastMove) by aligning distribution keys on join columns.
- In serverless pools, use Parquet format instead of CSV for better performance due to columnar storage and predicate pushdown.
- Use partition elimination in serverless pools by leveraging filepath() in WHERE clauses.

Exam Tips: Answering Questions on T-SQL Transformation in Azure Synapse Analytics

1. Know the CTAS pattern thoroughly: The DP-203 exam frequently tests your understanding of CTAS as the preferred transformation method in dedicated SQL pools. Remember that CTAS creates a new table and is minimally logged, making it faster than INSERT...SELECT.

2. Understand OPENROWSET() syntax: Be comfortable with the OPENROWSET() function for serverless SQL pools, including specifying BULK paths, FORMAT, and the WITH clause for schema definition. Expect questions about querying Parquet, CSV, and JSON files.

3. Differentiate dedicated vs. serverless capabilities: Know which T-SQL features are available in each pool type. If a question asks about UPDATE or DELETE operations, the answer likely involves a dedicated SQL pool. If it asks about querying data lake files in place, it involves a serverless SQL pool.

4. Master window functions: Questions often involve scenarios where you need to rank, deduplicate, or compute running totals. Be familiar with ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and their PARTITION BY and ORDER BY clauses.

5. Understand CETAS for serverless pools: CREATE EXTERNAL TABLE AS SELECT is how you persist transformation results from serverless SQL pools back to the data lake. Know the required components: external data source, external file format, and the CETAS syntax.

6. JSON transformation questions: Be prepared for questions about parsing JSON data using OPENJSON(), JSON_VALUE(), and JSON_QUERY(). Understand how to flatten nested JSON structures using CROSS APPLY with OPENJSON().

7. Distribution and performance: Expect scenario-based questions about choosing the right distribution strategy (HASH, ROUND_ROBIN, REPLICATE) to optimize T-SQL transformation performance. Hash distribution on join/group-by columns reduces data movement.

8. MERGE statement for upserts: Know the MERGE syntax for implementing upsert logic, especially in the context of maintaining slowly changing dimensions in a dedicated SQL pool.

9. Watch for trick answers involving unsupported features: Some T-SQL features available in SQL Server are not supported in Synapse. For example, certain system stored procedures, cross-database queries (in dedicated pools), and triggers are not available. If an answer option uses an unsupported feature, eliminate it.

10. Read the scenario carefully: Pay attention to whether the question specifies dedicated SQL pool, serverless SQL pool, or Spark pool. The correct T-SQL approach differs significantly depending on the compute engine specified.

11. Understand external tables vs. OPENROWSET(): External tables are persistent metadata objects that can be referenced by multiple queries, while OPENROWSET() is an ad-hoc inline function. If the scenario requires a reusable, shared definition, external tables are the answer. For one-time or ad-hoc queries, OPENROWSET() is appropriate.

12. Practice combining transformations: Real exam questions often combine multiple concepts—for example, using OPENROWSET() to read a Parquet file, applying CAST() and window functions, and writing results using CETAS. Practice writing queries that chain multiple transformation steps together.