Which Microsoft Fabric SQL Analytics Endpoint should you use?

Choosing the Right Microsoft Fabric SQL Analytics Endpoint for Your Needs

In the dynamic world of data management and analytics, decision-makers often grapple with choosing the best tools and technologies that align with their business needs. Microsoft Fabric, with its robust offerings, brings forward a range of SQL Analytics Endpoints that serve different purposes, each tailored to specific types of data operations. Understanding these endpoints can drastically improve how your organization interacts with data. Let’s dive into the specifics of which Microsoft Fabric SQL Analytics Endpoint is suitable for your use-case.

Introduction to Microsoft Fabric SQL Analytics Endpoints

Microsoft Fabric offers several SQL Analytics Endpoints, each designed to cater to different aspects of data management and querying within Lakehouse environments and warehousing solutions. These endpoints facilitate a diverse range of operations, from data querying to object manipulation, and understanding their capabilities can help you make informed decisions about deploying them effectively.

Understanding Lakehouse and Warehouse Endpoints

Lakehouse SQL Analytics Endpoint

The SQL Analytics Endpoint in a Lakehouse setup enables users to perform a variety of data querying operations. However, it’s crucial to note that this endpoint is somewhat limited when it comes to data definition languages (DDL). For example, while you can create views and functions, you cannot create tables directly. This restriction is pivotal to understand for those who might be planning to use the Lakehouse primarily for data storage and manipulation.

On the flip side, the Lakehouse endpoint allows read-only operations, meaning you cannot directly insert, update, or delete data using this endpoint. This setup is suitable for scenarios where data integrity and consistency are critical, and data is primarily ingested via batch processes or predefined streams rather than manual entries.

Warehouse SQL Analytics Endpoint

The Warehouse endpoint, contrastingly, offers extensive capabilities regarding data definition and manipulation. Users have the freedom to create tables, views, procedures, and more, enabling a flexible environment for managing a wide variety of data objects. This endpoint is ideally suited for users who need full control over their data structure and the ability to perform complex manipulations directly.

This endpoint also supports complete DML operations, including the ability to insert, update, and delete data. This flexibility makes the Warehouse endpoint a preferred choice for environments where active data management is required.

Use-Cases: Where Each Endpoint Shines

Scenario-Based Application of Lakehouse Endpoint

Analytics and Reporting: Ideal for scenarios where data needs to be accessed but not altered, ensuring that the reporting or analytical data remains consistent and reliable.
Data Viewing and Sharing: When multiple departments or teams need access to data without the risk of altering it, the Lakehouse endpoint is perfect as it ensures data viewers can execute read-only queries.

Scenario-Based Application of Warehouse Endpoint

Data Management: For environments that require regular updates, such as inventory systems or real-time transaction processing systems, the Warehouse endpoint offers the necessary tools to manipulate data efficiently.
Complex Query Operations: When the creation of new tables, updating schemas, or performing batch updates and deletions are routine tasks, the Warehouse endpoint provides the required capabilities without restrictions.

Selecting the Right Endpoint for Your Needs

The decision between using a Lakehouse or a Warehouse SQL Analytics Endpoint ultimately boils down to the specific needs of your project:

Read-Only vs. Read-Write: Choose the Lakehouse endpoint for read-only access, and opt for the Warehouse endpoint for read-write operations.
Complexity of Data Operations: For more complex data structuring and direct database manipulation, go with the Warehouse endpoint.

Conclusion

When working with Microsoft Fabric, understanding the nuances between different SQL Analytics Endpoints can lead to more efficient data management strategies that align closely with your business objectives. Whether your focus is on maintaining data integrity in a read-only environment or actively manipulating data sets, choosing the right endpoint is crucial for optimizing your operations within the Microsoft Fabric ecosystem.

Remember, the right choice will depend on your specific use-case scenarios, and flexibility might be required as your data strategies evolve. Always consider future scalability and potential changes in how your data is used when selecting your SQL Analytics Endpoint.

[h3]Watch this video for the full details:[/h3]

