By the end of this guide, you will have an automated, scheduled pipeline that extracts data from any connected service and writes it into your data warehouse with a write mode that matches how your data changes. Choose this pattern when your analytics live in a warehouse such as Google BigQuery, Snowflake, Amazon Redshift, or Databricks. If you want to feed dashboards directly without your own storage, see Data to Dashboards. If your target is file-based object storage or a lakehouse, see Data Lake Ingestion. If you need low-latency replication of operational databases, see Real-Time CDC Database Replication.
Architecture
A pipeline consists of a data source, a data flow, and a data destination. The source extracts data on a schedule you define, creating a snapshot with each run. The flow then writes the data to your warehouse. Extraction and writing are de-coupled: by setting a custom write trigger, you can control the writing process independently of extraction. When data is written to your storage, it always includes the full content of the most recent extraction.
Two flow types are available:
- Batch Data Flow is the standard and default type, and supports flow-level transformations.
- Streaming Data Flow is usually considerably faster for larger data volumes and guarantees that no data is cached during transfer, but features that require caching, such as flow-level transformations, are unavailable. See Data Flows Overview.
Automatic Table Setup
Dataddo creates an optimized table structure in your warehouse during the initial setup, including column names, data types, and indices. Once created, the schema is not updated automatically, so unintended changes are avoided; later modifications must be made manually. This matters especially for upsert: indexing is added automatically on the first data load for a new table, but cannot be added retroactively to an existing table. See Data Storages for details.
Adaptive Data Ingestion
Dataddo selects the most suitable loading method for your warehouse based on data volume: batch insert queries for smaller volumes, loading through files in object storage as volume grows, and streaming for very large volumes. Some techniques may require extra setup or higher permission levels on the destination side. See Data Storages for details.
ETL or ELT: Where to Transform
Dataddo supports both patterns, and most teams combine them:
- Extraction-level transformations (ETL) are performed automatically so data arrives warehouse-ready: data flattening, data harmonization, and metadata inclusion. For custom logic, transformation pipeline scripts let you reshape, filter, and derive fields during extraction, and generic connectors like the JSON Universal Connector allow tailored adjustments for unusual APIs.
- Flow-level transformations consolidate data from multiple sources before ingestion, for example via data union or blending. Not available in Streaming Data Flows.
- Warehouse-level transformations (ELT) run inside the warehouse itself or via tools like dbt or Airflow: materialized views, restructuring, advanced calculations. Because Dataddo delivers harmonized, analytics-ready data, the transformation work left for this layer is significantly reduced.
Which mix you choose depends on your overall architecture: teams standardizing on warehouse-native modeling keep Dataddo transformations minimal (ELT), while teams without a modeling layer let Dataddo deliver fully prepared tables (ETL).
Technical Metadata for Warehouse Tables
During source creation you can enrich every row with system metadata columns that make warehouse loading strategies work (see Metadata Inclusion):
| Metadata field | Column | What it gives you |
|---|---|---|
| Dataddo Extraction Timestamp | dataddo_extraction_timestamp |
The time each row was extracted; the basis for tracking Slowly Changing Dimensions and auditing loads |
| Dataddo Hash | dataddo_hash |
A hash of selected columns acting as a generated natural key; use it directly as the upsert composite key instead of hand-picking key columns (hash algorithm selectable: md5 or xxh3) |
| Extraction date range | dataddo_date_start, dataddo_date_stop |
The period each extraction covered; useful for partition management and reconciliation |
| Account identifiers | account id / account name | On multi-account sources, which account each row came from (see Multi-Account Extraction) |
Write Modes
The write mode controls how each load is applied to the destination table. Dataddo supports the following modes; availability differs per destination, and the flow setup always shows the valid subset for your target:
| Write mode | Behavior | Typical use |
|---|---|---|
| Insert | Appends new rows to the table (default) | Immutable, non-overlapping data such as daily logs |
| Insert Ignore | Appends rows, silently omitting rows that fail type checks | Tolerating occasional malformed rows |
| Truncate Insert | Deletes all existing rows, then inserts the new load | Full refresh of a bounded dataset |
| Upsert | Inserts new rows and updates existing ones matched by a composite key | Mutable or overlapping data without duplicates |
| Delete | Removes rows matching a delete composite key | Reflecting source deletions |
| CDC | Applies an ordered change stream from a log-based replication source, automatically squashing insert/update/delete operations into a fully materialized table based on the primary key | Database replication mirrors without post-processing (available on selected destinations, e.g. SQL Server, BigQuery) |
Databases and data warehouses typically offer insert, insert ignore, truncate insert, upsert, and delete. Update and update ignore appear mainly on business application destinations, where writes map to API operations on existing objects. For upsert on MySQL, PostgreSQL, SQL Server, Amazon Redshift, and Vertica, table indexing is created automatically on the first load only, so choose upsert when the table is first created (see Automatic Table Setup).
Setup Walkthrough
- Create a data source. Set the extraction frequency and snapshot keeping policy, and the date range each extraction should cover. If you plan to track changes over time, add the Dataddo Extraction Timestamp via metadata inclusion.
- Connect your data warehouse using the relevant connector, e.g. Google BigQuery, Snowflake, Amazon Redshift, Databricks, or Azure Synapse. Make sure the authorized account has permissions to create tables.
- Create a data flow connecting the source to the destination. Select the write mode; for upsert, define the composite key now, while the table is being created, so indexing is set up automatically.
- Verify the first load. Check the flow logs and inspect the automatically created table in your warehouse.
Configuration Recommendations
| Scenario | Write mode | Date range |
|---|---|---|
| Immutable, non-overlapping data (e.g. daily logs) | Insert | Non-overlapping increment, e.g. yesterday |
| Mutable data, latest state only | Upsert with composite key | Overlapping window, e.g. last 7 days |
| Mutable data, track changes over time (SCD) | Upsert with composite key including the Dataddo Extraction Timestamp | Overlapping window |
| Full refresh of a bounded dataset | Truncate Insert | Entire relevant period |
| Reflect deletions from the source | Delete with delete composite key | As required by the deletion feed |
Table Partitioning by Date
Two mechanisms keep large, continuously growing warehouse tables manageable:
- Date placeholders in table names. Use date range expressions in the destination table name to write each period's data into its own table, e.g.
orders_{{1d1}}for one table per day ororders_{{1m1|Ym}}for one per month. Placeholders are re-evaluated at every run, so scheduled flows roll over to new tables automatically. This works for any database or warehouse destination. - Native table partitioning. Some warehouses support partitioning the target table itself; for Google BigQuery, see the Table Partitioning section of the BigQuery destination article.
Operations
- Backfilling historical data: trigger a manual extraction for a chosen date range and then a manual data insert in the flow, as described in Data Backfilling to Storages. Check the flow's write mode first; upsert is recommended to avoid duplicates when the backfilled range overlaps existing data.
- Full Data Re-Sync: loads all historical data at once and is available for destinations that support upsert, i.e. databases and data warehouses. Large requests are automatically split into smaller jobs, re-syncs run separately from regular syncs, and you can monitor jobs on a dedicated overview page. One re-sync job per flow per day; the feature is plan-dependent. See Full Data Re-Sync.
- Data Quality Firewall: validate data between extraction and write with checks for null values, zero values, and anomalies, plus column-level business rules. In blocking mode the transfer stops when a rule is violated, so non-compliant data never reaches the warehouse; in non-blocking mode discrepancies are logged and you are notified. See Data Quality Firewall.
- Monitoring: watch flow logs for failed writes, and use the Data Quality Watcher to alert on anomalies in delivered data.
Troubleshooting
- Duplicates in the warehouse usually mean an inappropriate write mode, typically insert with overlapping extractions. Switch to upsert or truncate insert.
- Table was not created automatically: the flow breaks right after creation, most commonly due to insufficient permissions or an already existing table. See the troubleshooting section of Data Flows Overview.
- Cannot change write mode to upsert on an existing flow: for some destinations, indexes are created only at flow creation. See the workarounds in Data Backfilling to Storages.
- For generic issues, see Troubleshooting.