Why a full table scan when joining a clustered column to another table?

[uiltjesrups]

Hi, I have a question about the amount of bytes processed when joining a clustered column with another table. I've noticed that it still does a full table scan, which I wasn't expecting. If I filter against a literal value, it seems to work as expected.

I've noticed this because of the high cost of doing deletes based on a join, e.g.

DELETE foo.bar WHERE EXISTS (SELECT 1 FROM CTE WHERE bar.id = baz.id)

does a full table scan, even if `id` is a clustered column.

But the same applies to SELECT queries.

Example:

CREATE SCHEMA IF NOT EXISTS `foo`;
CREATE TABLE IF NOT EXISTS
  `foo.bar` ( id STRING )
CLUSTER BY
  id;
INSERT INTO
  `foo.bar` (id)
SELECT
  GENERATE_UUID() AS id
FROM
  UNNEST(GENERATE_ARRAY(1, 1024 * 1024 *10/ 36)); -- insert approximately 10MB of data
INSERT INTO
  `foo.bar` (id)
VALUES
  ('my_id');

-- bytes processed: 7B, as expected
SELECT
  *
FROM
  foo.bar
WHERE
  id = 'my_id';

-- bytes processed: 10.56 MB, why?
WITH
  cte AS (
  SELECT
    'my_id' AS id)
SELECT
  *
FROM
  foo.bar
WHERE
  EXISTS (
  SELECT
    1
  FROM
    CTE
  WHERE
    bar.id = cte.id);

What is the reason for this?

Is there anything I can do to make my DELETE queries that depend on other tables process less data, i.e. cheaper?

Thanks!