Cost & Performance interview questions

1. A.Charges incurred when moving data out of a cloud region or provider, a factor in multi-cloud and cross-region pipeline design.
2. B.Skipping entire partitions that cannot match a query's filter, the primary way partitioning reduces scanned data and cost.
3. C.A cache that returns a previously computed query result instantly when the same query runs again over unchanged data, scanning zero bytes.
4. D.Matching warehouse or slot capacity to workload so jobs finish quickly without paying for idle or oversized compute.

Answer + AI explanation with Pro

1. A.SELECT star avoidance
2. B.partition pruning
3. C.broadcast join
4. D.materialization tradeoff

Answer + AI explanation with Pro

1. A.partition pruning — Skipping entire partitions that cannot match a query's filter, the primary way partitioning reduces scanned data and cost.
2. B.partition pruning — Physically co-locating rows by frequently filtered columns so the engine reads fewer micro-partitions or blocks for selective queries.
3. C.partition pruning — The primary cost driver for on-demand scan-priced warehouses; minimized via partition pruning, clustering, and selecting only needed columns.
4. D.partition pruning — When a query exceeds available memory and writes intermediate data to local or remote disk, a major performance and cost red flag.

Answer + AI explanation with Pro

1. A.Physically co-locating rows by frequently filtered columns so the engine reads fewer micro-partitions or blocks for selective queries.
2. B.The primary cost driver in serverless query engines (BigQuery, Athena), reduced by partitioning, clustering, and selecting fewer columns.
3. C.Matching warehouse or slot capacity to workload so jobs finish quickly without paying for idle or oversized compute.
4. D.Tagging and tracking compute spend by team, query, or workload so chargeback and optimization can target the biggest cost drivers.

Answer + AI explanation with Pro

1. A.result cache
2. B.right-sizing warehouse
3. C.clustering
4. D.data egress cost

Answer + AI explanation with Pro

1. A.clustering — An uneven distribution of values across partitions or keys that overloads a few tasks, creating stragglers that dominate job runtime.
2. B.clustering — Physically co-locating rows by frequently filtered columns so the engine reads fewer micro-partitions or blocks for selective queries.
3. C.clustering — A join that ships a small table in full to every executor so each partition of the large table joins locally, avoiding a shuffle.
4. D.clustering — Refreshing only changed partitions of a derived table instead of rebuilding it fully, the main lever for cheap, frequent transformations.

Answer + AI explanation with Pro

1. A.A join that ships a small table in full to every executor so each partition of the large table joins locally, avoiding a shuffle.
2. B.The expensive redistribution of data across the cluster by key (for joins, group-bys, or sorts) that moves records over the network.
3. C.Refreshing only changed partitions of a derived table instead of rebuilding it fully, the main lever for cheap, frequent transformations.
4. D.Configuring compute to pause after idle time so you stop paying for unused warehouse runtime, a key cost control.

Answer + AI explanation with Pro

1. A.auto-suspend
2. B.result cache
3. C.partition pruning
4. D.clustering

Answer + AI explanation with Pro

1. A.auto-suspend — Configuring compute to pause after idle time so you stop paying for unused warehouse runtime, a key cost control.
2. B.auto-suspend — The primary cost driver for on-demand scan-priced warehouses; minimized via partition pruning, clustering, and selecting only needed columns.
3. C.auto-suspend — Refreshing only changed partitions of a derived table instead of rebuilding it fully, the main lever for cheap, frequent transformations.
4. D.auto-suspend — Choosing reserved capacity (lower unit cost, fixed commitment) versus pay-per-use (flexible, higher unit cost) based on workload predictability.

Answer + AI explanation with Pro

1. A.Refreshing only changed partitions of a derived table instead of rebuilding it fully, the main lever for cheap, frequent transformations.
2. B.An uneven distribution of values across partitions or keys that overloads a few tasks, creating stragglers that dominate job runtime.
3. C.Skipping entire partitions that cannot match a query's filter, the primary way partitioning reduces scanned data and cost.
4. D.When a query exceeds available memory and writes intermediate data to local or remote disk, a major performance and cost red flag.

