Data Ingestion Tools

Snowflake Data Loading: Part 2 - Advanced Batch & Micro-Batch 🚀 We covered batch fundamentals in Part 1. Now, let's explore advanced techniques for continuous ingestion and leveraging Snowflake's ecosystem. Advanced Batch & Continuous Loading Patterns (Do's ✅): ✅ Snowpipe: Continuous Data Ingestion (❄️➡️📈): For near real-time/micro-batch. Loads data as files arrive (auto-ingest from cloud events or REST API). ✅ Snowflake Connectors: Seamless Integration (🔗): * Kafka Connector 📬: High-throughput, low-latency streams into Snowflake tables (schema evolution, exactly-once). * Spark Connector ✨: Efficient data transfer between Spark (Databricks, EMR) and Snowflake for complex ELT. * Third-Party Connectors 🌐: Managed ingestion from SaaS apps/databases (e.g., Fivetran), simplifying ELT. ✅ External Tables: Query Data in Cloud Storage (☁️🔍): Define tables over S3, Azure Blob, GCS data without loading to Snowflake. Ideal for exploration, cold storage, data lakes. ✅ Schema Evolution Strategies (🔄🧬): Plan for schema changes. Use VARIANT for semi-structured data, MATCH_BY_COLUMN_NAME, or ALTER TABLE for graceful evolution. ✅ Performance Tuning (⚡⚙️): Right-size virtual warehouses. Monitor Snowpipe credits. Optimize connector batch sizes/parallelism. Ensure landing zones optimize parallel reads. Advanced Batch & Continuous Loading Anti-Patterns (Don'ts 🚫): 🚫 Ignoring Snowpipe Cost/Latency (💸⏳): Costly for too many tiny files. Monitor usage; align frequency with true latency needs. Don't use for scheduled COPY INTO workloads. 🚫 Directly Querying Unoptimized External Tables (🐢): Leads to slow queries/higher costs. Optimize with partitioning, proper file formats. 🚫 Overlooking Data Governance for Data Lake (🙈): Extend governance, access controls, auditing to data lake storage (S3/ADLS/GCS). Stay tuned for Part 3: Streaming Data Loading! #Snowflake #Snowpipe #ExternalTables #DataConnectors #DataEngineering

🚀 Structured Streaming with Databricks Autoloader : I was tasked with building a scalable ingestion pipeline for continuous data landing in Azure Data Lake. Instead of traditional batch ingestion, I went with Databricks Autoloader to handle it as structured streaming data. 🔎 The Scenario Our source system was dropping files (CSV & JSON) every few minutes into ADLS Gen2. The requirement was clear: • Ingest data continuously with minimal latency • Handle late-arriving data gracefully • Avoid re-processing already ingested files ⚠️ Where I Got Stuck At first, I noticed duplicate processing when files were re-landed or updated. Since Autoloader uses incremental file discovery, I had to make sure checkpoints and schema evolution were handled correctly. Without the right configuration, I risked processing the same file multiple times. 💡 The Solution I implemented: • CloudFiles with Autoloader + Structured Streaming to ingest files continuously • Checkpointing to track offsets and ensure exactly-once ingestion • Schema evolution (cloudFiles.schemaLocation) to handle new columns without breaking the pipeline • Idempotent writes with Delta Lake so late-arriving or updated files merged seamlessly The result? ✅ Near real-time ingestion into Bronze tables ✅ Automatic detection of new files and schema changes ✅ Reliable streaming foundation that downstream Silver & Gold layers could depend on ✨ Takeaway Databricks Autoloader isn’t just about convenience, it fundamentally simplifies building resilient, scalable streaming pipelines. For modern data platforms, moving beyond batch into continuous ingestion unlocks real business agility. #Autoloader #StreamingData #Datalake #Databricks #DataEngineering

⚙️ Serverless Ingestion Pipelines with Lambda, Functions, and Cloud Functions Building ingestion pipelines doesn’t always mean spinning up Spark clusters or scheduling heavy ETL jobs. Sometimes, less is more — and serverless functions are the cleanest way to handle event-driven ingestion. Here’s how I’ve seen this work across AWS, Azure, and GCP: 🟦 AWS Lambda + S3 Events Triggered directly when a file lands in S3. I’ve used this to: - Validate file schema - Extract metadata (like timestamp, source, format) - Queue downstream processing in Kinesis or trigger a Glue job 🟩 Azure Functions + Blob Triggers Blob storage changes fire Functions that: - Parse JSON/CSV/XML payloads - Write summaries into Cosmos DB or push messages to Event Hub - Apply initial validation logic (file size, encoding, null checks) 🟥 GCP Cloud Functions + Cloud Storage Used for similar real-time triggers — and often feed: - Pub/Sub topics - Composer workflows - Or just log transformations into BigQuery with lightweight Python code 💡 Real-World Benefits ✅ No infrastructure to manage ✅ Millisecond-scale trigger time ✅ Perfect for light pre-processing, tagging, validation, or queuing ✅ Scales independently for bursty ingestion workloads When you don’t need Spark, Databricks, or EMR — go serverless. It’s elegant, scalable, and often the most maintainable approach. #DataEngineering #Serverless #AWSLambda #AzureFunctions #GCPCloudFunctions #Infodataworx #Ingestion #ETL #CloudNative #Kinesis #EventProcessing #DataPipelines #Python #BigQuery #BlobStorage

