sdp-basics

SDP Pipeline Basics

Always build SDP pipelines using SQL (not Python). When creating or modifying tables in SDP pipelines, follow these rules.

For copy-paste SQL patterns (bronze CREATE, audit columns, data-quality flag), read sql-templates.md.

Table Naming

All table names MUST use lowercase_snake_case with a layer prefix:

Layer	Prefix	Example
Bronze	bronze_	bronze_transactions
Silver	silver_	silver_customers
Gold	gold_	gold_daily_revenue

Never use PascalCase, UPPERCASE, kebab-case, or camelCase. Never omit the layer prefix.

Table Types

Type	When to Use	Syntax
STREAMING TABLE	File ingestion (Auto Loader), CDC, real-time data	CREATE OR REFRESH STREAMING TABLE
MATERIALIZED VIEW	Batch data from existing Delta tables, aggregations	CREATE OR REFRESH MATERIALIZED VIEW

Do not use CREATE OR REFRESH LIVE TABLE (deprecated) or the LIVE. virtual schema. In default publishing mode, LIVE. is ignored. Reference other datasets in the same pipeline by unqualified table name (pipeline catalog/schema) or a fully qualified catalog.schema.table. See LIVE schema (legacy).

Comments

Every table MUST have a COMMENT clause describing its purpose:

Layer	Pattern
Bronze	COMMENT "Raw <entity> data from <source>"
Silver	COMMENT "Cleaned and validated <entity> with derived metrics"
Gold	COMMENT "Business aggregation for <use case>"

Table Properties

Every table MUST have TBLPROPERTIES with at least quality (add domain where known). Do not set "owner" -- Databricks reserves that key and raises an error. Table ownership is the pipeline run-as identity (reserved table property keys).

TBLPROPERTIES ("quality" = "bronze", "domain" = "finance")
TBLPROPERTIES ("quality" = "silver", "domain" = "finance", "delta.enableChangeDataFeed" = "true", "delta.enableRowTracking" = "true")
TBLPROPERTIES ("quality" = "gold", "domain" = "finance", "delta.enableChangeDataFeed" = "true")

Audit Columns

Every table MUST include these two columns as the LAST columns in the SELECT. See sql-templates.md for the exact expressions.

Data Quality Constraints

• Bronze: Use WHERE clause filtering only (preserve raw data)
• Silver: Use CONSTRAINT clauses for validation
  • ON VIOLATION FAIL UPDATE for critical fields
  • ON VIOLATION DROP ROW for non-critical fields
• Gold: Generally no constraints (data validated in Silver)

Data Quality Flag

Silver tables MUST include a data_quality_flag column. Use the CASE pattern in sql-templates.md.

SQL Formatting

Element	Case
SQL keywords	UPPERCASE (SELECT, FROM, WHERE, AS)
Table/column names	lowercase_snake_case
Aliases	short lowercase (t, a, d)
Functions	UPPERCASE (ROUND(), CAST(), COALESCE())

Column Organization Order

1. Identifiers (primary keys, foreign keys)
2. Dimensions (categories, hierarchies)
3. Measures (quantities, amounts)
4. Derived/calculated fields
5. Data quality flags
6. Audit columns LAST (audit_timestamp, source_system)

Clustering

Add CLUSTER BY AUTO for STREAMING TABLE definitions.

Joins

• Reference other datasets in the same pipeline by table name (FROM bronze_articles), not LIVE.bronze_articles
• Use fully qualified names for tables outside the pipeline
• Read streaming sources with the STREAM keyword (FROM STREAM read_files(...) or FROM STREAM source_table). Do not use STREAM when creating a materialized view.
• Always use explicit JOIN syntax with table aliases