title: scheduled query to partitioned and clustered table
body: <p>I have created a BQ table with time partitioned (day/ timestamp) and also clustering data using region and location field.</p>
  <p>I have created a daily scheduled query using the query that I ran to create the initial records.  The scheduled query was set to append data to the previously created partitioned and clustered table.  However, scheduled query failed to run with a message below:</p>
  <blockquote>
  <p>Incompatible table partitioning specification. Destination table
  exists with partitioning specification
  interval(type:DAY,field:timestamp) clustering(location,region), but
  transfer target partitioning specification is
  interval(type:DAY,field:timestamp). Please retry after updating either
  the destination table or the transfer partitioning specification.</p>
  </blockquote>
  <p>The simplified version of the query is below:</p>
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>CREATE TEMP FUNCTION
    isYesterday(recordTimestamp TIMESTAMP) AS ( DATE(recordTimestamp) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) );
  
  WITH
    daily AS (
    SELECT
      TIMESTAMP_TRUNC(timestamp,day) day,
      location,
      direction,
      region,
      MAX(metric1) metric1,
      MAX(metric2) metric2
    FROM (
      SELECT
        location,
        timestamp,
          direction,
          CASE
          WHEN area&gt;0 AND area&lt;20 THEN "region1"
          WHEN area&gt;10
        AND area&lt;20 THEN "region2"
          WHEN area&gt;30 AND area&lt;40 THEN "Region3"
          WHEN area=61 THEN "Region4"
          WHEN area=65 THEN "Region5"
          WHEN area=76 THEN "Region6"
          WHEN area&gt;89 AND area&lt;100 THEN "Region7"
      END
        AS region,
        
        SUM(terminalcount0) metric1,
        SUM(terminalcount1) metric2
      FROM
        `statistic`
      WHERE
    isYesterday(timestamp)
  --      DATE(timestamp) BETWEEN '2020-07-01'
  --      AND DATE_SUB(CURRENT_DATE(),INTERVAL 1 day)
          GROUP BY
        location,
        timestamp,
        direction,
        region )
    GROUP BY
      day,
      location,
      direction,
      region)
  SELECT
    day as timestamp,
    location,
    direction,
    SUM(metric1) metric1,
    SUM(metric2) metric2
  FROM
    daily
  GROUP BY
    day,
    location,
    direction,
    region
    
    </code></pre>
  </div>
  </div>
  </p>
  <p>How do I fix this so that scheduled query can run and save the results to the destination table regularly?   Or is it really the scheduled query to the existing clustered table not supported by GCP?</p>