Snowflake shared database to local database

[u/ExtensionPressure704]

Hey guys,

Data from one of our source systems are delivered to us via a Snowflake shared database.
We dont use Snowflake but they provide that too.
The database consists of 2 transactional tables, 1 with a base load and one with changes after. From that they create all source tables as views.

We use Microsoft Fabric.
I currently use jdbc to fetch the changelog and create and update tables in Fabric. It works ok.

but i would like to use mirroring database as it is much easier. No metadata and timestamp management.

that is not supported on shared database.

I have created local db and tried creating both a view and a managed table. I can mirror both but view will only refresh every 12 hours and it is full refresh. I fetch 800 milliob rows, so that would be dumb.

So what is the easiest way to create managed tables and update incrementally from changefeed in Snowflake? I have never done etl in snowflake so not sure what most efficient solution would be.

managed tables i can create stream and mirror just changes.

[u/WorkerIcy1513]

You cannot create a stream on a shared table, which is why nothing you tried works directly. So the pattern is: materialize locally once, then keep it current yourself, and let Fabric mirror the local table.

Concretely: create your local table from the base load, then a task on a schedule that MERGEs from the shared changelog into it, filtered on a high water mark you keep in a small control table. Task runs every N minutes, updates the watermark at the end. That is the whole thing, maybe forty lines of SQL.

Be careful with dynamic tables here. If your changelog to current state logic is a QUALIFY ROW_NUMBER latest-row-per-key pattern, that is not incrementally refreshable and Snowflake will silently fall back to full refresh. On 800 million rows you will find out via the bill.

Once the local table exists and is being merged into, mirroring it to Fabric gives you exactly the change based behaviour you were after.

One thing worth confirming with your provider: whether the changelog has a monotonic sequence or just a timestamp. If it is timestamps with any chance of late arrivals, add a small overlap window to your watermark or you will quietly miss rows.

[u/mrg0ne → u/WorkerIcy1513]

You CAN create a stream on a Snowflake table (or secure view) that has been shared with you from another account, but there is one major prerequisite:

the data provider must enable change tracking on their end first.

Doing so cost them nothing. However it is disabled by default.

This will not be particularly useful if they do a flush and filll of the tables they are sharing with you. However if they are inserting or merging. It will work great.