Joining of datasets keyed with partner encrypted identifiers

Abstract

A system joins datasets within a data warehouse using universal identifiers where each dataset uses a different customer-specific encoding of the identifiers by generating one or more queries which transcode the identifiers into a common join key on demand. The common join key may be an identifier in a common encoding, or a decrypted identifier that is then hashed with a randomized salt generated for one query only. The system thus allows customers of an identifier service to collaborate in a data clean room environment without any data preprocessing. The system allows for transcoding of identifiers in SQL to facilitate customer collaboration without creating persistent data copies. The system further allows for collaboration using identifiers to create ad-hoc queries without delays from data ingestions or preprocessing.

Description

Attorney Docket No. RAMP-00303-WOJOINING OF DATASETS KEYED WITH PARTNER ENCRYPTED IDENTIFIERSCROSS-REFERENCE TO RELATED APPLICATIONS

[0001] This application claims the benefit of U.S. provisional patent application no. 63 / 680,821, filed on August 8, 2024. Such application is incorporated herein by reference in its entirety.BACKGROUN D OF TH E INVENTION

[0002] A data clean room allows customers who own consumer data or other sorts of datasets to bring their own data, stored in their own data warehouses (or accounts), and collaborate with other data owners in the data clean room. A critical advantage of data clean rooms is that privacy may be maintained with respect to personally identifiable information (PH) in the data while at the same time allowing collaboration. Each participating organization maintains its own data storage within the data clean room infrastructure, with the data being encrypted both in transit and at rest.

[0003] Another technology that facilitates data use and collaboration is universal identifiers. These identifiers are assigned to particular consumers, businesses, or other entities and are unique within that universe of entities. The identifiers facilitate the linking together of all data that pertains to a particular entity, regardless of where that data may be stored, by simply associating the data in some fashion with the identifier. The identifier may be pseudonymous, in which case the identifier itself may be shared without revealing any PI I. Two common universal identifier services are the RampID® pseudonymous identifier service and the AbiliTec® identifier service, both offered by LiveRamp, Inc. of San Francisco, California.

[0004] Universal identifiers are often encoded in a customer-specific manner. This encoding may be performed, for example, using hashing and / or encryption algorithms. The pseudonymous identifiers are encoded in this manner to prevent different customers of the provider of this service from surreptitiously collaborating by sharing data with each other by matching using the identifiers. With the identifiers being coded specifically for each customer, no such sharing is possible, thereby protecting consumer PH from efforts to defeat privacy restrictions. The unencoded identifiers are maintained only within the private systems of the provider, thereby preventing misuse.

[0005] It may be seen that customer-specific encoding of the identifiers is an impediment to the sharing of data within a data clean room. One solution to this problem would be to performAttorney Docket No. RAMP-00303-WQ a web service call to the provider's systems in order to facilitate matching with each attempt to share data within the data clean room. This approach, however, would introduce significant data movement, latency, and cost.

[0006] References mentioned in this background section are not admitted to be prior art with respect to the present invention.SUMMARY OF THE INVENTION

[0007] The present invention is directed to a system and method to efficiently join datasets accessible within a data warehouse using universal identifiers, where each dataset may use a different customer-specific encoding, by generating one or more queries which transcode the identifier into a common join key on demand. In certain embodiments, the common join key may be an identifier in a common encoding, or a decrypted identifier that is then hashed with a randomized salt generated for one query only.

[0008] In an embodiment, the present invention is directed to a system for joining of datasets, including a query engine planner configured to receive a SQL query referencing multiple datasets, analyze the SQL query to identify tables that need transcoding of identifiers, determine a common encoding for joining the identified tables, generate an execution plan including steps for transcoding the identifiers; and a query executor configured to fetch transcoding keys from a key service, execute the steps of the execution plan, including transcoding the identifiers in the identified tables to the common encoding, and join the transcoded datasets using the common encoding, wherein, if the tables are resident in a common database, the transcoded datasets are joined fully within the common database.

[0009] In an embodiment, the present invention is directed to a method for joining datasets keyed with partner-encrypted identifiers, including receiving a SQL query referencing multiple datasets, analyzing the SQL query to identify tables that need transcoding of identifiers, determining a common encoding for joining the identified tables, generating an execution plan including steps for transcoding the identifiers, fetching transcoding keys from a key service, executing the steps of the execution plan, including transcoding the identifiers in the identified tables to the common encoding, and joining the transcoded datasets using the common encoding.Attorney Docket No. RAMP-00303-WQ

