Databricks Data Analyst

Question 1

Describe the key audience and side audiences for Databricks SQL.

Answer 1

primary audience is data analysts, data scientists and engineers are side audiences

Question 2

Describe that a variety of users can view and run Databricks SQL dashboards as stakeholders.

Answer 2

Users can view and run dashboards without having access to anything else in the platform. Bascially like viewers in Tableau

Question 3

Describe the 4 benefits of using Databricks SQL for in-Lakehouse platform data processing.

Answer 3

1) Allows users to query and analyze data stored in data lakes and warehouses using SQL.
2) Built on Databricks Lakehouse platform, provides unified platform for data engineering, science, and analytics.
3) Provides high performance query engine optimized for big data workloads
4) Provides a range of tools and features for data processing, including visualization, transformation, and ML

Question 4

Describe how to complete a basic Databricks SQL query.

Answer 4

Go to query editor, select warehouse, and run a SELECT statement

Question 5

Identify the information displayed in the schema browser from the Query Editor page.

Answer 5

Schema browser allows you to view all data objects, such as databases, tables, colums, and data types. Used to explore structure of data

Question 6

Identify Databricks SQL dashboards as a place to display the results of multiple queries at once.

Answer 6

Think of each query as a data source, multiple can be used in one dashboard

Question 7

Describe how to complete a basic Databricks SQL dashboard.

Answer 7

Select dashboard on toolbar, add tiles, select query and visualization for each tile

Question 8

Describe how dashboards can be configured to automatically refresh.

Answer 8

From dashboard, click schedule button at the top, can set up subscriptions here too, must schedule both query and dashboard independently

Question 9

Describe the purpose of Databricks SQL endpoints/warehouses.

Answer 9

1) provide general compute resources for queries, visualizations and dashboards 2) provide a way to separate compute resources for SQL workloads from other workloads 3) Serverless, Pro, Classic

Question 10

Identify Serverless Databricks SQL endpoint/warehouses as a quick-starting option.

Answer 10

Designed to be easy to set up and use, optimized for lower cost, lower performance workloads

Question 11

Describe the trade-off between cluster size and cost for Databricks SQL endpoints/warehouses.

Answer 11

Large clusters handle more concurrent queries and larger workloads, but cost more to run

Question 12

Identify Partner Connect as a tool for implementing simple integrations with a number of other data products.

Answer 12

1) Provides simpler alternative to manual connections by provisioning Azure Databricks resourses on your behalf and passing details on to partners 2) Creates trial account if you don’t already have an account

Question 13

Describe how to connect Databricks SQL to ingestion tools like Fivetran.

Answer 13

1) Select Partner Connect 2) Click Partner 3) Enter connection info 4) Complete log in from partner website on new tab 5) Follow set up instructions, set destination 6) create endpoint, warehouse, and table to receive data

Question 14

Identify the need to be set up with a partner to use it for Partner Connect.

Answer 14

Must have a license with partner in order to use it in Databricks

Question 15

Identify small-file upload as a solution for importing small text files like lookup tables and quick data integrations.

Answer 15

good for csv’s, is there a way to automate refreshes of local files?

Question 16

Import from object storage using Databricks SQL.

Answer 16

review object storage

Question 17

Identify that Databricks SQL can ingest directories of files when the files are the same type.

Answer 17

DB reads all files and combines them into a single table if they have the same structure

Question 18

Describe how to connect Databricks SQL to visualization tools like Tableau, Power BI, and Looker.

Answer 18

1) Navigate to clusters tab 2)In advanced options, select JDBC/ODBC tab 3) Follow instructioons to download driver for viz tool 4) configure tool with driver

Question 19

Identify Databricks SQL as a complementary tool for BI partner tool workflows.

Answer 19

take advantage of scalability and performance of DB platform while having familiar interface and functionalities of BI tool

Question 20

Describe the medallion architecture as a sequential data organization and pipeline system of progressively cleaner data.

Answer 20

bronze = raw data staging silver = data warehouse gold = published data sources

Question 21

Identify the gold layer as the most common layer for data analysts using Databricks SQL.

