Data Engineering - DriveDataScience

KQL Window Functions: serialize, prev, next, row_number, row_cumsum, row_rank_dense, row_rank_min, row_window_session, scan Operator, and Every Pattern for Fabric Real-Time Analytics

Leave a Comment / Azure, Data Engineering, Microsoft Fabric

The complete KQL window functions reference. The serialize operator and why window functions require it. prev() and next() for accessing adjacent rows. row_number() for sequential numbering. row_cumsum() for running totals. row_rank_dense() and row_rank_min() for ranking (dense vs gaps). row_window_session() for automatic session detection. The scan operator for stateful row processing. Partitioned windows with the restart parameter. KQL vs SQL window function comparison table. Eight real-world patterns, common mistakes, and interview Q&As.

KQL Window Functions: serialize, prev, next, row_number, row_cumsum, row_rank_dense, row_rank_min, row_window_session, scan Operator, and Every Pattern for Fabric Real-Time Analytics Read More »

Fabric Spark Configuration and Performance Tuning: shuffle.partitions, autoBroadcastJoinThreshold, maxPartitionBytes, AQE, Autotune, Native Execution Engine, and Every Setting Data Engineers Must Know

Leave a Comment / Data Engineering, Microsoft Fabric

Complete Fabric Spark configuration and performance tuning guide. Where to set configurations (Environments for persistent, %%configure for session-level, spark.conf.set for runtime) with mutable vs immutable properties table and the critical root-vs-conf placement rule for %%configure. The Big Three settings: spark.sql.shuffle.partitions (default 200, grocery checkout lane analogy, guidelines by data size, demonstration with empty partitions), spark.sql.autoBroadcastJoinThreshold (default 10MB, product catalog analogy, why increase to 100-256MB, manual broadcast hint, OOM warning with 20% rule, checking join strategy with explain()), and spark.sql.files.maxPartitionBytes (default 128MB, delivery truck analogy, when to increase/decrease). Adaptive Query Execution with GPS analogy (coalesce partitions, auto broadcast conversion, skew join splitting, AQE vs manual tuning). Autotune ML-based optimizer (how to enable, when it works best, checking driver log recommendations). Native Execution Engine (Velox/Gluten C++ engine, 2-4x faster, enable via %%configure or Environment, UDF fallback limitation). Memory and resources (driver vs executor memory, node sizes table, starter vs custom pools comparison, high concurrency mode). Delta Lake settings (optimize write, auto compaction, V-Order default-on, target file size). Join strategy deep dive (broadcast hash, sort-merge, shuffle hash with decision table). Four production configuration templates (small/medium/large/massive data). Spark UI diagnosis (reading jobs/stages/executors tabs, identifying shuffle bottlenecks, identifying data skew with median vs max gap). 8 common mistakes and 8 interview Q&As.

Fabric Spark Configuration and Performance Tuning: shuffle.partitions, autoBroadcastJoinThreshold, maxPartitionBytes, AQE, Autotune, Native Execution Engine, and Every Setting Data Engineers Must Know Read More »

Python Working with APIs and HTTP Requests: requests Library, REST APIs, Authentication, Pagination, Error Handling, Rate Limiting, and Every Pattern Data Engineers Need

Leave a Comment / Data Engineering, Python

Complete Python APIs and HTTP requests guide for data engineers. HTTP basics (what is an API, HTTP methods table with restaurant analogy, status codes table with pipeline actions, headers and content types). The requests library (GET with query params, POST with JSON and file uploads, PUT, PATCH, DELETE). Working with JSON (reading responses, nested navigation with safe .get() chains, flattening nested JSON). Query parameters (params dict vs manual URL building). Authentication (API key in headers vs params with security note, Bearer token OAuth two-step flow with token refresh, Basic auth). Complete error handling pattern (status code checking, raise_for_status, timeouts, connection errors, 429 rate limit handling, 5xx retry with backoff, 4xx fail-fast). Three pagination styles with book/library analogy (offset-based with total tracking, cursor-based for large/real-time data, link-header GitHub-style). Rate limiting (throttle with requests_per_second, Retry-After header respect). Sessions with taxi-for-the-day analogy (connection reuse, persistent headers, context manager). Five DE patterns: complete APIExtractor class (auth, pagination, retry, rate limiting, context manager), API response to DataFrame with recursive flatten_dict, incremental extraction with watermark file persistence, multi-endpoint pipeline. 7 common mistakes and 6 interview Q&As.

