Master Lakehouse Federation: Step-by-Step Tutorial

0:00hello my name is Yusef and today I am introducing laycar's Federation Vehicles Federation is the queer Federation platform for databricks which describe a collection of features that enable users and systems to run queries against multiple data sources without needing to migrate all data to any

0:22unified system databricks uses Unity catalog to manage query Federation lake house Federation supports connection to the following databases so we have of course Azure SQL data warehouses Microsoft SQL Server redshift postgrave mySQL database and snowflake

0:46and why should I use the confederation latest Federation emphasize Central Storage of data to reduce data redundancy and isolation but your organization may have numerous Data Systems in production and you may want to query data in Connected systems for a number of reasons can be supporting workloads during incremental migration

1:09POC ad hoc reporting and something very very important Unity catalogs data governance and data initials ensure that data access is managed and audited for all Federated queries made by users in your databricks workspace and you will see in a few how to set up

1:31a query Federation or lake house Federation by creating a connection and then a foreign catalog but before let's let's have a tour or walk about let's talk about the requirement first of all you need to have a Unity catalog enabled for your workspace and for the compute

1:53requirements it requires SQL warehouses to be either serverless or Pro to have databricks runtime uh uh 13.1 or above and shared cluster access mode and of course network connectivity from your databricks runtime cluster or

2:15SQL Warehouse to Target the database system and now let's move to the demo thank you okay so now that I am in the platform I will go to SQL warehouses and create

2:37click on create so I'll call let's call it uses I'll go for a large and then I will choose of course serverless but you can either go for serverless or Pro and I create it and then I'll go to the data Explorer then external and then I click on

3:00connections then create connection that's the name I'm gonna give to my connection or the connection type for my case it's SQL Server and then I'm gonna bring the different information required to login to my database

3:23you need to get the different connection details The Host the port foreign and of course always make sure to test the connection I will select my require point just and see if it works

3:48yes so the connections established so now I can create my connection and of course something important to make sure to Grants permission to be different uh people or groups you cannot work with of course you can grant permissions by group or users you can you get permission create foreign catalog or privileges so I'm gonna Grant

4:10all privileges to me that's it much easier and then I'll go to the SQL editor and as you can see now I will create my foreign catalog that would that would be based on the connection I've created previously called Titanic and I will

4:33choose the database user of course I could have created the connection not using the UI but using the SQL SQL command and now I'll create my scoring catalog called Titanic let me just select MySQL Warehouse

4:58and then I'll go to the data Explorer let's look for Titanic and you see if I select dbo you will see that I have my three tables passenger ports and tickets and the next step I will create a new

5:22catalog called Titanic enhanced operator schema called enrich and then I will create a table called survive passengers in this uh new Delta table I will run some Transformations I will clean the data I will create a new column and join it with a table with the table parts and then I'll uh I'll display the results

5:49and this will create a new table called survived passengers and of course I'll make sure to save this query and you will see that in this table I will have something very very important which is the lineage so if I go to the data Explorer

6:13and the table it's the catalog is called Titanic enhanced so let me just open the data Explorer so Titanic enhanced enriched revive and of course I'll go to lineage

6:36so we think I was able to track the lineage to the SQL Server tables that I have on my collection so you see I've used the table ports the table passengers and this helped me to create my new Delta table called survive passengers and if I go to lineage so you

6:58see that I have those two tables and of course the query which is get started with lake house Federation and of course then you can just go for example I'm gonna use filter for example p-class and then I can build different visualizations for example I go for pi let's call it survives

7:22to get some statistics and for example let's go for the gender still right okay honestly I can check the difference for example if you are if you are in class three like percentage of a dead person

7:45and per and the dead female and survived females so you can build dashboards you can build charts easily and then you can add them to a specific dashboard but you can really see how lake house Federation is so powerful without where it brings the possibility to avoid copying the data and just join your uh your

8:07different tables with the tables you have on on the lake house to enrich them and get the most of your of your data thank you