Bugzilla

1) You could run a BQ query to pull down the list of existing document_ids. This would require having GCP creds available for the service and using GCP client libraries. So it could be done, but is probably some effort.
2) The ingestion machinery really is not set up to handle deletion outside of the stated deduplication efforts.
3) Yes, it's reasonable to write a query that deduplicates. For example:

```
WITH
  base AS (
  SELECT
    *
  FROM
    `moz-fx-data-shar-nonprod-efed.eng_workflow_live.bmobugs_v1`
  WHERE
    submission_timestamp > '2019-01-01' ),
  numbered AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY document_id ORDER BY submission_timestamp) AS _n,
    *
  FROM
    base )
SELECT
  * EXCEPT (_n)
FROM
  numbered
WHERE
  _n = 1
```

But does the cron job always extract full history? If that's what you want to do, then submitting through structured ingestion may really not be what we want and we maybe need to consider a different way of approaching this problem.

1) You could run a BQ query to pull down the list of existing document_ids. This would require having GCP creds available for the service and using GCP client libraries. So it could be done, but is probably some effort.
2) The ingestion machinery really is not set up to handle deletion outside of the stated deduplication efforts.
3) Yes, it's reasonable to write a query that deduplicates. For example:

```
WITH
  base AS (
  SELECT
    *
  FROM
    `moz-fx-data-shar-nonprod-efed.eng_workflow_live.bmobugs_v1`
  WHERE
    submission_timestamp > '2019-01-01' ),
  numbered AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY document_id ORDER BY submission_timestamp DESC) AS _n,
    *
  FROM
    base )
SELECT
  * EXCEPT (_n)
FROM
  numbered
WHERE
  _n = 1
```

But does the cron job always extract full history? If that's what you want to do, then submitting through structured ingestion may really not be what we want and we maybe need to consider a different way of approaching this problem.