Question Re: Snowflake as a Live Data Source to Datasphere in Technology Q&A

Snowflake as a Live Data Source to Datasphere

Joel_B — Wed, 12 Jul 2023 21:55:29 GMT

I have read several blog posts and resources which indicated as of June 2022 there was no mechanism for Live data modeling of Snowflake sources in DWC or Datasphere, with the exception of APOS or other 3rd-Party extension. Has this positioning changed? Does anyone happen to know if SAP has made any advancement on Snowflake as a live data source?

Re: Snowflake as a Live Data Source to Datasphere

Sven_Knpfler — Thu, 13 Jul 2023 08:01:11 GMT

There is no native connector but you can use the generic jdbc connection tile (via the DP Agent) for that use case. There is an interesting blog from sefan.linders2 that explains the details: https://blogs.sap.com/2022/06/15/how-to-add-snowflake-as-a-source-for-sap-data-warehouse-cloud-or-sap-hana-cloud/

Re: Snowflake as a Live Data Source to Datasphere

Joel_B — Fri, 21 Jul 2023 19:43:12 GMT

Thank you for the feedback Sven. I have used the DP Agent to deploy the Snowflake driver and create the connections in Datasphere.

The really unfortunate result is that the Snowflake JDBC driver under Camel:

Data flows are not supported
Replication flows are not supported
Remote Tables are enabled (making this the only option)

The end result is that the only way to use the Snowflake data I can see is to replicate an entire snapshot for each table/view. Were you able to figure out any other options for consumption of Snowflake data other than snapshots?

Thank you!

Re: Snowflake as a Live Data Source to Datasphere

JulianJuraske — Mon, 24 Jul 2023 06:38:43 GMT

Remote Table doesn't mean that you have to replicate/persist the Data, you can also federate the Data (Live Access).

BTW Replication Flow and Data Flow always persist the the Data, you would only use the the DataFlow if you need Python and I can not see the Replication Flow fitting your Requirement.

May you specifiy your Problem with the Remote Tables?

Re: Snowflake as a Live Data Source to Datasphere

Joel_B — Mon, 24 Jul 2023 19:51:18 GMT

Thank you Julian,

I did not understand that Data Federation (using Live remote data) is possible when used through Data Provisioning Agent when I first asked the question. Thanks for clarifying that point. I have indeed validated that Snowflake data is usable via Federation. However, performance using Federation inherent in the network connection link is not great:

Outbound: Datasphere -> Data Provisioning Agent installed on internal network -> JDBC Connection to Snowflake Cloud
Inbound: Snowflake -> DPA ->DS

I have upsized the memory limits on DPA, but that 3-way hop is not optimal in my testing so far. Also, the statements seem to be sequentially executed.

Ideally, since DS to Snowflake is Cloud-to-Cloud, it seems suboptimal to put DPA in the middle. Perhaps SAP will consider a native Snowflake adapter down the road?

Being this is our first step towards Datasphere and I want the business to be be wowed by the result of the significant spend, replication of the Snowflake data gives much better SAC and reporting performance. This is why I have turned to trying to find the best way to replicate when I cannot Snapshot the very large snowflake tables as a whole.

As of right now, the best option I have found is to create views on top of the Snowflake remote tables to physically partition the data into history and changeable. From there, I have used the Data Flow to reload just the changeable data. While this technique technically works, it seems very clunky and manual. Do you have any other suggestions for replication of remote tables with delta-like functionality?

Thank you!

Re: Snowflake as a Live Data Source to Datasphere

Sven_Knpfler — Tue, 25 Jul 2023 07:41:27 GMT

Hello Joel,

you could create a view on top of your remote tables which enables you to create partitions for those views. Paritions that wont change anymore can be "locked". Only "open" partitions will be reloaded when you schedule the persistency of the view:

https://help.sap.com/docs/SAP_DATASPHERE/be5967d099974c69b77f4549425ca4c0/9b1b595054c34da5b67ac07595093f82.html?locale=en-US

Kind regards

Sven