Snowflake warehouse optimization

Concept 1: Cost vs Performance

Each time you increase the size of the warehouse by one level, you are doubling the CPU and RAM of your compute instance. Correspondingly, you are also doubling the price via the credits you consume.

While the price is doubled, in an ideal world on the proper workload/query, the query time is reduced by half due to the doubling of the compute instance’s resources. Therefore, in this situation, you would pay the same price on either warehouse, but the bigger warehouse would halve the run-time of the query, making it the more efficient choice.

Eventually, if you keep upsizing the warehouse, you’ll reach a point of diminishing returns, where the query run-time may be only slightly reduced while the costs increase disproportionately faster. Here’s a select.dev cost vs performance diagram illustrating this point [1].

Concept 2: Defining Warehouse efficiency

Warehouse Efficiency comes in two dimensions: cost and performance. Note that ‘warehouse efficiency’ is a different concept than model efficiency.

Because there are two dimensions, ‘warehouse efficiency’ doesn’t have a clear definition; it needs to be decided based on how much you value ‘cost’ versus ‘performance.’

In our case, we want one of these two outcomes when using a different warehouse:

• maintain existing runtimes while reducing costs
• reduce runtimes, without increasing cost drastically

Based on the requirements above, we will define warehouse efficiency clearly as follows:

A warehouse is efficient when compared to the next-size smaller warehouse; the query run-time is reduced by 40% or more.

Explanation: As discussed in the previous section, for the next-size bigger warehouse to break even, it needs to run 2x as fast (query run-time is reduced by 50%) to compensate for its 2x price increase.

In our definition, we require run-time to be reduced by at least 40%, which translates to a maximum increase in cost of 20%. The reason why we don’t require a 50% reduction in run-time (and break-even in costs) is the following:

• we need our performance to stay close to existing dbt run-times
• we want to work iteratively, which means setting a more realistic goal that allows some wiggle-room and then adjusting the goalpost once we learn more.

That might sound a bit vague, so here are some hypothetical examples:

Hypothetical Examples

Example 1

The run-time has decreased by 50%, from 1 hour -> 30 minutes. In this case, it’s a no-brainer to increase the warehouse size:

Example 2

The run-time has decreased by 40% from 1 hour -> 36 minutes. Meanwhile, the cost has increased by 20% from $2 to $2.40:

In this case, upsizing the warehouse is no longer a no-brainer. It now depends on how much you value cost vs. query run-time.

If you only cared about cost, you would stick with the X-small; if you only cared about speed, you would go with the Small.

In our case, we want to find a balance between both, so we use the previously established guidelines:

A warehouse is efficient when compared to the next-size smaller warehouse, the query run-time is 40% less.

In this case, since the query run-time has been reduced by 40%, it’s exactly on the threshold of being considered ’efficient’, and we should choose the ‘S’ warehouse.

Example 3

Example 3, run-time has decreased by 20% from 1 hour -> 48 minutes. Meanwhile, the cost has increased by 60%, from $2 to $3.20:

In this case, the query would be considered ‘inefficient’ because the runtime has only been reduced by 20%. For it to be considered ’efficient’, the runtime needs to be reduced by 40% or more. Therefore, the ‘XS’ warehouse should be chosen.

We have discussed in theory what an ‘optimal’ warehouse is, but how do we actually choose the correct warehouse for each model?

Here are the steps:

1. run an explain plan to figure out how many partitions the table has
2. Based on the number of partitions, use select.dev # of partitions diagram to estimate what warehouse to start with
3. Run the query in dbt with the estimated warehouse from the previous step
4. Check the below ‘Benchmarking heuristics’ section to see if you can immediately assign a warehouse; if not, continue
5. Using this Google Sheet record the query time
6. Upsizing warehouse(s)
   • Now, rerun the query with the next highest warehouse
   • Record the results in the Google Sheet again
   • Stop once the cost is increasing faster than the performance

In the following sections, we’ll look at the following:

• Why the benchmarking needs to happen in dbt
• We’ll look at two examples of right-sizing warehouses by following these steps:
  1. simple query: gitlab_dotcom_deployment_todo_dedupe_source
  2. more complicated query with more partitions: prep_ci_stage

