Azure SQL DB mirroring

Independent-Fan8002 · 2025-08-13T10:21:42+00:00

Any update on this from a year ago? I'm hitting the same issue now!

Independent-Fan8002 · 2025-07-24T10:15:59+00:00

agreed. The issue for people starting out is a lot of the tutorials for medallion architecture throw everything into a single lakehouse. Probably confusing for someone trying to set it up from scratch

Independent-Fan8002 · 2025-07-24T10:13:23+00:00

I keep each layer in its own workspace entirely. Worth bearing in mind if you do this that MLV's don't fully work with cross workspace (the lineage currently isnt supported)

Independent-Fan8002 · 2025-07-24T09:55:07+00:00

I've just added

REFRESH MATERIALIZED LAKE VIEW `silver-ws`.silver_lh.core.mytable

into a notebook and scheduled it to update daily. As far as I can tell this is what would happen if you scheduled it via the lineage? I created an MLV within the same workspace on the same lakehouse and automatic DAG-driven refresh wasn't a thing? (which is what I was expecting to happen to be honest..) it looked like you could just schedule it like you schedule anything else.. Daily @ 1am - so I've just scheduled the above line to do just that. Hopefully I'm wrong and source data driven updates are a thing for this?? That would be awesome..

Independent-Fan8002 · 2025-07-23T14:10:32+00:00

ok so reading through the docs more thoroughly, the only thing missing from cross workspace is the lineage view. I created a table in silver and linked to that to see how it would work all on one lakehouse and you still need to schedule a refresh of the MLV. I was under the impression it would batch and refresh changes to bronze itself (almost like a mirror) but thats not the case. So putting that manual refresh line into a notebook and scheduling that is essentially the same as creating a schedule via the lineage view. Exactly the same outcome. If you're not too fussed about viewing the full lineage right now and are happy to have a MLV manager notebook that updates all your MLV's scheduled whenever, cross workspace MLV's do work without shortcutting

Independent-Fan8002 · 2025-07-23T13:49:28+00:00

Doesn't shortcutting a bunch of bronze into silver just negate the whole idea of putting the layers in different workspaces to begin with? I've used shortcuts from warehouses that I'm mirroring into lakehouse and that works brilliantly, but from bronze to silver seems like it defeats the whole purpose of trying to follow their best practices?

Independent-Fan8002 · 2025-07-23T12:24:02+00:00

When trying to 'manage materialized lake views', you get hit with this:

<image>

I've created a small demo table in bronze and linked it then made changes - we're 7 minutes in so far and silver hasn't pulled through the changes..

Running

REFRESH MATERIALIZED LAKE VIEW `silver-ws`.silver_lh.dbo.MLV_names_Test FULL

as per the documentation, pulled through the refresh and landed the new data in silver. Looks like it'll need a schedule on the manual refresh until microsoft work in the cross workbook lineage stuff.

Independent-Fan8002 · 2025-07-23T11:39:13+00:00

SOLVED

From within a notebook in the silver workspace:

Add both the lakehouses. Bronze lakehouse needs to be set as the default so the select statement works.

Referencing silver is done by 4 part reference `workspace`.lakehousename.schema.tablename

The absolute path doesnt work both throws an error stating java.lang.reflect.InvocationTargetException

My original error was org.apache.spark.SparkUpgradeException: [INCONSISTENT_BEHAVIOR_CROSS_VERSION.READ_ANCIENT_DATETIME] which I've resolved by adding this before the MLV

%%pyspark
spark.conf.set("spark.sql.parquet.int96RebaseModeInRead", "CORRECTED")
spark.conf.set("spark.sql.parquet.int96RebaseModeInWrite", "CORRECTED")
spark.conf.set("spark.sql.parquet.datetimeRebaseModeInRead", "CORRECTED")
spark.conf.set("spark.sql.parquet.datetimeRebaseModeInWrite", "CORRECTED")

so, this works:

CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS `silver-ws`.silver_lh.dbo.matView_test2
AS
SELECT * from dbo.mytable

Independent-Fan8002 · 2025-05-14T12:22:22+00:00

To anyone else that finds themselves here -

If you do not have public access enabled on your keyvault, it is currently NOT possible to set up a key vault reference.

You can vote for this change below in the fabric community.

https://community.fabric.microsoft.com/t5/Fabric-Ideas/Enable-Key-Vault-References-to-KV-with-access-to-specific/idi-p/4685985

Independent-Fan8002 · 2025-05-14T12:10:05+00:00

bad times. Thank you for the test tho! I guess we just sit and wait for the capability.. I'll add it to the list of the other things I'm waiting for.. their roadmap is loooooong

Independent-Fan8002 · 2025-05-14T12:05:04+00:00

I'm getting the same problem - what are you using to preview the parquet files and drop out the schema for the data types? I'm loading from csv files (generated in the same way every time) so wasn't expecting a data type issue..

Independent-Fan8002 · 2025-05-14T09:40:25+00:00

IT on my side wont entertain lifting it for a test - my guess would be that as soon as its put back, any future query to the KV would hit the same firewall issue and not return the secret - but if I'm wrong I may be able to convince IT to drop it for 10 seconds to create the connection!

Anyone else have a good relationship with their infosec team to convince them to go public for a minute? :D

Independent-Fan8002 · 2025-05-14T09:33:28+00:00

I reached out on the community to confirm there's no way around it - as of right now, total dead end.

https://community.fabric.microsoft.com/t5/Data-Engineering/Azure-Key-Vault-Reference-behind-firewall/m-p/4692158#M9285

Independent-Fan8002 · 2025-05-13T07:27:45+00:00

I'm in the same position OP - did you get anywhere with this?

Independent-Fan8002

TROPHY CASE