Views, materialised views, and stored procedures are the database's mechanisms for encapsulating SQL logic into reusable, named objects. A view is a stored query that behaves like a table — every time it is queried, the underlying SQL executes and returns fresh results. A materialised view stores the query results physically, like a cache — queries against it are fast (reading precomputed data) but the results may be stale until explicitly refreshed. A stored procedure is a named block of procedural code that can execute SQL, implement business logic, and be called from applications or other procedures.
For data engineers, these objects serve distinct roles in the data architecture. Regular views provide a stable interface between the raw schema and consumers — the underlying tables can be restructured without breaking downstream queries, as long as the view's columns remain consistent. Materialised views are the database's native form of pre-aggregation — they reduce the cost of expensive recurring computations by persisting the results. Stored procedures encapsulate complex ETL logic and data quality checks, making them reusable, version-controllable, and testable in isolation from application code.
The choice between these objects involves trade-offs between freshness, performance, and complexity. A regular view always returns current data but executes the full query on every access. A materialised view returns fast pre-computed data but requires an explicit REFRESH or scheduled job to stay current. A stored procedure provides procedural flexibility but adds a layer of database-side logic that may be harder to test and version-control than equivalent application code. Understanding these trade-offs guides correct architectural decisions in data pipeline and schema design.