Mastering Databricks SQL Warehouse and Spark SQL

Learn to set up a Databricks environment on Azure, create a workspace, and access data science, engineering, and SQL warehouse clusters for hands-on practice.

Sign up for the Azure portal and set up Databricks on Azure to begin using Databricks sql or engineering clusters, with options on aws and gcp.

Sign in to the azure portal, search for azure databricks in the marketplace, create ITV DB demo resource group and workspace, and select premium for the sql editor.

Access the Azure Databricks environment via the unique workspace URL. Sign in with Azure AD single sign-on to access notebooks, clusters, and data science, engineering, or SQL workspaces.

Create a single-node Databricks cluster on Azure, with a selectable access mode and idle timeout to control costs, and attach notebooks to run Python, Scala, R, and SQL.

Learn how to edit and reconfigure Databricks clusters through the workspace UI, including changing runtime, node type, access mode, and inactivity settings, with a practical cluster wizard walkthrough.

Create a notebook and attach it to a running Databricks cluster to run code. Switch between Python, SQL, Scala, and R, use magics, and run individual cells or full notebooks.

Explore the Databricks SQL editor interface, create and configure a SQL warehouse, choose 2x-small or x-small clusters, set auto stop, manage permissions, and prepare to develop queries.

Increase your Azure quota to create a Databricks SQL warehouse cluster by submitting a quota request, then configure SQL warehouses and run queries once the quota is approved.

Run your first query on the nyctaxi trips table in the samples metastore using the Databricks sql warehouse, then explore databases, tables, and visualizations.

Upload a tab-delimited sales.csv into the Databricks sql warehouse ui to create a delta table in the default database, then run queries in the sql editor.

Upload data via Databricks SQL Warehouse UI, review data science and engineering interface, start clusters, explore catalogs, DBFS file store, and create tables using the sales.csv path.

Create a Databricks notebook, connect to the active cluster, and read a tab-delimited sales csv with spark.read.csv, using header and inferred schema, then define a precise schema to resolve issues.

Terminate databricks clusters by inactivity (30 minutes) via the cluster dashboard, and delete the cluster state to avoid runtime and infrastructure charges; learn to create clusters and clean up resources.

Terminate Databricks SQL warehouse clusters by stopping the SQL warehouse in the UI to halt charges. Clean up stopped warehouses by deleting them, noting you need compute to access tables.

Learn how to clean up an Azure Databricks workspace by navigating the Azure portal, selecting the Databricks environment, and deleting the workspace to remove clusters and SQL Warehouse resources.

Install and verify the Databricks CLI with Python 3 pip across Windows, Mac, and Linux, ensuring Python 3 compatibility and avoiding Python 2, with the 0.17.1 release.

Configure the Databricks CLI on Mac or PC by linking your account URL with a generated personal access token, using a named profile to run commands like databricks fs ls.

Clone the Databricks hyphen sql repository to access course materials and datasets, then follow folder navigation and expect updates as the course progresses.

Explore the databricks sql platform on aws, gcp, and Azure with premium access, featuring photon-accelerated queries and seamless data lake house integration with business intelligence tools.

Log in to the Databricks SQL platform, start a SQL endpoint, run your first Spark SQL query on NYC taxi trips, and save it to build dashboards with BI tools.

Explore Databricks SQL visualization features with sample dashboards like NYC taxi trip analysis and retail revenue, and learn to connect BI tools via SQL endpoint and use the SQL editor.

Explore Databricks SQL data explorer to access metastore databases and tables, review NYC Taxi and tpch data, inspect table details and permissions, and build queries and dashboards across platforms.

Use the Databricks sql editor to set up a retail_db database and create orders and order_items tables, then run and save scripts, validate with data explorer and data engineering clusters.

Review table metadata in Databricks SQL by describing formatted for orders and order_items, noting columns, data types, location, and delta provider, then use Data Explorer to validate and plan ingestion.

Learn how to load non-delta source data into delta-format retail_db tables using Databricks CLI and a data engineering cluster, and verify results with the SQL editor.

Configure the databricks cli, generate and use a new token, validate access, and push raw data into delta tables created via sql editor on the databricks sql platform.

Leverage the Databricks CLI to copy the retail_db_json data into DBFS public, creating folders, performing recursive copies, and validating with CLI and the web interface before loading into delta tables.

