Ever have a SQL view that won't seem to materialize? Getting that dreaded red X even when results are showing in the View editor? Well follow the below instructions to turn those red X's into green checks!
1. YOUR SQL SYNTAX ISN'T REDSHIFT COMPATIBLE.
If your SQL query isn't able to run on the cache, it will still try to run on your database to produce results. So if you have a database that doesn't use Redshift, the query will still show results even though the syntax can't be materialized. The best solution for this is to follow proper Redshift syntax. If you’re working on converting MySQL syntax to Redshift, check out this blog post for more details.
2. THE UNDERLYING TABLES HAVE YET TO BE CACHED.
Similar to point 1, your SQL query will show results if it can work on your database, but not the cache. If you're querying a table that isn’t on the cache, you might see results, but this won’t materialize! If you get the below error, chances are this is your issue:
ERROR: relation "_" does not exist
The steps to solving this are to check your cache setting page and look for the underlying tables.
If you seen green checkmarks there, you should be good to go. Otherwise, go ahead and cache the table to get this view materialized.
3. TWO COLUMNS HAVE THE SAME NAME.
Tables and Views on the cache require unique column names in order to materialize properly. Sometimes when joining tables non-unique column names like “id” will cause this error. Simply changing a column name to a more specific alias will do the trick here.
4. THERE ARE FILTERS IN THE VIEW.
One of the biggest reasons Views fail during materialization is due to the use of filters in the SQL query. If you're using filters, you might see either of the below errors:
ERROR: syntax error at or near "."
ERROR: syntax error at or near "="
Materialization doesn’t work in this scenario because filters are meant for dynamic changes to data, where as materialization is meant to provide faster, static data for you to work from. The easiest way to resolve this is to replace your filters with static clauses, or just remove these filters all together.
