Live Connect Zero Copy With Redshift

Live Connect Zero-Copy for Amazon Redshift allows customers to use the Treasure Data Customer Data Platform (CDP) to access their data on Redshift without the need for Extract, Transform, Load (ETL) processes. This approach enhances security and reduces costs associated with data movement. It enables seamless integration and querying of data stored in Redshift directly from Treasure Data environments.

Performing zero-copy from the Redshft Datawarehouse leverages the Treasure Data Redshift integration to perform a federated query. The data flow looks like this:

Federated queries enable data engineers to connect to external data warehouses like using their credentials to access and query data. This functionality facilitates quick data augmentation and improves the efficiency of the data enrichment process. By using federated queries, data engineers can dynamically query and join external data with internal data based on current needs, providing flexibility and reducing the need for complex ETL processes. This approach also ensures real-time data access, improved data governance and security, and simplified data management.

At a high level, the steps you take to implement zero-copy with Redshift are:

1. In the TD Integrations Hub, create an authentication using the Redshift integration.
2. Create a New Zero-Copy source, specifying your Redshift credentials and connection information.
3. In the TD Data Workbench, create a New Query. Preface the table name you want to query with the name of the zero-copy source you just created. For example, if you had a Zero-Copy (Federated Query) config named snowflake_conn which was configured like this:

Then you could specify a query of table1 in the test database like this:

select * from redshift_test.test_schema.table1

The test_schema is the Redshift schema name.

4. After running your query, process the results to update or augment your existing audience profiles.

After Treasure Data finishes processing the query results, the query results are deleted in seven days. However, you can use the INSERT INTO option to store the query results in Treasure Data.

Zero-copy with Redshift has the following limitations:

• Catalog name length cannot exceed 64 characters.
• Catalog name prefix must start with redshift_.
• You can create up to 200 "Zero-Copy (Federated Query) Configs" per account across all supported data warehouses combined. Each Config is a connection configuration for one data warehouse and corresponds to one Trino catalog; this limit applies to Configs, not tables.
• The federated query is only available on Trino.
• The 10GB input limit applies to the amount of data read from the external data warehouse per query. It does not apply to query results or data read from Treasure AI storage. If enabled and a query exceeds 10GB during execution, the query is aborted. This limit is not currently enabled by default in production, but is planned to be enabled by default in the near future. Customers cannot change this limit themselves, but Treasure AI Support can adjust the threshold on request.
• Catalog reflection time: Max 5 minutes(It takes up to 5 minutes to reflect a newly created zero-copy catalog or a change in a catalog.)

• Amazon Redshift Import Integration