Create a notebook in databricks, spin up a single-node cluster, and analyze json data with spark.read.json to prepare delta tables and align column order.

Analyze delta table schemas with spark APIs to review column names and data types, fix order discrepancies, and validate loading into retail DB tables.

Load data from spark data frames into delta tables by aligning column order to the target table and using APIs to overwrite and validate results in Databricks SQL.

Validate that data is properly loaded into retail db tables by running ad hoc queries in the Databricks sql editor, comparing counts and distinct values for orders and order items.

Create external tables in Databricks SQL on top of files in DBFS or data lake, defining the structure to query CSV or JSON data without copying files.

Use the copy command in Databricks SQL to load JSON data into delta tables with explicit column mapping and casting for orders and order_items.

Configure and manage Databricks sql endpoints to connect bi tools and run ad hoc analyses, choosing node configurations, auto-scaling policies, and spot instance settings for production workloads.

Explore databases using Data Explorer in Databricks SQL, start and configure a SQL warehouse, and review metadata for databases and tables in the metastore.

Create the LMS underscore bronze database in Databricks SQL, learn that a database is a schema, review with the schema browser and data explorer, and validate creation with run feedback.

Learn to create databases in Databricks SQL with create database if not exists to ignore errors when the database already exists, and see updates after running.

List databases with show databases and inspect metadata with describe database, revealing location, owner, namespace, and comments, while data explorer offers access to LMS underscore bronze and LMS underscore silver.

Learn to create a database with an explicit location in Databricks SQL Warehouse, including permission checks, syntax patterns, and validating the new database and its metadata.

Learn how to drop empty databases in Databricks SQL warehouse using drop database with if exists to ignore missing databases, and cascade to drop non-empty databases along with their tables.

Explore how to alter databases in Databricks SQL Warehouse, including attempting to change location (not supported; drop and recreate), changing owner, and managing database comments and properties.

learn how to add comments when creating databases or schemas and how to alter comments on existing databases in databricks sql, using the official documentation.

Validate databases lms_bronze, lms_gold, and lms_silver, save the delta table script, and explore creating delta tables in Databricks SQL Warehouse using Spark SQL with CRUD operations.

Create a delta format table in Databricks SQL Warehouse using the LMS underscore silver database, with a users table featuring user_id int, user_first_name string, user_last_name string, and user_email string.

Understand how Databricks SQL defaults to delta format and how using clause or explicit location affects table type. Explore delta table metadata and dbfs paths for managed versus external tables.

Learn to create external tables in Databricks SQL using delta by specifying a location; external is implied, and the location folder is created if it doesn't exist, with proper permissions.

Drop an external table to delete its metadata only, then clean up the data folder using Databricks CLI or notebook, noting Unity Catalog may enable drop external location.

Learn how to manage data in a delta table using Databricks SQL, covering insert, select, update, and delete operations (DML/CRUD) with table metadata and basic queries.

Explore inserting records into a Databricks SQL warehouse table using the insert statement. Learn single and multi-record inserts for LMS_silver.users, handling null values and column order.

Learn to insert multiple records into a Databricks SQL warehouse table in a single insert statement, including cleanup with truncate, column order alignment, and validating results.

Update Databricks SQL warehouse tables by using update and set to modify single or multiple columns, with where conditions to target specific user IDs and handle nulls.

Learn to perform dml updates on a Databricks SQL warehouse table when columns are null, using is null and is not null to set last names to a placeholder LNU.

Drop the users table in LMS underscore silver, remove metadata and files in the warehouse, and verify the DBFS location via notebook or databricks CLI, with Unity Catalog options.

Learn to use the Databricks CLI to manage DBFS files, listing, copying, moving, and deleting, and prepare datasets for delta file formatted tables.

Create a folder in DBFS with the Databricks CLI by using mkdirs under file store to set up LMS_DL and copy the course catalog data from a local folder.

Copy local files into dbfs with databricks fs cp, using -r and profile options, create LMS_dl/course_catalog and verify via ls.

Copy folders into dbfs with Databricks fs cp and the overwrite option, then preview small text files with cat to inspect json data.

Review the course catalog json records stored in dbfs, identify their string attributes, and map them into course and instructor tables using delta format across bronze and silver layers.

Explore practical options to analyze data in Databricks by running queries against the file path, or via views, external tables, or managed tables in DBFS, with or without loading data.