Answer 21

like published Tableau data sources

Question 22

Describe the cautions and benefits of working with streaming data.

Answer 22

similar to live data sources Benefits: real-time insights, faster decision making, ability to respond quickly Cautions: managing volume of data, ensuring quality and consistency, specialized expertise

Question 23

Identify that the Lakehouse allows the mixing of batch and streaming workloads

Answer 23

you can have both extracts and live data in your environment, allows you to buid real-time applications, while also having traditional batch processing for historical analysis

Question 24

Describe Delta Lake as a tool for managing data files.

Answer 24

supports ACID transactions (Atomicity, Consistency, Isolation, and Durability), Highly scalable, provides tools like VACUUM (removes unused files from table directory) and OPTIMISE (Optimizes layout of a subset of data)

Question 25

Describe that Delta Lake manages table metadata.

Answer 25

1) Provides support for schema evolution meaning you can modify the table over time without having to rewrite the whole table. 2) Provides support for managing table properties, such as the location of the table data and the format of the data files

Question 26

Identify that Delta Lake tables maintain history for a period of time.

Answer 26

Each table modification creates a new version, you can use VERSION AS OF or RESTORE TABLE TO VERSION to access old versions or revert changes, query table at a specific point in time to time travel

Question 27

Describe the benefits of Delta Lake within the Lakehouse.

Answer 27

1) ACID Transactions 2) Scalable metadata handling 3) efficient query processing 4) Schema evolution 5) Unified platform https://docs.databricks.com/en/introduction/delta-comparison.html

Question 28

Describe persistence and scope of tables on Databricks.

Answer 28

1) Global tables are available across all clusters in a workspacec anc can be accessed by all users 2) cluster scoped tables are available only within a specific cluster and not visible to other clusters or users 3) Notebook scoped tables are available only within a specific notebook and are not visible to other notebooks or users — Persisting tables in a storage format allows them to be stored on disk and accessed more efficiently

Question 29

Compare and contrast the behavior of managed and unmanaged tables.

Answer 29

Managed tables: managed by databricks, easy to create (ideal for small to medium sized datasets), optimized for performance Unmanaged tables: more flexible than managed tables, ideal for larger datasets, faster to load and write data, but require more manual management

Question 30

Identify whether a table is managed or unmanaged.

Answer 30

Check the info under details in the DB Catalog or use DESCRIBE EXTENDED in SQL Query Editor

Question 31

Explain how the LOCATION keyword changes the default location of database contents.

Answer 31

Use LOCATION to specify where you want the table to go

Question 32

Use Databricks to create, use, and drop databases, tables, and views.

Answer 32

Standard SQL commands. You can set USE with a dropdown above the SQL editor, don’t need to include that in your statement

Question 33

Describe the persistence of data in a view and a temp view

Answer 33

views don’t store data, but provide a way to access and query the underlying data, persisted in underlying tables so changes to table flow to the view, temp views are not persisted, so only availble during session that creates it, changes to underlying data will not be reflected in temp view

Question 34

Compare and contrast views and temp views.

Answer 34

see above

Question 35

Explore, preview, and secure data using Data Explorer.

Answer 35

Select a table or view and preview with Preview tab, Secure data with permissions tab, query data with query tab, visualize with visualize tab

Question 36

Use Databricks to create, drop, and rename tables.

Answer 36

standard SQL commands

Question 37

Identify the table owner using Data Explorer.

Answer 37

Details tab on Data Explorer or in SQL you can ALTER TABLE OWNER TO

Question 38

Change access rights to a table using Data Explorer.

Answer 38

permissions tab

Question 39

Describe the responsibilities of a table owner.

Answer 39

Create and manage table, ensure data quality, grant and revoke access, monitor usage and performance, ensure compliance

Question 40

Identify organization-specific considerations of PII data Section

Answer 40

Databricks, especially with the integration of Delta Lake, provides mechanisms for handling PII, such as fine-grained access control. This allows for specific permissions on sensitive data fields, ensuring that only authorized users can access PII.

Question 41

Identify a query that retrieves data from the database with specific conditions

