yoklainterview sim
ProgressHistory

Data Engineer Wh Materialized Views Caching Interview Questions

75 verified Data Engineer Wh Materialized Views Caching interview questions — solve with answers, learn from explanations, test yourself in a real simulation.

Try the real simulation →

Sample questions

Wh Materialized Views CachingDifficulty 1
In Snowflake, what is the core difference between a regular (non-materialized) view and a materialized view?
  • aThe materialized view stores its result physically✓
  • bThe materialized view exists only in warehouse memory
  • cBoth always compile to the same execution plan
  • dThe distinction is purely a naming convention
Explanation:A Snowflake materialized view stores its result set on disk and needs its own storage plus a refresh mechanism; a regular view has no stored data and re-runs its query on every reference.
Wh Materialized Views CachingDifficulty 1
Which Snowflake edition requirement applies to using materialized views?
  • aAvailable on every edition, including Standard
  • bA separately purchased add-on, edition-independent
  • cRequires Enterprise Edition or higher✓
  • dDepends only on the account's region
Explanation:Materialized views are an Enterprise Edition (or higher) feature in Snowflake; Standard Edition accounts cannot create them.
Wh Materialized Views CachingDifficulty 2
A Snowflake engineer tries to define a materialized view whose query joins two base tables together. What happens?
  • aIt is created normally; joins are supported
  • bCreation fails; joins are unsupported here✓
  • cThe join is dropped; only the first table is materialized
  • dIt is created but auto-converted into a regular view
Explanation:Snowflake materialized views cannot be defined over a query that joins tables, including self-joins; the defining query must be based on a single table.
Wh Materialized Views CachingDifficulty 2
In Snowflake, how does a materialized view stay up to date as its base table changes, from the user's point of view?
  • aThe user must run REFRESH after every change
  • bIt never updates; it must be dropped and recreated
  • cThe user must schedule a task that rewrites its data
  • dAn automatic background service maintains it✓
Explanation:Snowflake materialized view maintenance is fully automatic: a background service updates the view as the base table changes, so there is no user-facing REFRESH command, unlike some other warehouses.
Wh Materialized Views CachingDifficulty 2
CREATE MATERIALIZED VIEW mv_scored AS
SELECT id, my_custom_udf(amount) AS scored_amount
FROM orders;

In Snowflake, what happens when this statement runs, given my_custom_udf is a user-defined function?
  • aIt succeeds; any UDF is fully supported
  • bIt fails; UDFs of any kind are not allowed✓
  • cIt succeeds only for SQL-language UDFs
  • dIt succeeds but the UDF runs once, at creation
Explanation:Snowflake explicitly disallows UDFs — including external functions — inside a materialized view's defining query, so this CREATE statement fails regardless of the UDF's implementation language.
Wh Materialized Views CachingDifficulty 3
A team has a large Snowflake materialized view and notices that queries filtering on a specific column still scan a large portion of the view's stored data. What can they configure on the materialized view itself to improve pruning for that filter?
  • aA clustering key on the materialized view✓
  • bA foreign key constraint on the materialized view
  • cNothing; only base tables allow such settings
  • dA secondary index via CREATE INDEX
Explanation:Snowflake allows defining a clustering key directly on a materialized view, which reorganizes its stored micro-partitions to improve pruning for filters on that key — though it adds its own maintenance cost.

By specialization

Pipelines / ETL 450Streaming 450Warehousing 450

By seniority

Data Engineer JuniorData Engineer MidData Engineer Senior

Other fields

BackendFrontendFullstackDevOps / CloudAI EngineerDatabaseMobileSecurityQA / Test AutomationGame DevelopmentML Engineer

Test yourself against the 1950-question Data Engineer bank.

Start interview