Batch design, incremental loads, idempotency, dbt
Practise these as flashcards. One or two lines each; the ones you miss come back first.
Start freeETL transforms data before loading it into the destination, while ELT loads raw data first and then transforms it within the destination system, usually a data warehouse.
Cloud data warehouses like BigQuery and Snowflake are optimized for in-database transformations, making ELT more scalable and cost-effective by leveraging their compute power after loading.
A watermark is a marker, such as a timestamp or an ID, that tracks the latest data processed, enabling incremental loads by only processing new or updated records.
Using an UPSERT or MERGE statement to insert or update records ensures that running the job multiple times with the same data does not create duplicates.
updated_at column?I would store the maximum updated_at value processed in each run and use it as a filter in the next run, selecting only records where updated_at is greater than the last watermark.
Parquet is a columnar, binary format that provides better compression and query performance, especially for analytical workloads, while CSV is text-based and less efficient for large-scale analytics.
dbt incremental models process only new or changed data by using filters based on watermarks or unique keys, and it manages the logic to append or update records in the target table.
Late-arriving data is data that arrives after its expected processing window. To handle it, I might reprocess affected partitions or use upserts to update previously loaded data.
CDC tracks and captures changes (inserts, updates, deletes) in source data, enabling near-real-time or incremental updates. It's useful when you need to keep the target system in sync with source changes efficiently.
Delta Lake and Iceberg add ACID transactions, schema evolution, and time travel capabilities to Parquet tables, making them more suitable for production data lakes with concurrent writes and evolving schemas.
dbt supports tests for uniqueness, non-null values, referential integrity, and custom SQL-based checks to ensure data quality and correctness in models.
I would use upserts or merge operations to ensure repeated runs don't create duplicates. For backfills, I'd parameterize the pipeline to process specific date ranges and ensure historical data can be safely reprocessed without affecting current data.