[Bug] BigQuery: DATETIME_TRUNC over time dimensions defeats partition pruning and hard-fails on require_partition_filter tables #2078

Is this a new bug in metricflow?

• I believe this is a new bug in metricflow
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

On BigQuery, MetricFlow renders time-dimension truncation as DATETIME_TRUNC(<column>, <grain>) in both the SELECT projection and the WHERE clause. BigQuery's partition pruner cannot reason through DATETIME_TRUNC over a TIMESTAMP (or DATE) partition column — the implicit cast to DATETIME makes the predicate ineligible for partition elimination.

This has two consequences:

1. Silent cost: every MetricFlow query against a time-unit-partitioned table full-scans it, even when the query is constrained to a narrow date range. Nothing fails; you just pay for it.
2. Hard failure: tables configured with require_partition_filter = true cannot be queried by MetricFlow at all.

For a metric defined over a mart partitioned on created_at (TIMESTAMP, day granularity) with require_partition_filter = true:

mf query --metrics ca_usage --group-by metric_time__day \
  --start-time 2026-05-01 --end-time 2026-05-31

renders (essentials):

SELECT
  DATETIME_TRUNC(created_at, day) AS metric_time__day,
  SUM(some_field) AS answer
FROM `<project>.<dataset>.SOME_TABLE`
WHERE DATETIME_TRUNC(created_at, day) BETWEEN '2026-05-01' AND '2026-05-31'
GROUP BY metric_time__day

and fails with:

Database Error
  Cannot query over table '<project>.<dataset>.SOME_TABLE' without a
  filter over column(s) 'created_at' that can be used for partition elimination

The semantic configuration itself is valid: mf validate-configs passes, mf list metrics shows the metric, and the generated SQL is logically correct — it runs fine against a table without the partition-filter requirement (it just full-scans).

Expected Behavior

The rendered SQL should be partition-pruner-eligible. BigQuery's pruner accepts the truncation function that matches the column type: TIMESTAMP_TRUNC over a TIMESTAMP partition column, DATE_TRUNC over DATE, DATETIME_TRUNC over DATETIME (BigQuery docs: querying partitioned tables). What it rejects is the implicit type cast that DATETIME_TRUNC introduces over a TIMESTAMP or DATE column.

A one-line fix for the TIMESTAMP-column case already exists in PR #1765 (one approval, awaiting further review).

We verified this directly against the same table:

• WHERE created_at >= TIMESTAMP '2026-05-01' AND created_at < TIMESTAMP '2026-06-01' → prunes correctly.
• On a DATE-repartitioned variant of the same mart: WHERE created_at_date BETWEEN DATE '2026-05-01' AND DATE '2026-05-31' and DATE_TRUNC(created_at_date, MONTH) predicates → both prune correctly.
• MetricFlow's DATETIME_TRUNC(created_at_date, day) over that same DATE-partitioned variant → rejected, same error. The problem is the function/type mismatch, not the column type — repartitioning does not work around it.

Steps To Reproduce

1. Any BigQuery table partitioned on a TIMESTAMP column with require_partition_filter = true (in dbt: partition_by={"field": ..., "data_type": "timestamp", "granularity": "day"}, require_partition_filter=true).
2. Define a semantic model over it with a time dimension on the partition column, plus any simple metric.
3. mf query --metrics <metric> --group-by metric_time__day --start-time <d1> --end-time <d2> → the error above.
4. Remove require_partition_filter → query succeeds but full-scans (compare bytes processed against the bare-predicate equivalent).

Relevant log output

Environment

OS: macOS 26.5.1
Python: 3.12.4
dbt-core: 1.11.7
dbt-bigquery: 1.11.1
dbt-metricflow: 0.13.0
metricflow: 0.211.0

Which database are you using?

bigquery

Additional Context

There is an approved PR #1765 that will resolve this issue.

We tried some workarounds to this but none really addressed the root issue:

• --start-time/--end-time → renders DATETIME_TRUNC(...) BETWEEN ..., rejected.
• --where "created_at >= TIMESTAMP(...)" (raw column) → Unrecognized name: created_at; the user WHERE is applied in an outer SELECT that only exposes the aliased projection, too late to help the pruner.
• --where "{{ TimeDimension('metric_time', 'day') }} BETWEEN ..." → renders the same DATETIME_TRUNC wrapping.
• Metric-level filter: and measure-level filter (object form of type_params.measure) → silently absent from the rendered SQL in metricflow 0.211.0.
• The dbt discourse metrics-partition-keys pattern → pre-MetricFlow metrics syntax; that lever no longer exists.

Remaining mitigations are all table-side — dropping require_partition_filter or exposing passthrough views without it — i.e., disabling a safety control specifically so MetricFlow can query the table.