Week 13 - Big Data on Databricks
This page is optional. Nothing here is required for Week 13's learning goals or the assignment. Back to the Going Further overview.
In Workspace & Unity Catalog you opened raw_trips in Catalog Explorer and ran SQL yourself. On the same table page, Databricks also offers Genie: type a question in plain English, and it proposes SQL against the table you are viewing.
Catalog Explorer Sample data with Genie: question "What is the average trip distance?", suggested prompts, Show SQL, and a sample rows grid
What it is good for. Fast exploration when you do not remember a column name yet: "average trip distance", "trips with zero passengers", "trips per month". Genie reads the table schema (and often a sample of rows), drafts SQL, and can run it on the attached warehouse.
What it is not. It is not a substitute for the week's skills. The assignment still expects you to write PySpark and dbt yourself. Treat Genie like any other LLM helper: useful for a first draft, wrong often enough that you must check the SQL.
Habits if you try it:
hyf-dbt-warehouse first (Sample data needs compute, same as any catalog preview).Databricks SQL warehouses also support built-in AI functions powered by foundation models. You can execute LLM tasks directly inside SQL queries:
-- Classify sentiment or extract insights directly in Databricks SQL
SELECT
trip_id,
ai_generate_text(
concat('Summarize this feedback in 1 sentence: ', feedback_text),
'databricks-meta-llama-3-3-70b-instruct'
) AS summary
FROM hyf.dev_yourname.customer_feedback;
Other built-in AI functions include ai_embed (generates vector embeddings for vector search and RAG) and ai_classify (categorizes text into predefined labels).
<aside> 💡 Using AI to help: Genie and AI SQL functions stay inside Databricks and your grants, but the same caution applies as with external LLMs: do not paste secrets into prompts, and do not ship an answer you have not verified (⚠️ Ensure no tokens, connection strings, or PII are included in any prompt you copy elsewhere!).
</aside>
ai_generate_text, ai_embed, and model serving integration.In Workspace & Unity Catalog you learned that class tables are managed: Databricks owns the storage and you never pick an ADLS path. Unity Catalog also supports external tables. The difference is who owns the files underneath. You do not need this for the required path or the assignment; every table you touch this week is managed.
flowchart TB
subgraph managed["Managed table"]
mmeta["Unity Catalog entry<br/>hyf.nyc_yellow.raw_trips"]
mfiles["Files Databricks owns<br/>(location chosen for you)"]
mmeta --- mfiles
mdrop["DROP TABLE<br/>removes entry + files"]
mfiles --- mdrop
end
subgraph external["External table"]
emeta["Unity Catalog entry<br/>(pointer only)"]
efiles["Files you own on ADLS<br/>abfss://…/trips/"]
emeta --- efiles
edrop["DROP TABLE<br/>removes entry · files stay"]
efiles --- edrop
end
classDef box fill:#e8f0fe,stroke:#4285f4,color:#111;
class mmeta,mfiles,mdrop,emeta,efiles,edrop box;
A managed table means Databricks owns the storage. You create hyf.nyc_yellow.raw_trips (or write hyf.dev_yourname.manhattan_payment_summary from a notebook). Databricks picks the file location. If you DROP TABLE, the metadata and the Parquet/Delta files go away. That is what you want for marts and class work: create, query, drop, no storage paths to manage.
An external table is metadata that points at files you already control, for example a Delta folder in ADLS Gen2 such as abfss://[email protected]/trips/. DROP TABLE removes the catalog entry but leaves those files on the lake. Use that when another system (Spark job, ADF, another engine) already writes the lake files and Databricks should only register them as a table, or when you must keep the bytes after dropping the table name.
| Use a managed table when… | Use an external table when… |
|---|---|
| You create and own the table inside Databricks (this week's notebooks and dbt) | Files already live on the lake and other tools write them |
| Dropping the table should clean up storage too | You must keep the files after DROP TABLE |
| You do not want to manage ADLS paths | You need an explicit lake path for compliance or multi-engine access |
Once tables exist, a real data team has to answer questions a query alone cannot: who is allowed to read this, which columns hold personal data, and if a dashboard looks wrong, which upstream table is to blame? On Databricks the governance layer that answers them is Unity Catalog. As Workspace & Unity Catalog established, it is the shared namespace behind every catalog.schema.table name you used this week; dbt on Databricks relied on the same catalog so your models could read hyf.nyc_yellow.raw_trips and write to your own schema.
Every table you referenced this week had a three-part name: a catalog (top-level container), a schema (a group of tables), and the table itself. For example, hyf.nyc_yellow.raw_trips is the raw_trips table, in the nyc_yellow schema, in the hyf catalog. Defining access and governance once against this shared namespace is cleaner than doing it per-notebook or per-cluster. Access is controlled with SQL grants:
GRANT SELECT ON TABLE hyf.nyc_yellow.raw_trips TO `students`;
In addition to managed and external tables, Unity Catalog supports Volumes for governance over non-tabular files (CSV landing files, raw JSON dumps, PDF documents, or machine learning model artifacts):
/Volumes/hyf/dev_yourname/raw_files/). Dropping the volume deletes the files.abfss://...). Dropping the volume leaves the files on the lake.Volumes replace legacy DBFS (dbfs:/) paths with full Unity Catalog ACL permissions (GRANT READ VOLUME ON VOLUME ... TO students).
Because every read and write goes through the catalog, Databricks records lineage automatically: which tables and notebooks produced each table, and which consume it. When a dashboard number looks wrong, you follow the lineage graph back to the source instead of grepping code. Your fct_trips traces back through stg_trips to raw_trips, the same DAG dbt builds.
Unity Catalog also lets you tag columns holding personally identifiable information (PII):
ALTER TABLE shared.analytics.customers
ALTER COLUMN email SET TAGS ('pii' = 'email');
Governance sounds like the least exciting part of the job, which is exactly why it is disproportionately valuable: most self-taught engineers skip it, so being the person who can control access, prove lineage, and locate PII is a fast way to become the person a team trusts with production data.
<aside>
📝 Optional assignment bonus (Medium): Assignment Task 4 asks for SHOW GRANTS on your fct_trips, a lineage screenshot, and a sample ALTER TABLE ... SET TAGS statement for a hypothetical PII column (write the SQL; you do not have to run the tag). Put notes and screenshots under task-4/. An empty grants list on a personal schema is still a valid observation if you say so.
</aside>
GRANT and REVOKE reference.In production, tables change over time. Data pipelines can break silently: a source column changes data type, null values spike, or distributions shift dramatically without throwing a hard SQL error.
Lakehouse Monitoring attaches to any Delta table in Unity Catalog and automatically computes statistical profiles, data freshness metrics, and data quality drift over time:
The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 https://hackyourfuture.net/
Built with ❤️ by the HackYourFuture community · Thank you, contributors
Found a mistake or have a suggestion? Let us know in the feedback form.