Databricks SQL and AI/BI for Data Analysts

Launch the Databricks SQL workspace, attach a running warehouse, and write your first query using ANSI SQL to explore the nyctaxi trips table, then save and organize it.

Learn to switch catalogs and schemas in Databricks SQL using a three level namespace. Use explicit catalog.schema.table references and use catalog or use schema commands to query across catalogs.

Schedule Databricks SQL queries to run at intervals—from every minute to monthly—by selecting all columns from the trips table in nyctaxi, then turn off the schedule to save costs.

Add non-executable text comments to SQL queries using two hyphens for single-line notes and /* ... */ for multi-line descriptions to document query logic and context.

Create catalogs using the Data Explorer or SQL, explore schemas and tables, manage storage locations, and view catalog details, privileges, and sample data.

Create schemas inside catalogs using the Data Explorer and SQL commands, define storage locations and permissions, then verify with show and describe commands.

Explore Databricks SQL data types, including numerical (int to bigint, float, double, decimal), string, date and time (date, timestamp, timestamp_ntz, interval), and boolean, with column-specific operations.

Compare managed and external tables in Databricks: managed tables store metadata and data and use Delta file format with ACID, while external tables keep data externally and manage only metadata.

Create managed tables in Databricks sql using create table and create table as, insert into, and three-level namespace, with if not exists and replace options.

This lecture explains creating a table in the hive_metastore, a workspace level catalog not governed by Unity Catalog, and contrasts its storage and governance with Unity Catalog.

Create managed tables using the Data Explorer in Databricks SQL, upload parquet data, and configure catalog and schema for Citibike datasets.

Create an external storage location in Databricks using Azure Data Lake Gen2 and a storage credential, then assign access via an access connector and test the connection.

Create external tables in databricks sql from csv and parquet files by linking an external storage location, using the using and location clauses, and handling csv headers.

Override unity catalog's default managed table storage by creating a schema with a dedicated external location, using a storage credential, and validating with a test table.

Learn how to remove all rows from a Databricks SQL table with the truncate table command, preserving the table schema, demonstrated using a truncate_demo table built from Citibike station_data.

Use the alter table command to rename a table, add a column, and drop a column, and follow the general syntax alter table table_name with the desired clauses.

Tidy up data objects by dropping tables, schemas, and catalogs using cascade, while noting soft deletes for managed tables after 30 days and metadata-only deletions for external tables.

Master using the select clause to query data, alias columns with as or backticks, and alias tables like t to prepare for joins in Databricks sql.

Use select distinct to retrieve unique values from a column or column pair, illustrated on the bike data table with start_station_id and end_station_id to avoid duplicates.

Explore Citibike data with Databricks sql using the jc_bike_data_22 table, noting a deliberately missing month for 2022 that will be completed in a future charts and dashboards exercise.

Filter records with the where clause in SQL, applying conditions on string, datetime, and numeric fields using operators like equal to, greater than, and not equal to.

Master SQL filtering with and, or, not, and between in the where clause to combine conditions, using examples on bike data and the importance of parentheses for precedence.

Master the IN and LIKE operators in SQL to filter records. Use IN for multiple start_station_id values and LIKE with wildcards to match patterns in station_name.

Delete records in Databricks with the delete from statement, optionally using a where clause. The example removes HB203 from the temp_station_data table and then drops the table.

Learn built-in Databricks SQL functions and their use across string, numeric, aggregate, date/time, and cast categories, with examples on literal values to practice syntax like the upper function.

Explore string functions in Databricks SQL—upper, lower, initcap, length, left, right, and concat—apply to member_casual and start_station_id and end_station_id, and learn aliasing and the double pipe alternative.

Demonstrate numerical functions by creating a two-column table with doubles, performing add, subtract, multiply, and divide, and applying round and abs operations on the results.

Use datediff to compute minutes between started_at and ended_at, then format dates with date_format patterns like 'MM-yy' or 'MMM-yy', and capture current_date and current_timestamp for auditing.

Convert strings to dates and timestamps in Databricks SQL using to_date and to_timestamp, specifying input formats like dd/MM/yy to obtain standard date and timestamp values.

Learn conditional functions in sql, including the if function with nested forms and the case expression, while creating a trip_duration table and classifying rides by trip_category.

Discover how aggregation functions in SQL compute sum, average, max, min, and count on the trip_duration dataset, and why group by matters.

Learn to use the group by clause in sql to group by ride_id or multiple columns, compute mean trip_duration with datediff, and create a ctas table for grouped results.

Learn to filter aggregated data with the having clause after grouping, distinguish it from the where clause, and understand the order of operations: where, group by, having.

Master inner, left, right, and full joins in Databricks SQL to combine data from two tables using a common customer id, demonstrating one-to-many relationships and nulls in results.

