In this episode of the Azure Synapse Analytics series, Mitchell Pearson from Pragmatic Works introduces one of the most important foundational concepts in data integration: the Copy Activity. He explains how organizations often need to move data from multiple source systems into a centralized location, and how Azure Synapse Analytics makes that possible through linked services, datasets, and pipelines.
Mitchell begins by framing a common business challenge. Many companies store data across several systems such as SharePoint, Dynamics, SQL Server, Oracle, on-premises environments, and other platforms. For reporting, analytics, and training purposes, that data often needs to be gathered into one place so the organization can use it effectively. He notes that this is a frequent issue encountered during company training sessions and hackathons, where businesses must first prepare and centralize usable data before they can start building reports or analytics solutions.
Mitchell briefly explains the concept of ETL, which stands for Extract, Transform, and Load. This is a core process in data engineering and analytics workflows:
In this video, the focus is on a simple example of moving data from one location to another using Copy Activity in Azure Synapse Analytics. Rather than diving into complex transformations, Mitchell keeps the example straightforward so viewers can become familiar with the essential components.
One of the first concepts Mitchell introduces is the linked service. He describes a linked service as essentially a connection manager. Its role is to tell Azure Synapse where the data lives and what credentials should be used to connect to it.
Mitchell explains that when an Azure Synapse workspace is provisioned, some linked services are automatically created. For example, one points to the Synapse SQL environment, while another points to the primary Data Lake associated with the workspace. These built-in linked services simplify initial setup and provide immediate connectivity to key resources.
He also shows that users can create their own linked services manually through the Manage hub. Azure Synapse supports a large number of built-in connectors, allowing connections to services such as Amazon S3, Redshift, Dynamics, GitHub, Hive, MySQL, PayPal, REST APIs, SAP, FTP, and SFTP. Mitchell highlights this flexibility as one of the platform’s strengths, since many common enterprise data sources are already supported out of the box.
After introducing linked services, Mitchell explains the next level in the hierarchy: datasets. While a linked service connects to a data source broadly, a dataset points to a specific file, table, folder, or entity. In other words, the linked service establishes the connection, and the dataset identifies the exact data object to read from or write to.
For his demonstration, Mitchell uses a Parquet file stored in Azure Data Lake Storage. He creates a source dataset that points to an existing file named holiday.snappy.parquet inside a training folder. Because the file already exists, he imports the schema so Azure Synapse can detect the column definitions and data types stored in the metadata-rich Parquet file.
He then creates a second dataset for the destination. This dataset points to a new archive location where the copied file will be written. Since the destination file does not yet exist, he does not import the schema for that dataset.
With the linked service already available and both datasets created, Mitchell moves on to the Integrate hub to build a pipeline. He creates a new pipeline and names it as a simple copy demo. Inside the pipeline, he drags in a Copy Data activity from the Move and Transform category.
He then configures the activity by assigning:
Mitchell points out that Synapse provides additional flexibility here, including the ability to work with wildcard file patterns, lists of files, or folder-based filtering. However, for this introductory walkthrough, he keeps the configuration simple and focused.
Once the pipeline is configured, Mitchell runs it using the Debug option. Debug allows developers to test pipeline logic before publishing changes. After the debug run completes successfully, he verifies the result by navigating back to the Data hub and confirming that the file has been copied into the archive folder.
This validation step demonstrates that the Copy Activity worked as intended and provides a practical example of moving data from one location to another inside Azure Synapse Analytics.
Mitchell closes by discussing why pipelines are valuable. He explains that pipelines are highly UI-driven, which can be both a benefit and a limitation depending on the user’s preferences. Some developers prefer writing code in notebooks, but pipelines offer several advantages:
Overall, Mitchell presents Copy Activity as an excellent starting point for learning data movement in Azure Synapse Analytics. By understanding linked services, datasets, and pipelines, users can begin building reliable ETL processes that support centralized reporting, analytics, and data management across the organization.
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.