DEV Community: Reiji Otake

Fabric & Databricks Interoperability (4): Using Databricks Tables in Fabric for Viewing, Analysis, and Editing

Reiji Otake — Sun, 16 Feb 2025 10:52:27 +0000

Introduction

Is it possible to seamlessly reference and edit tables created in Fabric within Databricks?

Many people may have this question.

In this article, we will specifically explore the use case of:

Utilizing tables created in Databricks within Fabric.

For prerequisite settings and configurations, please refer to previous articles.

This article is part of a four-part series:

Overview and Purpose of Interoperability

Detailed Configuration of Hub Storage

Using Tables Created in Fabric in Databricks

Using Tables Created in Databricks in Fabric (this article)

Linking Tables Created in Databricks to Fabric

Creating a New Table in Databricks

Create a new empty external table from Databricks.

Specify the folder path of the hub storage as the Location.

CREATE TABLE create_from_Databricks_sales
USING DELTA
LOCATION 'abfss://<container_name>@<ADLS2_name>.dfs.core.windows.net/folder_name/create_from_Databricks_sales'

Checking the Created Table

You can verify that the external table created from the Catalog Explorer contains data.

A folder named create_from_Databricks_sales is created in the ext folder of the hub storage.
(This means that the newly created external table physically exists in the hub storage.)

It can also be confirmed that the table is in Delta format.

At this point, the create_from_Databricks_sales table also becomes visible from Fabric's Lakehouse.

Viewing and Analyzing Tables Created in Databricks in Fabric (Creating BI)

From the Semantic Model, select the create_from_Databricks_sales table (created in Databricks) and click [Confirm].

Now, the table created in Databricks can be analyzed in Fabric.

Editing (DML) Tables Created in Databricks from Fabric

Execute an UPDATE statement (DML statement) from Fabric's Notebook.

UPDATE Fabric_Lakehouse.ext.create_from_Databricks_sales
SET Item = 'No.1 Quantity Water Bottle - 30 oz.'
WHERE Item = 'Water Bottle - 30 oz.'

Of course, it was confirmed that changes were reflected from Fabric.

Editing was performed from Fabric, and the changes were also reflected on the Databricks side.

Therefore, it is possible to edit (DML statements) in Fabric for tables created in Databricks.

Issues and Specific Operational Methods

The method introduced here has the advantage that both Fabric and Databricks can edit data. However, this can also be a weakness, as it makes table updates too easy.

Additionally, in this case, an external table in Databricks was used.

However, predictive optimization is currently only available for managed tables, making it ideal to use managed tables rather than external ones.

Predictive optimization for Unity Catalog managed tables | Databricks on AWS

docs.databricks.com

We will continue to examine these challenges and share specific operational methods in the future.

I also think that table cloning in Databricks might provide some useful hints.

Databricksにおけるテーブルのクローン #deltalake - Qiita

Clone a table on Databricks | Databricks on AWS [2022/10/28時点]の翻訳です。本書は抄訳であり内容の正確性を保証するものではありません。正…

qiita.com

Conclusion

Based on the above,

it was confirmed that "tables created in Databricks can be used in Fabric."

Once the hub storage is set up, achieving interoperability between Fabric and Databricks is relatively simple.

▽ Previous article

FabricとDatabricksの相互運用性③：Fabric で作成したテーブルをDatabricksで利用する（Databrickで閲覧・分析・編集可能） #BI - Qiita

はじめにFabricで作成したテーブルをDatabricksでもシームレスに参照・編集が可能できるのか？このような疑問を持つ方も少なくないと思います。そこで今回はFabric で作成したテー…

qiita.com

Fabric & Databricks Interoperability (3): Using Fabric Tables in Databricks for Viewing, Analyzing, and Editing

Reiji Otake — Sun, 16 Feb 2025 10:34:06 +0000

Introduction

Can tables created in Fabric be seamlessly referenced and edited in Databricks?

Many people may have this question.

In this article, we will specifically introduce the use case of:

Using tables created in Fabric within Databricks.

For details on the prerequisite settings, please refer to the previous article.

This article is part of a four-part series:

Overview and Purpose of Interoperability

Detailed Configuration of Hub Storage

Using tables created in Fabric within Databricks (this article)

Using tables created in Databricks within Fabric

Linking Tables Created in Fabric to Databricks

Creating a New Table in Fabric

Upload a CSV file to the Fabric Lakehouse.

:::note info
The CSV file used in this article is sales.csv from the following Microsoft documentation:

Create a Microsoft Fabric Lakehouse
:::

From the CSV file, select [Load to Table] > [New Table].

Specify ext, which is a shortcut created in the hub storage, as the schema.

Verifying the Created Table

You can confirm that a new table has been created in the Lakehouse.

A create_from_fabric_sales folder is created in the ext folder of the hub storage.

