'Incremental' tables should support schema changes

[BenBirt]

(disclaimer: I haven't tested how the following works in all cases or against all warehouses, it may be that we already support what follows in some ways / or not, I'm not sure yet.)

Right now it's unclear what happens to an incremental table if the user changes its schema (i.e. adds a new column or deletes an old one, or perhaps changes the type of an existing one). We should check the behaviour of these cases against our currently-supported warehouses.

It would be great if we could support automatically running e.g. ALTER TABLE statements to get these cases to work. The tricky thing here will be figuring out precisely what table alterations need to happen, whether we need to diff old vs. new schemas somehow, or whether we can just update the schema of the table to a new state idempotently without caring what the old state of the table might be.

If it turns out to be impossible / really hard to support this kind of behaviour, we should at least check that dataform does something that the user might expect, e.g. throw an error depending on the case.

[deniszaboronsky]

@BenBirt I came on to raise this issue. In BigQuery (Dataform version 1.22.0) we are seeing that new columns require us to re-create the base table as otherwise what Dataform compiles to is:

INSERT INTO target_table (Col1, Col2, Col3)
AS (
SELECT
    Col1, Col2, Col3, Col4
FROM some_table)

Where the query used to be

SELECT
    Col1, Col2, Col3
FROM some_table

and is now

SELECT
    Col1, Col2, Col3, Col4
FROM some_table

This of course means that we lose the new column that we had hoped to add. Just though it was worth flagging that we would love an alter table statement. Let me know if any extra info would be useful

[SourabhKr]

@Ekrekr is there any update on this request?
The requirement here is very basic and usually there are workarounds running in all implementations.

[Ekrekr]

@Ekrekr is there any update on this request?

Still not yet.

The requirement here is very basic

Not really, there's a lot of usability implications that would need to be tested.

Contributions are welcome!

[chateletlealSephora]

Hello,

Is there any updates regarding this feature.

dbt manages it through configuration, why not dataform ? Event if it doesn't work for records or repeated records, it is really useful for a part of the schema changes use-cases.

https://docs.getdbt.com/docs/build/incremental-models#what-if-the-columns-of-my-incremental-model-change

[federicojasson]

I wanted to add something to this issue.

The workaround I've implemented is to add idempotent DDL statements in an operation.

Example:

IF (SELECT COUNT(*) FROM some_dataset.INFORMATION_SCHEMA.TABLES WHERE table_name = 'some_table') > 0 THEN
  ALTER TABLE some_dataset.some_table
  ADD COLUMN IF NOT EXISTS some_column STRING;
END IF;

This works, the column is added, but the first time the incremental action is run, it doesn't pick up the new column. My guess is that the table schema is being fetched too early in the execution pipeline.

Note: I made sure the incremental action is run after the operation using dependencies.