Universal constraint validation

Abstract

A universal constraints validator applies data rules consistently and effectively regardless of the data's location across multiple data warehouses, and enables easier collaboration between data warehouse owners with varied technology environments. The universal constraint validator allows for rules ascribed to datasets, both explicitly by the user and implicitly inferred from metadata, to be run on queries that access the dataset to validate that its contents have not been misused. In certain embodiments, the queries executed across these datasets can be written in any dialect of SQL due to first being parsed into an AST representation before validation rules are applied. The result is a more secure data environment with consistent and reliable protections against misuse of private data.

Description

Attorney Docket No. RAMP-00310-WQUniversal Constraint ValidationBACKGROUN D OF THE INVENTION

[0001] A data warehouse is a large, centralized repository of structured data from various sources within an organization. It is designed to store, process, and analyze vast amounts of historical and current data to support business intelligence, reporting, and decision-making. Data warehouses typically use a schema optimized for querying and analysis rather than for transaction processing, allowing users to quickly extract insights and generate reports across multiple dimensions of the business.

[0002] Some data warehouses allow rules to be applied to datasets to validate queries.However, when joining data between warehouses, or between companies with different levels of expertise in SQL dialects or privacy requirements, issues may arise applying these warehousespecific rules. The inventors hereof have recognized that it would be desirable to develop a technological environment that applies data rules consistently and effectively regardless of the data's location, and enables easier collaboration between data warehouse owners with varied technology environments in order to coordinate data use.SUMMARY OF THE INVENTION

[0003] The present invention is directed to a universal constraints validator operated by a service provider, which applies data rules consistently and effectively regardless of the data's location across multiple data warehouses, and enables easier collaboration between data warehouse owners with varied technology environments. The service provider's universal constraint validator allows for rules ascribed to datasets, both explicitly by the user and implicitly inferred from metadata, to be run on queries that access the dataset to validate that its contents have not been misused.

[0004] In certain embodiments, the queries executed across these datasets can be written in any dialect of SQL due to first being parsed into an AST representation before validation rules are applied. This gives the user confidence to share data via queries they have not written, with confidence that all applicable rules regarding privacy and other use restrictions will be honored. The result is a more secure data environment with consistent and reliable protections against misuse of private data.Attorney Docket No. RAMP-00310-WO

[0005] In certain embodiments, various constraints may be used alone or in combination in order to achieve the objectives of the invention. These include projection constraints, analytical functions, join column constraints, and mandatory join constraints. A projection constraint prevents columns from being projected from a query, regardless of their transformation within the query itself. Analytical functions only allow column projection if passed through certain aggregation functions, and no others. Join column constraints only allow certain columns to be used in join condition expressions. Mandatory join constraints only allow a table to be accessed if it is joined on.

[0006] The universal constraints validator is both more consistent and more efficient than previous solutions to the same problem that relied on warehouse-specific implementations of the constraints where available. These implementations are not necessarily identical between warehouses, and thus raise interoperability issues. These warehouse-specific implementations also mean that data warehouse owners using certain technology stacks would be excluded from beneficial privacy features.

[0007] The present invention also requires less network traffic over public networks, resulting in decreased latency. Some of the warehouse-specific implementations also require tables to exist for queries to be validated, and when a dataset owner is not ready to grant access to the real tables, the system must create fake tables with similar structures in order to run the constraints. These tables must then be cleared up once validation is run. This both increases latency in the validation run and is computationally more expensive than the simpler schema based approach used by the universal constraints validator according to certain embodiments of the present invention.

[0008] These and other features, objects and advantages of the present invention will become better understood from a consideration of the following detailed description of the preferred embodiments and appended claims in conjunction with the drawings as described following:BRIEF DESCRIPTION OF DRAWINGS

[0009] Fig. 1 is a swim lane diagram showing an implementation of the present invention using explicit analysis rules.

[0010] Fig. 2 is a swim lane diagram showing an implementation of the present invention using implicit analysis rules.Attorney Docket No. RAMP-00310-WO

