Flow Optimization Settings Dialog
Note
This feature may not be available in all product editions. For more information on available features, see Compare Editions.
In the Flow Optimization Settings dialog, you can configure the following settings, which provide finer-grained control and performance tuning over your flow and its job executions. From the Flow View menu, select Optimization settings.
This feature must be enabled at the workspace level. When enabled, the settings in this dialog are applied to the current flow.
These optimizations are designed to improve performance by pre-filtering the volume of data by reducing the columns and rows to the ones that are actually used.
Tip
In general, all of these optimizations should be enabled for each flow. As needed, you can selectively disable optimizations if you are troubleshooting execution issues.
When these filters are enabled, the number of filters successfully applied to a job execution is listed in the Optimization summary in the Job Details page. See Job Details Page.
Enable optimization for jobs from this flow
When enabled, the Trifacta Application attempts to apply any of the listed optimizations that are enabled to jobs that are executed for this flow.
Note
When thisoption is disabled, then no optimization settings are available.
General Optimizations
The following optimizations can be enabled or disabled in general. For individual data sources, you may be able to enable or disable these settings based on your environment and its requirements.
Tip
These optimizations are applied at the recipe level. They can be applied on any flow and may improve performance within the Transformer page.
When enabled, job execution performance is improved by removing any unused or redundant columns based on the recipe that is selected.
When this setting is enabled, the Trifacta Application optimizes job performance on this flow by pushing data filters to recipes.
Additional optimizations can be enabled or disabled for specific types of transformations or jobs.
File-Based Optimizations
When enabled, jobs for this flow that are sourced from files stored in Cloud Storage can be executed in BigQuery.
Note
Additional limitations and requirements may apply for file-based job execution.
For more information, see BigQuery Running Environment.
Databases that Support Pushdown
Individual types of databases may support one or more of the following pushdowns. Additional restrictions may apply for your specific database.
Tip
These optimizations are applied to queries of your relational datasources that support pushdown. These optimizations are applied within the source, which limits the volume of data that is transferred during job execution.
Note
For each relational connection, you can enable the optimization capabilities to improve the flow and its job execution performance. The optimization settings may vary based on the type of relational connections.
When enabled, job execution performance is improved by removing any unused or redundant columns from the source database.
Limitations:
Column pruning optimizations cannot be applied to imported datasets generated with custom SQL.
When this setting is enabled, the Trifacta Application optimizes job performance on this flow by pushing data filters directly on the source database.
Limitations:
Filter pushdown optimizations cannot be applied to imported datasets generated with custom SQL.
Pushdown filters cannot be applied to dates in your relational sources.
Note
SQL-based filtering is performed on a best-effort basis. When these optimizations are enabled for your flow, there is no guarantee that they will be applied during job execution.
Note
The connection types may or may not be available in your product edition. For more information, see Connection Types.
When this setting is enabled, the Trifacta Application optimizes job performance by executing sampling jobs directly on the source database.
Note
All pushdowns must be enabled to ensure sample jobs run in the database.
Limitations for sampling on BigQuery:
The following sampling types are not supported for execution in BigQuery:
Initial rows
Stratified sampling
Cluster-based sampling
Quick scan sampling jobs are executed on Trifacta Photon, which cannot be pushed down to BigQuery.
Note
Trifacta Photon running environment may not be enabled in your project. A workspace administrator can enable it, if needed. For more information, see Dataprep Project Settings Page.
Full execution of sampling jobs is limited only to BigQuery data sources.
If the calculated number of rows to include in the sample is not possible, the sample is limited to 10,000 rows as a maximum.
Datasources hosted in Cloud Storage are not pushed down to BigQuery for sampling.
Limitations for sampling on Snowflake :
The following sampling types are not supported for execution in Snowflake:
Initial rows
Stratified sampling
Cluster-based sampling
Full execution of sampling jobs is limited only to Snowflake data sources.
If the calculated number of rows to include in the sample is not possible, the sample is limited to 10,000 rows as a maximum.
Datasources hosted in Cloud Storage are not pushed down to Snowflake for sampling.
BigQuery pushdown options
In addition to common database options, the following flow optimization options are available for BigQuery.
When enabled,BigQuery tablesthat meet all pushdown requirements can be executed in BigQuery and published to Cloud Storage.
Limitations:
BigQuery running environment supports only GZIP compression type for CSV and JSON files.
BZIP2 compression is not supported.
Only CSV, JSON, and AVRO file formats are supported.
For more information on executing jobs in BigQuery, see BigQuery Running Environment.
Other Databases
Databases that do not support pushdown may support the following optimization settings.
When enabled, job execution performance is improved by removing any unused or redundant columns from the source database.