Pipeline Architecture — How It Works

Components

• Activities
• Data sources
• Data destinations
• Parameters
• Variables
• Control flow
• Monitoring

Common Pipeline Patterns

• Full load
• Incremental load
• CDC load
• Metadata‑driven load
• Multi‑source ingestion
• Multi‑layer ingestion

Pipeline → Lakehouse Flow

Source → Pipeline → Bronze → Silver → Gold

Pipelines vs Dataflows — When to Use What

Use both — they complement each other.

Building Bronze Ingestion with Pipelines

Best Practices

• Use copy activity
• Add ingestion metadata
• Store raw files in /Files/Bronze
• Convert to Delta
• Use incremental logic
• Use parameterized pipelines

Common Bronze Sources

• SQL databases
• APIs
• ERP systems
• CRM systems
• S3/ADLS shortcuts
• Event Streams

Bronze ingestion must be reliable above all else.

Building Silver Transformations with Dataflows Gen2

Best Practices

• Standardize column names
• Deduplicate
• Type‑cast
• Flatten JSON
• Join reference tables
• Validate data quality

Dataflows Gen2 make Silver accessible to business users — no Spark required.

Metadata‑Driven Pipelines — The Enterprise Pattern

Metadata‑driven pipelines use configuration tables to control ingestion — no hard‑coded logic, just parameters.

Benefits

• No hard‑coded logic
• Easy to add new sources
• Easy to modify ingestion
• Easy to scale
• Easy to govern

Metadata Examples

• Source system
• Table name
• Incremental column
• Destination path
• Partitioning rules

Metadata‑driven ingestion is the enterprise standard — it scales infinitely without touching pipeline code.

Error Handling & Monitoring

Best Practices

• Use retry logic
• Use failure notifications
• Use logging tables
• Use monitoring dashboards
• Use pipeline run history

Reliable ingestion requires strong monitoring. Without it, silent failures go undetected until dashboards are already broken.

Dev/Test/Prod Strategy

Best Practices

• Separate workspaces for each environment
• Parameterize pipelines for environment switching
• Use deployment pipelines
• Use version control
• Validate transformations before promoting

Ingestion must be stable across environments. Dev pipelines that work only in Dev are not production-ready.

2. Z‑Order — Optimize Columnar Reads

Z‑Order sorts data files based on column values, co-locating related data so queries scan fewer files.

Best Columns for Z‑Order

• Date
• CustomerID
• ProductID
• Region

Benefits

• Faster queries
• Better pruning
• Less data scanned
• Higher Direct Lake performance

Z‑Order is essential for Gold tables. Use it on your most-filtered columns.

3. File Compaction — Avoid Tiny Files

Tiny files kill performance. Each small file means an extra metadata lookup, an extra read, extra overhead.

Causes of Tiny Files

• Streaming ingestion
• Small batch loads
• Frequent merges

Benefits of Compaction

• Faster queries
• Better Z‑Order effectiveness
• Better partition pruning
• Lower cost

Compaction is mandatory for large tables receiving frequent writes. Schedule it regularly.

4. Vacuum — Clean Up Old Files

Vacuum removes old Delta files that are no longer referenced by the transaction log.

Benefits

• Lower storage cost
• Faster queries
• Cleaner metadata

Vacuum regularly — but be mindful of your time travel retention window. Default is 7 days; don’t vacuum below that unless you know what you’re doing.

5. Schema Evolution — Handle Changes Safely

Delta Lake supports schema evolution — your tables can grow and change without breaking downstream consumers, if you do it carefully.

Best Practices

• Add columns carefully
• Avoid dropping columns
• Use merge with schema evolution
• Validate schema changes before promoting to Silver or Gold

Schema evolution keeps pipelines stable. Unplanned schema changes are one of the most common causes of downstream breakage.

6. Merge Optimization — High‑Performance Upserts

Merges are expensive. A naive merge on a large unpartitioned table can take hours. Optimized merges take minutes.

Best Practices

• Partition merge tables
• Use incremental loads — only merge changed records
• Use surrogate keys for merge conditions
• Keep merge conditions narrow and selective

Optimized merges reduce compute cost dramatically — often by 10x or more on large tables.

7. Star Schema — The Key to Gold Performance

Star schema improves everything downstream — SQL endpoint performance, Direct Lake performance, BI performance, and query simplicity.

Components

• Fact tables
• Dimension tables
• Surrogate keys

Star schema is mandatory for Gold. Don’t expose wide flat tables to Power BI — model them properly.

8. Aggregation Tables — Boost BI Performance

Aggregation tables pre‑compute totals, counts, averages, and KPIs so Power BI doesn’t have to scan the full fact table on every query.

Benefits

• Faster dashboards
• Lower compute cost
• Better Direct Lake performance

Aggregation tables are essential for enterprise BI at scale.

9. Direct Lake Optimization

Best Practices

• Use Gold tables — never point Direct Lake at Bronze or Silver
• Partition fact tables by date
• Apply Z‑Order on frequently filtered columns
• Run compaction regularly
• Use star schema
• Use aggregation tables for high-traffic measures

Direct Lake performance depends entirely on Delta optimization. A poorly optimized Gold table means slow dashboards regardless of compute capacity.