📦 Materialized Views and Custom SQL

Materialized Views are queries that save their output to a custom table. Cron jobs refresh the data on a schedule; there’s a a 15-minute execution time limit.

Materialized Views are useful for very complex/expensive queries, but they should NOT be used to paper over poorly-written custom SQL. A well-designed CJ query will often run fast enough in real-time. Materialized Views may be necessary when, for example, you wish to query millions of rows of Ping data.

Materialized Views can be built like any other queries, including with Configurable Joins and custom SQL. Using custom SQL is tricky; Slate will parse your custom SQL and modify it. Unfortunately, the parser is very sensitive and easy to break.

Are you an IT/SQL veteran who's been dropped into Slate? You will be tempted to use Custom SQL queries. This tool is your last resort, not first. ReWorkflow spends a great deal of time converting IT's custom SQL queries to Configurable Joins after the IT guy leaves.

Example errors:

Error Message: SELECT INTO must be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.

ERR: Incorrect syntax near the keyword 'into'.

ERR: Unable to identify select statement.

The following tips are compilation of our own research and things posted on the forums (see Related Links below).

✅ Tips we have found to be accurate:

• The parser will insert into build.[whatever] after the last left-aligned, lowercase from keyword.
  • select should also be lowercase - unless you want the parser to ignore it when deciding where to insert the into.
• You can leverage this case sensitivity as a hint - the parser ignores uppercase FROM keywords.
• from must be on its own line, not on the same line as select.
• from must be immediately followed, on the same line, by the table or subquery it's selecting from. No linebreak.
• All subquery selects and unions should be offset from the left.

❌ Tips we have not found to be accurate:

• where keyword must be indented.
• Everything must have an alias.

These tips are also useful when using primary/secondary keys with custom SQL.

Related Links

Are Materialized Views with UNIONs no longer compiling? (2023)

Troubleshooting Materialized Views (2017)