Granted Patent B2

US 12,511,313 · App. 18/806,986 · Granted Dec 30, 2025

Inventors: Pui Kei Johnston Chu (Richmond Hill, CA); Shreyas Narendra Desai (Bellevue, WA); German Alberto Gil Echeverri (San Francisco, CA); Prasanna Krishnan (Palo Alto, CA); Nithin Mahesh (Redmond, WA); Subramanian Muralidhar (Mercer Island, WA); Eric Robinson (Sammamish, WA); Sahaj Saini (Seattle, WA)

Assignee: Snowflake Inc.

G06F16/275G06F16/2282G06F16/2379G06Q30/0185G06Q30/0205

Abstract

Claims (71)

1 . A computer-implemented method for sub-database replication, the method comprising:

receiving, by one or more processors, from a database user, an identification of a column in a data table of a first database for filtering;

clustering, by the one or more processors, the data table based on the column to generate one or more partitions sorted in the column;

accessing, by the one or more processors, metadata for the one or more partitions of the data table, the metadata indicating a minimum value and a maximum value for the column in each partition;

performing, by the one or more processors, file-level filtering of the one or more partitions based on the metadata without opening the one or more partitions;

selecting, by the one or more processors, a subset of table rows of the data table based on the file-level filtering;

performing, by the one or more processors, sub-database replication of filtered micro-partitions from the first database to a second database in a different region using the subset of table rows replication policy;

in response to determining, by the one or more processors, a new partition is being inserted during the sub-database replication, refreshing a materialized view of the second database by inserting the new partition into the materialized view; and

in response to determining, by the one or more processors, a deleted partition is being deleted during the sub-database replication, compacting the materialized view by removing the deleted partition from the materialized view.

2 . The computer-implemented method of claim 1 , wherein the data table comprises a plurality of data table rows associated with a plurality of client identifiers including data content.

3 . The computer-implemented method of claim 1 , wherein performing the file-level filtering comprises determining whether to replicate a partition based on the minimum value and the maximum value of the column in the metadata.

4 . The computer-implemented method of claim 1 , further comprising:

receiving, from the database user, a filtering behavior to be applied to the column.

5 . The computer-implemented method of claim 1 , wherein the first database is a primary database of the database user, and the second database is a secondary database of the database user.

6 . The computer-implemented method of claim 1 , wherein the sub-database replication further uses a replication policy to define objects to replicate to the second database.

7 . The computer-implemented method of claim 1 , wherein the sub-database replication is performed for a plurality of target accounts associated with a plurality of data consumers.

8 . The computer-implemented method of claim 1 , wherein the column is a customer identification column.

9 . The computer-implemented method of claim 1 , further comprising:

detecting an update to an entitlements table associated with the data table, wherein the entitlements table includes a plurality of entitlements table rows associated with client identifiers, consumer account identifiers, and provider account identifiers; and

performing incremental replication of the data table based on the update to the entitlements table.

10 . The computer-implemented method of claim 1 , further comprising:

detecting a change to the data table; and

updating the metadata for the one or more partitions affected by the change.

11 . A machine comprising:

at least one processor; and

at least one memory storing instructions that, when executed by the at least one processor, cause the machine to perform operations comprising:

receiving, by one or more processors, from a database user, an identification of a column in a data table of a first database for filtering;

clustering, by the one or more processors, the data table based on the column to generate one or more partitions sorted in the column;

accessing, by the one or more processors, metadata for the one or more partitions of the data table, the metadata indicating a minimum value and a maximum value for the column in each partition;

performing, by the one or more processors, file-level filtering of the one or more partitions based on the metadata without opening the one or more partitions;

selecting, by the one or more processors, a subset of table rows of the data table based on the file-level filtering;

performing, by the one or more processors, sub-database replication of filtered partitions from the first database to a second database in a different region using the subset of table rows;

in response to determining, by the one or more processors, a new partition is being inserted during the sub-database replication, refreshing a materialized view of the second database by inserting the new partition into the materialized view; and

