1

Many thanks in advance.

I'm looking to use Snowflake to share sensitive data as a provider. I can securely share the data via a secure UDF on a share, but I'd feel more comfortable if I could measure how many rows a consumer queries.

I.e. I want to know the size of the output table when the example UDTF (below) is called:

CREATE OR REPLACE SECURE FUNCTION CLIENT_ACCESS
    (INPUT_RECORDID STRING, INPUT_LOOKUP INTEGER)
    RETURNS TABLE
    (RECORDID STRING, LOOKUP INTEGER, SENSITIVE_DATA STRING)
AS
$$
  Select INPUT_RECORDID AS RECORDID, PROVIDER_DATA.* FROM
    <DB>.<SCHEMA>.SHARED_DATA PROVIDER_DATA
  WHERE
    PROVIDER_DATA.LOOKUP = INPUT_LOOKUP
$$;

Does Snowflake measure consumer usage to this granularity? If so, how can I access the information? If not, how could I measure this within the UDF?

Bonus Points (and boundless thanks) if I can measure the size of the column inputs too.

ConnorOSully
  • 11
  • 1

1 Answers1

0

Unless your consumers are using Reader accounts (in which case the SNOWFLAKE.READER_ACCOUNT_USAGE schema may be helpful), then I don't think this would be possible - you would need access to the consumer account to be able to see these types of metrics.

Why do you care how many rows a consumer queries? It seems (to me) to be an odd metric to be concerned with - but if you can explain what you are actually trying to achieve then someone may be able to suggest something i.e. if you knew that a consumer had queried 1000 rows then what?

NickW
  • 8,430
  • 2
  • 6
  • 19
  • I'm trying to share confidential information with consumers over Snowflake, but in such a way that doesn't allow the consumer to simply copy/download the entire table. We can limit the access by the UDTF, but we wouldn't know if someone manually made an exhaustive list of Input_Lookup and input that into the UDTF to extract the entire table. This is the worst case scenario that I'd like to monitor for. If we know a consumer had queried above X rows, we can pull their connection, and if above Y rows, it would be a breach of a (yet to be determined) commercial agreement. – ConnorOSully Jun 07 '22 at 22:44