Databricks Data Engineer Exam Notes

<p>(spark.table(&quot;sales&quot;) .withColumn(&quot;avgPrice&quot;, col(&quot;sales&quot;) / col(&quot;units&quot;)) .writeStream .option(&quot;checkpointLocation&quot;, checkpointPath) .outputMode(&quot;append&quot;) .table(&quot;newSales&quot;)) (D)</p> Signup and view all the answers

<p>if <code>day_of_week</code> == 1 and <code>review_period</code>: (B)</p> Signup and view all the answers

<p>CREATE FUNCTION combine_nyc(city STRING) RETURNS STRING BEGIN CASE WHEN city = &quot;brooklyn&quot; THEN &quot;new york&quot; ELSE city END; END; (A), CREATE FUNCTION combine_nyc(city STRING) RETURNS STRING BEGIN CASE WHEN city = &quot;brooklyn&quot; THEN &quot;new york&quot; ELSE city END; END; (C), CREATE FUNCTION combine_nyc(city STRING) RETURNS STRING BEGIN CASE WHEN city = &quot;brooklyn&quot; THEN &quot;new york&quot; ELSE city END; END; (E)</p> Signup and view all the answers

<p>Needs SELECT permission only on the VIEW (D)</p> Signup and view all the answers

<p>You can have more than 1 metastore within a databricks account console but only 1 per region. (D), If metastore is not associated with location, it’s mandatory to associate catalog with managed locations (E)</p> Signup and view all the answers

A structured data lakehouse uses an open data format, such as Parquet, and combines transactional ACID properties with data warehousing capabilities. This allows both real-time and batch analytics on the same data, eliminating the need for separate data lakes and data warehouses.

A data pipeline in Databricks typically follows a layered approach, starting with raw data and progressively refining it through various stages.

• Raw: Untouched data, as it arrives from its source.
• Bronze: Basic data processing and cleaning, ensuring consistency and format.
• Silver: Further data enrichment and transformations, bringing the data closer to its final analytical form.
• Gold: Final, analytical data, ready for reporting and analysis.

Delta Live Tables (DLT) in Databricks enables a more efficient and automated way to build data pipelines by providing a declarative approach using SQL. With DLT, data engineers can define the desired data transformations and constraints, and DLT manages the execution and monitoring of the pipeline. This simplifies the process of building data pipelines while ensuring data quality and consistency.

When you use a CREATE STREAMING LIVE TABLE instead of a CREATE LIVE TABLE, it indicates that you want the data to be processed incrementally as it arrives. This is useful for streaming sources, where new data is constantly being added.

Managed tables in Databricks are automatically managed by Databricks, meaning that they do not have a specific location in the filesystem. This means you can use a simple DROP TABLE command to delete a managed table, and the associated data files and metadata will be automatically deleted. External tables, on the other hand, require you to manage the data files and metadata yourself.

A cluster pool in Databricks allows you to pre-configure a set of clusters with specific configurations, such as worker types, instance sizes, and libraries, that can be reused by multiple jobs. This enables faster startup times for jobs, as the cluster is already ready to go when the job starts.

The spark.sql function in PySpark provides a unified way to interact with data in Databricks, regardless of whether it is in a Delta table, a DataFrame, or a plain SQL query.

Auto Loader is a feature in Databricks that acts as a smart data ingest tool for streaming data sources. It automatically detects new files in cloud storage and ingests them into a Delta table. This allows for near real-time data processing and eliminates the need for manual file monitoring and ingestion.

The trigger(processingTime="5 seconds") option in Structured Streaming lets you configure the query to process data in micro-batches, with a predetermined interval of 5 seconds. This means the query will run every 5 seconds to process the data that has arrived since the last run.

The COPY INTO command in Databricks is a convenient way to copy data from a file or directory in cloud storage into a Delta table. It supports various file formats, including Parquet, CSV, JSON, and Avro, and offers various options to control the data copying process, such as filtering, transformations, and error handling.

When using the MERGE command in Databricks to insert data into a Delta table, it will intelligently handle cases where the records already exist. If a record is already present in the table, it will update it with the new values. This ensures that duplicate records are not inserted, and the data in the table remains accurate and consistent.

An expectation in Delta Live Tables is like a rule that helps ensure data quality and integrity throughout your data pipeline. When you define an expectation, you are essentially saying that the data should meet certain criteria. For example, a timestamp in your data should be within a specific range. If the data violates an expectation, Delta Live Tables can handle the violation in different ways, such as dropping the record or failing the pipeline, depending on your configuration.

The CONSTRAINT clause in Delta Live Tables allows you to define and enforce specific rules on your data, ensuring data quality and consistency. It works in conjunction with expectations to define the criteria for valid data. You can define a constraint to drop rows that violate certain criteria.

The ON VIOLATION DROP ROW option in Delta Live Tables specifies how to handle data that violates constraints. If a constraint violation occurs, the entire row of data will be discarded and dropped from the table. This strict approach helps guarantee data quality by ensuring that invalid data is not included in the table.

The ON VIOLATION FAIL UPDATE option in Delta Live Tables dictates how a job should handle data that violates constraints. When this option is set, the entire job will immediately fail, preventing the entry of invalid or erroneous data into the tables. This helps ensure data accuracy and consistency by terminating the processing when constraints are not met.

SQL UDFs (User Defined Functions) in Databricks provide a way to extend the SQL language by allowing you to customize data transformations. These functions can be written in SQL and called within SQL queries, simplifying the implementation of complex logic.