Stop struggling to pick the “best” ingestion method in Databricks — here’s the truth. Data engineers ask me this all the time: “Should I use COPY INTO, CREATE TABLE AS READ_FILES, or Auto Loader to ingest files?” Here’s the simplest breakdown you’ll ever read 👇 1️⃣ COPY INTO Perfect for: Ad-hoc loads, backfills, one-time migrations Strengths: Easy to use Idempotent (won’t reload already processed files) Great for bulk historical ingestion Weaknesses: No schema evolution No streaming You must manually re-run it for new files Use when: You want simplicity + one-off or batch ingestion without a streaming pipeline. 2️⃣ CREATE TABLE AS (READ_FILES + cloud_files()) Perfect for: Simple ingestion without building pipelines Strengths: Directly creates a table from files Quick way to explore or hydrate tables Works with Auto Loader under the hood if cloud_files() is used Weaknesses: Not ideal for production-grade ingestion Limited orchestration / monitoring capabilities More suited for data exploration or initial setup Use when: You want a fast table from files — no infrastructure, no complexity. 3️⃣ Auto Loader (cloud_files) Perfect for: Production ingestion, streaming, incremental loads Strengths: Handles new files automatically Scales to millions of files Schema inference + evolution Checkpointing + deduplication built in Weaknesses: Slightly more setup than COPY INTO Best used with Structured Streaming (which feels unfamiliar to beginners) Use when: You want reliable, incremental, scalable ingestion with minimal babysitting. ⭐ So which one is the BEST? If you’re building anything long-term → Auto Loader wins. If you’re doing one-time loading or backfills → COPY INTO wins. If you’re exploring or doing quick data hydration → CREATE TABLE AS (READ_FILES) wins. If you want a deeper breakdown (with diagrams and real examples), I wrote a full article. Link in comments. Which ingestion method do you use the most — and why?

🚀 Databricks Auto Loader – My Summary If you're dealing with data pipelines that involve millions of files (and not only), Databricks Auto Loader is your friend. It simplifies file ingestion (CSV, JSON, Parquet, Avro, etc.) by automatically detecting new files and tracking what's already been processed. 🔄 Two operating modes: - Directory Listing Mode: Scans directories to detect new files. - File Notification Mode: Uses cloud events for faster, more efficient detection. 🛠️ A few features I found particularly useful: ✅ File Type Filtering: Don’t control the source folder? No problem. Auto Loader can filter and ingest only the file types you care about — skipping control files or irrelevant formats. ✅ Directory Filtering with Glob Patterns: You can dynamically read from multiple subfolders using pattern matching — no hardcoding paths required! 🧹 Clean Up Made Easy with cloudFiles.cleanSource Keep your landing zone tidy: - OFF (default): Leave files untouched - DELETE: Remove files after a retention period - MOVE: Archive files to another path This tool has been essential in making my ingestion pipelines more resilient, efficient, and maintainable. Have you tried Auto Loader yet? Would love to hear how you're using it! 👇 #DataEngineering #Databricks #AutoLoader #ETL

As Delivery Solutions Architects at Databricks—working hand-in-hand with some of the world’s largest technology companies—we’re excited to share our deep-dive on Lakeflow Connect’s SQL Server connector. In this post, we unpack: * How Change Tracking (CT) and Change Data Capture (CDC) power lightweight, incremental ingestion * End-to-end setup—from SQL Server on-prem or in the cloud to Unity Catalog tables * Best practices for private networking, encryption, and pipeline observability * Hands-on examples using serverless compute & Delta Live Tables Whether you’re migrating hundreds of tables or building a single ML feature store, this guide shows you how to simplify ingestion and unlock fresh data for analytics and AI. Read the full walkthrough.

