Redshift View Dependency Errors | Stitch Documentation

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.

Structural Changes in Tables

When a table’s structure is changed, dependent views must be ‘dropped’ so Stitch can re-create the underlying table.

A table’s structure can change for a few reasons:

A new column has been added to the source table.
A new column has been added to the table as a result of column/data type splitting.
A new column has been added to the table as a result of VARCHAR widening.

Stitch & Dependent Views

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.

Locating Dependent Views

To troubleshoot, you can run a query against tables in theINFORMATION_SCHEMA to find all first-order dependencies for the schema noted in the error notification.

Using a SQL or command line tool, login to your Redshift database as an administrator and run the query below. When you do this yourself, replace what’s in the [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]'

Now that you’ve found the dependent view, you can run a command to drop it. To ensure all dependent views are dropped, use the CASCADE option.

Remember to use the correct schema and dependent view name when running this yourself:

DROP VIEW [error_schema].[dependent_view] CASCADE;

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. After the replication cycle completes, you can re-create your dependent views.

Contacting Support

If new data still hasn’t entered your warehouse after dropping the views overnight, please reach out to support.

Questions? Feedback?

Did this article help? If you have questions or feedback, please reach out to us.

Tags: