Questions tagged [etl]

'Extract, Transform and Load': Data load processing, normally in the context of data warehouse systems.

Filter by
Sorted by
Tagged with
0 votes
0 answers
18 views

Best practice for applying complex business logic during transformation in ETL from Aurora db to Redshift data warehouse

I'll be pushing data from an Amazon Aurora db to a Redshift data warehouse. The painful part is that the transformation portion of the ETL workflow relies heavily on business logic, that lives within ...
mstrom's user avatar
  • 133
7 votes
5 answers
1k views

What are the idiomatic approaches to copying a table (and all of its data) from one server to another?

I have a massive table, let's say 500,000 rows. I want to copy it (schema and data) from one server to another. This is not an upsert or any kind of update; It's a one-off straight copy and paste. ...
J. Mini's user avatar
  • 91
1 vote
1 answer
113 views

What are the idiomatic approaches to cross-server upserts?

This is a very common problem, faced by virtually every DBA who has to responsibilities to both application and BI teams. Consider the following: You have two T-SQL servers, Production and Reporting. ...
J. Mini's user avatar
  • 91
1 vote
1 answer
48 views

Optimizing the SQLite database as source data for R scripts

I have an application written in R Shiny that supports a large number of csv's (and 3 new ones are added every day). These files have a structure that is quite unpleasant to handle, so I decided to ...
tomsu's user avatar
  • 111
0 votes
2 answers
76 views

Growing SQL Server Data Warehouse - How to reorganize efficiently?

we've been using SQL Server for quite a while now in our company to host all application data. We use a typical set-up with data being loaded from various data sources into a Staging Area, which ...
Benny's user avatar
  • 3
0 votes
0 answers
43 views

Analysis and Prediction using SQL

I have a transactional application on MSSQL database where people can send money to other people at anytime (More like Cashapp), i want to be able to predict a customer's frequent beneficiary on ...
Dera's user avatar
  • 23
1 vote
0 answers
39 views

ETL process between two Postgres Databases

I have two postgres databases, let's called them layer 1 and layer 2, that both are hosted on a separate servers. In between, there is a third server, that first, loads tables from layer 1, second, ...
Ai4l2s's user avatar
  • 111
0 votes
0 answers
15 views

I need explaination about Develop Detailed Table Schematics In Step 4, Chapter 20, The Data Warehouse Toolkit, 3rd Edition

Have anyone read The Data Warehouse Toolkit, 3rd Edition written by Ralph Kimball and his colleagues? I signed up for a Data Warehouse course at my uni and my prof told me and my group to read Chapter ...
silverbullet069's user avatar
0 votes
1 answer
79 views

Does it makes sense to have the operational DB and analytics DB on the same SQLServer?

Let's say I have a single table that stores tracing information from mobile devices (each record has deviceID, transaction info, a json column with full details about the operation, time of operation, ...
Mattia Durli's user avatar
0 votes
0 answers
34 views

Problem with using @@DBTS between the primary and secondary in an AG

I have an ETL process that uses rowversion field and had an issue, because the ETL process was using the source database which had become out of sync in an AG, so I read the rowversion data from the ...
Keivan Soleimani's user avatar
0 votes
0 answers
24 views

Enable syncing DB dump between tenants in a multi-tenant Postgres database

Our Java application stores data pertaining to multiple tenants in one single DB instance on AWS RDS. This setup is replicated across regions in prod. There are multiple schemas and each table within ...
deGee's user avatar
  • 111
0 votes
0 answers
14 views

SSIS - Retrieve variable value from controle flow (no using script task) to data flow

I'm facing an issue with a case where I need to retrieve variable value from controle fow to data flow. The value of the variable in controle flow = 3 but in data flow = 0 as if the value was not ...
Joe's user avatar
  • 1
0 votes
0 answers
20 views

Creating a real time data pipeline with transactional DB

I am working in fintech industry, they recently needed a dash-boarding solution for executive , finance and other departments while they also want AI solution to keep track of valid disputes and fraud ...
Muhammad Affan's user avatar
0 votes
0 answers
10 views

Using Stitch to import data to Postgres database, not all selected fields are being replicated

Has anyone has trouble with replicating tables/fields from the service Stitch? I've used this for various data sources in the past and I'm currently having issues with the platform Kustomer. The ...
N. Marchetti's user avatar
0 votes
1 answer
45 views

Update or insert records to the same table based on a similar table but with conditions

I'm tryin to update or insert records on the same table based on a similar table using Merge but based on the count of records in the source table. I have 2 tables say one is POV [pov, dc, item](the ...
Akash Reddy's user avatar
0 votes
0 answers
48 views

Neo4j to MS SQL ETL

There is Neo4j graph database storing devices data, I need those data need to out in MS SQL Reporting Data warehouse database. So essentially, I need conversion from Graph Database into RDMBS strong ...
Bhavesh Harsora's user avatar
0 votes
0 answers
572 views