(This means that the newly created table physically exists in the hub storage.)

You can also confirm that the table is in Delta format.

At this point, as expected, the table created in Fabric is not yet visible in Databricks.

Enabling Databricks to Access Fabric Tables

Use the Databricks SQL Editor to create an external table.

Specify the hub storage folder path (the folder of the table created in Fabric) in the Location field.

CREATE TABLE <table_name>
USING DELTA
LOCATION 'abfss://<container_name>@<ADLS2_name>.dfs.core.windows.net/folder_name/<table_folder_name>'

Then, you can view tables created in Fabric from the [Catalog].

Viewing and Analyzing Tables Created in Fabric with Databricks (BI Creation)

From the [Dashboard] in Databricks, you can create a new dashboard and select an external table (i.e., a table created in Fabric) from [Data] > [Select Table].

Thus, it is possible to analyze tables created in Fabric using Databricks.

Editing Tables Created in Fabric with Databricks (DML)

Try executing an UPDATE statement (DML statement) from the SQL Editor in Databricks.

UPDATE create_from_fabric_sales
SET Item = 'No.1 Item'
WHERE Item = 'Road-150 Red, 48'

Of course, you can confirm that the changes have been reflected on the Databricks side.

Although the edit was made from Databricks, the changes were successfully reflected on the Fabric side as well.

SELECT Item, SUM(Quantity * UnitPrice) AS Revenue
FROM Fabric_Lakehouse.ext.create_from_fabric_sales
GROUP BY Item
ORDER BY Revenue DESC;

Therefore, it is possible to edit tables created in Fabric using Databricks (DML statements).

Conclusion

From the above, we have confirmed that

"Tables created in Fabric can be used in Databricks."

Once the hub storage is set up, it is relatively easy to achieve interoperability between Fabric and Databricks.

In the next article, we will introduce the reverse case:

"Using tables created in Databricks in Fabric."

▽ Next article

FabricとDatabricksの相互運用性④：Databrick で作成したテーブルをFabricで利用する（Fabricで閲覧・分析・編集可能） #SQL - Qiita

はじめにFabricで作成したテーブルをDatabricksでもシームレスに参照・編集が可能できるのか？このような疑問を持つ方も少なくないと思います。そこで今回はDatabricksで作成し…

qiita.com

▽ Previous article

FabricとDatabricksの相互運用性②：hubストレージ設定方法 -Databricks で作成したテーブルをFabric で利用する、Fabric で作成したテーブルをDatabricksで利用する- #Azure - Qiita

はじめに今回はDatabricks で作成したテーブルをFabric で利用するFabric で作成したテーブルをDatabricksで利用するというユースケースを実施するための設定方法につ…

qiita.com

Fabric & Databricks Interoperability (2): Configuring Hub Storage

Reiji Otake — Sun, 16 Feb 2025 09:48:01 +0000

Introduction

This article provides a detailed guide on how to configure settings for the following use cases:

Using tables created in Databricks within Fabric
Using tables created in Fabric within Databricks

:::note info
This article is part of a four-part series:
1. Overview & Purpose of Interoperability

2. Detailed Configuration of Hub Storage (This Article)

3. Using Tables Created in Fabric within Databricks

4. Using Tables Created in Databricks within Fabric

:::

Preparing Azure Data Lake Gen2 (ADLS2) as the Hub

① Deploy a storage account in Azure Portal as the hub

:::note warn
Enable hierarchical namespace.
:::

② Create a container named 'hub' and a directory named 'ext'

Connecting Fabric to Hub Storage

① Create a Lakehouse

:::note warn
Enable Lakehouse schema (public preview).
:::

② Specify the hub storage in the new schema shortcut of the Lakehouse

From [Tables] in the Lakehouse, click the three-dot menu and select [New Schema Shortcut].

Select [Azure Data Lake Gen2].

Enter the details for creating a new connection.

:::note info

How to check ADLS2 access URL:

You can confirm it from the storage account's [Endpoints] section under 'Data Lake Storage'.

:::

Enable the 'ext' directory and click [Next].

Click [Create].

The 'ext' directory is created as an external schema shortcut.

Connecting Databricks to Hub Storage

① Create an access connector for Azure Databricks in the Azure Portal

Follow the steps under "Step 1: Create an Access Connector for Azure Databricks" in the "Use Azure Managed Identity to Access Storage in Unity Catalog" guide, using a system-assigned managed identity.

② Grant the connector access to the hub storage from the Azure Portal

Follow "Step 2: Grant Managed Identity Access to the Storage Account" in the same guide.

③ Create storage credentials in Databricks

After creation, click the newly created credential name.

Click [Permissions] > [Grant].

Grant [ALL PRIVILEGES] to necessary users.

:::note info
Reference for steps and required permissions:

Create Storage Credentials for Connecting to Azure Data Lake Storage Gen2
:::

