The info in this doc only applies to the Redshift destination.
We were unable to recreate the
[view] in your data warehouse with fresh data from the underlying table.
Typically, this error - along with missing views and incorrect data in views - are a result of how Stitch handles altered table structures and views with dependencies in Redshift. When a table's structure is changed, dependent views must be 'dropped' so the Stitch view can be re-created.
A table's structure can change for a few reasons:
VARCHARwidening. (For versions 1.1+)
Because we don’t want to affect your work without your say-so, Stitch wasn’t designed to automatically drop views with dependencies. In this article we’ll walk you through the manual method of finding and dropping these views, but you can also use a simple script to automate the job.
Several of our clients run this script once per day (usually in the middle of the night) to temporarily drop their dependent views. An hour or two is typically enough time for a full replication cycle to complete, which will allow Stitch to drop and re-create its views.
To troubleshoot, you can run a query against the
VIEW_TABLE_USAGE view in the
INFORMATION_SCHEMA to find all first-order dependencies for the schema noted in the error notification.
[square brackets]with the table name:
SELECT DISTINCT c_p.oid AS tbloid ,n_p.nspname AS schemaname ,c_p.relname AS NAME ,n_c.nspname AS refbyschemaname ,c_c.relname AS refbyname ,c_c.oid AS viewoid FROM pg_class c_p JOIN pg_depend d_p ON c_p.relfilenode = d_p.refobjid JOIN pg_depend d_c ON d_p.objid = d_c.objid JOIN pg_class c_c ON d_c.refobjid = c_c.relfilenode LEFT JOIN pg_namespace n_p ON c_p.relnamespace = n_p.oid LEFT JOIN pg_namespace n_c ON c_c.relnamespace = n_c.oid WHERE d_c.deptype = 'i'::"char" AND c_c.relkind = 'v'::"char" AND name = '[table_name]'
drop view [error_schema].[dependent_view] cascade;
After the replication cycle completes, you can re-create your dependent views.
Note that the amount of time required to perform table alterations depends on the size of the table in question. While dropping dependent views for an hour or two is typically sufficient to complete the process, some very large tables may require more time.
If new data still hasn't entered your warehouse after dropping the views overnight, please reach out to support.