Dataform write truncante only a specific partition

[romanazzi]

The question is self-explanatory. I need to write with truncate disposition only a specific partition of a table with Dataform. Using only { type: "table" ..., biquery: { partitionBy: ... } } in config with a partitioned table is not enough. Is it an unsupported feature? I can't find an answer in the documentation.

[ms4446]

Yes, it is currently an unsupported feature to write with truncate disposition only a specific partition of a table with Dataform. As a workaround in BigQuery, you can utilize the MERGE statement to truncate a specific partition within a date partitioned table. The following is an example of how you could do this:

MERGE INTO my_table AS t
USING (
SELECT *
FROM my_table
WHERE date = '2023-07-25'
) AS s
ON t.date = s.date
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT (
date,
column1,
column2,
...
)
VALUES (
s.date,
s.column1,
s.column2,
...
);

This query initiates by merging data from the source table (my_table) into the target table (t). The merge condition ensures that only data from the '2023-07-25' partition is merged. The clause 'WHEN MATCHED THEN DELETE' subsequently removes any existing data from the '2023-07-25' partition in the target table. Finally, the 'WHEN NOT MATCHED THEN INSERT' clause inserts the data from the source table into the target table.

[giorgiogrillini]

Any clue on when this feature will be available?

[vintage76]

Any news regarding this issue? is it possible to delete replace only a certain table partition? Is it possibile to generate merge with delete with dataform?

[vijaymohanp]

Any update on bringing in this feature, without this this product is not useful, I may need to end up using some other orchestration service.

[giorgiogrillini]

Google is promoting dataform as the google dbt. The truth is that dbt is far much better in terms of community and features. Google knows this, in fact is using dbt in Dataplex as anyone can see watching Dataplex logs. It's quite simple to setup a terraform script with cloudrun job launching dbt. The only feature dbt lacks is Bigquery integration. Dbt is the only way to go in projects with partitioned tables and complex transformation. The alternative is to wait for new feature indefinitely...

[MaxNRG]

@ms4446 Can we request this `Insert Overwrite strategy in DataForm` feature please?

[ms4446]

@MaxNRG ,Please submit you feature request for Dataform here.