Answer 41

SELECT statement with WHERE clause

Question 42

Compare and contrast MERGE INTO, INSERT TABLE, and COPY INTO.

Answer 42

MERGE INTO: merges data from target source to data table based on specific conditions INSERT TABLE: used to insert new rows into table COPY INTO used to load data into a Delta table, MERGE INTOis suitable for updating existing records and inserting new records, whileINSERT INTOis used only for adding new records, andCOPY INTOis used for loading data from files

Question 43

Use cube and roll-up to aggregate a data table.

Answer 43

ROLLUP: aggregates on a data cube by reducing one or more dimensions CUBE: aggregates based on a data cube by creating all possible combinations of dimensions

Question 44

Compare and contrast roll-up and cube.

Answer 44

rollup generates hierarchical aggregrations starting from leftmost column in group by clause

Question 45

Use windowing to aggregate time data.

Answer 45

SUM ___ OVER (Partition By) to aggregate data of specific time internals

Question 46

Identify a benefit of having ANSI SQL as the standard in the Lakehouse.

Answer 46

works with other relational databases. Portability, interoperability, familiarity, standardization

Question 47

Identify, access, and clean silver-level data.

Answer 47

bronze is raw data staging, silver is like a data warehouse, gold is like tableau data sources. Identify the data you need, Access the data (obtain necessary permissions and credentials), clean data

Question 48

Utilize query history and caching to reduce development time and query latency.

Answer 48

Query history allows you to use previous queries, caching stores results of a query in memory

Question 49

Optimize performance using higher-order Spark SQL functions.

Answer 49

higher order functions take other functions as input parameters. They optimize performance by 1) simplifying code 2) reducing amount of data that needs to be filtered 3) use with user defined functions (transform() to apply UDF to each element of an array) 4 use with window fucntions(ex: can use collect_list () to collect column into an array, then percentile_approx() to calculate approximate percentile of the values)

Question 50

Create and apply UDFs in common scaling scenarios

Answer 50

https://docs.databricks.com/en/sql/language-manual/sql-ref-functions-udf-aggregate.html

Question 51

Explain how visualization formatting changes the reception of a visualization

Answer 51

Formatting can determine where people focus their attention

Question 52

Describe how to add visual appeal through formatting

Answer 52

layout, Colors, font sizes, etc.

Question 53

Identify that customizable tables can be used as visualizations within Databricks SQL.

Answer 53

more user flexibility within crosstabs than Tableau

Question 54

Describe how query parameters change the output of underlying queries within a dashboard

Answer 54

parameter value is part of a WHERE clause in the underlying query

Question 55

Identify the behavior of a dashboard parameter

Answer 55

affects the entire dashboard

Question 56

Identify the use of the “Query Based Dropdown List” as a way to create a query parameter from the distinct output of a different query.

Answer 56

It dynamically generates a dropdown list based on the distinct output of a separate query, allowing users to select values as query parameters. Creates a parameter dropdown like we use in Tableau

Question 57

Identify that users without permission to all queries, databases, and endpoints can easily refresh a dashboard using the owner’s credentials.

Answer 57

Run as viewer’ enhances data security by adhering to individual viewer’s permissions (pro), but may limit data visibility (con). ‘Run as owner’ ensures consistent data visibility across users (pro), but might pose security risks if the owner has broader data access (con)

Question 58

Identify what happens if a refresh rate is less than the Warehouse’s “Auto Stop”

Answer 58

The dashboard will continue to refresh at the set interval, the SQL Warehouse will continue running.

Question 59

Describe key moments of statistical distributions.

Answer 59

then the first moment is theexpected value, the secondcentral momentis thevariance, the thirdstandardized momentis theskewness, and the fourth standardized moment is thekurtosis

Question 60

Describe data enhancement as a common analytics application.

Answer 60

Adding other sources of data into a data source

Question 61

Identify a scenario in which data enhancement would be beneficial.

Answer 61

Adding weather data to sales metrics

Question 62

Describe the blending of data between two source applications.

Answer 62

Not the same as Tableau, just refers to joining data together