Free Practice Questions for Databricks Databricks-Certified-Data-Analyst-Associate Exam

To get the number of orders per customer, you need to join the customers and orders tables on the customer_id, count the order_id, and group the results by customer_name. The correct SQL syntax, as outlined in Databricks SQL documentation, is to use GROUP BY on the selected customer field and use COUNT for aggregation. Only option A does this correctly, while the other options contain syntax errors or incorrect field names.

The reason the alert is not functioning as expected is becauseDatabricks SQL Alerts do not support query parameters. This limitation applies to all types of parameters, including date parameters.

Here’s why:

Alerts require static, deterministic query resultsso they can compare values consistently during scheduled executions.

When a query includes parameters (e.g., a date range parameter), its results may change based on user input or the default value set in the query editor.

However,Databricks SQL Alerts will always use the default value set for the parameter at the time the alert is created. This means the alert doesn’t dynamically adapt to new date ranges and will not reflect changes unless the query is manually updated.

As a result, if the business logic behind the alert depends on changing date ranges or any user input, the alert will not trigger correctly, or may never trigger at all.

Therefore, the correct explanation contradictsOption B, which is incorrect in saying that alerts cannot work with date-based queries at all. In fact, theycan—as long as the query is static(i.e., without parameters).

Databricks SQL uses ANSI SQL as its standard SQL dialect, which means it follows the SQL specifications defined by the American National Standards Institute (ANSI). This makes it easier to migrate existing SQL queries from other data warehouses or platforms that also use ANSI SQL or a similar dialect, such as PostgreSQL, Oracle, or Teradata. By using ANSI SQL, Databricks SQL avoids surprises in behavior or unfamiliar syntax that may arise from using a non-standard SQL dialect, such as Spark SQL or Hive SQL12. Moreover, Databricks SQL also adds compatibility features to support common SQL constructs that are widely used in other data warehouses, such as QUALIFY, FILTER, and user-defined functions2. References: ANSI compliance in Databricks Runtime, Evolution of the SQL language at Databricks: ANSI standard by default and easier migrations from data warehouses

External tables are tables that are defined in the Databricks metastore using the information stored in a cloud object storage location. External tables do not manage the data, but provide a schema and a table name to query the data. To create an external table, you can use the CREATE EXTERNAL TABLE statement and specify the object storage path to the LOCATION clause. For example, to create an external table named ext_table on a Parquet file stored in S3, you can use the following statement:

SQL

CREATE EXTERNAL TABLE ext_table (

col1 INT,

col2 STRING

)

STORED AS PARQUET

LOCATION 's3://bucket/path/file.parquet'

AI-generated code. Review and use carefully. More info on FAQ.

In Databricks, to determine the owner of a managed table, you can utilize the Catalog Explorer feature. The steps are as follows:​

Access Catalog Explorer:

In your Databricks workspace, click on theCatalogicon in the sidebar to open Catalog Explorer.​

Navigate to the Table:

Within Catalog Explorer, browse through the catalog and schema to locate the specific managed table whose ownership you wish to verify.​

View Table Details:

Click on the table name to open its details page.​

Identify the Owner:

On the table's details page, review theOwnerfield, which displays the principal (user, service principal, or group) that owns the table.​

This method provides a straightforward way to ascertain the ownership of managed tables within the Databricks environment. Understanding table ownership is essential for managing permissions and ensuring proper access control.​

Databricks provides a user-friendly interface that allows data analysts to quickly upload small datasets, such as a 50-row CSV file, and create tables within Databricks SQL. The steps are as follows:​

Access the Data Upload Interface:

In the Databricks workspace, navigate to the sidebar and click onNew>Add or upload data.​

SelectCreate or modify a table.​

Upload the CSV File:

Click on thebrowsebutton or drag and drop the CSV file directly onto the designated area.​

The interface supports uploading up to 10 files simultaneously, with a total size limit of 2 GB.​

Configure Table Settings:

After uploading, a preview of the data is displayed.​

Specify the table name, select the appropriate schema, and configure any additional settings as needed.​

Create the Table:

Once all configurations are set, click on theCreate Tablebutton to finalize the process.​

This method is efficient for quickly importing small datasets without the need for additional tools or complex configurations. Options B, C, and D involve more complex or manual processes that are unnecessary for this task.​

The mean is sensitive to extreme values, often called outliers, which can significantly skew the average away from the true center of the data. The median, however, is a measure of central tendency that is resistant to such outliers because it only considers the middle value(s) when the data is ordered. Therefore, when a variable contains many extreme outliers, there will be a substantial difference between the mean and the median. According to Databricks data analysis materials, this is a fundamental concept when choosing summary statistics for reporting.

In Databricks, aviewis a saved SQL query definition that references existing tables or other views. Once created, a view remains persisted in themetastore(such as Unity Catalog or Hive Metastore) until it is explicitly dropped.

Key points:

Views donot store datathemselves but reference data from underlying tables.

Logging out or being inactive doesnotdelete or alter views.

Unless a user or admin explicitlydropsthe view or the underlying data/table is deleted, the view continues to function as expected.

Therefore, after logging back in—even days later—a user can still run SELECT * FROM stakeholders.eur_customers, and it will return the same data (provided the underlying table hasn’t changed).

Databricks Partner Connect is designed to simplify and streamline the integration between Databricks and its technology partners. It provides a unified interface within the Databricks platform that facilitates the discovery and connection to a variety of data, analytics, and AI tools. By automating the configuration of necessary resources such as clusters, tokens, and connection files, Partner Connect enables seamless, bi-directional data flow between Databricks and partner solutions. This integration enhances the overall functionality of the Databricks Lakehouse by allowing users to easily incorporate external tools and services into their workflows, thereby expanding the platform's capabilities and fostering a more cohesive data ecosystem.​