NTT DATA Business Solutions
NTT DATA Business Solutions | December 12, 2024 | 10 mins

How Microsoft Copilot enhances productivity and streamlines the workflows when building Azure Modern Data Platforms

Explore MS Copilot enhances productivity and streamlines workflows across the stages of Azure Modern Data Platform development in the day to day data engineering work.

A person stands in a mesmerizing room illuminated by cascades of multicolored lights

In todayʼs rapidly evolving technological landscape, efficiency, collaboration, and automation are key drivers for success, especially in data-centric operations. Microsoft Copilot, powered by generative AI, has redefined how data engineering teams build and maintain data platforms by integrating directly with Azureʼs Modern Data Platform.

In this blog, I discuss my experiences using Microsoft Copilot in my daily data engineering work and how Microsoft Copilot enhanced productivity and streamlined workflows across the stages of Azure Modern Data Platform development.

1. Simplified Data Ingestion and Integration

When working on large-scale modern data platform projects, ingesting and integrating data from various sources is often a complex and time-consuming process. We can use MS Copilot to streamline this by automating tasks such as:

  • Data Pipeline CreationCopilot can generate code or scripts for Azure Data Factory pipelines or Synapse pipelines based on user input, reducing the time it takes to integrate new data sources. Let us take an example by asking Microsoft Copilot:Copilot question: Please draft a synapse data pipeline ingestion flow for a REST API data source


Figure 1‑1 example of Copilot creating the data pipelines for a REST API data source
  • API Integration

Below, Copilot gives you a quick ingestion flow so you can do the pipeline setup quickly.

Figure 1‑2 Example of Copilot generated the pipeline flow

Building on the response provided by Copilot in the example above, it is possible to continue the conversation and to ask Copilot for the details of pagination data processing in the pipeline REST API Request. This really showcases the intelligent and natural conversational nature of GenAI, which is very different from traditional web searches!

Below is an example that is based on the previous Copilot response:

Copilot question: please draft the pagination processing details for the above CopyToStorage pipeline

Figure 1-3 example of Copilot generating pagination processing details in CopyToStorage pipline

Finally, it is possible to ask Copilot to generate the Synapse pipeline resource file. As an data engineer, I can then customise the code and import it into my Synapse workspace and then start development now!

Figure 1‑4 Example of Copilot generating resource file for data pipeline

By tasks, a data engineering team can focus on higher-value activities like data modelling or advanced analytics work.

Based on my experience with MS Copilot in this data ingestion scenario, the generated resource files can be imported and used either in ADF or Synapse workspace, and those resource files define the prototype and pipeline framework. However, a Data Engineer, you will still need to define the Linked Services, Datasets, Parameters and any additional logic if needed, to enhance the imported pipelines.

2. Accelerated Data Processing with AI-Powered Optimizations

Once data is ingested, the next challenge is processing it efficiently.

Copilot can help developers and data engineers by:

  • Optimizing SQL Queries, Copilot can analyse SQL queries written for Synapse or Azure SQL and suggest performance improvements, ensuring that queries run faster with less resource consumption.

Figure 2‑1 Example of Copilot provided tuning tips for SQL script
  • Code Generation for Spark and SQL, By translating high-level descriptions into executable Spark code or SQL query, Copilot allows non-experts to work on data transformations, emocratizing access to data engineering workflows.

Below is an example to let Copilot generate the Synapse Spark notebook for a common Delta Merge activity.

Copilot question: Please generate a synapse spark notebook for ingested data merge to the existing dataset with delta lake parquet format in incremental load mode.


Figure 2‑2 Example of Copilot generated the Spark notebook for merging source into delta table

This AI-powered optimisation drastically on debugging or reworking inefficient code, leading to faster data processing and reduced costs.

3. Intelligent Data Analytics and Reporting

Building modern data platforms isnʼt just about integrating and processing data; it is about extracting actionable insights. Copilot helps downstream analysts and business users with the following:

  • Automate Report Generation – Using tools like Power BI integrated with Azure, Copilot can generate dashboards or reports based on verbal queries, eliminating the need for complex manual reporting.
  • Natural Language Queries – Users can ask questions in natural language, and Copilot translates them into queries, allowing for faster, more intuitive interaction with the data, DAX function and DAX script generation, whether using Power BI or other analytics services.

Below is an example of asking Copilot via natural language to generate a SQL query for product sales. Copilot can use the SQL function to get the top 10 sales in each state. Copilot even knows to use CTE (Common Table Expression) for this query.

Copilot question: Can you please generate a SQL query for select product sales across different state and only show top 10 sales in each state?

Figure 3‑1 Example of Copilot drafting a SQL query according to user’s natural language description

Another example is to create Power BI DAX scripts. These DAX scripts will help you calculate and analyse monthly sales data across different regions and stores. You can use these measures in your Power BI reports to create visualizations and gain insights into your sales performance.

Copilot question: Please create power BI DAX script to calculate monthly sales across different regions and stores

Figure 3‑2 Example of Copilot generate DAX calculations and Measures for Power BI report

By automating analytics tasks, Copilot allows decision-makers to focus on strategic insights instead of technical details.

4. Scalable and Secure Infrastructure Automation

Managing infrastructure in Azure can be complex, especially when deploying scalable, secure, and cost-efficient data platforms. With Copilot:

  •     Infrastructure as Code IaC Copilot helps generate Azure Resource Manager ARM templates or Terraform scripts, enabling teams to deploy infrastructure consistently and securely.
  •     Security Recommendations Copilot can analyse security settings and recommend optimisations, ensuring that Azure environments adhere to best practices, particularly around Azure RBAC, Privileged Identity Management (PIM), and Schema Permission of Synapse serverless SQL pool and Synapse Dedicated SQL pool.

This infrastructure automation enables teams to deploy environments faster, reduce manual configuration errors, and ensure that security is baked in from the start.

5. Conclusion

Microsoft Copilot acts as an intelligent assistant, enhancing productivity and streamlining workflows across the development of Azure Modern Data Platforms. From data ingestion to analytics, and infrastructure automation, Copilot transforms traditionally manual tasks into more automated, more efficient processes, allowing organisations to focus on delivering business value.

By leveraging Microsoft Copilot within the Azure Data and Analytics stack, data teams can on repetitive tasks, improving data quality and governance, and build modern data platforms that are scalable, secure, and future proof.

Reflecting on my experiences using Microsoft Copilot (for a while now) when building Azure Modern Data Platforms, it has helped me reduce time spent in design and build by about 30%, (about 2-3 hours saving per day). It has also helped increase my on deliverables with reduced research and development time.

With upgrading of underlying OpenAI models from time to time by Microsoft, I believe Microsoft Copilot will have much more use cases for end-users.

Author