End to End Data Engineering Project using Databricks Free Edition | Spark Declarative Pipelines

Summary

The video builds an end-to-end transportation data engineering project using Python, Spark, Databricks Free Edition, and Lakeflow Spark Declarative Pipelines (SDP). The fictional ride-hailing company Good Caps needs faster, city-specific analytics for regional managers. The project demonstrates how a declarative pipeline can reduce manual orchestration and support incremental data processing.

Architecture and data

• CSV files for cities and trips are uploaded to Amazon S3 and ingested into Databricks.
• Data moves through a medallion architecture:
  • Bronze: Raw source data with file-name and ingestion-time metadata.
  • Silver: Cleaned and validated data with renamed columns and processing timestamps.
  • Gold: A joined, analytics-ready trips dataset, plus city-specific views for regional managers.
• Unity Catalog organizes the project into bronze, silver, and gold schemas and is used to manage access.
• A generated calendar dimension adds date keys and fields such as year, month, quarter, weekday/weekend, and holidays for time-based analysis.

Databricks and SDP concepts covered

• Declarative vs. imperative programming: Imperative code specifies how to perform each step; declarative code describes the intended result and lets the pipeline framework manage execution and dependencies.
• Flows and targets: The tutorial explains materialized-view flows, append flows, Auto CDC flows, materialized views, streaming tables, sinks, and pipelines.
• Materialized views are used for batch-style dimension data such as city and calendar. Streaming tables support streaming use cases such as the trips fact table.
• Auto Loader reads newly arriving files from S3 without requiring users to manually move processed files into separate folders.
• Schema rescue and bad-record handling: The tutorial shows options for accommodating schema changes and capturing malformed records rather than simply failing on them.
• Expectations apply data-quality checks. The example tracks invalid values while allowing processing to continue; the video also describes drop-on-failure and fail-on-failure alternatives.
• Auto CDC handles inserts and updates using keys, including upsert behavior and ordering considerations. The tutorial demonstrates SCD Type 1 and explains how SCD Type 2 preserves historical versions.

Project implementation and results

1. Create a Databricks Free Edition account, project catalog, and bronze, silver, and gold schemas.
2. Create an S3 bucket, upload city and daily trip CSV files, and connect the bucket to Databricks.
3. Build bronze and silver processing for the city dimension, then generate the calendar dimension.
4. Ingest trips with Auto Loader into a bronze streaming table; process and validate them into a silver streaming table using Auto CDC.
5. Join trips, city, and calendar data into a gold fact view, then create city-specific views for regional managers.
6. Use Databricks Genie to ask natural-language questions that it translates into SQL, such as identifying the weekday with the highest passenger rating.
7. Switch the pipeline to continuous mode, upload a new daily file, and observe the new records flow through without reprocessing the full dataset.
8. Grant access to a regional view using role-based access control (RBAC), assigning a user to a city team rather than granting permissions individually.

The tutorial’s concluding review highlights three SDP benefits: less code, automatic orchestration and dependency management, and incremental processing. It compares a declarative silver-processing example of about 50 lines with an imperative equivalent of about 135 lines. The speakers present the work as a successful pilot, while cautioning that one experiment is not enough to justify replacing every existing pipeline.

Tutorial and guide topics

• Databricks Free Edition setup and project catalog/schema creation
• S3 bucket setup and Databricks connectivity
• Declarative versus imperative programming
• Bronze, silver, and gold pipeline implementation
• Calendar-dimension generation
• Auto Loader, streaming tables, and incremental loads
• Data validation and Auto CDC
• City-specific analytics views, Genie queries, and RBAC

The presenter says the project code is available through the video description and recommends using AI tools to help explain unfamiliar code and parameters.

Main speakers and sources

• Codebasics presenter/instructor: Leads the technical explanations and walkthrough.
• Tony Sharma, Peter Pande, and Bruce Hari: Fictional stakeholder characters used in the project scenario and closing review.
• Databricks documentation and product features: Referenced as the basis for the explanation of Lakeflow Spark Declarative Pipelines.