Skip to content

Data Analyst Associate · Domain 4: Executing queries using Databricks SQL and Databricks SQL Warehouses · 20% of the exam

Task 4.2: Querying cross-system analytics by joining data from a Delta table and a federated data source; Create a materialized view, including knowing when to use Streaming Tables and Materialized Views, and differentiate between dynamic and materialized views

Connections and foreign catalogs for read-only federated queries, and choosing among views, dynamic views, materialized views and streaming tables, with how each is created and refreshed.

Study it

  • Federated queries and materialized views, streaming tables and dynamic views

    Lesson coming

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

A foreign catalog named pg_crm mirrors a PostgreSQL database whose schema public holds the table accounts. An analyst wants one query on a serverless SQL warehouse that joins the Delta table main.sales.orders to that table on account_id. Which query does this?

  1. ASELECT o.order_id, a.account_name FROM main.sales.orders o JOIN pg_crm.public.accounts a USING (account_id)
  2. BSELECT o.order_id, a.account_name FROM main.sales.orders o JOIN pg_crm.accounts a ON o.account_id = a.account_id
  3. CCREATE TABLE pg_crm.public.orders_copy AS SELECT * FROM main.sales.orders, then join the two tables inside pg_crm
  4. DINSERT INTO pg_crm.public.accounts SELECT * FROM main.sales.orders, then query pg_crm.public.accounts alone
Show the answer and why
  • ASELECT o.order_id, a.account_name FROM main.sales.orders o JOIN pg_crm.public.accounts a USING (account_id)

    Correct

    A foreign catalog is a Unity Catalog catalog, so its tables are named catalog.schema.table and can be joined with Delta tables in a query.

  • BSELECT o.order_id, a.account_name FROM main.sales.orders o JOIN pg_crm.accounts a ON o.account_id = a.account_id

    Incorrect

    pg_crm is the catalog; without the schema public, the name does not reach the table in the three-level namespace.

  • CCREATE TABLE pg_crm.public.orders_copy AS SELECT * FROM main.sales.orders, then join the two tables inside pg_crm

    Incorrect

    Foreign catalogs allow read-only queries; tables cannot be created in them.

  • DINSERT INTO pg_crm.public.accounts SELECT * FROM main.sales.orders, then query pg_crm.public.accounts alone

    Incorrect

    Queries through a foreign catalog are read-only, so the insert fails.

A foreign catalog mirrors a database in an external system and allows read-only queries on it, with access managed in Unity Catalog. Its tables use the usual three-level name (catalog.schema.table), so a federated table can be joined to a Delta table in one SQL statement.

Question 2 · choose 1

An analyst runs this statement on a SQL warehouse, and it fails: CREATE MATERIALIZED VIEW main.gold.daily_revenue SCHEDULE EVERY 1 HOUR AS SELECT date_trunc('DAY', order_ts), region AS region, sum(amount) FROM main.sales.orders GROUP BY date_trunc('DAY', order_ts), region. Which change lets it succeed?

  1. ARemove the SCHEDULE clause and refresh the view from a job instead
  2. BGive the date_trunc and sum expressions aliases, such as AS order_day and AS revenue
  3. CCreate it as a streaming table, because materialized views cannot hold aggregates
  4. DRun it from a notebook on classic compute, because SQL warehouses cannot create materialized views
Show the answer and why
  • ARemove the SCHEDULE clause and refresh the view from a job instead

    Incorrect

    Materialized views can be created with a refresh schedule; the clause is not the problem.

  • BGive the date_trunc and sum expressions aliases, such as AS order_day and AS revenue

    Correct

    In a materialized view, expressions and aggregates in the SELECT clause must use aliases; region already has one.

  • CCreate it as a streaming table, because materialized views cannot hold aggregates

    Incorrect

    Materialized views precompute query results, aggregates included.

  • DRun it from a notebook on classic compute, because SQL warehouses cannot create materialized views

    Incorrect

    Standalone materialized views are created and refreshed from a Databricks SQL warehouse or serverless notebook compute.

CREATE MATERIALIZED VIEW requires an alias for every expression that is not a plain column reference, so date_trunc(...) and sum(amount) need one; the query already aliases region. A standalone materialized view created on a SQL warehouse is backed by a serverless pipeline and can be refreshed on a schedule.

Question 3 · choose 1