Demonstrates joining jc_bike_data_22 with station_data to map start_station_id and end_station_id to station_id, producing start_station_name and end_station_name using inner, left, right, and full joins with table aliases.

Sort results with order by after other clauses, using one or more columns; default is ascending, or use desc for descending, and apply limit for top records.

Master the sql order of execution, from the from and join clauses to limit, and learn how aliases affect where and order by behavior in Databricks.

Explore subqueries in Databricks SQL for data analysts, including nesting inside from and where clauses with parentheses and executing before the outer query.

Create views as virtual tables from underlying queries, with no data stored. Use the create view syntax to simplify queries, enable data security, and join multiple tables.

Explore set operators in Databricks SQL, combining two queries with union, intersect, and except. See how union blends oldest and latest records by started_at, while intersect shows overlaps and except excludes rows.

Create the vw_trip_duration view in course_project.citibike from jc_bike_data_22 to display ride_id, start_station_id, end_station_id, and trip_duration_mins. Compute trip duration with datediff(minute) between started_at and ended_at.

create a view vw_outliers in the course_project citibike schema from jc_bike_data_22 that lists ride_id, start_station_id, and end_station_id for trips with trip duration under 0 or over 2000 minutes using datediff.

Learn to compute monthly ride totals in databricks sql for data analysts by formatting started_at to year_month, grouping by that expression, counting rides, and ordering results.

Classify bike journeys by duration using a subquery and case expression in Databricks SQL for data analysts. Compute total rides by trip class (short, medium, long) from the jc_bike_data_22 dataset.

Join the jc_bike_data_22 and station_data tables, use concat to format start_station to end_station, then group and order by total_rides to reveal the most popular journeys.

Clean up unused tables and views in the citibike and citibike_ext schemas of the course project_catalog, retaining jc_bike_data_22 and station_data while deleting the rest in databricks sql for data analysts.

Explore the medallion architecture in a Databricks lakehouse, from bronze ingestion to silver conformance. Use gold tables for reporting, and apply last mile ETL as a data analyst.

Explore how the medallion architecture organizes citibike data into bronze, silver, and gold layers—from raw csv ingestion to delta format cleansing and last-mile etl with trip classification.

Explore Delta as the main data lakehouse file format, delivering ACID transactions, schema evolution, DML like merge into, audit history, time travel, and unified batch and streaming.

Explore how Delta Lake enables upserts with MERGE INTO between a source and target Delta table, handling updates, inserts, and deletes with when matched and not matched clauses.

Explore delta table audit history and time travel, viewing operations, user, timestamps, and metrics via the data explorer and describe history.

Explore how to view and filter query history, monitor performance in a SQL warehouse, and use query profiles to diagnose bottlenecks from execution time to I/O metrics.

Explore how caching speeds Databricks SQL queries by persisting intermediate results in memory or disk, including the Databricks SQL user interface cache, local and remote results caches, and disk cache.

Databricks SQL alerts monitor a scheduled query, flag negative trip durations in JC bike data, and notify email or Microsoft Teams when the minimum trip_duration_mins falls below zero.

Learn to create visualizations and dashboards in Databricks SQL for data storytelling, quick in-platform analysis and ad-hoc requests, and to complement Power BI with interactive dashboards.

In Databricks SQL, build your first bar chart from query results, showing average trip duration by rider type (member vs casual) and explaining dimensions and measures.

Learn to create line, area, and stacked area charts to visualize daily ride trends using timestamp data. Customize date level to day and compare member versus casual riders.

Learn to create a combo chart that compares monthly rides with average trip duration using dual left and right y-axes and a bar/line combination for six months in 2022.

Create and interpret pie charts in Databricks SQL to show the proportion of total rides by rider type and rideable_type for positive values. Understand chart limitations when comparing many sectors.

Learn to visualize relationships between two numeric measures with scatter plots and add a third dimension using bubble charts, illustrated by rides and total trip duration across start station IDs.

Learn to build and interpret a histogram of trip duration in minutes with the jc_bike_data_22 dataset, exploring bin widths and identifying the most common durations while handling outliers.

Learn to read box plots that reveal median, quartiles, and outliers, then build a box plot of trip duration by rider type and rideable type in Databricks SQL.

Learn to create heatmaps in Databricks SQL that map start and end stations to a count of rides, using color to reveal high-volume relationships and guide data exploration.

Create sankey charts in Databricks SQL by naming stage1 through stage5 and the measure as value to visualize the flow across 2–5 dimensions.

Use databricks table visualizations to display a count-aggregated table of ridetable_type and member_casual, with aliases like 'type of bike' and 'rider type', and enable search, formatting, and simple conditional coloring.

Create a pivot table in Databricks SQL to summarize jc_bike_data_22 results, using rideable_type and member_casual with a count aggregation, subtotals, formatting, and optional cell color, similar to Excel.

