Ingest and Explore LEGO Datasets using Pipelines and Serverless SQL in Azure Synapse Analytics

Woman standing next to a projector showing the Azure Synapse Analytics logo.

In the April session of the Azure Synapse Analytics and Microsoft MVP series, I got to show one of my favorite demos: ingesting and exploring LEGO datasets! 🤓 In just a few minutes, I grab a compressed file from the web, decompress it, store it as a Parquet file in my data lake, and run SQL queries on it using Serverless SQL. It really is that simple:

If you want to try this demo yourself, you can follow the detailed steps below. Have fun! 😃

Your event needs a Code of Conduct. You need a plan and process for enforcing it. Here's why.

Imagine that you are organizing an event. You have been working for months to make it the best possible experience for everyone, the big day is finally here, speakers are ready to deliver their sessions, and attendees are starting to walk through the doors. Everything is finally coming together! Then you hear that one of your attendees has a history of stalking and harassing someone who is already at your event.

What do you do?

How would you handle a sensitive and difficult situation at your event?

I want you to think about this for a minute. Be completely honest with yourself.

Would you immediately take action? Would you know exactly how to handle the situation, what to do, who to talk to? Would you approach the offender? What would you say? Would you hear them out? Ask them to leave? How would you respond if they deny everything? If they tell you that the victim is lying and trying to ruin their life? If they tell you that they are the real victim? What if they cause a scene? Do you have on-site security to ask for assistance? Would you notify the victim? What would you tell them? Where would you go for privacy? What support would you offer? How would you follow up during and after the event if they are visibly scared or upset? How would you ensure their safety inside and outside the venue? Who would be taking care of your tasks while you are handling the situation?

Would you be handling the situation at all?

Failed to Subscribe / Failed to Activate Storage Event Trigger After Publishing in Azure Synapse Analytics

I ran into an issue today while trying to publish a storage event trigger in Azure Synapse Analytics. After publishing, I got error messages that said “failed to subscribe” and “failed to activate”. The storage event trigger had been published, but it wouldn’t start. Help! 😱

Screenshot of Azure Synapse Analytics notifications showing “failed to subscribe” and “failed to activate” errors.

(Skip to how I fixed the failed to subscribe / failed to activate errors 👇🏻)

The Error Messages

The error messages hinted at access control or permission issues:

Connecting to HubSpot from Azure Synapse Analytics or Azure Data Factory

HubSpot logo.

I spent a couple of hours today figuring out how to connect to HubSpot from Azure Synapse Analytics. Since it wasn’t straightforward for me, I decided to blog about how I did it. Hopefully, this post can help one or two others (or future me!) save some time 😊

The HubSpot Linked Service

The Azure Synapse Analytics (or Azure Data Factory) linked service connects to HubSpot using a Client ID, Client Secret, Access Token, and Refresh Token:

Screenshot of the HubSpot linked service.

How do we get these values from HubSpot?

The overall process is to:

  1. Create a private app in HubSpot to get the Client ID and Client Secret
  2. Authorize the private app and get authorization code
  3. Exchange authorization code for Access Token and Refresh Token
  4. Create linked service in Azure Synapse Analytics or Azure Data Factory

Let’s dig into the details! 🤓

Training Day at SQLBits 2022: Data Integration using Azure Data Factory or Azure Synapse Analytics

Are you new to Azure Data Factory or Azure Synapse Analytics? Maybe you’re an expert SSIS developer wondering how to take that first step into Azure? Do you feel a little overwhelmed by all the possible ways you can move and transform data these days? Join me at the ExCeL London on Tuesday, March 8th, 2022! I will be delivering a training day at SQLBits 2022 called Beginner’s Guide to Data Integration using Azure Data Factory or Azure Synapse Analytics 🥳

Person announcing the Beginner’s Guide to Data Integration using Azure Data Factory or Azure Synapse Analytics training day at SQLBits 2022.

My goal is to help kickstart your journey as an Azure Data Engineer, and for all of us to have a great and safe day of learning and networking. SQLBits are taking covid safety seriously by requiring proof of vaccination / tests, wearing masks, and ensuring proper spacing between seats. I will be there in-person and hope you will be able to join us too!

(Oh, and if you want to get the most out of your week at SQLBits? Check out Paul Andrew’s training day on Wednesday called Azure Data Integration Pipelines - Advanced Design and Delivery 🤓)