Azure Synapse Analytics: Introduction to Copy Activity [Introduction to Synapse Analytics - Ep. 10]

In this episode of the Introduction to Synapse Analytics series, Mitchell Pearson walks through the process of creating your first copy activity in Azure Synapse Analytics (ASA). This tutorial provides a practical overview of ETL (Extract, Transform, Load) concepts and demonstrates how to move data between sources using linked services and datasets.

What is ETL and Why It Matters?

ETL stands for Extract, Transform, and Load. It’s a common process in data engineering where:

• Extract: Pull data from various sources such as Salesforce, SharePoint, SQL Server, or Oracle.
• Transform: Clean and curate the data for analysis or reporting.
• Load: Move the data into a centralized location for easy access across the organization.

Companies often struggle to consolidate data from multiple platforms. Azure Synapse Analytics simplifies this by providing tools to connect, transform, and load data efficiently.

Step 1: Understanding Linked Services

A Linked Service in ASA acts as a connection manager. It defines:

• The location of your data source (e.g., SQL Server, Data Lake, SharePoint).
• The credentials required for authentication.

When you provision an ASA workspace, default linked services are created automatically for your Synapse workspace and primary Data Lake. You can also create custom linked services for other sources like Amazon S3, SAP, or GitHub.

Step 2: Creating Datasets

After setting up linked services, the next step is creating Datasets. A dataset points to a specific file, table, or entity within your data source. For this demo:

1. Create a dataset for the source file (e.g., holiday.snappy.parquet in the Data Lake).
2. Create a second dataset for the destination (e.g., an archive folder in the Data Lake).

Datasets can be parameterized for dynamic file names, but this example keeps it simple with two static datasets.

Step 3: Building the Pipeline

With linked services and datasets ready, you can create a pipeline:

1. Navigate to the Integrate hub in ASA.
2. Create a new pipeline and name it (e.g., Simple Copy Demo).
3. Add a Copy Data activity from the Move and Transform category.
4. Configure the source and sink using the datasets you created.
5. Optionally, set wildcards or filters for dynamic file selection.

Finally, run the pipeline in Debug mode to validate the process before publishing.

Benefits of Using Pipelines

• Cost-effective: Pipelines are generally less expensive than notebooks for simple copy operations.
• User-friendly: The visual interface makes it easy to understand and share workflows.
• Flexible: Supports multiple activities like stored procedures, logic apps, and data transformations.

Key Takeaways

• Linked Services act as secure connection managers.
• Datasets define the specific data you want to move.
• Pipelines orchestrate the process with intuitive UI-driven design.

This quick example demonstrates how to copy data from one location to another in Azure Synapse Analytics. For more advanced scenarios, explore parameterization, dynamic datasets, and integration with other Azure services.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Azure Synapse and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.