Python Working with APIs and HTTP Requests: requests Library, REST APIs, Authentication, Pagination, Error Handling, Rate Limiting, and Every Pattern Data Engineers Need Read More »

Python Decorators and Context Managers: Closures, @wraps, Parameterized Decorators, Class Decorators, the with Statement, Custom Context Managers, contextlib, and Every Pattern Data Engineers Need

Leave a Comment / Data Engineering, Python

Complete Python decorators and context managers guide for data engineers. Part 1 Decorators: functions as first-class objects (assign, pass, return). Closures with robot factory analogy. Building your first decorator step-by-step (what happens under the hood). The @ syntax sugar explained. @functools.wraps for preserving function identity (always use it). Parameterized decorators (decorator factory with vending machine analogy, three layers explained). Built-in decorators recap (@staticmethod, @classmethod, @property). @functools.lru_cache memoization with speed dial analogy and cache_info. @functools.total_ordering. Stacking decorators (bottom-to-top with clothing layers analogy). Class-based decorators with CallCounter state example. Five DE decorator patterns: @timer with auto-warning for slow functions, @retry with configurable exponential backoff, @log_call with argument and return logging, @validate_input with type and lambda validators, @deprecated with warnings. Part 2 Context Managers: with statement recap and how it translates to __enter__/__exit__. Custom class-based PipelineTimer. @contextlib.contextmanager generator approach with hotel room analogy (before yield = checkin, yield = hand key, after yield = checkout). Nested context managers (Python 3.10+ multi-line). contextlib.suppress for clean exception ignoring. Four DE context manager patterns: database connection with auto commit/rollback/close, temporary directory with auto-cleanup, audit logger that writes JSON with metrics. Combining decorators + context managers (the ultimate production pattern). 7 common mistakes and 7 interview Q&As.

Python Decorators and Context Managers: Closures, @wraps, Parameterized Decorators, Class Decorators, the with Statement, Custom Context Managers, contextlib, and Every Pattern Data Engineers Need Read More »

Python Regular Expressions: re Module, Pattern Matching, Groups, Lookaheads, Data Cleaning, Validation, and Every Pattern Data Engineers Need

Leave a Comment / Data Engineering, Python

Complete Python regular expressions guide for data engineers. Why regex over manual string checks. The re module (search vs match vs findall with detective/bouncer analogy, finditer for lazy iteration, sub for search-and-replace with function replacement, split on patterns, compile for reuse with when-to guidance). Pattern syntax building blocks (literal characters, character classes [ ] with hex color example, predefined classes table with \d \w \s and opposites, quantifiers table with * + ? {n} {n,m}, greedy vs lazy with hungry dog analogy, anchors ^ $ for word boundaries, alternation |, escaping special characters with re.escape). Groups and capturing (basic groups with date and phone extraction, named groups (?P) with log parsing, non-capturing groups (?:) for performance, backreferences for duplicate word detection and name swapping). Lookahead and lookbehind (positive/negative lookahead and lookbehind with house-next-to-park analogy, extracting values after labels). Four flags (IGNORECASE, MULTILINE, DOTALL, VERBOSE with commented phone pattern). Nine data engineering patterns: email validation, phone number standardization (7 formats to one), date format detection, IP address extraction, log file parsing with named groups, column name cleaning to snake_case (camelCase, spaces, special chars), PII masking (email, phone, SSN, credit card), CSV field cleaning, URL parsing. 7 performance tips. 7 common mistakes. 6 interview Q&As.

Python Regular Expressions: re Module, Pattern Matching, Groups, Lookaheads, Data Cleaning, Validation, and Every Pattern Data Engineers Need Read More »

Python Dates, Times, and Timezones: datetime, timedelta, strftime, strptime, zoneinfo, Scheduling, and Every Pattern Data Engineers Need

Leave a Comment / Data Engineering, Python

