Forum Discussion

Poweraegg's avatar
Poweraegg
Icon for Advocate IV rankAdvocate IV
2 years ago

Fabric - Moving Data in Medallion Architecure between Workspaces

Hi,

 

I am trying to load and transform data from the Lakehouse in the Silver Layer Workspace to a Warehouse in the Gold Layer Workspace. However, I only seem to be able to use a Gen2 Dataflow for this. I cannot use a Pipeline Copy Data Activity, and I cannot seem to figure out how to use an SQL query in the Warehouse to load and transform the data using either the ABFS delta table path or DB name. I also tried writing the data into the Warehouse using the Lakehouse Notebook, but I get a PUT operation error. Any idea how to load and transform the data from a Silver Layer Lakehouse Workspace to a Gold Layer Warehouse Workspace without using a dataflow? Reasoning: In the dataflow I only have append or replace. I cannot make operations that perform a duplicates test before loading.

 

Thanks

Need Help

4 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Struggling to find it now but cross workspace data copy in a pipeline is not yet supported. (Sure it is on the roadmap).


    Shortcut I think is an option.

     

    Can you share the notebook code that is throwing the error?

  • Poweraegg's avatar
    Poweraegg
    Icon for Advocate IV rankAdvocate IV
    2 years ago
    from delta.tables import *
    from pyspark.sql.types import *
    from pyspark.sql.functions import *

    # Define the path
    silver_path = "abfss://[email protected]/Contoso_Silver_LH.Lakehouse/Files/Product_Silver_External"
    gold_path = "abfss://[email protected]/WH_Contoso_Gold.Datawarehouse/Tables/dbo/Products_Gold"

    # Read the Delta table
    deltaTable = DeltaTable.forPath(spark, silver_path)

    # Convert the Delta table to a DataFrame
    df_silver = deltaTable.toDF()

    # Cast the 'Unit_Cost' and 'Unit_Price' columns to match the types in the Gold Warehouse table
    df_silver = df_silver.withColumn("Unit_Cost", col("Unit_Cost").cast(DecimalType(18,0)))
    df_silver = df_silver.withColumn("Unit_Price", col("Unit_Price").cast(DecimalType(18,0)))

    # Write the data to the Gold Warehouse
    df_silver.write.format("delta").mode("overwrite").save(gold_path)
     
    Output - 
    • bcdobbs's avatar
      bcdobbs
      Icon for Community Champion rankCommunity Champion
      2 years ago

      I think the issue is you can only write to warehouse tables through the sql end point. I'll have a play and get back to you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I faced same issue while doing a merge query to warehouse table path from silver to gold using notebook. Is there a solution for this? Kindly provide if any.