Master counter visuals in Databricks SQL to display total rides, target rides, total ride duration, and average ride duration. See how scalar values and filtering update counters in real time.

Explore the full range of visualization types in Databricks SQL, with linked documentation and side-panel resources guiding you through each chart type for deeper insights.

Explore data exploration in Databricks SQL with visualizations to track monthly rides and average trip duration, and assess variations by member status and ride type while identifying data quality issues.

Add the missing July 22 data to jc_bike_data_22 by loading a Parquet file into a new table, inserting its records, and validating with a bar chart of monthly trips.

Create a view vw_jc_bike_data_22 to join ride details with station names, exclude docked bikes and end_station_id 'null' values, saving time for upcoming demos.

Explore query filters in Databricks SQL to refine visualizations after execution, using drop-downs or where clauses to select docked, classic, or electric bikes, with multi select options and chart refresh.

Substitute runtime values into a query with double curly braces, offering more flexibility than filters, and configure parameter widgets with keywords, input types, and defining parameters at query execution.

Design dashboards in Databricks by combining visualizations and text to monitor key metrics. Add SQL data sets, build counters and combo charts, publish, and schedule refresh.

Define and deploy metric views to create a semantic layer over data in Unity Catalog, delivering a single source of truth, governance, and consistent metrics across dashboards and BI tools.

Replace the existing dataset with the city bike metrics metric view and add a table of start-to-end journey pairs ordered by trips, including Hoboken Terminal to Hoboken Avenue.

Build a seasonal analysis by rider type page with bike metrics, featuring total rides by member type KPI, heat maps by day and month, and monthly trends via Gini code.

Explore how to add parameters to Databricks SQL dashboards by parameterizing queries with start and end date ranges, then link widget and dashboard parameters to create interactive visuals.

Outline the main administrative roles in a Unity Catalog enabled Databricks account, including account administrators, metastore admins, workspace admins, and data owners who grant access and transfer ownership.

Create a new Azure user, assign the reader role to the Databricks resource group, and configure workspace access to demonstrate roles and permissions in Databricks.

Add a new user in the account console, assign them to groups, and grant workspace permissions, then manage metastore and object-level access for that user.

Manage workspace users through admin settings, assign admins, and control access to the Databricks platform and SQL warehouses. Learn how group entitlements govern access and cluster creation.

Learn how to control access to Databricks workspace objects, including folders, queries, alerts, dashboards, and sql warehouses, and determine user permissions to access and modify these objects.

Explore SQL warehouse access control in Databricks, assigning can use, is owner, or can manage to control viewing, starting, stopping, editing, and deleting a serverless warehouse.

Configure folder access control in Databricks by granting any permission level—from none to can manage—and enable users to view, run, edit, or manage dashboards and queries.

Explore query access control in Databricks SQL, detailing five permission levels: no permission, can view, can run, can edit, and can manage, and sharing options (run as owner or viewer) and how ownership affects access to catalogs, schemas, and tables.

Explore dashboard access control with five permission levels and how running as owner or viewer affects viewing, refreshing, and access to underlying queries.

Master Genie Space permissions, including Can View, Can Run, Can Edit, and Can Manage, and understand why Genie Space and Unity Catalog data permissions must both be granted.

Explore workspace object access control across warehouses, sql warehouses, folders, queries, and dashboards, and review the documentation for permission levels and related components like alerts.

Discover securable objects and privileges in Unity Catalog, from metastore to external locations, including create, use, select, and modify permissions for catalogs, schemas, tables, and views.

Learn to grant and revoke privileges to Unity Catalog objects using the Data Explorer UI, enabling users to access external locations and create external tables.

Learn how dynamic views redact sensitive data for specific user groups. Use is_account_group_member to gate ride_id access and create redacted views for pii data.

Grant and revoke privileges on catalogs, schemas, tables, and views in Unity Catalog. Learn to grant use catalog and use schema, and practice select across objects with inheritance.

Explore data discovery in Databricks using the global search bar and data explorer to locate tables, queries, dashboards, and more, and query information_schema across catalogs and the metastore.

Explore how data lineage tracks data flow from source to destination in Unity Catalog, with lineage graphs for tables and views and insights on permissions and 30 days retention.

Explore delta sharing in Databricks, an open protocol integrated with Unity Catalog that supports open sharing and Databricks-to-Databricks sharing of read-only tables with recipients across platforms.

Learn how to share data between two Databricks workspaces using Delta Sharing, including creating a recipient, sharing assets, creating a catalog in the recipient, and querying read-only tables.

Demonstrates using open delta sharing to share data from Databricks to Power BI by creating a delta share, adding citibike tables, and connecting with a credential file and bearer token.