Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Redshift: Troubleshooting View Data & Creation Issues

Important!
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.

Stitch & Structural Changes

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. (For versions 1.1+)
  • A new column has been added to the table as a result of VARCHAR widening. (For versions 1.1+)

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.

Finding Dependent 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.

  1. Using a SQL or command line tool, login to your Redshift database as an administrator.
  2. 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]'
    
  3. Now that you’ve found the dependent view, you can run a command to drop it. In this example there's only one dependency, but to ensure all dependent views are dropped, you should 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;

After the replication cycle completes, you can re-create your dependent views.

Next Steps

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.

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!