[0011] Fig. 3 is a schematic of a compute component making up a portion of a compute cluster within a cloud computing framework in which the present invention may be implemented.DETAILED DESCRIPTION OF THE INVENTION

[0012] Before the present invention is described in further detail, it should be understood that the invention is not limited to the particular embodiments described, and that the terms used in describing the particular embodiments are for the purpose of describing those particular embodiments only, and are not intended to be limiting, since the scope of the present invention will be limited only by the claims.

[0013] Fig. 1 provides an example of a collaboration between two data warehouses operated or owned by two different parties (Customer 1 and Customer 2). A data clean room 10 is employed in this collaboration. In general, a data clean room is a secure, privacy-compliant environment where multiple parties can share and analyze sensitive data without revealing raw information to each other. It allows organizations to combine and process data sets while maintaining strict control over data access and usage. Data clean rooms employ advanced encryption, anonymization techniques, and access controls to enable collaborative analysis and insights generation while protecting individual privacy and adhering to data protection regulations.

[0014] In addition, a data registry 12 is shown in Fig. 1 for the purpose of keeping track of rules applicable to the collaboration that takes place in the clean room 10. In addition, the service provider server is shown that provides the universal constraints validator 14.

[0015] The collaboration process begins with Customer 1 formulating a question with an open dataset parameter at step 20, which is then presented in the clean room where data may be securely shared. The question is shared with the collaborator (Customer 2) at step 22, who accepts the invitation to share data within the clean room environment at step 24.

[0016] Next, Customer 2 configures the analysis rules that will apply to this use of its dataset at step 30, and the rules are saved to the data registry at step 32. Customer 2 then sets the dataset as a question parameter into the clean room at step 34, and dataset details are retrieved in communication with the data registry at step 36. The analysis of the rules for this dataset is run against the question at step 40, with the appropriate rules retrieved from the data registry at step 42.Attorney Docket No. RAMP-00310-WQ

[0017] The question, dataset, and analysis rules for validation are then all sent to the universal constraints validator at step 46. The question, likely but not necessarily in the form of a SQL query, is parsed into an abstract syntax tree (AST) at step 48. In general, an AST is a tree-like representation of the abstract syntactic structure of source code written in a programming language. It captures the essential elements and relationships of the code, stripping away syntax details like punctuation and whitespace. Each node in the tree typically represents a construct in the SQL code, such as a function declaration, a loop, or an arithmetic operation.

[0018] Once the conversion to AST is complete, the universal constraints validator validates the analysis rules against the AST at step 50. The universal constraints validator then returns details of analysis rule failures, if any, to the data clean room at step 54, and then to Customer 2 at step 56. If there are no analysis rules failures, the dataset operation is allowed to proceed.

[0019] Fig. 2 shows a different embodiment of the present invention in which Customer 2 initially sets up its dataset with certain fields as being sensitive, such as in the case that those fields contain personally identifiable information (PH). Then, when a question is run using the dataset, analysis rules are automatically inferred from the dataset's metadata, and the question is only run if the universal constraints validator finds no errors in the question. Otherwise, processing is similar to the explicit analysis rules example of Fig. 1.

[0020] More specifically, Customer 1 creates a question with an open dataset parameter, which is presented into the data clean room at step 20. This question is shared with Customer 2 at step 22, which then accepts the invitation for data sharing at step 24. Custom 2 then creates the dataset complete with marked Pll fields at step 26, and this is saved with metadata to the data registry at step 28. The dataset is set as a question parameter at step 34 and the question is run at step 38, the data registry provides dataset details into the clean room at step 36, and the analysis rules are inferred from the dataset with marked Pll fields at step 44.

[0021] The question, dataset, and analysis rules are provided to the universal constraints validator at step 46. The universal constraint validator first parses the question from SQL into AST format at step 48. It then validates the analysis rules inferred from the dataset's metadata against the resulting AST form of the question at step 50. Details of any analysis rule failures are provided back to the clean room at step 52. If the universal constraints validator does detect failures, then this is reported back to customer 2 at step 58. If there are no failures, then the question is submitted at step 60 and executed in the data warehouse / query executor 16 atAttorney Docket No. RAMP-00310-WO step 62, and the result for the question is returned to the clean room at step 64, which then in turn provides the result to customer 2 at step 66.

[0022] A case study was run with the universal constraint validator as described above against an alternative solution utilizing a popular data clean room collaboration environment. The universal constraint validator had a latency mean of 937ms across all samples for a simple query with a single projection constraint, and a standard deviation of 36.8ms. This is compared with the native functionality of the data clean room provider, which requires creation of a synthetic table and clean up after the query has been run. The native functionality had a latency mean of 6874ms across all samples, for the same query and projection constraints, with a standard deviation of 836ms. This makes the universal constraints validator as described above in a particular embodiment more than seven times faster, and much more consistent in response time as it does not need to call out from the private network maintained by the provider of the universal constraints validator.

[0023] Another test using a more complicated table with thirty configured projection constraints showed an increase in the latency of both solutions, with the mean latency of the universal constraints validator being 1240ms and the native warehouse validation being 8134ms. This is a higher percentage increase in the latency of the universal constraints validator (32.3% vs 18.7%), but given the lower starting value it is still a much smaller increase. This test case also saw a higher standard deviation for the native warehouse validation solution, 2794ms, with a maximum latency of over 16s. The additional consistency provided by the universal constraints validator would be important if we were to implement certain extensions in alternative embodiments, such as providing real-time feedback to users during query writing to avoid errors or highlight the parts of the query that have caused the failure.

[0024] When running on a fully deployed production environment, on a variety of queries, many of which are significantly more complex than those used in the performance tests, the latency of the universal constraints validator was still 2312ms on average. This is better than the warehouse-native specific implementation, even when that is run under more idealized conditions.

[0025] It may also be noted that there is a financial cost associated with the queries run against the native data clean room environment functionality, and while this is small it would scale linearly as projection constraint validation is enabled for all query runs.Attorney Docket No. RAMP-00310-WO

[0026] The methods described herein may in various embodiments be implemented by any combination of hardware and software. For example, in one embodiment, the methods may be implemented by a computer system (e.g., a computer system as in Fig. 3) or a collection of computer systems, each of which includes one or more hardware processors executing program instructions stored on a computer-readable physical storage medium coupled to the hardware processors, within the provider environment 10 and the customer environment 14. The program instructions may implement the functionality described herein (e.g., the functionality of various hardware servers and other components that implement the network-based cloud and non-cloud computing resources described herein). The various methods as illustrated in the figures and described herein represent example implementations. The order of any method may be changed, and various elements may be added, modified, or omitted.

[0027] Fig. 3 is a block diagram illustrating an example computer hardware system, according to various embodiments. Computer system 140 may implement a hardware portion of a cloud computing system as forming parts of the various implementations of the present invention. Computer system 140 may be any of various types of hardware devices, including, but not limited to, a commodity server, personal computer system, desktop computer, laptop or notebook computer, mainframe computer system, handheld computer, workstation, network computer, a consumer device, application server, physical storage device, telephone, mobile telephone, or in general any type of computing node, compute node, compute device, and / or hardware computing device.

[0028] Computer system 140 includes one or more hardware processors 140a, 141b... Mln (any of which may include multiple processing cores, which may be single or multi-threaded) coupled to a physical system memory 142 via an input / output (I / O) interface 144. Computer system 140 further may include a network interface 146 coupled to I / O interface 144. In various embodiments, computer system 140 may be a single processor system including one hardware processor 141a, or a multiprocessor system including multiple hardware processors 141a, 141b...141n as illustrated in Fig. 3. Processors 141a, etc. may be any suitable processors capable of executing computing instructions. For example, in various embodiments, processors 141a, etc. may be general-purpose or embedded processors implementing any of a variety of instruction set architectures. In multiprocessor systems, each of processors 141a, etc. may commonly, but not necessarily, implement the same instruction set. The computer system 140 also includes one or more hardware network communication devices (e.g., network interfaceAttorney Docket No. RAMP-00310-WO146) for communicating with other systems and / or components over a communications network, such as a local area network, wide area network, or the Internet. For example, a client application executing on system 140 may use network interface 146 to communicate with a server application executing on a single hardware server or on a cluster of hardware servers that implement one or more of the components of the systems described herein in a cloud computing environment as implemented in various sub-systems. In another example, an instance of a server application executing on computer system 140 may use network interface 146 to communicate with other instances of an application that may be implemented on other computer systems.

