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?
ASELECT o.order_id, a.account_name FROM main.sales.orders o JOIN pg_crm.public.accounts a USING (account_id)
BSELECT o.order_id, a.account_name FROM main.sales.orders o JOIN pg_crm.accounts a ON o.account_id = a.account_id
CCREATE TABLE pg_crm.public.orders_copy AS SELECT * FROM main.sales.orders, then join the two tables inside pg_crm
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.
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?
ARemove the SCHEDULE clause and refresh the view from a job instead
BGive the date_trunc and sum expressions aliases, such as AS order_day and AS revenue
CCreate it as a streaming table, because materialized views cannot hold aggregates
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
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.
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?
ABoth main.sales.orders and pg_crm.public.accounts
BOnly main.sales.orders, the Delta source
CNo PostgreSQL table until a steward adds it as external lineage
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.
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?
AThe SQL warehouses that queried it most often in the past month
BThe storage location of its managed tables
CIts connection and the external database it mirrors
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.
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?
ASET use_cached_result = false, run first
BASYNC
CSCHEDULE EVERY 1 HOUR
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.
A dynamic view masks salaries by group membership. One analyst can't read it from her cluster. Which compute cannot read dynamic views?
AA serverless SQL warehouse
BCompute in standard access mode (formerly shared)
CDedicated compute on Runtime 15.3 or below
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.