Both the Microsoft Fabric lakehouse and warehouse have a SQL Analytics Endpoint. But they have different limitations. Which one is the right one to use? Patrick breaks it down!

Better together: the lakehouse and warehouse
https://learn.microsoft.com/fabric/data-warehouse/get-started-lakehouse-sql-analytics-endpoint

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— http://www.facebook.com/guyinacube
— https://www.instagram.com/guyinacube/
— https://guyinacube.com

***Gear***
🛠 Check out my Tools page – https://guyinacube.com/tools/

#MicrosoftFabric #SQLEndpoint #GuyInACube

[h3]Transcript[/h3]
– Yooo! What’s up? It’s Patrick from Guy in a Cube. We’ve been talking about workloads inside of Fabric, and one of the things we haven’t touched on specifically is the endpoints that are available when you create a Lakehouse in a warehouse and we’ve been getting questions about it, what’s the difference? What are they? What can you do with them? What can’t you do with them? And Adam and I had a funny interaction when we were up in Norway at Fabric February, where we had this debate about whether or not you can use SQL, notice I didn’t say T-SQL, to create objects and insert data into those objects using the Lakehouse, using SQL and Adam was adamant that you couldn’t do it. And I was like, of course you can. And so I’m gonna show you all these things in this video. So enough of all this talking, you know what we like to do? Let’s head over to my laptop. So you can see in my workspace, I have a few items. Specifically, I have a Lakehouse and a warehouse. When you create a Lakehouse, you get the Lakehouse, the default Semantic model, and you also get a SQL Analytics endpoint. When you create the warehouse, you just get the warehouse and the Semantic model. And you may be thinking, "Well, Patrick, you said you get an endpoint when you create both." You absolutely do, but there’s some differences between them. So to get to the Analytics endpoint in the Lakehouse, you can click the Lakehouse itself, and then you can switch over to the SQL Analytics endpoint, and that’ll bring you to the view where you can start querying items and doing stuff like that. Or you can click directly on the SQL Analytics endpoint and it’ll take it to the exact same view. If you wanna get the endpoint itself, what you would do, regardless of which path you took to get here, you click the little settings gear, and then you’ll see the SQL connection string. And you can copy this on your clipboard. And you can see this is telling you what this is for, right? You copy this string to use it to connect externally to an item from Power BI Desktop or client tools like SSMS, Azure Data Studio, anything that can connect to SQL Server, you can use this endpoint tool. And then you can also get to the endpoint by simply clicking on ellipsis for the analytics endpoint and copy the connection string. For the warehouse, you click the warehouse and you click settings and then you can copy the end point there. Or just like the analytics endpoint for the Lakehouse, you can click it and you can copy the connection string. Once you have the connection string on the clipboard, you can go to tools like Power BI, Management Studio, or Azure Data Studio, you can create a new connection. And then you pop that survey name in with the database name, switch the authentication to Universal with MFA, and you can get authenticated. And now you can see here, I have my objects in my warehouse. That’s my end point. That’s the name of the database. And then in my Lakehouse, that’s the end point, and that’s the name of the Lakehouse. Both endpoints allow you to connect to the warehouse or the Lakehouse. You can write queries and you can view the objects in the corresponding items, okay? Now, there’s three types of queries that you would typically run. You would run data definition, you would use data manipulation language and data query language. And there’s differences between the two depending on the endpoint you use. From a data definition perspective, the warehouse gives you full capabilities, right? You can create tables, procedures, views, functions. With the analytics endpoint that you get with the Lakehouse, you have a limited set of things you can do. So you can create views and functions and even store procedures, but you cannot create tables. So if I head back over to the warehouse, I have a query that I’ve created. And what I’m gonna do is I’m gonna run this. So this will drop the table if it exists, and it’ll go ahead and create the table. So I’m gonna run this, and it creates the object. So I’m gonna copy this on my clipboard, and let’s go ahead and refresh this just so you can see that that table was created. Boom, you can see my table was created. Let’s hop over to the Lakehouse. Let’s open the Lakehouse, the analytics endpoint for the Lakehouse so we can write a query. And we’re gonna drop this right here, and let’s run this. Let’s see what happens. Let’s try to run this. It says you can’t. You can’t create tables. So using T-SQL, remember I said T-SQL, you cannot create tables with the analytics endpoint, but with the endpoint in the Warehouse, you can create those tables. So data definition is limited. And I’ll circle back to this when I get to data query lanes to show you what you can do with the Lakehouse in regards to creating objects, okay? Now, from a data manipulation perspective with the Warehouse, you can absolutely write insert statements, update statements, and delete statements. With the analytics endpoint in the Lakehouse, it’s read only. You cannot manipulate data. So if I go back over to my Warehouse, and I’m gonna insert some data, so let’s go ahead and insert that data. There you go. My data’s inserted. We’ll run a query and I’ll show you, but I’m not even gonna try it in the Lakehouse because you just can’t. You can’t create tables and you can’t manipulate data, okay? What about data query? If I just want to read the data out. Well, let me show you. I’m back in my warehouse, and if I do a select, I’m gonna do a quick select right here, and you’ll see, boom. I can query that data. If I head over to the Lakehouse, I’m gonna head over to the analytic endpoint for the Lakehouse. And so we have a query here. Let’s see if we can run this. I mean, my Lakehouse, well, it says no, it doesn’t because no tables exist, but I can do three part naming. I can say My Warehouse, if I could spell. It even gave me IntelliSense and I ignored it. And so we’ll run this query, and I can do a three part name from the Lakehouse to the warehouse. So if the Lakehouse and the Warehouse are in the same workspace, I can say MyWarehouse.Schema.ObjectName, and I can select from the table in the Lakehouse, right, we can query your way in bold. We can even use three part naming. Now, when I started this conversation, I said, "Data definition is limited in the Lakehouse "because you can’t create tables, "but you can create objects." Lemme show you. So I’m over here in my Lakehouse view, and I can say, "Drop proc if exists get products, go." And then I can say, "Create proc, get products as", I can add parameters, I can do all types of things here. So I’m gonna run this, it’ll drop that procedure and then create it and then I can run that procedure directly and it returns my data. I can add parameters, I can do all types of things. If I go to schemas, you can see I can create views, I can create functions, I can create stored procedures. While you don’t have the full parity of the T-SQL DDL language inside of the Lakehouse, you still can create objects. You can’t manipulate the data, and you can’t create tables, but you still have that ability. Now, Adam and I had this debate in front of a room of like 90 people. He said, "You said, Patrick, you can’t create tables "using T-SQL." I said, "T-SQL, but from a Spark-SQL perspective, "you can absolutely do it." And that’s my little loophole, because Adam was like, "Oh, you can’t do it." I was like, "Challenge accepted, my friend. "Let me show you." I created this little notebook and let’s go to edit. Let’s clear all my outputs so we can start from scratch. And so I’m gonna run this. So I’m gonna run this, in just a few seconds, it’s gonna create that object. It’s gonna create this table for me. So if I click a refresh right here, you’re gonna see there’s my table, right? And if I wanted to insert data, I could run this. And what you’ll see is that it’ll insert some data into that table and if I run a quick select, you’re gonna see, boom, there’s my data. Adam was like, "Wait, wait, Patrick." I’m like, "Yes, just by changing the language "of that code cell." And so if I go in the code cell, you can see at the top, I put a little percent percent SQL, or I can switch it using the picker right here to Spark-SQL. Using Spark-SQL, which is a SQL language, I can actually create tables and manipulate data in my Lakehouse. And he is like, "Okay, man, I quit. "I give up, you’re right." I’m like, "Of course I am right. "I’m always right." Anyway, all right. What do you think? Have you been working with these analytics endpoint? There are definitely some things you should look at. I’m including a doc in the comments below that talks about how the Lakehouse and the warehouse can work better together and there’s some section in there about how you can use these endpoints to do three part naming and query across. You should check it out. If you have any questions, comments, you know what to do, post it in the comments below. If you wanna learn more about Fabric, all the Fabric workloads, I guarantee there’s a video flying above my head. And as always, from Adam and myself, thanks for watching. We’ll see you in the next video. Boom, I’m out.