Direct data distribution from data warehouses covers extracting consolidated and cleaned data directly from a data warehouse and distributing it to your workspaces.
Integration with the following data warehouses is supported:
- Amazon Redshift data warehouse (see https://aws.amazon.com/redshift/)
- GoodData ADS (see Data Warehouse)
- Google BigQuery data warehouse (see https://cloud.google.com/bigquery/)
- Snowflake data warehouse (see https://www.snowflake.com/)
You can integrate data from your Snowflake instance/Redshift cluster/BigQuery project directly into the GoodData platform. The Automated Data Distribution (ADD) process synchronizes data from Snowflake/Redshift/BigQuery with your customers’ workspaces based on a defined schedule. This is a key approach in building optimized multi-tenant analytics for all your customers and users without the runaway costs associated with executing direct queries to your data warehouse. For more information about ADD benefits and usage, see Automated Data Distribution Reference.
Setting up direct data distribution from Snowflake/Redshift/BigQuery requires actions on your Snowflake instance/Redshift cluster/BigQuery project and on the GoodData platform.
Follow our step-by-step tutorials that will help you integrate Snowflake/Redshift/BigQuery and GoodData and provide you with as much automation during integration as possible.
- If you do not have a GoodData workspace yet or want to generate/update an existing LDM to best fit the existing data structure in your data warehouse, see Integrate Data Warehouses Directly to GoodData based on Your DW Schema.
- If you have a GoodData workspace with the logical data model (LDM) that meets your business requirements for data analysis, see Integrate Data Warehouses Directly to GoodData based on an Existing LDM.
Components of Direct Data Distribution
A Data Source is an entity that stores data warehouse credentials and the location of the Output Stage.
The Data Source is the main reference point when you are performing the following tasks:
- Generating the Output Stage. The API that you are using scans the data warehouse schema stored in your Data Source and generates recommended views for the Output Stage.
- Generating a logical data model (LDM). The API scans the Output Stage connected to your Data Source and provides a definition of the LDM, which can then be used for generating the LDM in your workspace via the API.
- Validating the mapping between the Data Source and the LDM. The API compares the Output Stage connected to your Data Source to the LDM and returns a list of inconsistencies. Validate the mapping after you have changed the Output Stage or the LDM to see what changes are required.
- Managing data mapping items. You can use data mapping if you want to override the way how data is loaded into workspaces. To do so, provide an alternative mapping scheme for project_id or client_id, respectively.
For more information about creating and listing Data Sources, see the API Reference.
The Output Stage is a set of tables and/or views that will serve as a source for loading data to the GoodData platform. You can prepare the Output Stage manually or generate the Output Stage for the Data Source.
If you decide to create the Output Stage manually, make sure that the following requirements are met:
- The Output Stage is located in the warehouse, database, and schema that you specified in your Data Source.
- All views and tables in your Output Stage have the prefix that you specified in the Data Source. All views and tables without the prefix are ignored during the data load.
- All columns are named according to the naming convention (see Naming Convention for Output Stage Objects). Column names can contain underscores (
__) only as part of the prefix.
- All columns in the Output Stage are compatible with the GoodData data types. For more information, see GoodData-Snowflake Integration Details, GoodData-Redshift Integration Details, and GoodData-BigQuery Integration Details.
- The names of the views and tables in the Output Stage do not contain special characters.
- There are no name collisions between your columns/tables and the GoodData technical columns and tables (see 'Special Columns in Output Stage Tables' in Naming Convention for Output Stage Objects).
If you decide to generate the Output Stage for the Data Source, review the resulting SQL code to check the following:
- All columns that should serve as connection points are prefixed with
- All columns that should serve as references are prefixed with
- Attributes that are represented by numeric values (for example, a customer tier that can be 1, 2, or 3) are prefixed with
a__. Unless this is done, the Data Source by default identifies all columns with a numerical data type (
FLOAT, and other) as facts (the prefix
You can generate the Output Stage from a different schema than the schema that will contain the Output Stage.
For better data load performance, we recommend that you apply the following best practices:
- Use tables/views to store data for all customers together with a
client_iddifferentiator. ADD can load such data to the customers’ workspaces much faster than when the data is stored per customer in dedicated tables/views.
- Use incremental loads instead of full loads.