Answer + AI explanation with Pro

1. A.shuffle
2. B.spilling
3. C.partition pruning
4. D.data skew

Answer + AI explanation with Pro

1. A.spilling — When a query exceeds available memory and writes intermediate data to local or remote disk, a major performance and cost red flag.
2. B.spilling — Refreshing only changed partitions of a derived table instead of rebuilding it fully, the main lever for cheap, frequent transformations.
3. C.spilling — Skipping entire partitions that cannot match a query's filter, the primary way partitioning reduces scanned data and cost.
4. D.spilling — Reading only needed columns instead of all columns, cutting bytes scanned and improving performance in columnar engines.

Answer + AI explanation with Pro

1. A.Refreshing only changed partitions of a derived table instead of rebuilding it fully, the main lever for cheap, frequent transformations.
2. B.Reading only needed columns instead of all columns, cutting bytes scanned and improving performance in columnar engines.
3. C.Choosing between recomputing a query each time (cheaper storage, costlier compute) and precomputing it as a table or MV (faster reads, refresh cost).
4. D.An uneven distribution of values across partitions or keys that overloads a few tasks, creating stragglers that dominate job runtime.

Answer + AI explanation with Pro

1. A.auto-suspend
2. B.materialization tradeoff
3. C.right-sizing warehouse
4. D.partition pruning

Answer + AI explanation with Pro

1. A.materialization tradeoff — Reading only needed columns instead of all columns, cutting bytes scanned and improving performance in columnar engines.
2. B.materialization tradeoff — Choosing between recomputing a query each time (cheaper storage, costlier compute) and precomputing it as a table or MV (faster reads, refresh cost).
3. C.materialization tradeoff — Skipping entire partitions that cannot match a query's filter, the primary way partitioning reduces scanned data and cost.
4. D.materialization tradeoff — A performance issue where many tiny files force excessive metadata and task overhead; compacting into larger files restores efficient scans.

Answer + AI explanation with Pro

1. A.Charges incurred when moving data out of a cloud region or provider, a factor in multi-cloud and cross-region pipeline design.
2. B.Tagging and tracking compute spend by team, query, or workload so chargeback and optimization can target the biggest cost drivers.
3. C.A cache that returns a previously computed query result instantly when the same query runs again over unchanged data, scanning zero bytes.
4. D.An uneven distribution of values across partitions or keys that overloads a few tasks, creating stragglers that dominate job runtime.

Answer + AI explanation with Pro

1. A.query cost attribution
2. B.spilling
3. C.broadcast join
4. D.shuffle

Answer + AI explanation with Pro

1. A.query cost attribution — A cache that returns a previously computed query result instantly when the same query runs again over unchanged data, scanning zero bytes.
2. B.query cost attribution — Tagging and tracking compute spend by team, query, or workload so chargeback and optimization can target the biggest cost drivers.
3. C.query cost attribution — A warehouse setting that idles compute after a period of inactivity so you stop paying for an unused cluster.
4. D.query cost attribution — Refreshing only changed partitions of a derived table instead of rebuilding it fully, the main lever for cheap, frequent transformations.

Answer + AI explanation with Pro

1. A.Tagging and tracking compute spend by team, query, or workload so chargeback and optimization can target the biggest cost drivers.
2. B.Skipping entire partitions that cannot match a query's filter, the primary way partitioning reduces scanned data and cost.
3. C.Matching warehouse or slot capacity to workload so jobs finish quickly without paying for idle or oversized compute.
4. D.A join that ships a small table in full to every executor so each partition of the large table joins locally, avoiding a shuffle.

Answer + AI explanation with Pro

1. A.clustering
2. B.right-sizing warehouse
3. C.spilling
4. D.materialization tradeoff

Answer + AI explanation with Pro