ETL staging area incremental load design considerations

We are currently working on a design for our new data warehouse. In our current data warehouse we have a 'persistent staging area' or PSA. We load new records incrementally into a staging table and ...
Niels Broertjes's user avatar
1 vote
1 answer
37 views

Detect table updates on OracleDB

Say we have a table in oracledb called fruits: id fruit 1 banana 2 mango 3 melon Imagine this table is being overwitten completely, say every Friday for example, the column id is the primary key. ...
moth's user avatar
  • 113
1 vote
1 answer
359 views

ETL table from one SQL Server to another SQL Server

I would like to ETL a table from one instance of SQL Server to another instance of SQL Server. I do not want to create an SSIS package. I have tried to extract the data using an External Table on the ...
xhr489's user avatar
  • 805
0 votes
1 answer
574 views

Azure Data Factory - copy data activity into a Postgresql database (on an IaaS Windows Azure vm, not Azure Database for Postgresql (PaaS))

I have a Postgresql database on a Windows vm (not a PaaS Azure Database), and I need to import csv files into it daily. ADF seems like a perfect fit, but I see that a standard Postgresql db is not a ...
Bobogator's user avatar
0 votes
2 answers
445 views

dbatools import csv help!

Is there a way I can import csv files with the proper data types into MSSQL table using PS dba tools? I'm using this below command but all I get is a table and all the columns are nvarchar(max): ...
SAKA UK's user avatar
  • 90
0 votes
1 answer
98 views

Postgres improvements in conversion functions for bulk load

I regularly import data from httparchive.org. The data is a MySQL CSV export and I use pgloader, which handles the quirks of this export (\N for NULL), etc. I also need to do some additional ...
Charlie Clark's user avatar
0 votes
1 answer
414 views

Bigint vs varchar datatype. Should I match source system? [closed]

Unfortunately, I think this is an opinion question. But I'd like to know if there is a best practice. Our datawarehouse uses a bigint as a primary key for an address table. The existing records all ...
Dayton Brown's user avatar
1 vote
1 answer
145 views

Bulk Updates on Normalised data present across several PostgreSQL tables

We have several websites from where we download csv files - say 100 such websites. We don't have a control over their schedules, so we download files from them every few hours. Each such file can be ...
KnowSQL's user avatar
  • 13
0 votes
1 answer
161 views

Extracting multiple similar tables in different schemas

I am attempting to extract tables from multiple schemas in the same database. The tables are similar enough that they are easy to identify and work with using meta data queries like this one: SELECT ...
David Hamilton's user avatar
1 vote
2 answers
146 views

From what volumes of data do data ingestion tools like apache nifi, flume, storm or tools like logstash become relevant?

I'm working in a company that has two legacy data warehouses, which have evolved to unmaintainable monoliths throughout the time. Therefore, they are in dire need of re-form. I'm investigating a ...
Psychotechnopath's user avatar
0 votes
0 answers
33 views

Best approach to convert a non trivial function (CASE WHEN) into a lookup (decision) table?

Assume A, B, C etc are attribute values in the current rowset Current function logic is similar to: Case A = 'Y' then Case B in (1,2,3,4,5) OR C in (3,4,5,6) then 'Sales' Case B in (99,98,102) OR D ...
adolf garlic 's user avatar
1 vote
1 answer
96 views

SSIS: Can't save "parallel load" option in Oracle destination with Attunity driver, how to solve this?

I am working on an existing SSIS package that has proven to run successfully, however, due to data volume issues, I would like to try enabling the "parallel load" option by selecting the ...
adolf garlic 's user avatar
0 votes
2 answers
149 views

Selecting MySQL row during Batch Update