Not that everybody needs real-time data ingestion pipelines or the complexity that comes with it. But when you actually need real-time data, I'd look at Estuary. Estuary is built for low-latency data ingestion pipelines, on top of traditional batch processing capabilities. And sure enough, it supports schema evolution out of the box too. Being a low-code tool, it removes the implementation complexity for real-time data ingestion pipelines. Also, Estuary does this at a lower cost. Imagine paying a traditional low-code data ingestion tool company for the number of rows you ingest. And they may only ingest data up to 1 min or 5 min. Estuary charges per GB of data you ingest and latency might be something like 100ms. So if your business actually depends on real-time data such as fraud detection and live analytics on top of traditional batch pipelines, you can get real-time pipelines without burning through your budget or engineering hours. Real-time where it matters. Batch where it doesn’t. That’s the balance Estuary strikes really well. #data #dataingestion #dataengineering

Data engineering in Microsoft Azure The diagram shows a typical modern data analytics pipeline on Azure: 1. Operational Data (Source Systems) This is where data originates. Examples include: SQL Databases – relational transactional systems NoSQL Databases – e.g., Cosmos DB Applications / Web Apps – generating logs or user data IoT Devices / Sensors – streaming telemetry data This represents the raw, operational data you want to analyze. 2. Data Ingestion / ETL (Extract, Transform, Load) Data is brought into the analytics platform and optionally transformed: Azure Synapse Analytics – can ingest data and transform it for analytics. Azure Stream Analytics – handles real-time streaming data from IoT devices, logs, or other event streams. Azure Data Factory – orchestrates ETL pipelines to move and transform data from multiple sources into storage for analysis. This step ensures that raw operational data is prepared and available in a structured form for analytics. 3. Analytical Data Storage and Processing This is where data is stored and processed for insights: Azure Data Lake Storage Gen2 – stores large volumes of structured and unstructured data for analytical workloads. Azure Synapse Analytics – provides SQL-based querying and analytical processing, either serverless or dedicated. Azure Databricks – allows big data processing, machine learning, and advanced analytics. The flow often looks like: Data from operational sources is ingested into Data Lake. Analytical queries or processing jobs are run using Synapse SQL pools or Databricks. Data is transformed into a format suitable for reporting or visualization. 4. Data Modeling and Visualization Once the data is prepared and processed: Microsoft Power BI – connects to the processed data and allows users to: Build dashboards and reports Visualize trends, KPIs, and insights Perform ad-hoc data exploration This step turns raw and processed data into actionable insights for business users. Summary Flow Operational Data → raw source data from SQL, NoSQL, applications, IoT. Data Ingestion/ETL → use Synapse, Stream Analytics, Data Factory to bring and transform data. Analytical Storage & Processing → store in Data Lake, process with Synapse or Databricks. Data Modeling & Visualization → connect to Power BI to produce reports and dashboards.