[0010] In certain embodiments of the present invention, the system allows customers of an identifier service to collaborate, joining data on the identifier within a data clean room environment, without any preprocessing of their data.

[0011] In certain embodiments of the present invention, the system operates with enough efficiency to provide results to queries in seconds or minutes.

[0012] In certain embodiments of the present invention, the system allows for transcoding of identifiers in SQL to facilitate customer collaboration without creating persistent data copies, thereby reducing storage, processing resources, and communications bandwidth required to access records.

[0013] In certain embodiments of the present invention, the system reduces data warehouse compute resource utilization.

[0014] In certain embodiments of the present invention, the system allows for customers to collaborate using identifiers to create ad-hoc queries without delays from data ingestions or preprocessing, thereby allowing customers to keep their data as-is and in place.

[0015] 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

[0016] Fig. 1 is an architectural diagram for a system according to an embodiment of the present invention.

[0017] Fig. 2 is a diagram of a computer system component according to an embodiment of the present invention.DETAILED DESCRIPTION OF THE INVENTION

[0018] 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.

[0019] A system and method for implementation of the present invention may be described with reference to Fig. 1. A customer or user at user machine 10 submits an SQL query 12. An example query, utilizing identifiers from the LiveRamp RampID® system, may be:Attorney Docket No. RAMP-00303-WQSELECT COUNT (*)FROM customerl_peopleINNER JOIN custmoer2_peopleUSING (rampid)

[0020] Query 12 is processed by query planner 14, which resolves any references to tables or other entities against data catalog 16. Data catalog 16 contains assets which represent each of these references, organized by customer. Data catalog 16 may be implemented as a data warehouse in a cloud environment using, for example Snowflake or Google Big Query.

[0021] When a customer has access to another customer's data in the clean room, query planner 14 is able to resolve the second customer's asset using the customer's organization (along with policy information). The asset in data catalog 16 contains metadata about the table, including the customer-specific encoding of any RampID® columns, and the location of the table in a data warehouse. Query planner 14 detects when two referenced tables do not share the same pseudonymous identifier encoding, and determines a common encoding to use based on a heuristic.

[0022] Query planner 14 next compiles an execution plan 18 based on the locations of the tables in terms of the corresponding data warehouse, and submits that execution plan 18 to work orchestrator 20. Work orchestrator 20 then messages one or more query executors 22 in order according to the plan, and waits for their responses.

[0023] Each query executor 22 executes a step of the plan. For nodes that are not the final node, this includes writing interim files to processing storage 24, such as— in the case of a Google cloud implementation— writing Parquet to a GCS bucket. Those files are registered with claim check service 26, which controls access to them by query executors 22. A subsequent query executor 22 invocation will fetch the previous file location from claim check service 26, and create an external table 28 that references the location using a name present in the message, and executes the query for that step. The query text will reference external table 28 by its expected name contained within the message, such that when executed the table name is resolved by the data warehouse that contains the table to the new external table 28.

[0024] In the case of a transcode, the plan will contain generated SQL including the transcode operation only for the data warehouse in which the tables to be transcoded reside. This SQL manages transcoding of the identifiers in the table from the existing encoding to the target, which allows joining with the other sides. In the case of the half-transcode and hash with a salt,Attorney Docket No. RAMP-00303-WO the plan will contain generated SQL including the transcode operation for all data warehouses where tables with identifiers reside. These steps of the plan will contain the list of customerspecific encoding keys necessary to perform the transcode, and the worker will fetch those keys from key service 30 using its service account credentials, which may be stored in, for example, a Kubernetes secret.

[0025] The keys from key service 30 are next decrypted and provided to the data warehouse in query parameters, to avoid logging of the keys. When the SQL query 12 is executed in the data warehouse, it is executed in a provider-controlled account which has access to the customer- controlled tables via shares or identity and access management (1AM) policy, so customers do not have access to the keys via query history.

[0026] The transcode SQL is a series of native function calls, in the case of a cloud such as BigQuery either the decrypt-and-hash approach using native functions is used or a JavaScript user-defined function is utilized. The result of the transcode operation is handled like normal data and continues the process, either within a given step or into the next step.

