Performance Optimization Flashcards

In a Snowflake project, which config physically organizes table data to speed up filtering on specific columns?

Answer: cluster_by

cluster_by defines clustering keys on Snowflake tables to improve pruning for common filters.

Which config helps BigQuery reduce bytes scanned by restricting queries to date ranges?

Answer: partition_by

partition_by defines table partitioning so queries filtering on the partition column scan fewer bytes.

What does --cache-selected-only do?

Answer: Limits relation caching to schemas of selected nodes, reducing metadata queries

With --cache-selected-only dbt only populates its relation cache for the schemas containing selected models, speeding up startup on large warehouses.

Which incremental strategy is commonly used on BigQuery and Spark to replace entire partitions?

Answer: insert_overwrite

insert_overwrite replaces whole partitions with the new data, avoiding row-level merges.

What is the benefit of dbt's partial parsing?

Answer: It re-parses only files that changed since the last invocation

Partial parsing reuses the previous manifest and re-parses only changed files, cutting startup time in large projects.

Which setting controls the number of models dbt can run concurrently?

Answer: threads

The threads setting in the profile target sets the maximum concurrent connections used to build models.

What does the on_schema_change config on an incremental model control?

Answer: How new or removed columns in the source are handled in the target table

on_schema_change accepts ignore, fail, append_new_columns or sync_all_columns to define behavior when columns change.