④ Add an external location in Databricks

:::note info

How to determine the URL:

Refer to the storage account's [Endpoints] section used in step ② of "Connecting Fabric to Hub Storage".
:::

:::note info
Reference for steps and required permissions:

Create an External Location to Connect Cloud Storage to Azure Databricks
:::

Conclusion

Now everything is set up!

Next, let's proceed with the actual interoperability of tables.

▽ Next Article

FabricとDatabricksの相互運用性③：Fabric で作成したテーブルをDatabricksで利用する（Databrickで閲覧・分析・編集可能） #BI - Qiita

qiita.com

▽ Previous Article

FabricとDatabricksの相互運用性①：hubストレージの目的 -Databricks で作成したテーブルをFabricで利用する、Fabricで作成したテーブルをDatabricksで利用する- #Azure - Qiita

はじめにそれぞれの特徴がありつつも、大枠ではできることが似ているMicrosoft FabricとDatabricks。Azure Databricks Unity カタログのミラーリングを通…

qiita.com

Fabric & Databricks Interoperability (1): Purpose of Hub Storage for Table Sharing

Reiji Otake — Sun, 16 Feb 2025 09:38:46 +0000

Introduction

Although they each have their own characteristics, Microsoft Fabric and Databricks are broadly similar in what they can do.

Through Azure Databricks Unity Catalog mirroring, we are now able to reference Databricks-managed data in Fabric, but editing the data is still not possible.

This brings up the following concerns:

Is it possible for our department to use tables managed by other departments in Databricks via our Fabric?
I want to make the tables I create open for modification and reference, regardless of the tool used!
We are currently using Databricks, but we might migrate to Fabric in the future... we want to maintain a vendor-free stance.
Business-side employees use Fabric, but engineers use Databricks; there are times when we need to reference the same table.

In this article, I will introduce use cases for seamlessly utilizing Fabric and Databricks:

Using tables created in Databricks in Fabric
Using tables created in Fabric in Databricks

The goal of this article:

:::note info
This article consists of four parts:

Overview and purpose of interoperability (this article)
Detailed setup of hub storage
Using tables created in Fabric in Databricks
Using tables created in Databricks in Fabric :::

Prerequisite: Fabric and Databricks have similar functions... which one should we actually use?

Fabric and Databricks are both attracting attention as Lakehouse platforms that handle data end-to-end.

As someone new to the industry, my first impression of using both was that "They can probably do about the same things?".
They both support ETL processes and AI model creation.

Fabric is appealing because of its beginner-friendly GUI, designed for intuitive operations.
On the other hand, Databricks is more code-based, so it seems to require a slightly higher skill level.
Additionally, Databricks offers more customization options for computer resources, and if you stop the cluster frequently, it can be more cost-effective than Fabric.

I believe the choice between these platforms depends on whether you prioritize ease of use or flexibility.

▽Reference

Azure Databricks と Microsoft Fabric の関係を考える🧐 #PowerBI - Qiita

はじめに本記事はDatabricks アドベントカレンダー2024 7日目の記事です。https://qiita.com/advent-calendar/2024/databricks/本記事…

qiita.com

Considering interoperability methods

Here, I will explore methods for achieving interoperability between Fabric and Databricks.

① Unity Catalog Mirroring (Currently DML from Fabric to Databricks is not supported)

Using Azure Databricks Unity Catalog mirroring (preview), it is possible to reference (SELECT statements) Databricks tables from Fabric, but editing (DML statements) is currently not supported.

Thus, this method is not suitable for interoperability.

Please let me know if my understanding is incorrect 🙇

② Specifying OneLake as an external location from Databricks (Currently not supported)

I tried configuring an external location for OneLake via cloud storage connection to Azure Databricks, but it is not currently supported.

I was hoping this method would work, but unfortunately, it doesn't...
So this method is also not suitable for interoperability.

▽Reference

Azure Databricks から OneLake 上のデータにアクセスする方法 2024/12 版 #Microsoft - Qiita

はじめにAzure Databricks の Unity Catalog ミラーリングを通して、Databricks の管理するデータについて Fabric で利用できるようになりましたが、Fa…

qiita.com

③ Hub storage as a solution

Since mirroring and external locations didn't work, I decided to store the actual tables in Azure Data Lake Gen 2 (ADLS2).
By using a schema shortcut to ADLS2 from Fabric, and specifying ADLS2 as the storage location in Databricks' catalog, both Fabric and Databricks can perform SELECT and DML operations.

This means that interoperability between Fabric and Databricks is now possible!

From this point forward, I will refer to this ADLS2 as hub storage.

▽For the specific setup method, refer to the following article:

FabricとDatabricksの相互運用性②：hubストレージ設定方法 -Databricks で作成したテーブルをFabric で利用する、Fabric で作成したテーブルをDatabricksで利用する- #Azure - Qiita