in response to determining, by the one or more processors, a deleted partition is being deleted during the sub-database replication, compacting the materialized view by removing the deleted partition from the materialized view.

12 . The machine of claim 11 , wherein the data table comprises a plurality of data table rows associated with a plurality of client identifiers including data content.

13 . The machine of claim 11 , wherein performing the file-level filtering comprises determining whether to replicate a partition based on the minimum value and the maximum value of the column in the metadata.

14 . The machine of claim 11 , wherein the operations further comprise:

receiving, from the database user, a filtering behavior to be applied to the column.

15 . The machine of claim 11 , wherein the first database is a primary database of the database user, and the second database is a secondary database of the database user.

16 . The machine of claim 11 , wherein the sub-database replication further uses a replication policy to define objects to replicate to the second database.

17 . The machine of claim 11 , wherein the sub-database replication is performed for a plurality of target accounts associated with a plurality of data consumers.

18 . The machine of claim 11 , wherein the column is a customer identification column.

19 . The machine of claim 11 , wherein the operations further comprise:

detecting an update to an entitlements table associated with the data table, wherein the entitlements table includes a plurality of entitlements table rows associated with client identifiers, consumer account identifiers, and provider account identifiers; and

performing incremental replication of the data table based on the update to the entitlements table.

20 . The machine of claim 11 , wherein the operations further comprise:

detecting a change to the data table; and

updating the metadata for the one or more partitions affected by the change.

21 . A machine-storage medium storing instructions that, when executed by a machine, cause the machine to perform operations comprising:

receiving, by one or more processors, from a database user, an identification of a column in a data table of a first database for filtering;

clustering, by the one or more processors, the data table based on the column to generate one or more partitions sorted in the column;

accessing, by the one or more processors, metadata for the one or more partitions of the data table, the metadata indicating a minimum value and a maximum value for the column in each partition;

performing, by the one or more processors, file-level filtering of the one or more partitions based on the metadata without opening the one or more partitions;

selecting, by the one or more processors, a subset of table rows of the data table based on the file-level filtering;

performing, by the one or more processors, sub-database replication of filtered micro-partitions from the first database to a second database in a different region using the subset of table rows replication policy;

in response to determining, by the one or more processors, a new partition is being inserted during the sub-database replication, refreshing a materialized view of the second database by inserting the new partition into the materialized view; and

in response to determining, by the one or more processors, a deleted partition is being deleted during the sub-database replication, compacting the materialized view by removing the deleted partition from the materialized view.

22 . The machine-storage medium of claim 21 , wherein the data table comprises a plurality of data table rows associated with a plurality of client identifiers including data content.

23 . The machine-storage medium of claim 21 , wherein performing the file-level filtering comprises determining whether to replicate a partition based on the minimum value and the maximum value of the column in the metadata.

24 . The machine-storage medium of claim 21 , wherein the operations further comprise:

receiving, from the database user, a filtering behavior to be applied to the column.

25 . The machine-storage medium of claim 21 , wherein the first database is a primary database of the database user, and the second database is a secondary database of the database user.

26 . The machine-storage medium of claim 21 , wherein the sub-database replication further uses a replication policy to define objects to replicate to the second database.

27 . The machine-storage medium of claim 21 , wherein the sub-database replication is performed for a plurality of target accounts associated with a plurality of data consumers.

28 . The machine-storage medium of claim 21 , wherein the column is a customer identification column.

29 . The machine-storage medium of claim 21 , wherein the operations further comprise:

detecting an update to an entitlements table associated with the data table, wherein the entitlements table includes a plurality of entitlements table rows associated with client identifiers, consumer account identifiers, and provider account identifiers; and

performing incremental replication of the data table based on the update to the entitlements table.

30 . The machine-storage medium of claim 21 , wherein the operations further comprise:

detecting a change to the data table; and

updating the metadata for the one or more partitions affected by the change.

Assignments (1)

Continuity (6)

References Cited (28)