Complete Python dates, times, and timezones guide for data engineers. Core classes table (date, time, datetime, timedelta). date for calendar dates (today, weekday, comparison). time for time-of-day. datetime creation (constructor, fromisoformat, combine, replace). Current time (now vs now(timezone.utc) and why naive is dangerous). Accessing components (year, month, day, hour, timestamp). timedelta for date math (adding/subtracting with stopwatch analogy, calculating differences, WARNING about .seconds vs .total_seconds(), format_duration helper, multiply/divide). strftime for formatting (15-code table, file name patterns, partition paths, display formats). strptime for parsing (common formats, multi-format parser for source systems, error handling with safe_parse_date, printer vs scanner analogy for f vs p memory trick). Timezones (naive vs aware with currency price tag analogy, zoneinfo with Toronto/London/Mumbai all equal, converting between timezones with astimezone, why store UTC with 5 reasons, DST pitfalls with spring forward and fall back). ISO 8601 (what it is, parsing and formatting, why it matters). Unix timestamps (epoch time, millisecond timestamps from APIs). Six data engineering patterns: date-partitioned file paths with ADF expression equivalent, watermark pattern with buffer minutes, pipeline scheduling windows, audit timestamp class, date range generator for backfill, business days calculator. 7 common mistakes (datetime.utcnow deprecated) and 6 interview Q&As.

Python Dates, Times, and Timezones: datetime, timedelta, strftime, strptime, zoneinfo, Scheduling, and Every Pattern Data Engineers Need Read More »

Fabric Copy Activity Deep Dive: Every Tab, Every Setting, Fault Tolerance, Staging, Logging, Intelligent Throughput, Parallelism, and Production Patterns Every Data Engineer Must Know

Leave a Comment / Data Engineering, Microsoft Fabric

Complete Fabric Copy Activity deep dive covering every tab and setting. Moving company analogy for understanding Source, Destination, Mapping, and Settings tabs. How the Copy activity works under the hood (4-step process). General tab (naming conventions, timeout best practices with restaurant analogy, retry and retry interval, secure input/output). Source tab (table vs query vs stored procedure with room analogy, partition options with None/Physical/Dynamic Range comparison table and performance benchmarks, additional columns for audit lineage, query timeout and isolation level). Destination tab (Lakehouse vs Warehouse differences table, table action Append/Overwrite/Upsert with bookshelf analogy, key columns for upsert, pre-copy script for idempotent reloads, destination partitioning, max rows per file). Mapping tab (auto vs manual mapping, import schemas, type conversion two-stage flow, schema drift handling). Settings tab (ITO with truck-size analogy and cost impact benchmarks, Degree of Copy Parallelism with tuning guidance, how ITO and parallelism compound, fault tolerance with postal service analogy and when/when-not to enable, enable staging with loading dock analogy and Workspace vs External options, session logging with file path structure, data consistency verification, preserve metadata). Monitoring output (11-field table, reading throughput, identifying bottlenecks across queue/pre-copy/transfer/post-copy phases). Five production patterns: standard SQL-to-Lakehouse, large table with Dynamic Range partitioned read, SQL-to-Warehouse with required staging, fault-tolerant load with quarantine session logging and Teams alerts, metadata-driven copy with per-table ITO and fault tolerance settings from config table. Six cost optimization tips. Eight common mistakes. Eight interview Q&As.

Fabric Copy Activity Deep Dive: Every Tab, Every Setting, Fault Tolerance, Staging, Logging, Intelligent Throughput, Parallelism, and Production Patterns Every Data Engineer Must Know Read More »

Python Modules, Packages, Virtual Environments, and Imports: How Python Organizes Code, Resolves Dependencies, and Every Pattern Data Engineers Need

Leave a Comment / Data Engineering, Python

Complete Python modules, packages, and virtual environments guide for data engineers. Modules (what they are, creating your first module, all import styles with toolbox analogy). Import mechanics (what happens when you import, module caching in sys.modules, the __name__ == __main__ guard with furniture store analogy, module search path sys.path). Packages (what they are, creating with __init__.py as reception desk analogy, nested sub-packages, relative vs absolute imports). Import best practices (PEP 8 ordering, circular imports with 3 fixes, lazy imports for heavy/optional dependencies, star imports and why to avoid them). Standard library essentials (20-module table covering os, pathlib, json, csv, logging, datetime, collections, typing, functools, itertools, hashlib, argparse, abc, concurrent.futures with examples). Third-party packages with pip (installing, requirements.txt, version pinning strategies with disaster scenario, PyPI and key data engineering packages). Virtual environments (restaurant kitchen analogy, creating/activating/deactivating venv, installing packages, complete requirements.txt workflow, when to create new venvs, comparison table of venv vs conda vs poetry vs pipenv vs uv). Five data engineering patterns: project directory structure, config module with dotenv, utils module with retry and DB helpers, pipeline package with extract/transform/load, shared library with setup.py for cross-project reuse. 7 common mistakes and 7 interview Q&As.