qiita.com

Hub storage works thanks to Delta Lake

As explained above, hub storage allows interoperability between Fabric and Databricks.

But why does this interoperability work?

The key lies in Delta Lake, the mechanism behind it.

The mechanism of Delta Lake

Delta Lake is an open-source storage layer that provides transaction and schema management on top of a data lake. It uses a combination of Parquet and JSON as its underlying data formats. Parquet is a columnar compression format that enables fast queries and data compression, while JSON is used as a transaction log to record data change history and versioning.

By leveraging the Delta Lake mechanism, hub storage enables advanced data sharing and operations. When using Fabric or Databricks, it’s crucial to understand the underlying infrastructure to fully take advantage of the features provided by Delta Lake.

▽For more details on Delta Lake, refer to the official documentation:

Delta Lake とは - Azure Databricks | Microsoft Learn

Databricks レイクハウスに電源を供給するために使用される Delta Lake ストレージプロトコルについて説明します。

learn.microsoft.com

Conclusion

In this article, I summarized the purpose and methods of interoperability between Fabric and Databricks.

The next article will provide a detailed guide for setting up hub storage.

▽Next article:

FabricとDatabricksの相互運用性②：hubストレージ設定方法 -Databricks で作成したテーブルをFabric で利用する、Fabric で作成したテーブルをDatabricksで利用する- #Azure - Qiita

qiita.com

How a New Graduate with No Industry Experience Passed the Databricks Certified Data Engineer Associate Exam in 3 Weeks

Reiji Otake — Sun, 16 Feb 2025 07:56:12 +0000

Introduction

As the title suggests, I am posting this as a memorandum to keep a record of my learning.

It's just a simple summary, but I hope it can be helpful for your studies as well.

If you have any additional questions, please feel free to leave a comment.

Personal Impressions & Key Points

Grasping the Overall Picture

By reading the Databricks Quick Start Guide, you can get an overview of the exam's flow and key points.

Dealing with SQL Questions

For those who have obtained Oracle Silver, the SQL section is relatively easy.

There were more than five SQL questions on the exam.

Utilizing the Databricks Environment

If you have access to a Databricks environment, actually using it and working through tutorials will deepen your understanding.

Fortunately, I had access to a study environment where I could freely experiment.

Leveraging Official Documentation

Going back to the official documentation is ultimately the fastest way to find answers.

In my case, I had a shallow understanding of Unity Catalog, DLT, structured streaming, and Git, so I studied by reading the official documentation.

Study Timeline

24/12/23 Asked Senior Employees Who Passed for Recommended Study Materials

Databricks Certifications: I Took Them All and Summarized Systematically

2024/12/27 Databricks Quick Start Guide

Databricks Quick Start Guide

Recommended for getting a broad overview.

Recommended Points

Affordable Price

The Kindle version is available for 99 yen (this is the regular price, not a sale).

The same content is available for free on Qiita, but the Kindle version is compiled into a single volume, making it significantly easier to read. (Well worth the 99 yen.)

Reliable Information Source

Published by Databricks Japan, ensuring high reliability.

Easy-to-Understand Explanations

Clearly explains the background and history of Lakehouse.

Practical Content

Includes ETL hands-on exercises, making it easy to apply in real-world scenarios.

Caution

Since it was published in 2022, some information may be slightly outdated.

2025/1/7~8 Official Practice Questions

Translated & Explained Databricks Certified Data Engineer Associate Practice Questions

Questions 1-30 have been translated into Japanese and explained.

Unofficial Explanation of Databricks Certified Data Engineer Associate Practice Exam Answers (As of January 2024)

Questions 31-45 are in English, but explanations are provided in Japanese.

2025/1/8~6 Udemy Course Recommended by Senior Employees

Practice Exams: Databricks Certified Data Engineer Associate

Accuracy Rates:

Practice 1: 51% (23/45)

Practice 2: 62% (28/45)

2025/1/16~19 Struggled with Structured Streaming, Delta Live Tables, and Git as a New Graduate

I found them too difficult to understand, so I searched through documentation and tutorials.

Relevant Resources:

Git/GitHub Resources:

2025/1/20 Retook the Official Practice Exam

Accuracy Rate: 86%

2025/1/20 Registered for the Exam

Databricks Certified Data Engineer Associate Exam Registration

Warning:

I do not recommend using a JCB card for payment.

(My confirmation email never arrived, but the payment was deducted. The follow-up with support was very time-consuming.)

1/22 Passed the Exam!! 🎉

Conclusion

Even as an industry newcomer with only 9 months of experience, I was able to pass on the first attempt!

I encourage you to give it a try!

Since then, I have continued using Databricks in my work and have found that the knowledge gained from certification studies has been directly applicable.

This was just a brief summary, but I hope it helps with your studies!