[0027] SQL query 12 will be modified by query executors 22 as needed for the particular data warehouse environment required for a particular implementation. For example, in a Snowflake environment, SQL query 12 may appear as follows:SELECT encrypt( decrypt( customerl_keycustomer2_keyFROM customerl_peopleIn a Google BigQuery environment, SQL query 12 may appear as follows:SELECT COUNT(*JFROM customer2_people aINNER JOIN TEMP_1 bON (a.rampid = b.rampid)

[0028] Answers to queries are stored in the data warehouse at results 32, and made available back to user 10 through a call.Attorney Docket No. RAMP-00303-WQ

[0029] Table 1 illustrates cost differences (calculated as a function of compute time) for each of several transcoding implementations of the invention implemented within a Snowflake environment. The final implementation, "SQL v2", is benchmarked at twenty-four times faster than the JavaScript implementation utilized by the embedded cloud solution. It can also process data volumes that the previous solution, a process executed as a dedicated step in the plan on a single Kubernetes pod, was unable to. The higher compute cost relative to the single Kubernetes pod implementation, described herein as "Locksmith" as shown in the table below, does not consider the base cost of a Snowflake query to export the data so that Locksmith can operate on it.Table 1

[0030] Table 2 compares execution times for queries of different sizes, demonstrating that the new solution is several times faster than the "Locksmith" solution as the size of data increases.Table 2

[0031] Table 3 illustrates cost savings between the solution in BigQuery utilizing a JavaScript user-defined function to the implementation in Snowflake referenced in Table 1. The heuristic leverages this to route more transcodes to BigQuery when possible:Attorney Docket No. RAMP-00303-WQTable 3

[0032] The decrypt-and-hash approach provides an additional two-fold performance improvement over BigQuery using a JavaScript user-defined function, by using only native BigQuery SQL functions for decryption and replacing any unsupported encrypt operations with a hash + salt on all referenced tables.

[0033] The implementation as described herein is part of Query Engine, which includes the query planner 14 and query executor 22 components and interacts with data catalog 16, claim check service 26, work orchestrator 20, and the RampID® key service 30.

[0034] Query planner 14 extends Apache Spark's Catalyst framework for SQL query parsing and optimization with the ability to split queries for execution across multiple databases, and with SQL generation backends for each database. An analysis phase of query planner 14 provides for transcoding by identifying which tables need to be transcoded in the execution plan 18, and applies a heuristic to determine which mechanism to use and which tables to transcode.

[0035] Once the relevant tables have been marked with a transcode node in execution plan 18, a query syntax tree is translated into a physical query plan represented as Select-Project -Join expressions, where the transcode operation is represented in a stack of unary operators on a Select-Project-Join. Once the physical query plan has been partitioned, the logical query plan for each is traversed to detect transcode nodes, and the necessary transcode key information is annotated onto the node in the final execution plan 18 step destined for a query executor 22. This step also contains the logical query plan rendered as SQL for the given data warehouse, which in the process converts the same transcode node to a SQL expression representing the transcode, including the parameter placeholder for the transcoding keys to be provided by the query executor 22.

[0036] Query executors 22 are extended to detect the presence of parameter placeholders in the execution plan 18 step, and fetch those keys from key service 30. In the case of BigQuery, when not using the decrypt-and-hash approach, it will also execute a CREATE TEMPORARY FUNCTION statement with the JavaScript user-defined function which references a third-party JavaScript cryptographic library, for example Forge by Digital Bazaar, Inc. The user-definedAttorney Docket No. RAMP-00303-WO function accepts a from and to key and the RampID® value to transcode, and uses the library's cryptographic primitives to implement the decrypt / encrypt operation.

[0037] In an embodiment, the invention is implemented in a system that requires a Kubernetes cluster running atop multiple VMs in GCP, and interacts with several other services across the network. The query engine (query planner 14 and query executors 22) components use NATS for communication. In this embodiment, the invention utilizes an extended version of the Apache Spark's Catalyst framework to process SQL as part of query planner 14, and the Forge JavaScript library for BigQuery transcoding (not decrypt-with-hash).

[0038] The systems and 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 plurality of computer systems (e.g., each of which being a computer system as in Fig. 2) 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.

[0039] The program instructions may implement the functionality described herein (e.g., the functionality of various hardware servers and other components that implement the networkbased 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.

[0040] Fig. 2 is a block diagram illustrating an example computer hardware system, according to various embodiments. Computer system 240 may implement a hardware portion of a cloud computing system or non-cloud computing system, as forming parts of the various implementations of the present invention. Computer system 240 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.

[0041] Computer system 240 includes one or more hardware processors 241a, 241b...241n (any of which may include multiple processing cores, which may be single or multi-threaded) coupled to a physical system memory 242 via an input / output (I / O) interface 244. Computer system 240 further may include a network interface 246 coupled to I / O interface 244. In variousAttorney Docket No. RAMP-00303-WO embodiments, computer system 240 may be a single processor system including one hardware processor 241a, or a multiprocessor system including multiple hardware processors 241a, 241b...241n as illustrated in Fig. 2.

[0042] Processors 241a, etc. may be any suitable processors capable of executing computing instructions. For example, in various embodiments, processors 241a, etc. may be general- purpose or embedded processors implementing any of a variety of instruction set architectures. In multiprocessor systems, each of processors 241a, etc. may commonly, but not necessarily, implement the same instruction set.

[0043] The computer system 240 also includes one or more hardware network communication devices (e.g., network interface 246) 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 240 may use network interface 246 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 or non-cloud computing environment as implemented in various sub-systems. In another example, an instance of a server application executing on computer system 240 may use network interface 246 to communicate with other instances of an application that may be implemented on other computer systems.

[0044] In the illustrated embodiment, computer system 240 also includes one or more physical persistent storage devices 248 and / or one or more I / O devices 250. In various embodiments, persistent storage devices 248 may correspond to disk drives, tape drives, solid-state memory or drives, other mass storage devices, or any other persistent storage devices.

[0045] Computer system 240 (or a distributed application or operating system operating thereon) may store instructions and / or data in persistent storage devices 248, as desired, and may retrieve the stored instructions and / or data as needed. For example, in some embodiments, computer system 240 may implement one or more nodes of a control plane or control system, and persistent storage 248 may include the solid-state drives (SSDs) attached to that server node. Multiple computer systems 240 may share the same persistent storage devices 248 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.Attorney Docket No. RAMP-00303-WO

[0046] Computer system 240 includes one or more physical system memories 242 that may store code / instructions 243 and data 245 accessible by processor(s) 241a, etc. The system memories 242 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 248 in a virtual memory implementation, where memory space is mapped onto the persistent storage devices 248.

[0047] The technologies used to implement the system memories 242 may include, by way of example, static random-access memory (RAM), dynamic RAM, read-only memory (ROM), nonvolatile memory, solid-state memory, or flash-type memory. As with persistent storage devices 248, multiple computer systems 240 may share the same system memories 242 or may share a pool of system memories 242. System memory or memories 242 may contain program instructions 243 that are executable by processor(s) 241a, etc. to implement the routines described herein.

[0048] In various embodiments, program instructions 243 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 243 may implement multiple separate clients, server nodes, and / or other components.

[0049] In some implementations, program instructions 243 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 243 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.

[0050] 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 240 via I / O interface 244.Attorney Docket No. RAMP-00303-WO

[0051] 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 240 as system memory 242 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 246. Network interface 246 may be used to interface with other devices 252, which may include other computer systems or any type of external electronic device.

[0052] In some embodiments, system memory 242 may include data store 245, as described herein. In general, system memory 242 and persistent storage 248 may be accessible on other devices 252 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.

[0053] In one embodiment, I / O interface 244 may coordinate I / O traffic between processors 241a, etc., system memory 242, and any peripheral devices in the system, including through network interface 246 or other peripheral interfaces. In some embodiments, I / O interface 244 may perform any necessary protocol, timing or other data transformations to convert data signals from one component (e.g., system memory 242) into a format suitable for use by another component (e.g., processors 241a, etc.).

[0054] In some embodiments, I / O interface 244 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 244, such as an interface to system memory 242, may be incorporated directly into processor(s) 241a, etc.

[0055] Network interface 246 may allow data to be exchanged between computer system 240 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 244 may allow communication between computer system 240 and various I / O devices 250 and / or remote storage 248.Attorney Docket No. RAMP-00303-WO

[0056] Input / output devices 250 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 240. These may connect directly to a particular computer system 240 or generally connect to multiple computer systems 240 in a cloud computing environment, grid computing environment, or other system involving multiple computer systems 240.

[0057] Multiple input / output devices 250 may be present in communication with computer system 240 or may be distributed on various nodes of a distributed system that includes computer system 240. In some embodiments, similar input / output devices may be separate from computer system 240 and may interact with one or more nodes of a distributed system that includes computer system 240 through a wired or wireless connection, such as over network interface 246.

[0058] Network interface 246 may commonly support one or more wireless networking protocols (e.g., Wi-Fi / I EEE 802.11, or another wireless networking standard). Network interface 246 may support communication via any suitable wired or wireless general data networks, such as other types of Ethernet networks, for example.

[0059] Additionally, network interface 246 may support communication via telecommunications / telephony networks such as analog voice networks or 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 240 may include more, fewer, or different components than those illustrated in Fig. 7 (e.g., displays, video cards, audio cards, peripheral devices, or an Ethernet interface).

[0060] 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.

[0061] 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 byAttorney Docket No. RAMP-00303-WO 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.

[0062] 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).

[0063] In some embodiments, network-based services may be implemented using Representational State Transfer (REST) techniques rather than message-based techniques. For example, a network-based service implemented according to a REST technique may be invoked through parameters included within an HTTP method such as PUT, GET, or DELETE.

[0064] 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.

[0065] All terms used herein should be interpreted in the broadest possible manner consistent with the context. In particular, the terms "comprises" and "comprising" should be interpreted as referring to elements, components, or steps in a non-exclusive manner, indicating that the referenced elements, components, or steps may be present, or utilized, or combined with other elements, components, or steps that are not expressly referenced.

[0066] When a grouping is used herein, all individual members of the group and all combinations and subcombinations possible of the group are intended to be individually included.Attorney Docket No. RAMP-00303-WO

[0067] 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.

[0068] 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.

[0069] All references cited herein are hereby incorporated by reference to the extent that there is no inconsistency with the disclosure of this specification.

[0070] 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-00303-WQClaims1. A system for joining of datasets, comprising: a query engine planner configured to: receive a SQL query referencing multiple datasets; analyze the SQL query to identify tables that need transcoding of identifiers; determine a common encoding for joining the identified tables; generate an execution plan including steps for transcoding the identifiers; and a query executor configured to: fetch transcoding keys from a key service; execute the steps of the execution plan, including transcoding the identifiers in the identified tables to the common encoding; and join the transcoded datasets using the common encoding, wherein, if the tables are resident in a common database, the transcoded datasets are joined fully within the common database.

2. The system of claim 1, wherein the query engine planner is further configured to split the SQL query for execution across multiple databases.

3. The system of claim 1, wherein the query engine planner is configured to determine the common encoding based on a heuristic.

4. The system of claim 1, wherein the execution plan includes SQL statements for transcoding the pseudonymous identifiers within a data warehouse where the datasets reside.

5. The system of claim 1, wherein the query executor is configured to determine a common encoding for joining the identified tables by hashing a decrypted identifier with a randomized salt, wherein the randomized salt is generated for one query only.

6. The system of claim 5, wherein the query executor is configured to execute the transcoding using native database functions for decryption and hashing.Attorney Docket No. RAMP-00303-WQ7. The system of claim 1, wherein the query executor is configured to execute the transcoding using a JavaScript user-defined function.

8. The system of claim 1, wherein the system is configured to operate within a data clean room environment.

9. The system of claim 1, wherein the system is configured to join datasets stored in different customer accounts within the same data warehouse.

10. The system of claim 1, wherein the system is configured to join datasets stored in different data warehouses.

11. A method for joining datasets keyed with partner-encrypted identifiers, comprising: receiving a SQL query referencing multiple datasets; analyzing the SQL query to identify tables that need transcoding of identifiers; determining a common encoding for joining the identified tables; generating an execution plan including steps for transcoding the identifiers; fetching transcoding keys from a key service; executing the steps of the execution plan, including transcoding the identifiers in the identified tables to the common encoding; and joining the transcoded datasets using the common encoding.

12. The method of claim 11, further comprising splitting the SQL query for execution across multiple databases.

13. The method of claim 11, wherein determining the common encoding is based on a heuristic.

14. The method of claim 11, wherein the execution plan includes SQL statements for transcoding the pseudonymous identifiers within a data warehouse where the datasets reside.

15. The system of claim 11, wherein the step of determining a common encoding for joining the identified tables comprises hashing a decrypted identifier with a randomized salt, wherein the randomized salt is generated for one query only.

16. The method of claim 15, wherein executing the transcoding comprises using native database functions for decryption and hashing.

17. The method of claim 11, wherein executing the transcoding comprises using a user-defined function.Attorney Docket No. RAMP-00303-WO18. The method of claim 11, wherein the method is performed within a data clean room environment.

19. The method of claim 11, further comprising joining datasets stored in different customer accounts within the same data warehouse.

20. The method of claim 11, further comprising joining datasets stored in different data warehouses.

Images