Materialized view sub-database replication

Patent metadata

Field	Value
Publication number	US-12093284-B2
Application number	US-202318295987-A
Country	US
Kind code	B2
Filing date	Apr 5, 2023
Priority date	May 29, 2020
Publication date	Sep 17, 2024
Grant date	Sep 17, 2024

Abstract

A system for sub-table replication within a materialized view of a source data table. A materialized view of a source data table is generated based on a query, the source data table having a plurality of data table rows associated with a plurality of client identifiers and including an entitlements column and data content, the entitlements column including a plurality of provider account identifiers and a plurality of consumer account identifiers. An update to an entitlements table of the source data table is detected where the entitlements table includes a plurality of entitlements table rows and where the entitlements table rows are associated with the client identifiers, the consumer account identifiers, and the provider account identifiers. An update to the source data table based on the first update to the entitlements table is detected, and an incremental replication of the materialized view of the source data table is performed.

First claim

What is claimed is:

1. A computer-implemented method, the method comprising:
receiving, by one or more processors, from a database customer, a list of one or more objects of a database provider account of a first database in a first region, the one or more objects being replicated to a target account of the database customer, the target account associated with a second database in a second region;
materializing, by the one or more processors, one or more rows from the one or more objects of the first database into a micro-partition in the database provider account by creating a table and specifying therein the one or more rows from the one or more objects to be copied into the micro-partition;
generating, by the one or more processors, a replication policy using the list, the replication policy a database object that the customer uses to define the one or more objects to replicate to the target account; and
performing, by the one or more processors, a sub-database replication of the one or more objects from the database provider account of the first database to the target account of the second database using the replication policy, the sub-database replication including replicating the micro-partition of the database provider account to the second database in the second region.

2. The computer-implemented method of claim 1 , wherein the one or more objects are selected from a schema and a table.

3. The method of claim 2 ,
wherein the list comprises one or more included objects being included in the sub-database replication and one or more excluded objects being excluded from the sub-database replication.

4. The method of claim 3 ,
wherein the one or more included objects comprise a first at least one schema and the one or more excluded objects comprise a second at least one schema, and
performing the sub-database replication comprises performing schema-level filtering by including the one or more included objects in the sub-database replication using the first at least one schema and excluding the one or more excluded objects from the sub-database replication using the second at least one schema.

5. The method of claim 3 ,
wherein the one or more included objects comprise a first at least one table and the one or more excluded objects comprise a second at least one table, and
performing the sub-database replication comprises performing table-level filtering by including the one or more included objects in the sub-database replication using the first at least one table and excluding the one or more excluded objects from the sub-database replication using the second at least one table.

6. The method of claim 1 , further comprising:
receiving, by the one or more processors, from the database customer, an identification of a column in a data table being filtered and a filtering behavior to be applied to the column,
wherein performing the sub-database replication comprises applying the filter behavior to the column to perform a row-level filtering without duplicating a subset of rows of the data table.

7. The method of claim 6 ,
wherein the row-level filtering comprises reviewing a micro-partition of the data table to determine whether rows of the micro-partition are to be replicated to the target account.

8. The method of claim 1 ,
wherein the provider account is an account of the database customer, and
wherein the target account is a consumer account of a data consumer.

9. The method of claim 1 ,
wherein the sub-database replication of the one or more objects from the database provider account is performed for a plurality of target accounts,
wherein the provider account is an account of the database customer, and
wherein the plurality of target accounts are consumer accounts of a plurality of data consumers.

10. The method of claim 1 ,
wherein the provider account is an account of the database customer, the first database is a primary database of the database customer, and the second database is a secondary database of the database customer.

11. A data platform comprising:
one or more processors; and
one or more memories storing executable instructions that, when executed by the one or more processors, cause the data platform to perform operations comprising:
receiving from a database customer, a list of one or more objects of a database provider account of a first database in a first region, the one or more objects being replicated to a target account of the database customer, the target account associated with a second database in a second region;
materializing, by the one or more processors, one or more rows from the one or more objects of the first database into a micro-partition in the database provider account by creating a table and specifying therein the one or more rows from the one or more objects to be copied into the micro-partition;
generating a replication policy using the list, the replication policy a database object that the customer uses to define the one or more objects to replicate to the target account; and
performing a sub-database replication of the one or more objects from the database provider account of the first database to the target account of the second database using the replication policy, the sub-database replication including replicating the micro-partition of the database provider account to the second database in the second region.

12. The data platform of claim 11 , wherein the one or more objects are selected from a schema and a table.

13. The data platform of claim 12 ,
wherein the list comprises one or more included objects being included in the sub-database replication and one or more excluded objects being excluded from the sub-database replication.

14. The data platform of claim 13 ,
wherein the one or more included objects comprise a first at least one schema and the one or more excluded objects comprise a second at least one schema, and
performing the sub-database replication comprises performing schema-level filtering by including the one or more included objects in the sub-database replication using the first at least one schema and excluding the one or more excluded objects from the sub-database replication using the second at least one schema.

15. The data platform of claim 13 ,
wherein the one or more included objects comprise a first at least one table and the one or more excluded objects comprise a second at least one table, and
performing the sub-database replication comprises performing table-level filtering by including the one or more included objects in the sub-database replication using the first at least one table and excluding the one or more excluded objects from the sub-database replication using the second at least one table.

16. The data platform of claim 11 , wherein the operations further comprise:
receiving, by the one or more processors, from the database customer, an identification of a column in a data table being filtered and a filtering behavior to be applied to the column,
wherein performing the sub-database replication comprises applying the filter behavior to the column to perform a row-level filtering without duplicating a subset of rows of the data table.

17. The data platform of claim 16 ,
wherein the row-level filtering comprises reviewing a micro-partition of the data table to determine whether rows of the micro-partition are to be replicated to the target account.

18. The data platform of claim 11 ,
wherein the provider account is an account of the database customer, and
wherein the target account is a consumer account of a data

Assignees

• Snowflake Inc

Inventors

• Chu Pui Kei Johnston
• Desai Shreyas Narendra
• Gil Echeverri German Alberto
• Krishnan Prasanna
• Mahesh Nithin
• Muralidhar Subramanian
• Robinson Eric
• Saini Sahaj

Classifications

• G06Q30/0205

  based on location or geographical consideration · CPC title

• G06Q30/0185

  Product, service or business identity fraud · CPC title

• G06F16/2282 Primary

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2379

  Updates performed during online database operations; commit processing · CPC title

• G06F16/278

  Data partitioning, e.g. horizontal or vertical partitioning · CPC title