I currently have a database with 500K rows that could grow to 3 million in the future. The structure of the table looks like the following: -id -price -sku -version (denoted by a Date Time string ...
Student's user avatar
  • 103
0 votes
1 answer
413 views

How to export data from AWS RDS MySQL database and import to an other AWS RDS MySQL database instance

I am looking for the best way to export data from one MySQL DB instance of RDS an import into a different one automatically. Any ideas?
Jordi Gallego's user avatar
2 votes
1 answer
4k views

What is the best way to export/extract varbinary images in a SQL Server database to flat file via t sql, anything better than sp_OA methods?

I've been tasked to extract images saved in SQL Server (as varbinary) to flat files via t-sql. I haven't done ETL work for over a decade and I can't recall any other method of doing this via t-sql ...
Eric Swiggum's user avatar
3 votes
3 answers
996 views

ADF patterns to minimise batch run elapsed time

We use Azure Data Factory (ADF) to pull a number of source tables from an on-prem SQL Server DB into Azure Data Lake (DL). We've made this data-driven using the Lookup-ForEach pattern. There is one ...
Michael Green's user avatar
1 vote
3 answers
164 views

Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of the time bucket width and data size?

For an ETL reporting system, is it normal for the total execution time for a pull of 15 minutes with no data to be similar to a 24 hour pull with data? I had expected that the total time for ETL when ...
D-K's user avatar
  • 503
1 vote
0 answers
31 views

History data in CUBE

Not sure if this is the right place to ask. I am working on an ETL procedure that gathers the data about medication consumption and I have something like this: Dim_Patient, the dimension for all the ...
Jovan Perovic's user avatar
0 votes
2 answers
503 views

How to "normalize" external data before insertion into "Data Warehouse" like database?

I want to be database agnostic but let's say we're inserting data into SQL Server which is to be used as a data warehouse. There are MILLIONS of email records from an email platform that wasn't too ...
user45867's user avatar
  • 1,683
1 vote
1 answer
763 views

Is "ETL vs ELT" just "tables vs views"?

TL;DR; Is the difference between ETL and ELT just whether or not the transformed data is a concretion vs abstraction before being loaded into the warehouse? So forgive me the broadness of the subject ...
e_i_pi's user avatar
  • 217
0 votes
1 answer
194 views

Oracle - Exchange partitions between two List partitioned tables

Need some help here. I have an ETL process which loads the data into a target table A. We have created another table B which is same as the target table in structure and this table is accessed by ...
Manish Mishra's user avatar
1 vote
2 answers
2k views

Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero. From the DBA I ...
D-K's user avatar
  • 503
0 votes
0 answers
140 views

How to ETL SQL Server resultset to mongo DB

Let's say I have multiple columns on sql server that I want to join and save the resultset into a single mongodb collection for faster lookup and calculations ( I'm talking about over 15 tables and ...
beto's user avatar
  • 1
0 votes
1 answer
587 views

Truncate and insert: Heap and build index or directly into Clustered table

In ETL is it better to drop the index before inserting millions of rows and right after create the index again, or to simply insert into the empty table with the indexe in place. I know that I can ...
xhr489's user avatar
  • 805
1 vote
1 answer
511 views

ODI remove auto lock when object / datastore is opened

I was being stupid and enabled automatically lock object during edit / opened when I was asked the first time. Now I want to remove the automatically lock from GUI but I couldn't seem to find it. I ...
Dora's user avatar
  • 125
-1 votes
1 answer
58 views

Non-procedural way of implementing ELT dependency logic

I have two tables with identical schema, sales_primary and sales_secondary: productID trxnWeekStartDate dollarAmt 123 2021-02-07 100 For a particular week, these two tables can both have a ...
Ryan's user avatar
  • 313
1 vote
1 answer
103 views

RCSI & ETL processing compatibility

The Situation: I have a write-heavy, read-light ETL workload with the basic process of: get chunk of raw data (generally 1000-5000 records) from an import landing table. pass the chunk of data from ...
SqlNightOwl's user avatar
2 votes
2 answers
821 views

What is the correct and performant pattern for loading (and querying) a large clustered columnstore table?

I am doing some test with clustered columnstore tables - both partitioned and not. In my research, I've seen flavors of this pattern: -- step 1: make new table SELECT TOP 0 * INTO [Fact].[Order_CCI] ...
Chris Harrington's user avatar
2 votes
1 answer
120 views

What are the disadadvantages of a datawarehouse with no datawarehouse tables, but only datamarts?

The ETL design pattern prescribes transformation from staging tables to a central data warehouse data model, followed by extracting data from the data warehouse data model to data marts. Are there any ...
dotsomething's user avatar
1 vote
1 answer
227 views

MYSQL Huge Data migration (5TB) from old server to new server

We have an old MYSQL server contains huge data ~5TB and wanted to migrate to the new server in order to minimize the costs and get rid of very old hardware. My one and only idea in my mind is using ...
BTH.S3's user avatar
  • 111
0 votes
2 answers
450 views

SQL join on col1 if present else use col2

There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup. What I want is either join on T1.col1 = T2.col1 or T1....
Theja's user avatar
  • 101
0 votes
1 answer
289 views

Snowflake/S3 Pipeline: ETL architecture Questions

I am trying to build a pipeline which is sending data from Snowflake to S3 and then from S3 back into Snowflake (after running it through a production ML model on Sagemaker). I am new to Data ...
cocoo84hh's user avatar
  • 101
1 vote
0 answers
264 views

How to modify the block size of my S3 parquet files that are being queried in Athena?

I read that adjusting the block size of the parquet files being queried with Athena can affect and possibly improve the performance of the queries. My parquet files for my database are currently ...
J.D.'s user avatar
  • 37.2k
0 votes
0 answers
688 views

Avoid transaction when reading data through a linked server

This is SQL Server related. Is there other techniques that I can use for this? (Read data from a procedure in another server.) I'm trying to re-use a stored procedure from another sever instance ...
Jahaziel's user avatar
  • 101

1
2 3 4 5 6