Following up on my previous discussion about Infrastructure as Code (#IaC) for data, I wanted to spotlight my go-to stack for building robust, observable, and AI-ready data solutions: DltHub, Dagster, and the boring-semantic-layer (powered by Ibis). 1. dltHub - Intelligent Data Ingestion for Diverse & Numerous Sources DltHub isn't just a connector; it's a strategic choice for efficient E&L within an ELT paradigm. * Scalable E&L: While robust for any setup, DltHub truly shines when dealing with numerous and diverse data sources, not just one or two. It streamlines the Extract and Load phases in ELT. * Pythonic & Resilient: Simple yet powerful, handling schema evolution, normalization, and incremental loads with ease. * Framework-Centric: Offers a standardized, Python-based, and highly customizable open-source approach to ingestion. * Complements Orchestration: Specialized for data extraction intricacies; Dagster then orchestrates its runs and tracks outputs as assets. 2. Dagster Labs - Asset-Centric Orchestration & Observability Dagster transforms orchestration into holistic data asset management. * Asset-Aware Design: Defines and manages data as core entities with clear lineage and dependencies. * Powerful Observability: Provides a unified view, robust monitoring, and efficient debugging via its rich UI. * Drives Operational Excellence: Ensures data pipelines are understood, governed, and contribute to data quality. 3. The boring-semantic-layer (Ibis-powered) by Julien Hurault: The Open Bridge to Business & AI This layer is crucial for consistent analytics and unlocking AI's potential. * Consistent Metrics: Ensures a single source of truth for business metrics. * Pythonic & Programmable: Built on Ibis for flexible, code-based metric definitions. * Leverages dbt Core: Designed to sit atop your dbt Core models, utilizing existing reliable transformations. * MCP for AI/LLMs: Explicit #ModelContextProtocol (#MCP) integration serves governed business knowledge directly to #LLMs, mitigating "hallucination." * Strategic & Vendor-Neutral: An open-source alternative providing maximum flexibility and decoupling your semantic layer from proprietary solutions. The Modularity Principle: Frameworks, Not Compute Engines It's crucial to remember these tools are frameworks, libraries, and orchestrators. They provide the intelligence, structure, and control, while relying on powerful cloud data platforms like Snowflake, Databricks, or Google Cloud for the heavy lifting of storage and processing. This modular approach allows for best-of-breed solutions, ensuring scalability and adaptability. What are your thoughts on building open and intelligent data platforms? Share your insights below! (See first comment for demos and the GitHub link for boring-semantic-layer!) #DataStack #DataEngineering #DltHub #Dagster #SemanticLayer #AI #LLMs #ModelContextProtocol #OpenSource #DataArchitecture #ELT #DataOps #DataQuality #Observability #dbtCore

𝐍𝐞𝐰 𝐞𝐱𝐚𝐦𝐩𝐥𝐞 🚀 : SEC EDGAR Financial Analytics from unstructured data with CocoIndex pipeline that ingests - TXT filings, JSON company facts, PDF exhibits, scrubs PII, extracts topic tags, generates embeddings, and exports everything into Apache Doris / VeloDB (Powered by Apache Doris) for hybrid search combining vector similarity with full-text matching using Reciprocal Rank Fusion. Every quarter, thousands of 10-Ks, 10-Qs, and exhibits land on EDGAR — across TXT, JSON, and PDF formats. Searching across all of them effectively requires more than keyword matching. You need semantic understanding, structured metadata filtering, and the ability to combine multiple document formats into a single searchable index. 𝐖𝐡𝐚𝐭 𝐭𝐡𝐞 𝐩𝐢𝐩𝐞𝐥𝐢𝐧𝐞 𝐝𝐨𝐞𝐬: • 𝐌𝐮𝐥𝐭𝐢-𝐬𝐨𝐮𝐫𝐜𝐞 𝐢𝐧𝐠𝐞𝐬𝐭𝐢𝐨𝐧 — TXT filings, JSON company facts, and PDF exhibits all flow into one unified index • 𝐏𝐈𝐈 𝐬𝐜𝐫𝐮𝐛𝐛𝐢𝐧𝐠 — SSNs, phone numbers, and emails are stripped before chunking so sensitive data never enters the search index • 𝐓𝐨𝐩𝐢𝐜 𝐞𝐱𝐭𝐫𝐚𝐜𝐭𝐢𝐨𝐧 — Each chunk gets tagged (RISK:CYBER, TOPIC:AI, RISK:CLIMATE…) for structured filtering • 𝐇𝐲𝐛𝐫𝐢𝐝 𝐬𝐞𝐚𝐫𝐜𝐡 — Vector similarity + full-text matching combined with Reciprocal Rank Fusion (RRF) in a single SQL query • 𝐈𝐧𝐜𝐫𝐞𝐦𝐞𝐧𝐭𝐚𝐥 𝐩𝐫𝐨𝐜𝐞𝐬𝐬𝐢𝐧𝐠 — When new filings land and processing logic are updated, only recomputes what changed in the input data or the code. End-to-end updates drop from hours/days to seconds while keeping full correctness. • 𝐃𝐚𝐭𝐚 𝐥𝐢𝐧𝐞𝐚𝐠𝐞 — Trace any search result back through the pipeline: which source file, which chunk, what PII was scrubbed, what topics were tagged. Critical for auditability in regulated industries 𝐓𝐡𝐞 𝐬𝐭𝐚𝐜𝐤: CocoIndex handles the data transformation — incremental processing, data lineage, and modular pipeline design. Apache Doris powers the search with HNSW vector indexes and inverted full-text indexes on the same table. No separate vector store needed. CocoInsight lets you visually walk the full lineage from raw filing to indexed embedding — see exactly how each field was constructed and verify transformations at every step. Search "cybersecurity risks in cloud infrastructure" and get ranked results across all three source types, filtered by company, time period, or risk category. 🔥 Full walkthrough with code and source code: https://lnkd.in/gTf6sqpQ - Thanks Tom Z. ! 🌟 Example is open sourced, star if you like it - https://lnkd.in/g6gKPwTX The same patterns — multi-source ingestion, PII scrubbing, hybrid search, topic filtering — apply to healthcare records, legal documents, or any domain with heterogeneous document formats. #CocoIndex #OpenSource #ApacheDoris #SEC #FinancialAnalytics #HybridSearch #AI #Python #DataEngineering #ContextEngineering #VectorSearch #DataPipelines #AIInfrastructure #EnterpriseAI #VeloDB