> ## Documentation Index
> Fetch the complete documentation index at: https://docs2.growthbook.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Query Optimization

> Learn about different techniques and settings to optimize SQL queries in GrowthBook

export const CommercialFeature = ({feature, description}) => {
  const commercialFeatures = {
    "adv-presentations": {
      plan: "enterprise",
      displayName: "Adv Presentations"
    },
    "advanced-permissions": {
      plan: "pro",
      displayName: "Advanced Permissions"
    },
    "ai-byok": {
      plan: "enterprise",
      displayName: "Ai Byok"
    },
    "ai-suggestions": {
      plan: "enterprise",
      displayName: "AI Suggestions"
    },
    archetypes: {
      plan: "pro",
      displayName: "Archetypes"
    },
    "audit-logging": {
      plan: "enterprise",
      displayName: "Audit Logging"
    },
    "cloud-proxy": {
      plan: "pro",
      displayName: "Cloud Proxy"
    },
    "code-references": {
      plan: "pro",
      displayName: "Code References"
    },
    "contextual-bandits": {
      plan: "enterprise",
      displayName: "Contextual Bandits"
    },
    "custom-hooks": {
      plan: "enterprise",
      displayName: "Custom Hooks"
    },
    "custom-launch-checklist": {
      plan: "enterprise",
      displayName: "Custom Launch Checklist"
    },
    "custom-markdown": {
      plan: "enterprise",
      displayName: "Custom Markdown"
    },
    "custom-metadata": {
      plan: "enterprise",
      displayName: "Custom Metadata"
    },
    "custom-roles": {
      plan: "enterprise",
      displayName: "Custom Roles"
    },
    dashboards: {
      plan: "enterprise",
      displayName: "Dashboards"
    },
    "decision-framework": {
      plan: "pro",
      displayName: "Decision Framework"
    },
    "encrypt-features-endpoint": {
      plan: "pro",
      displayName: "Encrypt Features Endpoint"
    },
    "environment-inheritance": {
      plan: "enterprise",
      displayName: "Environment Inheritance"
    },
    "events-forwarder": {
      plan: "pro",
      displayName: "Events Forwarder"
    },
    "experiment-impact": {
      plan: "enterprise",
      displayName: "Experiment Impact"
    },
    "feature-configs": {
      plan: "enterprise",
      displayName: "Feature Configs"
    },
    "funnel-metrics": {
      plan: "pro",
      displayName: "Funnel Metrics"
    },
    "hash-secure-attributes": {
      plan: "pro",
      displayName: "Hash Secure Attributes"
    },
    "historical-power": {
      plan: "pro",
      displayName: "Historical Power"
    },
    holdouts: {
      plan: "enterprise",
      displayName: "Holdouts"
    },
    "incremental-refresh": {
      plan: "enterprise",
      displayName: "Incremental Refresh"
    },
    "json-validation": {
      plan: "enterprise",
      displayName: "JSON Validation"
    },
    "large-saved-groups": {
      plan: "enterprise",
      displayName: "Large Saved Groups"
    },
    learnings: {
      plan: "enterprise",
      displayName: "Learnings"
    },
    livechat: {
      plan: "pro",
      displayName: "Livechat"
    },
    "manage-official-resources": {
      plan: "enterprise",
      displayName: "Manage Official Resources"
    },
    "metric-correlations": {
      plan: "enterprise",
      displayName: "Metric Correlations"
    },
    "metric-effects": {
      plan: "enterprise",
      displayName: "Metric Effects"
    },
    "metric-groups": {
      plan: "enterprise",
      displayName: "Metric Groups"
    },
    "metric-populations": {
      plan: "pro",
      displayName: "Metric Populations"
    },
    "metric-slices": {
      plan: "enterprise",
      displayName: "Metric Slices"
    },
    "multi-armed-bandits": {
      plan: "pro",
      displayName: "Multi Armed Bandits"
    },
    "multi-metric-queries": {
      plan: "enterprise",
      displayName: "Multi Metric Queries"
    },
    "multi-org": {
      plan: "enterprise",
      displayName: "Multi Org"
    },
    "multiple-sdk-webhooks": {
      plan: "pro",
      displayName: "Multiple Sdk Webhooks"
    },
    "no-access-role": {
      plan: "enterprise",
      displayName: "No Access Role"
    },
    "override-metrics": {
      plan: "pro",
      displayName: "Override Metrics"
    },
    "pipeline-mode": {
      plan: "enterprise",
      displayName: "Pipeline Mode"
    },
    "post-stratification": {
      plan: "enterprise",
      displayName: "Post Stratification"
    },
    "precomputed-dimensions": {
      plan: "pro",
      displayName: "Precomputed Dimensions"
    },
    "prerequisite-targeting": {
      plan: "enterprise",
      displayName: "Prerequisite Targeting"
    },
    prerequisites: {
      plan: "pro",
      displayName: "Prerequisites"
    },
    "product-analytics-dashboards": {
      plan: "pro",
      displayName: "Product Analytics Dashboards"
    },
    "project-admin-role": {
      plan: "enterprise",
      displayName: "Project Admin Role"
    },
    "quantile-metrics": {
      plan: "pro",
      displayName: "Quantile Metrics"
    },
    "ramp-schedules": {
      plan: "pro",
      displayName: "Ramp Schedules"
    },
    redirects: {
      plan: "pro",
      displayName: "Redirects"
    },
    "regression-adjustment": {
      plan: "pro",
      displayName: "CUPED"
    },
    releases: {
      plan: "enterprise",
      displayName: "Releases"
    },
    "remote-evaluation": {
      plan: "pro",
      displayName: "Remote Evaluation"
    },
    "require-approvals": {
      plan: "enterprise",
      displayName: "Require Approvals"
    },
    "require-project-for-features-setting": {
      plan: "enterprise",
      displayName: "Require Project For Features Setting"
    },
    "require-project-for-sdk-connections-setting": {
      plan: "enterprise",
      displayName: "Require Project For Sdk Connections Setting"
    },
    "retention-metrics": {
      plan: "pro",
      displayName: "Retention Metrics"
    },
    "safe-rollout": {
      plan: "pro",
      displayName: "Safe Rollout"
    },
    saveSqlExplorerQueries: {
      plan: "pro",
      displayName: "Save SQL Explorer Queries"
    },
    "schedule-feature-flag": {
      plan: "pro",
      displayName: "Schedule Feature Flag"
    },
    "scheduled-revisions": {
      plan: "enterprise",
      displayName: "Scheduled Revisions"
    },
    scim: {
      plan: "enterprise",
      displayName: "SCIM"
    },
    "sequential-testing": {
      plan: "pro",
      displayName: "Sequential Testing"
    },
    "share-product-analytics-dashboards": {
      plan: "enterprise",
      displayName: "Share Product Analytics Dashboards"
    },
    simulate: {
      plan: "pro",
      displayName: "Simulate"
    },
    sso: {
      plan: "enterprise",
      displayName: "SSO"
    },
    "sticky-bucketing": {
      plan: "pro",
      displayName: "Sticky Bucketing"
    },
    teams: {
      plan: "enterprise",
      displayName: "Teams"
    },
    templates: {
      plan: "enterprise",
      displayName: "Templates"
    },
    "unlimited-managed-warehouse-usage": {
      plan: "pro",
      displayName: "Unlimited Managed Warehouse Usage"
    },
    "visual-editor": {
      plan: "pro",
      displayName: "Visual Editor"
    }
  };
  const {plan, displayName} = commercialFeatures[feature];
  const isEnterprise = plan === "enterprise";
  const defaultDescription = isEnterprise ? "is available on Enterprise plans." : "is available on Pro and Enterprise plans.";
  const planLabel = isEnterprise ? "Enterprise" : "Pro";
  const containerStyle = isEnterprise ? {
    backgroundColor: "color-mix(in srgb, var(--indigo-a3) 60%, transparent)"
  } : {
    backgroundColor: "color-mix(in srgb, var(--amber-a3) 60%, transparent)"
  };
  const badgeStyle = isEnterprise ? {
    boxShadow: "inset 0 0 0 1px var(--indigo-a8)",
    color: "var(--indigo-a11)"
  } : {
    boxShadow: "inset 0 0 0 1px var(--amber-a8)",
    color: "var(--amber-a11)"
  };
  return <div className="flex items-start gap-2 mb-4 p-3 text-sm leading-[1.4] rounded-lg" style={containerStyle} role="note">
      <span className="inline-flex items-center justify-center px-1.5 h-5 text-xs font-medium rounded-full shrink-0 leading-none" style={badgeStyle}>
        {planLabel}
      </span>
      <div className="flex-1 leading-[1.3]">
        <strong className="font-semibold">{displayName}</strong>{" "}
        {defaultDescription} {description}
      </div>
    </div>;
};

