Deprecated and Temporary Objects

The rule

Never query an object whose name begins with zzz_ or tmp_. Never use one as a data source, never join to one, and never reference one in a dashboard, saved query, or analysis.

This holds across every database and schema — ANALYTICS_DB.ANALYTICS, every RAW_DB schema, and any future schema. The prefix is the signal; no other check is needed.

If a question seems to require one of these objects, the correct answer is that the data should come from somewhere else. Find the supported source rather than reaching for the prefixed object.

What each prefix means

zzz_ — deliberately retired. The object was once in use and has been decommissioned. It is retained only because it holds history that cannot be reproduced from current code and sources, or because dropping it has not yet been scheduled. It is FROZEN: no longer refreshed, and drifting further from reality every day. Numbers from a zzz_ object will look plausible and be wrong.

tmp_ — transient loader artifact. Scratch output from an ingestion process. It may be empty, may be a partial batch, may be mid-write while you query it, and may vanish without notice. It carries no guarantee of completeness at any moment.

Why this matters more than it looks

These objects are dangerous precisely because they are not obviously broken. A retired table still returns rows, still has sensible column names, and still joins cleanly. Nothing errors. The result is simply stale — often by months — and there is no signal in the output that anything is wrong.

A retired device table, for example, keeps returning a device count that was accurate on the day it was frozen. It just silently stops counting every device created since.

Where to go instead

If a replacement source is documented, use it. If none is documented, treat the data as unavailable and say so, rather than substituting a prefixed object.

Finding them

The set changes over time, so identify them by prefix rather than from a fixed list:

select 'RAW_DB' as database_name, table_schema, table_name, table_type
from raw_db.information_schema.tables
where lower(table_name) like 'zzz~_%' escape '~'
   or lower(table_name) like 'tmp~_%' escape '~'
union all
select 'ANALYTICS_DB', table_schema, table_name, table_type
from analytics_db.information_schema.tables
where lower(table_name) like 'zzz~_%' escape '~'
   or lower(table_name) like 'tmp~_%' escape '~'
order by database_name, table_schema, table_name;

INFORMATION_SCHEMA is scoped to a single database, so each database must be queried separately and unioned. Add a branch for any other database that needs checking.

Note the escape clause: _ is a single-character wildcard in SQL LIKE, so an unescaped 'zzz_%' also matches names like zzza. Escaping it keeps the match to a literal underscore.