[0029] In the illustrated embodiment, computer system 140 also includes one or more physical persistent storage devices 148 and / or one or more I / O devices 150. In various embodiments, persistent storage devices 148 may correspond to disk drives, tape drives, solid-state memory or drives, other mass storage devices, or any other persistent storage devices. Computer system 140 (or a distributed application or operating system operating thereon) may store instructions and / or data in persistent storage devices 148, as desired, and may retrieve the stored instructions and / or data as needed. For example, in some embodiments, computer system 140 may implement one or more nodes of a control plane or control system, and persistent storage 148 may include the solid-state drives (SSDs) attached to that server node. Multiple computer systems 140 may share the same persistent storage devices 148 or may share a pool of persistent storage devices, with the devices in the pool representing the same or different storage technologies, including such technologies as described above.

[0030] Computer system 140 includes one or more physical system memories 142 that may store code / instructions 143 and data 145 accessible by processor(s) 141a, etc. The system memories 142 may include multiple levels of memory and memory caches in a system designed to swap information in memories based on access speed, for example. The interleaving and swapping may extend to persistent storage devices 148 in a virtual memory implementation, where memory space is mapped onto the persistent storage devices 148. The technologies used to implement the system memories 142 may include, by way of example, static randomaccess memory (RAM), dynamic RAM, read-only memory (ROM), non-volatile memory, solid- state memory, or flash-type memory. As with persistent storage devices 148, multiple computer systems 140 may share the same system memory systems 142 or may share a pool of system memories 142. System memory or memory systems 142 may contain programAttorney Docket No. RAMP-00310-WO instructions 143 that are executable by processor(s) 141a, etc. to implement the routines described herein.

[0031] In various embodiments, program instructions 143 may be encoded in binary, Assembly language, any interpreted language such as Java, compiled languages such as C / C++, or in any combination thereof; the particular languages given here are only examples. In some embodiments, program instructions 143 may implement multiple separate clients, server nodes, and / or other components.

[0032] In some implementations, program instructions 143 may include instructions executable to implement an operating system (not shown), which may be any of various operating systems, such as UNIX, LINUX, Solaris™, MacOS™, or Microsoft Windows™. Any or all of program instructions 143 may be provided as a computer program product, or software, that may include a non-transitory computer-readable storage medium having stored thereon instructions, which may be used to program a computer system (or other electronic devices) to perform a process according to various implementations. A non-transitory computer-readable storage medium may include any mechanism for storing information in a form (e.g., software or processing application) readable by a machine (e.g., a physical computer). Generally speaking, a non- transitory computer-accessible medium may include computer-readable storage media or memory media such as magnetic or optical media, e.g., disk or DVD / CD-ROM, coupled to or in communication with computer system 140 via I / O interface 144. A non-transitory computer- readable storage medium may also include any volatile or non-volatile media such as RAM or ROM that may be included in some embodiments of computer system 140 as system memory 142 or another type of memory. In other implementations, program instructions may be communicated using optical, acoustical or other form of propagated signals (e.g., carrier waves, infrared signals, digital signals, etc.) conveyed via a communication medium such as a network and / or a wired or wireless link, such as may be implemented via network interface 606. Network interface 146 may be used to interface with other devices 142, which may include other computer systems or any type of external electronic device.

[0033] In some embodiments, system memory 142 may include data store 145, as described herein. In general, system memory 142 and persistent storage 148 may be accessible on other devices 142 through a network and may store data blocks, replicas of data blocks, metadata associated with data blocks, and / or their state, database configuration information, and / or any other information usable in implementing the routines described herein.Attorney Docket No. RAMP-00310-WO