Learn to run select queries directly from a DBFS path using from clause with backticks, validate data with JSON files, and explore using views and external tables in Databricks SQL.

Explore creating temporary views in Databricks SQL from JSON files via a path, and why temporary views may not power dashboards in SQL warehouse.

Create and query external tables in Databricks SQL by pointing to a DBFS path, using create external table, and noting JSON format limitations.

Learn how to query and process values in JSON string columns using Databricks SQL, with a focus on external tables, JSON processing functions, and building queries for data pipelines.

Practice basic SQL on the course_catalog data to retrieve distinct table names, count records per table, and apply group by with aliases in Databricks SQL.

Filter data with a where clause on the course_catalog table to retrieve records for instructors or courses, using star or explicit columns and noting case sensitivity.

Learn to validate Databricks SQL functions using select, experiment with current_date and current_timestamp, and leverage date_format with arguments to understand syntax and semantics.

Explore the record field in the course_catalog table, where each record is a JSON string with instructor or course attributes. Use from_json to parse these records by table schemas.

Learn to parse JSON strings in Databricks SQL with from_underscore_JSON, define schemas using struct, and extract fields like instructor_id and instructor_name for instructors and courses in the catalog.

Parse instructor and course records from json strings in the course_catalog table using from_json with the proper schema, enabling the query of course_id, instructor_id, and course_title.

Create permanent views named instructors underscore V and courses underscore V in LMS underscore bronze to process instructors and courses data; validate with select, show tables, and describe.

Create a delta table for the course catalog dataset from json files in dbfs under LMS bronze, using copy command and a table with table_name, record, and created_ts.

Use the input_file_name function in Databricks SQL and Spark SQL to view file names with data, alias to remove brackets, and copy command to load into course_catalog table in LMS_bronze.

Explore how the Databricks SQL copy into command loads data into delta formatted tables from files, detailing mandatory clauses, from and file format, with options for files or patterns.

Shows copying a single json file into a delta table with copy into, including syntax, from path, and json formatting; confirms five rows and duplicates avoided on re-run.

Copy data from multiple files into delta tables with the copy command after truncating the target table, and learn why loaded files are ignored and how to override this behavior.

Copy data from multiple files into Delta tables using pattern, while overriding default ignore behavior with copy options such as force and merge schema.

Create a Delta table with an extra created_ts column in Databricks SQL Warehouse, then populate it via copy into using a query that derives created_ts from current_timestamp, when structures differ.

Master copying data from files into delta tables with copy into, using from, file format, and optional pattern, while adding an created_ts audit column in the bronze layer.

Validate and query the bronze layer course_catalog table using from_json, extract instructor and course data from the record, and prepare for the silver layer with CTEs such as ICTE.

Learn to use the merge into syntax to insert or update data into delta tables from tables, views, queries, or cte, with when matched and when not matched conditions.

Create and manage delta tables in Databricks SQL by using merge and insert statements to populate LMS silver instructors and LMS bronze course_catalog, with conditional database and table creation.

Copy json data from dbfs into the lms_bronze.course_catalog delta table using a Databricks SQL copy command, validating with selects and row counts.

Filter the bronze course_catalog for instructors, convert JSON to a struct with from_json, explode to expose instructor_id and instructor_name, then insert into LMS_silver.instructors with created and updated timestamps.

Create a courses table in lms_silver, populate it from lms_bronze.course_catalog, and populate the lms_silver.instructors table; learn copy commands and timestamp columns for later joins with instructors.

Copy data from the new part file into the course_catalog table, validate nine records, and merge incremental instructor data into the instructors table in the LMS_silver database.

Learn how merge upserts update existing records and insert new ones in the instructors silver table, using key matches between the source results and the instructors data.

Develop a Spark SQL merge to insert the latest instructor records from the course_catalog into LMS_silver.instructors, using a subquery to fetch max of bl_created_ts from LMS_bronze.course_catalog.

Explore the merge statement syntax in Databricks SQL, merge into LMS_silver.instructors from a delta format source using an on clause, and update or insert when matched or not matched.

Merge data from the course catalog into LMS_silver.instructors using Databricks SQL, leveraging a CTE for the source and performing update and insert steps with validation.

Execute a merge statement to upsert course catalog data into the courses table using a cte, nested queries, and from_json for incremental data via the max created timestamp.