A PIVOT transformation in SQL is used to reshape data by moving one or more columns from rows into columns. This is useful for converting a table from a long format where data is stacked vertically in rows to a wide format, where data is spread horizontally across columns.

The count_if function in SQL enables you to count the rows in a table based on a specific filtering condition. You can provide a boolean expression as an argument, which is evaluated for each row. If the condition is true, the counter increments. Otherwise, the counter remains unchanged.

The count function in SQL allows you to determine the total number of rows in a table. This command essentially counts the rows in a SQL table and returns the total number of rows as an output.

Databricks Repos is a feature that allows you to version control your entire data engineering project in Databricks through Git integration. This enables a collaborative development environment, allowing multiple engineers to work on a project together and track changes, reducing the chance of conflicts.

Unity Catalog in Databricks provides a central repository for managing data assets, enabling secure and governed data sharing across different teams and departments. It enables fine-grained access controls and data governance policies, ensuring data security and compliance.

Roles in Databricks Unity Catalog determine the level of access a user has to a specific resource, such as a database, schema, or table. For example, you could assign a role that grants read-only access to specific tables within a database.

When a Delta table is being created in Databricks, the USING DELTA clause must be included in the CREATE TABLE statement. This clause specifies that the table should be created as a Delta table, guaranteeing transactional ACID properties and other features provided by Delta Lake.

A Databricks Job is a way to automate your data engineering tasks. You can create jobs in Databricks to run regularly, such as processing data or building models. Jobs can consist of multiple tasks.

Databricks Jobs can be set to run at specific intervals, such as daily, hourly, or even more frequently. This allows for consistent and automated data processing. You can determine the trigger schedule, allowing for continuous data processing or periodic data ingestion based on your need.

A task in a Databricks Job represents a individual step in your automated workflow. You can add multiple tasks to a Job, making it possible to run a series of steps seamlessly. Tasks can be notebooks, SQL queries, or even shell commands.

When you set up a task dependency in a Databricks Job, it creates a sequence of tasks that need to be run in a specific order. This ensures that the tasks are executed in the correct order based on their dependencies, thereby maintaining the flow of your automated data processes.

An alert in Databricks provides a means of monitoring your jobs and data pipelines. When an alert is triggered, it can notify you through various channels, such as email or webhooks, so you can take appropriate action quickly.

When developing data pipelines, Data Engineers typically use Python or SQL. Databricks allows the use of both languages within data pipelines, providing flexibility in implementing different parts of the pipeline based on each language's strengths.

The spark.delta.table function in PySpark is a convenient way to access and interact with Delta tables specifically. It provides a PySpark object representing the Delta table, allowing you to perform various operations, such as reading, writing, and performing schema updates.

The spark.table function in PySpark is a general purpose function that allows you to access any table in Databricks, including both managed and external tables. This versatility simplifies table access and reduces the need for specific functions for different table types.

The dbutils.sql function in Databricks provides a way to execute SQL commands programmatically. You can pass a SQL string as an argument to this function, enabling you to run SQL queries directly from your PySpark code, facilitating integration between Python and SQL within your data engineering tasks.

A Bronze table in a data pipeline typically represents the initial landing stage for raw data. The data in a Bronze table is usually unstructured or semi-structured, and its primary focus is to ingest the data as it arrives from the source.

A Silver table in a data pipeline often serves as an intermediary stage for data transformation and enrichment. It contains structured data and incorporates basic data quality checks, preparing the data for further analysis.

A Gold table in a data pipeline is the end destination for analytical data. It contains highly refined, consistent, and well-structured data, ready for analysis, reporting, and business intelligence tasks.

A dashboard in Databricks allows you to visualize and monitor your data in a dynamic, interactive way. You can create dashboards to track your data pipelines, performance metrics, and business insights.

A SQL endpoint in Databricks creates a secure and efficient way for your data team to access and query data from the SQL warehouse, through an interface similar to a standard SQL database.

Databricks allows you to implement various types of alerts to monitor data quality and pipeline health. These alerts can be triggered when data fails to meet expectations or when a job or query experiences errors.

The SELECT * FROM sales query is a simple way to select all the rows and columns from a table called 'sales' in SQL. It can be used for extracting data from a table or as a base for further operations like sorting, filtering, or aggregation.

A data engineer might use an all-purpose cluster for a variety of tasks, even during development. But for high throughput jobs, a jobs cluster focused on efficiency is preferred.

Serverless SQL warehouse provides compute power on-demand, so you only pay for the resources used, eliminating the need for idle resources.

When a Databricks SQL dashboard needs to be refreshed, the associated SQL endpoint needs to be running to process the queries.

When using Auto Loader, Databricks handles the automatic detection and ingestion of new files from cloud storage locations.

Checkpoints are used to record the progress of data processing in Structured Streaming jobs. They track the offset range of data processed during each trigger, enabling the job to recover gracefully from failures or restarts.

Write-ahead logs, also known as WAL, maintain a record of all changes made to a Delta Table. They provide a mechanism to track the history of data modifications, enabling reliable recovery and reverting to past states if necessary.

Idempotent sinks ensure that repeated data writes to the same sink (the final output) do not affect the data.

The GRANT ALL PRIVILEGES command in Databricks allows you to grant all the necessary permissions to a specific user or group on a table or database.

The GRANT USAGE command in Databricks is used to grant basic permission, allowing the user to view and reference the table, without specific access to its content.

Auto Stop feature allows you to automatically stop the SQL endpoint when it remains idle for a configured duration, saving compute costs.

With a Databricks cluster pool, you can configure multiple clusters with specific configurations, which can be used by different jobs, enabling faster startup times.

Signup and view all the flashcards