Articles on data engineering, cloud platforms, and software development.
Lakehouse Federation: Query External Databases Without Data Movement
lakehouse-federation
data-engineering
databricks
Lakehouse federation enables organizations to query external databases directly from a lakehouse without copying or moving data. It provides unified analytics across databases, warehouses, and cloud platforms using a single SQL interface.Why Lakehouse Federation MattersTraditional analytics requires ETL pipelines to move data into a centralized platform. With federation, data remains in the source system while still being available for analytics.Key BenefitsNo data duplicationReal-time access to external dataReduced ETL complexityFaster cross-platform analyticsCentralized governance and securityCommon External Databases SupportedMany lakehouse platforms support federation with:PostgreSQLMySQLOracleMicrosoft SQL ServerSnowflakeHow to Implement Lakehouse FederationStep 1: Choose a Lakehouse PlatformPopular federation-enabled platforms include:DatabricksSnowflakeTrinoStarburstStep 2: Configure External Database ConnectionConnect the external database using JDBC or native connectors.Typical configuration includes:HostnamePortDatabase nameAuthentication credentialsSSL/security settingsStep 3: Create a Federation ConnectionCREATE CONNECTION postgres_connTYPE POSTGRESQLOPTIONS ( host 'db.company.com', port '5432', database 'sales');Step 4: Create an External CatalogCREATE FOREIGN CATALOG sales_dbUSING CONNECTION postgres_conn;Step 5: Query External TablesSELECT customer_id, order_totalFROM sales_db.ordersWHERE order_total > 1000;This query directly accesses data from an external PostgreSQL database through the lakehouse federation layer.Performance Best PracticesTo improve federated query performance:Use predicate pushdownSelect only required columnsAvoid large cross-system joinsCache frequently accessed datasetsSecurity and GovernanceImplement:Role-based access control (RBAC)Encrypted database connectionsSecret managementAudit logging
Slowly Changing Dimensions (SCD) in Data Engineering: Types 0 to 6 Explained
scd
data-engineer
data-science
In the world of data engineering, data is rarely static. Customer addresses change, employees move between departments, products get reclassified, and business rules evolve over time.The challenge is: how do we store these changes while keeping historical data accurate?This is where Slowly Changing Dimensions (SCD) comes into play.What is a Slowly Changing Dimension (SCD)?A Slowly Changing Dimension is a data warehousing technique used to manage changes in dimension tables over time.Dimension tables contain descriptive information such as:Customer detailsProduct informationEmployee recordsLocation attributesFor example, consider a customer table:Customer IDCustomer NameCity101JohnNew YorkIf John moves to Chicago, we need to decide:Should we overwrite the old city?Should we keep the old city for historical reporting?Should we store both current and previous values?SCD types define how these changes are handled.SCD Type 0 — Fixed DimensionSCD Type 0 means no changes are allowed.The original value is preserved permanently.Example:Customer ID | Name | Birth Date101 | John | 15-Jan-1990Even if corrections are received later, the value remains unchanged.Common Use CasesDate of birthOriginal customer registration dateHistorical reference attributesSCD Type 1 — Overwrite Existing DataIn SCD Type 1, the old value is replaced with the new value.Before:Customer ID | Name | City |101. | John | NewYork |After:Customer ID | Name | City | 101. |John | Chicago|The previous value is lost.AdvantagesSimple implementationLess storage requiredFaster queriesDisadvantagesNo historical trackingCommon Use CasesFixing incorrect dataCorrecting spelling mistakesSCD Type 2 — Add New Record (Most Popular)SCD Type 2 maintains complete history by inserting a new row whenever a change occurs.Example:Before change:Surrogate Key | Customer ID | Name | City | Start Date | End Date | Current Flag1 | 101 | John | New York | 01-Jan-2024| 31-Dec-2024| NAfter customer moves to Chicago:Surrogate Key | Customer ID | Name | City | Start Date | End Date | Current Flag1 | 101 | John | New York | 01-Jan-2024| 31-Dec-2024| N2 | 101 | John | Chicago | 01-Jan-2025| NULL | YInstead of updating the old row, we:Expire the old record.Insert a new record.Mark the new record as active.AdvantagesComplete historical trackingSupports accurate reportingDisadvantagesMore storageMore complex ETL logicCommon Use CasesCustomer address historyEmployee department changesProduct category changesSCD Type 3 — Add Previous Value ColumnsSCD Type 3 stores limited history by adding extra columns.Example:Customer IDCurrent CityPrevious City101ChicagoNew YorkOnly a limited number of changes can be tracked.AdvantagesSimple queryingEasy comparison between current and previous valuesDisadvantagesCannot maintain complete historySCD Type 4 — Separate History TableSCD Type 4 separates current and historical data.Current DimensionCustomer IDNameCity101JohnChicagoHistory TableCustomer IDCityChange Date101New York2025–01–01The main table stores current data, while the history table stores previous versions.AdvantagesKeeps the main dimension smallerEasy access to historical recordsSCD Type 5 — Type 4 + Type 1 CombinationSCD Type 5 combines:Type 4: Separate history trackingType 1: Overwrite current valuesThe current dimension always contains the latest value, while historical changes are stored separately.Current DimensionCustomer IDNameCurrent Segment101JohnPremiumHistory TableCustomer IDSegmentStart DateEnd Date101Standard2024–01–012025–01–01101Premium2025–01–02NULLWhen to UseApplications need fast access to current data.Analysts need historical tracking.SCD Type 6 — Hybrid Approach (1 + 2 + 3)SCD Type 6 combines:Type 1: Current value overwriteType 2: Historical rowsType 3: Previous value columnsExample:Customer IDCurrent CityPrevious CityStart DateEnd Date101ChicagoNew York2025–01–01NULLIt provides both current-state reporting and historical analysis.SCD Types ComparisonSCDWhich SCD Type is Most Common?In modern data warehouses, SCD Type 2 is the most widely used approach because businesses often need historical analysis.For example:“What was the customer’s location when they placed an order six months ago?”A Type 1 dimension cannot answer this question because it only stores the latest value.
How I Passed the Databricks Data Engineer Professional Certification
databricks-certification
databricks
databricks-data-engineer
certificateWhy the Data Engineer Professional Certification?As a data engineer working with Databricks daily, I wanted to:Master modern data engineering patterns (Medallion Architecture, Lakeflow)Understand advanced topics like Liquid Clustering and Deletion VectorsValidate expertise across the entire Databricks stackPosition myself as a subject matter expert on my teamThe Data Engineer Professional certification proved to be the right step — it covers real-world, production-grade data engineering skills that directly impact how organizations build data platforms.SQL Fundamentals & DataFramesDays 1–2: Exam Blueprint & Spark SQLDay 1: Review the full exam blueprint at databricks.com/learn/certification/data-engineer-professionalDay 2: Master Spark SQL basics (SELECT, WHERE, GROUP BY, HAVING)Days 3–5: DataFrames & JoinsBuild strong DataFrame fundamentals — transformations and actions are everywhere on the examLearn all join types deeply (inner, left, right, full outer, cross)Study broadcast joins vs shuffle joins and their performance implicationsDays 6–7: Window Functions & UDFsPractice window functions (Rank, Dense Rank, Lag, Lead, Row Number)Understand Python UDFs and Pandas UDFs — these appear frequently in scenario questionsHands-on: Create small PySpark projects using each technique. Don’t just read — build.Data Ingestion & StreamingDays 8–9: Auto Loader MasteryDay 8: Basics of cloudFiles and Auto LoaderDay 9: Schema evolution and rescue data — critical for real-world ingestion pipelinesDay 10–11: Structured StreamingUnderstand stream processing fundamentalsMaster checkpointing for exactly-once processing semanticsThis is a major exam topic — dedicate time hereDays 12–15: Lakeflow & Declarative PipelinesLearn Lakeflow fundamentals (Databricks’ declarative pipeline framework)Dive into Streaming Tables conceptsUnderstand Materialized Views and AUTO CDC APIsLakehouse Architecture & Advanced DeltaDay 16: Medallion ArchitectureBronze-Silver-Gold layers aren’t just theory — understand how to design themStudy data quality implications at each layerDay 17: Delta Lake Core ConceptsACID properties of Delta LakeTime Travel and transaction historyVersion control for data — this is a differentiator from other platformsDays 18–19: Data Quality & Delta SharingExpectations and Constraints for data validationDelta Sharing (both Databricks-to-Databricks and Open Sharing)Real-world governance scenariosDays 20–22: Advanced Federation & MonitoringLakehouse Federation: Query external systems seamlesslySystem Tables for cost, audit, and usage monitoringEvent Logs for Lakeflow monitoring and troubleshootingOptimization, Security & ReviewDays 23–25: Query Performance & Delta OptimizationQuery Profile and Spark UI — learn to read execution plansOPTIMIZE and file compactionLiquid Clustering — modern data layout strategy (newer but important)Days 26–27: Advanced Delta FeaturesDeletion Vectors for efficient updates/deletesChange Data Feed (CDF) for incremental processingThese enable modern data pipeline patternsDays 28–29: Unity Catalog & SecurityGovernance fundamentals with Unity CatalogRow filters and column masks for fine-grained access controlSecurity is increasingly weighted on Databricks examsDay 29: CI/CD & DeploymentDeclarative Automation Bundles, CLI, and Git integrationHow professional teams deploy to productionDay 30: Full Revision & Mock ExamRevisit weak areas from your practice testsTake a full-length timed mock examReview every incorrect answer in detailOfficial Documentation ResourcesUnlike the Associate exam, the Professional exam tests deep knowledge across the entire platform. Here are the official docs you’ll reference constantly:Primary Study Resource: AI Prep GuideDatabricks provides an official AI Prep Guide specifically designed for certification preparation. I used this extensively:AI Prep Guide: Any Databricks CertificationThis guide is invaluable because it’s created by Databricks engineers and aligns exactly with exam objectives. Rather than just reading it passively, I built a preparation agent based on the guide’s structure — creating a practical tool that reinforced learning while building real skills.Practice Questions: 200+ Question Quiz BankI have created a small website for quiz for quick revision.🎯 Databricks DE Professional Practice Quiz — 200 questions covering all exam domainsCore Study Materials📘 Spark SQL Documentation📘 Spark DataFrames Programming Guide📘 Auto Loader Documentation📘 Structured Streaming Guide📘 Lakeflow & Declarative Pipelines📘 Delta Lake Fundamentals📘 Medallion Architecture📘 Lakehouse Federation📘 Unity Catalog & Governance📘 Databricks Bundles & CI/CDAdvanced TopicsLiquid Clustering: https://docs.databricks.com/en/delta/clustering.htmlDeletion Vectors & CDF: https://docs.databricks.com/en/delta/deletion-vectors.htmlDelta Sharing: https://docs.databricks.com/en/delta-sharing/index.htmlRow Filters & Column Masks: https://docs.databricks.com/en/data-governance/unity-catalog/filters-and-masks/System Tables: https://docs.databricks.com/en/admin/system-tables/index.htmlQuery Profile: https://docs.databricks.com/en/sql/user/queries/query-profile.htmlWhat Worked: The Secret IngredientsResource Combination That WonThe magic wasn’t any single resource — it was combining three things:The Official AI Prep Guide — Strategic direction (what to study)The 200-Question Practice Quiz — Tactical feedback (what I’m weak on)Official Databricks Docs — Deep knowledge (understanding the “why”)The official guide tells you what to learn. The practice quiz tells you what you haven’t learned yet. The docs tell you why it matters.Study Strategies That Worked for the Professional ExamBuild a Prep Agent (Meta Learning)Here’s something I did that most people don’t: I created a preparation agent based on the official AI Prep Guide.Why this worked:Forced me to structure the material (building an agent requires understanding relationships between topics)Created a reusable study tool (I could prompt it with questions throughout the month)Reinforced learning through building (the best way to learn is to create)Stayed engaging (studying is tedious; building is fun)What the agent does:Takes any topic and breaks it into study questionsSuggests related topics and their connectionsRecommends which official docs to reviewProvides practice scenarios for hands-on workTracks study progress against exam domainsFollow a Structured Day-by-Day PlanDon’t randomly jump between topics. The plan above is deliberately sequenced:Days 1–7 give you SQL/DataFrame foundations needed for everything elseDays 8–15 show how data gets into the LakehouseDays 16–22 teach enterprise architecture patternsDays 23–30 focus on optimization and production-grade practicesEach day builds on previous days. Skipping around creates gaps.Use the Practice Quiz for Continuous FeedbackThis is critical: Don’t wait until the end to assess yourself. The Databricks DE Professional Practice Quiz with 200 questions gives you:Topic-specific feedback: Identify exactly which domains need more workRealistic question format: Mirrors the actual exam styleEarly warning system: If you score 40% on monitoring questions now, you’ll know to adjust (I learned this the hard way!)Confidence building: Watching your scores improve day-by-day is motivatingHow to use it:Days 8–15: Quiz after each ingestion/streaming topicDays 16–22: Quiz after each architecture sectionDays 23–30: Full quiz simulations under timed conditionsHands-On for Every TopicThe Professional exam tests application, not memorization. For each topic:Write PySpark codeCreate actual Delta tablesBuild a streaming pipelineRun queries and read the Spark UIBreak things intentionally to understand what goes wrongExample: Don’t just read about Liquid Clustering. Create a table with it, run queries against it, compare performance to a non-clustered table.Focus on Real-World ScenariosThe Professional exam loves questions like:“Design a medallion architecture for an e-commerce platform”“A streaming pipeline is losing data. What went wrong?”“Optimize this query that’s scanning 50TB unnecessarily”These require understanding why things work, not just what they do.Practice with Mock Exams StrategicallyFirst mock (after Day 15): Identifies gaps mid-waySecond mock (after Day 25): Shows readinessThird mock (Day 29–30): Final confidence checkPro tip: Use the Databricks DE Professional Practice Quiz — 200 questions covering all exam domains. This resource alone can replace expensive third-party prep courses.Review every wrong answer. Don’t just move on.Leverage Your Day JobApply certification topics to real projects:Redesigning ingestion pipelines → Days 8–9Optimizing slow queries → Days 23–24Implementing governance → Days 28–29Real experience is your secret weapon.The 80/20 Exam FocusProfessional exam topic weights (approximate):Ingestion & Streaming (20%): Days 8–15Data Architecture & Delta (20%): Days 16–22Query Optimization (15%): Days 23–25Governance & Security (15%): Days 27–28Advanced Features (15%): Days 26, 29Foundational SQL (15%): Days 1–7Spend more time on the heavy topics, but don’t neglect the rest.
A Complete Guide to dbt Snapshots: Track Historical Data with Slowly Changing Dimensions (SCD Type…
scd-type-2
dbt
snapshot
A Complete Guide to dbt Snapshots: Track Historical Data with Slowly Changing Dimensions (SCD Type 2)If you’ve ever built a data warehouse, you’ve probably faced this question:How do you preserve the history of records that keep changing?Imagine a customer changes their address, a product price increases, or an employee moves to a different department. Most operational databases simply overwrite the old values with the new ones. While this works for transactional systems, it’s a major problem for analytics.Questions like these become impossible to answer:Where did this customer live six months ago?What was the product price during last year’s holiday sale?Which department was an employee in when a transaction occurred?This is exactly the problem dbt Snapshots are designed to solve.In this article, you’ll learn how dbt snapshots work, how they implement Slowly Changing Dimensions (SCD Type 2), how to configure them, and the performance considerations you should keep in mind when using them in production.What Are dbt Snapshots?A snapshot is a feature in dbt that captures changes in mutable source tables over time.Instead of updating an existing row when data changes, dbt creates a new version of the record while preserving the previous version.For example, suppose your source table initially contains the following data:customer_id | customer_name | city 101 | Alice. | LondonA month later, Alice moves to Paris.The source table now looks like this:customer_id | customer_name | city 101 | Alice. | ParisWithout snapshots, the original value (“London”) is gone forever.With dbt snapshots, the historical record is retained.customer_id | city | dbt_valid_from | dbt_valid_to 101 | London| 2025-01-01 | 2025-02-01 101 | Paris | 2025-02-01 | NULL The latest version always has a NULL value for dbt_valid_to, indicating that it is the current active record.Understanding Slowly Changing Dimensions (SCD Type 2)A Slowly Changing Dimension (SCD) is a data warehousing technique used to manage historical changes in dimension tables.There are multiple SCD types, but Type 2 is by far the most common because it preserves every historical version of a record.Instead of updating an existing row, SCD Type 2 inserts a new one.For example:Product | Price | Valid From | Valid ToLaptop | $999 | Jan 1 | Mar 15Laptop | $1,099| Mar 15 | NULLBoth versions remain available for historical analysis.This allows analysts to answer questions such as:What was the product price at the time of purchase?How many customers changed regions this year?Which employee managed an account during a specific month?Fortunately, dbt snapshots automatically implement this SCD Type 2 pattern without requiring you to write complex SQL.Snapshot Strategies in dbtdbt supports two strategies for detecting changes.1. Timestamp Strategy (Recommended)The timestamp strategy relies on an updated_at column in your source table.Whenever the timestamp changes, dbt assumes the record has changed and creates a new snapshot version.Example configuration:{% snapshot customers_snapshot %}{{ config( target_schema='snapshots', unique_key='customer_id', strategy='timestamp', updated_at='updated_at' )}}select *from {{ source('crm','customers') }}{% endsnapshot %}Why it’s recommendedThe timestamp strategy offers several advantages:Faster executionLess computationSimpler configurationBetter scalabilityIdeal for large production datasetsAs long as your source system maintains a reliable updated_at column, this should be your default choice.2. Check StrategyNot every source table includes an update timestamp.In such cases, dbt provides the check strategy.Instead of monitoring timestamps, dbt compares one or more columns.{{config( strategy='check', unique_key='customer_id', check_cols=['name','city'])}}Whenever either the customer’s name or city changes, dbt creates a new snapshot record.When should you use it?Use the check strategy only when no trustworthy timestamp exists.Keep in mind that it requires dbt to compare column values, making it slower than the timestamp strategy — especially for large tables.Metadata Columns Added by dbtEvery snapshot table automatically includes metadata columns.ColumnPurposedbt_valid_fromStart of record validitydbt_valid_toEnd of record validitydbt_updated_atTimestamp used for change detectiondbt_scd_idInternal snapshot identifierThese columns enable point-in-time analysis and simplify historical reporting.Querying Current RecordsTo retrieve only the latest version of every record:SELECT *FROM snapshots.customers_snapshotWHERE dbt_valid_to IS NULL;Querying Historical RecordsTo view the history of a specific customer:SELECT *FROM snapshots.customers_snapshotWHERE customer_id = 101ORDER BY dbt_valid_from;This returns every version of the customer’s record in chronological order.Point-in-Time QueriesOne of the biggest advantages of snapshots is the ability to reconstruct data exactly as it existed on a particular date.SELECT *FROM snapshots.customers_snapshotWHERE dbt_valid_from <= '2025-03-15'AND ( dbt_valid_to > '2025-03-15' OR dbt_valid_to IS NULL);This capability is invaluable for financial reporting, compliance, audits, and reproducible analytics.Configuring dbt SnapshotsA typical snapshot configuration looks like this:{{config( target_schema='snapshots', unique_key='customer_id', strategy='timestamp', updated_at='updated_at')}}Here’s what each parameter controls:target_schema — Where snapshot tables will be stored.unique_key — The business key that uniquely identifies each record.strategy — Either timestamp or check.updated_at — Timestamp column used with the timestamp strategy.check_cols — Columns to compare when using the check strategy.Running SnapshotsCreating snapshots is straightforward.Run every snapshot in your project:dbt snapshotOr execute a specific snapshot:dbt snapshot --select customers_snapshotDuring every execution, dbt compares the current source data with previously captured records and inserts new versions whenever changes are detected.Performance ConsiderationsSnapshot tables grow continuously as more historical versions are added. Keeping them efficient requires a few best practices.1. Prefer the Timestamp StrategyTimestamp comparisons are significantly cheaper than comparing multiple columns.If an updated_at field exists, use it.2. Choose a Stable Unique KeyThe unique_key should never change.Changing business keys can produce duplicate histories and inaccurate snapshot records.3. Snapshot Only Important TablesNot every source table requires historical tracking.Focus on tables where historical analysis provides business value, such as:CustomersProductsPricingEmployeesSubscription plans4. Minimize Checked ColumnsIf you’re using the check strategy, compare only the columns that truly matter.Avoid using check_cols='all' unless absolutely necessary.5. Optimize Your Data WarehouseAs snapshot tables grow, warehouse optimization becomes increasingly important.Depending on your platform, consider:Partitioning snapshot tablesClustering by business keysClustering by dbt_valid_fromClustering by dbt_valid_toThese optimizations reduce scan costs and improve query performance.6. Schedule Snapshots WiselyRunning snapshots every few minutes rarely provides additional business value.Instead, align your schedule with how frequently your source data changes.Hourly, daily, or nightly runs are often sufficient.7. Monitor Storage GrowthUnlike regular tables, snapshot tables never shrink on their own.Every change produces another row.As history accumulates, monitor storage costs and archive historical data if your retention policies allow.Best PracticesBefore using snapshots in production, keep these recommendations in mind:Prefer the timestamp strategy whenever possible.Use immutable business keys.Snapshot only high-value tables.Validate source data quality.Test snapshot logic regularly.Monitor runtime and storage growth.Document your snapshot strategy for future maintainers.— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —DevendraSenior Data Engineer · Data Platforms · Cloud · Big Data🌐 https://devendra.tech · 💻 https://github.com/devendra631997 · 🔗LinkedIn
Predicate Pushdown Explained: How Databases Read Less Data and Run Queries Faster
predicate-pushdown
data-engineering
Modern data systems process massive datasets every second. Whether you’re using SQL databases, data warehouses, or big data frameworks like Apache Spark, one optimization technique appears everywhere:Predicate PushdownIf you’ve ever wondered:Why some queries run instantly while others are painfully slowHow query engines reduce memory usageHow Parquet and columnar storage improve performanceWhat “PushedFilters” means in Spark execution plansWhat Is Predicate Pushdown?Predicate pushdown is a query optimization technique where filtering conditions are applied as early as possible during data retrieval.Instead of:Reading all rows from storageLoading everything into memoryFiltering unwanted data laterThe system:Pushes the filter condition down to the storage layerReads only matching dataAvoids unnecessary computationUnderstanding the Word “Predicate”In databases, a predicate simply means a condition that evaluates to true or false.Example:WHERE country = 'India'orWHERE age > 30These conditions are predicates.What Does “Pushdown” Mean?“Pushdown” means moving computation closer to where the data physically exists.So instead of filtering after data is loaded, the filtering happens:During file scanningInside the database engineAt the storage layerBefore network transferThis dramatically reduces the amount of data processed.Predicate Pushdown ExampleWithout Predicate PushdownSELECT * FROM users WHERE country = 'India';What happens:Entire users table is scannedAll rows are loadedFiltering happens afterwardIf the table has 1 billion rows, that’s expensive.With Predicate PushdownThe storage engine applies:country = 'India'during the scan itself.Result:Only matching rows are readLess disk I/OLower memory usageFaster executionWhy Predicate Pushdown MattersPredicate pushdown is one of the most important performance optimisations in modern data engineering.Benefits include:1. Reduced Disk I/OReading data from disk is expensive.By filtering early, systems avoid scanning unnecessary blocks.2. Faster Query PerformanceLess data means:fewer CPU cyclesfewer transformationsfaster aggregationsreduced shuffle operations3. Lower Memory ConsumptionOnly relevant records are loaded into memory.This is especially important for distributed systems.4. Reduced Network TrafficIn distributed architectures, moving data across nodes is costly.Predicate pushdown minimises transferred data.Predicate Pushdown in Apache SparkApache Spark heavily relies on predicate pushdown for performance.Example:df.filter(df.age > 30).select("name")Spark tries to push:age > 30down to:Parquet readersORC readersJDBC sourcesDelta tablesThis prevents Spark from reading irrelevant rows.How to Verify Predicate Pushdown in SparkUse:df.explain(True)Look for:PushedFilters: [IsNotNull(age), GreaterThan(age,30)]If you see PushedFilters, predicate pushdown is working.Predicate Pushdown in ParquetApache Parquet supports predicate pushdown using:column statisticsmetadatarow group filteringInstead of scanning entire files, Parquet skips irrelevant row groups.This is one reason Parquet performs much better than CSV for analytics workloads.Predicate Pushdown vs Partition PruningThese concepts are related but different.FeaturePredicate PushdownPartition PruningFilters rowsYesIndirectlySkips partitionsSometimesYesWorks inside filesYesNoUses metadataYesYesExample:WHERE year = 2025can enable partition pruning.Whereas:WHERE salary > 100000can use predicate pushdown inside files.Databases and Systems That Support Predicate PushdownMany modern systems implement predicate pushdown:PostgreSQLMySQLSnowflakeGoogle BigQueryApache HiveTrinoPrestoDatabricksWhen Predicate Pushdown Does NOT WorkPredicate pushdown may fail in certain scenarios.1. Using Complex UDFsExample:df.filter(custom_udf(df.name))Custom functions are often opaque to the optimizer.2. Non-Supported Data SourcesSome connectors cannot push filters down.3. Filtering After TransformationsExample:df.withColumn("x", expensive_function(df.col)).filter(df.x > 5)The optimizer may not push the filter early enough.Best Practices for Predicate PushdownTo maximize performance:Use Columnar FormatsPrefer:Apache ParquetORCAvoid CSV for analytical workloads.Filter EarlyApply filters before joins and aggregations.Good:df.filter(df.country == "India").groupBy("city").count()Avoid Unnecessary UDFsUse built-in SQL functions whenever possible.Partition Large TablesCombine partition pruning with predicate pushdown.Internal Working of Predicate PushdownAt a high level:Query optimizer analyzes filter conditionsDetermines which predicates can be pushedSends filter conditions to the storage engineStorage layer skips irrelevant data blocksQuery engine processes only required recordsReal-World ExampleImagine an e-commerce table with:5 billion rows10 years of transactionsstored in ParquetQuery:SELECT * FROM transactionsWHERE country = 'India'AND year = 2025;Optimisations:year = 2025 → partition pruningcountry = 'India' → predicate pushdownResult:Instead of scanning terabytes, the engine may scan only a few gigabytes.Final ThoughtsPredicate pushdown is one of the most impactful optimizations in modern databases and big data systems.It improves:query speedscalabilitymemory efficiencydistributed processing performanceIf you work with SQL, Spark, data lakes, or cloud warehouses, understanding predicate pushdown helps you write significantly faster queries.Frequently Asked Questions (FAQ)What is predicate pushdown in SQL?Predicate pushdown is an optimization where filter conditions are applied at the storage level to reduce data scanning.Why is predicate pushdown important?It reduces disk I/O, memory usage, and query execution time.Does Parquet support predicate pushdown?Yes. Apache Parquet supports predicate pushdown using metadata and column statistics.How do I check predicate pushdown in Spark?Use:df.explain(True)and inspect PushedFilters.Is predicate pushdown the same as partition pruning?No. Predicate pushdown filters rows within files, while partition pruning skips entire partitions.
Partitioning vs Clustering in Cloud Data Warehouses: Why Every Data Engineer Must Understand Both
partitioning
data-engineering
clustering
data-warehouse
Modern cloud data warehouses are designed to process massive datasets at incredible speed. But achieving fast query performance and low cloud costs requires more than just scalable infrastructure.The real secret lies in how the data is physically organised.Two of the most important optimisation techniques in modern data platforms are:PartitioningClusteringThese concepts are foundational for building scalable analytics systems in:SnowflakeBigQueryAmazon RedshiftDatabricksSynapse AnalyticsDelta LakeYet many data engineers still confuse partitioning with clustering — or use them incorrectly.What Is Partitioning in Cloud Data Warehouses?Partitioning is the process of dividing a large database table into smaller physical segments based on a column value.The most common partition key is:datetimestampevent timeExample:PARTITION BY event_dateInstead of storing all records in one massive table, the warehouse organizes the data into partitions such as:2026-05-012026-05-022026-05-03Now consider this query:SELECT *FROM eventsWHERE event_date = '2026-05-20';The warehouse scans only the required partition rather than the entire dataset.This optimisation technique is called:partition pruningpartition eliminationPartitioning is one of the most effective ways to improve query performance in cloud analytics platforms.Benefits of PartitioningPartitioning offers several major advantages:Faster Query PerformanceQueries scan less data, resulting in lower latency.Reduced Cloud CostsMany cloud warehouses charge based on bytes scanned.Partition pruning significantly reduces:compute usagequery coststorage read operationsImproved ScalabilityPartitioned tables handle large historical datasets more efficiently.Better Data Lifecycle ManagementOld partitions can be archived or deleted independently.This is extremely useful for:retention policiescompliance requirementslong-term storage optimizationWhat Is Clustering in Data Warehouses?Clustering organizes data within partitions or storage blocks based on selected columns.Example:CLUSTER BY customer_idRows with similar customer_id values are stored physically close together.This allows the warehouse to:skip unnecessary storage blocksoptimize filtering operationsaccelerate joinsimprove compression efficiencyThink of clustering as sorting books alphabetically inside a library section.Partitioning determines:“Which section should I search?”Clustering determines:“How quickly can I locate the exact book?”Benefits of ClusteringClustering improves:selective query performancefiltering efficiencycompression ratiosscan optimizationIt is especially useful for:high-cardinality columnsfrequently filtered identifierslarge analytical tablesPartitioning vs Clustering: Key DifferencesPartitioning vs Clustering: Key DifferencesThe most important distinction:Partitioning reduces the amount of data scannedClustering improves how efficiently scanned data is processedReal-World ExampleImagine a 20 TB clickstream table.You define:PARTITION BY event_dateCLUSTER BY user_idNow run this query:SELECT *FROM clicksWHERE event_date = '2026-05-20'AND user_id = 998877;Step 1: Partition PruningThe warehouse scans only the partition for May 20.Instead of scanning:20 TBit may scan:200 GBStep 2: Cluster PruningInside the partition, rows are organised by user_id.The warehouse quickly identifies relevant storage blocks and skips unnecessary ones.Maybe only:3 GBactually gets scanned.This is why combining partitioning and clustering is considered a best practice in modern cloud data engineering.Best Partition Keys for Data WarehousesGood partition keys should:appear frequently in query filtershave relatively low cardinalitygrow naturally over timeExcellent partition keys:event_datecreated_atingestion_timestampPoor partition keys:customer_idemailtransaction_idWhy?Because high-cardinality partitioning creates too many small partitions, leading to:metadata overheadinefficient scansslower query planningstorage fragmentationBest Clustering KeysGood clustering keys are:high-cardinality columnscommon filtering dimensionsfrequent join columnsExamples:customer_idsession_iddevice_idproduct_idWeak clustering candidates:gendercountrystatusLow-cardinality clustering typically provides minimal performance gains.Common Partitioning and Clustering MistakesOver-PartitioningBad example:PARTITION BY customer_idThis creates millions of tiny partitions and hurts performance.Under-PartitioningA giant unpartitioned table forces full-table scans.This becomes expensive at scale.Wrong Clustering KeysClustering low-cardinality columns usually provides little benefit.Example:CLUSTER BY countryOften not worth the overhead.Partitioning and Clustering in Popular Cloud Data WarehousesSnowflakeSnowflake automatically manages micro-partitions internally.Engineers typically:avoid manual partitioningoptionally define clustering keysBigQueryBigQuery supports both:partitioningclusteringA common optimization pattern:PARTITION BY event_dateCLUSTER BY user_idAmazon RedshiftRedshift uses:sort keysdistribution keysSort keys work similarly to clustering.Databricks and Delta LakeDatabricks commonly combines:partitioningZ-order clusteringfor large-scale lakehouse optimization.Recommended Best PracticeFor most analytical workloads:Partition by time.Cluster by identifiers.Example:PARTITION BY event_dateCLUSTER BY customer_idThis strategy works extremely well across modern cloud analytics platforms.
What Happens If the Preferred Replica Is Not in the ISR in Apache Kafka?
data-engineer
kafka
in-sync-replica
datastream
In Apache Kafka, the preferred replica is the replica Kafka ideally wants to use as the partition leader. However, Kafka only elects replicas that are part of the ISR (In-Sync Replica) list.If the preferred replica is not in ISR, Kafka will not elect it as the leader.In-Sync ReplicaWhy Does This Happen?The ISR list contains replicas that are fully synchronized with the current leader. If a preferred replica falls behind, Kafka removes it from ISR to maintain data consistency and reliability.During preferred leader election, Kafka checks:Is the preferred replica alive?Is it part of ISR?If the answer is no, Kafka skips the election and keeps the current ISR replica as leader.ExampleSuppose a Kafka partition has:Replica 0 → Preferred ReplicaReplica 1 → Current LeaderReplica 2 → FollowerCurrent ISR:[1,2]Since Replica 0 is not in the ISR list, Kafka does not promote it to leader.Benefits of This BehaviorThis Kafka replication mechanism helps:Prevent data lossAvoid stale leadersMaintain high availabilityEnsure strong consistency across replicas
Understanding Partition and Partitioner in Apache Spark
data-engineer
data-partitioning
spark-databricks
spark
When working with Apache Spark, two important concepts help Spark process big data efficiently:PartitionPartitionerLet’s understand them in a simple way.What is a Partition?A partition is a small chunk of data.Spark splits large datasets into multiple partitions so different parts can be processed in parallel.ExampleImagine you have 1 million records.Spark may divide them into 4 partitions:Partition 1 → 250k recordsPartition 2 → 250k recordsPartition 3 → 250k recordsPartition 4 → 250k recordsEach partition is processed separately by Spark.Why Partitions MatterPartitions improve:SpeedParallel processingCluster utilizationToo Few PartitionsSlow processingCPUs stay idleToo Many PartitionsExtra overheadToo many small tasksChecking Partitionsrdd.getNumPartitions()Changing Partitionsrepartition()Used to increase or redistribute partitions.df.repartition(10)coalesce()Used mainly to reduce partitions.df.coalesce(2)What is a Partitioner?A partitioner decides:“Which partition should a record go to?”It is mainly used for key-value data.ExampleSuppose we have:(A,10)(B,20)(A,5)A partitioner ensures all records with key A go to the same partition.This helps Spark perform operations like:reduceByKey()join()groupByKey()efficiently.Types of Partitioners1. HashPartitionerDefault partitioner in Spark.Uses:partition=hash(key) mod numPartitionspartition=hash(key)modnumPartitionsRecords are distributed based on key hash values.2. RangePartitionerDistributes data by ranges.Example:Partition 1 → 1–100Partition 2 → 101–200Useful for sorted data.Partition vs PartitionerPartition vs PartitionerSimple AnalogyThink of a library:Partitions = shelvesPartitioner = rule deciding which shelf a book belongs toExample:A–F → Shelf 1G–L → Shelf 2
Python Memory Management: A Deep Dive into Reference Counting, Garbage Collection, and Real-World…
memory-management
data-engineer
python-programming
Python Memory Management: A Deep Dive into Reference Counting, Garbage Collection, and Real-World LeaksPython is often described as a “garbage-collected language,” but that description hides a lot of important detail. In CPython — the standard Python implementation — memory management is a layered system combining reference counting, cyclic garbage collection, and generational heuristics, all sitting on top of heap-based object allocation.Understanding this system is essential for writing performant applications and debugging memory issues in production systems.1. Stack vs Heap: The Real FoundationBefore diving into Python’s internals, it’s important to understand how memory is conceptually divided.Stack: Execution LayerThe stack stores:Function framesLocal variable referencesControl flow dataIt operates in a strict LIFO (last-in-first-out) manner and is automatically cleaned when functions return.However, the stack does not store Python objects themselves.def func(): x = [1, 2, 3]Here:x lives in the stack frameThe list [1, 2, 3] lives in the heapHeap: Object Storage LayerAll Python objects live in the heap:ListsDictionariesFunctionsClass instancesEven integers and strings (with optimisations)The heap is where Python’s memory manager takes full control.2. Reference Counting: The Core MechanismAt the heart of CPython memory management is reference counting.The IdeaEvery object tracks how many references point to it.When the count drops to zero → the object is immediately freed.Examplea = []b = adel adel bAfter b = a, reference count = 2After deleting both references → refcount = 0Object is immediately deallocatedWhy Reference Counting MattersDeterministic memory cleanupNo long GC pauses for most objectsSimple and efficientDisadvantagesCannot handle cyclic referencesOverhead on every assignment and function call3. The Hidden Problem: Cyclic ReferencesReference counting alone fails in one important scenario: cycles.a = []b = []a.append(b)b.append(a)Even if you delete both references:del adel bThe objects still reference each other, keeping their reference counts above zero.This creates a memory leak by design — unless another system steps in.4. Cyclic Garbage CollectionTo solve this, Python includes a cyclic garbage collector (GC).What It DoesThe GC detects groups of objects that:Reference each otherBut are unreachable from the programIt then safely removes them.Root Reachability ConceptPython defines a “root set”:Stack referencesGlobal variablesBuilt-in referencesIf a group of objects cannot be reached from these roots, it is considered garbage.Important LimitationThe GC only tracks container objects, such as:listsdictscustom objectsPrimitive objects like integers and strings are not tracked.5. Generational Garbage CollectionScanning every object on every GC cycle would be extremely expensive.Python optimizes this using a generational hypothesis:The Three GenerationsGeneration 0 (Young Objects)New objects start hereCollected frequentlyFast and cheapGeneration 1 (Survivors)Objects that survive one GC cycleGeneration 2 (Long-Lived Objects)Stable objectsCollected rarelyPromotion StrategyIf an object survives collection:Gen 0 → Gen 1Gen 1 → Gen 2Older generations are scanned less frequently.Why This WorksMost temporary objects (like loop variables or function locals) are short-lived, so frequent scanning of older objects is wasteful.6. Memory Leaks in Real Python SystemsEven with automatic memory management, leaks still happen in production systems.1. Unintentional Global Referencescache = []def add(item): cache.append(item)If cache grows without bounds → memory leak pattern.2. Reference Cycles with FinalizersObjects with destructors (__del__) inside cycles can confuse the GC and delay cleanup.3. Closures Holding Large Objectsdef outer(): big_data = [0] * 10_000_000 def inner(): return big_data return innerAs long as inner exists, big_data stays in memory.4. C Extension LeaksLibraries written in C (like NumPy or others) can leak memory if reference counting is mismanaged.These leaks bypass Python’s GC entirely.5. Unbounded CachesDecorators like LRU caches can silently grow if not bounded properly.6. Long-Lived Cycles in Web AppsIn frameworks, request objects may accidentally reference logs, sessions, or context objects, forming long-lived cycles.7. Internal Memory Allocation (CPython Insight)Python does not directly request memory from the OS for every object.Instead, it uses layered allocators:pymallocHandles small objects efficientlyUses memory pools and arenasReduces fragmentationSystem allocatorUsed for large objectsDelegates to OS malloc8. Why Python Memory Management is “Hybrid”Python is not purely:reference counting (like naive systems)nor tracing GC (like Java)Instead, it combines:Reference counting → immediate cleanupCycle detector → safety netGenerational GC → performance optimizationThis hybrid design gives Python a unique balance of:low latencypredictable cleanupacceptable overhead9. Final Mental ModelYou can think of Python memory management like this:Stack → holds references (not objects)Heap → stores all objectsReference counting → primary cleanup mechanismCyclic GC → handles edge-case cyclesGenerational GC → optimizes performance over time
The Python GIL Explained: Concurrency in Simple Terms
python-programming
memory-management
The Global Interpreter Lock (GIL) in Global Interpreter Lock is a mechanism in Python (software) that allows only one thread to execute Python bytecode at a time.What this meansPython threads do not run in parallel for CPU tasksOnly one thread holds the GIL at any momentMulti-threading does not fully utilize multi-core CPUs for computation-heavy workImpact on performanceCPU-bound tasksThreading does not improve speedThe GIL becomes a bottleneckI/O-bound tasksThreading still works wellThreads switch during waiting (API calls, file I/O, DB queries)Alternatives to overcome the GILmultiprocessingUse separate processes instead of threads✔ True parallelism✔ Best for CPU-heavy workloadsasyncioSingle-threaded, event-driven concurrency✔ Best for high-scale I/O tasks✔ Efficient and lightweightExternal servicesOffload heavy work to workers or microservices✔ Scales beyond one machineMultiprocessing vs Async: When to choose?Choose multiprocessing when tasks are CPU-intensive and need real parallel execution.Choose asyncio when tasks are I/O-heavy and involve waiting (APIs, databases, network calls).