[0034] In one embodiment, I / O interface 144 may coordinate I / O traffic between processors 141a, etc., system memory 142, and any peripheral devices in the system, including through network interface 146 or other peripheral interfaces. In some embodiments, I / O interface 144 may perform any necessary protocol, timing or other data transformations to convert data signals from one component (e.g., system memory 142) into a format suitable for use by another component (e.g., processors 141a, etc.). In some embodiments, I / O interface 144 may include support for devices attached through various types of peripheral buses, such as a variant of the Peripheral Component Interconnect (PCI) bus standard or the Universal Serial Bus (USB) standard, as examples. Also, in some embodiments, some or all of the functionality of I / O interface 144, such as an interface to system memory 142, may be incorporated directly into processor(s) 141a, etc.

[0035] Network interface 146 may allow data to be exchanged between computer system 140 and other devices attached to a network, such as other computer systems (which may implement one or more storage system server nodes, primary nodes, read-only node nodes, and / or clients of the database systems described herein), for example. In addition, I / O interface 144 may allow communication between computer system 140 and various I / O devices 150 and / or remote storage 148. Input / output devices 150 may, in some embodiments, include one or more display terminals, keyboards, keypads, touchpads, scanning devices, voice or optical recognition devices, or any other devices suitable for entering or retrieving data by one or more computer systems 140. These may connect directly to a particular computer system 140 or generally connect to multiple computer systems 140 in a cloud computing environment, grid computing environment, or other system involving multiple computer systems 140. Multiple input / output devices 150 may be present in communication with computer system 140 or may be distributed on various nodes of a distributed system that includes computer system 140. In some embodiments, similar input / output devices may be separate from computer system 140 and may interact with one or more nodes of a distributed system that includes computer system 140 through a wired or wireless connection, such as over network interface 146. Network interface 146 may commonly support one or more wireless networking protocols (e.g., Wi- Fi / IEEE 802.11, or another wireless networking standard). Network interface 146 may support communication via any suitable wired or wireless general data networks, such as other types of Ethernet networks, for example. Additionally, network interface 146 may support communication via telecommunications / telephony networks such as analog voice networks orAttorney Docket No. RAMP-00310-WO digital fiber communications networks, via storage area networks such as Fibre Channel SANs, or via any other suitable type of network and / or protocol. In various embodiments, computer system 140 may include more, fewer, or different components than those illustrated in Fig. 3 (e.g., displays, video cards, audio cards, peripheral devices, or an Ethernet interface).

[0036] Any of the distributed system embodiments described herein, or any of their components, may be implemented as one or more network-based services in the cloud computing environment. For example, a read-write node and / or read-only nodes within the database tier of a hardware database system may present database services and / or other types of physical data storage services that employ the distributed storage systems described herein to clients as network-based services. In some embodiments, a network-based service may be implemented by a software and / or hardware system designed to support interoperable machine-to-machine interaction over a network. A web service may have an interface described in a machine-processable format. Other systems may interact with the network-based service in a manner prescribed by the description of the network-based service's interface. For example, the network-based service may define various operations that other systems may invoke, and may define a particular application programming interface (API) to which other systems may be expected to conform when requesting the various operations.

[0037] In various embodiments, a network-based service may be requested or invoked through the use of a message that includes parameters and / or data associated with the network-based services request. Such a message may be formatted according to a particular markup language such as Extensible Markup Language (XML), and / or may be encapsulated using a protocol. To perform a network-based services request, a network-based services client may assemble a message including the request and convey the message to an addressable endpoint (e.g., a Uniform Resource Locator (URL)) corresponding to the web service, using an Internet-based application layer transfer protocol such as Hypertext Transfer Protocol (HTTP).

[0038] Unless otherwise stated, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs. Although any methods and materials similar or equivalent to those described herein can also be used in the practice or testing of the present invention, a limited number of the exemplary methods and materials are described herein. It will be apparent to those skilled in the art that many more modifications are possible without departing from the inventive concepts herein.Attorney Docket No. RAMP-00310-WO