GrowthBook is designed to run efficient database queries out-of-the-box, but for large datasets or complex metrics, there are a few settings and techniques you can use to optimize your queries.

## SQL Template Variables

GrowthBook runs all SQL through a templating engine (Handlebars) to allow for dynamic SQL generation. This lets you apply advanced query optimization.

The most common is adding a date filter. For example:

```sql theme={null}
SELECT
  timestamp,
  user_id,
  amount
FROM purchases
WHERE
  timestamp BETWEEN '{{startDate}}' AND '{{endDate}}'
```

Read more about [SQL Templates](/app/sql-templates) and the different variables that are available.

## Fact Tables

Fact Tables are a shared SQL definition that can be re-used across multiple metrics. For example, a "Purchases" fact table could be used for both a "Total Revenue" metric and a "Items per Order" metric.

If you are still using legacy metrics (where each metric has its own separate SQL definition), you are missing out on important query optimizations and the newest features in GrowthBook.

Read more about [Fact Tables](/app/metrics) and how to [convert legacy metrics to Fact Tables](/app/metrics/legacy#migrating-legacy-metrics-to-fact-tables).

## Fact Table Query Optimization

<CommercialFeature feature="multi-metric-queries" />

Fact Table Query Optimization enables faster, more efficient queries.

If multiple metrics from the same Fact Table are added to an experiment, they will be combined into a single SQL query. For data sourcees with usage-based billing, this can result in dramatic cost savings.

There are some restrictions that limit when this optimization can be performed:

* Ratio metrics where the numerator and denominator are part of different Fact Tables are always excluded from this optimization
* If `Exclude In-Progress Conversions` is set for an experiment, optimization is disabled for all metrics
* If you are using MySQL and a metric has percentile capping, it will be excluded from optimization

In all other cases, this optimization is enabled by default. It can be disabled under **Settings → General → Experiment Settings**. When disabled, a separate SQL query will always be run for every individual metric.

## Data Pipeline Mode

<CommercialFeature feature="pipeline-mode" />

Data Pipeline Mode reduces the amount of duplicate data your warehouse needs to scan.

When enabled, GrowthBook will write some intermediate tables back to your warehouse with short retention and re-use those across all of the metric queries in an experiment.

Currently, this is limited to BigQuery, Snowflake, and Databricks, but we are working on adding support for other data sources soon.

Read more about [Data Pipeline Mode](/app/data-pipeline).

## Materialized Views

If your metric definitions are complex and involve multiple joins or subqueries, you may want to consider creating a materialized view in your warehouse.

Setting up materialized views differs by warehouse, so consult the documentation for your specific warehouse for more information.

You can also use a tool like [dbt](https://www.getdbt.com/) to create computed tables that are automatically refreshed on a schedule.

## Pre-Aggregated Tables

Pre-aggregated tables include a GROUP BY in the data pipeline to compress raw event-level data down to fewer rows. This is usually done when querying raw data directly is prohibitively expensive.

GrowthBook supports pre-aggregated tables as long as they satisfy two requirements:

* Must be grouped by both user and date
* Pre-aggregated columns can only be basic sums or counts. No averages, percentiles, count distinct, or complex derived formulas that break statistical assumptions.

Using pre-aggregated tables for experimentation comes with additional complexities and downsides, so we highly recommend sticking with event-level data whenever possible.

Read more about this approach with some [examples and best practices](/app/metrics/examples#pre-aggregated-tables).