1
0
During this post we will discuss Materialized View on Shared data in consumer account. Consider the scenario where Consumer has to perform some analysis on data. Analysis require aggregation on this shared data that take a long time to calculate. However, Consumer does not own this data and has asked provider to provide access on Database and table via shares. Later on Consumer plan to create Materialized view on this shared data using aggregations.
We will see how we can create MV on Shared table as well Consumer Account. Also we will check how MV behaves if any DML operation happens on table and MV is still not refresh.
So first we will create a DB, Schema and Table in provider Account:
- Insert the data into Table.
Create the share.
create or replace share MVSHARE;
grant usage on database SHARED_MV to share MVSHARE;
grant usage on schema SHARED_MV.PUBLIC to share MVSHARE;
grant select on table SHARED_MV.PUBLIC.cust_inv to share MVSHARE;
- Add the consumer Account to the Share
alter share MVSHARE add accounts = lk10744;
Go to the consumer Account and Import the Database from SHARE.
- Create the MV on the Shared DB and Table.
- Now verify the last refreshed timing of Materialized View.
- Now Go to the Provider Account and insert more data into the Table in below way:
INSERT INTO cust_inv
SELECT (SEQ8())::BIGINT AS INVC_NO ,randstr(2, random())::VARCHAR INVC_CD
,UNIFORM(1,99999,RANDOM(10000))::INTEGER AMT, 'C-6'|| trunc(ROW_NUMBER() OVER(ORDER BY SEQ8())/1000000) FROM TABLE(GENERATOR(ROWCOUNT => 1000));
- Now Login to the Consumer Account and verify the data in view:
Latest Data was immediately available in MV on Consumer account.
Point to Note:
Verify the Refresh history of MV and it is still showing the older one i.e. 2022-04-28 08:32:05.139 -0700
Also verify if any credits has been consume by MV at this point. To our surprise still the Refresh history service not executes by Snowflake in background.
Then question comes, How we are getting the latest data from MV. So this is the beauty of MV in Snowflake.
Data accessed through materialized views is always current, regardless of the amount of DML that has been performed on the base table. If a query is run before the materialized view is up-to-date, Snowflake either updates the materialized view or uses the up-to-date portions of the materialized view and retrieves any required newer data from the base table.
After some time I verified again ,Refresh history service executed and we could see the credits.
Key Note:
Remember that maintaining materialized views will consume credits. When you create a materialized view on someone else’s shared table, the changes to that shared table will result in charges to you as your materialized view is maintained.