[0039] All terms used herein should be interpreted in the broadest possible manner consistent with the context. When a grouping is used herein, all individual members of the group and all combinations and sub-combinations possible of the group are intended to be individually included. When a range is stated herein, the range is intended to include all sub-ranges within the range, as well as all individual points within the range. When "about," "approximately," or like terms are used herein, they are intended to include amounts, measurements, or the like that do not depart significantly from the expressly stated amount, measurement, or the like, such that the stated purpose of the apparatus or process is not lost. All references cited herein are hereby incorporated by reference to the extent that there is no inconsistency with the disclosure of this specification.

[0040] The present invention has been described with reference to certain preferred and alternative embodiments that are intended to be exemplary only and not limiting to the full scope of the present invention, as set forth in the appended claims.

Claims

Attorney Docket No. RAMP-00310-WOClaims1. An apparatus for universal constraint validation, comprising: a data clean room in a cloud computing environment; a data registry in communication with the data clean room; a universal constraints validator in communication with the data registry and the data clean room; and a processor configured to: receive a query and a dataset; retrieve analysis rules associated with the dataset from the data registry; parse the query into an abstract syntax tree (AST) format; apply the analysis rules to the AST format of the query using the universal constraints validator; and generate a validation result indicating whether the query complies with the analysis rules.

2. The apparatus of claim 1, wherein the analysis rules are explicitly configured by a dataset owner.

3. The apparatus of claim 1, wherein the analysis rules are implicitly inferred from metadata associated with the dataset.

4. The apparatus of claim 3, wherein the metadata includes information about sensitive fields in the dataset, personally identifiable information (PI I) in the dataset, or both sensitive fields and Pll in the dataset.

5. The apparatus of claim 1, wherein the processor is further configured to prevent execution of the query if the validation result indicates non-compliance with the analysis rules.

6. The apparatus of claim 1, wherein the processor is further configured to execute the query if the validation result indicates compliance with the analysis rules, and return a query result to a user.Attorney Docket No. RAMP-00310-WO7. The apparatus of claim 1, wherein the analysis rules include at least one of: a projection constraint, an analytical functions constraint, a joint column constraint, and a mandatory joint constraint.

8. The apparatus of claim 7, wherein the projection constraint prevents specific columns from being projected from the query.

9. The apparatus of claim 7, wherein the analytical functions constraint only allows column projection if passed through certain aggregation functions.

10. The apparatus of claim 7, wherein the joint column constraint only allows certain columns to be used in join condition expressions.

11. The apparatus of claim 7, wherein the mandatory joint constraint only allows a table in the dataset to be accessed if it is joined on.

12. A method for universal constraint validation, the method comprising the steps of: receiving a query and a dataset; retrieving analysis rules associated with the dataset from a data registry; parsing the query into an abstract syntax tree (AST) format; applying the analysis rules to the AST format of the query using a universal constraints validator; and generating a validation result indicating whether the query complies with the analysis rules.

13. The method of claim 12, further comprising the step of configuring the analysis rules explicitly by a dataset owner.

14. The method of claim 12, further comprising the step of inferring the analysis rules implicitly from metadata associated with the dataset.

15. The method of claim 14, wherein the metadata includes information about sensitive fields in the dataset or personally identifiable information (PH) in the dataset or both sensitive fields and PH in the dataset.Attorney Docket No. RAMP-00310-WQ16. The method of claim 12, further comprising the step of preventing execution of the query if the validation result indicates non-compliance with the analysis rules.

17. The method of claim 12, further comprising the steps of executing the query if the validation result indicates compliance with the analysis rules, and returning a query result to a user.

18. The method of claim 12, wherein the analysis rules include at least one of: a projection constraint, an analytical functions constraint, a joint column constraint, and a mandatory joint constraint.

19. The method of claim 12, wherein the query is written in a first dialect of SQL different from a second native dialect of SQL for the dataset.

20. The method of claim 12, further comprising the step of providing the validation result to a data clean room interface for display to a user.

Images