An analyst creates main.sales.account_revenue with CREATE TABLE ... AS SELECT, joining the Delta table main.sales.orders with pg_crm.public.accounts, a table in a foreign catalog that mirrors a PostgreSQL database. What does the Lineage tab of account_revenue in Catalog Explorer show upstream?

  1. ABoth main.sales.orders and pg_crm.public.accounts
  2. BOnly main.sales.orders, the Delta source
  3. CNo PostgreSQL table until a steward adds it as external lineage
  4. DNeither table, because CREATE TABLE AS SELECT records no lineage
Show the answer and why
  • ABoth main.sales.orders and pg_crm.public.accounts

    Correct

    Databricks tracks lineage for tables in foreign catalogs the same way as for standard Unity Catalog tables.

  • BOnly main.sales.orders, the Delta source

    Incorrect

    Tables in foreign catalogs are tracked like standard Unity Catalog tables.

  • CNo PostgreSQL table until a steward adds it as external lineage

    Incorrect

    The foreign catalog is a Unity Catalog securable, and lineage for its tables is captured automatically.

  • DNeither table, because CREATE TABLE AS SELECT records no lineage

    Incorrect

    Unity Catalog captures lineage automatically for queries run on Databricks, so the table this query creates has lineage.

A foreign catalog is a Unity Catalog securable that mirrors a database in an external system, and Databricks tracks data lineage for tables in foreign catalogs the same way it tracks lineage for standard Unity Catalog tables. The Lineage tab of the new table therefore shows both source tables upstream.

Question 4 · choose 1

An analyst opens a foreign catalog in Catalog Explorer. Besides what it shows for a standard catalog, what extra details does Catalog Explorer show for it?

  1. AThe SQL warehouses that queried it most often in the past month
  2. BThe storage location of its managed tables
  3. CIts connection and the external database it mirrors
  4. DThe refresh schedule of its tables
Show the answer and why
  • AThe SQL warehouses that queried it most often in the past month

    Incorrect

    The foreign catalog details add connection information, not warehouse usage.

  • BThe storage location of its managed tables

    Incorrect

    A foreign catalog mirrors an external database rather than holding managed tables.

  • CIts connection and the external database it mirrors

    Correct

    Catalog Explorer shows the standard details plus the connection used and the external database or catalog that the foreign catalog mirrors.

  • DThe refresh schedule of its tables

    Incorrect

    Foreign tables are read from the external system; they have no refresh schedule.

For a foreign catalog, Catalog Explorer shows the same details as for a standard Unity Catalog catalog, plus the connection used by the foreign catalog and the external database or catalog it mirrors.

Question 5 · choose 1

REFRESH MATERIALIZED VIEW main.gold.daily_sales blocks the analyst's SQL editor until the refresh finishes. She wants the command to return at once while the refresh runs. What should she add?

  1. ASET use_cached_result = false, run first
  2. BASYNC
  3. CSCHEDULE EVERY 1 HOUR
  4. DCLUSTER BY AUTO
Show the answer and why
  • ASET use_cached_result = false, run first

    Incorrect

    That setting concerns query result caching, not whether a refresh command waits.

  • BASYNC

    Correct

    Refreshes are synchronous by default; the ASYNC keyword makes the refresh run asynchronously.

  • CSCHEDULE EVERY 1 HOUR

    Incorrect

    A schedule sets future refreshes; it does not make this refresh return at once.

  • DCLUSTER BY AUTO

    Incorrect

    Clustering organizes data layout; it has no effect on waiting.

Refresh operations on standalone materialized views are synchronous by default: the command blocks until the refresh completes. Adding the ASYNC keyword runs the refresh asynchronously.

Question 6 · choose 1

A dynamic view masks salaries by group membership. One analyst can't read it from her cluster. Which compute cannot read dynamic views?

  1. AA serverless SQL warehouse
  2. BCompute in standard access mode (formerly shared)
  3. CDedicated compute on Runtime 15.3 or below
  4. DA pro SQL warehouse
Show the answer and why
  • AA serverless SQL warehouse

    Incorrect

    SQL warehouses can read dynamic views.

  • BCompute in standard access mode (formerly shared)

    Incorrect

    Standard access mode is among the documented options for dynamic views.

  • CDedicated compute on Runtime 15.3 or below

    Correct

    The dynamic view page states that you cannot read dynamic views using dedicated compute on Databricks Runtime 15.3 or below.

  • DA pro SQL warehouse

    Incorrect

    SQL warehouses, pro included, can read dynamic views.

Dynamic views filter rows and mask columns based on the querying user. They cannot be read with dedicated compute on Databricks Runtime 15.3 or below; SQL warehouses and standard access mode compute can read them.

Practice domain 4 →Practice all domains →