{% if is_incremental() %}
  {{ config(
      warehouse='smaller_warehouse'  # Use this warehouse for incremental runs
  ) }}
{% else %}
  {{ config(
      warehouse='larger_warehouse'  # Use this warehouse for full refresh runs
  ) }}
{% endif %}

SELECT *
FROM my_table

EXPLAIN
SELECT
  *
FROM
  "RAW".tap_postgres.GITLAB_DB_TODOS
QUALIFY ROW_NUMBER() OVER ( PARTITION BY id ORDER BY _uploaded_at DESC) = 1;

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

EXPLAIN
WITH
  dim_project AS (
    SELECT
      *
    FROM
      "PROD".common.dim_project
  ),
  dim_ci_pipeline AS (
    SELECT
      *
    FROM
      "PROD".common.dim_ci_pipeline
  ),
  dim_namespace_plan_hist AS (
    SELECT
      *
    FROM
      "PROD".common.dim_namespace_plan_hist
  ),
  dim_date AS (
    SELECT
      *
    FROM
      "PROD".common.dim_date
  ),
  ci_stages AS (
    SELECT
      *
    FROM
      "PREP".gitlab_dotcom.gitlab_dotcom_ci_stages_dedupe_source
    WHERE
      created_at IS NOT NULL
  ),
  joined AS (
    SELECT
      ci_stages.id AS dim_ci_stage_id,
      IFNULL(dim_project.dim_project_id, -1) AS dim_project_id,
      IFNULL(dim_ci_pipeline.dim_ci_pipeline_id, -1) AS dim_ci_pipeline_id,
      IFNULL(dim_namespace_plan_hist.dim_plan_id, 34) AS dim_plan_id,
      IFNULL(dim_namespace_plan_hist.dim_namespace_id, -1) AS ultimate_parent_namespace_id,
      dim_date.date_id AS created_date_id,
      ci_stages.created_at::TIMESTAMP AS created_at,
      ci_stages.updated_at::TIMESTAMP AS updated_at,
      ci_stages.name AS ci_stage_name,
      ci_stages.status AS ci_stage_status,
      ci_stages.lock_version AS lock_version,
      ci_stages.position AS POSITION
    FROM
      ci_stages
      LEFT JOIN dim_project ON ci_stages.project_id = dim_project.dim_project_id
      LEFT JOIN dim_namespace_plan_hist ON dim_project.ultimate_parent_namespace_id = dim_namespace_plan_hist.dim_namespace_id
      AND ci_stages.created_at >= dim_namespace_plan_hist.valid_from
      AND ci_stages.created_at < COALESCE(dim_namespace_plan_hist.valid_to, '2099-01-01')
      LEFT JOIN dim_ci_pipeline ON ci_stages.pipeline_id = dim_ci_pipeline.dim_ci_pipeline_id
      INNER JOIN dim_date ON TO_DATE(ci_stages.created_at) = dim_date.date_day
  )
SELECT
  *,
  '@mpeychet_'::VARCHAR AS created_by,
  '@chrissharp'::VARCHAR AS updated_by,
  '2021-06-29'::DATE AS model_created_date,
  '2022-06-01'::DATE AS model_updated_date,
  CURRENT_TIMESTAMP() AS dbt_updated_at,
  CURRENT_TIMESTAMP() AS dbt_created_at
FROM
  joined;

ALTER SESSION SET USE_CACHED_RESULT = FALSE;

As mentioned in the Prospectus section, a benchmark was done right-sizing all the models in the non-product dbt task, and per this benchmarking issue. It shows that right-sizing the smallest models does NOT lead to significant cost-savings (if at all), even though these type of models make up more than 90% of the models within the run.

Therefore, in the context of the dbt.py DAG, benchmarking would be most valuable for the most expensive models. We can use the query_attribution Snowflake table to find these models.

A small caveat to the above is that we also run dbt models in other environments outside of the production dbt.py DAG, such as:

• locally
• CI jobs

In these latter environments, it may make sense to right-size the warehouse for every model, even if the models are small. In these cases, the method to right-size doesn’t need to be as precise, i.e., having to compare against multiple warehouse sizes. Instead, one could immediately assign an estimated warehouse per model based on the number of partitions scanned by the model.

To summarize, the priority for right-sizing warehouses should be the largest models that take the longest to run and consume the most credits since these models make up a disproportionate amount of the total credits consumed.