From Fabric Lakehouse to Neo4j Knowledge Graph: Mapping the KEGG Drug Database

A practical guide to process data for Neo4j through the Medallion Architecture on Microsoft Fabric

The quest for new medicines is fundamentally a problem of complexity and connection. At its core, drug discovery is accelerated not just by laboratory work but also by data analytics — specifically, the ability to find, map, and analyze the intricate relationships between chemical compounds, therapeutic targets, and human diseases. Data engineers and scientists are the cartographers of this biological landscape, seeking reliable, structured data that can guide research, validate hypotheses, and uncover novel treatment avenues.

However, drug analytics is rarely straightforward. Consider the **Kyoto Encyclopedia of Genes and Genomes (KEGG)*, one of the most comprehensive resources for understanding drug pathways and biological systems. While KEGG is a goldmine of drug and relationship data, its native data structure presents a formidable barrier. Its API response data is typically delivered in semi-structured plain text (see example), which is designed for human readability, not machine parsing. Furthermore, the required information is highly fragmented. Integrating these different data slices often becomes an arduous process requiring numerous, lengthy API calls and complex custom stitching logic. This inherent difficulty was the key lesson I learned from my previous [Neo4j for Diseases](https://medium.com/data-science/neo4j-for-diseases-959dffb5b479)* project.

Recently, I recognized that Microsoft Fabric could dramatically streamline and modularize my data preparation. Fabric uses OneLake as a single, integrated storage. It allows for seamless ingestion of diverse data, from on-premises files (Excel, TXT, CSV), to databases, Google Sheets, and external cloud storage like Amazon S3.

Leveraging Fabric’s pipelines and lakehouses, I implemented the **Medallion Architecture. This modular pipeline stores the raw KEGG data in a Bronze state, through a filtered and enriched Silver layer, and finally into a highly structured and Neo4j-ready Golden** layer. This structured approach not only significantly reduces the complexity of KEGG data processing but also inherently provides easy scheduling, detailed data lineage, and strong governance management. You can find the code for this project in my Github repository here.

[embed]GitHub - dgg32/fabric Contribute to dgg32/fabric development by creating an account on GitHub.github.com

1. Preparation

You’ll need a valid Microsoft Fabric account for this project. While Microsoft offers a 60-day trial, enrolling can be tricky as it requires two specific conditions:

1. You must use a business email address.
2. Your organization must be configured to allow users to purchase Microsoft Fabric.

If you encounter these restrictions, there is an alternative approach:

1. Create an Azure account.
2. Set up an Entra ID user within that account.
3. Sign up for the Fabric trial using that user’s email address (instructions here).
4. Activate the Fabric trial.

I pre-downloaded the drug list (based on the ATC classification) from KEGG to use as the pipeline’s seed data. Alternatively, this data could be retrieved programmatically later within the Fabric pipeline itself.

2. Implementing the Medallion Architecture

Once inside Fabric, create a new Workspace. Then, add a task by clicking the “Select a predesigned task flow” button and choosing “Medallion”.

Figure 1. Create a Medallion task in Microsoft Fabric. Image by author.

The workspace canvas will display a flow chart visualizing the process, with elements color-coded by function: pipeline steps are represented by purple, green, and yellow boxes, while data storages are shown in blue.

Figure 2. The pre-designed Medallion architecture by Microsoft Fabric. Image by author.

I modified this initial chart to create the refined workflow shown on the left (Figure 3 left). I then configured each element:

• Blue Boxes: Attached a Lakehouse to each, serving as the data storage locations.
• Purple & Green Boxes: Attached a Notebook to each, representing the data processing and transformation steps.
• Yellow Box: Retained as a symbolic placeholder with no attached resource (Figure 3 right).

Figure 3. My modified Medallion architecture for this project. Left: workflow chart. Right: resource list. Image by author.

In the Bronze Lakehouse, I initiated the pipeline by uploading the ATC drug file (br08303_2025–05–15.txt).

Data moves from the Bronze layer to the Silver layer, undergoing extraction and enrichment. Specifically, the Notebook in the “Convert to Table” step reads the raw drug file, extracts essential details like drug names and KEGG IDs, and uses them to populate a corresponding table in the Silver layer.

# “Convert to Table”, parse raw atc to table
def parse_atc_file_function(file_path):
  ...

result = parse_atc_file_function(file_path = '/lakehouse/default/Files/br08303_2025-05-15.txt')

df = spark.createDataFrame(result)

df.write.format("delta").mode("overwrite").saveAsTable("SilverLakeHouse.atc_codes")

print("Data successfully processed and written to Silver Lakehouse.")

Consistent with other cloud lakehouse environments, Fabric notebooks rely on Spark for highly scalable data processing, as demonstrated in the “Convert to Table” notebook. However, the concurrent nature of Spark execution often conflicts with the strict rate limits imposed by external APIs. For instance, the KEGG API allows only three requests per second. To respect this limitation during the “Diseases and Targets” step, the “get disease and target” notebook must abandon parallelism: it first flattens the drug RDD and then queries the KEGG API sequentially to gather the indicated diseases and drug targets.

# get disease and target
def fetch_kegg_data_sequentially(drug_df, kegg_base_url, rate_limit_delay=0.4):
    """
    Collects KEGG IDs to the driver, calls the KEGG API sequentially
    with a delay to respect rate limits, and returns a list of Rows.
    """

    # 1. Collect unique KEGG IDs to the driver
    kegg_ids_to_process = drug_df.select("drug_kegg_id").distinct().rdd.map(lambda row: row[0]).collect()
    total_drugs = len(kegg_ids_to_process)
    print(f"Starting sequential fetching for {total_drugs} unique KEGG IDs...")

    results = []

    # 2. Sequential API Call Loop
    start_time = time.time()
    for i, kegg_id in enumerate(kegg_ids_to_process):
        url = f"{kegg_base_url}{kegg_id}"
        max_retries = 3
        kegg_details = None

        for attempt in range(max_retries):
            try:
                response = requests.get(url, timeout=10)

             ...

    return results

kegg_results = fetch_kegg_data_sequentially(
    drug_df, 
    KEGG_BASE_URL, 
    rate_limit_delay=0.4  # Set a safe delay
)

enriched_df = spark.createDataFrame(kegg_results, schema=ENRICHED_SCHEMA)

drug_disease_df = enriched_df.withColumn("disease", explode(col("kegg_details.diseases"))) \
                             .select(
                                 col("drug_kegg_id"),
                                 col("disease.ds_id").alias("disease_ds_id"),
                                 col("disease.name").alias("disease_name")
                             )

...

dim_diseases_df = drug_disease_df.select(
    col("disease_ds_id").alias("disease_id"),
    col("disease_name").alias("name")
).distinct().withColumn("sk", monotonically_increasing_id())

final_fact_drug_disease_df = drug_disease_df.select(
    col("drug_kegg_id"),
    col("disease_ds_id").alias("disease_id")
).distinct()

(dim_diseases_df
    .write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("SilverLakeHouse.dim_diseases")
)

(final_fact_drug_disease_df
    .write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("SilverLakeHouse.fact_drug_disease")
)

In the Silver layer, information pertaining to individual drugs, diseases, and targets is stored in the dimensional tables, while their relationships are recorded in the fact tables (Figure 4).

Figure 4. The dim_drugs table in the Silver layer. Image by author.

The Silver layer contains valuable, curated data accessible via SQL endpoints for direct T-SQL queries. While we could create a semantic model and Power BI dashboard using DAX, our primary project goal is to visualize the drug network using the Neo4j graph database.

To achieve this, the “format to Neo4j sources” notebook extracts the necessary data from the Silver layer and formats it into Neo4j-ready node (JSON) and edge (CSV) files (Figure 5).

#format to Neo4j sources. File generation for Neo4j
...
# nodes are formatted into JSON
drug_df = spark.table("SilverLakeHouse.dim_diseases")
aggregated_df = drug_df.groupBy("disease_id").agg(
    collect_list("name").alias("disease_name")
).orderBy("disease_id")

print("\n--- Aggregated DataFrame ---")

pandas_df = aggregated_df.toPandas() 

PANDAS_PATH = "abfss://drug_atc@onelake.dfs.fabric.microsoft.com/GoldenLakeHouse.Lakehouse/Files/diseases.json"

pandas_df.to_json(PANDAS_PATH, orient='records', lines=True)

# edges are formatted into CSV
disease_df = spark.table("SilverLakeHouse.fact_drug_disease")

# Select the required columns (assuming column names are drug_kegg_id and disease_id)
# Note: You can rename them here if needed, but we will use the existing column names
output_df = disease_df.select("drug_kegg_id", "disease_id")

# Define the output path for the CSV directory
CSV_PATH = "abfss://drug_atc@onelake.dfs.fabric.microsoft.com/GoldenLakeHouse.Lakehouse/Files/drug_disease.csv"

# (Optional: Use .coalesce(1) before .write.csv if you MUST have a single output file)
output_df.toPandas().to_csv(CSV_PATH, index=False)
...

Figure 5. The Neo4j-ready files in the Golden layer. Image by author.

The prepared data can be reviewed in the Golden layer explorer before being downloaded.

3. Drug data visualization in Neo4j

Automatic data retrieval from Fabric to a Mac is currently limited. Fabric’s “Copy” job and the OneLake File Explorer client are all restricted to Windows. While Mac users can always use the API for programmatic downloads, the simpler method for retrieving a small number of files is manual download, utilizing either the file’s “Download” menu item in the Fabric web interface or the VSCode “Fabric Data Engineering” extension (Figure 6).

Figure 6. The VSCode “Fabric Data Engineering” extension allows Fabric management in VSCode. Image by author.

Once the files were downloaded to my Mac, I transferred them to the Neo4j import directory and initiated the data import into a new Neo4j project. To handle the issue of multiple names for drugs, diseases, and targets, I designated the first item in their respective name lists as the node label.

# Neo4j import cypher commands
CREATE CONSTRAINT drug_index IF NOT EXISTS FOR (c:Drug) REQUIRE c.drug_id IS UNIQUE
;
CREATE CONSTRAINT disease_index IF NOT EXISTS FOR (c:Disease) REQUIRE c.disease_id IS UNIQUE
;
CREATE CONSTRAINT target_index IF NOT EXISTS FOR (c:Target) REQUIRE c.target_id IS UNIQUE
;

CALL apoc.load.jsonArray("file:///diseases.json") YIELD value MERGE (c:Disease {disease_id: value.disease_id, name: head(value.disease_name), names: value.disease_name})
;
CALL apoc.load.jsonArray("file:///drugs.json") YIELD value MERGE (c:Drug {drug_id: value.kegg_id, name: coalesce(head(value.drug_name), ""), names: coalesce(value.drug_name, []), atc: value.atc_name})
;
CALL apoc.load.jsonArray("file:///targets.json") YIELD value MERGE (c:Target {target_id: value.ko_number, name: head(value.target_name), names: value.target_name})
;

LOAD CSV WITH HEADERS FROM 'file:///drug_disease.csv' AS row MERGE (p1:Drug {drug_id: row.drug_kegg_id}) MERGE (p2:Disease {disease_id: row.disease_id}) MERGE (p1)-[r:TREATS]->(p2)
;
LOAD CSV WITH HEADERS FROM 'file:///drug_target.csv' AS row MERGE (p1:Drug {drug_id: row.drug_kegg_id}) MERGE (p2:Target {target_id: row.target_ko_number}) MERGE (p1)-[r:TARGETS]->(p2)
;

Within Neo4j Desktop, the graph database allows for quick visualization of the drug network. For example, the following Cypher query visualizes the diseases and targets connected to the drug Keytruda.

# Visualize the drug network of Keytruda
MATCH p= (t:Target) <-[:TARGETS]- (n:Drug) -[:TREATS]-> (d:Disease) 
WHERE "Keytruda" in n.names RETURN p LIMIT 25;

Figure 7. The drug network of Keytruda. Image by author.

The results clearly demonstrate that Keytruda targets the Programmed cell death protein 1 (PDCD1) and is indicated for 17 different types of cancer.

Conclusion

The current project demonstrated some obvious advantages of Microsoft Fabric. In previous projects, I always struggled with data sprawl: raw, intermediate, and final files were scattered in multiple versions across different folders, and I often processed them using a single, long, monolithic script. Fabric is designed specifically to resolve this inefficiency. By utilizing OneLake, all data versions — from the messy source to the clean target tables and files — are maintained in one central location. This unified structure enables modular data processing, allowing the pipeline to be broken into small, manageable components. Crucially, data lineage is automatically captured, simplifying the tracing of data from its origin to the final output. This consolidation also makes governance significantly more straightforward.

Released on May 23rd, 2023, Fabric is still a young product, and its surrounding ecosystem is still maturing. This means integration challenges for early adopters. For instance, the official Neo4j workload could not be loaded into the workspace during this project. Moreover, even when functional, the current Neo4j workload exhibits key limitations: the graph creation relies solely on the generative AI, lacks pre-import editing, does not return graph analysis results to OneLake, and can only source data from a single Lakehouse. On the usability front, automated file download functionality is still needed for non-Windows users. These functional gaps are expected to be addressed over time.

Despite these limitations, the potential of a truly unified platform for data integration, warehousing, and business intelligence remains highly compelling. It represents a significant architectural shift that simplifies the infrastructure typically required to stitch together multiple sources. So, I encourage you to test Fabric with your next project, whether you’re building a knowledge graph, running real-time analytics, or constructing an AI application.

This blog is published in partnership with UselessAI.in! Read some amazing Data and AI blogs, especially related to Microsoft Fabric and AI on this publication.