Python Modules, Packages, Virtual Environments, and Imports: How Python Organizes Code, Resolves Dependencies, and Every Pattern Data Engineers Need Read More »

Python OOP Advanced: Inheritance, Method Overriding, super(), Multiple Inheritance, MRO, Polymorphism, Abstract Classes, Mixins, Composition vs Inheritance, and Every Pattern Data Engineers Need

Leave a Comment / Data Engineering, Python

Complete Python advanced OOP guide for data engineers. Inheritance basics (what gets inherited table, smartphone analogy). Parent-child class with DataSource/SQLSource/APISource examples. Method overriding (complete override vs extend with airline check-in analogy, when to override guide). super() explained (what it does, super().__init__() with new-hire form analogy, super() in other methods for extending behavior). isinstance() and issubclass() for type checking. Multiple inheritance (two parents, diamond problem, Method Resolution Order with corporate chain-of-command analogy, C3 linearization). Polymorphism (universal remote analogy, three source types with same extract() interface, duck typing with taxi analogy, polymorphism in built-in functions). Abstract classes with abc module (building code analogy, creating abstract classes, abstract properties, why they matter — 5 reasons, catching missing implementations at creation time). Mixins (phone case analogy, LoggingMixin, JSONSerializableMixin, TimestampMixin, combining multiple). Composition vs inheritance (when inheritance goes wrong with over-inheritance and wrong relationship examples, has-a vs is-a with PC building analogy, when-to-use comparison table, rule of thumb). Five data engineering patterns: abstract BasePipeline with template method, source-specific implementations (SQL/API/File), polymorphic pipeline runner that adds new types with zero changes, validator hierarchy with NotNull/Unique/Range, composed pipeline with pluggable BaseExtractor/BaseTransformer/BaseLoader. 7 common mistakes and 8 interview Q&As.

Python OOP Advanced: Inheritance, Method Overriding, super(), Multiple Inheritance, MRO, Polymorphism, Abstract Classes, Mixins, Composition vs Inheritance, and Every Pattern Data Engineers Need Read More »

Python OOP: Classes, init, self, Instance vs Class Variables, Methods, Properties, Dunder Methods, and Every Pattern Data Engineers Need

Leave a Comment / Data Engineering, Python

Complete Python OOP guide for data engineers. Why OOP when functions are not enough (scattered state problem with ID badge template analogy). Your first class (defining, creating instances, modifying state). The __init__ constructor (what it does, default parameters, validation at creation time). self explained (what it actually is, why Python makes it explicit, the two equivalent calling forms). Instance vs class attributes (shared vs unique with badge template analogy, when-to-use table, the mutable class attribute trap with fix). Three method types (instance methods with self, @classmethod with cls for alternative constructors with restaurant ordering analogy, @staticmethod for utility functions, comparison table). Properties (getter/setter/deleter with validation, computed properties for success_rate and rows_per_second). Five dunder methods (__str__ vs __repr__ rules, __len__, __eq__ and __lt__ for sorting, __getitem__ for indexing, __enter__/__exit__ for context managers with rollback). Encapsulation (public, _protected, __private naming conventions with name mangling and doors analogy). Five data engineering patterns: PipelineConfig with from_json/from_dict/dev_config class methods, TableLoader with extract-transform-load cycle, ConnectionManager context manager with auto rollback, DataValidator with method chaining and check_not_null/unique/range, PipelineTracker with computed properties and dunder methods. 6 common mistakes and 6 interview Q&As.

Python OOP: Classes, __init__, self, Instance vs Class Variables, Methods, Properties, Dunder Methods, and Every Pattern Data Engineers Need Read More »