Noos
PT EN
RAN-DBDEA-00054
Ranvier · DBDEA

Exam DBDEA Topics: transformation Difficulty: hard

The analytics team wants catalog.analytics.daily_revenue as a precomputed daily revenue rollup over catalog.sales.gold_orders. The underlying aggregation is expensive, so recomputing it on every dashboard query is unacceptable; BI tools must read the object like a normal table, and the results must be refreshed automatically every night at 02:00 UTC with no manual steps. Which set of statements achieves this?

Options (Reading Mode) Click to filter discussion →
  1. A. CREATE VIEW catalog.analytics.daily_revenue AS SELECT order_date, sum(amount) AS revenue FROM catalog.sales.gold_orders GROUP BY order_date; ALTER VIEW catalog.analytics.daily_revenue ADD SCHEDULE CRON '0 0 2 * * ?' AT TIME ZONE 'UTC';
  2. B. CREATE OR REFRESH STREAMING TABLE catalog.analytics.daily_revenue SCHEDULE CRON '0 0 2 * * ?' AT TIME ZONE 'UTC' AS SELECT order_date, sum(amount) AS revenue FROM catalog.sales.gold_orders GROUP BY order_date;
  3. C. CREATE TABLE catalog.analytics.daily_revenue TBLPROPERTIES ('refresh.cron' = '0 0 2 * * ?') AS SELECT order_date, sum(amount) AS revenue FROM catalog.sales.gold_orders GROUP BY order_date;
  4. D. CREATE MATERIALIZED VIEW catalog.analytics.daily_revenue SCHEDULE CRON '0 0 2 * * ?' AT TIME ZONE 'UTC' AS SELECT order_date, sum(amount) AS revenue FROM catalog.sales.gold_orders GROUP BY order_date;
Reveal answer

Correct answer: CREATE MATERIALIZED VIEW catalog.analytics.daily_revenue SCHEDULE CRON '0 0 2 * * ?' AT TIME ZONE 'UTC' AS SELECT order_date, sum(amount) AS revenue FROM catalog.sales.gold_orders GROUP BY order_date;

Reference: https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-materialized-view

Train this exam on Ranvier →

02 — The Myelin Layer · Canonical Discussion

Where the exam tries to trick you — and how those who passed solved it.

0 Passed

contributed to this question

Answer Distractor Matrix Click an option to filter or open its dissection.
✦ Consolidated Synthesis (Wiki) rev. #1 (baseline) · CC BY-SA 4.0
Start synthesis for this question →

No community synthesis consolidated for this question yet. Below is the verifiable official starting point — if you passed the exam, open the option dissection.

Official Answer (D): CREATE MATERIALIZED VIEW catalog.analytics.daily_revenue SCHEDULE CRON '0 0 2 * * ?' AT TIME ZONE 'UTC' AS SELECT order_date, sum(amount) AS revenue FROM catalog.sales.gold_orders GROUP BY order_date;

Ranvier · DBDEA

Contribute to this Question

Your contribution builds your Orbital Sheath

Peer contributions

No public contribution yet. Be the first who passed to explain why the distractors trick candidates — your contribution builds your Orbital Sheath.