1. A.right-sizing warehouse — Matching warehouse or slot capacity to workload so jobs finish quickly without paying for idle or oversized compute.
2. B.right-sizing warehouse — Physically co-locating rows by frequently filtered columns so the engine reads fewer micro-partitions or blocks for selective queries.
3. C.right-sizing warehouse — A join that ships a small table in full to every executor so each partition of the large table joins locally, avoiding a shuffle.
4. D.right-sizing warehouse — The primary cost driver for on-demand scan-priced warehouses; minimized via partition pruning, clustering, and selecting only needed columns.

Answer + AI explanation with Pro

1. A.The expensive redistribution of data across the cluster by key (for joins, group-bys, or sorts) that moves records over the network.
2. B.Configuring compute to pause after idle time so you stop paying for unused warehouse runtime, a key cost control.
3. C.Charges incurred when moving data out of a cloud region or provider, a factor in multi-cloud and cross-region pipeline design.
4. D.A cache that returns a previously computed query result instantly when the same query runs again over unchanged data, scanning zero bytes.

Answer + AI explanation with Pro

1. A.data egress cost
2. B.small-files problem
3. C.clustering
4. D.spill

Answer + AI explanation with Pro

1. A.data egress cost — Matching warehouse or slot capacity to workload so jobs finish quickly without paying for idle or oversized compute.
2. B.data egress cost — Charges incurred when moving data out of a cloud region or provider, a factor in multi-cloud and cross-region pipeline design.
3. C.data egress cost — The primary cost driver for on-demand scan-priced warehouses; minimized via partition pruning, clustering, and selecting only needed columns.
4. D.data egress cost — When an operation's working set exceeds memory and is written to disk, slowing execution; reduced by tuning partitions or adding memory.

Answer + AI explanation with Pro

1. A.A performance issue where many tiny files force excessive metadata and task overhead; compacting into larger files restores efficient scans.
2. B.A join that ships a small table in full to every executor so each partition of the large table joins locally, avoiding a shuffle.
3. C.Tagging and tracking compute spend by team, query, or workload so chargeback and optimization can target the biggest cost drivers.
4. D.The primary cost driver in serverless query engines (BigQuery, Athena), reduced by partitioning, clustering, and selecting fewer columns.

Answer + AI explanation with Pro

1. A.bytes scanned
2. B.SELECT star avoidance
3. C.data skew
4. D.commitment vs on-demand

Answer + AI explanation with Pro

1. A.bytes scanned — When a query exceeds available memory and writes intermediate data to local or remote disk, a major performance and cost red flag.
2. B.bytes scanned — Charges incurred when moving data out of a cloud region or provider, a factor in multi-cloud and cross-region pipeline design.
3. C.bytes scanned — A cache that returns a previously computed query result instantly when the same query runs again over unchanged data, scanning zero bytes.
4. D.bytes scanned — The primary cost driver in serverless query engines (BigQuery, Athena), reduced by partitioning, clustering, and selecting fewer columns.

Answer + AI explanation with Pro

1. A.Physically co-locating rows by frequently filtered columns so the engine reads fewer micro-partitions or blocks for selective queries.
2. B.A warehouse setting that idles compute after a period of inactivity so you stop paying for an unused cluster.
3. C.Reading only needed columns instead of all columns, cutting bytes scanned and improving performance in columnar engines.
4. D.Charges incurred when moving data out of a cloud region or provider, a factor in multi-cloud and cross-region pipeline design.

Answer + AI explanation with Pro

1. A.broadcast join
2. B.query cost attribution
3. C.commitment vs on-demand
4. D.SELECT star avoidance

Answer + AI explanation with Pro

1. A.SELECT star avoidance — A performance issue where many tiny files force excessive metadata and task overhead; compacting into larger files restores efficient scans.
2. B.SELECT star avoidance — Reading only needed columns instead of all columns, cutting bytes scanned and improving performance in columnar engines.
3. C.SELECT star avoidance — Tagging and tracking compute spend by team, query, or workload so chargeback and optimization can target the biggest cost drivers.
4. D.SELECT star avoidance — Choosing between recomputing a query each time (cheaper storage, costlier compute) and precomputing it as a table or MV (faster reads, refresh cost).

Answer + AI explanation with Pro