Snowflake POC (Load data from Microsoft AX on Premise to Snowflake)

0
votes

I have to do a Proof of Concept (POC) with Snowflake and I am new to Snowflake and looking for advice.

Use cases:

  1. Have to load data for 7 tables (5 dimension tables and 2 fact tables) from Microsoft Dynamics AX (On Premise) to snowflake
  2. Two tables are big in size and having more than 150 million records
  3. Once data loaded to Snowflake, have to create star schema model in Snowflake around 7 tables
  4. Going to read data from Snowflake using either SSRS, Power BI or Excel.

Need to gauge:

  1. Time taken to load the data from Source to Snowflake (Time, Resource Utilized etc)
  2. and how the read performance is?
  3. Row Level Security: Area manager browses Power BI Report, should only see his data and not other area manager data

Please somebody can explain steps involved to achieve above? Would be great if you can provide some supportive links and scripts.

Do I need to do following:

  1. Load data from AX tables to a files (I think there is a limitation on file size: https://docs.snowflake.net/manuals/user-guide-getting-started.html)
  2. Upload these files either on Amazon S3 or Azure Blob Storage and from there can load each file to Snowflake
2
snowflake-cloud-data-platformpoc
I haven't seen any hard limit on file import size, but obviously the cloud storage object size limitation applies. I believe Snowflake's recommendation comes from optimal CPU resource utilization with a standard number of ingestion threads. – Hans Henrik Eriksen

2 Answers

1
votes

How about this one from Snowflake: How to Craft Your Data Warehouse POC
You must register to get this eBook from 2019...

Also, I would highly recommend doing both in your "Do I need to do?" section.

0
votes

4 Data loading Options available with Snowflake: Small datasets: 1) Snowflake Web UI/Interface: to load limited data (small datasets) Bulk load: 2) SnowSQL (CLI Client): SnowSQL command line interface to load Bulk Data from files on clouds to snowflake. SnowSQL is the next-generation command line client for connecting to Snowflake to execute SQL queries and perform all DDL and DML operations, including loading data into and unloading data out of database tables. You got to install and configure SnowSQL on Client Machine. 3) Snowpipe: Snowpipe is Snowflake’s continuous data ingestion service. Snowpipe loads data within minutes after files are added to a stage and submitted for ingestion 4) 3rd Party ETL tools: like Matillion (SaaS), SSIS (IaaS & on-premise), Talend (SaaS) etc. Create your own Data integration packages to load data to snowflake.

Steps: 1. Load data from Source AX to Snowflake i) As we are a MS Shop, create a SSIS Package to load data from AX to CSV Files (Max size of each file should be 100 MB) and place the files on Azure Blob Storage or AWS S3 ii) Use SnowSQL to load data from file (Azure Blob Storage) to Snowflake OR iii) Use 3rd party ETL tool SSIS to load data directly from Source to Snowflake without any transformation and once data is dumped to Snowflake you can do transformation.